def check_existence(thread_id): connection = DBConnect() query = Query.select.format( columns='id', table=Tables.THREAD_TABLE_NAME ) + Query.where.format( clause='id = ' + str(thread_id) ) try: cursor = connection.cursor() cursor.execute(query) thread_id = cursor.fetchone()[0] except Exception as e: connection.rollback() if 'NoneType' in str(e): return Codes.NOT_FOUND, 'Thread %s not found' % thread_id else: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Codes.OK, {'thread': thread_id}
def restore(post_id): connection = DBConnect() query = 'UPDATE ' + Tables.POST_TABLE_NAME \ + ' SET isDeleted = 0 ' \ + Query.where.format( clause='id = ' + str(post_id) ) select_query = Query.select.format( columns='thread', table=Tables.POST_TABLE_NAME ) + Query.where.format( clause='id = ' + str(post_id) ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() cursor.execute(select_query) code, response = Post.update_thread(cursor.fetchone()[0], True) if code != Codes.OK: return code, response except Exception as e: connection.rollback() return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Post.check_existence(post_id)
def insert(columns, values, short_name): connection = DBConnect() query = Query.insert.format( table=Tables.FORUM_TABLE_NAME, columns=columns, values=values ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() if e[0] == Errors.DUPLICATE_ENTRY: return Codes.USER_EXISTS, 'Forum ' + short_name + ' already exists' elif e[0] == Errors.FOREIGN_KEY_CONSTRAINT_FAILS: return Codes.NOT_FOUND, 'User not found' else: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Forum.get_forum(short_name)
def restore(thread_id): connection = DBConnect() update_thread = 'UPDATE ' + Tables.THREAD_TABLE_NAME + ' as t'\ + ' SET isDeleted = 0, posts = ' \ '(SELECT COUNT(p.id) ' \ 'FROM post as p ' \ 'WHERE p.thread = t.id)' \ + Query.where.format( clause='id = ' + str(thread_id) ) update_post = 'UPDATE ' + Tables.POST_TABLE_NAME \ + ' SET isDeleted = 0 ' \ + Query.where.format( clause='thread = ' + str(thread_id) ) + ';' try: cursor = connection.cursor() cursor.execute(update_thread) connection.commit() cursor.execute(update_post) connection.commit() except Exception as e: connection.rollback() return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Thread.check_existence(thread_id)
def follow(follower, followee): connection = DBConnect() query = Query.insert.format( table=Tables.USER_FOLLOW_TABLE_NAME, columns='follower, followee', values='%r, %r' % (str(follower), str(followee)) ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() if e[0] == Errors.FOREIGN_KEY_CONSTRAINT_FAILS: if str(e).__contains__('followee'): return Codes.NOT_FOUND, 'User ' + followee + ' not found' else: return Codes.NOT_FOUND, 'User ' + follower + ' not found' elif e[0] != Errors.DUPLICATE_ENTRY: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return User.details(follower)
def get_thread(thread_id): connection = DBConnect() query = Query.select.format( columns='*', table=Tables.THREAD_TABLE_NAME ) + Query.where.format( clause='id = ' + str(thread_id) ) try: cursor = connection.cursor() cursor.execute(query) result = dictfetchall(cursor)[0] except Exception as e: if 'list index out of range' in str(e): return Codes.NOT_FOUND, 'Post %s not found' % thread_id else: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() result['date'] = str(result['date']) result['isDeleted'] = bool(result['isDeleted']) result['isClosed'] = bool(result['isClosed']) return Codes.OK, result
def subscribe(thread_id, user): connection = DBConnect() query = Query.insert.format( table=Tables.THREAD_SUBSCRIBES_TABLE_NAME, columns='thread, user', values='%s, %r' % (thread_id, str(user)) ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() if e[0] == Errors.FOREIGN_KEY_CONSTRAINT_FAILS: if 'fk_subscribes_user' in str(e[1]): return Codes.NOT_FOUND, 'User %s not found' % user elif 'fk_subscribes_thread' in str(e[1]): return Codes.NOT_FOUND, 'Thread %s not found' % thread_id else: return Codes.UNKNOWN_ERROR, str(e) elif e[0] != Errors.DUPLICATE_ENTRY: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Codes.OK, {'thread': thread_id, 'user': user}
def insert(columns, values): connection = DBConnect() query = Query.insert.format( table=Tables.THREAD_TABLE_NAME, columns=columns, values=values ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() if e[0] == Errors.FOREIGN_KEY_CONSTRAINT_FAILS: if 'fk_thread_forum' in str(e[1]): return Codes.NOT_FOUND, 'Forum not found' elif 'fk_thread_user' in str(e[1]): return Codes.NOT_FOUND, 'User not found' else: return Codes.UNKNOWN_ERROR, str(e) else: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() # get thread id code, result = Thread.get_last_id() if code == Codes.OK: thread_id = result else: return code, result return Thread.get_thread(thread_id)
def remove(thread_id): connection = DBConnect() update_thread = 'UPDATE ' + Tables.THREAD_TABLE_NAME \ + ' SET isDeleted = 1, posts = 0 ' \ + Query.where.format( clause='id = ' + str(thread_id) ) update_post = 'UPDATE ' + Tables.POST_TABLE_NAME \ + ' SET isDeleted = 1 ' \ + Query.where.format( clause='thread = ' + str(thread_id) ) + ';' try: cursor = connection.cursor() cursor.execute(update_thread) connection.commit() cursor.execute(update_post) connection.commit() except Exception as e: connection.rollback() return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Thread.check_existence(thread_id)
def details(email): connection = DBConnect() columns = 'u.*, group_concat(distinct f.followee) as following,' \ 'group_concat(distinct f1.follower) as followers,' \ 'group_concat(distinct s.thread) as subscriptions' query = Query.select.format( columns=columns, table=Tables.USER_TABLE_NAME + ' as u' ) + Query.left_join.format( table=Tables.USER_FOLLOW_TABLE_NAME + ' as f', clause='u.email = f.follower' ) + Query.left_join.format( table=Tables.USER_FOLLOW_TABLE_NAME + ' as f1', clause='u.email = f1.followee' ) + Query.left_join.format( table=Tables.THREAD_SUBSCRIBES_TABLE_NAME + ' as s', clause='u.email= s.user' ) + Query.where.format( clause='u.email = %r' % str(email) ) try: cursor = connection.cursor() cursor.execute(query) response = dictfetchall(cursor)[0] except Exception as e: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() if not response['email']: return Codes.NOT_FOUND, 'User ' + email + ' not found' response['isAnonymous'] = bool(response['isAnonymous']) if response['following']: response['following'] = response['following'].split(',') else: response['following'] = [] if response['followers']: response['followers'] = response['followers'].split(',') else: response['followers'] = [] if response['subscriptions']: subscribes_list = [] subscribes = response['subscriptions'].split(',') for subscribe in subscribes: subscribes_list.append(int(subscribe)) response['subscriptions'] = subscribes_list else: response['subscriptions'] = [] return Codes.OK, response
def get_last_id(): connection = DBConnect() query = Query.select.format( columns='MAX(id)', table=Tables.THREAD_TABLE_NAME ) try: cursor = connection.cursor() cursor.execute(query) thread_id = int(cursor.fetchone()[0]) except Exception as e: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Codes.OK, thread_id
def unfollow(follower, followee): connection = DBConnect() query = Query.delete.format( table=Tables.USER_FOLLOW_TABLE_NAME, clause='follower = %r AND followee = %r' % (str(follower), str(followee)) ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return User.details(follower)
def unsubscribe(thread_id, user): connection = DBConnect() query = Query.delete.format( table=Tables.THREAD_SUBSCRIBES_TABLE_NAME, clause='thread = %s and user = %r' % (thread_id, str(user)) ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Codes.OK, {'thread': thread_id, 'user': user}
def insert(columns, values, request): connection = DBConnect() query = Query.insert.format( table=Tables.POST_TABLE_NAME, columns=columns, values=values ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() if e[0] == Errors.FOREIGN_KEY_CONSTRAINT_FAILS: if 'fk_post_thread' in str(e[1]): return Codes.NOT_FOUND, 'Thread not found' elif 'fk_post_forum' in str(e[1]): return Codes.NOT_FOUND, 'Forum not found' elif 'fk_post_user' in str(e[1]): return Codes.NOT_FOUND, 'User not found' else: return Codes.UNKNOWN_ERROR, str(e) else: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() code, response = Post.update_thread(request['thread'], True) if code != Codes.OK: return code, response # get post id code, result = Post.get_last_id() if code == Codes.OK: post_id = result else: return code, result return Post.get_post(post_id)
def update(thread_id, message, slug): connection = DBConnect() query = 'UPDATE ' + Tables.THREAD_TABLE_NAME \ + ' SET message = %r, slug = %r ' % (str(message), str(slug)) \ + Query.where.format( clause='id = %s' % thread_id ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Thread.details(thread_id)
def update(post_id, message): connection = DBConnect() query = 'UPDATE ' + Tables.POST_TABLE_NAME \ + ' SET message = %r ' % str(message) \ + Query.where.format( clause='id = %s' % post_id ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Post.details(post_id)
def close(thread_id): connection = DBConnect() query = 'UPDATE ' + Tables.THREAD_TABLE_NAME \ + ' SET isClosed = 1 ' \ + Query.where.format( clause='id = ' + str(thread_id) ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Thread.check_existence(thread_id)
def update(email, about, name): connection = DBConnect() query = 'UPDATE ' + Tables.USER_TABLE_NAME \ + ' SET about = %r, name = %r ' % (str(about), str(name)) \ + Query.where.format( clause='email = %r' % (str(email)) ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return User.details(email)
def get_posts_by_user(user, request=None): connection = DBConnect() query = Query.select.format( columns='*', table=Tables.POST_TABLE_NAME ) query += Query.where.format( clause='user = %r' % str(user) ) if request and ('since' in request): query += Query.and_clause.format( clause='date >= %r' % str(request['since']) ) if request and ('order' in request): query += Query.order_by.format( column='date', type=request['order'] ) if request and ('limit' in request): query += Query.limit.format(count=request['limit']) try: cursor = connection.cursor() cursor.execute(query) response = dictfetchall(cursor) except Exception as e: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() for post in response: post['date'] = str(post['date']) post['isDeleted'] = bool(post['isDeleted']) post['isApproved'] = bool(post['isApproved']) post['isEdited'] = bool(post['isEdited']) post['isSpam'] = bool(post['isSpam']) post['isHighlighted'] = bool(post['isHighlighted']) return Codes.OK, response
def get_users_by_forum(forum, request=None): connection = DBConnect() query = Query.select.format( columns='DISTINCT(p.user)', table=Tables.POST_TABLE_NAME + ' as p' ) if request and ('since_id' in request): query += Query.join.format( table=Tables.USER_TABLE_NAME + ' as u', clause='u.email = p.user' ) query += Query.where.format( clause='p.forum = %r' % str(forum) ) if request and ('since_id' in request): query += Query.and_clause.format( clause='u.id >= %s' % request['since_id'] ) if request and ('order' in request): query += Query.order_by.format( column='p.user_name', type=request['order'] ) if request and ('limit' in request): query += Query.limit.format(count=request['limit']) try: cursor = connection.cursor() cursor.execute(query) users = cursor.fetchall() except Exception as e: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() response = [] for user in users: response.append(user[0]) return Codes.OK, response
def get_followees(email, request=None): connection = DBConnect() query = Query.select.format( columns='f.followee', table=Tables.USER_FOLLOW_TABLE_NAME + ' as f' ) if request and ('order' in request or 'since_id' in request): query += Query.join.format( table=Tables.USER_TABLE_NAME + ' as u', clause='u.email = f.followee' ) query += Query.where.format( clause='f.follower = %r' % str(email) ) if request and ('since_id' in request): query += Query.and_clause.format( clause='u.id >= %s' % request['since_id'] ) if request and ('order' in request): query += Query.order_by.format( column='u.name', type=request['order'] ) if request and ('limit' in request): query += Query.limit.format(count=request['limit']) try: cursor = connection.cursor() cursor.execute(query) followees = cursor.fetchall() except Exception as e: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() response = [] for followee in followees: response.append(followee[0]) return response
def update_thread(thread, increment): connection = DBConnect() query = 'UPDATE ' + Tables.THREAD_TABLE_NAME + \ ' SET posts = posts ' if increment: query += '+ ' else: query += '- ' query += '1 WHERE id = %s' % thread try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Codes.OK, 'OK'
def vote(thread_id, vote): connection = DBConnect() query = Query.insert.format( table=Tables.THREAD_VOTES_TABLE_NAME, columns='thread, vote', values='%s, %s' % (thread_id, vote) ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() if e[0] == Errors.FOREIGN_KEY_CONSTRAINT_FAILS: return Codes.NOT_FOUND, 'Thread %s not found' % thread_id else: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Thread.details(thread_id)
def insert(columns, values, email): connection = DBConnect() query = Query.insert.format( table=Tables.USER_TABLE_NAME, columns=columns, values=values ) try: cursor = connection.cursor() cursor.execute(query) connection.commit() except Exception as e: connection.rollback() if e[0] == Errors.DUPLICATE_ENTRY: return Codes.USER_EXISTS, 'User ' + email + ' already exists' else: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return User.get_user(email)
def get_subscriptions(email): connection = DBConnect() query = Query.select.format( columns='thread', table=Tables.THREAD_SUBSCRIBES_TABLE_NAME ) + Query.where.format( clause='user = %r' % str(email) ) try: cursor = connection.cursor() cursor.execute(query) threads = cursor.fetchall() except Exception as e: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() response = [] for thread in threads: response.append(thread[0]) return response
def get_user(email): connection = DBConnect() query = Query.select.format( columns='*', table=Tables.USER_TABLE_NAME ) + Query.where.format( clause='email = %r' % str(email) ) try: cursor = connection.cursor() cursor.execute(query) result = dictfetchall(cursor)[0] result['isAnonymous'] = bool(result['isAnonymous']) except Exception as e: if str(e) == 'list index out of range': return Codes.NOT_FOUND, "No such user" else: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Codes.OK, result
def get_forum(short_name): connection = DBConnect() query = Query.select.format( columns='*', table=Tables.FORUM_TABLE_NAME ) + Query.where.format( clause='short_name = %r' % str(short_name) ) try: cursor = connection.cursor() cursor.execute(query) result = dictfetchall(cursor)[0] except Exception as e: if str(e) == 'list index out of range': return Codes.NOT_FOUND, "No such user" else: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() return Codes.OK, result
def get_threads_by_user(user, request=None): connection = DBConnect() query = Query.select.format( columns='id', table=Tables.THREAD_TABLE_NAME ) query += Query.where.format( clause='user = %r' % str(user) ) if request and ('since' in request): query += Query.and_clause.format( clause='date >= %r' % str(request['since']) ) if request and ('order' in request): query += Query.order_by.format( column='date', type=request['order'] ) if request and ('limit' in request): query += Query.limit.format(count=request['limit']) try: cursor = connection.cursor() cursor.execute(query) threads = cursor.fetchall() except Exception as e: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() response = [] for thread in threads: response.append(thread[0]) return Codes.OK, response
def get_posts_by_forum(forum, related, request=None): connection = DBConnect() columns = 'p.*' if 'thread' in related: columns += ', t.id as t_id, t.forum as t_forum, t.title as t_title, ' \ 't.isClosed as t_isClosed, t.user as t_user, t.date as t_date, ' \ 't.message as t_message, t.slug as t_slug, t.isDeleted as t_isDeleted, ' \ 't.posts as t_posts, t.likes as t_likes, t.dislikes as t_dislikes, t.points as t_points' if 'forum' in related: columns += ', f.id as f_id, f.name as f_name, f.short_name as f_short_name, f.user as f_user' query = Query.select.format( columns=columns, table=Tables.POST_TABLE_NAME + ' as p' ) if 'thread' in related: query += Query.join.format( table=Tables.THREAD_TABLE_NAME + ' as t', clause='p.thread = t.id' ) if 'forum' in related: query += Query.join.format( table=Tables.FORUM_TABLE_NAME + ' as f', clause='p.forum = f.short_name' ) query += Query.where.format( clause='p.forum = %r' % str(forum) ) if request and ('since' in request): query += Query.and_clause.format( clause='p.date >= %r' % str(request['since']) ) if request and ('order' in request): query += Query.order_by.format( column='p.date', type=request['order'] ) if request and ('limit' in request): query += Query.limit.format(count=request['limit']) try: cursor = connection.cursor() cursor.execute(query) posts = dictfetchall(cursor) except Exception as e: return Codes.UNKNOWN_ERROR, str(e) finally: connection.close() response = [] for post in posts: post['date'] = str(post['date']) post['isDeleted'] = bool(post['isDeleted']) post['isApproved'] = bool(post['isApproved']) post['isEdited'] = bool(post['isEdited']) post['isSpam'] = bool(post['isSpam']) post['isHighlighted'] = bool(post['isHighlighted']) if 'thread' in related: post['t_date'] = str(post['t_date']) post['t_isDeleted'] = bool(post['t_isDeleted']) post['t_isClosed'] = bool(post['t_isClosed']) post['thread'] = {} keys = [] for val in post: if val[:2] == 't_': post['thread'][val[2:]] = post[val] keys.append(val) for key in keys: del post[key] if 'forum' in related: post['forum'] = {} keys = [] for val in post: if val[:2] == 'f_': post['forum'][val[2:]] = post[val] keys.append(val) for key in keys: del post[key] response.append(post) return Codes.OK, response