def follow(follower, followee):
query = Query.INSERT.format(
table=Tables.TABLE_NAMES['USER_FOLLOW'],
columns='following, follower',
values='%r, %r' % (str(followee), str(follower))
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
connection.commit()
except IntegrityError as e:
connection.rollback()
if e.errno == Errors.FOREIGN_KEY_CONSTRAINT_FAILS and 'following' in str(e):
return Codes.NOT_FOUND, 'User %r not found' % str(followee)
elif e.errno == Errors.FOREIGN_KEY_CONSTRAINT_FAILS and 'follower' in str(e):
return Codes.NOT_FOUND, 'User %r not found' % str(follower)
elif e.errno != Errors.DUPLICATE_ENTRY:
return Codes.UNKNOWN_ERROR, str(e)
except Exception as e:
connection.rollback()
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
return User.details(follower)
def restore(post):
query = 'UPDATE ' + Tables.TABLE_NAMES['POST'] \
+ ' SET isDeleted = 0 ' \
+ Query.WHERE.format(
clause='id = ' + str(post)
)
select_query = Query.SELECT.format(
columns='thread',
table=Tables.TABLE_NAMES['POST']
) + Query.WHERE.format(
clause='id = ' + str(post)
)
connection = db_connect.get_connection()
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:
if 'NoneType' in str(e):
return Codes.NOT_FOUND, 'Post %s not found' % post
else:
connection.rollback()
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
return Codes.OK, {'post': post}
def create(columns, values):
query = Query.INSERT.format(
columns=columns,
table=Tables.TABLE_NAMES['POST'],
values=values
)
get_id_query = Query.SELECT.format(
columns='MAX(id)',
table=Tables.TABLE_NAMES['POST']
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
connection.commit()
cursor.execute(get_id_query)
post = cursor.fetchone()[0]
except Exception as e:
connection.rollback()
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
return Post.get_post(post)
def create(columns, values, forum):
query = Query.INSERT.format(
columns=columns,
table=Tables.TABLE_NAMES['FORUM'],
values=values
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
connection.commit()
except IntegrityError as e:
connection.rollback()
if e.errno == Errors.FOREIGN_KEY_CONSTRAINT_FAILS and 'fk_forum_user' in str(e.msg):
return Codes.NOT_FOUND, 'User not found'
elif e.errno == Errors.NULL_COLUMN:
return Codes.NOT_CORRECT, str(e.msg)
elif e.errno != Errors.DUPLICATE_ENTRY:
return Codes.UNKNOWN_ERROR, str(e)
except Exception as e:
connection.rollback()
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
return Forum.get_forum(forum)
def details(forum, related=None):
if not related:
related = []
query = Query.SELECT.format(
columns='*',
table=Tables.TABLE_NAMES['FORUM']
) + Query.WHERE.format(
clause='short_name = %r' % str(forum)
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
response = dictfetchall(cursor)[0]
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
code = Codes.OK
if 'user' in related:
code, response['user'] = User.details(response['user'])
return code, response
def create(columns, values, email):
query = Query.INSERT.format(
columns=columns,
table=Tables.TABLE_NAMES['USER'],
values=values
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
connection.commit()
except IntegrityError as e:
connection.rollback()
if e.errno == Errors.DUPLICATE_ENTRY:
return Codes.USER_EXISTS, 'User %r already exist' % str(email)
elif e.errno == Errors.NULL_COLUMN:
return Codes.NOT_CORRECT, str(e.msg)
else:
return Codes.UNKNOWN_ERROR, str(e)
except Exception as e:
connection.rollback()
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
return User.get_user(email)
def status():
response = {}
tables = [
Tables.TABLE_NAMES["USER"],
Tables.TABLE_NAMES["THREAD"],
Tables.TABLE_NAMES["FORUM"],
Tables.TABLE_NAMES["POST"],
]
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
for table in tables:
query = Query.SELECT.format(columns="COUNT(*)", table=table)
cursor.execute(query)
response[table] = cursor.fetchone()[0]
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
return Codes.OK, response
def details(email):
columns = 'u.*, group_concat(distinct f.following) as following,' \
'group_concat(distinct f1.follower) as followers,' \
'group_concat(distinct s.thread) as subscriptions'
query = Query.SELECT.format(
columns=columns,
table=Tables.TABLE_NAMES['USER'] + ' as u'
) + Query.LEFT_JOIN.format(
table=Tables.TABLE_NAMES['USER_FOLLOW'] + ' as f',
clause='u.email = f.follower'
) + Query.LEFT_JOIN.format(
table=Tables.TABLE_NAMES['USER_FOLLOW'] + ' as f1',
clause='u.email = f1.following'
) + Query.LEFT_JOIN.format(
table=Tables.TABLE_NAMES['USER_SUBSCRIBE'] + ' as s',
clause='u.email= s.user'
) + Query.WHERE.format(
clause='u.email = %r' % str(email)
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
user = dictfetchall(cursor)[0]
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
if not user['email']:
return Codes.NOT_FOUND, 'User %r not found' % str(email)
return Codes.OK, User.get_response_values(user)
def vote(post, vote):
if vote == -1:
column = 'dislikes'
points = '- 1'
elif vote == 1:
column = 'likes'
points = '+ 1'
else:
return Codes.NOT_CORRECT, 'Vote can be only \'1\' or \'-1\''
query = 'UPDATE ' + Tables.TABLE_NAMES['POST'] + \
' SET {0} = {0} + 1, points = points {1}'.format(column, points) + \
' WHERE id = %s' % post
connection = db_connect.get_connection()
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)
def list_by_forum(forum, request):
if 'order' not in request:
request['order'] = 'DESC'
columns = 'u.*, group_concat(distinct f.following) as following,' \
'group_concat(distinct f1.follower) as followers,' \
'group_concat(distinct s.thread) as subscriptions'
query = Query.SELECT.format(
columns=columns,
table=Tables.TABLE_NAMES['USER'] + ' as u'
) + Query.LEFT_JOIN.format(
table=Tables.TABLE_NAMES['USER_FOLLOW'] + ' as f',
clause='u.email = f.follower'
) + Query.LEFT_JOIN.format(
table=Tables.TABLE_NAMES['USER_FOLLOW'] + ' as f1',
clause='u.email = f1.following'
) + Query.LEFT_JOIN.format(
table=Tables.TABLE_NAMES['USER_SUBSCRIBE'] + ' as s',
clause='u.email= s.user'
) + Query.JOIN.format(
table=Tables.TABLE_NAMES['POST'] + ' as p',
clause='p.user = u.email'
) + Query.WHERE.format(
clause='p.forum = %r' % str(forum)
)
if 'since_id' in request:
query += Query.AND_CLAUSE.format(
clause='u.id >= %s' % request['since_id']
)
query += Query.GROUP_BY.format(
column='u.email'
) + Query.ORDER_BY.format(
column='p.user_name',
type=request['order']
)
if 'limit' in request:
query += Query.LIMIT.format(
count=request['limit']
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
users = dictfetchall(cursor)
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
response = []
for user in users:
response.append(User.get_response_values(user))
return Codes.OK, response
def unfollow(follower, following):
query = Query.DELETE.format(
table=Tables.TABLE_NAMES['USER_FOLLOW'],
clause='follower = %r AND following = %r' % (str(follower), str(following))
)
connection = db_connect.get_connection()
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 clear():
tables = []
for table in Tables.TABLE_NAMES:
tables.append(Tables.TABLE_NAMES[table])
queries = Query.truncate_query(tables)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
for query in queries:
cursor.execute(query)
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
return Codes.OK, "OK"
def get_forum(forum):
query = Query.SELECT.format(
columns='*',
table=Tables.TABLE_NAMES['FORUM']
) + Query.WHERE.format(
clause='short_name = %r' % str(forum)
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
response = dictfetchall(cursor)[0]
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
return Codes.OK, response
def update(email, name, about):
query = 'UPDATE ' + Tables.TABLE_NAMES['USER'] + \
' SET name = %r, about = %r ' % (str(name), str(about)) \
+ Query.WHERE.format(
clause='email = %r' % str(email)
)
connection = db_connect.get_connection()
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):
query = 'UPDATE ' + Tables.TABLE_NAMES['POST'] \
+ ' SET message = %r ' % str(message) \
+ Query.WHERE.format(
clause='id = %s' % post_id
)
connection = db_connect.get_connection()
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 list_by_thread(thread, request):
if 'order' not in request:
request['order'] = 'DESC'
columns = 'p.id, p.message, p.date, p.likes, p.dislikes, p.points, p.isApproved, ' \
'p.isHighlighted, p.isEdited, p.isSpam, p.isDeleted, p.forum, p.thread, p.user, p.parent'
query = Query.SELECT.format(
columns=columns,
table=Tables.TABLE_NAMES['POST'] + ' as p'
) + Query.WHERE.format(
clause='p.thread = %r' % str(thread)
)
if 'since' in request:
query += Query.AND_CLAUSE.format(
clause='p.date > %r' % str(request['since'])
)
query += Query.ORDER_BY.format(
column='p.date',
type=request['order']
)
if 'limit' in request:
query += Query.LIMIT.format(
count=request['limit']
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
post = dictfetchall(cursor)
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
response = []
code = Codes.OK
for post in post:
response.append(Post.get_response_values(post))
return code, response
def get_user(email):
query = Query.SELECT.format(
columns='*',
table=Tables.TABLE_NAMES['USER']
) + Query.WHERE.format(
clause='email = %r' % str(email)
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
response = dictfetchall(cursor)[0]
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
response['isAnonymous'] = bool(response['isAnonymous'])
return Codes.OK, response
def get_post(post):
query = Query.SELECT.format(
columns='id, message, date, isApproved, isHighlighted, isEdited, isSpam, isDeleted, forum, thread, user',
table=Tables.TABLE_NAMES['POST']
) + Query.WHERE.format(
clause='id = %s' % post
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
response = dictfetchall(cursor)[0]
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
response = Post.get_response_values(response)
return Codes.OK, response
def update_thread(thread, increment):
query = 'UPDATE ' + Tables.TABLE_NAMES['THREAD'] + \
' SET posts = posts '
if increment:
query += '+ '
else:
query += '- '
query += '1 WHERE id = %s' % thread
connection = db_connect.get_connection()
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 list_following(email):
columns = 'u.*, group_concat(distinct f.following) as following,' \
'group_concat(distinct f1.follower) as followers,' \
'group_concat(distinct s.thread) as subscriptions'
query = Query.SELECT.format(
columns=columns,
table=Tables.TABLE_NAMES['USER'] + ' as u'
) + Query.LEFT_JOIN.format(
table=Tables.TABLE_NAMES['USER_FOLLOW'] + ' as f',
clause='u.email = f.follower'
) + Query.LEFT_JOIN.format(
table=Tables.TABLE_NAMES['USER_FOLLOW'] + ' as f1',
clause='u.email = f1.following'
) + Query.LEFT_JOIN.format(
table=Tables.TABLE_NAMES['USER_SUBSCRIBE'] + ' as s',
clause='u.email= s.user'
) + Query.WHERE.format(
clause='f1.follower = %r' % str(email)
) + Query.GROUP_BY.format(
column='f1.following'
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
users = dictfetchall(cursor)
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
response = []
for user in users:
response.append(User.get_response_values(user))
return Codes.OK, response
def list_by_forum(forum, request, related=None):
if not related:
related = []
if 'order' not in request:
request['order'] = 'DESC'
columns = 'p.id, p.message, p.date, p.likes, p.dislikes, p.points, p.isApproved, ' \
'p.isHighlighted, p.isEdited, p.isSpam, p.isDeleted, p.forum, p.thread, p.user, p.parent'
if 'forum' in related:
columns += ', f.id f_id, f.name f_name, f.short_name f_short_name, f.user f_user'
if 'thread' in related:
columns += ', t.id t_id, t.title t_title, t.slug t_slug, t.message t_message, t.date t_date, ' \
't.posts t_posts, t.likes t_likes, t.dislikes t_dislikes, t.points t_points, ' \
't.isClosed t_isClosed, t.isDeleted t_isDeleted, t.forum t_forum, t.user t_user'
query = Query.SELECT.format(
columns=columns,
table=Tables.TABLE_NAMES['POST'] + ' as p'
)
if 'forum' in related:
query += Query.JOIN.format(
table=Tables.TABLE_NAMES['FORUM'] + ' as f',
clause='p.forum = f.short_name'
)
if 'thread' in related:
query += Query.JOIN.format(
table=Tables.TABLE_NAMES['THREAD'] + ' as t',
clause='p.thread = t.id'
)
query += Query.WHERE.format(
clause='p.forum = %r' % str(forum)
)
if 'since' in request:
query += Query.AND_CLAUSE.format(
clause='p.date > %r' % str(request['since'])
)
query += Query.ORDER_BY.format(
column='p.date',
type=request['order']
)
if 'limit' in request:
query += Query.LIMIT.format(
count=request['limit']
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
posts = dictfetchall(cursor)
except Exception as e:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
response = []
code = Codes.OK
for post in posts:
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]
if 'thread' in related:
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]
post['thread'] = Thread.get_response_values(post['thread'])
if 'user' in related:
code, post['user'] = User.details(post['user'])
response.append(Post.get_response_values(post))
return code, response
def details(post, related=None):
if not related:
related = []
columns = 'p.id, p.message, p.date, p.isApproved, p.isHighlighted, p.isEdited, p.isSpam, ' \
'p.isDeleted, p.forum, p.thread, p.user, p.dislikes, p.likes, p.points, p.parent'
if 'forum' in related:
columns += ', f.name f_name, f.short_name f_short_name, f.user f_user, f.id f_id'
if 'thread' in related:
columns += ', t.id t_id, t.forum t_forum, t.title t_title, t.isClosed t_isClosed, ' \
't.user t_user, t.date t_date, t.message t_message, t.slug t_slug, t.isDeleted t_isDeleted, ' \
't.posts t_posts, t.likes t_likes, t.dislikes t_dislikes, t.points t_points'
query = Query.SELECT.format(
columns=columns,
table=Tables.TABLE_NAMES['POST'] + ' as p'
)
if 'forum' in related:
query += Query.JOIN.format(
table=Tables.TABLE_NAMES['FORUM'] + ' as f',
clause='p.forum = f.short_name'
)
if 'thread' in related:
query += Query.JOIN.format(
table=Tables.TABLE_NAMES['THREAD'] + ' as t',
clause='p.thread = t.id'
)
query += Query.WHERE.format(
clause='p.id = %s' % post
)
connection = db_connect.get_connection()
try:
cursor = connection.cursor()
cursor.execute(query)
response = dictfetchall(cursor)[0]
except Exception as e:
if 'list index out of range' in str(e):
return Codes.NOT_FOUND, 'Post %s not found' % post
else:
return Codes.UNKNOWN_ERROR, str(e)
finally:
connection.close()
response = Post.get_response_values(response)
code = Codes.OK
if 'thread' in related:
response['t_date'] = str(response['t_date'])
response['t_isDeleted'] = bool(response['t_isDeleted'])
response['t_isClosed'] = bool(response['t_isClosed'])
response['thread'] = {}
keys = []
for val in response:
if val[:2] == 't_':
response['thread'][val[2:]] = response[val]
keys.append(val)
for key in keys:
del response[key]
if 'forum' in related:
response['forum'] = {}
keys = []
for val in response:
if val[:2] == 'f_':
response['forum'][val[2:]] = response[val]
keys.append(val)
for key in keys:
del response[key]
if 'user' in related:
code, response['user'] = User.details(response['user'])
return code, response
