def user_create(nickname, payload=None):
about = payload.get('about')
email = payload.get('email')
fullname = payload.get('fullname')
try:
with connection.xact():
user_insert = connection.prepare(
'INSERT INTO "user" VALUES (DEFAULT, $1::citext, $2::citext, $3::citext, $4::citext)'
)
user_insert(nickname, about, email, fullname)
except UniqueError:
user_select = connection.prepare(
'SELECT * FROM "user" WHERE nickname = $1::citext OR email = $2::citext'
)
conflict_list = []
for _, ex_nickname, ex_about, ex_email, ex_fullname in user_select(
nickname, email):
conflict_list.append(
flush_dictionary({
'nickname': ex_nickname,
'about': ex_about,
'email': ex_email,
'fullname': ex_fullname
}))
return conflict_list, 409
result = flush_dictionary({
'nickname': nickname,
'about': about,
'email': email,
'fullname': fullname
})
return result, 201
def forum_create(payload):
slug = payload.get('slug')
title = payload.get('title')
user = payload.get('user')
try:
with connection.xact():
user_select = connection.prepare(
'SELECT * FROM "user" WHERE nickname = $1::CITEXT')
forum_insert = connection.prepare(
'INSERT INTO forum VALUES (DEFAULT, $1::CITEXT, $2::TEXT, $3::BIGINT)'
)
user_record = user_select.first(user)
if not user_record:
resp = DEFAULT_ERROR_DICT
return resp, 404
user_id = user_record[0]
forum_insert(slug, title, user_id)
resp = {'slug': slug, 'user': user_record[1], 'title': title}
return resp, 201
except:
forum_select = connection.prepare(
'SELECT * FROM forum WHERE slug = $1::CITEXT')
forum = forum_select.first(slug)
if forum:
user_select = connection.prepare(
'SELECT * FROM "user" WHERE id = $1::BIGINT')
user = user_select.first(forum[3])
username = user[1]
resp = {'slug': forum[1], 'user': username, 'title': forum[2]}
return resp, 409
def user_objects_get(slug, query_args):
desc = query_args.get('desc', False)
desc = True if desc == 'true' else False
since = query_args.get('since')
limit = query_args.get('limit')
if limit:
limit = int(limit)
forum_id = connection.prepare(
'SELECT id from forum WHERE slug = $1').first(slug)
if not forum_id:
error = DEFAULT_ERROR_DICT
return error, 404
sort_option = ['ASC', 'DESC'][desc]
comp = ['>', '<'][desc]
since_opt = ''
if limit:
limit_opt = 'LIMIT $2'
if since:
since_opt = 'AND usernick ' + comp + '$3'
sql_query = '''
SELECT usr.*
FROM userforum
JOIN "user" AS usr ON userid = usr.id
WHERE forumid = $1 {since}
ORDER BY usernick {sort} {limit}'''.format(sort=sort_option,
limit=limit_opt,
since=since_opt)
users_select = connection.prepare(sql_query)
if since:
users = users_select(forum_id, limit, since)
else:
users = users_select(forum_id, limit)
else:
if since:
since_opt = 'AND nickname ' + comp + '$2'
sql_query = '''
SELECT usr.*
FROM userforum
JOIN "user" AS usr ON userid = usr.id
WHERE forumid = $1 {since}
ORDER BY usernick {sort}'''.format(sort=sort_option, since=since_opt)
users_select = connection.prepare(sql_query)
if since:
users = users_select(forum_id, since)
else:
users = users_select(forum_id)
result = [{
'nickname': user[1],
'about': user[2],
'email': user[3],
'fullname': user[4]
} for user in users]
return result, 200
def post_details_update(post_id, payload):
new_message = payload.get('message')
post_id = int(post_id)
message_update = connection.prepare('''
WITH updated AS (
UPDATE message SET message = $2, isedited = sub.isedited OR sub.message IS DISTINCT FROM $2
FROM (
SELECT * FROM message WHERE id = $1 FOR UPDATE
) as sub
JOIN "user" ON sub.authorid = "user".id
JOIN forum ON sub.forumid = forum.id
RETURNING sub.authorid, sub.threadid, sub.forumid,
sub.created_on, message.message, message.isedited, slug, nickname
)
SELECT created_on, message, nickname, threadid, slug, isedited FROM updated'''
)
# Consider returning less {remove thread} (Optimisation)
# Consider using a pre-update trigger to determine isedited and prevent needless message updates (Optimisation)
if new_message:
message = message_update.first(post_id, new_message)
else:
message = connection.prepare('''
SELECT m.created_on, m.message, "user".nickname, m.threadid, forum.slug, isedited FROM (
SELECT created_on, message, authorid, threadid, forumid, isedited
FROM message WHERE id = $1
) as m
JOIN "user" ON m.authorid = "user".id
JOIN forum ON m.forumid = forum.id''').first(post_id)
if not message:
error = DEFAULT_ERROR_DICT
return error, 404
# Consider returning less {remove thread} (Optimisation)
resp = {
'id': post_id,
'created': normalize_timestamp(message[0], time_to='+03:00'),
'message': message[1],
'author': message[2],
'thread': message[3],
'forum': message[4],
'isEdited': message[5]
}
return resp, 200
def thread_objects_get(slug, query_args):
limit = query_args.get('limit')
desc = query_args.get('desc', False)
desc = True if desc == 'true' else False
since = query_args.get('since')
sort_option = ['ASC', 'DESC'][1 if desc else 0]
since_cond = ''
if since is not None:
# since = normalize_timestamp(since.decode('utf-8'))
since = normalize_timestamp(since)
since_cond = ' AND created_on ' + [
'>=', '<='
][1 if desc else 0] + '\'' + str(since) + '\''
forum_select = connection.prepare(
'SELECT * FROM forum WHERE slug = $1::CITEXT')
forum = forum_select.first(slug)
if not forum:
resp = DEFAULT_ERROR_DICT
return resp, 404
thread_select = connection.prepare(
'SELECT t.id, slug, created_on, message, title, nickname, voice'
' FROM thread as t'
' JOIN "user" as u ON t.authorid = u.id WHERE t.forumid = $1::BIGINT'
+ since_cond + ' ORDER BY created_on ' + sort_option + ' LIMIT $2')
threads = []
for id, slug, created, message, title, nickname, voice in thread_select(
forum[0],
int(limit) if limit else None):
thread = {
'id': id,
'slug': slug,
'created': normalize_timestamp(created),
'message': message,
'title': title,
'author': nickname,
'forum': forum[1],
'votes': voice,
}
threads.append(flush_dictionary(thread))
return threads, 200
def forum_details_get(slug):
forum_select = connection.prepare(
'SELECT * FROM forum WHERE slug = $1::CITEXT')
forum = forum_select.first(slug)
if forum:
user_select = connection.prepare(
'SELECT * FROM "user" WHERE id = $1::BIGINT')
user = user_select.first(forum[3])
username = user[1]
resp = {
'slug': forum[1],
'user': username,
'title': forum[2],
'posts': forum[4],
'threads': forum[5]
}
return resp, 200
resp = DEFAULT_ERROR_DICT
return resp, 404
def user_profile_update(nickname, payload):
about = payload.get('about')
email = payload.get('email')
fullname = payload.get('fullname')
try:
with connection.xact():
if payload:
user_update = connection.prepare(
'''UPDATE "user" SET about = coalesce($2, about),
email = coalesce($3, email),
fullname = coalesce($4, fullname)
WHERE nickname = $1::CITEXT'''
)
affected = user_update.first(nickname, about, email, fullname)
if affected is 0:
resp = DEFAULT_ERROR_DICT
return resp, 404
user_select = connection.prepare(
'SELECT * FROM "user" WHERE nickname = $1::CITEXT')
user = user_select.first(nickname)
if user:
about = user[2]
email = user[3]
fullname = user[4]
else:
resp = DEFAULT_ERROR_DICT
return resp, 404
return {
'fullname': fullname,
'email': email,
'nickname': nickname,
'about': about
}, 200
except:
resp = DEFAULT_ERROR_DICT
return resp, 409
def user_profile_get(nickname):
with connection.xact():
user_select = connection.prepare(
'SELECT * FROM "user" WHERE nickname = $1::CITEXT')
user = user_select.first(nickname)
if user:
resp = flush_dictionary({
'nickname': user[1],
'about': user[2],
'email': user[3],
'fullname': user[4]
})
return resp, 200
else:
resp = DEFAULT_ERROR_DICT
return resp, 404
def service_status_get():
status = connection.prepare('''
SELECT count(forum.id) FROM forum
UNION ALL
SELECT count(thread.id) FROM thread
UNION ALL
SELECT count("user".id) FROM "user"
UNION ALL
SELECT count(message.id) FROM message''')()
resp = {
'forum': status[0][0],
'post': status[3][0],
'thread': status[1][0],
'user': status[2][0]
}
return resp, 200
def thread_details_update(slug_or_id, payload):
thread_slug, thread_id = parse_slug_or_id(slug_or_id)
message = payload.get('message')
title = payload.get('title')
with connection.xact():
thread_update = connection.prepare(
'''UPDATE thread SET message = coalesce($2, message), title = coalesce($3, title) WHERE {cond}
RETURNING id, slug, created_on, message, title, authorid, forumid, voice'''
.format(cond='id = $1' if thread_id else 'slug = $1'))
if thread_id:
thread = thread_update.first(thread_id, message, title)
else:
thread = thread_update.first(thread_slug, message, title)
if not thread:
error = DEFAULT_ERROR_DICT
return error, 404
thread_id, thread_slug, thread_created_on, thread_message, \
thread_title, thread_author_id, forum_id, voice \
= thread
author = api.methods.user_select_by_id.first(thread_author_id)[1]
forum_slug = api.methods.forum_select_by_id.first(forum_id)[1]
resp = {
'slug': thread_slug,
'forum': forum_slug,
'message': thread_message,
'title': thread_title,
'author': author,
'id': thread_id,
'created': normalize_timestamp(thread_created_on),
'votes': voice
}
return resp, 200
from settings import connection
user_select_by_id = connection.prepare('SELECT * FROM "user" WHERE id = $1::BIGINT')
user_select_by_nickname = connection.prepare('SELECT * FROM "user" WHERE nickname = $1::CITEXT')
forum_select_by_id = connection.prepare('SELECT * FROM forum WHERE id = $1::BIGINT')
thread_select_by_id = connection.prepare('SELECT * FROM thread WHERE id = $1::BIGINT')
thread_select_by_slug = connection.prepare('SELECT * FROM thread WHERE slug = $1::CITEXT')
increment_thread_count = connection.prepare('''
UPDATE forum
SET threads_count = threads_count + 1
WHERE id = $1;''')
def post_create(slug_or_id, payload):
thread_id = None
slug = None
try:
thread_id = int(slug_or_id)
except:
slug = slug_or_id
created = normalize_timestamp(arrow.Arrow.utcnow())
try:
with connection.xact() as xact:
# Оставить
if thread_id is not None:
thread_select = connection.prepare(
'SELECT * FROM thread WHERE id = $1::BIGINT')
thread = thread_select.first(thread_id)
else:
thread_select = connection.prepare(
'SELECT * FROM thread WHERE slug = $1::CITEXT')
thread = thread_select.first(slug)
if not thread:
error = DEFAULT_ERROR_DICT
return error, 404
if not payload:
return [], 201
thread_id = thread[0]
forum_select = connection.prepare(
'SELECT id, slug FROM forum WHERE id = $1::BIGINT')
forum = forum_select.first(thread[6])
forum_slug = forum[1]
forum_id = forum[0]
# Оставить
message_insert = connection.prepare(
'''INSERT INTO message (created_on, "message", authorid, threadid, forumid, parentid)
SELECT
$1 :: TEXT :: TIMESTAMP,
$2 :: TEXT,
usr.id,
$4 :: BIGINT,
$5 :: BIGINT,
$6 :: BIGINT
FROM "user" AS usr
WHERE nickname = $3 :: CITEXT RETURNING id, authorid;''')
forum_user_insert = connection.prepare(
'''INSERT INTO userforum (forumid, usernick, userid) VALUES ($1, $2::CITEXT, $3)
ON CONFLICT DO NOTHING;''')
result = []
forumuser = []
created_str = normalize_timestamp(created,
json_format=True,
time_to='+03:00')
try:
for item in payload:
id = message_insert(created, item['message'],
item['author'], thread_id, forum_id,
item.get('parent', 0))
if len(id) != 1:
xact.rollback()
raise NotFoundException
message = {
'created': created_str,
'message': item['message'],
'author': item['author'],
'id': id[0][0],
'parent': to_int(item.get('parent', 0)),
'thread': thread_id,
'forum': forum_slug
}
forumuser.append((forum_id, item['author'], id[0][1]))
result.append(message)
except KeyError:
error = DEFAULT_ERROR_DICT
return error, 404
forumuser.sort(key=lambda val: val[1])
forum_user_insert.load_rows(forumuser)
connection.prepare('''
UPDATE forum
SET posts_count = posts_count + $1
WHERE id = $2;''')(len(payload), forum_id)
return result, 201
except NotFoundException:
return DEFAULT_ERROR_DICT, 404
except Exception as e:
if e.message == 'invalid_foreign_key':
error = DEFAULT_ERROR_DICT
return error, 409
import traceback
print(traceback.format_exc())
def post_objects_get(slug_or_id, query_args):
thread_slug, thread_id = parse_slug_or_id(slug_or_id)
sort = query_args.get('sort')
since = query_args.get('since')
if since is not None:
since = int(since)
desc = query_args.get('desc', False)
desc = True if desc == 'true' else False
if desc:
sort_option = 'DESC'
else:
sort_option = 'ASC'
limit = int(query_args.get('limit'))
comp = ['>', '<'][1 if desc else 0]
if since:
since_cond_message = ' AND message.id ' + [
'>', '<'
][1 if desc else 0] + ' $3::BIGINT'
since_cond_m = ' WHERE m.id ' + ['>', '<'
][1 if desc else 0] + ' $3::BIGINT'
since_cond_tree = 'WHERE m.parenttree' + comp + ' (SELECT parenttree FROM message WHERE id = $3::BIGINT)' # AND (message.parenttree[2] ' + ['>', '<'][desc] + ' $3::BIGINT OR message.parenttree[2] IS NULL) '
else:
since_cond_tree = since_cond_message = since_cond_m = ''
select_fields = 'SELECT m.id, m.created_on, m.message, m.threadid, m.parentid, "user".nickname, forum.slug'
inner_select_fields = '''SELECT message.id, message.created_on, message.threadid, message.message,
message.parentid, message.authorid, message.forumid'''
inner_select_fields_tree = inner_select_fields + ', message.parenttree'
with connection.xact():
messages = []
if not thread_id:
thread_id = connection.prepare(
'SELECT id FROM thread WHERE slug = $1').first(thread_slug)
else:
thread_id = connection.prepare(
'SELECT id FROM thread WHERE id = $1').first(thread_id)
if not thread_id:
error = DEFAULT_ERROR_DICT
return error, 404
if sort == 'flat' or sort is None:
if thread_slug:
message_select = connection.prepare('''
{select_fields}
FROM (
{select_inner}
FROM message JOIN thread ON message.threadid = thread.id
WHERE thread.slug = $1 {since} ORDER BY message.id {sort} LIMIT $2
) as m
JOIN "user" ON "user".id = m.authorid
JOIN forum ON m.forumid = forum.id
ORDER BY m.id {sort}'''.format(
sort=sort_option,
since=since_cond_message,
select_fields=select_fields,
select_inner=inner_select_fields))
if since:
messages = message_select(thread_slug, limit, since)
else:
messages = message_select(thread_slug, limit)
else:
message_select = connection.prepare('''
{select_fields}
FROM (
{select_inner}
FROM message WHERE message.threadid = $1 {since} ORDER BY message.id {sort} LIMIT $2
) as m
JOIN "user" ON "user".id = m.authorid
JOIN forum ON m.forumid = forum.id
ORDER BY m.id {sort}'''.format(
sort=sort_option,
since=since_cond_message,
select_fields=select_fields,
select_inner=inner_select_fields))
if since:
messages = message_select(thread_id, limit, since)
else:
messages = message_select(thread_id, limit)
elif sort == 'tree':
if thread_slug:
message_select = connection.prepare('''
{select_fields}
FROM (
{select_inner}
FROM message JOIN thread ON message.threadid = thread.id
WHERE thread.slug = $1 ORDER BY message.parenttree {sort}
) as m
JOIN "user" ON "user".id = m.authorid
JOIN forum ON m.forumid = forum.id
{since}
ORDER BY m.parenttree {sort} LIMIT $2'''.format(
sort=sort_option,
since=since_cond_tree,
select_fields=select_fields,
select_inner=inner_select_fields_tree))
if since:
messages = message_select(thread_slug, limit, since)
else:
messages = message_select(thread_slug, limit)
else:
message_select = connection.prepare('''
{select_fields}
FROM (
{select_inner}
FROM message
WHERE message.threadid = $1 ORDER BY message.parenttree {sort}
) as m
JOIN "user" ON "user".id = m.authorid
JOIN forum ON m.forumid = forum.id
{since}
ORDER BY m.parenttree {sort} LIMIT $2'''.format(
sort=sort_option,
since=since_cond_tree,
select_fields=select_fields,
select_inner=inner_select_fields_tree))
if since:
messages = message_select(thread_id, limit, since)
else:
messages = message_select(thread_id, limit)
elif sort == 'parent_tree':
if since:
message_select = connection.prepare('''
{select_fields}
FROM (
SELECT * FROM message
WHERE message.threadid = $1 AND (message.parenttree)[1] in (
SELECT
m1.id
FROM message m1
WHERE m1.parentid = 0 AND m1.threadid = $1 AND m1.parenttree {comp} (
SELECT m2.parenttree
FROM message m2
WHERE m2.id = $3
)
ORDER BY m1.parenttree {sort} LIMIT $2
)
) as m
JOIN "user" ON "user".id = m.authorid
JOIN forum ON m.forumid = forum.id
ORDER BY m.parenttree {sort}'''.format(
sort=sort_option,
comp=comp,
since=since_cond_message,
select_fields=select_fields,
select_inner=inner_select_fields_tree))
messages = message_select(thread_id, limit, since)
else:
message_select = connection.prepare('''
{select_fields}
FROM (
SELECT * FROM message
WHERE message.threadid = $1 AND message.parenttree [1] in (
SELECT
m1.id
FROM message m1
WHERE m1.parentid = 0 AND m1.threadid = $1
ORDER BY m1.parenttree {sort} LIMIT $2
)
) as m
JOIN "user" ON "user".id = m.authorid
JOIN forum ON m.forumid = forum.id
ORDER BY m.parenttree {sort}'''.format(
sort=sort_option,
since=since_cond_message,
select_fields=select_fields,
select_inner=inner_select_fields_tree))
messages = message_select(thread_id, limit)
result = []
for x in messages:
message = {
'id': x[0],
'created': normalize_timestamp(x[1], json_format=True),
'message': x[2],
'thread': x[3],
'parent': to_int(x[4]),
'author': x[5],
'forum': x[6]
}
result.append(message)
return result, 200
def thread_create(slug, payload):
author = payload.get('author')
created = payload.get('created')
thread_slug = payload.get('slug')
message = payload.get('message')
title = payload.get('title')
try:
with connection.xact():
if created:
created = normalize_timestamp(created)
author_select = connection.prepare('SELECT * FROM "user" WHERE nickname = $1::CITEXT')
forum_select = connection.prepare('SELECT * FROM forum WHERE slug = $1::CITEXT')
thread_create = connection.prepare(
'INSERT INTO thread VALUES (DEFAULT, $1::CITEXT, $2::TEXT::TIMESTAMP, '
'$3::TEXT, $4::TEXT, $5::BIGINT, $6::BIGINT) RETURNING id')
forum = forum_select.first(slug)
if not forum:
resp = DEFAULT_ERROR_DICT
return resp, 404
author = author_select.first(author)
if not author:
resp = DEFAULT_ERROR_DICT
return resp, 404
thread_id = thread_create.first(thread_slug, created, message, title, author[0], forum[0])
increment_thread_count(forum[0])
connection.prepare('INSERT INTO userforum (forumid, usernick, userid) VALUES ($1, $2::CITEXT, $3) ON CONFLICT DO NOTHING;')(forum[0], author[1], author[0])
raw_resp = {
'slug': thread_slug,
'forum': forum[1],
'message': message,
'title': title,
'author': author[1],
'id': thread_id,
'created': created
}
resp = flush_dictionary(raw_resp)
return resp, 201
except UniqueError:
thread_select = connection.prepare('SELECT * FROM thread WHERE slug = $1::CITEXT')
thread = thread_select.first(thread_slug)
thread_id = thread[0]
author_id = thread[5]
forum_id = thread[6]
author = connection.prepare('SELECT * FROM "user" WHERE id = $1::BIGINT').first(author_id)
forum = connection.prepare('SELECT * FROM forum WHERE id = $1::BIGINT').first(forum_id)
raw_resp = {
'slug': thread[1],
'forum': forum[1],
'message': thread[3],
'title': thread[4],
'author': author[1],
'id': thread_id,
'created': normalize_timestamp(thread[2])
}
resp = flush_dictionary(raw_resp)
return resp, 409
def post_details_get(post_id, query_args):
post_id = int(post_id)
related = query_args.get('related')
related_items = {'user': False, 'thread': False, 'forum': False}
if related:
related = related.split(',')
for item in related:
if item in ['user', 'thread', 'forum']:
related_items[item] = True
message_fields_len = 7
message_select = connection.prepare('''
SELECT m.created_on, m.message, "user".nickname, m.threadid, forum.slug, isedited, m.parentid
{thread_fields}
{forum_fields}
{user_fields}
FROM
(SELECT authorid, threadid, forumid, parentid, created_on, message, isedited FROM message
WHERE id = $1) as m
JOIN "user" ON "user".id = m.authorid
JOIN forum ON m.forumid = forum.id
{join_thread}
{join_forum_author}'''.format(
join_thread='JOIN thread on m.threadid = thread.id '
'JOIN "user" AS thread_author ON thread_author.id = thread.authorid'
if related_items['thread'] else '',
thread_fields=', thread.id, thread.slug, thread.created_on, '
'thread.message, thread.title, thread.voice, thread_author.nickname'
if related_items['thread'] else '',
join_forum_author=
'JOIN "user" AS forum_author ON forum_author.id = forum.userid'
if related_items['forum'] else '',
forum_fields=
', forum.id, forum.title, forum_author.nickname, forum.posts_count, forum.threads_count'
if related_items['forum'] else '',
user_fields=', "user".about, "user".email, "user".fullname'
if related_items['user'] else ''))
message = message_select.first(post_id)
if not message:
error = DEFAULT_ERROR_DICT
return error, 404
if related_items['thread']:
thread_offset = message_fields_len
thread = {
'id': message[thread_offset],
'slug': message[thread_offset + 1],
'created': normalize_timestamp(message[thread_offset + 2]),
'message': message[thread_offset + 3],
'title': message[thread_offset + 4],
'votes': message[thread_offset + 5],
'author': message[thread_offset + 6],
'forum': message[4]
}
thread_fields_len = 7
else:
thread = None
thread_fields_len = 0
if related_items['forum']:
forum_offset = message_fields_len + thread_fields_len
forum_fields_len = 5
forum = {
'id': message[forum_offset],
'slug': message[4],
'title': message[forum_offset + 1],
'user': message[forum_offset + 2],
'posts': message[forum_offset + 3],
'threads': message[forum_offset + 4]
}
else:
forum = None
forum_fields_len = 0
if related_items['user']:
user_offset = message_fields_len + forum_fields_len + thread_fields_len
user = {
'nickname': message[2],
'about': message[user_offset],
'email': message[user_offset + 1],
'fullname': message[user_offset + 2]
}
else:
user = None
post = {
'id': post_id,
'created': normalize_timestamp(message[0], time_to='+03:00'),
'message': message[1],
'author': message[2],
'thread': message[3],
'forum': message[4],
'isEdited': message[5],
'parent': message[6]
}
resp = {'post': post, 'thread': thread, 'forum': forum, 'author': user}
return flush_dictionary(resp), 200