diff options
Diffstat (limited to 'db/db.py')
| -rw-r--r-- | db/db.py | 113 |
1 files changed, 7 insertions, 106 deletions
@@ -6,117 +6,18 @@ from app import app from db.user import DBUser from db.quiz import DBQuiz +from db.question import DBQuestion +from db.answer import DBAnswer +from db.analyse import DBAnalyse class DB: def __init__(self): self.db = SQLAlchemy() self.db.init_app(app) + self.user = DBUser(self.db) self.quiz = DBQuiz(self.db) - - def question_new(self, question, neg_ans, pos_ans ): - sql = "INSERT \ - INTO questions (question, neg_answer, pos_answer) \ - VALUES (:question, :neg_answer, :pos_answer) \ - RETURNING id ;" - result = self.db.session.execute( - text(sql), { - "question":question, - "neg_answer":neg_ans, - "pos_answer":pos_ans } - ) - self.db.session.commit() - return result.fetchone()[0] - - - def answer_new(self, user_id, question_id, answer): - sql = "INSERT \ - INTO answers (user_id, question_id, answer ) \ - VALUES (:user_id, :question_id, :answer);" - self.db.session.execute( text(sql), { - "user_id":user_id, - "question_id":question_id, - "answer":answer - } ) - 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() - + self.question = DBQuestion(self.db) + self.answer = DBAnswer(self.db) + self.analyse = DBAnalyse(self.db)
\ No newline at end of file |