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 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_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_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_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_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 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_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 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 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 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 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 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 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 get_next_id(self):
cmd = "SELECT nextval(pg_get_serial_sequence('posts', 'id'))"
db_service.execute(cmd)
return db_service.get_one()['nextval']
def get_post_path(self, id):
cmd = """SELECT path FROM posts WHERE id = {id}""".format(id=id)
db_service.execute(cmd)
return db_service.get_one()
def get_user(self, username):
cmd = '''SELECT * FROM users WHERE LOWER(nickname) = LOWER('{nickname}')
'''.format(nickname=username)
db_service.execute(cmd)
return db_service.get_one()
