def user_details(): data = get_params(request) email = data.get('user') if not email: return_data = {"code": 2, "response": RESPONSE_CODE_2} return jsonify(return_data) user = dictionary_of_user(email) params = {} sql = db.execute_get("SELECT thread FROM Subscription WHERE subscriber = '%s'" % (email), params) subscribed_list = list() for thread in sql: subscribed_list.append(thread[0]) user['followers'] = followers_list(email) user['following'] = following_list(email) user['subscriptions'] = subscribed_list return_data = {"code": 0, "response": user} return jsonify(return_data)
def status(): params = {} user_count = db.execute_get("SELECT count(*) FROM User", params) thread_count = db.execute_get("SELECT count(*) FROM Thread", params) forum_count = db.execute_get("SELECT count(*) FROM Forum", params) post_count = db.execute_get("SELECT count(*) FROM Post", params) users = user_count[0][0] threads = thread_count[0][0] forums = forum_count[0][0] posts = post_count[0][0] return_data = {"code": 0, "response": {"user": users, "thread": threads, "forum": forums, "post": posts}} return jsonify(return_data)
def following_list(email): params = {} sql = db.execute_get("SELECT following FROM Follower WHERE follower = '%s'" % (email), params) if not sql: return list() return sql[0]
def get_thread_list(title="", forum="", user="", since="", limit=-1, order="desc"): if title != "": where_sql = "title = '{}'".format(title) elif forum != "": where_sql = "forum = '{}'".format(forum) elif user != "": where_sql = "user = '******'".format(user) else: return list() since_sql = "" if since != "": since_sql = "AND date >= '%s'" % (since) if order != 'asc' and order != 'desc': return list() order_sql = """ ORDER BY date {}""".format(order) limit_sql = "" if limit != -1: try: limit = int(limit) except ValueError: return list() if limit < 0: return list() limit_sql = "LIMIT %d" % (int(limit)) sql = "SELECT thread, title, user, message, forum, isDeleted, isClosed, date, slug, likes, dislikes, \ points, posts FROM Thread WHERE {where_value} {since_value} {order_value} {limit_value}".format( where_value=where_sql, since_value=since_sql, order_value=order_sql, limit_value=limit_sql) params = {} thread_list_sql = db.execute_get(sql, params) if not thread_list_sql: return list() thread_list = list() for thread_sql in thread_list_sql: thread_list.append({'id': str_to_json(thread_sql[0]), 'title': str_to_json(thread_sql[1]), 'user': str_to_json(thread_sql[2]), 'message': str_to_json(thread_sql[3]), 'forum': str_to_json(thread_sql[4]), 'isDeleted': str_to_json(thread_sql[5], True), 'isClosed': str_to_json(thread_sql[6], True), 'date': thread_sql[7].strftime('%Y-%m-%d %H:%M:%S'), 'slug': str_to_json(thread_sql[8]), 'likes': str_to_json(thread_sql[9]), 'dislikes': str_to_json(thread_sql[10]), 'points': str_to_json(thread_sql[11]), 'posts': str_to_json(thread_sql[12])}) return thread_list
def get_subscribed_list(email): params = {} sql = db.execute_get("SELECT thread FROM Subscription WHERE subscriber = '%s'" % (email), params) result = list() for thread in sql: result.append(thread[0]) return result
def dictionary_of_forum(short_name): params = {} sql = "SELECT forum, name, short_name, user FROM Forum WHERE short_name = '%s' LIMIT 1" % (short_name) sql = db.execute_get(sql, params) if not sql: return dict() sql = sql[0] return {'id': str_to_json(sql[0]),'name': str_to_json(sql[1]),'short_name': str_to_json(sql[2]),'user': str_to_json(sql[3])}
def dictionary_of_user(email): params = {} user_list_sql = db.execute_get("SELECT user, email, name, username, isAnonymous, about FROM User WHERE email = '%s'" % (email), params) if not user_list_sql: return dict() sql = user_list_sql[0] return {'id': str_to_json(sql[0]),'email': str_to_json(sql[1]),'name': str_to_json(sql[2]), 'username': str_to_json(sql[3]),'isAnonymous': str_to_json(sql[4], True),'about': str_to_json(sql[5])}
def get_subscribed_list(email): params = {} sql = db.execute_get( "SELECT thread FROM Subscription WHERE subscriber = '%s'" % (email), params) result = list() for thread in sql: result.append(thread[0]) return result
def get_id_thread(id_value): params = {} sql ="SELECT thread, title, user, message, forum, isDeleted, isClosed, date, slug, likes, dislikes, points, posts FROM Thread WHERE thread = '%s' LIMIT 1" % (id_value) thread_list_sql = db.execute_get(sql, params) if not thread_list_sql: return list() thread_sql = thread_list_sql[0] return {'id': str_to_json(thread_sql[0]), 'title': str_to_json(thread_sql[1]), 'user': str_to_json(thread_sql[2]), 'message': str_to_json(thread_sql[3]), 'forum': str_to_json(thread_sql[4]), 'isDeleted': str_to_json(thread_sql[5], True), 'isClosed': str_to_json(thread_sql[6], True), 'date': thread_sql[7].strftime('%Y-%m-%d %H:%M:%S'), 'slug': str_to_json(thread_sql[8]), 'likes': str_to_json(thread_sql[9]), 'dislikes': str_to_json(thread_sql[10]), 'points': str_to_json(thread_sql[11]), 'posts': str_to_json(thread_sql[12])}
def dictionary_of_forum(short_name): params = {} sql = "SELECT forum, name, short_name, user FROM Forum WHERE short_name = '%s' LIMIT 1" % ( short_name) sql = db.execute_get(sql, params) if not sql: return dict() sql = sql[0] return { 'id': str_to_json(sql[0]), 'name': str_to_json(sql[1]), 'short_name': str_to_json(sql[2]), 'user': str_to_json(sql[3]) }
def dictionary_of_user(email): params = {} user_list_sql = db.execute_get( "SELECT user, email, name, username, isAnonymous, about FROM User WHERE email = '%s'" % (email), params) if not user_list_sql: return dict() sql = user_list_sql[0] return { 'id': str_to_json(sql[0]), 'email': str_to_json(sql[1]), 'name': str_to_json(sql[2]), 'username': str_to_json(sql[3]), 'isAnonymous': str_to_json(sql[4], True), 'about': str_to_json(sql[5]) }
def get_id_post(id_value): where_sql = "post = {}".format(id_value) sql = """SELECT post, user, thread, forum, message, parent, date, likes, dislikes, points, \ isSpam, isEdited, isDeleted, isHighlighted, isApproved FROM Post \ WHERE post = %(id)s LIMIT 1;""".format(where_value=where_sql) params = {'id': id_value} post_list_sql = db.execute_get(sql, params) if not post_list_sql: return list() post_sql = post_list_sql[0] return {'id': str_to_json(post_sql[0]), 'user': str_to_json(post_sql[1]), 'thread': str_to_json(post_sql[2]), 'forum': str_to_json(post_sql[3]), 'message': str_to_json(post_sql[4]), 'parent': str_to_json(post_sql[5]), 'date': post_sql[6].strftime('%Y-%m-%d %H:%M:%S'), 'likes': str_to_json(post_sql[7]), 'dislikes': str_to_json(post_sql[8]), 'points': str_to_json(post_sql[9]), 'isSpam': str_to_json(post_sql[10], True), 'isEdited': str_to_json(post_sql[11], True), 'isDeleted': str_to_json(post_sql[12], True), 'isHighlighted': str_to_json(post_sql[13], True), 'isApproved': str_to_json(post_sql[14], True)}
def list_following(): data = get_params(request) email = data.get('user') if not email: return_data = {"code": 2, "response": RESPONSE_CODE_2} return jsonify(return_data) since_id = data.get('since_id', -1) if since_id != -1: since_sql = "AND User.user >= '%s'" % (since_id) else: since_sql = "" order_part = data.get('order', 'desc') order_sql = """ORDER BY User.name {}""".format(order_part) limit = data.get('limit', -1) if limit != -1: try: limit = int(limit) except ValueError: return_data = {"code": 3, "response": RESPONSE_CODE_3} return jsonify(return_data) if limit < 0: return_data = {"code": 3, "response": RESPONSE_CODE_3} return jsonify(return_data) limit_sql = "LIMIT %d" % (int(limit)) else: limit_sql = "" sql = """SELECT about, email, user, isAnonymous, name, username FROM User JOIN Follower ON Follower.following = User.email WHERE Follower.follower""" sql += """ = %(email)s {since_value} {order_value} {limit_value};""".format(since_value=since_sql, order_value=order_sql, limit_value=limit_sql) params = {'email': email} user_list_sql = db.execute_get(sql, params) if not user_list_sql: return_data = {"code": 1, "response": RESPONSE_CODE_1} return jsonify(return_data) user_list = list() for user_sql in user_list_sql: follower_email = str_to_json(user_sql[1]) user_list.append({'about': str_to_json(user_sql[0]), 'email': follower_email, 'id': str_to_json(user_sql[2]), 'isAnonymous': str_to_json(user_sql[3]), 'name': str_to_json(user_sql[4]), 'username': str_to_json(user_sql[5]), 'followers': followers_list(follower_email), 'following': following_list(follower_email), 'subscriptions': get_subscribed_list(follower_email)}) return_data = {"code": 0, "response": user_list} return jsonify(return_data)
limit_sql = "LIMIT %d" % (int(limit)) else: limit_sql = '' order_part = data.get('order', 'desc') order_sql = """ORDER BY User.name {}""".format(order_part) sql = """SELECT User.user, User.email, User.name, User.username, User.isAnonymous, User.about FROM User \ WHERE User.email IN (SELECT DISTINCT user FROM Post WHERE forum = %(forum)s) {snc_sql} {ord_sql} \ {lim_sql};""".format(snc_sql=since_id_sql, lim_sql=limit_sql, ord_sql=order_sql) params = {'forum': data.get('forum')} user_list_sql = db.execute_get(sql, params) user_list = list() for user_sql in user_list_sql: email = str_to_json(user_sql[1]) user_list.append({'id': str_to_json(user_sql[0]), 'email': email, 'name': str_to_json(user_sql[2]), 'username': str_to_json(user_sql[3]), 'isAnonymous': str_to_json(user_sql[4]), 'about': str_to_json(user_sql[5]), 'subscriptions': get_subscribed_list(email)}) return_data = {"code": 0, "response": user_list} return jsonify(return_data) ##### Thread
def get_post_list(user="", forum="", thread="", since="", limit=-1, sort='flat', order='desc'): if forum != "": where_sql = "forum = '{}'".format(forum) elif thread != "": where_sql = "thread = {}".format(thread) elif user != "": where_sql = "user = '******'".format(user) else: return list() since_sql = "" if since != "": since_sql = "AND date >= '%s'" % (since) if sort != 'flat' and sort != 'tree' and sort != 'parent_tree': return list() #sort_sql = """ORDER BY Post.date {}""".format(sort) sort_sql = """""" limit_sql = "" if limit != -1: try: limit = int(limit) except ValueError: return list() if limit < 0: return list() limit_sql = "LIMIT %d" % (int(limit)) if order != 'asc' and order != 'desc': return json.dumps({"code": 3, "response": RESPONSE_CODE_3}) order_sql = """ORDER BY date {}""".format(order) sql = "SELECT post, user, thread, forum, message, parent, date, likes, dislikes, points, \ isSpam, isEdited, isDeleted, isHighlighted, isApproved FROM Post \ WHERE {where_value} {since_value} {order_value} {sort_value} {limit_value}".format( where_value=where_sql, since_value=since_sql, limit_value=limit_sql, order_value=order_sql, sort_value=sort_sql) params = {} post_list_sql = db.execute_get(sql, params) if not post_list_sql: return list() post_list = list() for post_sql in post_list_sql: post_list.append({ 'id': str_to_json(post_sql[0]), 'user': str_to_json(post_sql[1]), 'thread': str_to_json(post_sql[2]), 'forum': str_to_json(post_sql[3]), 'message': str_to_json(post_sql[4]), 'parent': str_to_json(post_sql[5]), 'date': post_sql[6].strftime('%Y-%m-%d %H:%M:%S'), 'likes': str_to_json(post_sql[7]), 'dislikes': str_to_json(post_sql[8]), 'points': str_to_json(post_sql[9]), 'isSpam': str_to_json(post_sql[10], True), 'isEdited': str_to_json(post_sql[11], True), 'isDeleted': str_to_json(post_sql[12], True), 'isHighlighted': str_to_json(post_sql[13], True), 'isApproved': str_to_json(post_sql[14], True) }) return post_list
def get_thread_list(title="", forum="", user="", since="", limit=-1, order="desc"): if title != "": where_sql = "title = '{}'".format(title) elif forum != "": where_sql = "forum = '{}'".format(forum) elif user != "": where_sql = "user = '******'".format(user) else: return list() since_sql = "" if since != "": since_sql = "AND date >= '%s'" % (since) if order != 'asc' and order != 'desc': return list() order_sql = """ ORDER BY date {}""".format(order) limit_sql = "" if limit != -1: try: limit = int(limit) except ValueError: return list() if limit < 0: return list() limit_sql = "LIMIT %d" % (int(limit)) sql = "SELECT thread, title, user, message, forum, isDeleted, isClosed, date, slug, likes, dislikes, \ points, posts FROM Thread WHERE {where_value} {since_value} {order_value} {limit_value}".format( where_value=where_sql, since_value=since_sql, order_value=order_sql, limit_value=limit_sql) params = {} thread_list_sql = db.execute_get(sql, params) if not thread_list_sql: return list() thread_list = list() for thread_sql in thread_list_sql: thread_list.append({ 'id': str_to_json(thread_sql[0]), 'title': str_to_json(thread_sql[1]), 'user': str_to_json(thread_sql[2]), 'message': str_to_json(thread_sql[3]), 'forum': str_to_json(thread_sql[4]), 'isDeleted': str_to_json(thread_sql[5], True), 'isClosed': str_to_json(thread_sql[6], True), 'date': thread_sql[7].strftime('%Y-%m-%d %H:%M:%S'), 'slug': str_to_json(thread_sql[8]), 'likes': str_to_json(thread_sql[9]), 'dislikes': str_to_json(thread_sql[10]), 'points': str_to_json(thread_sql[11]), 'posts': str_to_json(thread_sql[12]) }) return thread_list
def get_post_list(user="", forum="", thread="", since="", limit=-1, sort='flat', order='desc'): if forum != "": where_sql = "forum = '{}'".format(forum) elif thread != "": where_sql = "thread = {}".format(thread) elif user != "": where_sql = "user = '******'".format(user) else: return list() since_sql = "" if since != "": since_sql = "AND date >= '%s'" % (since) if sort != 'flat' and sort != 'tree' and sort != 'parent_tree': return list() #sort_sql = """ORDER BY Post.date {}""".format(sort) sort_sql = """""" limit_sql = "" if limit != -1: try: limit = int(limit) except ValueError: return list() if limit < 0: return list() limit_sql = "LIMIT %d" % (int(limit)) if order != 'asc' and order != 'desc': return json.dumps({"code": 3, "response": RESPONSE_CODE_3}) order_sql = """ORDER BY date {}""".format(order) sql = "SELECT post, user, thread, forum, message, parent, date, likes, dislikes, points, \ isSpam, isEdited, isDeleted, isHighlighted, isApproved FROM Post \ WHERE {where_value} {since_value} {order_value} {sort_value} {limit_value}".format( where_value=where_sql, since_value=since_sql, limit_value=limit_sql, order_value=order_sql, sort_value=sort_sql) params = {} post_list_sql = db.execute_get(sql, params) if not post_list_sql: return list() post_list = list() for post_sql in post_list_sql: post_list.append({'id': str_to_json(post_sql[0]), 'user': str_to_json(post_sql[1]), 'thread': str_to_json(post_sql[2]), 'forum': str_to_json(post_sql[3]), 'message': str_to_json(post_sql[4]), 'parent': str_to_json(post_sql[5]), 'date': post_sql[6].strftime('%Y-%m-%d %H:%M:%S'), 'likes': str_to_json(post_sql[7]), 'dislikes': str_to_json(post_sql[8]), 'points': str_to_json(post_sql[9]), 'isSpam': str_to_json(post_sql[10], True), 'isEdited': str_to_json(post_sql[11], True), 'isDeleted': str_to_json(post_sql[12], True), 'isHighlighted': str_to_json(post_sql[13], True), 'isApproved': str_to_json(post_sql[14], True)}) return post_list