def listThreadHelper(table, id, related, params):
if table == "forum":
finder.find(table="Forums", id="short_name", value=id)
if table == "user":
finder.find(table="Users", id="email", value=id)
select = "SELECT id FROM Threads WHERE " + table + " = %s "
parameters = [id]
if "since" in params:
select += " AND date >= %s"
parameters.append(params["since"])
if "order" in params:
select += " ORDER BY date " + params["order"]
else:
select += " ORDER BY date DESC "
if "limit" in params:
select += " LIMIT " + str(params["limit"])
result = Select(query=select, params=parameters)
threadArray= []
for id in result:
id = id[0]
threadArray.append(detailsThreadHelper(id=id, related=related))
return threadArray
def votePostHelper(id, vote):
finder.find(table="Posts", id="id", value=id)
if vote == -1:
Update("UPDATE Posts SET dislikes=dislikes+1, points=points-1 where id = %s", (id, ))
else:
Update("UPDATE Posts SET likes=likes+1, points=points+1 where id = %s", (id, ))
return detailsPostHelper(id=id, option=[])
def createThreadHelper(forum, title, isClosed, user, date, message, slug, optional):
finder.find(table="Users", id="email", value=user)
finder.find(table="Forums", id="short_name", value=forum)
isDeleted = 0
if "isDeleted" in optional:
isDeleted = optional["isDeleted"]
select = Select('select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM Threads WHERE slug = %s', (slug, )
)
if len(select) == 0:
Update('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, )
)
select = Select(
'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM Threads WHERE slug = %s', (slug, )
)
response = threadFormat(select)
# Delete few extra elements
del response["dislikes"]
del response["likes"]
del response["points"]
del response["posts"]
return response
def listFollowersHelper(email, fol1, optional):
finder.find(table="Users", id="email", value=email)
if fol1 == "followee":
fol2 = "follower"
else:
fol2 = "followee"
query = "SELECT "+fol1+" FROM Followers JOIN Users ON Users.email = Followers."+fol1+\
" WHERE "+fol2+" = %s "
resultArray = []
if "since_id" in optional:
query += " AND Users.id >= "+str(optional["since_id"])
if "order" in optional:
query += " ORDER BY Users.name "+optional["order"]
else:
query += " ORDER BY Users.name DESC "
if "limit" in optional:
query += " LIMIT "+str(optional["limit"])
resultSelect = Select(query=query, params=(email, ))
for id in resultSelect:
id = id[0]
resultArray.append(detailsHelper(email=id))
return resultArray
def createForumHelper(name, short_name, user):
finder.find(table="Users", id="email", value=user)
result = Select('select id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, ))
if len(result) == 0:
Update('INSERT INTO Forums (name, short_name, user) VALUES (%s, %s, %s)',
(name, short_name, user, ))
result = Select('select id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, ))
return ForumFormat(result)
def toggleDeleteThreadHelper(thread_id, status):
finder.find(table="Threads", id="id", value=thread_id)
Update("UPDATE Threads SET isDeleted = %s WHERE id = %s", (status, thread_id, ))
res = {
"thread": thread_id
}
return res
def voteThreadHelper(id, value):
finder.find(table="Threads", id="id", value=id)
if value == -1:
Update("UPDATE Threads SET dislikes=dislikes+1, points=points-1 where id = %s", (id, ))
else:
Update("UPDATE Threads SET likes=likes+1, points=points+1 where id = %s", (id, ))
return detailsThreadHelper(id=id, related=[])
def toggleThreadHelper(id, isClosed):
finder.find(table="Threads", id="id", value=id)
Update("UPDATE Threads SET isClosed = %s WHERE id = %s", (isClosed, id, ))
response = {
"thread": id
}
return response
def createSubscriptionHelper(email, thread_id):
finder.find(table="Threads", id="id", value=thread_id)
finder.find(table="Users", id="email", value=email)
select = Select('select thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, ))
if len(select) == 0:
Update('INSERT INTO Subscriptions (thread, user) VALUES (%s, %s)', (thread_id, email, ))
select = Select('select thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, ))
answer = {
"thread": select[0][0],
"user": select[0][1]
}
return answer
def followHelper(email1, email2):
finder.find(table="Users", id="email", value=email1)
finder.find(table="Users", id="email", value=email2)
if email1 == email2:
raise Exception("User with email=" + email1 + " can't follow himself")
result = Select('SELECT id FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, ))
if len(result) == 0:
Update('INSERT INTO Followers (follower, followee) VALUES (%s, %s)',
(email1, email2, ))
return detailsHelper(email1)
def removeSubscriprionHelper(email, thread_id):
finder.find(table="Threads", id="id", value=thread_id)
finder.find(table="Users", id="email", value=email)
select = Select('select thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, ))
if len(select) == 0:
raise Exception("user " + email + " does not subscribe thread #" + str(thread_id))
Update('DELETE FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, ))
answer = {
"thread": select[0][0],
"user": select[0][1]
}
return answer
def listForumUsersHelper(short_name, optional):
finder.find(table="Forums", id="short_name", value=short_name)
select = "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:
select += " AND Users.id >= " + str(optional["since_id"])
if "order" in optional:
select += " ORDER BY Users.id " + optional["order"]
if "limit" in optional:
select += " LIMIT " + str(optional["limit"])
resultArray = []
result = Select(select, (short_name, ))
for user in result:
user = user[0]
resultArray.append(UsersHelper.detailsHelper(user))
return resultArray
def createPostHelper(date, thread, message, user, forum, optional):
finder.find(table="Threads", id="id", value=thread)
finder.find(table="Forums", id="short_name", value=forum)
finder.find(table="Users", id="email", value=user)
if len(Select("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("no thread with id = " + thread + " in forum " + forum)
if "parent" in optional:
if len(Select("SELECT Posts.id FROM Posts JOIN Threads ON Threads.id = Posts.thread "
"WHERE Posts.id = %s AND Threads.id = %s", (optional["parent"], thread, ))) == 0:
raise Exception("Cant find post with id = " + optional["parent"])
query = "INSERT INTO Posts (message, user, forum, thread, date"
values = "(%s, %s, %s, %s, %s"
parameters = [message, user, forum, thread, date]
for param in optional:
query += ", "+param
values += ", %s"
parameters.append(optional[param])
query += ") VALUES " + values + ")"
update = "UPDATE Threads SET posts = posts + 1 WHERE id = %s"
connection = connect()
with connection:
cursor = connection.cursor()
try:
connection.begin()
cursor.execute(update, (thread, ))
cursor.execute(query, parameters)
connection.commit()
except Exception as e:
connection.rollback()
raise Exception("Database error: " + e.message)
post_id = cursor.lastrowid
cursor.close()
connection.close()
post = postQueryHelper(post_id)
del post["dislikes"]
del post["likes"]
del post["parent"]
del post["points"]
return post
def postListHelper(table, id, related, option):
if table == "user":
finder.find(table="Users", id="email", value=id)
if table == "forum":
finder.find(table="Forums", id="short_name", value=id)
if table == "thread":
finder.find(table="Threads", id="id", value=id)
select = "SELECT id FROM Posts WHERE " + table + " = %s "
par = [id]
if "since" in option:
select += " AND date >= %s"
par.append(option["since"])
if "order" in option:
select += " ORDER BY date " + option["order"]
else:
select += " ORDER BY date DESC "
if "limit" in option:
select += " LIMIT " + str(option["limit"])
query = Select(query=select, params=par)
post_list = []
for id in query:
id = id[0]
post_list.append(detailsPostHelper(id=id, option=related))
return post_list
def updatePostHelper(id, message):
finder.find(table="Posts", id="id", value=id)
Update('UPDATE Posts SET message = %s WHERE id = %s', (message, id, ))
return detailsPostHelper(id=id, option=[])
def updateProfileHelper(email, about, name):
finder.find(table="Users", id="email", value=email)
Update('UPDATE Users SET email = %s, about = %s, name = %s WHERE email = %s',
(email, about, name, email, ))
return detailsHelper(email)
def updateThreadHelper(id, slug, message):
finder.find(table="Threads", id="id", value=id)
Update('UPDATE Threads SET slug = %s, message = %s WHERE id = %s',
(slug, message, id, ))
return detailsThreadHelper(id=id, related=[])
def togglePostHelper(post_id, mark):
finder.find(table="Posts", id="id", value=post_id)
Update("UPDATE Posts SET isDeleted = %s WHERE Posts.id = %s", (mark, post_id, ))
return {
"post": post_id
}