def get_posts_parent_tree_sorter(cls, thread_id, since, limit, desc):
from models.post import Post
sql = """
SELECT
sub.id AS id,
sub.created AS created,
sub.isEdited AS isEdited,
sub.message AS message,
sub.parent AS parent,
sub.thread AS thread,
sub.author AS author,
sub.forum AS forum
FROM
(SELECT
DENSE_RANK()
OVER (
ORDER BY p.root_id {order_direction} ) AS root_count,
p.root_id,
p.id AS id,
p.created AS created,
p.is_edited AS isEdited,
p.message AS message,
p.parent_id AS parent,
p.thread_id AS thread,
u.nickname AS author,
t.forum_slug AS forum
FROM posts AS p
JOIN member AS u ON u.id = p.user_id
JOIN thread AS t ON t.id = p.thread_id
--JOIN forum AS f ON f.id = t.forum_id
WHERE t.id = %(thread_id)s {where_additional}
ORDER BY p.path {order_direction}) AS sub
{limit_statement}
""".format_map({
'tbl_post':
Post.tbl_name,
'tbl_user':
User.tbl_name,
'tbl_thread':
Thread.tbl_name,
'tbl_forum':
Forum.tbl_name,
'where_additional':
"AND p.path > (SELECT path FROM Posts WHERE id = %(since)s) "
if since else " ",
'order_direction':
"DESC " if desc == "true" else "ASC ",
'limit_statement':
"WHERE root_count <= %(limit)s" if limit else " "
})
data = {
'thread_id': thread_id,
}
if limit:
data['limit'] = limit
if since:
data['since'] = since
if desc == "true":
sql = sql.replace('>', '<')
return DbConnector.execute_get(sql, data)
def get_forum_users(cls, forum_id, limit, since, desc):
sql = """
SELECT
u.nickname,
u.about,
u.email,
u.fullname
FROM member AS u
INNER JOIN user_forum AS uf ON u.id = uf.user_id
WHERE uf.forum_id = {forum_id} {additional_where}
GROUP BY u.id
ORDER BY lower(u.nickname){additional_order}
{limit}
""".format_map({
'forum_id':
forum_id,
'additional_order':
"DESC " if desc == 'true' else " ",
'limit':
"LIMIT %(limit)s " if limit else " ",
'additional_where':
"AND lower(u.nickname) > lower(%(since)s)" if since else " ",
})
data = {}
if limit:
data['limit'] = limit
if since:
data['since'] = since
if desc == 'true':
sql = sql.replace('>', '<')
resp = DbConnector.execute_get(sql, data)
return [] if not resp or resp[0]['email'] is None else resp
def find_others_with_nickname_or_email(self, user_id, nickname, email):
if not (nickname or email):
return []
where_condition = ''
if nickname:
where_condition += "LOWER({tbl_name}.nickname) = LOWER('{nickname}')".format(
**{
'tbl_name': self.tbl_name,
'nickname': nickname
})
if email:
where_condition += "{or} LOWER({tbl_name}.email) = LOWER('{email}')".format(
**{
'or': 'OR' if where_condition else '',
'tbl_name': self.tbl_name,
'email': email
})
where_condition = "WHERE ({where}) AND {tbl_name}.id != {id}".format(
**{
'where': where_condition,
'tbl_name': self.tbl_name,
'id': user_id
})
data = DbConnector.execute_get(
self.sql_builder(self.sql_select(), where_condition))
return data[0] if data else []
def check_user_and_thread(cls, thread_slug_or_id, nickname):
if thread_slug_or_id.isdigit():
thread_where_statement = "t.id = %(slug_or_id)s "
else:
thread_where_statement = "LOWER(t.slug) = %(slug_or_id)s "
thread_slug_or_id = thread_slug_or_id.lower()
sql = """
SELECT
'thread_id' AS sub,
t.id AS id
FROM {tbl_thread} AS t
WHERE {thread_where_statement}
UNION
SELECT
'user_id' AS sub,
u.id AS id
FROM {tbl_user} AS u
WHERE LOWER(u.nickname) = %(lowered_nickname)s
ORDER BY sub
""".format_map({
'tbl_thread': cls.tbl_name,
'tbl_user': User.tbl_name,
'thread_where_statement': thread_where_statement
})
data = DbConnector.execute_get(sql, {
'lowered_nickname': nickname.lower(),
'slug_or_id': thread_slug_or_id
})
if len(data) == 2:
return data[0]['id'], data[1]['id']
else:
return None, None
def get_by_slug_or_id_with_forum_id(cls, slug_or_id):
sql = """
SELECT
u.nickname as author,
t.created,
t.forum_slug as forum,
t.id,
t.message,
t.slug as slug,
t.title,
t.votes as votes,
t.forum_id as forum_id
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 LOWER(t.slug) = LOWER(%s)
""".format(cls.tbl_name, User.tbl_name, Forum.tbl_name)
try:
int(slug_or_id)
sql += "or t.id = %s"
data = (
slug_or_id,
slug_or_id,
)
except ValueError:
data = (slug_or_id, )
thread = DbConnector.execute_get(sql, data)
return thread[0] if thread else []
def get_post(cls, post_id):
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,
p.is_edited as isEdited
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 = %(post_id)s
""".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,
})
post = DbConnector.execute_get(sql, {'post_id': post_id})
if post:
post[0]['created'] = format_time(post[0]['created'])
return post[0] if post else []
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 get_serialised_with_user(cls, slug):
sql = """
SELECT
forum.count_posts as posts,
forum.slug,
forum.count_threads as threads,
forum.title,
member.nickname as user
FROM forum
JOIN member ON member.id = forum.user_id
WHERE LOWER(forum.slug) = LOWER(%s)
"""
data = DbConnector.execute_get(sql, (slug, ))
return data[0] if data else None
def get_posts_flat_sorted(cls, thread_id, since, limit, desc):
from models.post import Post
sql = """
SELECT
p.id AS id,
p.created AS created,
p.is_edited AS isEdited,
p.message AS message,
p.parent_id AS parent,
p.thread_id AS thread,
u.nickname AS author,
t.forum_slug AS forum
FROM {tbl_post} AS p
JOIN {tbl_user} AS u ON u.id = p.user_id
JOIN {tbl_thread} AS t ON t.id = p.thread_id
--JOIN {tbl_forum} AS f ON f.id = t.forum_id
WHERE t.id = %(thread_id)s {where_additional}
ORDER BY p.created {order_direction}, p.id {order_direction}
{limit_statement}
""".format_map({
'tbl_post':
Post.tbl_name,
'tbl_user':
User.tbl_name,
'tbl_thread':
Thread.tbl_name,
'tbl_forum':
Forum.tbl_name,
'where_additional':
"AND p.id > %(since_id)s" if since else " ",
'order_direction':
"DESC " if desc == "true" else "ASC ",
'limit_statement':
"LIMIT %(limit)s" if limit else " "
})
if desc == "true":
sql = sql.replace('>', '<')
data = {
'thread_id': thread_id,
}
if limit:
data['limit'] = limit
if since:
data['since_id'] = since
return DbConnector.execute_get(sql, data)
def get_user_ids_by_slug(cls, usernames):
if not usernames:
return []
l_names = tuple()
sql = """
SELECT
LOWER(nickname) as nickname,
id
FROM {tbl_name}
WHERE LOWER(nickname) in (
""".format_map({'tbl_name': cls.tbl_name})
for item in usernames:
l_names += (str(item).lower(), )
sql += " %s,"
sql = sql[:-1]
sql += ")"
return DbConnector.execute_get(sql, l_names)
def get_posts_by_id_in_thread(cls, thread_id, posts_ids):
in_condition = ''
in_vars = tuple()
for id in posts_ids:
in_condition += " %s,"
in_vars += (id, )
in_condition = in_condition[:-1]
sql = """
SELECT
p.id
FROM {tbl_posts} AS p
WHERE p.id in ({in_condition}) AND p.thread_id = {thread_id}
""".format_map({
'tbl_posts': Post.tbl_name,
'in_condition': in_condition,
'thread_id': thread_id
})
return DbConnector.execute_get(sql, in_vars)
def get_by_slug_with_id(self, slug):
sql = """
SELECT
{tbl_name}.id,
{tbl_user}.nickname as user,
{tbl_name}.slug,
{tbl_name}.title
FROM {tbl_name}
JOIN {tbl_user} ON {tbl_user}.id = {tbl_name}.user_id
WHERE LOWER({tbl_name}.slug) = LOWER('{slug}')
""".format(
**{
'sql_select': self.sql_select(),
'tbl_name': self.tbl_name,
'tbl_user': User.tbl_name,
'slug': slug
})
data = DbConnector.execute_get(sql)
return data[0] if data else []
def get_info(cls, post_id, related):
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,
p.is_edited as isEdited
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 = %(post_id)s
""".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,
})
post = DbConnector.execute_get(sql, {'post_id': post_id})
if post:
post[0]['isEdited'] = post[0]['isedited']
post[0]['created'] = format_time(post[0]['created'])
data = {'post': post[0] if post else None}
if related and post:
related = related.split(',')
if 'user' in related:
data['author'] = User().get_by_nickname(post[0]['author'])
if 'thread' in related:
data[
'thread'] = Thread.get_serialised_with_forum_user_by_id_or_slug(
id=post[0]['thread'])
data['thread']['created'] = format_time(
data['thread']['created'])
if 'forum' in related:
data['forum'] = Forum().get_by_slug(post[0]['forum'])
return data
def get_threads_list(cls, slug, limit, since, desc):
data_tuple = tuple()
sql = """
SET TIME ZONE 'GMT+3';
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
""".format(cls.tbl_name, User.tbl_name, Forum.tbl_name)
sql += """
WHERE LOWER(f.slug) = LOWER(%s)
"""
data_tuple += (slug, )
if since:
if desc == 'true':
sql += """
AND t.created <= %s
"""
else:
sql += """
AND t.created >= %s
"""
data_tuple += (since, )
if desc == 'true':
sql += 'ORDER BY t.created DESC'
else:
sql += 'ORDER BY t.created'
if limit:
sql += " LIMIT {0}".format(int(limit))
return DbConnector.execute_get(sql, data_tuple)
def get_count(cls):
sql = """
SELECT COUNT(*) as count FROM {tbl_name}
""".format_map({'tbl_name': cls.tbl_name})
return DbConnector.execute_get(sql)[0]['count']
def get_all_by_nickname_or_email(self, nickname, email):
sql = self.sql_builder(
self.sql_select(),
self.sql_where_nickname_or_email(nickname, email))
return DbConnector.execute_get(sql)
def get_others_with_nickname_or_email(self, user_id, nickname, email):
sql = self.sql_builder(
self.sql_select(),
self.sql_others_nickname_or_email(user_id, nickname, email))
return DbConnector.execute_get(sql)
def get_by_nickname(self, nickname, hide_id=True):
sql = self.sql_builder(self.sql_select(hide_id=hide_id),
self.sql_where_nickname(nickname))
data = DbConnector.execute_get(sql)
return data[0] if data else {}