From c80656abf09459c8b13c9cdb33c9a8d3a8cee502 Mon Sep 17 00:00:00 2001
From: LarsVomMars
Date: Fri, 2 Oct 2020 23:29:06 +0200
Subject: Added basic voting

---
 tables.sql | 21 +++++++++++++++------
 1 file changed, 15 insertions(+), 6 deletions(-)

(limited to 'tables.sql')

diff --git a/tables.sql b/tables.sql
index 6161b03..ea86339 100644
--- a/tables.sql
+++ b/tables.sql
@@ -9,12 +9,13 @@ CREATE TABLE IF NOT EXISTS theme(
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 -- TODO: Remove dropping
-DROP TABLE IF EXISTS quotes;
-DROP TABLE IF EXISTS ranking_questions;
-DROP TABLE IF EXISTS ranking_answers;
-DROP TABLE IF EXISTS users;
-DROP TABLE IF EXISTS types;
-DROP TABLE IF EXISTS class;
+-- DROP TABLE IF EXISTS motto_votes;
+-- DROP TABLE IF EXISTS quotes;
+-- DROP TABLE IF EXISTS ranking_questions;
+-- DROP TABLE IF EXISTS ranking_answers;
+-- DROP TABLE IF EXISTS users;
+-- DROP TABLE IF EXISTS types;
+-- DROP TABLE IF EXISTS class;
 
 CREATE TABLE IF NOT EXISTS types(
     id INTEGER PRIMARY KEY AUTO_INCREMENT,
@@ -72,3 +73,11 @@ CREATE TABLE IF NOT EXISTS ranking_answers(
     CONSTRAINT `fk_user_answer1` FOREIGN KEY (user_id) REFERENCES users (id),
     CONSTRAINT `fk_user_answer2` FOREIGN KEY (answer_id) REFERENCES users (id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+
+CREATE TABLE IF NOT EXISTS motto_votes(
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    name VARCHAR(255) NOT NULL,
+    description VARCHAR(255) NOT NULL DEFAULT '',
+    votes INTEGER DEFAULT 0
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 
cgit v1.2.3


From 467eb283888328b977871b6899bede744cedfdd8 Mon Sep 17 00:00:00 2001
From: LarsVomMars
Date: Sat, 3 Oct 2020 00:41:10 +0200
Subject: Multivote

---
 db.js                      |  2 +-
 mottovote/index.js         | 23 ++++++++++++++++-------
 mottovote/public/script.js | 30 ++++++++++++++++--------------
 tables.sql                 | 17 +++++++++++++++--
 4 files changed, 48 insertions(+), 24 deletions(-)

(limited to 'tables.sql')

diff --git a/db.js b/db.js
index 26e3d97..193daa8 100644
--- a/db.js
+++ b/db.js
@@ -57,7 +57,7 @@ class DB {
                 mottos.forEach(async (motto) => {
                     const [name, desc] = motto.split(" - ");
                     if (motto)
-                        await this.query("INSERT INTO motto_votes (name, description) VALUES (?, ?)", [name, desc]);
+                        await this.query("INSERT INTO mottos (name, description) VALUES (?, ?)", [name, desc]);
                 });
             });
 
diff --git a/mottovote/index.js b/mottovote/index.js
index 4806e55..e06f23c 100644
--- a/mottovote/index.js
+++ b/mottovote/index.js
@@ -4,18 +4,27 @@ const { checkUser } = require("../auth");
 const app = express.Router();
 
 
-app.use("/", express.static(__dirname + "/public/"));
+app.use("/", checkUser, express.static(__dirname + "/public/"));
 
-app.get("/api/list", async (req, res) => {
-    const mottos = await db.query("SELECT id, name, description FROM motto_votes ORDER BY name, description");
+app.get("/api/list", checkUser, async (req, res) => {
+    const mottos = await db.query("SELECT id, name, description FROM mottos ORDER BY name, description");
     res.json(mottos);
 });
 
-app.put("/api/vote", async (req, res) => {
-    for (const mid in req.body) {
-        await db.query("UPDATE motto_votes SET votes = votes + ? WHERE id = ?", [req.body[mid], mid]);
+app.put("/api/vote", checkUser, async (req, res) => {
+    await db.query("DELETE FROM motto_votes WHERE user_id = ?", [req.session.uid]);
+    try {
+        for (const mid in req.body) {
+            await db.query(
+                "INSERT INTO motto_votes (user_id, motto_id, votes) VALUES (?, ?, ?)",
+                [req.session.uid, mid, req.body[mid]]
+            );
+        }
+        res.send("ok");
+    } catch (e) {
+        console.error(e);
+        res.send("error");
     }
-    res.send("ok");
 });
 
 module.exports = app;
\ No newline at end of file
diff --git a/mottovote/public/script.js b/mottovote/public/script.js
index 7216af4..9fada2e 100644
--- a/mottovote/public/script.js
+++ b/mottovote/public/script.js
@@ -1,3 +1,4 @@
+const maxVotes = 3;
 get();
 
 async function get() {
@@ -7,17 +8,18 @@ async function get() {
     for (const motto of mottos) {
         const row = document.createElement("div");
         const id = motto.id;
-
-        const cb = document.createElement("input");
-        cb.type = "checkbox";
-        cb.id = "motto" + id;
-        cb.name = id;
-
-        const label = document.createElement("label");
-        label.for = "motto" + id;
-        label.textContent = `${motto.name} ${motto.description ? "-" : ""} ${motto.description}`
-
-        row.append(cb, label);
+        
+        for (let i = 0; i < maxVotes; i++) {
+            const cb = document.createElement("input");
+            cb.type = "checkbox";
+            cb.name = id;
+            row.append(cb);
+        }
+        
+        const text = document.createElement("span");
+        text.textContent = `${motto.name} ${motto.description ? "-" : ""} ${motto.description}`
+
+        row.append(text);
         window.vote.appendChild(row);
     }
     addListeners();
@@ -29,15 +31,15 @@ function addListeners() {
     boxes.forEach((box) => {
         box.addEventListener("change", (evt) => {
             const checkedSiblings = document.querySelectorAll("input[type=checkbox]:checked");
-            if (checkedSiblings.length > 3) evt.target.checked = false;
+            if (checkedSiblings.length > maxVotes) evt.target.checked = false;
         });
     });
 
     window.voteButton.addEventListener("click", async () => {
         const checked = document.querySelectorAll("input[type=checkbox]:checked");
-        if (checked.length !== 3) return;
+        if (checked.length > maxVotes) return; // Shouldn't be necessary
         const req = {};
-        for (const box of checked) req[box.name] = 1; // Amount of votes
+        for (const box of checked) req[box.name] = box.name in req ? req[box.name] + 1 : 1; // Amount of votes
         const resp = await fetch("api/vote", {
             method: "PUT",
             headers: { "Content-Type": "application/json" },
diff --git a/tables.sql b/tables.sql
index ea86339..e630058 100644
--- a/tables.sql
+++ b/tables.sql
@@ -10,6 +10,7 @@ CREATE TABLE IF NOT EXISTS theme(
 
 -- TODO: Remove dropping
 -- DROP TABLE IF EXISTS motto_votes;
+-- DROP TABLE IF EXISTS mottos;
 -- DROP TABLE IF EXISTS quotes;
 -- DROP TABLE IF EXISTS ranking_questions;
 -- DROP TABLE IF EXISTS ranking_answers;
@@ -75,9 +76,21 @@ CREATE TABLE IF NOT EXISTS ranking_answers(
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
-CREATE TABLE IF NOT EXISTS motto_votes(
+CREATE TABLE IF NOT EXISTS mottos(
     id INTEGER PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(255) NOT NULL,
     description VARCHAR(255) NOT NULL DEFAULT '',
-    votes INTEGER DEFAULT 0
+    
+    UNIQUE KEY main (name, description)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS motto_votes(
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    user_id INTEGER NOT NULL,
+    motto_id INTEGER NOT NULL,
+    votes SMALLINT UNSIGNED NOT NULL DEFAULT 0,
+
+    UNIQUE KEY uk_vote (user_id, motto_id),
+    CONSTRAINT `fk_voted_user` FOREIGN KEY (user_id) REFERENCES users (id),
+    CONSTRAINT `fk_voted_vote` FOREIGN KEY (motto_id) REFERENCES mottos (id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 
cgit v1.2.3

