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 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 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 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 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 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 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(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 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(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 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 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 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)