def dec_posts_count(connect,post): thread = DB_connect.select_query(connect,"SELECT thread FROM Post WHERE id = %s", (post, )) try: DB_connect.update_query(connect,"UPDATE Thread SET posts = posts - 1 WHERE id = %s", (thread[0][0], )) except Exception as e: print(e.message) return
def subscribe_user(connect, thread, user): isDeleted = 0; DB_connect.update_query(connect, "INSERT INTO Subscription (user, thread_id, isDeleted) VALUES (%s, %s, %s)", (user, thread, isDeleted)) return { "thread": thread, "user": user }
def vote_for_post(connect, post, vote): if vote == 1: DB_connect.update_query(connect, "UPDATE Post SET likes = likes + 1 WHERE id = %s", (post,)) else: if vote == -1: DB_connect.update_query(connect, "UPDATE Post SET dislikes = dislikes + 1 WHERE id = %s", (post,)) else: raise Exception("VallueError") return show_post(connect, post, [])
def vote_for_thread(connect, thread, vote): if (vote == 1): DB_connect.update_query(connect,'UPDATE Thread SET likes = likes + 1 WHERE id = %s', (thread, )) else: if (vote == -1): DB_connect.update_query(connect,'UPDATE Thread SET dislikes = dislikes + 1 WHERE id = %s',(thread, )) else: raise Exception("VallueError") return show_thread(connect, thread, [])
def save_forum(connect, name, short_name, user): print('save_forum:') #str = DB_connect.select_query(connect, 'SELECT id FROM Forum WHERE short_name = %s', (short_name, )) #if len(str) > 0: # raise Exception("Exist") DB_connect.update_query(connect, "INSERT INTO Forum (name, short_name, user) VALUES (%s, %s, %s)", (name, short_name, user, )) forum = DB_connect.select_query(connect, 'SELECT id, name, short_name, user FROM Forum WHERE short_name = %s', (short_name, )) return forum_description(forum)
def unsubscribe_user(connect, thread, user): isDeleted = 1; try: DB_connect.update_query(connect, "UPDATE Subscription SET isDeleted = %s WHERE user = %s AND thread_id = %s ",(isDeleted, user, thread,)) except Exception as e: raise Exception("user " + user + " does not subscribe thread #" + str(thread)) return { "thread": thread, "user": user }
def follow_user(connect, follower, followee): user1 = DB_connect.select_query(connect, 'SELECT email FROM User WHERE email = %s', (follower, )) user2 = DB_connect.select_query(connect, 'SELECT email FROM User WHERE email = %s', (follower, )) if user1 and user2: str = DB_connect.update_query(connect, 'INSERT INTO Follow (user, follow) VALUES (%s, %s)', (follower, followee, ) ) else: raise Exception("Error. Such users don't exist"); user = show_user(connect, follower) return user
def remove_restore(connect,thread, isDeleted): if isDeleted == 1: posts = 0 else: posts = DB_connect.select_query(connect,"SELECT COUNT(id) FROM Post WHERE thread = %s", (thread, ))[0][0] DB_connect.update_query(connect,"UPDATE Thread SET isDeleted = %s, posts = %s WHERE id = %s", (isDeleted,posts, thread )) DB_connect.update_query(connect,"UPDATE Post SET isDeleted = %s WHERE thread = %s", (isDeleted,thread, )) return { "thread": thread }
def save_thread(connect, forum, title, isClosed, user, date, message, slug, optional): isDeleted = 0; if "isDeleted" in optional: isDeleted= optional["isDeleted"] str = DB_connect.update_query(connect, 'INSERT INTO Thread (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, )) max_id = DB_connect.select_query(connect, 'SELECT max(id) FROM Thread', ()) thread = DB_connect.select_query(connect, 'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user FROM Thread WHERE id = %s', (max_id[0][0], ) ) return thread_description(thread)
def save_user(connect, username, about, name, email, optional): isAnonim = 0 if "isAnonymous" in optional: isAnonim = optional["isAnonymous"] if isAnonim: name = username = about = '' str = DB_connect.update_query(connect, 'INSERT INTO User (email, about, name, username, isAnonymous) VALUES (%s, %s, %s, %s, %s)', (email, about, name, username, isAnonim, )) user = DB_connect.select_query(connect, 'Select email, about, id, isAnonymous, name, username FROM User WHERE email = %s', (email, ) ) return user_description(user)
def unfollow_user(connect, follower, followee): str = DB_connect.update_query(connect, 'DELETE FROM Follow WHERE user = %s AND follow = %s', (follower, followee, ) ) if str == "Exist": raise Exception("Such row in db does not exist"); user = show_user(connect, follower) return user
def save_post(connect, date, thread, message, user, forum, optional): try: query = "INSERT INTO Post (date, thread, message, user, forum " values = "(%s, %s, %s, %s, %s" parameters = [date, thread, message, user, forum] for param in optional: query += ", " + param values += ", %s" parameters.append(optional[param]) except Exception as e: print e.message query += ") VALUES " + values + ")" # str = DB_connect.update_query(connect, query, parameters) update_thread_posts = "UPDATE Thread SET posts = posts + 1 WHERE id = %s" # DB_connect.update_query(connect, "UPDATE Thread SET posts = posts + 1 WHERE id = %s", (thread, )) # id_post = DB_connect.select_query(connect, "SELECT max(id) FROM Post", ()) con = connect with con: cursor = con.cursor() cursor.execute(update_thread_posts, (thread,)) cursor.execute(query, parameters) con.commit() id_post = cursor.lastrowid cursor.close() post = DB_connect.select_query( connect, "SELECT date, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, message, parent, thread, user \ FROM Post WHERE forum = %s and thread = %s and user = %s and id = %s", (forum, thread, user, id_post), ) response = post_description(post) return response
def users_list(connect, short_name, optional): query = "SELECT id, email, name, username, isAnonymous, about FROM User WHERE email IN (SELECT DISTINCT user FROM Post WHERE forum = %s)" params = [short_name] if "since_id" in optional: query += " AND id >= %s" params.append(optional["since"]) if "order" in optional: query += " ORDER BY name " + str(optional["order"]) else: query += " ORDER BY name DESC " if "limit" in optional: query += " LIMIT " + str(optional["limit"]) users_ids = DB_connect.select_query(connect, query, params) user_list = [] for user in users_ids: usr = { 'id' : user[0], 'email' : user[1], 'name' : user[2], 'username' : user[3], 'isAnonymous': bool(user[4]), 'about' : user[5], } if usr['isAnonymous'] == True: usr['about'] = usr['name'] = usr['username'] = None usr["followers"] = users.followers(connect, usr["email"], "user") usr["following"] = users.followers(connect, usr["email"], "follow") usr["subscriptions"] = users.subscriptions(connect, usr["email"]) user_list.append(usr) return user_list
def followers_list(connect, email, type, params): where = "user" if type == "user": where = "follow" query = "SELECT " + type + " FROM Follow JOIN User ON User.email = Follow." + type + \ " WHERE " + where + " = %s " if "since_id" in params: query += " AND User.id >= " + str(params["since_id"]) if "order" in params: query += " ORDER BY User.name " + params["order"] else: query += " ORDER BY User.name DESC " if "limit" in params: query += " LIMIT " + str(params["limit"]) followers_ids_tuple = DB_connect.select_query(connect=connect,query=query, params=(email, )) f_list = [] for id in followers_ids_tuple: id = id[0] f_list.append(show_user(connect=connect,email=id)) return f_list
def followers(connect, email, type): where = "user" if type == "user": where = "follow" f_list = DB_connect.select_query(connect, "SELECT " + type + " FROM Follow WHERE " + where + " = %s ", (email, )) return convert_to_list(f_list)
def show_user(connect, email): user = DB_connect.select_query(connect, 'Select email, about, id, isAnonymous, name, username FROM User WHERE email = %s', (email, ) ) user = user_description(user) if user is None: raise Exception("User with email " + email +" doesn't exist ") user["followers"] = followers(connect, email, "user") user["following"] = followers(connect, email, "follow") user["subscriptions"] = subscriptions(connect, email) return user
def show_forum(connect, short_name, related): forum = DB_connect.select_query(connect, 'SELECT id, name, short_name, user FROM Forum WHERE short_name = %s LIMIT 1;', (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.show_user(connect, forum["user"]) return forum
def posts_list(connect, entity, params, identifier, related=[]): query = ( "SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, likes, message, \ parent, (likes-dislikes) as points, thread, user FROM Post WHERE " + entity + " = %s " ) print ("step2") parameters = [identifier] 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 = DB_connect.select_query(connect, query, parameters) post_list = [] for post in post_ids: pf = { "date": str(post[0]), "dislikes": post[1], "forum": post[2], "id": post[3], "isApproved": bool(post[4]), "isDeleted": bool(post[5]), "isEdited": bool(post[6]), "isHighlighted": bool(post[7]), "isSpam": bool(post[8]), "likes": post[9], "message": post[10], "parent": post[11], "points": post[12], "thread": post[13], "user": post[14], } if "user" in related: pf["user"] = users.show_user(connect, pf["user"]) if "forum" in related: pf["forum"] = forums.show_forum(connect, pf["forum"], []) if "thread" in related: pf["thread"] = threads.show_thread(connect, pf["thread"], []) post_list.append(pf) return post_list
def show_thread(connect, thread, related): thread_all = DB_connect.select_query(connect, 'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, posts, (likes - dislikes) as points \ FROM Thread WHERE id = %s LIMIT 1;', (thread, ) ) if len(thread_all) == 0: raise Exception('No thread with id=' + str(thread)) thread = thread_description(thread_all) thread["dislikes"] = thread_all[0][9] thread["likes"] = thread_all[0][10] thread["posts"] = thread_all[0][11] thread["points"] = thread_all[0][12] if "user" in related: thread["user"] = users.show_user(connect, thread["user"]) if "forum" in related: thread["forum"] = forums.show_forum(connect, thread["forum"], []) return thread
def threads_list(connect, entity, params, identifier, related=[]): query = "SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, (likes - dislikes) as points, posts \ FROM Thread WHERE " + entity + " = %s " parameters = [identifier] if "since" in params: query += " AND date >= %s" parameters.append(params["since"]) if "order" in params: query += " ORDER BY date " + str(params["order"]) else: query += " ORDER BY date DESC " if "limit" in params: query += " LIMIT " + str(params["limit"]) thread_ids = DB_connect.select_query(connect, query, parameters) thread_list = [] for thread in thread_ids: thr = { 'date': str(thread[0]), 'forum': thread[1], 'id': thread[2], 'isClosed': bool(thread[3]), 'isDeleted': bool(thread[4]), 'message': thread[5], 'slug': thread[6], 'title': thread[7], 'user': thread[8], 'dislikes': thread[9], 'likes': thread[10], 'points': thread[11], 'posts': thread[12], } if "user" in related: thr["user"] = users.show_user(connect,thr["user"]) if "forum" in related: thr["forum"] = forums.show_forum(connect, thr["forum"], []) thread_list.append(thr) return thread_list
def show_post(connect, post, related): post_all = DB_connect.select_query( connect, "SELECT date, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, message, \ parent, thread, user, dislikes, likes, (likes - dislikes) as points \ FROM Post WHERE id = %s LIMIT 1;", (post,), ) if len(post_all) == 0: raise Exception("No post with id=" + str(post)) post = post_description(post_all) post["dislikes"] = post_all[0][12] post["likes"] = post_all[0][13] post["points"] = post_all[0][14] if "user" in related: print ("useeeeer") post["user"] = users.show_user(connect, post["user"]) if "thread" in related: post["thread"] = threads.show_thread(connect, post["thread"], []) if "forum" in related: post["forum"] = forums.show_forum(connect, post["forum"], []) return post
def open_close_thread(connect, thread, isClosed): DB_connect.update_query(connect,'UPDATE Thread SET isClosed = %s WHERE id = %s', (isClosed, thread,)) return { "thread": thread }
def update_thread(connect, message, slug, thread): DB_connect.update_query(connect,'UPDATE Thread SET message = %s, slug = %s WHERE id = %s', (message, slug, thread, )) return show_thread(connect,thread, [])
def subscriptions(connect, email): s_list = DB_connect.select_query(connect, "Select thread_id FROM Subscription WHERE user = %s and isDeleted = 0 ", (email, )) return convert_to_list(s_list)
def update_user(connect,email, about, name): DB_connect.update_query(connect,'UPDATE User SET email = %s, about = %s, name = %s WHERE email = %s', (email, about, name, email, )) return show_user(connect,email)
def inc_posts_count(connect,post): thread = DB_connect.select_query(connect,"SELECT thread FROM Post WHERE id = %s", (post, )) DB_connect.update_query(connect,"UPDATE Thread SET posts = posts + 1 WHERE id = %s", (thread[0][0], )) return
def update_post(connect, post, message): DB_connect.update_query(connect, "UPDATE Post SET message = %s WHERE id = %s", (message, post)) return show_post(connect, post, [])
def restore_post(connect, post): is_deleted = 0 str = DB_connect.update_query(connect, "UPDATE Post SET isDeleted = %s WHERE id = %s", (is_deleted, post)) threads.inc_posts_count(connect, post) return {"post": post}