Move DB stuff inside a class for further refactoring.

11 files changed, 207 insertions, 198 deletions

diff --git a/app.py b/app.py
index 3d8d5c7..72becc3 100644
--- a/app.py
+++ b/app.py
@@ -1,7 +1,7 @@
 from secrets import token_hex
 from flask import Flask
 from os import getenv
-from db_actions import db
+#from db_actions import db
 
 app = Flask(__name__, static_url_path='')
 
@@ -15,7 +15,10 @@ if s_key := getenv("SECRET_KEY"):
 else:
     app.secret_key = token_hex()
     
-db.init_app(app)
+from db.db import DB
+D = DB()
+    
+#db.init_app(app)
 
 import routes.base
 import routes.answer
diff --git a/db/db.py b/db/db.py
new file mode 100644
index 0000000..ad6f3ac
--- /dev/null
+++ b/db/db.py
@@ -0,0 +1,190 @@
+from time import time
+
+from flask import Flask
+from flask_sqlalchemy import SQLAlchemy
+from sqlalchemy.sql import text
+
+from app import app
+
+class DB:
+    def __init__(self):
+        self.db = SQLAlchemy()
+        self.db.init_app(app)
+        
+    def user_new(self, nick):
+        sql = "INSERT \
+                INTO users (nick, created) \
+                VALUES (:nick, :created) \
+                RETURNING id ;"
+        result = self.db.session.execute(
+                text(sql), { "nick":nick, "created":int(time()) }
+            )
+        self.db.session.commit()
+        return result.fetchone()[0]
+
+    def user_get_nick(self, id):
+        sql = "SELECT nick \
+                FROM users \
+                WHERE id=(:id);"
+        result = self.db.session.execute(text(sql), { "id":id }).fetchone()
+        return result[0] if result else result
+
+    def user_exists(self, nick):
+        sql = "SELECT COUNT(id) \
+                FROM users \
+                WHERE nick=(:nick);"
+        return self.db.session.execute(text(sql), { "nick":nick }).scalar()
+
+    def question_new(self, question, neg_ans, pos_ans ):
+        sql = "INSERT \
+                INTO questions (question, neg_answer, pos_answer, created) \
+                VALUES (:question, :neg_answer, :pos_answer, :created) \
+                RETURNING id ;"
+        result = self.db.session.execute(
+                text(sql), { 
+                        "question":question, 
+                        "neg_answer":neg_ans,
+                        "pos_answer":pos_ans,
+                        "created":int(time()) }
+            )
+        self.db.session.commit()
+        return result.fetchone()[0]
+
+    def quiz_new(self, user_id):
+        sql = "INSERT \
+                INTO questionaires (creator_id, created) \
+                VALUES (:creator_id, :created) \
+                RETURNING id ;"
+        result = self.db.session.execute( text(sql),
+            { "creator_id":user_id, "created":int(time()) } )
+        self.db.session.commit()
+        return result.fetchone()[0]
+
+    def quiz_add(self, quiz_id, question_id ):
+        sql = "UPDATE questionaires \
+                SET questionset = ARRAY_APPEND(questionset, :question_id) \
+                WHERE id=:quiz_id;"
+        self.db.session.execute(text(sql), {
+                "quiz_id":quiz_id,
+                "question_id":question_id
+            })
+        self.db.session.commit()
+    
+
+    def set_quiz_link( self, quiz_id, link ):
+        sql = "INSERT \
+                INTO quiz_links (quiz_id, link, created) \
+                VALUES (:quiz_id, :link, :created)"
+        result = self.db.session.execute( text(sql),
+            {  "quiz_id":quiz_id, "link":link, "created":int(time()) } )
+        self.db.session.commit()
+
+
+    def find_quiz_by_link( self, link ):
+        sql = "SELECT quiz_id \
+                FROM quiz_links \
+                WHERE link=:link;"
+        result = self.db.session.execute(text(sql), { "link":link }).fetchone()
+        return result[0] if result else False
+
+
+    def get_quiz_link( self, quiz_id ):
+        sql = "SELECT link \
+                FROM quiz_links \
+                WHERE quiz_id=:quiz_id;"
+        result = self.db.session.execute(text(sql), { "quiz_id":quiz_id }).fetchone()
+        return result[0] if result else result
+
+    
+    def answer_new(self, user_id, question_id, answer):
+        sql = "INSERT \
+                INTO answers (user_id, question_id,	answer,	created) \
+                VALUES (:user_id, :question_id, :answer, :created);"
+        self.db.session.execute( text(sql), {
+                "user_id":user_id,
+                "question_id":question_id, 
+                "answer":answer,
+                "created":int(time())
+            } )
+        self.db.session.commit()
+
+
+    def get_questions(self, quiz_id):
+        sql = "SELECT q.id, q.question, q.neg_answer, q.pos_answer, a.answer \
+                FROM questionaires quiz \
+                JOIN questions q ON q.id = ANY(quiz.questionset) \
+                JOIN answers a ON a.user_id = quiz.creator_id \
+                WHERE a.question_id = q.id AND quiz.id = (:quiz_id);"
+        return self.db.session.execute( text(sql), { "quiz_id":quiz_id } ).fetchall()
+
+    def get_comparable(self, quiz_id,user1,user2):
+        sql = "SELECT q.question, q.neg_answer, q.pos_answer, \
+                a1.answer, a2.answer, \
+                100 - ABS( a1.answer - a2.answer ) / 10 \
+                FROM questionaires quiz \
+                JOIN questions q ON q.id = ANY(quiz.questionset) \
+                JOIN answers a1 ON a1.user_id = (:user1) \
+                JOIN answers a2 ON a2.user_id = (:user2) \
+                WHERE a1.question_id = q.id \
+                AND a2.question_id = q.id \
+                AND quiz.id = (:quiz_id);"
+        return self.db.session.execute( text(sql), {
+                "quiz_id":quiz_id,
+                "user1":user1,
+                "user2":user2
+            } ).fetchall()
+
+
+    def get_user_answer(self, user_id, question_id):
+        sql = "SELECT answer \
+            FROM answers \
+            WHERE question_id = (:question_id) AND user_id = (:user_id);"
+        result = self.db.session.execute( text(sql), { 
+                'question_id': question_id,
+                'user_id': user_id 
+                } ).fetchone()
+        return result[0] if result else -1
+
+
+    def get_user_answers_for_quiz(self, quiz_id, user_id):
+        sql = "SELECT a.question_id, a.answer \
+                FROM questionaires quiz \
+                JOIN answers a ON a.question_id = ANY(quiz.questionset) \
+                WHERE a.user_id = (:user_id) AND quiz.id = (:quiz_id);"
+        return self.db.session.execute( text(sql), { 
+                'user_id': user_id,
+                'quiz_id': quiz_id
+                } ).fetchall()
+
+    def get_users_answered(self, quiz_id):
+        sql = "SELECT DISTINCT a.user_id, u.nick \
+                FROM questionaires quiz \
+                JOIN answers a ON a.question_id = quiz.questionset[1] \
+                JOIN users u ON u.id = a.user_id \
+                WHERE quiz.id = (:quiz_id);"
+        return self.db.session.execute( text(sql), { 
+                'quiz_id': quiz_id
+                } ).fetchall()
+
+    def is_user_answered(self, quiz_id, user_id):
+        sql = "SELECT a.answer \
+                FROM questionaires quiz \
+                JOIN answers a ON a.question_id = quiz.questionset[1] \
+                WHERE quiz.id = (:quiz_id) AND a.user_id = (:user_id);"
+        results = self.db.session.execute( text(sql), { 
+                'quiz_id': quiz_id,
+                'user_id': user_id
+                } ).fetchone()
+        return True if results else False
+
+
+    def get_all_answers_for_quiz(self, quiz_id):
+        sql = "SELECT a.question_id, a.user_id, a.answer \
+                FROM questionaires quiz \
+                JOIN answers a ON a.question_id = ANY(quiz.questionset) \
+                WHERE quiz.id = (:quiz_id);"
+        return self.db.session.execute( text(sql), { 
+                'quiz_id': quiz_id
+                } ).fetchall()
+
+    
\ No newline at end of file
diff --git a/db_actions.py b/db_actions.py
deleted file mode 100644
index 993ef46..0000000
--- a/db_actions.py
+++ /dev/null
@@ -1,184 +0,0 @@
-from time import time
-
-from flask import Flask
-from flask_sqlalchemy import SQLAlchemy
-from sqlalchemy.sql import text
-
-db = SQLAlchemy()
-
-def user_new(nick):
-    sql = "INSERT \
-            INTO users (nick, created) \
-            VALUES (:nick, :created) \
-            RETURNING id ;"
-    result = db.session.execute(
-            text(sql), { "nick":nick, "created":int(time()) }
-        )
-    db.session.commit()
-    return result.fetchone()[0]
-
-def user_get_nick(id):
-    sql = "SELECT nick \
-            FROM users \
-            WHERE id=(:id);"
-    result = db.session.execute(text(sql), { "id":id }).fetchone()
-    return result[0] if result else result
-
-def user_exists(nick):
-    sql = "SELECT COUNT(id) \
-            FROM users \
-            WHERE nick=(:nick);"
-    return db.session.execute(text(sql), { "nick":nick }).scalar()
-
-def question_new( question, neg_ans, pos_ans ):
-    sql = "INSERT \
-            INTO questions (question, neg_answer, pos_answer, created) \
-            VALUES (:question, :neg_answer, :pos_answer, :created) \
-            RETURNING id ;"
-    result = db.session.execute(
-            text(sql), { 
-                    "question":question, 
-                    "neg_answer":neg_ans,
-                    "pos_answer":pos_ans,
-                    "created":int(time()) }
-        )
-    db.session.commit()
-    return result.fetchone()[0]
-
-def quiz_new(user_id):
-    sql = "INSERT \
-            INTO questionaires (creator_id, created) \
-            VALUES (:creator_id, :created) \
-            RETURNING id ;"
-    result = db.session.execute( text(sql),
-        { "creator_id":user_id, "created":int(time()) } )
-    db.session.commit()
-    return result.fetchone()[0]
-
-def quiz_add( quiz_id, question_id ):
-    sql = "UPDATE questionaires \
-            SET questionset = ARRAY_APPEND(questionset, :question_id) \
-            WHERE id=:quiz_id;"
-    db.session.execute(text(sql), {
-            "quiz_id":quiz_id,
-            "question_id":question_id
-        })
-    db.session.commit()
-    
-
-def set_quiz_link( quiz_id, link ):
-    sql = "INSERT \
-            INTO quiz_links (quiz_id, link, created) \
-            VALUES (:quiz_id, :link, :created)"
-    result = db.session.execute( text(sql),
-        {  "quiz_id":quiz_id, "link":link, "created":int(time()) } )
-    db.session.commit()
-    
-
-def find_quiz_by_link( link ):
-    sql = "SELECT quiz_id \
-            FROM quiz_links \
-            WHERE link=:link;"
-    result = db.session.execute(text(sql), { "link":link }).fetchone()
-    return result[0] if result else False
-
-
-def get_quiz_link( quiz_id ):
-    sql = "SELECT link \
-            FROM quiz_links \
-            WHERE quiz_id=:quiz_id;"
-    result = db.session.execute(text(sql), { "quiz_id":quiz_id }).fetchone()
-    return result[0] if result else result
-
-    
-def answer_new(user_id, question_id, answer):
-    sql = "INSERT \
-            INTO answers (user_id, question_id,	answer,	created) \
-            VALUES (:user_id, :question_id, :answer, :created);"
-    db.session.execute( text(sql), {
-            "user_id":user_id,
-            "question_id":question_id, 
-            "answer":answer,
-            "created":int(time())
-        } )
-    db.session.commit()
-
-
-def get_questions(quiz_id):
-    sql = "SELECT q.id, q.question, q.neg_answer, q.pos_answer, a.answer \
-            FROM questionaires quiz \
-            JOIN questions q ON q.id = ANY(quiz.questionset) \
-            JOIN answers a ON a.user_id = quiz.creator_id \
-            WHERE a.question_id = q.id AND quiz.id = (:quiz_id);"
-    return db.session.execute( text(sql), { "quiz_id":quiz_id } ).fetchall()
-
-def get_comparable(quiz_id,user1,user2):
-    sql = "SELECT q.question, q.neg_answer, q.pos_answer, \
-            a1.answer, a2.answer, \
-            100 - ABS( a1.answer - a2.answer ) / 10 \
-            FROM questionaires quiz \
-            JOIN questions q ON q.id = ANY(quiz.questionset) \
-            JOIN answers a1 ON a1.user_id = (:user1) \
-            JOIN answers a2 ON a2.user_id = (:user2) \
-            WHERE a1.question_id = q.id \
-            AND a2.question_id = q.id \
-            AND quiz.id = (:quiz_id);"
-    return db.session.execute( text(sql), {
-            "quiz_id":quiz_id,
-            "user1":user1,
-            "user2":user2
-        } ).fetchall()
-
-
-def get_user_answer(user_id, question_id):
-    sql = "SELECT answer \
-        FROM answers \
-        WHERE question_id = (:question_id) AND user_id = (:user_id);"
-    result = db.session.execute( text(sql), { 
-            'question_id': question_id,
-            'user_id': user_id 
-            } ).fetchone()
-    return result[0] if result else -1
-
-
-def get_user_answers_for_quiz(quiz_id, user_id):
-    sql = "SELECT a.question_id, a.answer \
-            FROM questionaires quiz \
-            JOIN answers a ON a.question_id = ANY(quiz.questionset) \
-            WHERE a.user_id = (:user_id) AND quiz.id = (:quiz_id);"
-    return db.session.execute( text(sql), { 
-            'user_id': user_id,
-            'quiz_id': quiz_id
-            } ).fetchall()
-
-def get_users_answered(quiz_id):
-    sql = "SELECT DISTINCT a.user_id, u.nick \
-            FROM questionaires quiz \
-            JOIN answers a ON a.question_id = quiz.questionset[1] \
-            JOIN users u ON u.id = a.user_id \
-            WHERE quiz.id = (:quiz_id);"
-    return db.session.execute( text(sql), { 
-            'quiz_id': quiz_id
-            } ).fetchall()
-
-def is_user_answered(quiz_id, user_id):
-    sql = "SELECT a.answer \
-            FROM questionaires quiz \
-            JOIN answers a ON a.question_id = quiz.questionset[1] \
-            WHERE quiz.id = (:quiz_id) AND a.user_id = (:user_id);"
-    results = db.session.execute( text(sql), { 
-            'quiz_id': quiz_id,
-            'user_id': user_id
-            } ).fetchone()
-    return True if results else False
-
-
-def get_all_answers_for_quiz(quiz_id):
-    sql = "SELECT a.question_id, a.user_id, a.answer \
-            FROM questionaires quiz \
-            JOIN answers a ON a.question_id = ANY(quiz.questionset) \
-            WHERE quiz.id = (:quiz_id);"
-    return db.session.execute( text(sql), { 
-            'quiz_id': quiz_id
-            } ).fetchall()
-
diff --git a/routes/analyse.py b/routes/analyse.py
index 3b59ae7..8175d47 100644
--- a/routes/analyse.py
+++ b/routes/analyse.py
@@ -1,7 +1,6 @@
 from itertools import combinations
