def details(email):
user = dbFunctions.select_query(
'SELECT email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, )
)
user = user[0]
user_response = {
'about': user[1],
'email': user[0],
'id': user[3],
'isAnonymous': bool(user[2]),
'name': user[4],
'username': user[5]
}
user_response["followers"] = followers(email, "follower")
user_response["following"] = followers(email, "followee")
subs_list = []
subscriptions = dbFunctions.select_query(
'SELECT thread FROM Subscriptions WHERE user = %s', (email, )
)
for i in subscriptions:
subs_list.append(i[0])
user_response["subscriptions"] = subs_list
return user_response
def save_user(email, username, about, name, optional):
isAnonymous = 0
if "isAnonymous" in optional:
isAnonymous = optional["isAnonymous"]
try:
user = dbFunctions.select_query(
'select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s',
(email, ))
if len(user) == 0:
dbFunctions.change_query(
'INSERT INTO Users (email, about, name, username, isAnonymous) VALUES (%s, %s, %s, %s, %s)',
(
email,
about,
name,
username,
isAnonymous,
))
user = dbFunctions.select_query(
'SELECT email, about, isAnonymous, id, name, username FROM Users WHERE email = %s',
(email, ))
except Exception as e:
raise Exception(e.message)
user = user[0]
user_response = {
'about': user[1],
'email': user[0],
'id': user[3],
'isAnonymous': bool(user[2]),
'name': user[4],
'username': user[5]
}
return user_response
def save_user(email, username, about, name, optional):
isAnonymous = 0
if "isAnonymous" in optional:
isAnonymous = optional["isAnonymous"]
try:
user = dbFunctions.select_query('select email, about, isAnonymous, id, name, username FROM Users WHERE email = %s',
(email, ))
if len(user) == 0:
dbFunctions.change_query(
'INSERT INTO Users (email, about, name, username, isAnonymous) VALUES (%s, %s, %s, %s, %s)',
(email, about, name, username, isAnonymous, ))
user = dbFunctions.select_query(
'SELECT email, about, isAnonymous, id, name, username FROM Users WHERE email = %s', (email, )
)
except Exception as e:
raise Exception(e.message)
user = user[0]
user_response = {
'about': user[1],
'email': user[0],
'id': user[3],
'isAnonymous': bool(user[2]),
'name': user[4],
'username': user[5]
}
return user_response
def new_thread(forum, title, isClosed, user, date, message, slug, optional):
isDeleted = 0
if "isDeleted" in optional:
isDeleted = optional["isDeleted"]
thread = dbFunctions.select_query(
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM Threads WHERE slug = %s', (slug, )
)
if len(thread) == 0:
dbFunctions.change_query('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 = dbFunctions.select_query(
'select date, forum, id, isClosed, isDeleted, message, slug, title, user '
'FROM Threads 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],
}
return response
def details(id, related):
thread = dbFunctions.select_query(
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM Threads WHERE id = %s', (id, )
)
thread = thread[0]
thread_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],
}
if "user" in related:
thread_response["user"] = userFunctions.details(thread["user"])
if "forum" in related:
thread_response["forum"] = forumFunctions.details(short_name=thread["forum"], related=[])
return thread_response
def add_subscribe(email, thread_id):
subscription = dbFunctions.select_query(
'SELECT thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, )
)
if len(subscription) == 0:
dbFunctions.change_query(
'INSERT INTO Subscriptions (thread, user) VALUES (%s, %s)', (thread_id, email, )
)
subscription = dbFunctions.select_query(
'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 details(details_id, related):
post = dbFunctions.select_query(
'SELECT date, dislikes, forum, id, isApproved, isDeleted, isEdited, '
'isHighlighted, isSpam, likes, message, parent, points, thread, user '
'FROM Posts WHERE id = %s', (details_id, )
)
post = post[0]
post_response = {
'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:
post_response["user"] = userFunctions.details(post["user"])
if "forum" in related:
post_response["forum"] = forumFunctions.details(short_name=post["forum"], related=[])
if "thread" in related:
post_response["thread"] = threadFunctions.details(id=post["thread"], related=[])
return post
def details(id, related):
thread = dbFunctions.select_query(
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM Threads WHERE id = %s', (id, ))
thread = thread[0]
thread_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],
}
if "user" in related:
thread_response["user"] = userFunctions.details(thread["user"])
if "forum" in related:
thread_response["forum"] = forumFunctions.details(
short_name=thread["forum"], related=[])
return thread_response
def new_post(date, thread, message, user, forum, optional):
if len(dbFunctions.select_query("SELECT Threads.id FROM Threads JOIN Forums ON Threads.forum = Forums.short_name "
"WHERE Threads.forum = %s AND Threads.id = %s", (forum, thread, ))) == 0:
raise Exception("Нет thread с айдишником = " + str(thread) + " в форуме " + forum)
if "parent" in optional:
if len(dbFunctions.select_query("SELECT Posts.id FROM Posts JOIN Threads ON Threads.id = Posts.thread "
"WHERE Posts.id = %s AND Threads.id = %s", (optional["parent"], thread, ))) == 0:
raise Exception("Нет поста с айдишником = " + optional["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 optional:
query += ", " + param
values += ", %s"
parameters.append(optional[param])
query += ") VALUES " + values + ")"
update_posts = "UPDATE Threads SET posts = posts + 1 WHERE id = %s"
dbFunctions.change_query(update_posts, (thread, ))
post_id = dbFunctions.change_query(query, parameters)
post = dbFunctions.select_query('SELECT date, forum, id, isApproved, isDeleted, isEdited, '
'isHighlighted, isSpam, message, thread, user '
'FROM Posts WHERE id = %s', (post_id, ))
post = post[0]
post_response = {
'date': str(post[0]),
'forum': post[1],
'id': post[2],
'isApproved': bool(post[3]),
'isDeleted': bool(post[4]),
'isEdited': bool(post[5]),
'isHighlighted': bool(post[6]),
'isSpam': bool(post[7]),
'message': post[8],
'thread': post[9],
'user': post[10],
}
return post_response
def add_follow(email1, email2):
follower = dbFunctions.select_query(
'SELECT id FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, )
)
if len(follower) == 0:
dbFunctions.change_query('INSERT INTO Followers (follower, followee) VALUES (%s, %s)', (email1, email2, ))
user = details(email1)
return user
def delete_subscription(email, thread_id):
subscription = dbFunctions.select_query(
'SELECT thread, user FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, )
)
dbFunctions.change_query(
'DELETE FROM Subscriptions WHERE user = %s AND thread = %s', (email, thread_id, )
)
response = {
"thread": subscription[0][0],
"user": subscription[0][1]
}
return response
def add_subscribe(email, thread_id):
subscription = dbFunctions.select_query(
'SELECT thread, user FROM Subscriptions WHERE user = %s AND thread = %s',
(
email,
thread_id,
))
if len(subscription) == 0:
dbFunctions.change_query(
'INSERT INTO Subscriptions (thread, user) VALUES (%s, %s)', (
thread_id,
email,
))
subscription = dbFunctions.select_query(
'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 followers(email, type):
if type == "follower":
where = "followee"
if type == "followee":
where = "follower"
f_list = dbFunctions.select_query(
"SELECT " + type + " FROM Followers JOIN Users ON Users.email = Followers." + type +
" WHERE " + where + " = %s ", (email, )
)
l = []
for el in f_list:
l.append(el[0])
return l
def remove_follow(email1, email2):
follows = dbFunctions.select_query(
'SELECT id FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, )
)
if len(follows) != 0:
dbFunctions.change_query(
'DELETE FROM Followers WHERE follower = %s AND followee = %s', (email1, email2, )
)
else:
raise Exception("Упс!")
return details(email1)
def followers(email, type):
if type == "follower":
where = "followee"
if type == "followee":
where = "follower"
f_list = dbFunctions.select_query(
"SELECT " + type +
" FROM Followers JOIN Users ON Users.email = Followers." + type +
" WHERE " + where + " = %s ", (email, ))
l = []
for el in f_list:
l.append(el[0])
return l
def new_forum(name, short_name, user):
"""
Создание нового форума
"""
dbFunctions.exist(entity="Users", identifier="email", value=user)
forum = dbFunctions.select_query(
'SELECT id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, )
)
if len(forum) == 0:
dbFunctions.change_query('INSERT INTO Forums (name, short_name, user) VALUES (%s, %s, %s)',
(name, short_name, user, ))
forum = dbFunctions.select_query(
'SELECT id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, )
)
forum = forum[0]
response = {
'id': forum[0],
'name': forum[1],
'short_name': forum[2],
'user': forum[3]
}
return response
def delete_subscription(email, thread_id):
subscription = dbFunctions.select_query(
'SELECT thread, user FROM Subscriptions WHERE user = %s AND thread = %s',
(
email,
thread_id,
))
dbFunctions.change_query(
'DELETE FROM Subscriptions WHERE user = %s AND thread = %s', (
email,
thread_id,
))
response = {"thread": subscription[0][0], "user": subscription[0][1]}
return response
def new_thread(forum, title, isClosed, user, date, message, slug, optional):
isDeleted = 0
if "isDeleted" in optional:
isDeleted = optional["isDeleted"]
thread = dbFunctions.select_query(
'SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM Threads WHERE slug = %s', (slug, ))
if len(thread) == 0:
dbFunctions.change_query(
'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 = dbFunctions.select_query(
'select date, forum, id, isClosed, isDeleted, message, slug, title, user '
'FROM Threads 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],
}
return response
def details(email):
user = dbFunctions.select_query(
'SELECT email, about, isAnonymous, id, name, username FROM Users WHERE email = %s',
(email, ))
user = user[0]
user_response = {
'about': user[1],
'email': user[0],
'id': user[3],
'isAnonymous': bool(user[2]),
'name': user[4],
'username': user[5]
}
user_response["followers"] = followers(email, "follower")
user_response["following"] = followers(email, "followee")
subs_list = []
subscriptions = dbFunctions.select_query(
'SELECT thread FROM Subscriptions WHERE user = %s', (email, ))
for i in subscriptions:
subs_list.append(i[0])
user_response["subscriptions"] = subs_list
return user_response
def add_follow(email1, email2):
follower = dbFunctions.select_query(
'SELECT id FROM Followers WHERE follower = %s AND followee = %s', (
email1,
email2,
))
if len(follower) == 0:
dbFunctions.change_query(
'INSERT INTO Followers (follower, followee) VALUES (%s, %s)', (
email1,
email2,
))
user = details(email1)
return user
def remove_follow(email1, email2):
follows = dbFunctions.select_query(
'SELECT id FROM Followers WHERE follower = %s AND followee = %s', (
email1,
email2,
))
if len(follows) != 0:
dbFunctions.change_query(
'DELETE FROM Followers WHERE follower = %s AND followee = %s', (
email1,
email2,
))
else:
raise Exception("Упс!")
return details(email1)
def posts_list(entity, params, identifier, related=[]):
query = "SELECT id FROM Posts 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 " + params["order"]
else:
query += " ORDER BY date DESC "
if "limit" in params:
query += " LIMIT " + str(params["limit"])
post_ids = dbFunctions.select_query(query=query, params=parameters)
post_list = []
for id in post_ids:
id = id[0]
post_list.append(details(details_id=id, related=related))
return post_list
def list_users(short_name, optional):
"""
"""
query = "SELECT distinct email FROM Users JOIN Posts ON Posts.user = Users.email " \
" JOIN Forums on Forums.short_name = Posts.forum WHERE Posts.forum = %s "
if "since_id" in optional:
query += " AND Users.id >= " + str(optional["since_id"])
if "order" in optional:
query += " ORDER BY Users.id " + optional["order"]
if "limit" in optional:
query += " LIMIT " + str(optional["limit"])
users = dbFunctions.select_query(query, (short_name, ))
list_u = []
for user in users:
user = user[0]
list_u.append(users.details(user))
return list_u
def forum_details(short_name, related):
"""
Подробности о форуме
"""
forum = dbFunctions.select_query(
'SELECT id, name, short_name, user FROM Forums WHERE short_name = %s', (short_name, )
)
if len(forum) == 0:
raise ("Нет форума с именем=" + short_name)
forum = forum[0]
response = {
'id': forum[0],
'name': forum[1],
'short_name': forum[2],
'user': forum[3]
}
if "user" in related:
response["user"] = userFunctions.details(forum["user"])
return forum
def threads_list(entity, identifier, related, params):
query = "SELECT id FROM Threads 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 " + params["order"]
else:
query += " ORDER BY date DESC "
if "limit" in params:
query += " LIMIT " + str(params["limit"])
thread_ids = dbFunctions.select_query(query=query, params=parameters)
thread_l = []
for id in thread_ids:
id = id[0]
thread_l.append(details(id=id, related=related))
return thread_l
def threads_list(entity, identifier, related, params):
query = "SELECT id FROM Threads 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 " + params["order"]
else:
query += " ORDER BY date DESC "
if "limit" in params:
query += " LIMIT " + str(params["limit"])
thread_ids = dbFunctions.select_query(query=query, params=parameters)
thread_l = []
for id in thread_ids:
id = id[0]
thread_l.append(details(id=id, related=related))
return thread_l
def followers_list(email, type, params):
if type == "follower":
where = "followee"
if type == "followee":
where = "follower"
query = "SELECT " + type + " FROM Followers JOIN Users ON Users.email = Followers." + type + \
" WHERE " + 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_id = dbFunctions.select_query(query=query, params=(email, ))
list_f = []
for id in followers_id:
id = id[0]
list_f.append(details(email=id))
return list_f
def followers_list(email, type, params):
if type == "follower":
where = "followee"
if type == "followee":
where = "follower"
query = "SELECT " + type + " FROM Followers JOIN Users ON Users.email = Followers." + type + \
" WHERE " + 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_id = dbFunctions.select_query(query=query, params=(email, ))
list_f = []
for id in followers_id:
id = id[0]
list_f.append(details(email=id))
return list_f
