def thread_list(entity, entity_attr, related, params):
if entity == "forum":
db_funcs.db_exist(entity="Forums", entity_attr="short_name", attr_value=entity_attr)
else:
# if entity == "user":
db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=entity_attr)
select = "SELECT id" \
" FROM Threads" \
" WHERE " + entity + " = %s "
select_params = [entity_attr]
if "since" in params:
select += " AND date >= %s"
select_params.append(params["since"])
if "order" in params:
select += " ORDER BY date " + params["order"]
else:
select += " ORDER BY date DESC "
if "limit" in params:
select += " LIMIT " + str(params["limit"])
threads = db_funcs.db_select(query=select, query_params=select_params)
list_t = []
for thread in threads:
list_t.append(thread_details(thread_id=thread[0], related=related))
return list_t
def thread_update(thread_id, slug, message):
db_funcs.db_exist(entity="Threads", entity_attr="id", attr_value=thread_id)
db_funcs.db_insert_or_delete_or_update(
'UPDATE Threads'
' SET slug = %s, message = %s'
' WHERE id = %s',
(slug, message, thread_id, )
)
return thread_details(thread_id=thread_id, related=[])
def user_update_profile(email, about, name):
db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=email)
db_funcs.db_insert_or_delete_or_update(
'UPDATE Users'
' SET about = %s, name = %s'
' WHERE email = %s',
(about, name, email, )
)
return user_details(email)
def post_update(post_id, message):
db_funcs.db_exist(entity="Posts", entity_attr="id", attr_value=post_id)
db_funcs.db_insert_or_delete_or_update(
'UPDATE Posts'
' SET message = %s'
' WHERE id = %s',
(message, post_id, )
)
return post_details(post_id=post_id, related=[])
def post_remove_or_restore(post_id, status):
db_funcs.db_exist(entity="Posts", entity_attr="id", attr_value=post_id)
db_funcs.db_insert_or_delete_or_update(
"UPDATE Posts "
"SET isDeleted = %s "
"WHERE id = %s",
(status, post_id, )
)
post_response = {"post": post_id}
return post_response
def forum_create(name, short_name, user):
db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=user)
forum = forum_get(short_name)
if not len(forum):
db_funcs.db_insert_or_delete_or_update(
'INSERT INTO Forums (name, short_name, user) '
'VALUES (%s, %s, %s)',
(name, short_name, user, )
)
forum = forum_get(short_name)
return forum_describe(forum[0])
def thread_remove_or_restore(thread_id, is_deleted):
db_funcs.db_exist(entity="Threads", entity_attr="id", attr_value=thread_id)
db_funcs.db_insert_or_delete_or_update(
"UPDATE Threads"
" SET isDeleted = %s"
" WHERE id = %s",
(is_deleted, thread_id, )
)
thread_response = {
"thread": thread_id
}
return thread_response
def user_follow(follower_email, followee_email):
if follower_email == followee_email:
raise Exception("followers: follower_email=followee_email=" + follower_email)
db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=follower_email)
db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=followee_email)
follows = follower_select(follower_email, followee_email)
if not len(follows):
db_funcs.db_insert_or_delete_or_update(
'INSERT INTO Followers (follower, followee)'
' VALUES (%s, %s)',
(follower_email, followee_email, )
)
return db_users_func.user_details(follower_email)
def thread_subscribe(sub_email, thread_id):
db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=sub_email)
db_funcs.db_exist(entity="Threads", entity_attr="id", attr_value=thread_id)
subscription = subscription_select(sub_email, thread_id)
if not len(subscription):
db_funcs.db_insert_or_delete_or_update(
'INSERT INTO Subscriptions (thread, user)'
' VALUES (%s, %s)',
(thread_id, sub_email, )
)
subscription = subscription_select(sub_email, thread_id)
subscription_response = {
"thread": subscription[0][0],
"user": subscription[0][1]
}
return subscription_response
def user_unfollow(follower_email, followee_email):
if follower_email == followee_email:
raise Exception("followers: follower_email=followee_email=" + follower_email)
db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=follower_email)
db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=followee_email)
follows = follower_select(follower_email, followee_email)
if len(follows):
db_funcs.db_insert_or_delete_or_update(
'DELETE'
' FROM Followers'
' WHERE id= %s',
(follows[0][0], )
)
else:
raise Exception("followers: following not found")
return db_users_func.user_details(follower_email)
def post_vote(post_id, vote):
db_funcs.db_exist(entity="Posts", entity_attr="id", attr_value=post_id)
if vote == -1:
db_funcs.db_insert_or_delete_or_update(
"UPDATE Posts"
" SET dislikes=dislikes+1, points=points-1"
" WHERE id = %s",
(post_id, )
)
else:
# if vote == 1:
db_funcs.db_insert_or_delete_or_update(
"UPDATE Posts"
" SET likes=likes+1, points=points+1"
" WHERE id = %s",
(post_id, )
)
return post_details(post_id=post_id, related=[])
def thread_vote(thread_id, vote):
db_funcs.db_exist(entity="Threads", entity_attr="id", attr_value=thread_id)
if vote == -1:
db_funcs.db_insert_or_delete_or_update(
"UPDATE Threads"
" SET dislikes = dislikes + 1, points = points - 1"
" WHERE id = %s",
(thread_id, )
)
else:
# if vote == 1:
db_funcs.db_insert_or_delete_or_update(
"UPDATE Threads"
" SET likes = likes + 1, points = points + 1"
" WHERE id = %s",
(thread_id, )
)
return thread_details(thread_id=thread_id, related=[])
def user_details(email):
db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=email)
user = user_select(email)
if not len(user):
raise Exception("user: user with email " + email + " not found")
user_response = user_describe(user[0])
user_response["followers"] = user_follower(email, "follower")
user_response["following"] = user_follower(email, "followee")
list_s = []
subscriptions = db_funcs.db_select(
'SELECT thread'
' FROM Subscriptions'
' WHERE user = %s',
(email, )
)
for sub in subscriptions:
list_s.append(sub[0])
user_response["subscriptions"] = list_s
return user_response
def thread_create(forum, title, is_closed, user, date, message, slug, optional):
db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=user)
db_funcs.db_exist(entity="Forums", entity_attr="short_name", attr_value=forum)
is_deleted = 0
if "isDeleted" in optional:
is_deleted = optional["isDeleted"]
thread = thread_select(slug)
if not len(thread):
db_funcs.db_insert_or_delete_or_update(
'INSERT INTO Threads (forum, title, isClosed, user, date, message, slug, isDeleted)'
' VALUES (%s, %s, %s, %s, %s, %s, %s, %s)',
(forum, title, is_closed, user, date, message, slug, is_deleted, )
)
thread = thread_select(slug)
thread_response = thread_describe(thread[0])
del thread_response["dislikes"]
del thread_response["likes"]
del thread_response["points"]
del thread_response["posts"]
return thread_response
def forum_list_users(short_name, optional):
db_funcs.db_exist(entity="Forums", entity_attr="short_name", attr_value=short_name)
select = "SELECT DISTINCT email" \
" FROM Users u, Posts p, Forums f" \
" WHERE p.user = u.email" \
" AND f.short_name = p.forum" \
" AND p.forum = %s"
if "since_id" in optional:
select += " AND u.id >= " + str(optional["since_id"])
if "order" in optional:
select += " ORDER BY u.id " + optional["order"]
else:
select += " ORDER BY u.id DESC"
if "limit" in optional:
select += " LIMIT " + str(optional["limit"])
result = db_funcs.db_select(select, (short_name, ))
list_u = []
for record in result:
list_u.append(db_users_func.user_details(record[0]))
return list_u
def user_list_followers_or_following(type_email, type, params):
db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=type_email)
if type == "follower":
where_condition = "followee"
else:
# if type == "followee":
where_condition = "follower"
select = "SELECT " + type + \
" FROM Followers f, Users u" \
" WHERE u.email = f." + type +\
" AND " + where_condition + " = %s "
if "since_id" in params:
select += " AND u.id >= " + str(params["since_id"])
if "order" in params:
select += " ORDER BY u.name " + params["order"]
else:
select += " ORDER BY u.name DESC"
if "limit" in params:
select += " LIMIT " + str(params["limit"])
followers_ees = db_funcs.db_select(query=select, query_params=(type_email, ))
list_f = []
for follower_ee in followers_ees:
list_f.append(db_users_func.user_details(email=follower_ee[0]))
return list_f
def post_create(date, thread, message, user, forum, optional):
db_funcs.db_exist(entity="Threads", entity_attr="id", attr_value=thread)
db_funcs.db_exist(entity="Forums", entity_attr="short_name", attr_value=forum)
db_funcs.db_exist(entity="Users", entity_attr="email", attr_value=user)
thread_counter = db_funcs.db_select(
"SELECT COUNT(t.id)"
" FROM Threads t, Forums f"
" WHERE t.forum = f.short_name"
" AND t.forum = %s"
" AND t.id = %s",
(forum, thread, )
)
if not thread_counter[0][0]:
raise Exception("post: thread with id = " + thread + " in forum " + forum + " not found")
if "parent" in optional:
parent_counter = db_funcs.db_select(
"SELECT COUNT(p.id)"
" FROM Posts p, Threads t"
" WHERE t.id = p.thread"
" AND p.id = %s"
" AND t.id = %s",
(optional["parent"], thread, )
)
if not parent_counter[0][0]:
raise Exception("post: post with id = " + optional["parent"] + " not found")
insert = "INSERT INTO Posts (message, user, forum, thread, date"
values = "(%s, %s, %s, %s, %s"
params = [message, user, forum, thread, date]
for is_attr in optional:
insert += ", " + is_attr
values += ", %s"
params.append(optional[is_attr])
insert += ") VALUES " + values + ")"
update_threads_posts = "UPDATE Threads SET posts = posts + 1 WHERE id = %s"
conn = db_funcs.db_connect()
conn.autocommit(False)
with conn:
cursor = conn.cursor()
try:
cursor.execute(update_threads_posts, (thread, ))
cursor.execute(insert, params)
conn.commit()
except Exception as e:
conn.rollback()
raise Exception("Database error: " + e.message)
post_id = cursor.lastrowid
cursor.close()
conn.close()
post_response = post_select(post_id)
del post_response["dislikes"]
del post_response["likes"]
del post_response["parent"]
del post_response["points"]
return post_response
