def create(): request_body = request.json username = try_encode(request_body.get('username')) about = request_body.get('about') name = try_encode(request_body.get('name')) email = request_body.get('email') is_anonymous_key = request_body.get('isAnonymous', False) if is_anonymous_key: is_anonymous = 1 else: is_anonymous = 0 sql = """INSERT INTO User (username, about, name, email, isAnonymous) VALUES \ (%(username)s, %(about)s, %(name)s, %(email)s, %(isAnonymous)s);""" args = {'username': username, 'about': about, 'name': name, 'email': email, 'isAnonymous': is_anonymous} try: db.execute(sql, args, True) except MySQLdb.IntegrityError, message: if message[0] == MYSQL_DUPLICATE_ENTITY_ERROR: return json.dumps({"code": 5, "response": "This user already exists"}, indent=4) return json.dumps({"code": 4, "response": "Oh, we have some really bad error"}, indent=4)
def create(): request_body = request.json # Required forum = request_body.get('forum') title = try_encode(request_body.get('title')) if request_body.get('isClosed'): is_closed = 1 else: is_closed = 0 user = request_body.get('user') date = request_body.get('date') message = request_body.get('message') slug = request_body.get('slug') # Optional if request_body.get('isDeleted', False): is_deleted = 1 else: is_deleted = 0 sql = """INSERT INTO Thread (forum, title, isClosed, user, date, message, slug, isDeleted) \ VALUES (%(forum)s, %(title)s, %(isClosed)s, %(user)s, %(date)s, %(message)s, %(slug)s, %(isDeleted)s);""" args = {'forum': forum, 'title': title, 'isClosed': is_closed, 'user': user, 'date': date, 'message': message, 'slug': slug, 'isDeleted': is_deleted} try: db.execute(sql, args, True) except MySQLdb.IntegrityError, message: print message[0]
def vote(): request_body = request.json post_id = request_body.get('post') vote_value = request_body.get('vote') if vote_value == 1: db.execute( """UPDATE Post SET likes = likes + 1, points = points + 1 WHERE post = %(post)s;""", {'post': post_id}, True) elif vote_value == -1: db.execute( """UPDATE Post SET dislikes = dislikes + 1, points = points - 1 WHERE post = %(post)s;""", {'post': post_id}, True) else: return json.dumps({ "code": 3, "response": "Wrong 'vote' value'" }, indent=4) post = get_post_by_id(post_id) if not post: return json.dumps({"code": 1, "response": "Empty set"}, indent=4) return json.dumps({"code": 0, "response": post}, indent=4)
def update(): request_body = request.json message = request_body.get('message') slug = request_body.get('slug') thread_id = request_body.get('thread') db.execute("""UPDATE Thread SET message = %(message)s, slug = %(slug)s WHERE thread = %(thread)s;""", {'message': message, 'slug': slug, 'thread': thread_id}, True) return json.dumps({"code": 0, "response": get_thread_by_id(thread_id)}, indent=4)
def update_profile(): request_body = request.json about = try_encode(request_body.get('about')) email = try_encode(request_body.get('user')) name = try_encode(request_body.get('name')) args = {'about': about, 'name': name, 'email': email} db.execute("""UPDATE User SET about = %(about)s, name = %(name)s WHERE email = %(email)s;""", args, True) return json.dumps({"code": 0, "response": get_user_dict(email)}, indent=4)
def remove(): request_body = request.json thread = request_body.get('thread') post_list = get_post_list(thread=thread) for post in post_list: remove_post(post['id']) db.execute("""UPDATE Thread SET isDeleted = 1, posts = 0 WHERE thread = %(thread)s;""", {'thread': thread}, True) return json.dumps({"code": 0, "response": thread}, indent=4)
def restore(): thread = request.json.get('thread') post_list = get_post_list(thread=thread) for post in post_list: restore_post(post['id']) db.execute("""UPDATE Thread SET isDeleted = 0, posts = %(posts)s WHERE thread = %(thread)s;""", {'posts': len(post_list), 'thread': thread}, True) return json.dumps({"code": 0, "response": thread}, indent=4)
def create(): request_body = request.json name = request_body.get('name') short_name = request_body.get('short_name') user = request_body.get('user') try: db.execute("""INSERT INTO Forum (name, short_name, user) VALUES (%(name)s, %(short_name)s, %(user)s);""", {'name': name, 'short_name': short_name, 'user': user}, True) except MySQLdb.IntegrityError, message: print message[0]
def update_profile(): request_body = request.json about = try_encode(request_body.get('about')) email = try_encode(request_body.get('user')) name = try_encode(request_body.get('name')) args = {'about': about, 'name': name, 'email': email} db.execute( """UPDATE User SET about = %(about)s, name = %(name)s WHERE email = %(email)s;""", args, True) return json.dumps({"code": 0, "response": get_user_dict(email)}, indent=4)
def subscribe_method(unsubscribe_value=False): request_body = request.json user = request_body.get('user') thread = request_body.get('thread') if not unsubscribe_value: try: db.execute("""INSERT INTO Subscription (subscriber, thread) VALUES (%(subscriber)s, %(thread)s);""", {'subscriber': user, 'thread': thread}, True) except MySQLdb.IntegrityError, message: if message[0] == MYSQL_DUPLICATE_ENTITY_ERROR: print "Already subscribed"
def follow_method(do_unfollow): request_body = request.json follower = request_body.get('follower') followee = request_body.get('followee') args = {'follower': follower, 'following': followee} if not do_unfollow: db.execute("""INSERT INTO Follower (follower, following) VALUES (%(follower)s, %(following)s);""", args, True) else: db.execute("""DELETE FROM Follower WHERE follower = %(follower)s AND following = %(following)s;""", args, True) return json.dumps({"code": 0, "response": get_user_dict(follower)}, indent=4)
def remove(): request_body = request.json thread = request_body.get('thread') post_list = get_post_list(thread=thread) for post in post_list: remove_post(post['id']) db.execute( """UPDATE Thread SET isDeleted = 1, posts = 0 WHERE thread = %(thread)s;""", {'thread': thread}, True) return json.dumps({"code": 0, "response": thread}, indent=4)
def vote(): request_body = request.json vote_value = request_body.get('vote') thread_id = request_body.get('thread') if vote_value == 1: db.execute("""UPDATE Thread SET likes = likes + 1, points = points + 1 WHERE thread = %(thread)s;""", {'thread': thread_id}, True) else: db.execute("""UPDATE Thread SET dislikes = dislikes + 1, points = points - 1 WHERE thread = %(thread)s;""", {'thread': thread_id}, True) return json.dumps({"code": 0, "response": get_thread_by_id(thread_id)}, indent=4)
def update(): request_body = request.json post_id = request_body.get('post') message = try_encode(request_body.get('message')) args = {'message': message, 'post': post_id} db.execute( """UPDATE Post SET message = %(message)s WHERE post = %(post)s;""", args, True) post = get_post_by_id(post_id) if not post: return json.dumps({"code": 1, "response": "Empty set"}, indent=4) return json.dumps({"code": 0, "response": post}, indent=4)
def restore(): thread = request.json.get('thread') post_list = get_post_list(thread=thread) for post in post_list: restore_post(post['id']) db.execute( """UPDATE Thread SET isDeleted = 0, posts = %(posts)s WHERE thread = %(thread)s;""", { 'posts': len(post_list), 'thread': thread }, True) return json.dumps({"code": 0, "response": thread}, indent=4)
def get_followers_list(email): followers_list_sql = db.execute("""SELECT follower FROM Follower WHERE following = %(following)s;""", {'following': email}) if not followers_list_sql: return list() return followers_list_sql[0]
def get_post_by_id(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) post_list_sql = db.execute(sql, {'id': id_value}) 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 subscribe_method(unsubscribe_value=False): request_body = request.json user = request_body.get('user') thread = request_body.get('thread') if not unsubscribe_value: try: db.execute( """INSERT INTO Subscription (subscriber, thread) VALUES (%(subscriber)s, %(thread)s);""", { 'subscriber': user, 'thread': thread }, True) except MySQLdb.IntegrityError, message: if message[0] == MYSQL_DUPLICATE_ENTITY_ERROR: print "Already subscribed"
def get_post_by_id(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) post_list_sql = db.execute(sql, {'id': id_value}) 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 get_following_list(email): following_list = db.execute("""SELECT following FROM Follower WHERE follower = %(follower)s;""", {'follower': email}) if not following_list: return list() return following_list[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: print "Can't find search field in getThreadList" return list() # Since part since_sql = "" if since != "": since_sql = """ AND date >= '{}'""".format(since) # Order part if order != 'asc' and order != 'desc': print "Wrong order value" return list() order_sql = """ ORDER BY date {}""".format(order) # Limit part limit_sql = "" if limit != -1: try: limit = int(limit) except ValueError: print "Wrong limit value" return list() if limit < 0: print "Wrong limit value" return list() limit_sql = """ LIMIT {}""".format(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) thread_list_sql = db.execute(sql) 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_threads_list(email): subscriptions_list = db.execute("""SELECT thread FROM Subscription WHERE subscriber = %(subscriber)s;""", {'subscriber': email}) result = list() for thread in subscriptions_list: result.append(thread[0]) return result
def get_followers_list(email): followers_list_sql = db.execute( """SELECT follower FROM Follower WHERE following = %(following)s;""", {'following': email}) if not followers_list_sql: return list() return followers_list_sql[0]
def create(): request_body = request.json name = request_body.get('name') short_name = request_body.get('short_name') user = request_body.get('user') try: db.execute( """INSERT INTO Forum (name, short_name, user) VALUES (%(name)s, %(short_name)s, %(user)s);""", { 'name': name, 'short_name': short_name, 'user': user }, True) except MySQLdb.IntegrityError, message: print message[0]
def get_following_list(email): following_list = db.execute( """SELECT following FROM Follower WHERE follower = %(follower)s;""", {'follower': email}) if not following_list: return list() return following_list[0]
def list_followers_method(is_following): qs = get_json(request) email = qs.get('user') if not email: return json.dumps({"code": 2, "response": "No 'user' key"}, indent=4) # Since part since_id = qs.get('since_id', -1) if since_id != -1: since_sql = """AND User.user >= {}""".format(since_id) else: since_sql = "" # Order part order_sql = """ORDER BY User.name {}""".format(qs.get('order', 'desc')) # Limit part limit = qs.get('limit', -1) if limit != -1: try: limit = int(limit) except ValueError: return json.dumps({"code": 3, "response": "Wrong limit value"}, indent=4) if limit < 0: return json.dumps({"code": 3, "response": "Wrong limit value"}, indent=4) limit_sql = """LIMIT {}""".format(limit) else: limit_sql = "" sql = """SELECT about, email, user, isAnonymous, name, username FROM User JOIN Follower ON """ if not is_following: sql += """Follower.follower = User.email WHERE Follower.following""" else: sql += """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) user_list_sql = db.execute(sql, {'email': email}) if not user_list_sql: return json.dumps({"code": 1, "response": "Empty set"}, indent=4) 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': get_followers_list(follower_email), 'following': get_following_list(follower_email), 'subscriptions': get_subscribed_threads_list(follower_email)}) return json.dumps({"code": 0, "response": user_list}, indent=4)
def get_subscribed_threads_list(email): subscriptions_list = db.execute( """SELECT thread FROM Subscription WHERE subscriber = %(subscriber)s;""", {'subscriber': email}) result = list() for thread in subscriptions_list: result.append(thread[0]) return result
def vote(): request_body = request.json vote_value = request_body.get('vote') thread_id = request_body.get('thread') if vote_value == 1: db.execute( """UPDATE Thread SET likes = likes + 1, points = points + 1 WHERE thread = %(thread)s;""", {'thread': thread_id}, True) else: db.execute( """UPDATE Thread SET dislikes = dislikes + 1, points = points - 1 WHERE thread = %(thread)s;""", {'thread': thread_id}, True) return json.dumps({ "code": 0, "response": get_thread_by_id(thread_id) }, indent=4)
def update(): request_body = request.json message = request_body.get('message') slug = request_body.get('slug') thread_id = request_body.get('thread') db.execute( """UPDATE Thread SET message = %(message)s, slug = %(slug)s WHERE thread = %(thread)s;""", { 'message': message, 'slug': slug, 'thread': thread_id }, True) return json.dumps({ "code": 0, "response": get_thread_by_id(thread_id) }, indent=4)
def get_forum_dict(short_name): sql = """SELECT forum, name, short_name, user FROM Forum WHERE short_name = %(short_name)s LIMIT 1;""" forum_sql = db.execute(sql, {'short_name': short_name}) if not forum_sql: return dict() forum_sql = forum_sql[0] return {'id': str_to_json(forum_sql[0]), 'name': str_to_json(forum_sql[1]), 'short_name': str_to_json(forum_sql[2]), 'user': str_to_json(forum_sql[3])}
def follow_method(do_unfollow): request_body = request.json follower = request_body.get('follower') followee = request_body.get('followee') args = {'follower': follower, 'following': followee} if not do_unfollow: db.execute( """INSERT INTO Follower (follower, following) VALUES (%(follower)s, %(following)s);""", args, True) else: db.execute( """DELETE FROM Follower WHERE follower = %(follower)s AND following = %(following)s;""", args, True) return json.dumps({ "code": 0, "response": get_user_dict(follower) }, indent=4)
def list_users(): qs = get_json(request) if not qs.get('forum'): return json.dumps({"code": 2, "response": "No 'forum' key"}, indent=4) # Since id part since_id = qs.get('since_id') if since_id: try: since_id = int(since_id) except ValueError: return json.dumps({"code": 3, "response": "Wrong since_id value"}, indent=4) since_id_sql = """AND User.user >= {}""".format(since_id) else: since_id_sql = '' # Limit part if qs.get('limit'): limit = qs.get('limit')[0] try: limit = int(limit) except ValueError: return json.dumps({"code": 3, "response": "Wrong limit value"}, indent=4) if limit < 0: return json.dumps({"code": 3, "response": "Wrong limit value"}, indent=4) limit_sql = """LIMIT {}""".format(limit) else: limit_sql = '' # Order part order = qs.get('order', 'desc') order_sql = """ORDER BY User.name {}""".format(order) 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) user_list_sql = db.execute(sql, {'forum': qs.get('forum')}) 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_threads_list(email)}) return json.dumps({"code": 0, "response": user_list}, indent=4)
def get_forum_dict(short_name): sql = """SELECT forum, name, short_name, user FROM Forum WHERE short_name = %(short_name)s LIMIT 1;""" forum_sql = db.execute(sql, {'short_name': short_name}) if not forum_sql: return dict() forum_sql = forum_sql[0] return { 'id': str_to_json(forum_sql[0]), 'name': str_to_json(forum_sql[1]), 'short_name': str_to_json(forum_sql[2]), 'user': str_to_json(forum_sql[3]) }
def create(): request_body = request.json # Required forum = request_body.get('forum') title = try_encode(request_body.get('title')) if request_body.get('isClosed'): is_closed = 1 else: is_closed = 0 user = request_body.get('user') date = request_body.get('date') message = request_body.get('message') slug = request_body.get('slug') # Optional if request_body.get('isDeleted', False): is_deleted = 1 else: is_deleted = 0 sql = """INSERT INTO Thread (forum, title, isClosed, user, date, message, slug, isDeleted) \ VALUES (%(forum)s, %(title)s, %(isClosed)s, %(user)s, %(date)s, %(message)s, %(slug)s, %(isDeleted)s);""" args = { 'forum': forum, 'title': title, 'isClosed': is_closed, 'user': user, 'date': date, 'message': message, 'slug': slug, 'isDeleted': is_deleted } try: db.execute(sql, args, True) except MySQLdb.IntegrityError, message: print message[0]
def create(): request_body = request.json username = try_encode(request_body.get('username')) about = request_body.get('about') name = try_encode(request_body.get('name')) email = request_body.get('email') is_anonymous_key = request_body.get('isAnonymous', False) if is_anonymous_key: is_anonymous = 1 else: is_anonymous = 0 sql = """INSERT INTO User (username, about, name, email, isAnonymous) VALUES \ (%(username)s, %(about)s, %(name)s, %(email)s, %(isAnonymous)s);""" args = { 'username': username, 'about': about, 'name': name, 'email': email, 'isAnonymous': is_anonymous } try: db.execute(sql, args, True) except MySQLdb.IntegrityError, message: if message[0] == MYSQL_DUPLICATE_ENTITY_ERROR: return json.dumps( { "code": 5, "response": "This user already exists" }, indent=4) return json.dumps( { "code": 4, "response": "Oh, we have some really bad error" }, indent=4)
def get_user_dict(email): user_list_sql = db.execute("""SELECT user, email, name, username, isAnonymous, about FROM User \ WHERE email = %(email)s;""", {'email': email}) if not user_list_sql: return dict() user_sql = user_list_sql[0] return {'id': str_to_json(user_sql[0]), 'email': str_to_json(user_sql[1]), 'name': str_to_json(user_sql[2]), 'username': str_to_json(user_sql[3]), 'isAnonymous': str_to_json(user_sql[4], True), 'about': str_to_json(user_sql[5])}
def get_user_dict(email): user_list_sql = db.execute( """SELECT user, email, name, username, isAnonymous, about FROM User \ WHERE email = %(email)s;""", {'email': email}) if not user_list_sql: return dict() user_sql = user_list_sql[0] return { 'id': str_to_json(user_sql[0]), 'email': str_to_json(user_sql[1]), 'name': str_to_json(user_sql[2]), 'username': str_to_json(user_sql[3]), 'isAnonymous': str_to_json(user_sql[4], True), 'about': str_to_json(user_sql[5]) }
def get_thread_by_id(id_value): sql = """SELECT thread, title, user, message, forum, isDeleted, isClosed, date, slug, likes, dislikes, \ points, posts FROM Thread WHERE thread = %(thread)s LIMIT 1;""" thread_list_sql = db.execute(sql, {'thread': id_value}) 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 get_thread_by_id(id_value): sql = """SELECT thread, title, user, message, forum, isDeleted, isClosed, date, slug, likes, dislikes, \ points, posts FROM Thread WHERE thread = %(thread)s LIMIT 1;""" thread_list_sql = db.execute(sql, {'thread': id_value}) 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 close_route(): thread = request.json.get('thread') db.execute("""UPDATE Thread SET isClosed = 1 WHERE thread = %(thread)s;""", {'thread': thread}, True) return json.dumps({"code": 0, "response": thread}, indent=4)
thread = request_body.get('thread') if not unsubscribe_value: try: db.execute( """INSERT INTO Subscription (subscriber, thread) VALUES (%(subscriber)s, %(thread)s);""", { 'subscriber': user, 'thread': thread }, True) except MySQLdb.IntegrityError, message: if message[0] == MYSQL_DUPLICATE_ENTITY_ERROR: print "Already subscribed" else: db.execute( """DELETE FROM Subscription WHERE subscriber = %(subscriber)s AND thread = %(thread)s;""", { 'subscriber': user, 'thread': thread }, True) result_dict = {'thread': thread, 'user': str_to_json(user)} return json.dumps({"code": 0, "response": result_dict}, indent=4) @module.route("/vote/", methods=["POST"]) def vote(): request_body = request.json vote_value = request_body.get('vote') thread_id = request_body.get('thread') if vote_value == 1:
def clear(): # db.execute("""TRUNCATE TABLE Forum;""", post=True) # db.execute("""TRUNCATE TABLE User;""", post=True) # db.execute("""TRUNCATE TABLE Post;""") # db.execute("""TRUNCATE TABLE Thread;""", post=True) # db.execute("""TRUNCATE TABLE Subscription;""", post=True) # db.execute("""TRUNCATE TABLE Follower;""", post=True) # this is faster db.execute("""DELETE Forum.* FROM Forum;""", post=True) db.execute("""DELETE User.* FROM User;""", post=True) db.execute("""DELETE Post.* FROM Post;""") db.execute("""DELETE Thread.* FROM Thread;""", post=True) db.execute("""DELETE Subscription.* FROM Subscription;""", post=True) db.execute("""DELETE Follower.* FROM Follower;""", post=True) return json.dumps({"code": 0, "response": "OK"})
def dec_posts_for_thread(thread_id): db.execute("""UPDATE Thread SET posts = posts - 1 WHERE thread = %(thread)s;""", {'thread': thread_id}, post=True)
def list_followers_method(is_following): qs = get_json(request) email = qs.get('user') if not email: return json.dumps({"code": 2, "response": "No 'user' key"}, indent=4) # Since part since_id = qs.get('since_id', -1) if since_id != -1: since_sql = """AND User.user >= {}""".format(since_id) else: since_sql = "" # Order part order_sql = """ORDER BY User.name {}""".format(qs.get('order', 'desc')) # Limit part limit = qs.get('limit', -1) if limit != -1: try: limit = int(limit) except ValueError: return json.dumps({ "code": 3, "response": "Wrong limit value" }, indent=4) if limit < 0: return json.dumps({ "code": 3, "response": "Wrong limit value" }, indent=4) limit_sql = """LIMIT {}""".format(limit) else: limit_sql = "" sql = """SELECT about, email, user, isAnonymous, name, username FROM User JOIN Follower ON """ if not is_following: sql += """Follower.follower = User.email WHERE Follower.following""" else: sql += """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) user_list_sql = db.execute(sql, {'email': email}) if not user_list_sql: return json.dumps({"code": 1, "response": "Empty set"}, indent=4) 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': get_followers_list(follower_email), 'following': get_following_list(follower_email), 'subscriptions': get_subscribed_threads_list(follower_email) }) return json.dumps({"code": 0, "response": user_list}, indent=4)
def restore_post(post_id): db.execute("""UPDATE Post SET isDeleted = 0 WHERE post = %(post)s;""", {'post': post_id}, True)
def get_post_list(user="", forum="", thread="", since="", limit=-1, sort='flat', order='desc'): # WHERE part if forum != "": where_sql = "forum = '{}'".format(forum) elif thread != "": where_sql = "thread = {}".format(thread) elif user != "": where_sql = "user = '******'".format(user) else: print "Can't find search field in getPostList" return list() # since part since_sql = "" if since != "": since_sql = """AND date >= '{}'""".format(since) # sort part TODO if sort != 'flat' and sort != 'tree' and sort != 'parent_tree': print "Wrong sort value" return list() # sort_sql = """ORDER BY Post.date {}""".format(sort) sort_sql = """""" # limit part limit_sql = "" if limit != -1: try: limit = int(limit) except ValueError: print "Wrong limit value" return list() if limit < 0: print "Wrong limit value" return list() limit_sql = """LIMIT {}""".format(limit) # order part if order != 'asc' and order != 'desc': return json.dumps({"code": 3, "response": "Wrong order value"}, indent=4) 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) post_list_sql = db.execute(sql) 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_post_list(user="", forum="", thread="", since="", limit=-1, sort='flat', order='desc'): # WHERE part if forum != "": where_sql = "forum = '{}'".format(forum) elif thread != "": where_sql = "thread = {}".format(thread) elif user != "": where_sql = "user = '******'".format(user) else: print "Can't find search field in getPostList" return list() # since part since_sql = "" if since != "": since_sql = """AND date >= '{}'""".format(since) # sort part TODO if sort != 'flat' and sort != 'tree' and sort != 'parent_tree': print "Wrong sort value" return list() # sort_sql = """ORDER BY Post.date {}""".format(sort) sort_sql = """""" # limit part limit_sql = "" if limit != -1: try: limit = int(limit) except ValueError: print "Wrong limit value" return list() if limit < 0: print "Wrong limit value" return list() limit_sql = """LIMIT {}""".format(limit) # order part if order != 'asc' and order != 'desc': return json.dumps({ "code": 3, "response": "Wrong order value" }, indent=4) 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) post_list_sql = db.execute(sql) 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 subscribe_method(unsubscribe_value=False): request_body = request.json user = request_body.get('user') thread = request_body.get('thread') if not unsubscribe_value: try: db.execute("""INSERT INTO Subscription (subscriber, thread) VALUES (%(subscriber)s, %(thread)s);""", {'subscriber': user, 'thread': thread}, True) except MySQLdb.IntegrityError, message: if message[0] == MYSQL_DUPLICATE_ENTITY_ERROR: print "Already subscribed" else: db.execute("""DELETE FROM Subscription WHERE subscriber = %(subscriber)s AND thread = %(thread)s;""", {'subscriber': user, 'thread': thread}, True) result_dict = {'thread': thread, 'user': str_to_json(user)} return json.dumps({"code": 0, "response": result_dict}, indent=4) @module.route("/vote/", methods=["POST"]) def vote(): request_body = request.json vote_value = request_body.get('vote') thread_id = request_body.get('thread') if vote_value == 1: db.execute("""UPDATE Thread SET likes = likes + 1, points = points + 1 WHERE thread = %(thread)s;""", {'thread': thread_id}, True)
def close_route(): thread = request.json.get('thread') db.execute("""UPDATE Thread SET isClosed = 1 WHERE thread = %(thread)s;""", {'thread': thread}, True) return json.dumps({"code": 0, "response": thread}, indent=4)
def restore_post(post_id): db.execute("""UPDATE Post SET isDeleted = 0 WHERE post = %(post)s;""", {'post': post_id}, True)
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: print "Can't find search field in getThreadList" return list() # Since part since_sql = "" if since != "": since_sql = """ AND date >= '{}'""".format(since) # Order part if order != 'asc' and order != 'desc': print "Wrong order value" return list() order_sql = """ ORDER BY date {}""".format(order) # Limit part limit_sql = "" if limit != -1: try: limit = int(limit) except ValueError: print "Wrong limit value" return list() if limit < 0: print "Wrong limit value" return list() limit_sql = """ LIMIT {}""".format(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) thread_list_sql = db.execute(sql) 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 clear(): # db.execute("""TRUNCATE TABLE Forum;""", post=True) # db.execute("""TRUNCATE TABLE User;""", post=True) # db.execute("""TRUNCATE TABLE Post;""") # db.execute("""TRUNCATE TABLE Thread;""", post=True) # db.execute("""TRUNCATE TABLE Subscription;""", post=True) # db.execute("""TRUNCATE TABLE Follower;""", post=True) # this is faster db.execute("""DELETE Forum.* FROM Forum;""", post=True) db.execute("""DELETE User.* FROM User;""", post=True) db.execute("""DELETE Post.* FROM Post;""") db.execute("""DELETE Thread.* FROM Thread;""", post=True) db.execute("""DELETE Subscription.* FROM Subscription;""", post=True) db.execute("""DELETE Follower.* FROM Follower;""", post=True) return json.dumps({"code": 0, "response": "OK"})
def dec_posts_for_thread(thread_id): db.execute( """UPDATE Thread SET posts = posts - 1 WHERE thread = %(thread)s;""", {'thread': thread_id}, post=True)
def list_users(): qs = get_json(request) if not qs.get('forum'): return json.dumps({"code": 2, "response": "No 'forum' key"}, indent=4) # Since id part since_id = qs.get('since_id') if since_id: try: since_id = int(since_id) except ValueError: return json.dumps({ "code": 3, "response": "Wrong since_id value" }, indent=4) since_id_sql = """AND User.user >= {}""".format(since_id) else: since_id_sql = '' # Limit part if qs.get('limit'): limit = qs.get('limit')[0] try: limit = int(limit) except ValueError: return json.dumps({ "code": 3, "response": "Wrong limit value" }, indent=4) if limit < 0: return json.dumps({ "code": 3, "response": "Wrong limit value" }, indent=4) limit_sql = """LIMIT {}""".format(limit) else: limit_sql = '' # Order part order = qs.get('order', 'desc') order_sql = """ORDER BY User.name {}""".format(order) 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) user_list_sql = db.execute(sql, {'forum': qs.get('forum')}) 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_threads_list(email) }) return json.dumps({"code": 0, "response": user_list}, indent=4)
def create(): request_body = request.json # Required date = request_body.get('date') thread = request_body.get('thread') message = request_body.get('message') user = request_body.get('user') forum = request_body.get('forum') # Optional parent = request_body.get('parent', None) if request_body.get('isApproved', False): is_approved = 1 else: is_approved = 0 if request_body.get('isHighlighted', False): is_highlighted = 1 else: is_highlighted = 0 if request_body.get('isEdited', False): is_edited = 1 else: is_edited = 0 if request_body.get('isSpam', False): is_spam = 1 else: is_spam = 0 if request_body.get('isDeleted', False): is_deleted = 1 else: is_deleted = 0 sql = """INSERT INTO Post (user, thread, forum, message, parent, date, \ isSpam, isEdited, isDeleted, isHighlighted, isApproved) VALUES \ (%(user)s, %(thread)s, %(forum)s, %(message)s, %(parent)s, %(date)s, \ %(isSpam)s, %(isEdited)s, %(isDeleted)s, %(isHighlighted)s, %(isApproved)s);""" args = { 'user': user, 'thread': thread, 'forum': forum, 'message': message, 'parent': parent, 'date': date, 'isSpam': is_spam, 'isEdited': is_edited, 'isDeleted': is_deleted, 'isHighlighted': is_highlighted, 'isApproved': is_approved } post_id = db.execute(sql, args, True) post = get_post_by_id(post_id) inc_posts_for_thread(thread) if not post: return json.dumps({"code": 1, "response": "Empty set"}, indent=4) return json.dumps({"code": 0, "response": post}, indent=4)