def details(email): user = sql.select_query('select email, about, isAnonymous, id, name, username FROM user WHERE email = %s LIMIT 1;', (email, )) user = user_format(user) if user is None: raise Exception("No user with email " + email) f_list = sql.select_query( "SELECT follower FROM follower WHERE followee = %s ", (email, ) ) user["followers"] = tuple2list(f_list) f_list = sql.select_query( "SELECT followee FROM follower WHERE follower = %s ", (email, ) ) user["following"] = tuple2list(f_list) user["subscriptions"] = user_subscriptions(email) return user
def details(id, related): thread = sql.select_query( 'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts ' 'FROM thread WHERE id = %s LIMIT 1;', (id, ) ) if len(thread) == 0: raise Exception('No thread exists with id=' + str(id)) 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: thread["user"] = user.details(thread["user"]) if "forum" in related: thread["forum"] = forum.details(short_name=thread["forum"], related=[]) return thread
def dec_posts_count(post): thread = sql.select_query("SELECT thread FROM post WHERE id = %s", (post, )) try: sql.update_query("UPDATE thread SET posts = posts - 1 WHERE id = %s", (thread[0][0], )) except Exception as e: print(e.message) return
def followers_list(email, type, params): if type == "follower": where = "followee" if type == "followee": where = "follower" query = "SELECT " + type + " FROM follower JOIN user ON user.email = follower." + 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 = sql.select_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 save_forum(name, short_name, user): sql.update_query('INSERT INTO forum (name, short_name, user) VALUES (%s, %s, %s)', (name, short_name, user, )) forum = sql.select_query( 'select id, name, short_name, user FROM forum WHERE short_name = %s', (short_name, ) ) return forum_description(forum)
def post_query(id): post = sql.select_query('select date, dislikes, forum, id, isApproved, isDeleted, isEdited, ' 'isHighlighted, isSpam, likes, message, parent, points, thread, user ' 'FROM post WHERE id = %s LIMIT 1;', (id, )) if len(post) == 0: return None return post_formated(post)
def followers(email, type): where = "followee" if type == "followee": where = "follower" f_list = sql.select_query( "SELECT " + type + " FROM follower WHERE " + where + " = %s ", (email, ) ) return tuple2list(f_list)
def remove_restore(thread_id, status): if status == 1: posts = 0 else: posts = sql.select_query("SELECT COUNT(id) FROM post WHERE thread = %s", (thread_id))[0][0] sql.update_query("UPDATE thread SET isDeleted = %s, posts = %s WHERE id = %s", (status,posts,thread_id)) sql.update_query("UPDATE post SET isDeleted = %s WHERE thread = %s", (status,thread_id)) response = { "thread": thread_id } return response
def details(short_name, related): forum = sql.select_query( 'select id, name, short_name, user FROM forum WHERE short_name = %s LIMIT 1;', (short_name, ) ) if len(forum) == 0: raise ("No forum with exists short_name=" + short_name) forum = forum_description(forum) if "user" in related: forum["user"] = user.details(forum["user"]) return forum
def save_user(email, username, about, name, optional): isAnonymous = 0 if "isAnonymous" in optional: isAnonymous = optional["isAnonymous"] query = sql.update_query( 'INSERT INTO user (email, about, name, username, isAnonymous) VALUES (%s, %s, %s, %s, %s)', (email, about, name, username, isAnonymous, )) if query == STATUS_CODE['ALREADY_EXISTS']: raise Exception(query) user = sql.select_query('select email, about, isAnonymous, id, name, username FROM user WHERE email = %s', (email, )) return user_format(user)
def status(): resp = [] tables = ['user', 'thread', 'forum', 'post'] for table in tables: count = len(sql.select_query('SELECT id FROM ' + table, ())) resp.append(count) response = { 'user': resp[0], 'thread': resp[1], 'forum': resp[2], 'post': resp[3] } return response
def posts_list(entity, params, identifier, related=[]): query = "SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, likes, message, " \ "parent, points, thread, user FROM post WHERE " + entity + " = %s " parameters = [identifier] if "since" in params: query += " AND date >= %s" parameters.append(params["since"]) query += " ORDER BY date " + params["order"] if "limit" in params: query += " LIMIT " + str(params["limit"]) post_ids = sql.select_query(query=query, params=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"] = user.details(pf["user"]) if "forum" in related: pf["forum"] = forum.details(short_name=pf["forum"], related=[]) if "thread" in related: pf["thread"] = thread.details(id=pf["thread"], related=[]) post_list.append(pf) return post_list
def thread_list(entity, identifier, related, params): query = "SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, 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 " + params["order"] else: query += " ORDER BY date DESC " if "limit" in params: query += " LIMIT " + str(params["limit"]) thread_ids_tuple = sql.select_query(query=query, params=parameters) thread_list = [] for thread in thread_ids_tuple: 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: thread["user"] = user.details(thread["user"]) if "forum" in related: thread["forum"] = forum.details(short_name=thread["forum"], related=[]) thread_list.append(thread) return thread_list
def save_thread(forum, title, isClosed, user, date, message, slug, optional): isDeleted = 0 if "isDeleted" in optional: isDeleted = optional["isDeleted"] sql.update_query('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, )) thread = sql.select_query( 'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts ' 'FROM thread WHERE slug = %s', (slug, ) ) thread = thread[0] response = { '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], } return response
def inc_posts_count(post): thread = sql.select_query("SELECT thread FROM post WHERE id = %s", (post, )) sql.update_query("UPDATE thread SET posts = posts + 1 WHERE id = %s", (thread[0][0], )) return
def user_subscriptions(email): s_list = [] subscriptions = sql.select_query('select thread FROM subscription WHERE user = %s', (email, )) for el in subscriptions: s_list.append(el[0]) return s_list