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