def create_by_nickname(self, nickname, payload): user = self.get_all_by_nickname_or_email(nickname, payload['email']) if user: return user, self.EXISTS payload['nickname'] = nickname sql = self.sql_insert(fields=payload) user = DbConnector.execute_set_and_get(self.sql_insert_returning(sql)) return user[0], self.CREATED
def create_posts(cls, posts_data, thread_id, forum_id): insert_data = tuple() if not posts_data: return [] rel_insert = "" for post in posts_data: rel_insert += "(%s, %s, %s), " insert_data += (post['author_id'], forum_id, post['author_nickname']) rel_insert = rel_insert[:-2] sql = """ UPDATE forum SET count_posts = count_posts + {count_posts} WHERE id = {forum_id}; INSERT INTO user_forum (user_id, forum_id, user_nickname) VALUES {rel_insert} ON CONFLICT DO NOTHING; INSERT INTO {tbl_name} (user_id, thread_id, parent_id, message) VALUES """.format_map({ 'tbl_name': cls.tbl_name, 'count_posts': len(posts_data), 'forum_id': forum_id, 'rel_insert': rel_insert }) for post in posts_data: sql += "(%s, %s, %s, %s)," insert_data += (post['author_id'], thread_id, post['parent_id'], post['message']) sql = sql[:-1] sql += """ RETURNING id """ ids = DbConnector.execute_set_and_get(sql, insert_data) sql = """ SELECT m.nickname as author, p.created as created, f.slug as forum, p.id as id, p.message as message, p.thread_id as thread, p.parent_id as parent FROM {tbl_name} AS p JOIN {u_tbl_name} AS m ON m.id = p.user_id JOIN {t_tbl_name} AS t ON t.id = p.thread_id JOIN {f_tbl_name} AS f ON f.id = t.forum_id WHERE p.id in ( """.format_map({ 'tbl_name': cls.tbl_name, 'u_tbl_name': User.tbl_name, 't_tbl_name': Thread.tbl_name, 'f_tbl_name': Forum.tbl_name, }) for i in ids: sql += "{0}, ".format(i['id']) sql = sql[:-2] + ")" + " ORDER BY id" return DbConnector.execute_get(sql)
def update_by_id(self, id, payload): update = '' counter = len(payload) for key, value in payload.items(): counter -= 1 update += " {0} = '{1}'".format(key, value) if counter > 0: update += "," sql = """ UPDATE {tbl_name} SET {update_str} WHERE id = {id} RETURNING nickname, fullname, email, about """.format(**{ 'tbl_name': self.tbl_name, 'id': id, 'update_str': update }) return DbConnector.execute_set_and_get(sql)[0]
def create(self, payload): sql = """ WITH f AS ( INSERT INTO {tbl_name} (user_id, slug, title) VALUES ({user_id}, '{slug}', '{title}') RETURNING user_id, slug, title) SELECT {tbl_user}.nickname as user, f.slug, f.title FROM f JOIN {tbl_user} ON {tbl_user}.id = f.user_id """.format( **{ 'tbl_name': self.tbl_name, 'tbl_user': User.tbl_name, 'user_id': payload['user_id'], 'slug': payload['slug'], 'title': payload['title'] }) return DbConnector.execute_set_and_get(sql)[0]
def vote_for_thread(cls, user_id, voice, thread_id): sql = """ SELECT FROM {tbl_thread} WHERE id = %(thread_id)s FOR UPDATE; DELETE FROM {tbl_name} WHERE user_id = %(user_id)s AND thread_id = %(thread_id)s; INSERT INTO {tbl_name} (user_id, thread_id, voice) VALUES (%(user_id)s, %(thread_id)s, %(voice)s); UPDATE {tbl_thread} SET votes = sub.votes FROM (SELECT SUM(voice) as votes FROM {tbl_name} WHERE thread_id = %(thread_id)s) as sub WHERE id=%(thread_id)s; """.format_map({ 'tbl_name': cls.tbl_name, 'tbl_thread': Thread.tbl_name, # 'set_statement': 'votes = votes + %(voice)s' if int(voice) > 0 else 'votes = votes - %(voice)s' }) sql += """ SELECT u.nickname as author, t.created, f.slug as forum, t.id, t.message, t.slug as slug, t.title, t.votes as votes FROM "{0}" as t JOIN "{1}" as u ON u.id = t.user_id JOIN "{2}" as f ON t.forum_id = f.id WHERE t.id = %(thread_id)s """.format(Thread.tbl_name, User.tbl_name, Forum.tbl_name) variables = { 'thread_id': thread_id, 'voice': voice, 'user_id': user_id, } return DbConnector.execute_set_and_get(sql, variables)[0]