New motto

11 files changed, 148 insertions, 99 deletions

diff --git a/motto/.eslintrc.js b/.eslintrc.js
index 4d11b96..4d11b96 100644
--- a/motto/.eslintrc.js
+++ b/.eslintrc.js
diff --git a/motto/.gitignore b/.gitignore
index 89b0631..b16b65a 100644
--- a/motto/.gitignore
+++ b/.gitignore
@@ -1,3 +1,4 @@
 *.db
 *lock*
 node_*
+*.env*
diff --git a/.prettierrc.js b/.prettierrc.js
new file mode 100644
index 0000000..222cb77
--- /dev/null
+++ b/.prettierrc.js
@@ -0,0 +1,9 @@
+module.exports = {
+    semi: true,
+    trailingComma: "all",
+    singleQuote: false,
+    printWidth: 120,
+    tabWidth: 4,
+    useTabs: false,
+    endOfLine: "auto"
+}
diff --git a/app.js b/app.js
new file mode 100644
index 0000000..0bbd1f3
--- /dev/null
+++ b/app.js
@@ -0,0 +1,13 @@
+require("dotenv").config();
+const express = require("express");
+
+const motto = require("./motto");
+
+const app = express();
+
+app.use(express.urlencoded({ extended: true }));
+app.use(express.json());
+
+app.use("/motto", motto);
+
+app.listen(5005, () => console.log("Server started on http://localhost:5005"));
diff --git a/db.js b/db.js
new file mode 100644
index 0000000..244e9a6
--- /dev/null
+++ b/db.js
@@ -0,0 +1,42 @@
+const mariadb = require("mariadb");
+const fs = require("fs");
+
+class DB {
+    constructor() {
+        this.pool = mariadb.createPool({
+            host: process.env.DBHost,
+            user: process.env.DBUser,
+            password: process.env.DBPassword,
+            database: process.env.DBName,
+        });
+        this.init();
+    }
+
+    connect() {
+        return this.pool.getConnection();
+    }
+
+    init() {
+        fs.readFile(__dirname + "/tables.sql", "utf8", async (err, data) => {
+            if (err) throw err;
+            const queries = data.split(";");
+            queries.pop();
+            const conn = await this.connect();
+            for (const query of queries) await conn.query(query);
+            console.log("Tables created!");
+        });
+    }
+
+    async query(query, params) {
+        const conn = await this.connect();
+        try {
+            return await conn.query(query, params);
+        } catch (e) {
+            throw e;
+        } finally {
+            conn.release();
+        }
+    }
+}
+
+module.exports = new DB();
diff --git a/motto/index.js b/motto/index.js
index ec14a33..26d70bd 100644
--- a/motto/index.js
+++ b/motto/index.js
@@ -1,8 +1,9 @@
 const express = require("express");
-const sqlite3 = require("sqlite3");
 const rateLimit = require("express-rate-limit");
