1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
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 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()
|