-from app import app
+from app import app, D
 from flask import render_template,session,request,redirect
-import db_actions as D
 from routes.tools import rows2dicts, get_alert, get_nick, csrf_check
 
 def find_best_and_worst(aid, uid):
diff --git a/routes/answer.py b/routes/answer.py
index e224b44..89b6aa8 100644
--- a/routes/answer.py
+++ b/routes/answer.py
@@ -1,6 +1,5 @@
-from app import app
+from app import app, D
 from flask import render_template, session, request, redirect
-import db_actions as D
 from routes.tools import rows2dicts, get_alert, get_nick, csrf_check
 
 
diff --git a/routes/base.py b/routes/base.py
index 2c4b1f2..d5eae8f 100644
--- a/routes/base.py
+++ b/routes/base.py
@@ -1,7 +1,6 @@
 from secrets import token_urlsafe
-from app import app
+from app import app, D
 from flask import render_template,session,request,redirect
-import db_actions as D
 from routes.tools import rows2dicts, get_alert, get_nick, csrf_check
 
 @app.route("/")
diff --git a/routes/create.py b/routes/create.py
index 2de8e27..af2e25e 100644
--- a/routes/create.py
+++ b/routes/create.py
@@ -1,6 +1,5 @@
-from app import app
+from app import app, D
 from flask import render_template,session,request,redirect
