def post_restore(post_id):
connect.is_exist(entity="Posts", id="id", value=post_id)
connect.exec_query(
"UPDATE Posts "
"SET isDeleted = 0 "
"WHERE Posts.id = %s", (post_id, ))
return {"post": post_id}
def thread_create(forum, title, isClosed, user, date, message, slug, optional):
connect.is_exist(entity="Users", id="email", value=user)
connect.is_exist(entity="Forums", id="short_name", value=forum)
isDeleted = 0
if "isDeleted" in optional:
isDeleted = optional["isDeleted"]
thread = connect.exec_query(
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM Threads WHERE slug = %s', (slug, )
)
if len(thread) == 0:
connect.exec_query(
'INSERT INTO Threads (forum, title, isClosed, user, date, message, slug, isDeleted) '
'VALUES (%s, %s, %s, %s, %s, %s, %s, %s)',
(forum, title, isClosed, user, date, message, slug, isDeleted, )
)
thread = connect.exec_query(
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM Threads WHERE slug = %s', (slug, )
)
response = thread_description(thread)
del response["dislikes"]
del response["likes"]
del response["points"]
del response["posts"]
return response
def thread_close(id):
connect.is_exist(entity="Threads", id="id", value=id)
connect.exec_query("UPDATE Threads "
"SET isClosed = 1 "
"WHERE id = %s", (id, ))
response = {"thread": id}
return response
def thread_create(forum, title, isClosed, user, date, message, slug, optional):
connect.is_exist(entity="Users", id="email", value=user)
connect.is_exist(entity="Forums", id="short_name", value=forum)
isDeleted = 0
if "isDeleted" in optional:
isDeleted = optional["isDeleted"]
thread = connect.exec_query(
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM Threads WHERE slug = %s', (slug, ))
if len(thread) == 0:
connect.exec_query(
'INSERT INTO Threads (forum, title, isClosed, user, date, message, slug, isDeleted) '
'VALUES (%s, %s, %s, %s, %s, %s, %s, %s)', (
forum,
title,
isClosed,
user,
date,
message,
slug,
isDeleted,
))
thread = connect.exec_query(
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM Threads WHERE slug = %s', (slug, ))
response = thread_description(thread)
del response["dislikes"]
del response["likes"]
del response["points"]
del response["posts"]
return response
def thread_subscribe(email, thread_id):
connect.is_exist(entity="Threads", id="id", value=thread_id)
connect.is_exist(entity="Users", id="email", value=email)
subscription = connect.exec_query(
'SELECT thread, user '
'FROM Subscriptions '
'WHERE user = %s AND thread = %s', (
email,
thread_id,
))
if len(subscription) == 0:
connect.exec_query(
'INSERT INTO Subscriptions (thread, user) VALUES (%s, %s)', (
thread_id,
email,
))
subscription = connect.exec_query(
'SELECT thread, user '
'FROM Subscriptions '
'WHERE user = %s AND thread = %s', (
email,
thread_id,
))
response = {"thread": subscription[0][0], "user": subscription[0][1]}
return response
def post_update(id, message):
connect.is_exist(entity="Posts", id="id", value=id)
connect.exec_query('UPDATE Posts '
'SET message = %s '
'WHERE id = %s', (
message,
id,
))
return post_details(id=id, related=[])
def thread_update(id, slug, message):
connect.is_exist(entity="Threads", id="id", value=id)
connect.exec_query(
'UPDATE Threads SET slug = %s, message = %s WHERE id = %s', (
slug,
message,
id,
))
return thread_details(id=id, related=[])
def post_update(id, message):
connect.is_exist(entity="Posts", id="id", value=id)
connect.exec_query(
'UPDATE Posts '
'SET message = %s '
'WHERE id = %s',
(message, id, )
)
return post_details(id=id, related=[])
def thread_vote(id, vote):
connect.is_exist(entity="Threads", id="id", value=id)
if vote == -1:
connect.exec_query(
"UPDATE Threads SET dislikes=dislikes+1, points=points-1 "
"WHERE id = %s", (id, ))
else:
connect.exec_query(
"UPDATE Threads SET likes=likes+1, points=points+1 "
"WHERE id = %s", (id, ))
return thread_details(id=id, related=[])
def post_restore(post_id):
connect.is_exist(entity="Posts", id="id", value=post_id)
connect.exec_query(
"UPDATE Posts "
"SET isDeleted = 0 "
"WHERE Posts.id = %s",
(post_id, )
)
return {
"post": post_id
}
def thread_open(id):
connect.is_exist(entity="Threads", id="id", value=id)
connect.exec_query(
"UPDATE Threads SET isClosed = 0 "
"WHERE id = %s",
(id, )
)
response = {
"thread": id
}
return response
def thread_remove(thread_id):
connect.is_exist(entity="Threads", id="id", value=thread_id)
connect.exec_query(
"UPDATE Threads SET isDeleted = 1 "
"WHERE id = %s",
(thread_id, )
)
response = {
"thread": thread_id
}
return response
def user_update_profile(email, about, name):
connect.is_exist(entity="Users", id="email", value=email)
connect.exec_query(
'UPDATE Users SET email = %s, about = %s, name = %s WHERE email = %s',
(
email,
about,
name,
email,
))
return user_details(email)
def thread_vote(id, vote):
connect.is_exist(entity="Threads", id="id", value=id)
if vote == -1:
connect.exec_query(
"UPDATE Threads SET dislikes=dislikes+1, points=points-1 "
"WHERE id = %s",
(id, )
)
else:
connect.exec_query(
"UPDATE Threads SET likes=likes+1, points=points+1 "
"WHERE id = %s",
(id, )
)
return thread_details(id=id, related=[])
def user_follow(email1, email2):
connect.is_exist(entity="Users", id="email", value=email1)
connect.is_exist(entity="Users", id="email", value=email2)
if email1 == email2:
raise Exception("User with email=" + email1 + " can't follow himself")
follows = connect.exec_query(
'SELECT id '
'FROM Followers '
'WHERE follower = %s AND followee = %s',
(email1, email2, )
)
if len(follows) == 0:
connect.exec_query(
'INSERT INTO Followers (follower, followee) VALUES (%s, %s)',
(email1, email2, ))
user = user_details(email1)
return user
def thread_unsubscribe(email, thread_id):
connect.is_exist(entity="Threads", id="id", value=thread_id)
connect.is_exist(entity="Users", id="email", value=email)
subscription = connect.exec_query(
'SELECT thread, user '
'FROM Subscriptions '
'WHERE user = %s AND thread = %s', (email, thread_id, )
)
if len(subscription) == 0:
raise Exception("user " + email + " does not subscribe thread #" + str(thread_id))
connect.exec_query(
'DELETE FROM Subscriptions '
'WHERE user = %s AND thread = %s', (email, thread_id, )
)
response = {
"thread": subscription[0][0],
"user": subscription[0][1]
}
return response
def user_follow(email1, email2):
connect.is_exist(entity="Users", id="email", value=email1)
connect.is_exist(entity="Users", id="email", value=email2)
if email1 == email2:
raise Exception("User with email=" + email1 + " can't follow himself")
follows = connect.exec_query(
'SELECT id '
'FROM Followers '
'WHERE follower = %s AND followee = %s', (
email1,
email2,
))
if len(follows) == 0:
connect.exec_query(
'INSERT INTO Followers (follower, followee) VALUES (%s, %s)', (
email1,
email2,
))
user = user_details(email1)
return user
def forum_create(name, short_name, user):
connect.is_exist(entity="Users", id="email", value=user)
forum = connect.exec_query(
'SELECT id, name, short_name, user '
'FROM Forums '
'WHERE short_name = %s',
(short_name, )
)
if len(forum) == 0:
connect.exec_query(
'INSERT INTO Forums (name, short_name, user) '
'VALUES (%s, %s, %s)',
(name, short_name, user, )
)
forum = connect.exec_query(
'SELECT id, name, short_name, user '
'FROM Forums '
'WHERE short_name = %s',
(short_name, )
)
return forum_description(forum)
def thread_unsubscribe(email, thread_id):
connect.is_exist(entity="Threads", id="id", value=thread_id)
connect.is_exist(entity="Users", id="email", value=email)
subscription = connect.exec_query(
'SELECT thread, user '
'FROM Subscriptions '
'WHERE user = %s AND thread = %s', (
email,
thread_id,
))
if len(subscription) == 0:
raise Exception("user " + email + " does not subscribe thread #" +
str(thread_id))
connect.exec_query(
'DELETE FROM Subscriptions '
'WHERE user = %s AND thread = %s', (
email,
thread_id,
))
response = {"thread": subscription[0][0], "user": subscription[0][1]}
return response
def forum_list_users(short_name, optional):
connect.is_exist(entity="Forums", id="short_name", value=short_name)
query = "SELECT distinct email " \
"FROM Users " \
"JOIN Posts ON Posts.user = Users.email " \
" JOIN Forums on Forums.short_name = Posts.forum " \
"WHERE Posts.forum = %s "
if "since_id" in optional:
query += " AND Users.id >= " + str(optional["since_id"])
if "order" in optional:
query += " ORDER BY Users.id " + optional["order"]
if "limit" in optional:
query += " LIMIT " + str(optional["limit"])
users_tuple = connect.exec_query(query, (short_name, ))
list = []
for user in users_tuple:
user = user[0]
list.append(users.user_details(user))
return list
def post_list(entity, id, related, params):
if entity == "user":
connect.is_exist(entity="Users", id="email", value=id)
if entity == "forum":
connect.is_exist(entity="Forums", id="short_name", value=id)
if entity == "thread":
connect.is_exist(entity="Threads", id="id", value=id)
query = "SELECT id FROM Posts WHERE " + entity + " = %s "
parameters = [id]
if "since" in params:
query += " AND date >= %s"
parameters.append(params["since"])
if "order" in params:
query += " ORDER BY date " + params["order"]
else:
query += " ORDER BY date DESC "
if "limit" in params:
query += " LIMIT " + str(params["limit"])
post_ids = connect.exec_query(query=query, params=parameters)
post_list = []
for id in post_ids:
id = id[0]
post_list.append(post_details(id=id, related=related))
return post_list
def post_list(entity, id, related, params):
if entity == "user":
connect.is_exist(entity="Users", id="email", value=id)
if entity == "forum":
connect.is_exist(entity="Forums", id="short_name", value=id)
if entity == "thread":
connect.is_exist(entity="Threads", id="id", value=id)
query = "SELECT id FROM Posts WHERE " + entity + " = %s "
parameters = [id]
if "since" in params:
query += " AND date >= %s"
parameters.append(params["since"])
if "order" in params:
query += " ORDER BY date " + params["order"]
else:
query += " ORDER BY date DESC "
if "limit" in params:
query += " LIMIT " + str(params["limit"])
post_ids = connect.exec_query(query=query, params=parameters)
post_list = []
for id in post_ids:
id = id[0]
post_list.append(post_details(id=id, related=related))
return post_list
def user_list_followers_or_following(email, type, params):
connect.is_exist(entity="Users", id="email", value=email)
if type == "follower":
where = "followee"
if type == "followee":
where = "follower"
query = "SELECT "+type+" FROM Followers JOIN Users ON Users.email = Followers."+type+\
" WHERE "+where+" = %s "
if "since_id" in params:
query += " AND Users.id >= " + str(params["since_id"])
if "order" in params:
query += " ORDER BY Users.name " + params["order"]
else:
query += " ORDER BY Users.name DESC "
if "limit" in params:
query += " LIMIT " + str(params["limit"])
followers_ids_tuple = connect.exec_query(query=query, params=(email, ))
f_list = []
for id in followers_ids_tuple:
id = id[0]
f_list.append(user_details(email=id))
return f_list
def user_list_followers_or_following(email, type, params):
connect.is_exist(entity="Users", id="email", value=email)
if type == "follower":
where = "followee"
if type == "followee":
where = "follower"
query = "SELECT "+type+" FROM Followers JOIN Users ON Users.email = Followers."+type+\
" WHERE "+where+" = %s "
if "since_id" in params:
query += " AND Users.id >= "+str(params["since_id"])
if "order" in params:
query += " ORDER BY Users.name "+params["order"]
else:
query += " ORDER BY Users.name DESC "
if "limit" in params:
query += " LIMIT "+str(params["limit"])
followers_ids_tuple = connect.exec_query(query=query, params=(email, ))
f_list = []
for id in followers_ids_tuple:
id = id[0]
f_list.append(user_details(email=id))
return f_list
def thread_subscribe(email, thread_id):
connect.is_exist(entity="Threads", id="id", value=thread_id)
connect.is_exist(entity="Users", id="email", value=email)
subscription = connect.exec_query(
'SELECT thread, user '
'FROM Subscriptions '
'WHERE user = %s AND thread = %s',
(email, thread_id, )
)
if len(subscription) == 0:
connect.exec_query(
'INSERT INTO Subscriptions (thread, user) VALUES (%s, %s)', (thread_id, email, )
)
subscription = connect.exec_query(
'SELECT thread, user '
'FROM Subscriptions '
'WHERE user = %s AND thread = %s',
(email, thread_id, )
)
response = {
"thread": subscription[0][0],
"user": subscription[0][1]
}
return response
def thread_update(id, slug, message):
connect.is_exist(entity="Threads", id="id", value=id)
connect.exec_query('UPDATE Threads SET slug = %s, message = %s WHERE id = %s',
(slug, message, id, ))
return thread_details(id=id, related=[])
def post_create(date, thread, message, user, forum, optional_params):
connect.is_exist(entity="Threads", id="id", value=thread)
connect.is_exist(entity="Forums", id="short_name", value=forum)
connect.is_exist(entity="Users", id="email", value=user)
if len(
connect.exec_query(
"SELECT Threads.id "
"FROM Threads "
"JOIN Forums ON Threads.forum = Forums.short_name "
"WHERE Threads.forum = %s AND Threads.id = %s", (
forum,
thread,
))) == 0:
raise Exception("thread with id = " + thread + " in forum " + forum +
" not found ")
if "parent" in optional_params:
if len(
connect.exec_query(
"SELECT Posts.id "
"FROM Posts "
"JOIN Threads ON Threads.id = Posts.thread "
"WHERE Posts.id = %s AND Threads.id = %s", (
optional_params["parent"],
thread,
))) == 0:
raise Exception("post with id = " + optional_params["parent"] +
" not found ")
query_for_ins = "INSERT INTO Posts (message, user, forum, thread, date"
values = "(%s, %s, %s, %s, %s"
params = [message, user, forum, thread, date]
for param in optional_params:
query_for_ins += ", " + param
values += ", %s"
params.append(optional_params[param])
query_for_ins += ") VALUES " + values + ")"
update_thread_posts = "UPDATE Threads SET posts = posts + 1 WHERE id = %s"
con = connect_to_db()
con.autocommit(False)
with con:
cursor = con.cursor()
try:
con.begin()
cursor.execute(update_thread_posts, (thread, ))
cursor.execute(query_for_ins, params)
con.commit()
except Exception as e:
con.rollback()
raise Exception("Database error: " + e.message)
id = cursor.lastrowid
cursor.close()
con.close()
post = connect.exec_query(
'SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, '
'isHighlighted, isSpam, likes, message, parent, points, thread, user '
'FROM Posts '
'WHERE id = %s', (id, ))
if len(post) == 0:
post_describtion = None
else:
post_describtion = post_describe(post)
del post_describtion["dislikes"]
del post_describtion["likes"]
del post_describtion["parent"]
del post_describtion["points"]
return post_describtion
def thread_remove(thread_id):
connect.is_exist(entity="Threads", id="id", value=thread_id)
connect.exec_query("UPDATE Threads SET isDeleted = 1 "
"WHERE id = %s", (thread_id, ))
response = {"thread": thread_id}
return response
def user_update_profile(email, about, name):
connect.is_exist(entity="Users", id="email", value=email)
connect.exec_query('UPDATE Users SET email = %s, about = %s, name = %s WHERE email = %s',
(email, about, name, email, ))
return user_details(email)
def post_create(date, thread, message, user, forum, optional_params):
connect.is_exist(entity="Threads", id="id", value=thread)
connect.is_exist(entity="Forums", id="short_name", value=forum)
connect.is_exist(entity="Users", id="email", value=user)
if len(connect.exec_query(
"SELECT Threads.id "
"FROM Threads "
"JOIN Forums ON Threads.forum = Forums.short_name "
"WHERE Threads.forum = %s AND Threads.id = %s",
(forum, thread, )
)) == 0:
raise Exception("thread with id = " + thread + " in forum " + forum + " not found ")
if "parent" in optional_params:
if len(connect.exec_query(
"SELECT Posts.id "
"FROM Posts "
"JOIN Threads ON Threads.id = Posts.thread "
"WHERE Posts.id = %s AND Threads.id = %s",
(optional_params["parent"], thread, )
)) == 0:
raise Exception("post with id = " + optional_params["parent"] + " not found ")
query_for_ins = "INSERT INTO Posts (message, user, forum, thread, date"
values = "(%s, %s, %s, %s, %s"
params = [message, user, forum, thread, date]
for param in optional_params:
query_for_ins += ", "+param
values += ", %s"
params.append(optional_params[param])
query_for_ins += ") VALUES " + values + ")"
update_thread_posts = "UPDATE Threads SET posts = posts + 1 WHERE id = %s"
con = connect_to_db()
con.autocommit(False)
with con:
cursor = con.cursor()
try:
con.begin()
cursor.execute(update_thread_posts, (thread, ))
cursor.execute(query_for_ins, params)
con.commit()
except Exception as e:
con.rollback()
raise Exception("Database error: " + e.message)
id = cursor.lastrowid
cursor.close()
con.close()
post = connect.exec_query(
'SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, '
'isHighlighted, isSpam, likes, message, parent, points, thread, user '
'FROM Posts '
'WHERE id = %s',
(id, )
)
if len(post) == 0:
post_describtion = None
else:
post_describtion = post_describe(post)
del post_describtion["dislikes"]
del post_describtion["likes"]
del post_describtion["parent"]
del post_describtion["points"]
return post_describtion