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_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_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 user_details(email): user = connect.exec_query( 'select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, )) if len(user) == 0: return None user_describtion = user_describe(user) if user_describtion is None: raise Exception("No user with email " + email) f_list = connect.exec_query( 'SELECT follower ' 'FROM Followers ' 'JOIN Users ON Users.email = Followers.follower ' 'WHERE followee = %s ', (email, )) user_describtion["followers"] = tuple2list(f_list) f_list = connect.exec_query( 'SELECT followee ' 'FROM Followers ' 'JOIN Users ON Users.email = Followers.followee ' 'WHERE follower = %s ', (email, )) user_describtion["following"] = tuple2list(f_list) s_list = [] subscriptions = connect.exec_query( 'SELECT thread FROM Subscriptions WHERE user = %s', (email, )) for el in subscriptions: s_list.append(el[0]) user_describtion["subscriptions"] = s_list return user_describtion
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 user_details(email): user = connect.exec_query('select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, )) if len(user) == 0: return None user_describtion = user_describe(user) if user_describtion is None: raise Exception("No user with email " + email) f_list = connect.exec_query( 'SELECT follower ' 'FROM Followers ' 'JOIN Users ON Users.email = Followers.follower ' 'WHERE followee = %s ', (email, ) ) user_describtion["followers"] = tuple2list(f_list) f_list = connect.exec_query( 'SELECT followee ' 'FROM Followers ' 'JOIN Users ON Users.email = Followers.followee ' 'WHERE follower = %s ', (email, ) ) user_describtion["following"] = tuple2list(f_list) s_list = [] subscriptions = connect.exec_query('SELECT thread FROM Subscriptions WHERE user = %s', (email, )) for el in subscriptions: s_list.append(el[0]) user_describtion["subscriptions"] = s_list return user_describtion
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 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 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_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 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_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_unfollow(email1, email2): follows = connect.exec_query( 'SELECT id FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, ) ) if len(follows) != 0: connect.exec_query( 'DELETE FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, ) ) else: raise Exception("No such following") return user_details(email1)
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_unfollow(email1, email2): follows = connect.exec_query( 'SELECT id FROM Followers WHERE follower = %s AND followee = %s', ( email1, email2, )) if len(follows) != 0: connect.exec_query( 'DELETE FROM Followers WHERE follower = %s AND followee = %s', ( email1, email2, )) else: raise Exception("No such following") return user_details(email1)
def user_create(email, username, about, name, optional): isAnonymous = 0 if "isAnonymous" in optional: isAnonymous = optional["isAnonymous"] try: user = connect.exec_query('select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, )) if len(user) == 0: connect.exec_query( 'INSERT INTO Users (email, about, name, username, isAnonymous) VALUES (%s, %s, %s, %s, %s)', (email, about, name, username, isAnonymous, )) user = connect.exec_query('select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, )) except Exception as e: raise Exception(e.message) return user_describe(user)
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_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 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_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_details(id, related): thread = connect.exec_query( 'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts ' 'FROM Threads WHERE id = %s', (id, )) if len(thread) == 0: raise Exception('No thread exists with id=' + str(id)) thread = thread_description(thread) if "user" in related: thread["user"] = users.user_details(thread["user"]) if "forum" in related: thread["forum"] = forums.forum_details(short_name=thread["forum"], related=[]) return thread
def thread_details(id, related): thread = connect.exec_query( 'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts ' 'FROM Threads WHERE id = %s', (id, ) ) if len(thread) == 0: raise Exception('No thread exists with id=' + str(id)) thread = thread_description(thread) if "user" in related: thread["user"] = users.user_details(thread["user"]) if "forum" in related: thread["forum"] = forums.forum_details(short_name=thread["forum"], related=[]) return thread
def forum_details(short_name, related): forum = connect.exec_query( 'SELECT id, name, short_name, user ' 'FROM Forums ' 'WHERE short_name = %s', (short_name, ) ) if len(forum) == 0: raise ("No forum with short_name=" + short_name) forum = forum_description(forum) if "user" in related: forum["user"] = users.user_details(forum["user"]) return forum
def user_create(email, username, about, name, optional): isAnonymous = 0 if "isAnonymous" in optional: isAnonymous = optional["isAnonymous"] try: user = connect.exec_query( 'select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, )) if len(user) == 0: connect.exec_query( 'INSERT INTO Users (email, about, name, username, isAnonymous) VALUES (%s, %s, %s, %s, %s)', ( email, about, name, username, isAnonymous, )) user = connect.exec_query( 'select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, )) except Exception as e: raise Exception(e.message) return user_describe(user)
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 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 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 post_details(id, related): 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) if post_describtion is None: raise Exception("no post with id = "+id) if "user" in related: post_describtion["user"] = users.user_details(post_describtion["user"]) if "forum" in related: post_describtion["forum"] = forums.forum_details(short_name=post_describtion["forum"], related=[]) if "thread" in related: post_describtion["thread"] = threads.thread_details(id=post_describtion["thread"], related=[]) return post_describtion
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 post_details(id, related): 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) if post_describtion is None: raise Exception("no post with id = " + id) if "user" in related: post_describtion["user"] = users.user_details(post_describtion["user"]) if "forum" in related: post_describtion["forum"] = forums.forum_details( short_name=post_describtion["forum"], related=[]) if "thread" in related: post_describtion["thread"] = threads.thread_details( id=post_describtion["thread"], related=[]) return post_describtion
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 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
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 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