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 blog():
query = 'SELECT * FROM posts'
cur = flask.g.db.cursor()
cur.execute(query);
rows = cur.fetchall()
keys = [ i[0] for i in cur.description ]
posts = [ dict(zip(keys, row)) for row in rows ]
for post in posts:
post['post_date'] = utils.normalize_timestamp(post['post_date'])
return flask.render_template('blog/index.html', posts=posts[::-1])
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 timestamp_generator(time_meta):
start_time = time_meta["start"]
interval = time_meta["period"]
offset = time_meta["offset"]
# number of unit based on second
time_unit = timeunitstr2enum(time_meta["unit"])
# normalize timestamp in config file.
time_val = utils.timestr_to_timestamp(start_time)
curr_time = utils.start_timestamp()
num_intervals = (curr_time - time_val)/interval
time_val += interval * num_intervals
time_val += offset
while True:
curr_clock = yield utils.normalize_timestamp(time_val, time_unit)
num_intervals = (curr_clock - time_val)/interval
time_val += interval * num_intervals
def thread_details_get(slug_or_id):
thread_id = None
thread_slug = None
try:
thread_id = int(slug_or_id)
except ValueError:
thread_slug = slug_or_id
# try:
if thread_id:
thread = api.methods.thread_select_by_id.first(thread_id)
else:
thread = api.methods.thread_select_by_slug.first(thread_slug)
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
forum_slug = api.methods.forum_select_by_id.first(forum_id)[1]
author = api.methods.user_select_by_id.first(thread_author_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,
time_to='+03:00',
database_format=True),
'votes':
voice
}
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
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