底层设计：轮询系统 - 边缘情况

CREATE DATABASE polling_system; USE polling_system; CREATE TABLE polls ( poll_id INT AUTO_INCREMENT PRIMARY KEY, current_version_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (current_version_id) REFERENCES poll_versions(version_id) ON DELETE SET NULL ); CREATE TABLE poll_versions ( version_id INT AUTO_INCREMENT PRIMARY KEY, poll_id INT, question VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE ); 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 );

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 (current_version_id) VALUES (NULL)' ); const pollId = result.insertId; const [versionResult] = await connection.execute( 'INSERT INTO poll_versions (poll_id, question) VALUES (?, ?)', [pollId, question] ); const versionId = versionResult.insertId; // Update the current version in the polls table await connection.execute( 'UPDATE polls SET current_version_id = ? WHERE poll_id = ?', [versionId, 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(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 || !options || !Array.isArray(options) || options.length < 2) { return res.status(400).json({ message: "Invalid input data. At least two options are required." }); } try { const connection = await pool.getConnection(); await connection.beginTransaction(); // Fetch the existing poll const [existingPoll] = await connection.execute( 'SELECT question FROM poll_versions WHERE poll_id = (SELECT current_version_id FROM polls WHERE poll_id = ?)', [pollId] ); if (existingPoll.length === 0) { await connection.rollback(); connection.release(); return res.status(404).json({ message: "Poll not found." }); } const currentQuestion = existingPoll[0].question; // Check if the question has changed if (currentQuestion !== question) { // Create a new version since the question has changed const [versionResult] = await connection.execute( 'INSERT INTO poll_versions (poll_id, question) VALUES (?, ?)', [pollId, question] ); const versionId = versionResult.insertId; // Update the current version in the polls table await connection.execute( 'UPDATE polls SET current_version_id = ? WHERE poll_id = ?', [versionId, pollId] ); } // Remove old options and insert new ones 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); await connection.rollback(); 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." }); } };

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;