def check_user(self, data):
cmd = '''SELECT CASE WHEN
(SELECT nickname FROM users WHERE LOWER(nickname) = LOWER('{author}') LIMIT 1)
IS NULL THEN TRUE ELSE FALSE END AS "user_not_found";
'''.format(**data)
db_service.execute(cmd)
return db_service.get_one()
def check_not_found(self, data):
cmd = '''SELECT CASE WHEN
(SELECT id FROM posts p WHERE p.id = {id} LIMIT 1)
IS NULL THEN TRUE ELSE FALSE END AS "not_found";
'''.format(**data)
db_service.execute(cmd)
return db_service.get_one()
def get_posts_parent_tree(self, data):
root_posts = self.get_root_posts(data)
result = []
data.update({'parent': 0})
for parent in root_posts:
data['parent'] = parent['id']
cmd = """SELECT * FROM posts p
WHERE p.thread = {thread} and p.path[1] = {parent}
""".format(**data)
if data['since']:
cmd += ' AND p.path[1]'
cmd += ' < ' if data['desc'] else ' > '
cmd += '(SELECT path[1] FROM posts WHERE id = ' + data[
'since'].__str__() + ')'
# order = 'DESC' if data['desc'] else 'ASC'
cmd += ' ORDER BY p.path, p.id'
db_service.execute(cmd)
current_result = db_service.get_all()
for post in current_result:
post['created'] = time_to_str(post['created'])
result.extend(current_result)
return result
def check_by_id(self, id):
cmd = """SELECT CASE WHEN
(SELECT id FROM threads t WHERE t.id = '{id}' LIMIT 1)
IS NOT NULL THEN TRUE ELSE FALSE END AS "conflict";
""".format(id=id)
db_service.execute(cmd)
return db_service.get_one()
def check_by_slug(self, slug):
cmd = """SELECT CASE WHEN
(SELECT slug FROM threads t WHERE t.slug = '{slug}' LIMIT 1)
IS NOT NULL THEN TRUE ELSE FALSE END AS "conflict";
""".format(slug=slug)
db_service.execute(cmd)
return db_service.get_one()
def check_errors_vote(self, data):
cmd = """SELECT CASE WHEN
(SELECT nickname FROM users u WHERE LOWER(u.nickname) = LOWER('{nickname}') LIMIT 1)
IS NOT NULL THEN FALSE ELSE TRUE END AS "user_not_found";
""".format(**data)
db_service.execute(cmd)
return db_service.get_one()
def check_parent(self, data):
cmd = '''SELECT CASE WHEN
(SELECT id FROM posts p WHERE p.id = {parent} AND p.thread = {thread} LIMIT 1)
IS NULL THEN TRUE ELSE FALSE END AS "parent_conflict";
'''.format(**data)
db_service.execute(cmd)
return db_service.get_one()
def get_thread_by_slug(self, slug):
cmd = """SELECT * FROM threads WHERE slug = '{slug}'
""".format(slug=slug)
db_service.execute(cmd)
result = db_service.get_one()
if 'created' in result.keys() and result['created'] is not None:
result['created'] = time_to_str(result['created'])
return result
def get_thread_by_id(self, id):
cmd = """SELECT * FROM threads WHERE id = {id}
""".format(id=id)
db_service.execute(cmd)
result = db_service.get_one()
if 'created' in result.keys() and result['created'] is not None:
result['created'] = time_to_str(result['created'])
return result
def update_vote(self, data):
cmd = '''UPDATE votes SET voice = {voice} WHERE LOWER(username) = LOWER('{nickname}');
UPDATE threads SET votes = votes {number} WHERE id = {thread};
'''.format(**data,
number='+ ' + data['vote'].__str__() if
data['vote'] > 0 else ' ' + data['vote'].__str__())
db_service.execute(cmd)
return
def update(self, data):
cmd = """UPDATE users SET {about}{email}{fullname} WHERE LOWER(nickname) = LOWER('{nickname}');"""\
.format(about="about='" + data['about'] + "'," if 'about' in data.keys() else '',
email=" email='" + data['email'] + "'" if 'email' in data.keys() else '',
fullname=", fullname='" + data['fullname'] + "'" if 'fullname' in data.keys() else '',
nickname=data['nickname'])
db_service.execute(cmd)
return self.get_user(data['nickname'])
def get_forum_by_slug(self, slug):
cmd = '''SELECT * FROM forums f WHERE f.slug = '{slug}';
'''.format(slug=slug)
db_service.execute(cmd)
db_res = db_service.get_one()
db_res['user'] = db_res['author']
db_res.pop('author', None)
return db_res
def get_forum_by_id(self, id):
cmd = '''SELECT * FROM forums f WHERE f.id = '{id}';
'''.format(id=id)
db_service.execute(cmd)
db_res = db_service.get_one()
db_res['user'] = db_res['author']
db_res.pop('author', None)
return db_res
def vote(self, data):
cmd = '''INSERT INTO votes (username, voice, thread) VALUES ('{nickname}', {voice}, {thread});
UPDATE threads SET votes = votes {number} WHERE id = {thread};
'''.format(**data,
number='+ ' +
data['vote'].__str__() if data['vote'] > 0 else ' ' +
data['vote'].__str__())
db_service.execute(cmd)
return
def get_post_by_id(self, id):
cmd = """SELECT * FROM posts WHERE id = {id}""".format(id=id)
db_service.execute(cmd)
result = db_service.get_one()
# if 'created' in result.keys() and result['created'] is not None:
# result['created'] = time_to_str(result['created'])
result['isEdited'] = result['isedited']
result.pop('isedited')
return result
def check_to_vote(self, data):
cmd = """SELECT CASE WHEN
(SELECT username FROM votes v WHERE v.username = '******' AND v.thread = {thread} LIMIT 1)
IS NOT NULL THEN TRUE ELSE FALSE END AS "found",
CASE WHEN
(SELECT username FROM votes v WHERE v.username = '******' AND v.thread = {thread} AND voice = {voice} LIMIT 1)
IS NOT NULL THEN TRUE ELSE FALSE END AS "conflict";
""".format(**data)
db_service.execute(cmd)
return db_service.get_one()
def check_errors(self, data):
cmd = """SELECT CASE WHEN
(SELECT slug FROM forums f WHERE f.slug = '{slug}' LIMIT 1)
IS NOT NULL THEN TRUE ELSE FALSE END AS "conflict",
CASE WHEN
(SELECT nickname FROM users u WHERE LOWER(u.nickname) = LOWER('{user}') LIMIT 1)
IS NOT NULL THEN FALSE ELSE TRUE END AS "not_found";
""".format(**data)
db_service.execute(cmd)
result = db_service.get_one()
return result
def create(self, data):
author = user_service.get_user(data['user'])['nickname']
db_service.reconnect()
cmd = """INSERT INTO forums (slug, title, author, posts, threads)
VALUES ('{slug}', '{title}', '{username}', 0, 0) RETURNING *;
""".format(**data, username=author)
db_service.execute(cmd)
result = db_service.get_one()
result['user'] = result['author']
result.pop('author', None)
return result
def update(self, data):
cmd = """UPDATE threads SET {message}{dot}{title} WHERE {slug_or_id} = '{slug_or_id_data}';
""".format(
message="message='" + data['message'] +
"'" if 'message' in data.keys() else '',
dot=', '
if 'message' in data.keys() and 'title' in data.keys() else '',
title=" title='" + data['title'] +
"'" if 'title' in data.keys() else '',
slug_or_id='slug' if data['slug'] else 'id',
slug_or_id_data=data['slug'] if data['slug'] else data['id'])
db_service.execute(cmd)
if data['id'] is not False:
return self.get_thread_by_id(data['id'])
return self.get_thread_by_slug(data['slug'])
def get_root_posts(self, data):
cmd = """SELECT id FROM posts p
WHERE p.thread = {thread} AND p.parent = 0
""".format(**data)
if data['since']:
cmd += ' AND p.path[1]'
cmd += ' < ' if data['desc'] else ' > '
cmd += '(SELECT path[1] FROM posts WHERE id = ' + data[
'since'].__str__() + ')'
order = 'DESC' if data['desc'] else 'ASC'
cmd += ' ORDER BY p.id ' + order + ' LIMIT ' + data['limit']
db_service.execute(cmd)
result = db_service.get_all()
return result
def get_forum_users(self, data):
cmd = """SELECT u.* FROM users u
JOIN forum_users fu ON LOWER(u.nickname) = LOWER(fu.user_nickname)
WHERE LOWER(fu.forum) = LOWER('{slug}')
""".format(**data)
if data['since']:
cmd += 'AND LOWER(u.nickname)'
cmd += '<' if data['desc'] else '>'
cmd += "LOWER('" + data['since'] + "')"
order = 'DESC' if data['desc'] else 'ASC'
cmd += ' ORDER BY LOWER(u.nickname) ' + order
cmd += ' LIMIT ' + data['limit'] if data['limit'] else ''
db_service.execute(cmd)
result = db_service.get_all()
return result
def get_posts_tree(self, data):
cmd = """SELECT * FROM posts p
WHERE p.thread = {thread}
""".format(**data)
if data['since']:
cmd += ' AND p.path'
cmd += ' < ' if data['desc'] else ' > '
cmd += '(SELECT path FROM posts WHERE id = ' + data[
'since'].__str__() + ')'
order = 'DESC' if data['desc'] else 'ASC'
cmd += ' ORDER BY p.path ' + order + ' LIMIT ' + data['limit']
db_service.execute(cmd)
result = db_service.get_all()
for post in result:
post['created'] = time_to_str(post['created'])
return result
def get_forum_threads(self, data):
cmd = """SELECT * FROM threads t
WHERE LOWER(t.forum) = LOWER('{slug}')
""".format(**data)
if data['since']:
cmd += ' AND t.created'
cmd += ' <= ' if data['desc'] else ' >= '
cmd += "'" + data['since'] + "'"
order = 'DESC' if data['desc'] else 'ASC'
cmd += ' ORDER BY t.created ' + order
cmd += ' LIMIT ' + data['limit'] if data['limit'] else ''
db_service.execute(cmd)
result = db_service.get_all()
for thread in result:
thread['created'] = time_to_str(thread['created'])
return result
def create(self, data):
cmd = """INSERT INTO threads (author, message, title, forum{is_created}{is_slug})
VALUES ('{author}', '{message}', '{title}', '{forum}'{created_data}{slug_data}) RETURNING id;
""".format(**data,
is_created=', created'
if 'created' in data.keys() else '',
created_data=", '" + data['created'] +
"'" if 'created' in data.keys() else '',
is_slug=', slug' if 'slug' in data.keys() else '',
slug_data=", '" + data['slug'] +
"'" if 'slug' in data.keys() else '')
db_service.execute(cmd)
result = self.get_thread_by_id(db_service.get_one()['id'])
db_service.execute(
"""INSERT INTO forum_users (user_nickname, forum) SELECT '{author}', '{forum}'
WHERE NOT EXISTS (SELECT forum FROM forum_users WHERE LOWER(user_nickname) = LOWER('{author}') AND forum = '{forum}');
UPDATE forums SET threads = threads + 1 WHERE LOWER(slug) = LOWER('{forum}');"""
.format(**data))
return result
def create(self, post):
cmd = """INSERT INTO posts (id, author, message, forum, thread, created, parent, path)
VALUES ({id}, '{author}', '{message}', '{forum}', '{thread}',
'{created}', '{parent}', array_append(ARRAY[{path_data}]::integer[], {id})) RETURNING *;
""".format(**post,
path_data=', '.join('{0}'.format(n)
for n in post['path']))
db_service.execute_only(cmd)
result = db_service.get_one()
# if 'created' in result.keys() and result['created'] is not None:
# result['created'] = time_to_str(result['created'])
db_service.execute(
"""INSERT INTO forum_users (user_nickname, forum) SELECT '{author}', '{forum}'
WHERE NOT EXISTS (SELECT forum FROM forum_users WHERE LOWER(user_nickname) = LOWER('{author}') AND forum = '{forum}');
UPDATE forums SET posts = posts + 1 WHERE LOWER(slug) = LOWER('{forum}');"""
.format(**post))
return result
def check_errors(self, data):
result = {}
if 'email' in data.keys():
check_cmd = """SELECT CASE WHEN
( SELECT nickname FROM users WHERE LOWER(nickname) <> LOWER('{nickname}') AND LOWER(email) = LOWER('{email}'))
IS NOT NULL THEN TRUE ELSE FALSE END AS "conflict",
CASE WHEN
(SELECT nickname FROM users WHERE LOWER(nickname) = LOWER('{nickname}'))
IS NOT NULL THEN FALSE ELSE TRUE END AS "not_found";
""".format(**data)
else:
check_cmd = """SELECT CASE WHEN
(SELECT nickname FROM users WHERE LOWER(nickname) = LOWER('{nickname}'))
IS NOT NULL THEN FALSE ELSE TRUE END AS "not_found";
""".format(**data)
result.update({'conflict': False})
db_service.execute(check_cmd)
result.update(db_service.get_one())
db_service.reconnect()
return result
def status(self):
result = {}
cmd = 'SELECT COUNT(*) AS user FROM users;'
db_service.execute(cmd)
result.update(db_service.get_one())
cmd = 'SELECT COUNT(*) AS post FROM posts;'
db_service.execute(cmd)
result.update(db_service.get_one())
cmd = 'SELECT COUNT(*) AS forum FROM forums;'
db_service.execute(cmd)
result.update(db_service.get_one())
cmd = 'SELECT COUNT(*) AS thread FROM threads;'
db_service.execute(cmd)
result.update(db_service.get_one())
return result
def details(self, data):
db_service.reconnect()
cmd = """SELECT * FROM posts p WHERE p.id = {id};
""".format(**data)
db_service.execute(cmd)
result = {"post": db_service.get_one()}
# if 'created' in result['post'].keys() and result['post']['created'] is not None:
# result['post']['created'] = time_to_str(result['post']['created'])
result['post']['isEdited'] = result['post']['isedited']
if 'user' in data['related']:
cmd += """SELECT u.email, u.about, u.nickname, u.fullname
FROM users u JOIN posts p ON p.author = u.nickname WHERE p.id = {id};
""".format(**data)
db_service.execute(cmd)
result.update({"author": db_service.get_one()})
if 'thread' in data['related']:
cmd += """SELECT t.author, t.created, t.forum, t.id, t.message, t.slug, t.title, t.votes
FROM threads t JOIN posts p ON p.thread = t.id WHERE p.id = {id};
""".format(**data)
db_service.execute(cmd)
result.update({"thread": db_service.get_one()})
if 'created' in result['thread'].keys(
) and result['thread']['created'] is not None:
result['thread']['created'] = time_to_str(
result['thread']['created'])
if 'forum' in data['related']:
cmd += """SELECT f.slug, f.posts, f.threads, f.title, f.author
FROM forums f JOIN posts p ON p.forum = f.slug WHERE p.id = {id};
""".format(**data)
db_service.execute(cmd)
result.update({"forum": db_service.get_one()})
if 'created' in result['forum'].keys(
) and result['forum']['created'] is not None:
result['forum']['created'] = time_to_str(
result['forum']['created'])
result['forum']['user'] = result['forum']['author']
result['forum'].pop('author')
return result
def update(self, data):
cmd = """UPDATE posts SET {message_data} isEdited = TRUE WHERE id = {id};
""".format(message_data="message='" + data['message'] + "',", **data)
db_service.execute(cmd)
return self.get_post_by_id(data['id'])
def get_next_id(self):
cmd = "SELECT nextval(pg_get_serial_sequence('posts', 'id'))"
db_service.execute(cmd)
return db_service.get_one()['nextval']