-import db_actions as D
 from routes.tools import rows2dicts, get_alert, get_nick, generate_link, csrf_check
 
 
diff --git a/routes/question.py b/routes/question.py
index 42fce8d..41722d5 100644
--- a/routes/question.py
+++ b/routes/question.py
@@ -1,6 +1,5 @@
-from app import app
+from app import app, D
 from flask import render_template,session,request,redirect
-import db_actions as D
 from routes.tools import rows2dicts, get_alert, get_nick, csrf_check
 
 @app.route("/pages/question.html")
diff --git a/routes/tools.py b/routes/tools.py
index c55ca74..5f6e141 100644
--- a/routes/tools.py
+++ b/routes/tools.py
@@ -1,6 +1,6 @@
 from random import randint
 from flask import session, request
-import db_actions as D
+from app import D
 
 def rows2dicts( rows, names ):
     dlist=[]
diff --git a/static/index.html b/static/index.html
index 6c7917b..4a3020d 100644
--- a/static/index.html
+++ b/static/index.html
@@ -16,10 +16,10 @@
 <H1>Kyselmä</H1>
 <a href="pages/info.html">Pääsivu</a><br>
 <a href="pages/create.html">Luo kysely</a><br>
+<a href="pages/question.html">Lisää kysymys kyselyyn</a><br>
 <a href="pages/answer.html">Vastaa kyselyyn</a><br>
 <a href="pages/analyse.html">Tutki vastauksia</a><br>
 <a href="pages/moderate.html">Moderoi sivustoa</a><br>
-<a href="pages/nick.html">Anna nimimerkki</a><br>
 <hr>
 Tämä sivu tarvitsee JavaScriptin toimiakseen. Voit kumminkin yrittää käyttää
 sivua ylläolevien linkkien kautta.
diff --git a/templates/question.html b/templates/question.html
index 76cba79..451fb32 100644
--- a/templates/question.html
+++ b/templates/question.html
@@ -1,4 +1,7 @@
 {% include 'base.html' %}
+
+{% if nick %}
+
 <form action="/set/question" method="POST">
 <div class="kysQuestion">
 <div class="kysCreateText">Kysymys:</div>
@@ -16,3 +19,5 @@
 <input type="submit" value="Lisää kysymys">
 </div>
 </form>
+
+{% endif %}