def dec_posts_count(connect,post):
thread = DB_connect.select_query(connect,"SELECT thread FROM Post WHERE id = %s", (post, ))
try:
DB_connect.update_query(connect,"UPDATE Thread SET posts = posts - 1 WHERE id = %s", (thread[0][0], ))
except Exception as e:
print(e.message)
return
def subscribe_user(connect, thread, user):
isDeleted = 0;
DB_connect.update_query(connect, "INSERT INTO Subscription (user, thread_id, isDeleted) VALUES (%s, %s, %s)", (user, thread, isDeleted))
return {
"thread": thread,
"user": user
}
def vote_for_post(connect, post, vote):
if vote == 1:
DB_connect.update_query(connect, "UPDATE Post SET likes = likes + 1 WHERE id = %s", (post,))
else:
if vote == -1:
DB_connect.update_query(connect, "UPDATE Post SET dislikes = dislikes + 1 WHERE id = %s", (post,))
else:
raise Exception("VallueError")
return show_post(connect, post, [])
def vote_for_thread(connect, thread, vote):
if (vote == 1):
DB_connect.update_query(connect,'UPDATE Thread SET likes = likes + 1 WHERE id = %s', (thread, ))
else:
if (vote == -1):
DB_connect.update_query(connect,'UPDATE Thread SET dislikes = dislikes + 1 WHERE id = %s',(thread, ))
else:
raise Exception("VallueError")
return show_thread(connect, thread, [])
def save_forum(connect, name, short_name, user):
print('save_forum:')
#str = DB_connect.select_query(connect, 'SELECT id FROM Forum WHERE short_name = %s', (short_name, ))
#if len(str) > 0:
# raise Exception("Exist")
DB_connect.update_query(connect, "INSERT INTO Forum (name, short_name, user) VALUES (%s, %s, %s)", (name, short_name, user, ))
forum = DB_connect.select_query(connect, 'SELECT id, name, short_name, user FROM Forum WHERE short_name = %s', (short_name, ))
return forum_description(forum)
def unsubscribe_user(connect, thread, user):
isDeleted = 1;
try:
DB_connect.update_query(connect, "UPDATE Subscription SET isDeleted = %s WHERE user = %s AND thread_id = %s ",(isDeleted, user, thread,))
except Exception as e:
raise Exception("user " + user + " does not subscribe thread #" + str(thread))
return {
"thread": thread,
"user": user
}
def follow_user(connect, follower, followee):
user1 = DB_connect.select_query(connect, 'SELECT email FROM User WHERE email = %s', (follower, ))
user2 = DB_connect.select_query(connect, 'SELECT email FROM User WHERE email = %s', (follower, ))
if user1 and user2:
str = DB_connect.update_query(connect,
'INSERT INTO Follow (user, follow) VALUES (%s, %s)', (follower, followee, ) )
else:
raise Exception("Error. Such users don't exist");
user = show_user(connect, follower)
return user
def remove_restore(connect,thread, isDeleted):
if isDeleted == 1:
posts = 0
else:
posts = DB_connect.select_query(connect,"SELECT COUNT(id) FROM Post WHERE thread = %s", (thread, ))[0][0]
DB_connect.update_query(connect,"UPDATE Thread SET isDeleted = %s, posts = %s WHERE id = %s", (isDeleted,posts, thread ))
DB_connect.update_query(connect,"UPDATE Post SET isDeleted = %s WHERE thread = %s", (isDeleted,thread, ))
return {
"thread": thread
}
def save_thread(connect, forum, title, isClosed, user, date, message, slug, optional):
isDeleted = 0;
if "isDeleted" in optional:
isDeleted= optional["isDeleted"]
str = DB_connect.update_query(connect,
'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, ))
max_id = DB_connect.select_query(connect, 'SELECT max(id) FROM Thread', ())
thread = DB_connect.select_query(connect,
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user FROM Thread WHERE id = %s', (max_id[0][0], ) )
return thread_description(thread)
def save_user(connect, username, about, name, email, optional):
isAnonim = 0
if "isAnonymous" in optional:
isAnonim = optional["isAnonymous"]
if isAnonim:
name = username = about = ''
str = DB_connect.update_query(connect,
'INSERT INTO User (email, about, name, username, isAnonymous) VALUES (%s, %s, %s, %s, %s)',
(email, about, name, username, isAnonim, ))
user = DB_connect.select_query(connect, 'Select email, about, id, isAnonymous, name, username FROM User WHERE email = %s', (email, ) )
return user_description(user)
def unfollow_user(connect, follower, followee):
str = DB_connect.update_query(connect,
'DELETE FROM Follow WHERE user = %s AND follow = %s', (follower, followee, ) )
if str == "Exist":
raise Exception("Such row in db does not exist");
user = show_user(connect, follower)
return user
def save_post(connect, date, thread, message, user, forum, optional):
try:
query = "INSERT INTO Post (date, thread, message, user, forum "
values = "(%s, %s, %s, %s, %s"
parameters = [date, thread, message, user, forum]
for param in optional:
query += ", " + param
values += ", %s"
parameters.append(optional[param])
except Exception as e:
print e.message
query += ") VALUES " + values + ")"
# str = DB_connect.update_query(connect, query, parameters)
update_thread_posts = "UPDATE Thread SET posts = posts + 1 WHERE id = %s"
# DB_connect.update_query(connect, "UPDATE Thread SET posts = posts + 1 WHERE id = %s", (thread, ))
# id_post = DB_connect.select_query(connect, "SELECT max(id) FROM Post", ())
con = connect
with con:
cursor = con.cursor()
cursor.execute(update_thread_posts, (thread,))
cursor.execute(query, parameters)
con.commit()
id_post = cursor.lastrowid
cursor.close()
post = DB_connect.select_query(
connect,
"SELECT date, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, message, parent, thread, user \
FROM Post WHERE forum = %s and thread = %s and user = %s and id = %s",
(forum, thread, user, id_post),
)
response = post_description(post)
return response
def users_list(connect, short_name, optional):
query = "SELECT id, email, name, username, isAnonymous, about FROM User WHERE email IN (SELECT DISTINCT user FROM Post WHERE forum = %s)"
params = [short_name]
if "since_id" in optional:
query += " AND id >= %s"
params.append(optional["since"])
if "order" in optional:
query += " ORDER BY name " + str(optional["order"])
else:
query += " ORDER BY name DESC "
if "limit" in optional:
query += " LIMIT " + str(optional["limit"])
users_ids = DB_connect.select_query(connect, query, params)
user_list = []
for user in users_ids:
usr = {
'id' : user[0],
'email' : user[1],
'name' : user[2],
'username' : user[3],
'isAnonymous': bool(user[4]),
'about' : user[5],
}
if usr['isAnonymous'] == True:
usr['about'] = usr['name'] = usr['username'] = None
usr["followers"] = users.followers(connect, usr["email"], "user")
usr["following"] = users.followers(connect, usr["email"], "follow")
usr["subscriptions"] = users.subscriptions(connect, usr["email"])
user_list.append(usr)
return user_list
def followers_list(connect, email, type, params):
where = "user"
if type == "user":
where = "follow"
query = "SELECT " + type + " FROM Follow JOIN User ON User.email = Follow." + 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 = DB_connect.select_query(connect=connect,query=query, params=(email, ))
f_list = []
for id in followers_ids_tuple:
id = id[0]
f_list.append(show_user(connect=connect,email=id))
return f_list
def followers(connect, email, type):
where = "user"
if type == "user":
where = "follow"
f_list = DB_connect.select_query(connect,
"SELECT " + type + " FROM Follow WHERE " + where + " = %s ", (email, ))
return convert_to_list(f_list)
def show_user(connect, email):
user = DB_connect.select_query(connect, 'Select email, about, id, isAnonymous, name, username FROM User WHERE email = %s', (email, ) )
user = user_description(user)
if user is None:
raise Exception("User with email " + email +" doesn't exist ")
user["followers"] = followers(connect, email, "user")
user["following"] = followers(connect, email, "follow")
user["subscriptions"] = subscriptions(connect, email)
return user
def show_forum(connect, short_name, related):
forum = DB_connect.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 short_name: " + short_name)
forum = forum_description(forum)
if "user" in related:
forum["user"] = users.show_user(connect, forum["user"])
return forum
def posts_list(connect, entity, params, identifier, related=[]):
query = (
"SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, likes, message, \
parent, (likes-dislikes) as points, thread, user FROM Post WHERE "
+ entity
+ " = %s "
)
print ("step2")
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"])
post_ids = DB_connect.select_query(connect, 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"] = users.show_user(connect, pf["user"])
if "forum" in related:
pf["forum"] = forums.show_forum(connect, pf["forum"], [])
if "thread" in related:
pf["thread"] = threads.show_thread(connect, pf["thread"], [])
post_list.append(pf)
return post_list
def show_thread(connect, thread, related):
thread_all = DB_connect.select_query(connect,
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, posts, (likes - dislikes) as points \
FROM Thread WHERE id = %s LIMIT 1;', (thread, ) )
if len(thread_all) == 0:
raise Exception('No thread with id=' + str(thread))
thread = thread_description(thread_all)
thread["dislikes"] = thread_all[0][9]
thread["likes"] = thread_all[0][10]
thread["posts"] = thread_all[0][11]
thread["points"] = thread_all[0][12]
if "user" in related:
thread["user"] = users.show_user(connect, thread["user"])
if "forum" in related:
thread["forum"] = forums.show_forum(connect, thread["forum"], [])
return thread
def threads_list(connect, entity, params, identifier, related=[]):
query = "SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, (likes - dislikes) as 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 " + str(params["order"])
else:
query += " ORDER BY date DESC "
if "limit" in params:
query += " LIMIT " + str(params["limit"])
thread_ids = DB_connect.select_query(connect, query, parameters)
thread_list = []
for thread in thread_ids:
thr = {
'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:
thr["user"] = users.show_user(connect,thr["user"])
if "forum" in related:
thr["forum"] = forums.show_forum(connect, thr["forum"], [])
thread_list.append(thr)
return thread_list
def show_post(connect, post, related):
post_all = DB_connect.select_query(
connect,
"SELECT date, forum, id, isApproved, isDeleted, isEdited, isHighlighted, isSpam, message, \
parent, thread, user, dislikes, likes, (likes - dislikes) as points \
FROM Post WHERE id = %s LIMIT 1;",
(post,),
)
if len(post_all) == 0:
raise Exception("No post with id=" + str(post))
post = post_description(post_all)
post["dislikes"] = post_all[0][12]
post["likes"] = post_all[0][13]
post["points"] = post_all[0][14]
if "user" in related:
print ("useeeeer")
post["user"] = users.show_user(connect, post["user"])
if "thread" in related:
post["thread"] = threads.show_thread(connect, post["thread"], [])
if "forum" in related:
post["forum"] = forums.show_forum(connect, post["forum"], [])
return post
def open_close_thread(connect, thread, isClosed):
DB_connect.update_query(connect,'UPDATE Thread SET isClosed = %s WHERE id = %s', (isClosed, thread,))
return {
"thread": thread
}
def update_thread(connect, message, slug, thread):
DB_connect.update_query(connect,'UPDATE Thread SET message = %s, slug = %s WHERE id = %s',
(message, slug, thread, ))
return show_thread(connect,thread, [])
def subscriptions(connect, email):
s_list = DB_connect.select_query(connect,
"Select thread_id FROM Subscription WHERE user = %s and isDeleted = 0 ", (email, ))
return convert_to_list(s_list)
def update_user(connect,email, about, name):
DB_connect.update_query(connect,'UPDATE User SET email = %s, about = %s, name = %s WHERE email = %s',
(email, about, name, email, ))
return show_user(connect,email)
def inc_posts_count(connect,post):
thread = DB_connect.select_query(connect,"SELECT thread FROM Post WHERE id = %s", (post, ))
DB_connect.update_query(connect,"UPDATE Thread SET posts = posts + 1 WHERE id = %s", (thread[0][0], ))
return
def update_post(connect, post, message):
DB_connect.update_query(connect, "UPDATE Post SET message = %s WHERE id = %s", (message, post))
return show_post(connect, post, [])
def restore_post(connect, post):
is_deleted = 0
str = DB_connect.update_query(connect, "UPDATE Post SET isDeleted = %s WHERE id = %s", (is_deleted, post))
threads.inc_posts_count(connect, post)
return {"post": post}
