def details(con, email):
user = dbConnector.select_query(
con,
'SELECT email, id, isAnonymous, name, username, about FROM user WHERE email = %s LIMIT 1', (email,)
)
if len(user) == 0:
raise Exception("User not found")
user = user_description(user)
following = dbConnector.select_query(
con,
'SELECT followee FROM follower WHERE follower = %s', (email,)
)
user["following"] = to_list(following)
followers = dbConnector.select_query(
con,
'SELECT follower FROM follower WHERE followee = %s', (email,)
)
user["followers"] = to_list(followers)
subscriptions = dbConnector.select_query(
con,
'SELECT thread FROM subscription WHERE user = %s', (email,)
)
user["subscriptions"] = to_list(subscriptions)
return user
def create(con, forum, title, isClosed, user, date, message, slug, optional):
isDeleted = 0
if "isDeleted" in optional:
isDeleted = optional["isDeleted"]
thread = dbConnector.update_query(con,
'INSERT INTO thread (forum, title, isClosed, user, date, message, slug, isDeleted) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)',
(forum, title, isClosed, user, date, message, slug, isDeleted,))
if thread == "Error":
raise Exception("Thread already exists")
thread = dbConnector.select_query(con,
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts FROM thread WHERE slug = %s',
(slug,)
)
thread = thread[0]
response = {
'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],
}
return response
def details(con, id, related):
thread = dbConnector.select_query(
con,
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts FROM thread USE INDEX (full_except_about) WHERE id = %s;',
(id,)
)
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:
thread["user"] = user.details(con, thread["user"])
if "forum" in related:
thread["forum"] = forum.details(con=con, short_name=thread["forum"], related=[])
return thread
def list(con, required, optional, related):
query = "SELECT date, dislikes, forum, id, isClosed, isDeleted, likes, message, points, posts, slug, title, user FROM thread WHERE "
params = []
if 'forum' in required:
query += "forum = %s USE INDEX (forum_full) "
params.append(required["forum"][0])
# query += "forum = " + "\'" + str(required["forum"][0]) + "\'"
else:
query += "user = %s USE INDEX (user_full) "
params.append(required["user"][0])
# query += "user = "******"\'" + str(required["user"][0]) + "\'"
if 'since' in optional:
params.append(optional["since"][0])
query += " AND date >= %s "
if 'order' in optional:
query += " ORDER BY date " + "".join(optional["order"])
if 'limit' in optional:
query += " LIMIT " + "".join(optional["limit"])
try:
threads = dbConnector.select_query(con, query, params)
except Exception as e:
print (e.message)
response = []
if threads != ():
for k in threads:
k = {
'date': str(k[0]), # .strftime("%Y-%m-%d %H:%M:%S"),
'dislikes': k[1],
'forum': k[2],
'id': k[3],
'isClosed': bool(k[4]),
'isDeleted': bool(k[5]),
'likes': k[6],
'message': k[7],
'points': k[8],
'posts': k[9],
'slug': k[10],
'title': k[11],
'user': k[12]
}
if "user" in related:
k["user"] = user.details(con, k["user"])
if "forum" in related:
k["forum"] = forum.details(con=con, short_name=k["forum"], related=[])
response.append(k)
return response
def post_query(connection, id):
post = dbConnector.select_query(
connection,
"SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, likes, message, parent, points, thread, user FROM post WHERE id = %s ;",
(id,),
)
if len(post) == 0:
return None
post = post_formated(post)
return post
def details(con, short_name, related):
forum = dbConnector.select_query(
con, 'SELECT id, name, short_name, user FROM forum WHERE short_name = %s', (short_name,)
)
if len(forum) == 0:
raise Exception("Forum " + short_name + " not found")
forum = forum_description(forum)
if "user" in related:
forum["user"] = user.details(con, forum["user"])
return forum
def create(con, name, short_name, user): dbConnector.update_query( con, 'INSERT INTO forum (name, short_name, user) VALUES (%s, %s, %s)', (name, short_name, user) ); # check result of update query: forum = dbConnector.select_query( con, 'SELECT id, name, short_name, user FROM forum WHERE short_name = %s', (short_name,) ) return forum_description(forum);
def create(connection, name, short_name, user):
dbConnector.update_query(
connection,
'INSERT INTO forum (name, short_name, user) VALUES (%s, %s, %s)', (name,
short_name, user)
)
forum = dbConnector.select_query(
connection,
'SELECT id, name, short_name, user FROM forum WHERE short_name = %s', (
short_name,)
)
return serialise_f(forum)
def posts_list(connection, 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 + " = " + "'" + str("".join(identifier)) + "'"
)
parameters = tuple()
if "since" in params:
query += " AND date >= %s"
parameters += tuple(params["since"])
query += " ORDER BY date " + "".join(params["order"])
if "limit" in params:
query += " LIMIT " + "".join(params["limit"])
parameters += tuple("")
query_res = dbConnector.select_query(connection, query, parameters)
post_list = []
for post in query_res:
responseDict = {
"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:
responseDict["user"] = user.details(connection, responseDict["user"])
if "forum" in related:
responseDict["forum"] = forum.details(connection, short_name=responseDict["forum"], related=[])
if "thread" in related:
responseDict["thread"] = thread.details(connection, id=responseDict["thread"], related=[])
post_list.append(responseDict)
return post_list
def posts_list(con, 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 + " = " + '\'' + str(''.join(identifier)) + '\''
parameters = tuple()
if "since" in params:
query += " AND date >= %s"
parameters += tuple(params["since"])
query += " ORDER BY date " + ''.join(params["order"])
if "limit" in params:
query += " LIMIT " + ''.join(params["limit"])
parameters += tuple('')
post_ids = dbConnector.select_query(con, query, 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"] = user.details(con, pf["user"])
if "forum" in related:
pf["forum"] = forum.details(con, short_name=pf["forum"], related=[])
if "thread" in related:
pf["thread"] = thread.details(con, id=pf["thread"], related=[])
post_list.append(pf)
return post_list
def subscribe(connection, user, thread):
query = "INSERT INTO subscription (thread, user) VALUES (\'" + \
str(thread) + "\',\'" + str(user) + "\')"
try:
dbConnector.update_query(connection, query, ())
except Exception as e:
print (e.message)
query = "SELECT thread, user FROM subscription WHERE thread = \'" + \
str(thread) + "\', " + "user = \'" + str(user) + "\'"
try:
sub = dbConnector.select_query(connection, query, ())
except Exception as e:
print (e.message)
result = {"thread": sub[0][0], "user": sub[0][1]}
return result
def forum_listUsers(con, forum_shortname, optional): query = "SELECT user.id, user.name, user.email FROM user " \ "INNER JOIN post ON user.email = post.user WHERE forum = \'" + str(forum_shortname) + "\')" if "since_id" in optional: query += " AND user.id >= " + str(optional["since_id"][0]) if "order" in optional: query += " ORDER BY user.name " + str(optional["order"][0]) if "limit" in optional: query += " LIMIT " + str(optional["limit"][0]) try: posts = dbConnector.select_query(con, query, ()) except Exception as e: print (e.message) response = [] for post in posts: res = user.details(con, str(post[2])) response.append(res) return response
def restore_remove(con, thread, isDeleted):
posts = 0
if isDeleted == 0:
query = "SELECT COUNT(id) FROM post WHERE thread = " + str(thread)
print query
posts = dbConnector.select_query(con, query, ())[0][0]
query_thread = "UPDATE thread SET isDeleted = " + str(isDeleted) + ", posts = " + str(posts) + " WHERE id = " + str(
thread)
query_post = "UPDATE post SET isDeleted = " + str(isDeleted) + " WHERE thread = " + str(thread)
try:
dbConnector.update_query(con, query_thread, ())
dbConnector.update_query(con, query_post, ())
except Exception as e:
print (e.message)
response = {
"thread": thread}
return response
def listFollowers(con, email, optional): query = "SELECT followee FROM follower WHERE follower = \'" + str(email[0]) + "\'" if 'since_id' in optional: query += " AND id >= " + optional['since_id'][0] if 'order' in optional: query += " ORDER BY followee " + "".join(optional["order"][0]) if 'limit' in optional: query += " LIMIT " + "".join(optional["limit"][0]) try: followers = dbConnector.select_query(con, query, ())[0] except Exception as e: print (e.message) response = [] for follower in followers: response.append(details(con, str(follower))) return response
def create(con, username, about, name, email, optional):
isAnonymous = 0;
if "isAnonymous" in optional:
isAnonymous = optional["isAnonymous"]
user = dbConnector.update_query(
con,
'INSERT INTO user (username, about, name, email, isAnonymous) VALUES (%s, %s, %s, %s, %s)',
(username, about, name, email, isAnonymous,)
)
if user == "Error":
raise Exception("5")
# check insertion
user = dbConnector.select_query(
con,
'SELECT id, email, about, isAnonymous, name, username FROM user WHERE email = %s',
(email,)
)
return user_description(user)
def status():
con = connect()
response = []
tables = ['user', 'thread', 'forum', 'post']
for table in tables:
currCount = len(dbConnector.select_query(con, 'SELECT id FROM ' + table, ()))
response.append(currCount)
statusResponse = {
'user': response[0],
'thread': response[1],
'forum': response[2],
'post': response[3]
}
con.close()
stat = statusResponse
return jsonify({
"code": STATUS_CODE['OK'],
"response": stat})
def listPosts(con, email, optional):
query = "SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, likes, message, parent, points, thread, user FROM post WHERE user = \'" + str(
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])
try:
posts = dbConnector.select_query(con, query, ())
except Exception as e:
print (e.message)
if posts != ():
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 dec_posts(con, post): query = "SELECT thread FROM post WHERE id = " + str(post) thread = dbConnector.select_query(con, query, ())[0][0] query = "UPDATE thread SET posts = posts - 1 WHERE id = " + str(thread) dbConnector.update_query(con, query, ()) return
