def vote(id, vote):
verify(table_name="Posts", param="id", val=id)
if vote == -1:
ins_upd_delQuery("UPDATE Posts SET dislikes=dislikes+1, points=points-1 where id = %s", (id, ))
else:
ins_upd_delQuery("UPDATE Posts SET likes=likes+1, points=points+1 where id = %s", (id, ))
return details(id=id, related=[])
def add_threads(forum, title, isClosed, user, date, message, slug, optional):
verify(table_name="Users", param="email", val=user)
verify(table_name="Forums", param="short_name", val=forum)
isDeleted = 0
if "isDeleted" in optional:
isDeleted = optional["isDeleted"]
thread = selectQuery(
'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM Threads WHERE slug = %s', (slug, )
)
if len(thread) == 0:
ins_upd_delQuery(
'INSERT INTO Threads (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 = selectQuery(
'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM Threads WHERE slug = %s', (slug, )
)
response = threads_info(thread)
del response["dislikes"]
del response["likes"]
del response["points"]
del response["posts"]
return response
def remove_restore(thread_id, status):
verify(table_name="Threads", param="id", val=thread_id)
ins_upd_delQuery("UPDATE Threads SET isDeleted = %s WHERE id = %s", (status, thread_id, ))
response = {
"thread": thread_id
}
return response
def open_close_threads(id, isClosed):
verify(table_name="Threads", param="id", val=id)
ins_upd_delQuery("UPDATE Threads SET isClosed = %s WHERE id = %s", (isClosed, id, ))
response = {
"thread": id
}
return response
def add_forum(name, short_name, user):
verify(table_name="Users", param="email", val=user)
forum = selectQuery('SELECT id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, ))
if len(forum) == 0:
ins_upd_delQuery('INSERT INTO Forums (name, short_name, user) VALUES (%s, %s, %s)', (name, short_name, user, ))
forum = selectQuery('SELECT id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, ))
return forums_info(forum)
def add_follows(email1, email2):
verify(table_name="Users", param="email", val=email1)
verify(table_name="Users", param="email", val=email2)
if email1 == email2:
raise Exception("User with email=" + email1 + " can't follow himself")
follows = selectQuery('SELECT id FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, ))
if len(follows) == 0:
ins_upd_delQuery('INSERT INTO Followers (follower, followee) VALUES (%s, %s)', (email1, email2, ))
user = users.details(email1)
return user
def remove_subscriptions(email, thread_id):
verify(table_name="Threads", param="id", val=thread_id)
verify(table_name="Users", param="email", val=email)
subscriptions = selectQuery(
'SELECT thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, )
)
if len(subscriptions) == 0:
raise Exception("user " + email + " does not subscribe thread #" + str(thread_id))
ins_upd_delQuery('DELETE FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, ))
response = {
"thread": subscriptions[0][0],
"user": subscriptions[0][1]
}
return response
def add_subscriptions(email, thread_id):
verify(table_name="Threads", param="id", val=thread_id)
verify(table_name="Users", param="email", val=email)
subscription = selectQuery(
'select thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, )
)
if len(subscription) == 0:
ins_upd_delQuery('INSERT INTO Subscriptions (thread, user) VALUES (%s, %s)', (thread_id, email, ))
subscription = selectQuery(
'SELECT thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, )
)
response = {
"thread": subscription[0][0],
"user": subscription[0][1]
}
return response
def list_users(short_name, opt):
verify(table_name="Forums", param="short_name", val=short_name)
query = "SELECT distinct email FROM Users, Posts, Forums WHERE Posts.user = Users.email " \
" and Forums.short_name = Posts.forum and Posts.forum = %s "
if "since_id" in opt:
query += " AND Users.id >= " + str(opt["since_id"])
if "order" in opt:
query += " ORDER BY Users.id " + opt["order"]
if "limit" in opt:
query += " LIMIT " + str(opt["limit"])
users_tuple = selectQuery(query, (short_name, ))
user_array = []
for user in users_tuple:
user = user[0]
user_array.append(users.details(user))
return user_array
def followers_list(email, type, params):
verify(table_name="Users", param="email", val=email)
if type == "follower":
where = "followee"
if type == "followee":
where = "follower"
query = "SELECT "+type+" FROM Followers, Users WHERE Users.email = Followers."+type+\
" AND "+where+" = %s "
if "since_id" in params:
query += " AND Users.id >= "+str(params["since_id"])
if "order" in params:
query += " ORDER BY Users.name "+params["order"]
else:
query += " ORDER BY Users.name DESC "
if "limit" in params:
query += " LIMIT "+str(params["limit"])
followers_ids_tuple = selectQuery(query=query, params=(email, ))
followers_array = []
for id in followers_ids_tuple:
id = id[0]
followers_array.append(users.details(email=id))
return followers_array
def threads_list(table_, parametr, related, params):
if table_ == "forum":
verify(table_name="Forums", param="short_name", val=parametr)
if table_ == "user":
verify(table_name="Users", param="email", val=parametr)
query = "SELECT id FROM Threads WHERE " + table_ + " = %s "
parameters = [parametr]
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 = selectQuery(query=query, params=parameters)
thread_array = []
for id in thread_ids_tuple:
id = id[0]
thread_array.append(details(id=id, related=related))
return thread_array
def create(date, thread, message, user, forum, opt):
verify(table_name="Threads", param="id", val=thread)
verify(table_name="Forums", param="short_name", val=forum)
verify(table_name="Users", param="email", val=user)
if len(selectQuery("SELECT Threads.id FROM Threads,Forums WHERE Threads.forum = Forums.short_name "
"AND Threads.forum = %s AND Threads.id = %s", (forum, thread, ))) == 0:
raise Exception("no thread with id = " + thread + " in forum " + forum)
if "parent" in opt:
if len(selectQuery("SELECT Posts.id FROM Posts, Threads WHERE Threads.id = Posts.thread "
"AND Posts.id = %s AND Threads.id = %s", (opt["parent"], thread, ))) == 0:
raise Exception("No post with id = " + opt["parent"])
query = "INSERT INTO Posts (message, user, forum, thread, date"
values = "(%s, %s, %s, %s, %s"
parameters = [message, user, forum, thread, date]
for param in opt:
query += ", "+param
values += ", %s"
parameters.append(opt[param])
query += ") VALUES " + values + ")"
update_thread_posts = "UPDATE Threads SET posts = posts + 1 WHERE id = %s"
con = Connector()
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)
#DatabaseConnection.connection.commit()
post_id = cursor.lastrowid
cursor.close()
con.close()
post = posts_query(post_id)
del post["dislikes"]
del post["likes"]
del post["parent"]
del post["points"]
return post
def remove_restore(post_id, status):
verify(table_name="Posts", param="id", val=post_id)
ins_upd_delQuery("UPDATE Posts SET isDeleted = %s WHERE Posts.id = %s", (status, post_id, ))
return {
"post": post_id
}
def update(id, message):
verify(table_name="Posts", param="id", val=id)
ins_upd_delQuery('UPDATE Posts SET message = %s WHERE id = %s', (message, id, ))
return details(id=id, related=[])
def update_threads(id, slug, message):
verify(table_name="Threads", param="id", val=id)
ins_upd_delQuery('UPDATE Threads SET slug = %s, message = %s WHERE id = %s',(slug, message, id, ))
return details(id=id, related=[])
def update_users(email, about, name):
verify(table_name="Users", param="email", val=email)
ins_upd_delQuery('UPDATE Users SET email = %s, about = %s, name = %s WHERE email = %s', (email, about, name, email, ))
return details(email)
