目录
案例 1 - 处理更新的版本控制
情况 2 - pollid 作为 uuid 而不是主键
情况 3 - 选项为空或无效
案例 4 - 重复选项
案例 5 - 问题长度限制
案例 6 - 投票过期
请先参考以下文章:
底层设计:投票系统:基本
底层设计:轮询系统 - 使用 node.js 和 sql
要管理投票问题和选项的更新,同时保留与同一投票 id 关联的先前详细信息,您可以实现版本控制系统。这种方法允许您跟踪每次民意调查的历史数据,确保即使在更新后也保留旧的详细信息。
更新投票表
创建投票版本表
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 );
修改 updatepoll 方法,在创建新版本之前检查问题是否发生变化。
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 { 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 { 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." }); } };
确保在 pollroutes.js 中正确定义路由。
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;
数据库:
api:
路线:
处理 pollid 需要是 uuid(通用唯一标识符)的场景。
以下是在轮询系统中为 thepollid 实现 uuid 的步骤,无需提供代码:
** 数据库架构更新:**
** uuid 生成:**
** 创建投票逻辑:**
** 更新投票逻辑:**
检查问题是否已更改。
** 投票逻辑:**
验证投票请求中提供的 uuid 是否存在于 polls 表中。
** api 更新:**
** 测试:**
** 文档:**
按照以下步骤,您可以在轮询系统中成功实现 pollid 的 uuid,同时确保数据完整性和历史跟踪。
空或无效选项
验证方法:
重复选项
唯一性检查:
问题长度限制
字符限制:
投票过期
过期机制:
请先参考以下文章:
底层设计:投票系统:基本
底层设计:轮询系统 - 使用 node.js 和 sql
更多详情:
获取所有与系统设计相关的文章
标签:systemdesignwithzeeshanali
系统设计与zeeshanali
git:https://github.com/zeeshanali-0704/systemdesignwithzeeshanali