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