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 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 get_threads(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):
return tornado.escape.json_encode(
{"message": "Can`t find user with id #42\n"}), '404'
db.obj_reconnect()
request = '''SELECT * FROM thread
WHERE LOWER(thread.forum) = LOWER('{slug}') '''.format(
slug=slug)
if desk == 'true':
request += '''{since}'''\
.format(since=' AND thread.created <= ' + "'" + since + "'" if since != None else '')
else:
request += '''{since}''' \
.format(since=' AND thread.created >= ' + "'" + since + "'" if since != None else '')
request += ''' ORDER BY thread.created{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)
threads = self.db_cur.fetchall()
for thread in threads:
thread['created'] = datetime.isoformat(thread['created'])
return tornado.escape.json_encode(threads), '200'
def vote(self, id, slug, data):
self.db_cur.execute(self.check_user.format(nickname=data['nickname']))
user = self.db_cur.fetchone()
if not user:
return tornado.escape.json_encode(
{"message": "Can`t find thread with id #42\n"}), '404'
if id:
self.db_cur.execute(self.check_id_thread.format(id=id))
else:
self.db_cur.execute(self.check_slug_thread.format(slug=slug))
thread = self.db_cur.fetchone()
if not thread:
return tornado.escape.json_encode(
{"message": "Can`t find thread with id #42\n"}), '404'
self.db_cur.execute('''SELECT * FROM votes
WHERE votes.thread={thread} AND votes.nickname='{nickname}' LIMIT 1;'''
.format(thread=thread['id'],
nickname=data['nickname']))
vote = self.db_cur.fetchone()
if not vote:
self.db_cur.execute(
'''INSERT INTO votes (voice, nickname, thread) VALUES ({voice}, '{nickname}', {thread})'''
.format(voice=data['voice'],
nickname=data['nickname'],
thread=thread['id']))
db.obj_reconnect(True)
return self.update_vote_thread(thread['id'], data['voice']), 200
sum = vote['voice'] + data['voice']
if sum == 2 or sum == -2:
thread['created'] = datetime.datetime.isoformat(thread['created'])
return tornado.escape.json_encode(thread), '200'
elif not sum:
self.db_cur.execute('''UPDATE votes SET voice={voice}
WHERE votes.thread={thread} AND votes.nickname='{nickname}';'''
.format(thread=thread['id'],
nickname=data['nickname'],
voice=data['voice']))
return self.update_vote_thread(thread['id'],
2 * data['voice']), 200
else:
self.db_cur.execute('''UPDATE votes SET voice={voice}
WHERE votes.thread={thread} AND votes.nickname='{nickname}';'''
.format(thread=thread['id'],
nickname=data['nickname'],
voice=data['voice']))
return self.update_vote_thread(thread['id'], -vote['voice']), 200
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 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'