def getAddedPost(forums_short_name, threads_id, users_email, date):
cursor = connection.cursor()
try:
cursor.execute(
"SELECT "
"posts.date AS date, "
"forums.short_name AS forum, "
"posts.id AS id, "
"posts.isApproved AS isApproved, "
"posts.isDeleted AS isDeleted, "
"posts.isEdited AS isEdited, "
"posts.isHighlighted AS isHighlighted, "
"posts.isSpam AS isSpam, "
"posts.message AS message, "
"threads.id AS thread, "
"users.email AS user "
"FROM posts RIGHT JOIN threads ON posts.thread_id = threads.id "
"RIGHT JOIN forums ON threads.forum_id = forums.id "
"JOIN users ON posts.user_id = users.id "
"WHERE forums.short_name = %s AND threads.id = %s AND users.email=%s AND posts.date=%s""",
(forums_short_name, threads_id, users_email, date))
post = dictfetchone(cursor)
cursor.close()
post['date'] = post['date'].strftime("%Y-%m-%d %H:%M:%S")
post['isApproved'] = bool(post['isApproved'])
post['isEdited'] = bool(post['isEdited'])
post['isHighlighted'] = bool(post['isHighlighted'])
post['isSpam'] = bool(post['isSpam'])
post['isDeleted'] = bool(post['isDeleted'])
except Exception:
raise
else:
return post
finally:
cursor.close()
def getDetailedForum(short_name, related):
cursor = connection.cursor()
try:
cursor.execute(
"SELECT "
"forums.id AS id, "
"forums.name AS name, "
"forums.short_name AS short_name, "
"users.email AS user "
"FROM forums JOIN users ON forums.user_id = users.id "
"WHERE forums.short_name = %s """,
(short_name,)
)
forum = dictfetchone(cursor)
cursor.close()
if not forum:
raise Exception("Forum doesn't exists")
if 'user' in related:
from technopark_db_api_app.queries import user_queries
forum['user'] = user_queries.getDetailedUser(forum['user'])
except Exception:
raise
else:
return forum
finally:
cursor.close()
def getAddedThread(forums_short_name, threads_slug):
cursor = connection.cursor()
try:
cursor.execute(
"SELECT "
"threads.date AS date, "
"forums.short_name AS forum, "
"threads.id AS id, "
"threads.isClosed AS isClosed, "
"threads.isDeleted AS isDeleted, "
"threads.message AS message, "
"threads.slug AS slug, "
"threads.title AS title, "
"users.email AS user "
"FROM threads JOIN forums ON threads.forum_id = forums.id "
"JOIN users ON threads.user_id = users.id "
"WHERE forums.short_name = %s AND threads.slug = %s ",
(forums_short_name, threads_slug,))
thread = dictfetchone(cursor)
cursor.close()
if not thread:
raise Exception("Thread doesn't exist")
thread['isClosed'] = bool(thread['isClosed'])
thread['isDeleted'] = bool(thread['isDeleted'])
thread['date'] = thread['date'].strftime("%Y-%m-%d %H:%M:%S")
except Exception:
raise
else:
return thread
finally:
cursor.close()
def getDetailedPost(id, related):
cursor = connection.cursor()
try:
cursor.execute(
"SELECT "
"posts.date AS date, "
"posts.dislikes AS dislikes, "
"forums.short_name AS forum, "
"posts.id AS id, "
"posts.isApproved AS isApproved, "
"posts.isDeleted AS isDeleted, "
"posts.isEdited AS isEdited, "
"posts.isHighlighted AS isHighlighted, "
"posts.isSpam AS isSpam, "
"posts.likes AS likes, "
"posts.message AS message, "
"posts.post_id AS parent, "
"posts.likes - posts.dislikes AS points, "
"posts.thread_id AS thread, "
"users.email AS user "
"FROM posts RIGHT JOIN threads ON posts.thread_id = threads.id "
"RIGHT JOIN forums ON threads.forum_id = forums.id "
"JOIN users ON posts.user_id = users.id "
"WHERE posts.id = %s """, (id,))
post = dictfetchone(cursor)
cursor.close()
if not post:
raise Exception("Post doesn't exist")
if 'user' in related:
from technopark_db_api_app.queries import user_queries
post['user'] = user_queries.getDetailedUser(post['user'])
if 'thread' in related:
from technopark_db_api_app.queries import thread_queries
post['thread'] = thread_queries.getDetailedThread(post['thread'],[])
if 'forum' in related:
from technopark_db_api_app.queries import forum_queries
post['forum'] = forum_queries.getDetailedForum(post['forum'], [])
post['isApproved'] = bool(post['isApproved'])
post['isDeleted'] = bool(post['isDeleted'])
post['isEdited'] = bool(post['isEdited'])
post['isHighlighted'] = bool(post['isHighlighted'])
post['isSpam'] = bool(post['isSpam'])
post['date'] = post['date'].strftime("%Y-%m-%d %H:%M:%S")
except Exception:
raise
else:
return post
finally:
cursor.close()
def removePost(id):
cursor = connection.cursor()
try:
cursor.execute("UPDATE posts SET isDeleted = TRUE WHERE id = %s", (id,))
connection.commit()
cursor.execute("SELECT id AS post FROM posts WHERE id = %s AND isDeleted = TRUE", (id,))
thread = dictfetchone(cursor)
cursor.close()
if not thread:
raise Exception("Post doesn't exist")
except Exception:
raise
else:
return thread
finally:
cursor.close()
def restorePost(id):
cursor = connection.cursor()
try:
cursor.execute("UPDATE posts SET isDeleted = FALSE WHERE id = %s", (id,))
connection.commit()
cursor.execute("SELECT id AS post FROM posts WHERE id = %s AND isDeleted = FALSE", (id,))
post = dictfetchone(cursor)
cursor.close()
if not post:
raise Exception("Post doesn't exist")
except Exception:
raise
else:
return post
finally:
cursor.close()
def getCreatedUser(email):
cursor = connection.cursor()
try:
cursor.execute(
"SELECT about, email, id, isAnonymous, name, username " "FROM users " "WHERE email = %s ", (email,)
)
user = dictfetchone(cursor)
if not user:
raise Exception("User doesn't exist")
user["isAnonymous"] = bool(user["isAnonymous"])
except Exception:
raise
else:
return user
finally:
cursor.close()
def restoreThread(id):
cursor = connection.cursor()
try:
cursor.execute("UPDATE threads SET isDeleted = FALSE WHERE id = %s", (id,))
connection.commit()
cursor.execute("SELECT id AS thread FROM threads WHERE id = %s AND isDeleted = FALSE", (id,))
thread = dictfetchone(cursor)
cursor.close()
if not thread:
raise Exception("Thread doesn't exist")
except Exception:
raise
else:
return thread
finally:
cursor.close()
def votePost(id, vote):
cursor = connection.cursor()
try:
if vote == 1:
cursor.execute("UPDATE posts SET likes = likes + 1 WHERE id = %s ", (id,))
elif vote == -1:
cursor.execute("UPDATE posts SET dislikes = dislikes + 1 WHERE id = %s ", (id,))
else:
raise Exception("Wrong vote value")
cursor.execute("SELECT id AS post FROM posts WHERE id = %s ", (id,))
post = dictfetchone(cursor)
cursor.close()
except Exception:
raise
else:
return post
finally:
cursor.close()
def voteThread(id, vote):
cursor = connection.cursor()
try:
if vote == 1:
cursor.execute("UPDATE threads SET likes = likes + 1 WHERE id = %s ", (id,))
elif vote == -1:
cursor.execute("UPDATE threads SET dislikes = dislikes + 1 WHERE id = %s ", (id,))
else:
raise Exception("Wrong vote value")
cursor.execute("SELECT id AS thread FROM threads WHERE id = %s ", (id,))
thread = dictfetchone(cursor)
cursor.close()
except Exception:
raise
else:
return thread
finally:
cursor.close()
def getDetailedThread(id, related):
cursor = connection.cursor()
try:
cursor.execute(
"SELECT "
"threads.date AS date, "
"threads.dislikes AS dislikes, "
"forums.short_name AS forum, "
"threads.id AS id, "
"threads.isClosed AS isClosed, "
"threads.isDeleted AS isDeleted, "
"threads.likes AS likes, "
"threads.message AS message, "
"threads.likes - threads.dislikes AS points, "
"COUNT(posts.id) AS posts, "
"threads.slug AS slug, "
"threads.title AS title, "
"users.email AS user "
"FROM forums JOIN threads ON forums.id = threads.forum_id "
"JOIN users ON threads.user_id = users.id "
"JOIN posts ON posts.thread_id = threads.id "
"WHERE threads.id = %s""", (id,))
thread = dictfetchone(cursor)
cursor.close()
thread['isClosed'] = bool(thread['isClosed'])
thread['isDeleted'] = bool(thread['isDeleted'])
thread['date'] = thread['date'].strftime("%Y-%m-%d %H:%M:%S")
if 'user' in related:
from technopark_db_api_app.queries import user_queries
thread['user'] = user_queries.getDetailedUser(thread['user'])
if 'forum' in related:
from technopark_db_api_app.queries import forum_queries
thread['forum'] = forum_queries.getDetailedForum(thread['forum'], [])
except Exception:
raise
else:
return thread
finally:
cursor.close()
def getAddedForum(short_name):
cursor = connection.cursor()
try:
cursor.execute(
"SELECT "
"forums.id AS id, "
"forums.name AS name, "
"forums.short_name as short_name, "
"users.email AS user "
"FROM forums JOIN users ON forums.user_id = users.id "
"WHERE forums.short_name = %s """,
(short_name,))
forum = dictfetchone(cursor)
cursor.close()
if not forum:
raise Exception("Forum doesn't exist")
except Exception:
raise
else:
return forum
finally:
cursor.close()
def unsubscribeThread(users_email, id):
cursor = connection.cursor()
try:
cursor.execute(
"INSERT INTO subscribers (user_id, thread_id, subscribed) "
"VALUES ((SELECT id FROM users WHERE email = %s), %s, FALSE) "
"ON DUPLICATE KEY UPDATE subscribed=FALSE ",
(users_email, int(id),))
connection.commit()
cursor.execute(
"SELECT "
"threads.id AS thread, "
"users.email AS user "
"FROM threads JOIN subscribers ON threads.id = subscribers.thread_id "
"JOIN users ON subscribers.user_id = users.id "
"WHERE users.email = %s AND threads.id = %s AND subscribers.subscribed = FALSE ", (users_email, id, ))
thread = dictfetchone(cursor)
cursor.close()
except Exception:
raise
else:
return thread
finally:
cursor.close()
