def user_sort(condition, user_id): condition_list = ["submission_time ASC", "submission_time DESC", "view_number ASC", "view_number DESC", "vote_number ASC", "vote_number DESC"] if condition in condition_list: return common.query_handler("SELECT * FROM question WHERE users_id=%s ORDER BY " + condition, (user_id,)) else: return common.query_handler("SELECT * FROM question WHERE users_id=%s ORDER BY submission_time ASC", (user_id,))
def sort_by_condition(condition): condition_list = ["submission_time ASC", "submission_time DESC", "view_number ASC", "view_number DESC", "vote_number ASC", "vote_number DESC"] if condition in condition_list: return common.query_handler("""SELECT question.id, title, message, user_name, question.submission_time, view_number, vote_number, image FROM question LEFT JOIN users ON users.id=users_id ORDER BY """ + condition) else: return common.query_handler("""SELECT question.id, title, message, user_name, question.submission_time, view_number, vote_number, image FROM question LEFT JOIN users ON users.id=users_id ORDER BY submission_time ASC""")
def comment_question_answer_to_userid(user_id): return common.query_handler("""SELECT comment.question_id AS questid, comment.submission_time, comment.message, answer.message AS ansme, question.title AS quest, answer.question_id FROM comment LEFT JOIN question ON comment.question_id=question.id LEFT JOIN answer ON comment.answer_id=answer.id WHERE comment.users_id=%s""", (user_id,))
def search(replacement): return common.query_handler("""SELECT DISTINCT question.id, question.users_id, user_name, question.submission_time, question.view_number, question.vote_number, question.image, answer.question_id, Replace(question.title, %(search)s, %(marks)s) AS title, Replace(question.message, %(search)s, %(marks)s) AS message FROM question LEFT JOIN users ON question.users_id=users.id FULL JOIN answer ON question.id = answer.question_id WHERE question.title ILIKE '%%' || %(search)s || '%%' OR answer.message ILIKE '%%' || %(search)s || '%%' OR question.message ILIKE '%%' || %(search)s || '%%' ;""",replacement)
def question_to_userid(user_id): return common.query_handler("""SELECT * FROM question WHERE users_id=%s""", (user_id,))
def sort_users(condition): condition_list = ["user_name ASC", "user_name DESC"] if condition in condition_list: return common.query_handler("SELECT * FROM users ORDER BY " + condition) else: return common.query_handler("""SELECT * FROM users ORDER BY user_name ASC;""")
def question_to_comment(comment_id): return common.query_handler("SELECT question_id, answer_id FROM comment WHERE id=%s", (int(comment_id),))
def index(): return common.query_handler("""SELECT question.id, question.submission_time, view_number, vote_number, title, message, image, users_id, user_name, reputation FROM question LEFT JOIN users ON users.id=users_id ORDER BY question.submission_time DESC LIMIT 5;""")
def update_question(formdata): common.query_handler("""UPDATE question SET title=%s, message=%s, image=%s WHERE id=%s;""", (formdata["title"], formdata["Question"], formdata["image"], formdata["question_id"]))
def insert_users(current_user): common.query_handler("INSERT INTO users (submission_time, user_name, reputation) \ VALUES (%s, %s, %s)", (datetime.now().replace(microsecond=0), current_user, 0))
def questionid_to_answer_from_dict(question): return common.query_handler("SELECT question_id FROM answer WHERE id=%s", (question[0]["answer_id"],))
def delete_tag(questionid, tag_id): common.query_handler("DELETE FROM question_tag WHERE question_id=%s AND tag_id=%s", (questionid, tag_id))
def insert_tag_to_question(formdata, tagid): common.query_handler("""INSERT INTO question_tag (question_id, tag_id) VALUES(%s, %s)""", (formdata['question_id'], tagid[0]['id']))
def insert_tag_name(formdata): common.query_handler("""INSERT INTO tag (name) VALUES(%s)""", (formdata['Tag'],))
def tag_by_id(formdata, tagkey): return common.query_handler('SELECT * FROM tag WHERE name=%s', (formdata[tagkey],))
def all_users(): return common.query_handler("SELECT * FROM users;")
def select_all_tags(): return common.query_handler("SELECT * FROM tag")
def answer_question_to_userid(user_id): return common.query_handler("""SELECT answer.submission_time, answer.vote_number,answer.question_id, answer.message, answer.image, question.title AS quest FROM answer JOIN question ON question_id=question.id WHERE answer.users_id=%s""", (user_id,))
def tags(): return common.query_handler("SELECT name, COUNT(question_id) AS question_number \ FROM tag JOIN question_tag ON tag.id=question_tag.tag_id \ GROUP BY name")
def delete_comment(comment_id): common.query_handler("DELETE FROM comment WHERE id=%s", (comment_id,))
def question_user(): return common.query_handler("""SELECT question.id, title, message, user_name, question.submission_time, view_number, vote_number, image FROM question LEFT JOIN users ON users.id=users_id;""")
def answer_to_userid(user_id): return common.query_handler("""SELECT * FROM answer WHERE users_id=%s""", (user_id,))
def usernames(): return common.query_handler("SELECT user_name FROM users")
def viewcount(questionid): return common.query_handler("UPDATE question SET view_number = view_number + 1 WHERE id=%s", (questionid,))
def username_id(): return common.query_handler("""SELECT id, user_name, id FROM users;""")
def comment_to_userid(user_id): return common.query_handler("""SELECT * FROM comment WHERE users_id=%s""", (user_id,))
def new_question(formdata): common.query_handler("""INSERT INTO question (submission_time, view_number, vote_number, title, message, image, users_id) VALUES(%s,%s,%s,%s,%s,%s,%s);""", (datetime.now().replace(microsecond=0), 0, 0, formdata["title"], formdata["Question"], formdata["image"], formdata["user"]))
def user_by_id(user_id): return common.query_handler("""SELECT * FROM users WHERE id=%s""", (user_id,))
def select_question(questionid): return common.query_handler("""SELECT question.id, question.submission_time, view_number, vote_number, title, message, image, user_name FROM question LEFT JOIN users ON users.id=users_id WHERE question.id=%s""", (questionid,))
def comment_user(comment_id): return common.query_handler("""SELECT comment.id, question_id, answer_id, message, comment.submission_time, edited_count, users_id, user_name FROM comment LEFT JOIN users ON users_id=users.id WHERE comment.id=%s""", (comment_id,))