def followers_list(email, type, params):
DBconnect.exist(entity="user", identifier="email", value=email)
if type == "follower":
where = "followee"
if type == "followee":
where = "follower"
query = "SELECT " + type + " FROM follower JOIN user ON user.email = follower." + type + \
" WHERE " + where + " = %s "
if "since_id" in params:
query += " AND user.id >= " + str(params["since_id"])
if "order" in params:
query += " ORDER BY user.name " + params["order"]
else:
query += " ORDER BY user.name DESC "
if "limit" in params:
query += " LIMIT " + str(params["limit"])
followers_ids_tuple = DBconnect.select_query(query=query, params=(email, ))
f_list = []
for id in followers_ids_tuple:
id = id[0]
f_list.append(users.details(email=id))
return f_list
def details(connect,id, related):
thread = DBconnect.select_query(connect,
'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM thread WHERE id = %s LIMIT 1;', (id, )
)
if len(thread) == 0:
raise Exception('No thread exists with id=' + str(id))
thread = thread[0]
thread = {
'date': str(thread[0]),
'forum': thread[1],
'id': thread[2],
'isClosed': bool(thread[3]),
'isDeleted': bool(thread[4]),
'message': thread[5],
'slug': thread[6],
'title': thread[7],
'user': thread[8],
'dislikes': thread[9],
'likes': thread[10],
'points': thread[11],
'posts': thread[12],
}
if "user" in related:
thread["user"] = users.details(connect,thread["user"])
if "forum" in related:
thread["forum"] = forums.details(connect=connect,short_name=thread["forum"], related=[])
return thread
def details(id, related):
thread = DBconnect.select_query(
'''select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, IFNULL(posts.posts, 0) FROM thread LEFT JOIN (SELECT thread, COUNT(*) as posts
FROM post
WHERE post.isDeleted = FALSE
GROUP BY thread) posts ON posts.thread = thread.id
WHERE id = %s''', (id, )
)
if len(thread) == 0:
raise Exception('No thread exists with id=' + str(id))
thread = thread[0]
thread = {
'date': str(thread[0]),
'forum': thread[1],
'id': thread[2],
'isClosed': bool(thread[3]),
'isDeleted': bool(thread[4]),
'message': thread[5],
'slug': thread[6],
'title': thread[7],
'user': thread[8],
'dislikes': thread[9],
'likes': thread[10],
'points': thread[11],
'posts': thread[12],
}
if "user" in related:
thread["user"] = users.details(thread["user"])
if "forum" in related:
thread["forum"] = forums.details(short_name=thread["forum"], related=[])
return thread
def details():
request_data = get_json(request)
required_data = ["user"]
try:
choose_required(data=request_data, required=required_data)
user_details = users.details(email=request_data["user"])
except Exception as e:
return json.dumps({"code": 1, "response": (e.message)})
return json.dumps({"code": 0, "response": user_details})
def details(connect,short_name, related):
forum = DBconnect.select_query(connect,
'select id, name, short_name, user FROM forum WHERE short_name = %s LIMIT 1;', (short_name, )
)
if len(forum) == 0:
raise ("No forum with exists short_name=" + short_name)
forum = forum_description(forum)
if "user" in related:
forum["user"] = users.details(connect,forum["user"])
return forum
def remove_follow(email1, email2):
follows = DBconnect.select_query(
'SELECT id FROM follower WHERE follower = %s AND followee = %s', (email1, email2, )
)
if len(follows) != 0:
DBconnect.update_query('DELETE FROM follower WHERE follower = %s AND followee = %s', (email1, email2, ))
else:
raise Exception("No such following")
return users.details(email1)
def details(connect,details_id, related):
post = post_query(connect,details_id)
if post is None:
raise Exception("no post with id = " + details_id)
if "user" in related:
post["user"] = users.details(connect,post["user"])
if "forum" in related:
post["forum"] = forums.details(connect=connect,short_name=post["forum"], related=[])
if "thread" in related:
post["thread"] = threads.details(connect=connect,id=post["thread"], related=[])
return post
def add_follow(email1, email2):
DBconnect.exist(entity="user", identifier="email", value=email1)
DBconnect.exist(entity="user", identifier="email", value=email2)
if email1 == email2:
raise Exception("User with email=" + email1 + " can't follow himself")
follows = DBconnect.select_query(
'SELECT id FROM follower WHERE follower = %s AND followee = %s', (email1, email2, )
)
if len(follows) == 0:
DBconnect.update_query('INSERT INTO follower (follower, followee) VALUES (%s, %s)', (email1, email2, ))
user = users.details(email1)
return user
def thread_list(entity, identifier, related, params):
# DBconnect.exist(entity="user", identifier="email", value=identifier)
query = "SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts FROM thread WHERE " + entity + " = %s "
parameters = [identifier]
if "since" in params:
query += " AND date >= %s"
parameters.append(params["since"])
if "order" in params:
query += " ORDER BY date " + params["order"]
else:
query += " ORDER BY date DESC "
if "limit" in params:
query += " LIMIT " + str(params["limit"])
print(query.format(parameters))
begin = int(round(time.time() * 1000))
thread_ids_tuple = DBconnect.select_query(query=query, params=parameters)
end = int(round(time.time() * 1000))
print(end - begin)
begin = int(round(time.time() * 1000))
thread_list = []
for thread in thread_ids_tuple:
thread = {
'date': str(thread[0]),
'forum': thread[1],
'id': thread[2],
'isClosed': bool(thread[3]),
'isDeleted': bool(thread[4]),
'message': thread[5],
'slug': thread[6],
'title': thread[7],
'user': thread[8],
'dislikes': thread[9],
'likes': thread[10],
'points': thread[11],
'posts': thread[12],
}
if "user" in related:
thread["user"] = users.details(thread["user"])
if "forum" in related:
thread["forum"] = forums.details(short_name=thread["forum"], related=[])
thread_list.append(thread)
end = int(round(time.time() * 1000))
print(end - begin)
return thread_list
def posts_list(connect, entity, params, identifier, related=[]):
query = "SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, likes, message, " \
"parent, points, thread, user FROM post WHERE " + entity + " = %s "
parameters = [identifier]
if "since" in params:
query += " AND date >= %s"
parameters.append(params["since"])
query += " ORDER BY date " + params["order"]
if "limit" in params:
query += " LIMIT " + str(params["limit"])
post_ids = DBconnect.select_query(connect=connect,query=query, params=parameters)
post_list = []
for post in post_ids:
pf = {
'date': str(post[0]),
'dislikes': post[1],
'forum': post[2],
'id': post[3],
'isApproved': bool(post[4]),
'isDeleted': bool(post[5]),
'isEdited': bool(post[6]),
'isHighlighted': bool(post[7]),
'isSpam': bool(post[8]),
'likes': post[9],
'message': post[10],
'parent': post[11],
'points': post[12],
'thread': post[13],
'user': post[14],
}
if "user" in related:
pf["user"] = users.details(connect,pf["user"])
if "forum" in related:
pf["forum"] = forums.details(connect,short_name=pf["forum"], related=[])
if "thread" in related:
pf["thread"] = threads.details(connect,id=pf["thread"], related=[])
post_list.append(pf)
return post_list
def thread_list(connect,entity, identifier, related, params):
query = "SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts " \
"FROM thread WHERE " + entity + " = %s "
parameters = [identifier]
if "since" in params:
query += " AND date >= %s"
parameters.append(params["since"])
if "order" in params:
query += " ORDER BY date " + params["order"]
else:
query += " ORDER BY date DESC "
if "limit" in params:
query += " LIMIT " + str(params["limit"])
thread_ids_tuple = DBconnect.select_query(connect,query=query, params=parameters)
thread_list = []
for thread in thread_ids_tuple:
thread = {
'date': str(thread[0]),
'forum': thread[1],
'id': thread[2],
'isClosed': bool(thread[3]),
'isDeleted': bool(thread[4]),
'message': thread[5],
'slug': thread[6],
'title': thread[7],
'user': thread[8],
'dislikes': thread[9],
'likes': thread[10],
'points': thread[11],
'posts': thread[12],
}
if "user" in related:
thread["user"] = users.details(connect,thread["user"])
if "forum" in related:
thread["forum"] = forums.details(connect=connect,short_name=thread["forum"], related=[])
thread_list.append(thread)
return thread_list
def posts_list(entity, params, identifier, related=[]):
# if entity == "forum":
# DBconnect.exist(entity="forum", identifier="short_name", value=identifier)
# if entity == "thread":
# DBconnect.exist(entity="thread", identifier="id", value=identifier)
# if entity == "user":
# DBconnect.exist(entity="user", identifier="email", value=identifier)
query = "SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, likes, message, " \
"parent, points, thread, user, path FROM post WHERE " + entity + " = %s "
parameters = [identifier]
if "since" in params:
query += " AND date >= %s"
parameters.append(params["since"])
if "sort" in params:
if params["sort"] == "flat":
query += " ORDER BY date "
elif params["sort"] == "tree":
if params["order"] == "desc":
query += " ORDER BY SUBSTRING_INDEX(path, '.', 2) DESC, TRIM(LEADING SUBSTRING_INDEX(path, '.', 2) FROM path) "
else:
query += " ORDER BY path "
if "limit" in params:
query += " LIMIT " + str(params["limit"])
else:
bound_query = "SELECT SUBSTRING_INDEX(MIN(t.path), '.', 2) AS left_bound FROM "\
"(SELECT path FROM post WHERE thread = %s AND parent IS NULL ORDER BY path " + params["order"] or ""
if "limit" in params:
bound_query += " LIMIT " + str(params["limit"])
bound_query += ") AS t;"
left = DBconnect.select_query(bound_query, (identifier, ))[0][0]
bound_query = "SELECT MAX(t.path) AS left_bound FROM "\
"(SELECT path FROM post WHERE thread = %s AND parent IS NULL ORDER BY path " + params["order"] or ""
if "limit" in params:
bound_query += " LIMIT " + str(params["limit"])
bound_query += ") AS t;"
right = DBconnect.select_query(bound_query, (identifier, ))[0][0]
query += " AND SUBSTRING_INDEX(path, '.', 2) BETWEEN %s AND %s ORDER BY " + \
params["order"] == "desc" and " SUBSTRING_INDEX(path, '.', 2) DESC, " \
"TRIM(LEADING SUBSTRING_INDEX(path, '.', 2) FROM path) " or " path "
parameters.append(left)
parameters.append(right)
else:
if "order" in params:
query += " ORDER BY date " + params["order"]
else:
query += " ORDER BY date DESC"
if "limit" in params:
query += " LIMIT " + str(params["limit"])
post_ids = DBconnect.select_query(query=query, params=parameters)
post_list = []
if "sort" in params and params["sort"] != "flat":
for post in post_ids:
path = post[15].split('.')[1:]
if len(path) == 1:
pf = {
'date': str(post[0]),
'dislikes': post[1],
'forum': post[2],
'id': post[3],
'isApproved': bool(post[4]),
'isDeleted': bool(post[5]),
'isEdited': bool(post[6]),
'isHighlighted': bool(post[7]),
'isSpam': bool(post[8]),
'likes': post[9],
'message': post[10],
'parent': post[11],
'points': post[12],
'thread': post[13],
'user': post[14],
'childs': walk(post_ids, path[0], 2)
}
post_list.append(pf)
else:
for post in post_ids:
pf = {
'date': str(post[0]),
'dislikes': post[1],
'forum': post[2],
'id': post[3],
'isApproved': bool(post[4]),
'isDeleted': bool(post[5]),
'isEdited': bool(post[6]),
'isHighlighted': bool(post[7]),
'isSpam': bool(post[8]),
'likes': post[9],
'message': post[10],
'parent': post[11],
'points': post[12],
'thread': post[13],
'user': post[14],
}
if "user" in related:
pf["user"] = users.details(pf["user"])
if "forum" in related:
pf["forum"] = forums.details(short_name=pf["forum"], related=[])
if "thread" in related:
pf["thread"] = threads.details(id=pf["thread"], related=[])
post_list.append(pf)
return post_list
def remove_follow(connect,email1, email2):
DBconnect.update_query(connect,'DELETE FROM follower WHERE follower = %s AND followee = %s', (email1, email2, ))
return users.details(connect,email1)
def add_follow(connect,email1, email2):
DBconnect.update_query(connect,'INSERT INTO follower (follower, followee) VALUES (%s, %s)', (email1, email2, ))
user = users.details(connect,email1)
return user
