def get_user_activities(cursor:RealDictCursor, user_id: int) -> list:
id = {'user_id': user_id}
question_query = """
SELECT title AS question, id AS question_id
FROM question
WHERE question.user_id = %(user_id)s"""
answer_query = """
SELECT message AS answer, question_id
FROM answer
WHERE answer.user_id = %(user_id)s"""
comment_query = """
SELECT message AS comment,
CASE
WHEN question_id IS NULL THEN
(SELECT question_id
FROM answer
WHERE id = comment.answer_id)
WHEN answer_id IS NULL THEN question_id
END AS question_id
FROM comment
WHERE comment.user_id = %(user_id)s"""
cursor.execute(question_query, id)
question = cursor.fetchall()
cursor.execute(answer_query, id)
answer = cursor.fetchall()
cursor.execute(comment_query, id)
comment = cursor.fetchall()
return question, answer, comment
def get_comments(cursor: RealDictCursor) -> list:
query = """
SELECT *
FROM comment
ORDER BY submission_time"""
cursor.execute(query)
return cursor.fetchall()
def get_emails(cursor:RealDictCursor):
query = """
SELECT users.email
FROM users;
"""
cursor.execute(query)
return cursor.fetchall()
def get_max_tag_id(cursor: RealDictCursor):
query = """
SELECT MAX(id)
FROM tag
"""
cursor.execute(query)
[max_value] = cursor.fetchall()
return max_value['max']
def get_views(cursor: RealDictCursor, id):
query = """
SELECT view_number
FROM question
WHERE id=%(id)s"""
cursor.execute(query, {'id': id})
[view_num] = cursor.fetchall()
return int(view_num['view_number'])
def get_question_tags(cursor: RealDictCursor, id) ->list:
query = """
SELECT A.*
FROM tag A
WHERE A.id IN (SELECT B.tag_id from question_tag B WHERE B.question_id = %(input)s)
"""
cursor.execute(query, {'input':id})
return cursor.fetchall()
def get_answer_user_id(cursor: RealDictCursor, answer_id: int):
query = """
SELECT user_id
FROM answer
WHERE id = %(answer_id)s"""
cursor.execute(query, {'answer_id': answer_id})
[result] = cursor.fetchall()
return result['user_id']
def get_comment_by_id(cursor: RealDictCursor, comment_id: int):
query = """
SELECT *
FROM comment
WHERE id = %(comment_id)s;"""
cursor.execute(query, {'comment_id': comment_id})
[data] = cursor.fetchall()
return data
def get_question_id_by_answer(cursor: RealDictCursor, answer_id: int):
query = """
SELECT question_id
FROM answer
WHERE id = %(answer_id)s"""
cursor.execute(query, {'answer_id': answer_id})
[data] = cursor.fetchall()
return data['question_id']
def get_answer_data(cursor:RealDictCursor, email):
sql = """
SELECT * FROM answer
WHERE email = %(email)s
"""
cursor.execute(sql,{'email':email})
data = cursor.fetchall()
return data
def get_answer_message(cursor: RealDictCursor, search_phrase) -> list:
query = """
SELECT message
FROM answer
WHERE message ILIKE %(search_phrase)s
"""
cursor.execute(query, {'search_phrase': '%' + search_phrase + '%'})
return cursor.fetchall()
def get_latest_questions(cursor: RealDictCursor) -> list:
query = """
SELECT *
FROM question
ORDER BY submission_time DESC
LIMIT 5;"""
cursor.execute(query)
return cursor.fetchall()
def get_single_answer(cursor: RealDictCursor, id) -> list:
query = """
SELECT *
FROM answer
WHERE id=%(id)s"""
cursor.execute(query, {'id': id})
[data] = cursor.fetchall()
return data
def get_question_user_id(cursor: RealDictCursor, question_id: int):
query = """
SELECT user_id
FROM question
WHERE id = %(question_id)s"""
cursor.execute(query, {'question_id': question_id})
[result] = cursor.fetchall()
return result['user_id']
def get_question_vote_count(cursor: RealDictCursor, id):
query = """
SELECT vote_number
FROM question
WHERE id=%(id)s"""
cursor.execute(query, {'id': id})
[vote_num_data] = cursor.fetchall()
return vote_num_data
def get_answer_vote_count(cursor: RealDictCursor, id) -> list:
query = """
SELECT vote_number, question_id
FROM answer
WHERE id=%(id)s"""
cursor.execute(query, {'id': id})
[vote_num_data] = cursor.fetchall()
return vote_num_data
def get_list(sql, args):
conn = psycopg2.connect(**PG_SQL_LOCAL)
cursor = conn.cursor()
cursor.execute(sql, args)
result = cursor.fetchall()
cursor.close()
conn.close()
return result
def get_players_data(cursor, room_id):
query = """
SELECT name, points, is_drawer, word, max_round, round_counter, drawing_time, player.id AS player_id, avatar
FROM player
JOIN room ON player.room_id = room.id
WHERE room.id = %(room_id)s
"""
cursor.execute(query, {"room_id": room_id})
return cursor.fetchall()
def get_question_id_by_comment(cursor: RealDictCursor, comment_id):
query = """
SELECT question_id
FROM comment
WHERE id = %(comment_id)s;"""
cursor.execute(query, {'comment_id': comment_id})
[data] = cursor.fetchall()
question_id = data['question_id']
if question_id is None:
sec_query = """
SELECT answer_id
FROM comment
WHERE id = %(comment_id)s;"""
cursor.execute(sec_query, {'comment_id': comment_id})
[sec_data] = cursor.fetchall()
answer_id = sec_data['answer_id']
question_id = get_question_id_by_answer(answer_id)
return question_id
def get_tag_id(cursor: RealDictCursor, name):
query = """
SELECT id
FROM tag
WHERE name = %(name)s
"""
cursor.execute(query, {'name': name})
[tag_id] = cursor.fetchall()
return tag_id['id']
def fancy_search(cursor: RealDictCursor, input):
query = """
SELECT message, question_id
FROM answer
WHERE message ILIKE %(input)s
AND %(input)s NOT IN(SELECT title FROM question) AND %(input)s NOT IN(SELECT message FROM question)
"""
cursor.execute(query, {'input':'%' + input + '%'})
search_data = cursor.fetchall()
return search_data
def search(cursor: RealDictCursor, input):
query = """
SELECT A.*
FROM question A
WHERE title ILIKE %(input)s OR message ILIKE %(input)s
OR A.id IN (SELECT B.question_id from answer B WHERE message ILIKE %(input)s)
"""
cursor.execute(query, {'input': '%' + input + '%'})
search_data = cursor.fetchall()
return search_data
def get_answers(cursor: RealDictCursor, id) -> list:
query = """
SELECT
users.email user_email,
answer.*
FROM users
JOIN answer
ON users.id = answer.user_id
WHERE answer.question_id =%(id)s"""
cursor.execute(query, {'id': id})
answer = cursor.fetchall()
return answer
def tag_occurence(cursor: RealDictCursor):
query = """
SELECT
tag.name as tag_name,
COUNT(question_tag.question_id) as occurence
FROM tag
JOIN question_tag
ON tag.id = question_tag.tag_id
GROUP BY tag_name;
"""
cursor.execute(query)
return cursor.fetchall()
def get_existing_room(cursor):
query = '''
SELECT
room.id AS room_id,
player.name AS player_name,
player.id AS player_id
FROM room
JOIN player
ON room.id = player.room_id
ORDER BY player.id ASC
'''
cursor.execute(query)
return cursor.fetchall()
def login():
if request.method == "POST":
name = request.form.get("name")
db = Database()
with db.get_cursor() as cursor:
cursor.execute("SELECT * FROM Kullanici WHERE kullanici_adi= %s", (name,))
rows = cursor.fetchall()
for row in rows:
if request.form.get("password") == str(row[2]):
usertype = str(row[3])
return render_template("exams.html", user_type=usertype, exam=createdexams)
else:
return "<script> alert('Wrong username or password!'); </script>" + render_template("home.html")
return render_template('home.html')
def get_rooms(cursor):
query = '''
SELECT STRING_AGG(player.id::text, ',') AS player_id,
player.room_id,
STRING_AGG(player.name, ',') AS player_name,
STRING_AGG(player.avatar, ',') AS player_avatar,
room.is_open,
room.owner_id
FROM player
JOIN room ON player.room_id = room.id
GROUP BY player.room_id, room.is_open, room.owner_id;
'''
cursor.execute(query)
return cursor.fetchall()
def login():
if request.method == "POST":
name = request.form.get("name")
db = Database()
with db.get_cursor() as cursor:
cursor.execute("SELECT * FROM Kullanici WHERE kullanici_adi= %s",
(name, ))
rows = cursor.fetchall()
for row in rows:
if request.form.get("password") == str(row[2]):
global current_usertype
global current_user_id
current_user_id = row[0]
current_usertype = str(row[3])
return redirect(url_for("show_exams"))
else:
return "<script> alert('Wrong username or password!'); </script>" + render_template(
"home.html")
return render_template('home.html')
def list_user_data(cursor:RealDictCursor, user_id=False):
query = """
SELECT
users.id,
users.email,
users.registration_date,
COUNT(question.id) as asked_questions,
COUNT(distinct answer.id) as answer,
COUNT(distinct comment.id) as comment,
users.reputation
FROM users
LEFT JOIN question
ON users.id = question.user_id
LEFT JOIN answer
ON users.id = answer.user_id
LEFT JOIN comment
ON users.id = comment.user_id"""
if user_id:
query += f"""
WHERE users.id={user_id}"""
query += """
GROUP BY users.id;"""
cursor.execute(query)
return cursor.fetchall()
def get_answers_data(cursor: RealDictCursor) -> list:
query = """
SELECT *
FROM answer"""
cursor.execute(query)
return cursor.fetchall()
