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
def user_select(email):
return db_funcs.db_select(
'SELECT email, about, isAnonymous, id, name, username'
' FROM Users'
' WHERE email = %s',
(email, )
)
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_select(slug):
return db_funcs.db_select(
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts'
' FROM Threads'
' WHERE slug = %s',
(slug, )
)
def forum_get(short_name):
return db_funcs.db_select(
'SELECT id, name, short_name, user '
'FROM Forums '
'WHERE short_name = %s',
(short_name, )
)
def follower_select(follower_email, followee_email):
return db_funcs.db_select(
'SELECT id'
' FROM Followers'
' WHERE follower = %s'
' AND followee = %s',
(follower_email, followee_email, )
)
def subscription_select(sub_email, thread_id):
return db_funcs.db_select(
'SELECT thread, user'
' FROM Subscriptions'
' WHERE user = %s'
' AND thread = %s',
(sub_email, thread_id, )
)
def post_select(post_id):
post = db_funcs.db_select(
'SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, '
'isHighlighted, isSpam, likes, message, parent, points, thread, user '
'FROM Posts '
'WHERE id = %s', (post_id, )
)
if not len(post):
return None
return post_describe(post[0])
def thread_details(thread_id, related):
thread = db_funcs.db_select(
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts'
' FROM Threads'
' WHERE id = %s',
(thread_id, )
)
if not len(thread):
raise Exception('thread: thread with id=' + str(thread_id) + " not found")
thread_response = thread_describe(thread[0])
if "user" in related:
thread_response["user"] = db_users_func.user_details(thread_response["user"])
if "forum" in related:
thread_response["forum"] = db_forums_funcs.forum_details(short_name=thread_response["forum"], related=[])
return thread_response
def user_follower(email, type):
if type == "follower":
where_condition = "followee"
else:
# if type == "followee":
where_condition = "follower"
followers_ees = db_funcs.db_select(
"SELECT " + type +
" FROM Followers f, Users u"
" WHERE u.email = f." + type +
" AND " + where_condition + " = %s ",
(email, )
)
list_f = []
for follower_ee in followers_ees:
list_f.append(follower_ee[0])
return list_f
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 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
