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 }