def restore_remove(connection, thread, isDeleted):
posts = 0
current_state = db_tools.execute_select(connection,
"SELECT isDeleted FROM Threads WHERE id = %s", (thread,))[0][0]
if current_state != isDeleted:
if isDeleted == 0:
query = 'SELECT COUNT(id) FROM Posts WHERE thread = %s'
params = (thread, )
posts = db_tools.execute_select(connection, query, params)[0][0]
query_thread = 'UPDATE Threads SET isDeleted = %s, posts = %s WHERE id = %s'
query_post = 'UPDATE Posts SET isDeleted = %s WHERE thread = %s'
params_thread = (isDeleted, posts, thread, )
try:
db_tools.execute_update(connection, query_thread, params_thread)
db_tools.execute_update(connection, query_post, (isDeleted, thread, ))
except Exception as e:
print (e.message)
response = {"thread": thread}
return response
def create(connection, username, about, name, email, optional):
isAnonymous = 0
if "isAnonymous" in optional:
isAnonymous = optional["isAnonymous"]
query = 'INSERT INTO Users (username, about, name, email, isAnonymous) VALUES (%s, %s, %s, %s, %s)'
params = (
username,
about,
name,
email,
isAnonymous,
)
inserted_id = db_tools.execute_update(connection, query, params)
if inserted_id == "Error":
raise Exception("5")
user = db_tools.execute_select(
connection,
'SELECT id, email, about, isAnonymous, name, username FROM Users WHERE email = %s',
(email, ))
return serialize_u(user)
def list(connection, required, optional, related):
query = "SELECT date, dislikes, forum, id, isClosed, isDeleted, likes, message, points, posts, slug, title, user FROM Threads WHERE "
threads = []
if 'forum' in required:
query += "forum = " + "\'" + str(required["forum"][0]) + "\'"
if 'user' in required:
query += "user = "******"\'" + str(required["user"][0]) + "\'"
if 'since' in optional:
since = optional["since"][0]
query += " AND date >= " + "\'" + str(since) + "\'"
if 'order' in optional:
order = optional["order"][0]
query += " ORDER BY date " + "".join(optional["order"])
if 'limit' in optional:
limit = optional["limit"][0]
query += " LIMIT " + "".join(optional["limit"])
try:
threads = db_tools.execute_select(connection, query, ())
except Exception as e:
print (e.message)
response = []
if threads != ():
for thread in threads:
thread = {
'date': str(thread[0]),
'dislikes': thread[1],
'forum': thread[2],
'id': thread[3],
'isClosed': bool(thread[4]),
'isDeleted': bool(thread[5]),
'likes': thread[6],
'message': thread[7],
'points': thread[8],
'posts': thread[9],
'slug': thread[10],
'title': thread[11],
'user': thread[12]
}
if "user" in related:
try:
thread["user"] = user_tools.details(connection, thread["user"])
except Exception:
pass
if "forum" in related:
thread["forum"] = forum_tools.details(connection, thread["forum"], [])
response.append(thread)
return response
def dec_posts(connection, post):
query = 'SELECT thread FROM Posts WHERE id = %s'
params = (post, )
thread = db_tools.execute_select(connection, query, params)[0][0]
query = 'UPDATE Threads SET posts = posts - 1 WHERE id = %s'
params = (thread, )
db_tools.execute_update(connection, query, params)
return
def set_post_path(connection, post, post_id):
update_path = "UPDATE Posts SET path = %s WHERE id = %s"
parent_path = "SELECT path FROM Posts WHERE id = %s"
if post["parent"] is None:
path = enlength(str(post_id))
else:
parent_path = db_tools.execute_select(connection, parent_path, (post["parent"],))
path = "".join(parent_path[0]) + "." + enlength(str(post_id))
db_tools.execute_update(connection, update_path, (path, post_id,))
def post_query(connection, id):
post = db_tools.execute_select(
connection,
'SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, likes, message, parent, points, thread, user FROM Posts WHERE id = %s ;', (
id, )
)
if len(post) == 0:
return None
post = serialize_f(post)
return post
def create(connection, name, short_name, user):
query = 'INSERT INTO Forums (name, short_name, user) VALUES (%s, %s, %s)'
params = (name, short_name, user, )
inserted_id = db_tools.execute_update(connection, query, params)
query = 'SELECT id, name, short_name, user FROM Forums WHERE short_name = %s'
params = (short_name, )
forum = db_tools.execute_select(connection, query, params)
response = serialize_f(forum)
return response
def list_subsriptions(connection, user_email):
query = 'SELECT thread FROM Subscribe WHERE user = %s'
params = (user_email, )
subscriptions = db_tools.execute_select(connection, query, params)
response = []
if len(subscriptions) == 0:
return response
for sub in subscriptions:
response.append(str(sub))
return response
def details(connection, user_email):
query = 'SELECT id, email, about, isAnonymous, name, username FROM Users WHERE email = %s'
params = (user_email, )
user = db_tools.execute_select(connection, query, params)
if len(user) == 0:
raise Exception("User not found")
user = serialize_u(user)
query = 'SELECT followee FROM Follow WHERE follower = %s'
following = db_tools.execute_select(connection, query, params)
user["following"] = to_list(following)
query = 'SELECT follower FROM Follow WHERE followee = %s'
followers = db_tools.execute_select(connection, query, params)
user["followers"] = to_list(followers)
query = 'SELECT thread FROM Subscribe WHERE user = %s'
subscriptions = db_tools.execute_select(connection, query, params)
user["subscriptions"] = to_list(subscriptions)
return user
def details(connection, short_name, optional):
query = 'SELECT id, name, short_name, user FROM Forums WHERE short_name = %s'
params = (short_name, )
forum = db_tools.execute_select(connection, query, params)
if len(forum) == 0:
raise Exception("Forum not found")
forum = serialize_f(forum)
if "user" in optional:
try:
forum["user"] = user_tools.details(connection, forum["user"])
except Exception:
pass
return forum
def restore_remove(connection, post, isDeleted):
current_state = db_tools.execute_select(connection,
"SELECT isDeleted FROM Posts WHERE id = %s", (post,))[0][0]
query = "UPDATE Posts SET isDeleted = %s WHERE id = %s"
params = (isDeleted, post)
if current_state != isDeleted:
if isDeleted == 0:
thread_tools.inc_posts(connection, post)
else:
thread_tools.dec_posts(connection, post)
db_tools.execute_update(connection, query, params)
response = {"post": post}
return response
def subscribe(connection, user, thread):
query = 'INSERT INTO Subscribe (thread, user) VALUES (%s, %s)'
params = (thread, user, )
subscriptions = []
try:
db_tools.execute_update(connection, query, params)
except Exception as e:
print (e.message)
query = 'SELECT thread, user FROM Subscribe WHERE thread = %s AND user = %s'
try:
subscriptions = db_tools.execute_select(connection, query, params)
except Exception as e:
print (e.message)
result = {"thread": subscriptions[0][0], "user": subscriptions[0][1]}
return result
def details(connection, id, related):
query = 'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts FROM Threads WHERE id = %s'
params = (id, )
thread = db_tools.execute_select(connection, query, params)
if len(thread) == 0:
raise Exception('Thread not founded')
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:
try:
thread["user"] = user_tools.details(connection, thread["user"])
except Exception:
pass
if "forum" in related:
try:
thread["forum"] = forum_tools.details(connection, short_name=thread["forum"], optional=[])
except Exception:
pass
return thread
def list_users(connection, short_name, optional):
query = "SELECT id, name, email FROM Users WHERE email IN (SELECT DISTINCT user FROM Posts WHERE forum = %s)"
params = (str(short_name), )
response = []
if len(optional) != 0:
if "since_id" in optional:
query += " AND Users.id >= " + str(optional["since_id"][0])
if "order" in optional:
query += " ORDER BY name " + str(optional["order"][0])
if "limit" in optional:
query += " LIMIT " + str(optional["limit"][0])
posts = db_tools.execute_select(connection, query, params)
if posts is None or len(posts) == 0:
return response
for post in posts:
res = user_tools.details(connection, str(post[2]))
response.append(res)
return response
def list_posts(connection, user_email, optional):
query = 'SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, likes, ' \
'message, parent, points, thread, user FROM Posts WHERE user = %s'
params = (user_email[0], )
if 'since' in optional:
query += " AND date >= " + "\'" + optional['since'][0] + "\'"
if 'order' in optional:
query += " ORDER BY date " + "".join(optional["order"][0])
if 'limit' in optional:
query += " LIMIT " + "".join(optional["limit"][0])
posts = db_tools.execute_select(connection, query, params)
if len(posts) != 0:
posts = posts[0]
posts = {
'date': posts[0].strftime("%Y-%m-%d %H:%M:%S"),
'dislikes': posts[1],
'forum': posts[2],
'id': posts[3],
'isApproved': posts[4],
'isDeleted': posts[5],
'isEdited': posts[6],
'isHighlighted': posts[7],
'isSpam': posts[8],
'likes': posts[9],
'message': posts[10],
'parent': posts[11],
'points': posts[12],
'thread': posts[13],
'user': posts[14]
}
return posts
def list_followees(connection, user_email, optional):
query = 'SELECT followee FROM Follow WHERE follower = %s'
params = (user_email[0], )
response = []
if len(optional) != 0:
if 'since_id' in optional:
query += " AND id >= " + optional['since_id'][0]
if 'order' in optional:
query += " ORDER BY followee " + optional["order"][0]
if 'limit' in optional:
query += " LIMIT " + optional["limit"][0]
followees = db_tools.execute_select(connection, query, params)
if followees is None or len(followees) == 0:
return response
for followee in followees:
response.append(details(connection, followee[0]))
return response
def posts_list(connection, entity, params, identifier, related=[]):
query = "SELECT * FROM Posts WHERE " + \
entity + " = " + '\'' + str(''.join(identifier)) + '\''
sort = 'flat'
order = 'desc'
limit = 0
parameters = tuple()
if "since" in params:
query += " AND date >= %s"
parameters += tuple(params["since"])
if "sort" in params:
sort = check_sort(params["sort"][0])
if "order" in params:
order = check_order(params["order"][0])
sort_order_stmt = make_sort_statement(sort, order)
query += sort_order_stmt
if "limit" in params:
limit = int(str(params["limit"][0]))
query += " LIMIT " + ''.join(params["limit"])
posts = db_tools.execute_select(connection, query, (parameters))
if sort != 'flat':
posts = get_child_posts(connection, posts,sort, limit)
post_list = []
for post in posts:
parent = post[9]
if post[9] == 0:
parent = None
responseDict = {
'date': str(post[4]),
'dislikes': post[6],
'forum': post[3],
'id': post[0],
'isApproved': bool(post[12]),
'isDeleted': bool(post[15]),
'isEdited': bool(post[13]),
'isHighlighted': bool(post[11]),
'isSpam': bool(post[14]),
'likes': post[7],
'message': post[5],
'parent': parent,
'points': post[8],
'thread': post[1],
'user': post[2],
}
if "user" in related:
try:
responseDict["user"] = user_tools.details(connection, responseDict["user"])
except Exception:
pass
if "forum" in related:
try:
responseDict["forum"] = forum_tools.details(connection, responseDict["forum"],[])
except Exception:
pass
if "thread" in related:
try:
responseDict["thread"] = thread_tools.details(connection, id=responseDict["thread"], related=[])
except Exception:
pass
post_list.append(responseDict)
return post_list