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']