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()