def posts_parent_tree_sort_since(thread, params):
connect = connectDB()
cursor = connect.cursor()
try:
command = '''SELECT posts.post_id, posts.user_id, posts.thread_id, posts.forum_id,
posts.created, posts.isedited, posts.message, posts.parent_id, posts.path
FROM posts WHERE path[1] IN (SELECT posts.post_id FROM posts
WHERE thread_id = %s AND parent_id = 0 ORDER BY post_id %s)
ORDER BY path %s;''' % (thread.id, params['order'], params['order'])
cursor.execute(command)
posts = cursor.fetchall()
if posts is None:
raise Exception("post is not exist")
posts = posts_since_limit_parent(posts, params['since'], params['limit'])
post_arr = []
for post in posts:
post_arr.append(post_model.from_tuple(post))
return post_arr
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
except Exception as e:
print("IntegrityError")
raise
finally:
cursor.close()
def update_thread(thread, new_thread):
connect = connectDB()
cursor = connect.cursor()
message = new_thread.message
if message is None:
message = thread.message
title = new_thread.title
if title is None:
title = thread.title
try:
command = '''UPDATE threads SET message = '%s', title = '%s'
WHERE thread_id = %s RETURNING *;''' % (str(message), str(title), thread.id)
cursor.execute(command)
thread = cursor.fetchone()
return thread_model.from_tuple(thread)
except psycopg2.IntegrityError as e:
print("This user is already exist")
raise
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def delete_users():
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(DELETE_USERS_TABLE)
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def count_users():
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(SELECT_COUNT_USERS)
count_users = cursor.fetchone()[0]
return count_users
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def count_votes_by_thread_id(thread_id):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(COUNT_VOTES_BY_THREAD_ID, [thread_id, ])
count_votes = cursor.fetchone()[0]
return count_votes
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def count_threads_by_forum_id(forum):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(SELECT_COUNT_THREADS_BY_FORUM_ID, [forum.id, ])
count_threads = cursor.fetchone()[0]
return count_threads
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def create_forum(forum, user):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(INSERT_FORUM, [user.id, forum.slug, forum.title, ])
returning_forum = cursor.fetchone()
return forum_model.from_tuple(returning_forum)
except psycopg2.IntegrityError as e:
print("This user is already exist" + e.diag.message_primary)
raise
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def create_thread(thread, forum, user):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(INSERT_THREAD,
[forum.id, user.id, thread.created, thread.message, thread.slug, thread.title, ])
returning_thread = cursor.fetchone()
return thread_model.from_tuple(returning_thread)
except psycopg2.IntegrityError as e:
print("This user is already exist" + e.diag.message_primary)
raise
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def select_post_by_id(post_id):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(SELECT_POST_BY_ID, [post_id, ])
post = cursor.fetchone()
if post is None:
raise Exception("post is not exist")
return post_model.from_tuple(post)
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
except Exception as e:
print("IntegrityError")
raise
finally:
cursor.close()
def select_forum_by_id(forum_id):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(SELECT_FORUM_BY_ID, [forum_id, ])
forum = cursor.fetchone()
if forum is None:
raise Exception("forum is not exist")
return forum_model.from_tuple(forum)
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
except Exception as e:
print("IntegrityError")
raise
finally:
cursor.close()
def get_thread_by_id(thread_id):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(SELECT_THREAD_BY_ID, [thread_id, ])
thread = cursor.fetchone()
if thread is None:
raise Exception("threads is not exist")
return thread_model.from_tuple(thread)
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
except Exception as e:
print("IntegrityError")
raise
finally:
cursor.close()
def count_votes_by_thread_id(thread_id):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(COUNT_VOTES_BY_THREAD_ID, [
thread_id,
])
count_votes = cursor.fetchone()[0]
if count_votes is None:
raise Exception("vote is not exist")
return count_votes
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
except Exception as e:
print("IntegrityError" + +e.diag.message_primary)
finally:
cursor.close()
def update_post(post, message):
connect = connectDB()
cursor = connect.cursor()
isEdited = True
try:
command = '''UPDATE posts SET message = '%s', isedited = %s
WHERE post_id = %s RETURNING *;''' % (message, isEdited, post.id)
cursor.execute(command)
post = cursor.fetchone()
return post_model.from_tuple(post)
except psycopg2.IntegrityError as e:
print("This user is already exist")
raise
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def select_vote(thread_id, user_id):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(SELECT_VOTE_BY_THREAD_AND_USER_ID,
[thread_id, user_id])
vote = cursor.fetchone()
if vote is None:
raise Exception("vote is not exist")
return vote_model.from_tuple(vote)
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
except Exception as e:
print("IntegrityError")
raise
finally:
cursor.close()
def select_user_by_user_id(user_id):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(SELECT_USER_BY_USER_ID, [
user_id,
])
user = cursor.fetchone()
if user is None:
raise Exception("user is not exist")
return user_model.from_tuple(user)
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
except Exception as e:
print("IntegrityError")
raise
finally:
cursor.close()
def select_user_by_nickname_or_email(user):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(SELECT_USERS_BY_NICKNAME_OR_EMAIL, [
user.nickname,
user.email,
])
param_name_array = ["nickname", "about", "email", "fullname"]
users = []
for user in cursor.fetchall():
users.append(dict(zip(param_name_array, user[1:])))
return users
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def create_post(post):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(SELECT_NEXT_VAL)
post_id = cursor.fetchone()[0]
post_path = post.path
post_path = post_path.append(post_id)
cursor.execute(INSERT_POST, [post_id, post.user_id, post.thread_id, post.forum_id, post.parent_id, post.created, post.message, post.path ])
returning_post = cursor.fetchone()
return post_model.from_tuple(returning_post)
except psycopg2.IntegrityError as e:
print("This user is already exist" + e.diag.message_primary)
raise
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def update_vote(voice, thread_id, user_id):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(UPDATE_VOTE, [
voice,
user_id,
thread_id,
])
returning_vote = cursor.fetchone()
return vote_model.from_tuple(returning_vote)
except psycopg2.IntegrityError as e:
print("This vote is already exist")
raise
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def update_user_by_nickname(user):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(UPDATE_USER_BY_NICKNAME, [
user.about,
user.email,
user.fullname,
user.nickname,
])
user = cursor.fetchone()
return user_model.from_tuple(user)
except psycopg2.IntegrityError as e:
print("This user is already exist")
raise
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def insert_user(user):
connect = connectDB()
cursor = connect.cursor()
try:
cursor.execute(INSERT_USER, [
user.nickname,
user.about,
user.email,
user.fullname,
])
returning_user = cursor.fetchone()
return user_model.from_tuple(returning_user)
except psycopg2.IntegrityError as e:
print("This user is already exist")
raise
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
finally:
cursor.close()
def select_threads_by_forum_id(forum, params):
connect = connectDB()
cursor = connect.cursor()
try:
command = SELECT_THREADS_BY_FORUM_ID % (forum.id, params['since'], params['order'], params['limit'])
cursor.execute(command)
threads = cursor.fetchall()
if threads is None:
raise Exception("threads is not exist")
thread_arr = []
for thread in threads:
thread_arr.append(thread_model.from_tuple(thread))
return thread_arr
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
except Exception as e:
print("IntegrityError")
raise
finally:
cursor.close()
def select_users_arr(forum_id, params):
connect = connectDB()
cursor = connect.cursor()
if params['since'] is not None:
if params['desc']:
try:
command = SELECT_USERS_SINCE_DESC % (
forum_id, forum_id, params['since'], params['limit'])
cursor.execute(command)
users = cursor.fetchall()
if users is None:
raise Exception("user is not exist")
users_arr = []
for user in users:
user = user_model.from_tuple(user)
users_arr.append(user)
return users_arr
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
except Exception as e:
print("IntegrityError")
raise
finally:
cursor.close()
else:
try:
command = SELECT_USERS_SINCE % (
forum_id, forum_id, params['since'], params['limit'])
cursor.execute(command)
users = cursor.fetchall()
if users is None:
raise Exception("user is not exist")
users_arr = []
for user in users:
user = user_model.from_tuple(user)
users_arr.append(user)
return users_arr
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
except Exception as e:
print("IntegrityError")
raise
finally:
cursor.close()
else:
if params['desc']:
try:
command = SELECT_USERS_DESC % (forum_id, forum_id,
params['limit'])
cursor.execute(command)
users = cursor.fetchall()
if users is None:
raise Exception("user is not exist")
users_arr = []
for user in users:
user = user_model.from_tuple(user)
users_arr.append(user)
return users_arr
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
except Exception as e:
print("IntegrityError")
raise
finally:
cursor.close()
else:
try:
command = SELECT_USERS % (forum_id, forum_id,
params['limit'])
cursor.execute(command)
users = cursor.fetchall()
if users is None:
raise Exception("user is not exist")
users_arr = []
for user in users:
user = user_model.from_tuple(user)
users_arr.append(user)
return users_arr
except psycopg2.Error as e:
print("PostgreSQL Error: " + e.diag.message_primary)
except Exception as e:
print("IntegrityError")
raise
finally:
cursor.close()
