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 remove_restore(thread_id, status):
DBconnect.exist(entity="thread", identifier="id", value=thread_id)
DBconnect.update_query("UPDATE thread SET isDeleted = %s WHERE id = %s", (status, thread_id, ))
DBconnect.update_query("UPDATE post SET isDeleted = %s WHERE thread = %s", (status, thread_id, ))
response = {
"thread": thread_id
}
return response
def open_close_thread(id, isClosed):
DBconnect.exist(entity="thread", identifier="id", value=id)
DBconnect.update_query("UPDATE thread SET isClosed = %s WHERE id = %s", (isClosed, id, ))
response = {
"thread": id
}
return response
def save_forum(name, short_name, user):
DBconnect.exist(entity="user", identifier="email", value=user)
forum = DBconnect.select_query(
'select id, name, short_name, user FROM forum WHERE short_name = %s', (short_name, )
)
if len(forum) == 0:
DBconnect.update_query('INSERT INTO forum (name, short_name, user) VALUES (%s, %s, %s)',
(name, short_name, user, ))
forum = DBconnect.select_query(
'select id, name, short_name, user FROM forum WHERE short_name = %s', (short_name, )
)
return forum_description(forum)
def remove_restore(thread_id, status):
DBconnect.exist(entity="thread", identifier="id", value=thread_id)
if status == 1:
posts = 0
else:
posts = DBconnect.select_query("SELECT COUNT(id) FROM post WHERE thread = %s", (thread_id, ))[0]
DBconnect.update_query("UPDATE thread SET isDeleted = %s, posts = %s WHERE id = %s", (status, posts, thread_id, ))
DBconnect.update_query("UPDATE post SET isDeleted = %s WHERE thread = %s", (status, thread_id, ))
response = {
"thread": thread_id
}
return response
def remove_subscription(email, thread_id):
DBconnect.exist(entity="thread", identifier="id", value=thread_id)
DBconnect.exist(entity="user", identifier="email", value=email)
subscription = DBconnect.select_query(
'select thread, user FROM subscription WHERE user = %s AND thread = %s', (email, thread_id, )
)
if len(subscription) == 0:
raise Exception("user " + email + " does not subscribe thread #" + str(thread_id))
DBconnect.update_query('DELETE FROM subscription WHERE user = %s AND thread = %s', (email, thread_id, ))
response = {
"thread": subscription[0][0],
"user": subscription[0][1]
}
return response
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 save_subscription(email, thread_id):
DBconnect.exist(entity="thread", identifier="id", value=thread_id)
DBconnect.exist(entity="user", identifier="email", value=email)
subscription = DBconnect.select_query(
'select thread, user FROM subscription WHERE user = %s AND thread = %s', (email, thread_id, )
)
if len(subscription) == 0:
DBconnect.update_query('INSERT INTO subscription (thread, user) VALUES (%s, %s)', (thread_id, email, ))
subscription = DBconnect.select_query(
'select thread, user FROM subscription WHERE user = %s AND thread = %s', (email, thread_id, )
)
response = {
"thread": subscription[0][0],
"user": subscription[0][1]
}
return response
def save_thread(forum, title, isClosed, user, date, message, slug, optional):
DBconnect.exist(entity="user", identifier="email", value=user)
DBconnect.exist(entity="forum", identifier="short_name", value=forum)
isDeleted = 0
if "isDeleted" in optional:
isDeleted = optional["isDeleted"]
thread = DBconnect.select_query(
'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM thread WHERE slug = %s', (slug, )
)
if len(thread) == 0:
DBconnect.update_query('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, ))
thread = DBconnect.select_query(
'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],
'dislikes': thread[9],
'likes': thread[10],
'points': thread[11],
'posts': thread[12],
}
# Delete few extra elements
del response["dislikes"]
del response["likes"]
del response["points"]
del response["posts"]
return response
def create(date, thread, message, user, forum, optional):
DBconnect.exist(entity="thread", identifier="id", value=thread)
DBconnect.exist(entity="forum", identifier="short_name", value=forum)
DBconnect.exist(entity="user", identifier="email", value=user)
print (date, thread, message, user, forum, optional)
if len(DBconnect.select_query("SELECT thread.id as id FROM thread JOIN forum ON thread.forum = forum.short_name "
"WHERE thread.forum = %s AND thread.id = %s", (forum, thread, ))) == 0:
raise Exception("no thread with id = " + str(thread) + " in forum " + forum)
if (("parent" in optional) and (optional["parent"] != None)):
parent = DBconnect.select_query("SELECT post.id, post.path FROM post JOIN thread ON thread.id = post.thread "
"WHERE post.id = %s AND thread.id = %s", (optional["parent"], thread, ))
if len(parent) == 0:
raise Exception("No post with id = " + str(optional["parent"]))
path = parent[0][1]
else:
path = str(thread)
try:
query = "INSERT INTO post (message, user, forum, thread, date"
values = "(%s, %s, %s, %s, %s"
parameters = [message, user, forum, thread, date]
#optional_data = ["parent", "isApproved", "isHighlighted", "isEdited", "isSpam", "isDeleted"]
for param in optional:
query += ", " + param
values += ", %s"
parameters.append(optional[param])
except Exception as e:
print e.message
query += ") VALUES " + values + ")"
update_thread_posts = "UPDATE thread SET posts = posts + 1 WHERE id = %s"
update_path = "UPDATE post SET path = CONCAT_WS('.', '" + path + "', %s) WHERE id = %s"
con = DBConnection()
con = con.connect()
con.autocommit(False)
with con:
cursor = con.cursor()
# try:
con.begin()
cursor.execute(update_thread_posts, (thread, ))
cursor.execute(query, parameters)
con.commit()
# except Exception as e:
# con.rollback()
# raise Exception("Database error: " + e.message)
post_id = cursor.lastrowid
cursor.execute(update_path, (post_id, post_id, ))
cursor.close()
con.close()
post = post_query(post_id)
del post["dislikes"]
del post["likes"]
del post["parent"]
del post["points"]
return post
def create(date, thread, message, user, forum, optional):
DBconnect.exist(entity="thread", identifier="id", value=thread)
DBconnect.exist(entity="forum", identifier="short_name", value=forum)
DBconnect.exist(entity="user", identifier="email", value=user)
if (("parent" in optional) and (optional["parent"] != None)):
parent = DBconnect.select_query("SELECT id FROM post WHERE id = %s ", (optional["parent"], ))
if len(parent) == 0:
raise Exception("No post with id = " + str(optional["parent"]))
try:
query = "INSERT INTO post (message, user, forum, thread, date"
values = "(%s, %s, %s, %s, %s"
parameters = [message, user, forum, thread, date]
for param in optional:
query += ", " + param
values += ", %s"
parameters.append(optional[param])
except Exception as e:
print e.message
query += ") VALUES " + values + ")"
update_thread_posts = "UPDATE thread SET posts = posts + 1 WHERE id = %s"
update_parent = "UPDATE post SET parent = %s WHERE id = %s"
con = connect()
#con.autocommit(False)
with con:
cursor = con.cursor()
# try:
con.begin()
cursor.execute(update_thread_posts, (thread, ))
cursor.execute(query, parameters)
con.commit()
# except Exception as e:
# con.rollback()
# raise Exception("Database error: " + e.message)
post_id = cursor.lastrowid
#cursor.execute(update_parent, (parent, post_id, ))
cursor.close()
con.close()
post = post_query(post_id)
del post["dislikes"]
del post["likes"]
del post["parent"]
del post["points"]
return post
def update_user(email, about, name):
DBconnect.exist(entity="user", identifier="email", value=email)
DBconnect.update_query('UPDATE user SET email = %s, about = %s, name = %s WHERE email = %s',
(email, about, name, email, ))
return details(email)
def remove_restore(post_id, status):
DBconnect.exist(entity="post", identifier="id", value=post_id)
DBconnect.update_query("UPDATE post SET isDeleted = %s WHERE post.id = %s", (status, post_id, ))
return {
"post": post_id
}
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 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 update_thread(id, slug, message):
DBconnect.exist(entity="thread", identifier="id", value=id)
DBconnect.update_query('UPDATE thread SET slug = %s, message = %s WHERE id = %s', (slug, message, id, ))
return details(id=id, related=[])