-const app = express();
-// const fs = require("fs");
+const db = require("../db");
+const app = express.Router();
+
+const fs = require("fs");
 
 const apiLimiter = rateLimit({
     windowMs: 10 * 60 * 1000,
@@ -10,95 +11,68 @@ const apiLimiter = rateLimit({
     message: "Access overflow!",
 });
 
-app.use(express.urlencoded({ extended: true }));
-app.use(express.json());
-
-let db;
-function create_db() {
-    if (db) db.close();
-
-    db = new sqlite3.Database("db.db", sqlite3.OPEN_READWRITE, (err) => {
-        if (err) console.error(err.message);
-        console.log("Connected to the database");
-    });
-
-    db.run(
-        "CREATE TABLE IF NOT EXISTS theme(id INTEGER PRIMARY KEY AUTOINCREMENT, main TEXT NOT NULL, description TEXT NOT NULL, votes INTEGER DEFAULT 0, hidden BOOL DEFAULT 0 UNIQUE(main, description))",
-        (err) => {
-            if (err) console.error(err.message);
-        }
-    );
-}
-
-function insert(main, description, votes) {
-    db.run(`INSERT INTO theme(main, description, votes) VALUES(?, ?, ?)`, [main, description, votes], (err) => {
+app.get("/sync", (req, res) => {
+    fs.readFile(__dirname + "/list.txt", "utf8", (err, data) => {
         if (err) {
-            console.error(err.message);
-            return;
+            console.error(err);
+            return res.send("error");
         }
+        const lines = data.split("\n");
+        lines.forEach(async (line) => {
+            const split = line.split(" - ");
+            try {
+                if (split.length >= 2)
+                    await db.query("INSERT INTO theme (main, description) VALUES (?, ?)", split.slice(0, 2));
+                else console.log(line);
+            } catch (e) {
+                console.error(e);
+            } finally {
+                res.send("ok");
+            }
+        });
     });
-}
-
-// app.get("/sync", (req, res) => {
-//     fs.unlinkSync("db.db");
-//     fs.closeSync(fs.openSync("db.db", "w"));
-//     create_db();
-//     fs.readFile("list.txt", "utf8", (err, data) => {
-//         if (err) return;
-//         const lines = data.split("\n");
-//         lines.forEach((line) => {
-//             const split = line.split(" - ");
-//             if (split[0] && split[1]) insert(split[0], split[1], 0);
-//             else console.log(line);
-//         });
-//     });
-//     res.send("ok");
-// });
+});
 
 app.use("/", express.static(__dirname + "/public"));
 
 app.use("/api/", apiLimiter);
 
-app.get("/api/list", (req, res) => {
-    db.all("SELECT * FROM theme WHERE hidden = 0 ORDER BY votes DESC", (err, all) => {
-        if (err) {
-            res.send("error");
-            console.error(err.message);
-            return;
-        }
-        res.send(all);
-    });
+app.get("/api/list", async (req, res) => {
+    try {
+        const themes = await db.query("SELECT * FROM theme" /* WHERE hidden = FALSE ORDER BY votes DESC"*/);
+        res.json(themes);
+    } catch (e) {
+        console.error(e);
+        res.send("error");
+    }
 });
 
-app.post("/api/add", (req, res) => {
+app.post("/api/add", async (req, res) => {
     console.log(req.body.main, req.body.description);
-    if (!req.body.main || !req.body.description) res.send("error");
-    insert(req.body.main, req.body.description, 1);
-    res.send("ok");
+    if (!req.body.main || !req.body.description) return res.send("error");
+    try {
+        await db.query("INSERT INTO theme (main, description) VALUES (?, ?)", [req.body.main, req.body.description]);
+        res.send("ok");
+    } catch (e) {
+        console.error(e);
+        res.send("error");
+    }
 });
 
-app.post("/api/vote", (req, res) => {
+app.post("/api/vote", async (req, res) => {
     console.log(req.body.id, req.body.vote);
     if (req.body.vote < -1 || req.body.vote > 1) {
         res.send("error");
         return;
     }
 
-    db.all("UPDATE theme SET votes = votes + ? WHERE id = ?", [req.body.vote, req.body.id], (err) => {
-        if (err) {
-            res.send("error");
-            console.error(err.message);
-            return;
-        }
-    });
-    res.send("ok");
-});
-
-app.on("close", () => {
-    console.log("CLOSE");
-    db.close();
+    try {
+        await db.query("UPDATE theme SET votes = votes + ? WHERE id = ?", [req.body.vote, req.body.id]);
+        res.send("ok");
+    } catch (e) {
+        console.error(e);
+        res.send("error");
+    }
 });
 
-create_db();
-console.log("Listening on port 5005");
-app.listen(5005);
+module.exports = app;
diff --git a/motto/package.json b/motto/package.json
deleted file mode 100644
index 01f2bc6..0000000
--- a/motto/package.json
+++ /dev/null
@@ -1,19 +0,0 @@
-{
-  "name": "motto",
-  "version": "1.0.0",
-  "description": "",
-  "main": "index.js",
-  "scripts": {
-    "test": "echo \"Error: no test specified\" && exit 1"
-  },
-  "author": "",
-  "license": "MIT",
-  "dependencies": {
-    "express": "^4.17.1",
-    "express-rate-limit": "^5.1.3",
-    "sqlite3": "^5.0.0"
-  },
-  "devDependencies": {
-    "eslint": "^7.8.0"
-  }
-}
diff --git a/motto/public/script.js b/motto/public/script.js
index 7b27ce0..a4e75e9 100644
--- a/motto/public/script.js
+++ b/motto/public/script.js
@@ -4,7 +4,7 @@ document.querySelector(".wrapper").style.opacity = 1;
 
 function send(id, vote) {
     var xhp = new XMLHttpRequest();
-    xhp.open("POST", "/api/vote");
+    xhp.open("POST", "api/vote");
     xhp.setRequestHeader("Content-type", "application/json");
     xhp.onreadystatechange = () => {
         if (xhp.readyState == 4 && xhp.status == 200) console.log(xhp.responseText);
@@ -16,7 +16,7 @@ function send(id, vote) {
 
 function add(main, description) {
     var xhp = new XMLHttpRequest();
-    xhp.open("POST", "/api/add");
+    xhp.open("POST", "api/add");
     xhp.setRequestHeader("Content-type", "application/json");
     xhp.onreadystatechange = () => {
         if (xhp.readyState == 4 && xhp.status == 200) console.log(xhp.responseText);
@@ -141,7 +141,7 @@ function createButtonListener(yay) {
 }
 
 var xhp = new XMLHttpRequest();
-xhp.open("GET", "/api/list");
+xhp.open("GET", "api/list");
 xhp.onreadystatechange = () => {
     if (xhp.readyState == 4 && xhp.status == 200) {
         let list = JSON.parse(xhp.responseText);
@@ -207,6 +207,10 @@ document.getElementById("aah").addEventListener("click", () => {
 document.getElementById("all").addEventListener("click", toggleOverview);
 document.getElementById("go").addEventListener("click", toggleOverview);
 
-addEventListener("load", () => {
-    window.scrollTo(1, 0);
-}, false);
+addEventListener(
+    "load",
+    () => {
+        window.scrollTo(1, 0);
+    },
+    false,
+);
diff --git a/motto/public/style.css b/motto/public/style.css
index 143f2db..22532da 100644
--- a/motto/public/style.css
+++ b/motto/public/style.css
@@ -124,6 +124,10 @@ body {
     vertical-align: middle;
 }
 
+.top_button img {
+    cursor: pointer;
+}
+
 @media only screen and (max-width: 600px) {
     .fab button {
         margin: 10px;
diff --git a/package.json b/package.json
new file mode 100644
index 0000000..b6d8bdd
--- /dev/null
+++ b/package.json
@@ -0,0 +1,14 @@
+{
+  "name": "abizeugs",
+  "version": "1.0.0",
+  "main": "app.js",
+  "repository": "git@github.com:marvinborner/abizeugs",
+  "author": "LarsVomMars <lars@kroenner.eu>",
+  "license": "MIT",
+  "dependencies": {
+    "dotenv": "^8.2.0",
+    "express": "^4.17.1",
+    "express-rate-limit": "^5.1.3",
+    "mariadb": "^2.4.2"
+  }
+}
diff --git a/tables.sql b/tables.sql
new file mode 100644
index 0000000..a90c8a9
--- /dev/null
+++ b/tables.sql
@@ -0,0 +1,7 @@
+CREATE TABLE IF NOT EXISTS theme(
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    main TEXT NOT NULL UNIQUE,
+    description TEXT NOT NULL UNIQUE,
+    votes INTEGER DEFAULT FALSE,
+    hidden BOOLEAN DEFAULT FALSE
+);