def get_users(self, slug, limit, since, desk):
self.db_cur.execute(self.check_forum.format(slug=slug))
forum_status = self.db_cur.fetchall()
if not len(forum_status):
db.close()
return tornado.escape.json_encode(
{"message": "Can`t find user with id #42\n"}), '404'
db.obj_reconnect()
request = '''SELECT nickname, fullname, about, email FROM users u
JOIN usersForums t ON LOWER(u.nickname) = LOWER(t.author)
WHERE LOWER(t.forum) = LOWER('{slug}')'''\
.format(slug=slug)
if desk == 'true':
request += '''{since}'''\
.format(since=' AND LOWER(u.nickname) < ' + "LOWER('" + since + "')" if since != None else '')
else:
request += '''{since}''' \
.format(since=' AND LOWER(u.nickname) > ' + "LOWER('" + since + "')" if since != None else '')
request += ''' ORDER BY LOWER(u.nickname){desk_or_ask}{limit}'''\
.format(limit=' LIMIT ' + limit if limit != None else '',
desk_or_ask=' DESC' if desk == 'true' else ' ASC')
self.db_cur.execute(request)
users = self.db_cur.fetchall()
return tornado.escape.json_encode(users), '200'
def create_forum(self, slug, title, user_name):
self.db_cur.execute(self.check_user.format(nickname=user_name))
user_status = self.db_cur.fetchone()
if not user_status:
return tornado.escape.json_encode(
{"message": "Can`t find user with id #42\n"}), '404'
else:
user_name = user_status['nickname']
self.db_cur.execute(self.check_forum.format(slug=slug))
forum_status = self.db_cur.fetchone()
if forum_status:
db.close()
forum_status['user'] = forum_status['author']
return tornado.escape.json_encode(forum_status), '409'
self.db_cur.execute(
'''INSERT INTO forum (slug, title, author) VALUES ('{slug}','{title}', '{username}') RETURNING *;'''
.format(slug=slug, title=title, username=user_name))
forum = self.db_cur.fetchone()
forum['user'] = forum['author']
db.obj_reconnect(True)
return tornado.escape.json_encode(forum), '201'
def create_thread(self, forum, author, created, message, title, slug):
# db = DataBase()
# db_cur = db.get_object_cur()
self.db_cur.execute(
'''SELECT nickname FROM users WHERE LOWER(users.nickname) = LOWER('{author}');'''
.format(author=author))
author = self.db_cur.fetchone()
self.db_cur.execute(self.check_forum.format(slug=forum))
forum_status = db_cur.fetchone()
if not author or not forum_status:
db.close()
return tornado.escape.json_encode(
{"message": "Can`t find user with id #42\n"}), '404'
self.db_cur.execute('''INSERT INTO usersForums (author, forum)
SELECT '{author}', '{forum}'
WHERE NOT EXISTS
(SELECT forum FROM usersForums
WHERE LOWER(author) = LOWER('{author}') AND forum = '{forum}')'''
.format(author=author['nickname'],
forum=forum_status['slug']))
db.obj_reconnect(True)
if slug != None:
self.db_cur.execute(self.check_slug.format(slug=slug))
thread_status = self.db_cur.fetchall()
if len(thread_status):
thread_status[0]['created'] = datetime.isoformat(
thread_status[0]['created'])
return tornado.escape.json_encode(thread_status[0]), '409'
self.db_cur.execute(
'''INSERT INTO thread (created, message, title, author, forum{is_slug})
VALUES ('{created_on}', '{message}', '{title}', '{author}', '{forum}'{slug}) RETURNING *;'''
.format(is_slug=', slug' if slug != None else '',
created_on=created,
message=message,
title=title,
author=author['nickname'],
forum=forum_status['slug'],
slug=", '" + slug + "'" if slug != None else ''))
thread = self.db_cur.fetchone()
thread['created'] = datetime.isoformat(thread['created'])
db.obj_reconnect(True)
return tornado.escape.json_encode(thread), '201'
def get_forum(self, slug):
self.db_cur.execute(
'''SELECT * FROM forum WHERE LOWER(forum.slug) = LOWER('{slug}');'''
.format(slug=slug))
forum = self.db_cur.fetchone()
if not forum:
db.close()
return tornado.escape.json_encode(
{"message": "Can`t find user with id #42\n"}), '404'
self.db_cur.execute('''SELECT COUNT(*) FROM thread
WHERE LOWER(thread.forum) = LOWER('{slug}');'''.
format(slug=slug))
forum.update({'threads': db_cur.fetchone()['count']})
self.db_cur.execute('''SELECT COUNT(*) FROM messages
WHERE LOWER(messages.forum) = LOWER('{slug}');'''
.format(slug=slug))
forum.update({'posts': self.db_cur.fetchone()['count']})
forum['user'] = forum['author']
return tornado.escape.json_encode(forum), '200'
def create_post(self, id, forum, date, data):
author = data['author']
if 'parent' not in data:
data['parent'] = 0
self.db_cur.execute(self.check_user.format(nickname=author))
user = self.db_cur.fetchone()
if not user:
db.close()
return {"message": "Can`t find thread with id #42\n"}, '404'
self.db_cur.execute(
self.check_parent.format(id=data['parent'], thread=id))
parent = self.db_cur.fetchone()
if data['parent'] != 0:
if not parent:
db.close()
return {"message": "Can`t find parent with id #42\n"}, '409'
self.db_cur.execute('''INSERT INTO usersForums (author, forum)
SELECT '{author}', '{forum}'
WHERE NOT EXISTS
(SELECT forum FROM usersForums
WHERE LOWER(author) = LOWER('{author}') AND forum = '{forum}')'''
.format(author=author, forum=forum))
db.obj_reconnect(True)
if parent:
data['path'] = parent['path']
data['path'].append(parent['id'])
else:
data['path'] = []
path = ''
for x in data['path']:
path += str(x) + ','
if len(path) > 1:
path = path[:-1]
self.db_cur.execute(
'''SELECT nextval(pg_get_serial_sequence('messages', 'id'))''')
mid = self.db_cur.fetchone()
self.db_cur.execute(
'''INSERT INTO messages (id, created, message, author, thread, forum, parent, path)
VALUES ({mid}, '{datetime}','{message}','{username}', {thread}, '{forum}', {parent},
array_append(ARRAY[{path}]::integer[], {mid})) RETURNING *;'''
.format(datetime=date,
message=data['message'],
username=author,
thread=id,
parent=data['parent'],
forum=forum,
path=path,
mid=mid['nextval']))
post = self.db_cur.fetchone()
db.obj_reconnect(True)
# post['created'] = datetime.datetime.isoformat(post['created'])
return post, '201'