def dec_posts_count(connect,post):
thread = DBconnect.select_query(connect,"SELECT thread FROM post WHERE id = %s", (post, ))
try:
DBconnect.update_query(connect,"UPDATE thread SET posts = posts - 1 WHERE id = %s", (thread[0][0], ))
except Exception as e:
print(e.message)
return
def followers_list(email, type, params):
DBconnect.exist(entity="user", identifier="email", value=email)
if type == "follower":
where = "followee"
if type == "followee":
where = "follower"
query = "SELECT " + type + " FROM follower JOIN user ON user.email = follower." + 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 = DBconnect.select_query(query=query, params=(email, ))
f_list = []
for id in followers_ids_tuple:
id = id[0]
f_list.append(users.details(email=id))
return f_list
def save_thread(connect, forum, title, isClosed, user, date, message, slug, optional):
isDeleted = 0
if "isDeleted" in optional:
isDeleted = optional["isDeleted"]
DBconnect.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, ))
thread = DBconnect.select_query(connect,
'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM thread 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 vote(vote_id, vote_type):
# DBconnect.exist(entity="post", identifier="id", value=vote_id)
if vote_type == -1:
DBconnect.update_query("UPDATE post SET dislikes=dislikes+1, points=points-1 where id = %s", (vote_id, ))
else:
DBconnect.update_query("UPDATE post SET likes=likes+1, points=points+1 where id = %s", (vote_id, ))
return details(details_id=vote_id, related=[])
def save_forum(connect,name, short_name, user):
DBconnect.update_query(connect,'INSERT INTO forum (name, short_name, user) VALUES (%s, %s, %s)',
(name, short_name, user, ))
forum = DBconnect.select_query(connect,
'select id, name, short_name, user FROM forum WHERE short_name = %s', (short_name, )
)
return forum_description(forum)
def open_close_thread(connect,id, isClosed):
DBconnect.update_query(connect,"UPDATE thread SET isClosed = %s WHERE id = %s", (isClosed, id, ))
response = {
"thread": id
}
return response
def open_close_thread(id, isClosed):
DBconnect.exist(entity="thread", identifier="id", value=id)
DBconnect.update_query("UPDATE thread SET isClosed = %s WHERE id = %s", (isClosed, id, ))
response = {
"thread": id
}
return response
def vote(connect,id, vote):
try:
if vote == -1:
DBconnect.update_query(connect,"UPDATE thread SET dislikes=dislikes+1, points=points-1 where id = %s", (id, ))
else:
DBconnect.update_query(connect,"UPDATE thread SET likes=likes+1, points=points+1 where id = %s", (id, ))
except Exception as e:
print(e.message)
return details(connect,id=id, related=[])
def remove_subscription(connect,email, thread_id):
try:
DBconnect.update_query(connect,'DELETE FROM subscription WHERE user = %s AND thread = %s', (email, thread_id, ))
except Exception as e:
raise Exception("user " + email + " does not subscribe thread #" + str(thread_id))
response = {
"thread": subscription[0][0],
"user": subscription[0][1]
}
return response
def remove_follow(email1, email2):
follows = DBconnect.select_query(
'SELECT id FROM follower WHERE follower = %s AND followee = %s', (email1, email2, )
)
if len(follows) != 0:
DBconnect.update_query('DELETE FROM follower WHERE follower = %s AND followee = %s', (email1, email2, ))
else:
raise Exception("No such following")
return users.details(email1)
def remove_restore(connect,thread_id, status):
if status == 1:
posts = 0
else:
posts = DBconnect.select_query(connect,"SELECT COUNT(id) FROM post WHERE thread = %s", str(thread_id))[0][0]
DBconnect.update_query(connect,"UPDATE thread SET isDeleted = %s, posts = %s WHERE id = %s", (status,posts,thread_id))
DBconnect.update_query(connect,"UPDATE post SET isDeleted = %s WHERE thread = %s", (status,thread_id))
response = {
"thread": thread_id
}
return response
def save_subscription(connect,email, thread_id):
DBconnect.update_query(connect,'INSERT INTO subscription (thread, user) VALUES (%s, %s)', (thread_id, email, ))
subscription = DBconnect.select_query(connect,
'select thread, user FROM subscription WHERE user = %s AND thread = %s', (email, thread_id, )
)
response = {
"thread": subscription[0][0],
"user": subscription[0][1]
}
return response
def vote(id, vote):
print("entered")
try:
# DBconnect.exist(entity="thread", identifier="id", value=id)
if vote == -1:
DBconnect.update_query("UPDATE thread SET dislikes=dislikes+1, points=points-1 where id = %s", (id, ))
else:
DBconnect.update_query("UPDATE thread SET likes=likes+1, points=points+1 where id = %s", (id, ))
except Exception as e:
print(e.message)
return details(id=id, related=[])
def save_user(connect,email, username, about, name, optional):
isAnonymous = 0
if "isAnonymous" in optional:
isAnonymous = optional["isAnonymous"]
str = DBconnect.update_query(connect,
'INSERT INTO user (email, about, name, username, isAnonymous) VALUES (%s, %s, %s, %s, %s)',
(email, about, name, username, isAnonymous, ))
if str == "Ituser":
raise Exception("5")
user = DBconnect.select_query(connect,'select email, about, isAnonymous, id, name, username FROM user WHERE email = %s',
(email, ))
return user_format(user)
def details(email):
user = DBconnect.select_query('select email, about, isAnonymous, id, name, username FROM user USE KEY (email) WHERE email = %s', (email, ))
user = user_format(user)
if user is None:
raise Exception("No user with email " + email)
f_list = DBconnect.select_query(
"SELECT follower FROM follower WHERE followee = %s ", (email, )
)
user["followers"] = tuple2list(f_list)
f_list = DBconnect.select_query(
"SELECT followee FROM follower WHERE follower = %s ", (email, )
)
user["following"] = tuple2list(f_list)
user["subscriptions"] = user_subscriptions(email)
return user
def list_users(short_name, optional):
# DBconnect.exist(entity="forum", identifier="short_name", value=short_name)
query = "SELECT user.id, user.email, user.name, user.username, user.isAnonymous, user.about FROM user USE KEY (name_email) " \
"WHERE user.email IN (SELECT DISTINCT user FROM post USE KEY (forum_user) WHERE forum = %s)"
if "since_id" in optional:
query += " AND user.id >= " + str(optional["since_id"])
if "order" in optional:
query += " ORDER BY user.name " + optional["order"]
# else:
# query += " ORDER BY user.name DESC"
if "limit" in optional:
query += " LIMIT " + str(optional["limit"])
users_tuple = DBconnect.select_query(query, (short_name, ))
list_u = []
for user_sql in users_tuple:
email = user_sql[1]
list_u.append({
'id': user_sql[0],
'email': email,
'name': user_sql[2],
'username': user_sql[3],
'isAnonymous': user_sql[4],
'about': user_sql[5],
'subscriptions': users.user_subscriptions(email),
'followers': users.followers(email, "follower"),
'following': users.followers(email, "followee")
})
return list_u
def details(connect,id, related):
thread = DBconnect.select_query(connect,
'select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts '
'FROM thread WHERE id = %s LIMIT 1;', (id, )
)
if len(thread) == 0:
raise Exception('No thread exists with id=' + str(id))
thread = thread[0]
thread = {
'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["user"] = users.details(connect,thread["user"])
if "forum" in related:
thread["forum"] = forums.details(connect=connect,short_name=thread["forum"], related=[])
return thread
def details(id, related):
thread = DBconnect.select_query(
'''select date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, IFNULL(posts.posts, 0) FROM thread LEFT JOIN (SELECT thread, COUNT(*) as posts
FROM post
WHERE post.isDeleted = FALSE
GROUP BY thread) posts ON posts.thread = thread.id
WHERE id = %s''', (id, )
)
if len(thread) == 0:
raise Exception('No thread exists with id=' + str(id))
thread = thread[0]
thread = {
'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["user"] = users.details(thread["user"])
if "forum" in related:
thread["forum"] = forums.details(short_name=thread["forum"], related=[])
return thread
def details_in(in_str):
query = "SELECT date, forum, id, isClosed, isDeleted, message, slug, title, user, dislikes, likes, points, posts FROM thread" \
" WHERE id IN (%s);"
print(query % (in_str, ))
threads = DBconnect.select_query(query, (in_str, ))
print(in_str)
print(threads)
thread_list = {}
for thread in threads:
thread = {
'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],
}
thread_list[int(thread['id'])] = thread
return thread_list
def list_users(short_name, optional):
query = "SELECT user.id, user.email, user.name, user.username, user.isAnonymous, user.about FROM user " \
"WHERE user.email IN (SELECT DISTINCT user FROM post WHERE forum = %s)"
if "since_id" in optional:
query += " AND user.id >= " + str(optional["since_id"])
if "order" in optional:
query += " ORDER BY user.name " + optional["order"]
if "limit" in optional:
query += " LIMIT " + str(optional["limit"])
connection = DBconnect.connect()
cursor = connection.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(query, (short_name, ))
users_tuple = [i for i in cursor.fetchall()]
for user_sql in users_tuple:
cursor.execute("""SELECT `thread` FROM `subscription` WHERE `user` = %s;""", (user_sql['email'], ))
sub = [i['thread'] for i in cursor.fetchall()]
followers = common.list_followers(cursor, user_sql['email'])
following = common.list_following(cursor, user_sql['email'])
user_sql.update({'following': following, 'followers': followers, 'subscriptions': sub})
cursor.close()
connection.close()
return users_tuple
def post_query(connect,id):
post = DBconnect.select_query(connect,'select date, dislikes, forum, id, isApproved, isDeleted, isEdited, '
'isHighlighted, isSpam, likes, message, parent, points, thread, user '
'FROM post WHERE id = %s LIMIT 1;', (id, ))
if len(post) == 0:
return None
return post_formated(post)
def create(date, thread, message, user, forum, optional):
DBconnect.exist(entity="thread", identifier="id", value=thread)
DBconnect.exist(entity="forum", identifier="short_name", value=forum)
DBconnect.exist(entity="user", identifier="email", value=user)
print (date, thread, message, user, forum, optional)
if len(DBconnect.select_query("SELECT thread.id as id FROM thread JOIN forum ON thread.forum = forum.short_name "
"WHERE thread.forum = %s AND thread.id = %s", (forum, thread, ))) == 0:
raise Exception("no thread with id = " + str(thread) + " in forum " + forum)
if (("parent" in optional) and (optional["parent"] != None)):
parent = DBconnect.select_query("SELECT post.id, post.path FROM post JOIN thread ON thread.id = post.thread "
"WHERE post.id = %s AND thread.id = %s", (optional["parent"], thread, ))
if len(parent) == 0:
raise Exception("No post with id = " + str(optional["parent"]))
path = parent[0][1]
else:
path = str(thread)
try:
query = "INSERT INTO post (message, user, forum, thread, date"
values = "(%s, %s, %s, %s, %s"
parameters = [message, user, forum, thread, date]
#optional_data = ["parent", "isApproved", "isHighlighted", "isEdited", "isSpam", "isDeleted"]
for param in optional:
query += ", " + param
values += ", %s"
parameters.append(optional[param])
except Exception as e:
print e.message
query += ") VALUES " + values + ")"
update_thread_posts = "UPDATE thread SET posts = posts + 1 WHERE id = %s"
update_path = "UPDATE post SET path = CONCAT_WS('.', '" + path + "', %s) WHERE id = %s"
con = DBConnection()
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)
post_id = cursor.lastrowid
cursor.execute(update_path, (post_id, post_id, ))
cursor.close()
con.close()
post = post_query(post_id)
del post["dislikes"]
del post["likes"]
del post["parent"]
del post["points"]
return post
def followers(email, type):
where = "followee"
if type == "followee":
where = "follower"
f_list = DBconnect.select_query(
"SELECT " + type + " FROM follower WHERE " + where + " = %s ", (email, )
)
return tuple2list(f_list)
def status():
statuses = DBconnect.select_query('''SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'forumdb'
AND table_name IN ('user', 'thread', 'forum', 'post')''', [])
result = {}
for status in statuses:
result[status[0]] = status[1]
return result
def save_user(email, username, about, name, optional):
isAnonymous = 0
if "isAnonymous" in optional:
isAnonymous = optional["isAnonymous"]
try:
user = DBconnect.select_query('SELECT email, about, isAnonymous, id, name, username FROM user WHERE email = %s', (email, ))
if len(user) == 0:
DBconnect.update_query(
'INSERT INTO user (email, about, name, username, isAnonymous) VALUES (%s, %s, %s, %s, %s)',
(email, about, name, username, isAnonymous, ))
else:
raise Exception("5")
user = DBconnect.select_query('select email, about, isAnonymous, id, name, username FROM user WHERE email = %s',
(email, ))
except Exception as e:
raise Exception(e.message)
return user_format(user)
def details(connect,short_name, related):
forum = DBconnect.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 exists short_name=" + short_name)
forum = forum_description(forum)
if "user" in related:
forum["user"] = users.details(connect,forum["user"])
return forum
def select_query(query, params):
try:
con = DBconnect.connect()
cursor = con.cursor()
cursor.execute(query, params)
result = cursor.fetchall()
cursor.close()
con.close()
except db.Error as e:
raise Exception(e.message)
return result
def clear():
tables = ['post', 'thread', 'forum', 'subscription', 'follower', 'user']
DBconnect.execute("SET global foreign_key_checks = 0;")
for table in tables:
DBconnect.execute("TRUNCATE TABLE %s;" % table)
DBconnect.execute("SET global foreign_key_checks = 1;")
return
def update_query(query, params):
try:
con = DBconnect.connect()
cursor = con.cursor()
cursor.execute(query, params)
con.commit()
inserted_id = cursor.lastrowid
cursor.close()
con.close()
except db.Error as e:
raise Exception(e.message)
return inserted_id
def remove_restore(thread_id, status):
DBconnect.exist(entity="thread", identifier="id", value=thread_id)
DBconnect.update_query("UPDATE thread SET isDeleted = %s WHERE id = %s", (status, thread_id, ))
DBconnect.update_query("UPDATE post SET isDeleted = %s WHERE thread = %s", (status, thread_id, ))
response = {
"thread": thread_id
}
return response