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]
