diff options
Diffstat (limited to 'db_actions.py')
| -rw-r--r-- | db_actions.py | 184 |
1 files changed, 0 insertions, 184 deletions
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() - |