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 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_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_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_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 get_users_by_email_or_nick(self, data):
cmd = '''SELECT * FROM users WHERE LOWER(nickname) = LOWER('{nickname}') OR email = '{email}'
'''.format(**data)
db_service.execute(cmd)
return db_service.get_all()