def details(email): user = dbFunctions.select_query( 'SELECT email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, ) ) user = user[0] user_response = { 'about': user[1], 'email': user[0], 'id': user[3], 'isAnonymous': bool(user[2]), 'name': user[4], 'username': user[5] } user_response["followers"] = followers(email, "follower") user_response["following"] = followers(email, "followee") subs_list = [] subscriptions = dbFunctions.select_query( 'SELECT thread FROM Subscriptions WHERE user = %s', (email, ) ) for i in subscriptions: subs_list.append(i[0]) user_response["subscriptions"] = subs_list return user_response
def save_user(email, username, about, name, optional): isAnonymous = 0 if "isAnonymous" in optional: isAnonymous = optional["isAnonymous"] try: user = dbFunctions.select_query( 'select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, )) if len(user) == 0: dbFunctions.change_query( 'INSERT INTO Users (email, about, name, username, isAnonymous) VALUES (%s, %s, %s, %s, %s)', ( email, about, name, username, isAnonymous, )) user = dbFunctions.select_query( 'SELECT email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, )) except Exception as e: raise Exception(e.message) user = user[0] user_response = { 'about': user[1], 'email': user[0], 'id': user[3], 'isAnonymous': bool(user[2]), 'name': user[4], 'username': user[5] } return user_response
def save_user(email, username, about, name, optional): isAnonymous = 0 if "isAnonymous" in optional: isAnonymous = optional["isAnonymous"] try: user = dbFunctions.select_query('select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, )) if len(user) == 0: dbFunctions.change_query( 'INSERT INTO Users (email, about, name, username, isAnonymous) VALUES (%s, %s, %s, %s, %s)', (email, about, name, username, isAnonymous, )) user = dbFunctions.select_query( 'SELECT email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, ) ) except Exception as e: raise Exception(e.message) user = user[0] user_response = { 'about': user[1], 'email': user[0], 'id': user[3], 'isAnonymous': bool(user[2]), 'name': user[4], 'username': user[5] } return user_response
def new_thread(forum, title, isClosed, user, date, message, slug, optional): isDeleted = 0 if "isDeleted" in optional: isDeleted = optional["isDeleted"] thread = dbFunctions.select_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: dbFunctions.change_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 = dbFunctions.select_query( 'select date, forum, id, isClosed, isDeleted, message, slug, title, user ' 'FROM Threads 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 details(id, related): thread = dbFunctions.select_query( 'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts ' 'FROM Threads WHERE id = %s', (id, ) ) thread = thread[0] thread_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], 'dislikes': thread[9], 'likes': thread[10], 'points': thread[11], 'posts': thread[12], } if "user" in related: thread_response["user"] = userFunctions.details(thread["user"]) if "forum" in related: thread_response["forum"] = forumFunctions.details(short_name=thread["forum"], related=[]) return thread_response
def add_subscribe(email, thread_id): subscription = dbFunctions.select_query( 'SELECT thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, ) ) if len(subscription) == 0: dbFunctions.change_query( 'INSERT INTO Subscriptions (thread, user) VALUES (%s, %s)', (thread_id, email, ) ) subscription = dbFunctions.select_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 details(details_id, related): post = dbFunctions.select_query( 'SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, ' 'isHighlighted, isSpam, likes, message, parent, points, thread, user ' 'FROM Posts WHERE id = %s', (details_id, ) ) post = post[0] post_response = { '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: post_response["user"] = userFunctions.details(post["user"]) if "forum" in related: post_response["forum"] = forumFunctions.details(short_name=post["forum"], related=[]) if "thread" in related: post_response["thread"] = threadFunctions.details(id=post["thread"], related=[]) return post
def details(id, related): thread = dbFunctions.select_query( 'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts ' 'FROM Threads WHERE id = %s', (id, )) thread = thread[0] thread_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], 'dislikes': thread[9], 'likes': thread[10], 'points': thread[11], 'posts': thread[12], } if "user" in related: thread_response["user"] = userFunctions.details(thread["user"]) if "forum" in related: thread_response["forum"] = forumFunctions.details( short_name=thread["forum"], related=[]) return thread_response
def new_post(date, thread, message, user, forum, optional): if len(dbFunctions.select_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 с айдишником = " + str(thread) + " в форуме " + forum) if "parent" in optional: if len(dbFunctions.select_query("SELECT Posts.id FROM Posts JOIN Threads ON Threads.id = Posts.thread " "WHERE Posts.id = %s AND Threads.id = %s", (optional["parent"], thread, ))) == 0: raise Exception("Нет поста с айдишником = " + optional["parent"]) query = "INSERT INTO Posts (message, user, forum, thread, date" values = "(%s, %s, %s, %s, %s" parameters = [message, user, forum, thread, date] for param in optional: query += ", " + param values += ", %s" parameters.append(optional[param]) query += ") VALUES " + values + ")" update_posts = "UPDATE Threads SET posts = posts + 1 WHERE id = %s" dbFunctions.change_query(update_posts, (thread, )) post_id = dbFunctions.change_query(query, parameters) post = dbFunctions.select_query('SELECT date, forum, id, isApproved, isDeleted, isEdited, ' 'isHighlighted, isSpam, message, thread, user ' 'FROM Posts WHERE id = %s', (post_id, )) post = post[0] post_response = { 'date': str(post[0]), 'forum': post[1], 'id': post[2], 'isApproved': bool(post[3]), 'isDeleted': bool(post[4]), 'isEdited': bool(post[5]), 'isHighlighted': bool(post[6]), 'isSpam': bool(post[7]), 'message': post[8], 'thread': post[9], 'user': post[10], } return post_response
def add_follow(email1, email2): follower = dbFunctions.select_query( 'SELECT id FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, ) ) if len(follower) == 0: dbFunctions.change_query('INSERT INTO Followers (follower, followee) VALUES (%s, %s)', (email1, email2, )) user = details(email1) return user
def delete_subscription(email, thread_id): subscription = dbFunctions.select_query( 'SELECT thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, ) ) dbFunctions.change_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 add_subscribe(email, thread_id): subscription = dbFunctions.select_query( 'SELECT thread, user FROM Subscriptions WHERE user = %s AND thread = %s', ( email, thread_id, )) if len(subscription) == 0: dbFunctions.change_query( 'INSERT INTO Subscriptions (thread, user) VALUES (%s, %s)', ( thread_id, email, )) subscription = dbFunctions.select_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 followers(email, type): if type == "follower": where = "followee" if type == "followee": where = "follower" f_list = dbFunctions.select_query( "SELECT " + type + " FROM Followers JOIN Users ON Users.email = Followers." + type + " WHERE " + where + " = %s ", (email, ) ) l = [] for el in f_list: l.append(el[0]) return l
def remove_follow(email1, email2): follows = dbFunctions.select_query( 'SELECT id FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, ) ) if len(follows) != 0: dbFunctions.change_query( 'DELETE FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, ) ) else: raise Exception("Упс!") return details(email1)
def followers(email, type): if type == "follower": where = "followee" if type == "followee": where = "follower" f_list = dbFunctions.select_query( "SELECT " + type + " FROM Followers JOIN Users ON Users.email = Followers." + type + " WHERE " + where + " = %s ", (email, )) l = [] for el in f_list: l.append(el[0]) return l
def new_forum(name, short_name, user): """ Создание нового форума """ dbFunctions.exist(entity="Users", identifier="email", value=user) forum = dbFunctions.select_query( 'SELECT id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, ) ) if len(forum) == 0: dbFunctions.change_query('INSERT INTO Forums (name, short_name, user) VALUES (%s, %s, %s)', (name, short_name, user, )) forum = dbFunctions.select_query( 'SELECT id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, ) ) forum = forum[0] response = { 'id': forum[0], 'name': forum[1], 'short_name': forum[2], 'user': forum[3] } return response
def delete_subscription(email, thread_id): subscription = dbFunctions.select_query( 'SELECT thread, user FROM Subscriptions WHERE user = %s AND thread = %s', ( email, thread_id, )) dbFunctions.change_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 new_thread(forum, title, isClosed, user, date, message, slug, optional): isDeleted = 0 if "isDeleted" in optional: isDeleted = optional["isDeleted"] thread = dbFunctions.select_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: dbFunctions.change_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 = dbFunctions.select_query( 'select date, forum, id, isClosed, isDeleted, message, slug, title, user ' 'FROM Threads 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 details(email): user = dbFunctions.select_query( 'SELECT email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, )) user = user[0] user_response = { 'about': user[1], 'email': user[0], 'id': user[3], 'isAnonymous': bool(user[2]), 'name': user[4], 'username': user[5] } user_response["followers"] = followers(email, "follower") user_response["following"] = followers(email, "followee") subs_list = [] subscriptions = dbFunctions.select_query( 'SELECT thread FROM Subscriptions WHERE user = %s', (email, )) for i in subscriptions: subs_list.append(i[0]) user_response["subscriptions"] = subs_list return user_response
def add_follow(email1, email2): follower = dbFunctions.select_query( 'SELECT id FROM Followers WHERE follower = %s AND followee = %s', ( email1, email2, )) if len(follower) == 0: dbFunctions.change_query( 'INSERT INTO Followers (follower, followee) VALUES (%s, %s)', ( email1, email2, )) user = details(email1) return user
def remove_follow(email1, email2): follows = dbFunctions.select_query( 'SELECT id FROM Followers WHERE follower = %s AND followee = %s', ( email1, email2, )) if len(follows) != 0: dbFunctions.change_query( 'DELETE FROM Followers WHERE follower = %s AND followee = %s', ( email1, email2, )) else: raise Exception("Упс!") return details(email1)
def posts_list(entity, params, identifier, related=[]): query = "SELECT id FROM Posts 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"]) post_ids = dbFunctions.select_query(query=query, params=parameters) post_list = [] for id in post_ids: id = id[0] post_list.append(details(details_id=id, related=related)) return post_list
def list_users(short_name, optional): """ """ 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 = dbFunctions.select_query(query, (short_name, )) list_u = [] for user in users: user = user[0] list_u.append(users.details(user)) return list_u
def forum_details(short_name, related): """ Подробности о форуме """ forum = dbFunctions.select_query( 'SELECT id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, ) ) if len(forum) == 0: raise ("Нет форума с именем=" + short_name) forum = forum[0] response = { 'id': forum[0], 'name': forum[1], 'short_name': forum[2], 'user': forum[3] } if "user" in related: response["user"] = userFunctions.details(forum["user"]) return forum
def threads_list(entity, identifier, related, params): query = "SELECT id FROM Threads 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 = dbFunctions.select_query(query=query, params=parameters) thread_l = [] for id in thread_ids: id = id[0] thread_l.append(details(id=id, related=related)) return thread_l
def followers_list(email, type, params): 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_id = dbFunctions.select_query(query=query, params=(email, )) list_f = [] for id in followers_id: id = id[0] list_f.append(details(email=id)) return list_f