def details(con, email): user = dbConnector.select_query( con, 'SELECT email, id, isAnonymous, name, username, about FROM user WHERE email = %s LIMIT 1', (email,) ) if len(user) == 0: raise Exception("User not found") user = user_description(user) following = dbConnector.select_query( con, 'SELECT followee FROM follower WHERE follower = %s', (email,) ) user["following"] = to_list(following) followers = dbConnector.select_query( con, 'SELECT follower FROM follower WHERE followee = %s', (email,) ) user["followers"] = to_list(followers) subscriptions = dbConnector.select_query( con, 'SELECT thread FROM subscription WHERE user = %s', (email,) ) user["subscriptions"] = to_list(subscriptions) return user
def create(con, forum, title, isClosed, user, date, message, slug, optional): isDeleted = 0 if "isDeleted" in optional: isDeleted = optional["isDeleted"] thread = dbConnector.update_query(con, '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,)) if thread == "Error": raise Exception("Thread already exists") thread = dbConnector.select_query(con, '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 details(con, id, related): thread = dbConnector.select_query( con, 'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts FROM thread USE INDEX (full_except_about) WHERE id = %s;', (id,) ) 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: thread["user"] = user.details(con, thread["user"]) if "forum" in related: thread["forum"] = forum.details(con=con, short_name=thread["forum"], related=[]) return thread
def list(con, required, optional, related): query = "SELECT date, dislikes, forum, id, isClosed, isDeleted, likes, message, points, posts, slug, title, user FROM thread WHERE " params = [] if 'forum' in required: query += "forum = %s USE INDEX (forum_full) " params.append(required["forum"][0]) # query += "forum = " + "\'" + str(required["forum"][0]) + "\'" else: query += "user = %s USE INDEX (user_full) " params.append(required["user"][0]) # query += "user = "******"\'" + str(required["user"][0]) + "\'" if 'since' in optional: params.append(optional["since"][0]) query += " AND date >= %s " if 'order' in optional: query += " ORDER BY date " + "".join(optional["order"]) if 'limit' in optional: query += " LIMIT " + "".join(optional["limit"]) try: threads = dbConnector.select_query(con, query, params) except Exception as e: print (e.message) response = [] if threads != (): for k in threads: k = { 'date': str(k[0]), # .strftime("%Y-%m-%d %H:%M:%S"), 'dislikes': k[1], 'forum': k[2], 'id': k[3], 'isClosed': bool(k[4]), 'isDeleted': bool(k[5]), 'likes': k[6], 'message': k[7], 'points': k[8], 'posts': k[9], 'slug': k[10], 'title': k[11], 'user': k[12] } if "user" in related: k["user"] = user.details(con, k["user"]) if "forum" in related: k["forum"] = forum.details(con=con, short_name=k["forum"], related=[]) response.append(k) return response
def post_query(connection, id): post = dbConnector.select_query( connection, "SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, likes, message, parent, points, thread, user FROM post WHERE id = %s ;", (id,), ) if len(post) == 0: return None post = post_formated(post) return post
def details(con, short_name, related): forum = dbConnector.select_query( con, 'SELECT id, name, short_name, user FROM forum WHERE short_name = %s', (short_name,) ) if len(forum) == 0: raise Exception("Forum " + short_name + " not found") forum = forum_description(forum) if "user" in related: forum["user"] = user.details(con, forum["user"]) return forum
def create(con, name, short_name, user): dbConnector.update_query( con, 'INSERT INTO forum (name, short_name, user) VALUES (%s, %s, %s)', (name, short_name, user) ); # check result of update query: forum = dbConnector.select_query( con, 'SELECT id, name, short_name, user FROM forum WHERE short_name = %s', (short_name,) ) return forum_description(forum);
def create(connection, name, short_name, user): dbConnector.update_query( connection, 'INSERT INTO forum (name, short_name, user) VALUES (%s, %s, %s)', (name, short_name, user) ) forum = dbConnector.select_query( connection, 'SELECT id, name, short_name, user FROM forum WHERE short_name = %s', ( short_name,) ) return serialise_f(forum)
def posts_list(connection, 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 + " = " + "'" + str("".join(identifier)) + "'" ) parameters = tuple() if "since" in params: query += " AND date >= %s" parameters += tuple(params["since"]) query += " ORDER BY date " + "".join(params["order"]) if "limit" in params: query += " LIMIT " + "".join(params["limit"]) parameters += tuple("") query_res = dbConnector.select_query(connection, query, parameters) post_list = [] for post in query_res: responseDict = { "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: responseDict["user"] = user.details(connection, responseDict["user"]) if "forum" in related: responseDict["forum"] = forum.details(connection, short_name=responseDict["forum"], related=[]) if "thread" in related: responseDict["thread"] = thread.details(connection, id=responseDict["thread"], related=[]) post_list.append(responseDict) return post_list
def posts_list(con, 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 + " = " + '\'' + str(''.join(identifier)) + '\'' parameters = tuple() if "since" in params: query += " AND date >= %s" parameters += tuple(params["since"]) query += " ORDER BY date " + ''.join(params["order"]) if "limit" in params: query += " LIMIT " + ''.join(params["limit"]) parameters += tuple('') post_ids = dbConnector.select_query(con, 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"] = user.details(con, pf["user"]) if "forum" in related: pf["forum"] = forum.details(con, short_name=pf["forum"], related=[]) if "thread" in related: pf["thread"] = thread.details(con, id=pf["thread"], related=[]) post_list.append(pf) return post_list
def subscribe(connection, user, thread): query = "INSERT INTO subscription (thread, user) VALUES (\'" + \ str(thread) + "\',\'" + str(user) + "\')" try: dbConnector.update_query(connection, query, ()) except Exception as e: print (e.message) query = "SELECT thread, user FROM subscription WHERE thread = \'" + \ str(thread) + "\', " + "user = \'" + str(user) + "\'" try: sub = dbConnector.select_query(connection, query, ()) except Exception as e: print (e.message) result = {"thread": sub[0][0], "user": sub[0][1]} return result
def forum_listUsers(con, forum_shortname, optional): query = "SELECT user.id, user.name, user.email FROM user " \ "INNER JOIN post ON user.email = post.user WHERE forum = \'" + str(forum_shortname) + "\')" if "since_id" in optional: query += " AND user.id >= " + str(optional["since_id"][0]) if "order" in optional: query += " ORDER BY user.name " + str(optional["order"][0]) if "limit" in optional: query += " LIMIT " + str(optional["limit"][0]) try: posts = dbConnector.select_query(con, query, ()) except Exception as e: print (e.message) response = [] for post in posts: res = user.details(con, str(post[2])) response.append(res) return response
def restore_remove(con, thread, isDeleted): posts = 0 if isDeleted == 0: query = "SELECT COUNT(id) FROM post WHERE thread = " + str(thread) print query posts = dbConnector.select_query(con, query, ())[0][0] query_thread = "UPDATE thread SET isDeleted = " + str(isDeleted) + ", posts = " + str(posts) + " WHERE id = " + str( thread) query_post = "UPDATE post SET isDeleted = " + str(isDeleted) + " WHERE thread = " + str(thread) try: dbConnector.update_query(con, query_thread, ()) dbConnector.update_query(con, query_post, ()) except Exception as e: print (e.message) response = { "thread": thread} return response
def listFollowers(con, email, optional): query = "SELECT followee FROM follower WHERE follower = \'" + str(email[0]) + "\'" if 'since_id' in optional: query += " AND id >= " + optional['since_id'][0] if 'order' in optional: query += " ORDER BY followee " + "".join(optional["order"][0]) if 'limit' in optional: query += " LIMIT " + "".join(optional["limit"][0]) try: followers = dbConnector.select_query(con, query, ())[0] except Exception as e: print (e.message) response = [] for follower in followers: response.append(details(con, str(follower))) return response
def create(con, username, about, name, email, optional): isAnonymous = 0; if "isAnonymous" in optional: isAnonymous = optional["isAnonymous"] user = dbConnector.update_query( con, 'INSERT INTO user (username, about, name, email, isAnonymous) VALUES (%s, %s, %s, %s, %s)', (username, about, name, email, isAnonymous,) ) if user == "Error": raise Exception("5") # check insertion user = dbConnector.select_query( con, 'SELECT id, email, about, isAnonymous, name, username FROM user WHERE email = %s', (email,) ) return user_description(user)
def status(): con = connect() response = [] tables = ['user', 'thread', 'forum', 'post'] for table in tables: currCount = len(dbConnector.select_query(con, 'SELECT id FROM ' + table, ())) response.append(currCount) statusResponse = { 'user': response[0], 'thread': response[1], 'forum': response[2], 'post': response[3] } con.close() stat = statusResponse return jsonify({ "code": STATUS_CODE['OK'], "response": stat})
def listPosts(con, email, optional): query = "SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, likes, message, parent, points, thread, user FROM post WHERE user = \'" + str( 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]) try: posts = dbConnector.select_query(con, query, ()) except Exception as e: print (e.message) if posts != (): 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 dec_posts(con, post): query = "SELECT thread FROM post WHERE id = " + str(post) thread = dbConnector.select_query(con, query, ())[0][0] query = "UPDATE thread SET posts = posts - 1 WHERE id = " + str(thread) dbConnector.update_query(con, query, ()) return