def thread_list(entity, entity_attr, related, params): if entity == "forum": db_funcs.db_exist(entity="Forums", entity_attr="short_name", attr_value=entity_attr) else: # if entity == "user": db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=entity_attr) select = "SELECT id" \ " FROM Threads" \ " WHERE " + entity + " = %s " select_params = [entity_attr] if "since" in params: select += " AND date >= %s" select_params.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"]) threads = db_funcs.db_select(query=select, query_params=select_params) list_t = [] for thread in threads: list_t.append(thread_details(thread_id=thread[0], related=related)) return list_t
def thread_update(thread_id, slug, message): db_funcs.db_exist(entity="Threads", entity_attr="id", attr_value=thread_id) db_funcs.db_insert_or_delete_or_update( 'UPDATE Threads' ' SET slug = %s, message = %s' ' WHERE id = %s', (slug, message, thread_id, ) ) return thread_details(thread_id=thread_id, related=[])
def user_update_profile(email, about, name): db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=email) db_funcs.db_insert_or_delete_or_update( 'UPDATE Users' ' SET about = %s, name = %s' ' WHERE email = %s', (about, name, email, ) ) return user_details(email)
def post_update(post_id, message): db_funcs.db_exist(entity="Posts", entity_attr="id", attr_value=post_id) db_funcs.db_insert_or_delete_or_update( 'UPDATE Posts' ' SET message = %s' ' WHERE id = %s', (message, post_id, ) ) return post_details(post_id=post_id, related=[])
def post_remove_or_restore(post_id, status): db_funcs.db_exist(entity="Posts", entity_attr="id", attr_value=post_id) db_funcs.db_insert_or_delete_or_update( "UPDATE Posts " "SET isDeleted = %s " "WHERE id = %s", (status, post_id, ) ) post_response = {"post": post_id} return post_response
def forum_create(name, short_name, user): db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=user) forum = forum_get(short_name) if not len(forum): db_funcs.db_insert_or_delete_or_update( 'INSERT INTO Forums (name, short_name, user) ' 'VALUES (%s, %s, %s)', (name, short_name, user, ) ) forum = forum_get(short_name) return forum_describe(forum[0])
def thread_remove_or_restore(thread_id, is_deleted): db_funcs.db_exist(entity="Threads", entity_attr="id", attr_value=thread_id) db_funcs.db_insert_or_delete_or_update( "UPDATE Threads" " SET isDeleted = %s" " WHERE id = %s", (is_deleted, thread_id, ) ) thread_response = { "thread": thread_id } return thread_response
def user_follow(follower_email, followee_email): if follower_email == followee_email: raise Exception("followers: follower_email=followee_email=" + follower_email) db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=follower_email) db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=followee_email) follows = follower_select(follower_email, followee_email) if not len(follows): db_funcs.db_insert_or_delete_or_update( 'INSERT INTO Followers (follower, followee)' ' VALUES (%s, %s)', (follower_email, followee_email, ) ) return db_users_func.user_details(follower_email)
def thread_subscribe(sub_email, thread_id): db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=sub_email) db_funcs.db_exist(entity="Threads", entity_attr="id", attr_value=thread_id) subscription = subscription_select(sub_email, thread_id) if not len(subscription): db_funcs.db_insert_or_delete_or_update( 'INSERT INTO Subscriptions (thread, user)' ' VALUES (%s, %s)', (thread_id, sub_email, ) ) subscription = subscription_select(sub_email, thread_id) subscription_response = { "thread": subscription[0][0], "user": subscription[0][1] } return subscription_response
def user_unfollow(follower_email, followee_email): if follower_email == followee_email: raise Exception("followers: follower_email=followee_email=" + follower_email) db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=follower_email) db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=followee_email) follows = follower_select(follower_email, followee_email) if len(follows): db_funcs.db_insert_or_delete_or_update( 'DELETE' ' FROM Followers' ' WHERE id= %s', (follows[0][0], ) ) else: raise Exception("followers: following not found") return db_users_func.user_details(follower_email)
def post_vote(post_id, vote): db_funcs.db_exist(entity="Posts", entity_attr="id", attr_value=post_id) if vote == -1: db_funcs.db_insert_or_delete_or_update( "UPDATE Posts" " SET dislikes=dislikes+1, points=points-1" " WHERE id = %s", (post_id, ) ) else: # if vote == 1: db_funcs.db_insert_or_delete_or_update( "UPDATE Posts" " SET likes=likes+1, points=points+1" " WHERE id = %s", (post_id, ) ) return post_details(post_id=post_id, related=[])
def thread_vote(thread_id, vote): db_funcs.db_exist(entity="Threads", entity_attr="id", attr_value=thread_id) if vote == -1: db_funcs.db_insert_or_delete_or_update( "UPDATE Threads" " SET dislikes = dislikes + 1, points = points - 1" " WHERE id = %s", (thread_id, ) ) else: # if vote == 1: db_funcs.db_insert_or_delete_or_update( "UPDATE Threads" " SET likes = likes + 1, points = points + 1" " WHERE id = %s", (thread_id, ) ) return thread_details(thread_id=thread_id, related=[])
def user_details(email): db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=email) user = user_select(email) if not len(user): raise Exception("user: user with email " + email + " not found") user_response = user_describe(user[0]) user_response["followers"] = user_follower(email, "follower") user_response["following"] = user_follower(email, "followee") list_s = [] subscriptions = db_funcs.db_select( 'SELECT thread' ' FROM Subscriptions' ' WHERE user = %s', (email, ) ) for sub in subscriptions: list_s.append(sub[0]) user_response["subscriptions"] = list_s return user_response
def thread_create(forum, title, is_closed, user, date, message, slug, optional): db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=user) db_funcs.db_exist(entity="Forums", entity_attr="short_name", attr_value=forum) is_deleted = 0 if "isDeleted" in optional: is_deleted = optional["isDeleted"] thread = thread_select(slug) if not len(thread): db_funcs.db_insert_or_delete_or_update( 'INSERT INTO Threads (forum, title, isClosed, user, date, message, slug, isDeleted)' ' VALUES (%s, %s, %s, %s, %s, %s, %s, %s)', (forum, title, is_closed, user, date, message, slug, is_deleted, ) ) thread = thread_select(slug) thread_response = thread_describe(thread[0]) del thread_response["dislikes"] del thread_response["likes"] del thread_response["points"] del thread_response["posts"] return thread_response
def forum_list_users(short_name, optional): db_funcs.db_exist(entity="Forums", entity_attr="short_name", attr_value=short_name) select = "SELECT DISTINCT email" \ " FROM Users u, Posts p, Forums f" \ " WHERE p.user = u.email" \ " AND f.short_name = p.forum" \ " AND p.forum = %s" if "since_id" in optional: select += " AND u.id >= " + str(optional["since_id"]) if "order" in optional: select += " ORDER BY u.id " + optional["order"] else: select += " ORDER BY u.id DESC" if "limit" in optional: select += " LIMIT " + str(optional["limit"]) result = db_funcs.db_select(select, (short_name, )) list_u = [] for record in result: list_u.append(db_users_func.user_details(record[0])) return list_u
def user_list_followers_or_following(type_email, type, params): db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=type_email) if type == "follower": where_condition = "followee" else: # if type == "followee": where_condition = "follower" select = "SELECT " + type + \ " FROM Followers f, Users u" \ " WHERE u.email = f." + type +\ " AND " + where_condition + " = %s " if "since_id" in params: select += " AND u.id >= " + str(params["since_id"]) if "order" in params: select += " ORDER BY u.name " + params["order"] else: select += " ORDER BY u.name DESC" if "limit" in params: select += " LIMIT " + str(params["limit"]) followers_ees = db_funcs.db_select(query=select, query_params=(type_email, )) list_f = [] for follower_ee in followers_ees: list_f.append(db_users_func.user_details(email=follower_ee[0])) return list_f
def post_create(date, thread, message, user, forum, optional): db_funcs.db_exist(entity="Threads", entity_attr="id", attr_value=thread) db_funcs.db_exist(entity="Forums", entity_attr="short_name", attr_value=forum) db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=user) thread_counter = db_funcs.db_select( "SELECT COUNT(t.id)" " FROM Threads t, Forums f" " WHERE t.forum = f.short_name" " AND t.forum = %s" " AND t.id = %s", (forum, thread, ) ) if not thread_counter[0][0]: raise Exception("post: thread with id = " + thread + " in forum " + forum + " not found") if "parent" in optional: parent_counter = db_funcs.db_select( "SELECT COUNT(p.id)" " FROM Posts p, Threads t" " WHERE t.id = p.thread" " AND p.id = %s" " AND t.id = %s", (optional["parent"], thread, ) ) if not parent_counter[0][0]: raise Exception("post: post with id = " + optional["parent"] + " not found") insert = "INSERT INTO Posts (message, user, forum, thread, date" values = "(%s, %s, %s, %s, %s" params = [message, user, forum, thread, date] for is_attr in optional: insert += ", " + is_attr values += ", %s" params.append(optional[is_attr]) insert += ") VALUES " + values + ")" update_threads_posts = "UPDATE Threads SET posts = posts + 1 WHERE id = %s" conn = db_funcs.db_connect() conn.autocommit(False) with conn: cursor = conn.cursor() try: cursor.execute(update_threads_posts, (thread, )) cursor.execute(insert, params) conn.commit() except Exception as e: conn.rollback() raise Exception("Database error: " + e.message) post_id = cursor.lastrowid cursor.close() conn.close() post_response = post_select(post_id) del post_response["dislikes"] del post_response["likes"] del post_response["parent"] del post_response["points"] return post_response