def restore_remove(connection, thread, isDeleted): posts = 0 current_state = db_tools.execute_select(connection, "SELECT isDeleted FROM Threads WHERE id = %s", (thread,))[0][0] if current_state != isDeleted: if isDeleted == 0: query = 'SELECT COUNT(id) FROM Posts WHERE thread = %s' params = (thread, ) posts = db_tools.execute_select(connection, query, params)[0][0] query_thread = 'UPDATE Threads SET isDeleted = %s, posts = %s WHERE id = %s' query_post = 'UPDATE Posts SET isDeleted = %s WHERE thread = %s' params_thread = (isDeleted, posts, thread, ) try: db_tools.execute_update(connection, query_thread, params_thread) db_tools.execute_update(connection, query_post, (isDeleted, thread, )) except Exception as e: print (e.message) response = {"thread": thread} return response
def create(connection, username, about, name, email, optional): isAnonymous = 0 if "isAnonymous" in optional: isAnonymous = optional["isAnonymous"] query = 'INSERT INTO Users (username, about, name, email, isAnonymous) VALUES (%s, %s, %s, %s, %s)' params = ( username, about, name, email, isAnonymous, ) inserted_id = db_tools.execute_update(connection, query, params) if inserted_id == "Error": raise Exception("5") user = db_tools.execute_select( connection, 'SELECT id, email, about, isAnonymous, name, username FROM Users WHERE email = %s', (email, )) return serialize_u(user)
def list(connection, required, optional, related): query = "SELECT date, dislikes, forum, id, isClosed, isDeleted, likes, message, points, posts, slug, title, user FROM Threads WHERE " threads = [] if 'forum' in required: query += "forum = " + "\'" + str(required["forum"][0]) + "\'" if 'user' in required: query += "user = "******"\'" + str(required["user"][0]) + "\'" if 'since' in optional: since = optional["since"][0] query += " AND date >= " + "\'" + str(since) + "\'" if 'order' in optional: order = optional["order"][0] query += " ORDER BY date " + "".join(optional["order"]) if 'limit' in optional: limit = optional["limit"][0] query += " LIMIT " + "".join(optional["limit"]) try: threads = db_tools.execute_select(connection, query, ()) except Exception as e: print (e.message) response = [] if threads != (): for thread in threads: thread = { 'date': str(thread[0]), 'dislikes': thread[1], 'forum': thread[2], 'id': thread[3], 'isClosed': bool(thread[4]), 'isDeleted': bool(thread[5]), 'likes': thread[6], 'message': thread[7], 'points': thread[8], 'posts': thread[9], 'slug': thread[10], 'title': thread[11], 'user': thread[12] } if "user" in related: try: thread["user"] = user_tools.details(connection, thread["user"]) except Exception: pass if "forum" in related: thread["forum"] = forum_tools.details(connection, thread["forum"], []) response.append(thread) return response
def dec_posts(connection, post): query = 'SELECT thread FROM Posts WHERE id = %s' params = (post, ) thread = db_tools.execute_select(connection, query, params)[0][0] query = 'UPDATE Threads SET posts = posts - 1 WHERE id = %s' params = (thread, ) db_tools.execute_update(connection, query, params) return
def set_post_path(connection, post, post_id): update_path = "UPDATE Posts SET path = %s WHERE id = %s" parent_path = "SELECT path FROM Posts WHERE id = %s" if post["parent"] is None: path = enlength(str(post_id)) else: parent_path = db_tools.execute_select(connection, parent_path, (post["parent"],)) path = "".join(parent_path[0]) + "." + enlength(str(post_id)) db_tools.execute_update(connection, update_path, (path, post_id,))
def post_query(connection, id): post = db_tools.execute_select( connection, '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: return None post = serialize_f(post) return post
def create(connection, name, short_name, user): query = 'INSERT INTO Forums (name, short_name, user) VALUES (%s, %s, %s)' params = (name, short_name, user, ) inserted_id = db_tools.execute_update(connection, query, params) query = 'SELECT id, name, short_name, user FROM Forums WHERE short_name = %s' params = (short_name, ) forum = db_tools.execute_select(connection, query, params) response = serialize_f(forum) return response
def list_subsriptions(connection, user_email): query = 'SELECT thread FROM Subscribe WHERE user = %s' params = (user_email, ) subscriptions = db_tools.execute_select(connection, query, params) response = [] if len(subscriptions) == 0: return response for sub in subscriptions: response.append(str(sub)) return response
def details(connection, user_email): query = 'SELECT id, email, about, isAnonymous, name, username FROM Users WHERE email = %s' params = (user_email, ) user = db_tools.execute_select(connection, query, params) if len(user) == 0: raise Exception("User not found") user = serialize_u(user) query = 'SELECT followee FROM Follow WHERE follower = %s' following = db_tools.execute_select(connection, query, params) user["following"] = to_list(following) query = 'SELECT follower FROM Follow WHERE followee = %s' followers = db_tools.execute_select(connection, query, params) user["followers"] = to_list(followers) query = 'SELECT thread FROM Subscribe WHERE user = %s' subscriptions = db_tools.execute_select(connection, query, params) user["subscriptions"] = to_list(subscriptions) return user
def details(connection, short_name, optional): query = 'SELECT id, name, short_name, user FROM Forums WHERE short_name = %s' params = (short_name, ) forum = db_tools.execute_select(connection, query, params) if len(forum) == 0: raise Exception("Forum not found") forum = serialize_f(forum) if "user" in optional: try: forum["user"] = user_tools.details(connection, forum["user"]) except Exception: pass return forum
def restore_remove(connection, post, isDeleted): current_state = db_tools.execute_select(connection, "SELECT isDeleted FROM Posts WHERE id = %s", (post,))[0][0] query = "UPDATE Posts SET isDeleted = %s WHERE id = %s" params = (isDeleted, post) if current_state != isDeleted: if isDeleted == 0: thread_tools.inc_posts(connection, post) else: thread_tools.dec_posts(connection, post) db_tools.execute_update(connection, query, params) response = {"post": post} return response
def subscribe(connection, user, thread): query = 'INSERT INTO Subscribe (thread, user) VALUES (%s, %s)' params = (thread, user, ) subscriptions = [] try: db_tools.execute_update(connection, query, params) except Exception as e: print (e.message) query = 'SELECT thread, user FROM Subscribe WHERE thread = %s AND user = %s' try: subscriptions = db_tools.execute_select(connection, query, params) except Exception as e: print (e.message) result = {"thread": subscriptions[0][0], "user": subscriptions[0][1]} return result
def details(connection, id, related): query = 'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts FROM Threads WHERE id = %s' params = (id, ) thread = db_tools.execute_select(connection, query, params) if len(thread) == 0: raise Exception('Thread not founded') thread = thread[0] thread = { '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: try: thread["user"] = user_tools.details(connection, thread["user"]) except Exception: pass if "forum" in related: try: thread["forum"] = forum_tools.details(connection, short_name=thread["forum"], optional=[]) except Exception: pass return thread
def list_users(connection, short_name, optional): query = "SELECT id, name, email FROM Users WHERE email IN (SELECT DISTINCT user FROM Posts WHERE forum = %s)" params = (str(short_name), ) response = [] if len(optional) != 0: if "since_id" in optional: query += " AND Users.id >= " + str(optional["since_id"][0]) if "order" in optional: query += " ORDER BY name " + str(optional["order"][0]) if "limit" in optional: query += " LIMIT " + str(optional["limit"][0]) posts = db_tools.execute_select(connection, query, params) if posts is None or len(posts) == 0: return response for post in posts: res = user_tools.details(connection, str(post[2])) response.append(res) return response
def list_posts(connection, user_email, optional): query = 'SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, likes, ' \ 'message, parent, points, thread, user FROM Posts WHERE user = %s' params = (user_email[0], ) if 'since' in optional: query += " AND date >= " + "\'" + optional['since'][0] + "\'" if 'order' in optional: query += " ORDER BY date " + "".join(optional["order"][0]) if 'limit' in optional: query += " LIMIT " + "".join(optional["limit"][0]) posts = db_tools.execute_select(connection, query, params) if len(posts) != 0: posts = posts[0] posts = { 'date': posts[0].strftime("%Y-%m-%d %H:%M:%S"), 'dislikes': posts[1], 'forum': posts[2], 'id': posts[3], 'isApproved': posts[4], 'isDeleted': posts[5], 'isEdited': posts[6], 'isHighlighted': posts[7], 'isSpam': posts[8], 'likes': posts[9], 'message': posts[10], 'parent': posts[11], 'points': posts[12], 'thread': posts[13], 'user': posts[14] } return posts
def list_followees(connection, user_email, optional): query = 'SELECT followee FROM Follow WHERE follower = %s' params = (user_email[0], ) response = [] if len(optional) != 0: if 'since_id' in optional: query += " AND id >= " + optional['since_id'][0] if 'order' in optional: query += " ORDER BY followee " + optional["order"][0] if 'limit' in optional: query += " LIMIT " + optional["limit"][0] followees = db_tools.execute_select(connection, query, params) if followees is None or len(followees) == 0: return response for followee in followees: response.append(details(connection, followee[0])) return response
def posts_list(connection, entity, params, identifier, related=[]): query = "SELECT * FROM Posts WHERE " + \ entity + " = " + '\'' + str(''.join(identifier)) + '\'' sort = 'flat' order = 'desc' limit = 0 parameters = tuple() if "since" in params: query += " AND date >= %s" parameters += tuple(params["since"]) if "sort" in params: sort = check_sort(params["sort"][0]) if "order" in params: order = check_order(params["order"][0]) sort_order_stmt = make_sort_statement(sort, order) query += sort_order_stmt if "limit" in params: limit = int(str(params["limit"][0])) query += " LIMIT " + ''.join(params["limit"]) posts = db_tools.execute_select(connection, query, (parameters)) if sort != 'flat': posts = get_child_posts(connection, posts,sort, limit) post_list = [] for post in posts: parent = post[9] if post[9] == 0: parent = None responseDict = { 'date': str(post[4]), 'dislikes': post[6], 'forum': post[3], 'id': post[0], 'isApproved': bool(post[12]), 'isDeleted': bool(post[15]), 'isEdited': bool(post[13]), 'isHighlighted': bool(post[11]), 'isSpam': bool(post[14]), 'likes': post[7], 'message': post[5], 'parent': parent, 'points': post[8], 'thread': post[1], 'user': post[2], } if "user" in related: try: responseDict["user"] = user_tools.details(connection, responseDict["user"]) except Exception: pass if "forum" in related: try: responseDict["forum"] = forum_tools.details(connection, responseDict["forum"],[]) except Exception: pass if "thread" in related: try: responseDict["thread"] = thread_tools.details(connection, id=responseDict["thread"], related=[]) except Exception: pass post_list.append(responseDict) return post_list