請參考文章輪詢系統基礎底層設計-I
讓我們將整個過程分解為詳細的步驟，包括資料庫設定、使用 Node.js 和 Express 實作 API 以及與 MySQL 的交互。我們將涵蓋：
首先，我們將定義 MySQL 資料庫的架構並建立必要的表。
CREATE DATABASE polling_system;
USE polling_system;
CREATE TABLE polls (
poll_id INT AUTO_INCREMENT PRIMARY KEY,
question VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE options (
option_id INT AUTO_INCREMENT PRIMARY KEY,
poll_id INT,
option_text VARCHAR(255) NOT NULL,
FOREIGN KEY (poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE
);
CREATE TABLE votes (
vote_id INT AUTO_INCREMENT PRIMARY KEY,
poll_id INT,
user_id VARCHAR(255) NOT NULL,
option_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE,
FOREIGN KEY (option_id) REFERENCES options(option_id) ON DELETE CASCADE
);
投票表：儲存帶有唯一識別碼、問題和建立時間戳記的投票資訊。
選項表：儲存與投票相關的選項，透過 poll_id 連結。
投票表：記錄每次投票，連結到民意調查、選項和使用者。
實體：
關係：
這是 ERD 的描述：
投票表：
選項表:
投票表：
關係將以實體之間的線表示：
讓我們使用 Express 和 MySQL 設定一個 Node.js 專案。
mkdir polling-system cd polling-system npm init -y npm install express mysql2 dotenv
為專案建立基本結構：
polling-system/
│
├── .env
├── index.js
├── db/
│ └── db.js
├── routes/
│ └── pollRoutes.js
└── controllers/
└── pollController.js
檔案 - db/db.js
const mysql = require('mysql2/promise');
require('dotenv').config();
const pool = mysql.createPool({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
module.exports = pool;
文件 - .env
DB_HOST=localhost DB_USER=root DB_PASSWORD=yourpassword DB_NAME=polling_system PORT=3000
檔案-controllers/pollController.js
該檔案將實現輪詢系統所有必需的 CRUD 操作。
const pool = require('../db/db');
// Create Poll
exports.createPoll = async (req, res) => {
const { question, options } = req.body;
if (!question || !options || !Array.isArray(options) || options.length < 2) {
return res.status(400).json({ message: "Invalid input data. Question and at least two options are required." });
}
try {
const connection = await pool.getConnection();
await connection.beginTransaction();
const [result] = await connection.execute(
'INSERT INTO polls (question) VALUES (?)',
[question]
);
const pollId = result.insertId;
const optionQueries = options.map(option => {
return connection.execute(
'INSERT INTO options (poll_id, option_text) VALUES (?, ?)',
[pollId, option]
);
});
await Promise.all(optionQueries);
await connection.commit();
connection.release();
res.status(201).json({ pollId, message: "Poll created successfully." });
} catch (error) {
console.error("Error creating poll:", error.message);
res.status(500).json({ message: "Error creating poll." });
}
};
// Update Poll
exports.updatePoll = async (req, res) => {
const { pollId } = req.params;
const { question, options } = req.body;
if (!pollId || !question || !options || !Array.isArray(options) || options.length < 2) {
return res.status(400).json({ message: "Invalid input data. Question and at least two options are required." });
}
try {
const connection = await pool.getConnection();
await connection.beginTransaction();
const [pollResult] = await connection.execute(
'UPDATE polls SET question = ? WHERE poll_id = ?',
[question, pollId]
);
if (pollResult.affectedRows === 0) {
await connection.rollback();
connection.release();
return res.status(404).json({ message: "Poll not found." });
}
await connection.execute('DELETE FROM options WHERE poll_id = ?', [pollId]);
const optionQueries = options.map(option => {
return connection.execute(
'INSERT INTO options (poll_id, option_text) VALUES (?, ?)',
[pollId, option]
);
});
await Promise.all(optionQueries);
await connection.commit();
connection.release();
res.status(200).json({ message: "Poll updated successfully." });
} catch (error) {
console.error("Error updating poll:", error.message);
res.status(500).json({ message: "Error updating poll." });
}
};
// Delete Poll
exports.deletePoll = async (req, res) => {
const { pollId } = req.params;
try {
const connection = await pool.getConnection();
const [result] = await connection.execute(
'DELETE FROM polls WHERE poll_id = ?',
[pollId]
);
connection.release();
if (result.affectedRows === 0) {
return res.status(404).json({ message: "Poll not found." });
}
res.status(200).json({ message: "Poll deleted successfully." });
} catch (error) {
console.error("Error deleting poll:", error.message);
res.status(500).json({ message: "Error deleting poll." });
}
};
// Vote in Poll
exports.voteInPoll = async (req, res) => {
const { pollId } = req.params;
const { userId, option } = req.body;
if (!userId || !option) {
return res.status(400).json({ message: "User ID and option are required." });
}
try {
const connection = await pool.getConnection();
const [userVote] = await connection.execute(
'SELECT * FROM votes WHERE poll_id = ? AND user_id = ?',
[pollId, userId]
);
if (userVote.length > 0) {
connection.release();
return res.status(400).json({ message: "User has already voted." });
}
const [optionResult] = await connection.execute(
'SELECT option_id FROM options WHERE poll_id = ? AND option_text = ?',
[pollId, option]
);
if (optionResult.length === 0) {
connection.release();
return res.status(404).json({ message: "Option not found." });
}
const optionId = optionResult[0].option_id;
await connection.execute(
'INSERT INTO votes (poll_id, user_id, option_id) VALUES (?, ?, ?)',
[pollId, userId, optionId]
);
connection.release();
res.status(200).json({ message: "Vote cast successfully." });
} catch (error) {
console.error("Error casting vote:", error.message);
res.status(500).json({ message: "Error casting vote." });
}
};
// View Poll Results
exports.viewPollResults = async (req, res) => {
const { pollId } = req.params;
try {
const connection = await pool.getConnection();
const [poll] = await connection.execute(
'SELECT * FROM polls WHERE poll_id = ?',
[pollId]
);
if (poll.length === 0) {
connection.release();
return res.status(404).json({ message: "Poll not found." });
}
const [options] = await connection.execute(
'SELECT option_text, COUNT(votes.option_id) as vote_count FROM options ' +
'LEFT JOIN votes ON options.option_id = votes.option_id ' +
'WHERE options.poll_id = ? GROUP BY options.option_id',
[pollId]
);
connection.release();
res.status(200).json({
pollId: poll[0].poll_id,
question: poll[0].question,
results: options.reduce((acc, option) => {
acc[option.option_text] = option.vote_count;
return acc;
}, {})
});
} catch (error) {
console.error("Error viewing poll results:", error.message);
res.status(500).json({ message: "Error viewing poll results." });
}
};
檔案-routes/pollRoutes.js
定義每個 API 端點的路由：
const express = require('express');
const router = express.Router();
const pollController = require('../controllers/pollController');
//
Routes
router.post('/polls', pollController.createPoll);
router.put('/polls/:pollId', pollController.updatePoll);
router.delete('/polls/:pollId', pollController.deletePoll);
router.post('/polls/:pollId/vote', pollController.voteInPoll);
router.get('/polls/:pollId/results', pollController.viewPollResults);
module.exports = router;
檔案 - index.js
最後，設定伺服器：
const express = require('express');
const pollRoutes = require('./routes/pollRoutes');
require('dotenv').config();
const app = express();
app.use(express.json());
// Routes
app.use('/api', pollRoutes);
// Error Handling Middleware
app.use((err, req, res, next) => {
console.error(err.stack);
res.status(500).json({ message: "Internal server error" });
});
// Start Server
const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
console.log(`Server is running on port ${PORT}`);
});
每種方法都包含對常見問題的錯誤處理，例如無效輸入、重複投票、缺少投票或選項以及伺服器錯誤。
使用Postman或curl等工具測試每個端點。
這是一個使用 Node.js、Express 和 MySQL 的線上投票系統的全面模組化實作。它處理基本的CRUD操作並確保資料與交易的一致性。它還包括基本的錯誤處理，使 API 更加健壯和用戶友好。
請參考文章輪詢系統基礎底層設計-I
更多詳情：
取得所有與系統設計相關的文章
標籤：SystemDesignWithZeeshanAli
系統設計與zeeshanali
Git：https://github.com/ZeeshanAli-0704/SystemDesignWithZeeshanAli
以上是底層設計：輪詢系統 - 使用 Nodejs的詳細內容。更多資訊請關注PHP中文網其他相關文章！
