async def login(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
try:
incoming_data = await request.json()
username = incoming_data.get('username')
username_lower = username.lower()
password = incoming_data.get('password')
pass_sha3_512 = await generate_pass_hash(username_lower, password)
except:
return json_response({'info': 'Incorrect data'}, status=400)
q = db\
.from_table('accounts')\
.select('accountid','username', 'email')\
.where(
(db.T('accounts').username == db.P('$1')) & \
(db.T('accounts').pass_sha3_512 == db.P('$2')))
args = [username_lower, pass_sha3_512]
result = await db.fetch(q, args)
if len(result) == 1:
user_data = {
'accountid': result[0].get('accountid'),
'username': result[0].get('username'),
'email': result[0].get('email')
}
q = db\
.from_table('permission_groups_accounts')\
.select('username', 'groupname')\
.where( db.T('permission_groups_accounts').username == db.P('$1') )\
.where( db.T('permission_groups_accounts').active == True )
args = [user_data['username'], ]
result = await db.fetch(q, args)
groupname = result[-1].get('groupname')
session = await new_session(request)
session['anonymous'] = False
session['user'] = user_data
session['group'] = groupname
print(groupname)
return json_response({
'info': 'You are logged in',
'data': user_data
})
return json_response({'info': 'User not found or incorrect data'}, status=404)
async def get_record(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
incoming_data = await request.json()
recordid = incoming_data.get('recordid')
if not isinstance(recordid, str):
return json_response({'info': 'Incorrect data'}, status=400)
q = db\
.from_table('records')\
.select('recordid', 'username', 'post_date', 'record_headline', 'record_text')\
.where(db.T('records').recordid == db.P('$1'))\
.where(db.T('records').active == True)
result = await db.fetch(q, [
recordid,
])
if len(result) != 1:
return json_response({
'info': 'Not found',
}, status=404)
record = {
'recordid': result[0].get('recordid'),
'username': result[0].get('username'),
'post_date': (result[0].get('post_date')).isoformat(),
'record_headline': result[0].get('record_headline'),
'record_text': result[0].get('record_text')
}
q = db\
.from_table('records_tags')\
.select('tagname')\
.where((db.T('records_tags').recordid == db.P('$1')) & \
(db.T('records_tags').active == True))
result = await db.fetch(q, [
recordid,
])
tags = [_.get('tagname') for _ in result]
return json_response({
'info': 'Record',
'data': {
'record': record,
'tags': tags
}
})
async def create_record(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
session = await get_session(request)
user_data = session.get('user')
tags_lower = []
try:
incoming_data = await request.json()
record_text = incoming_data.get('record_text')
tags = incoming_data.get('tags')
record_headline = incoming_data.get('record_headline')
assert isinstance(tags, list)
assert isinstance(record_text, str)
assert len(tags) > 0 and len(tags) < 11
for tag in tags:
assert re.match(r'^[a-zA-Z0-9_]{3,100}$', tag)
tags_lower.append(tag.lower())
assert len(record_text) <= 1000
except:
return json_response({'info': 'Incorrect data'}, status=400)
recid = recid_gen()
record = {
'recordid': recid,
'username': user_data['username'],
'record_text': record_text
}
if record_headline:
record['record_headline'] = record_headline
await db.insert_to_table('records', record)
for tag in tags_lower:
try:
await db.insert_to_table('tags', {'tagname': tag},
ignore_conflict=[
'tagname',
])
except UniqueViolationError:
pass
try:
await db.insert_to_table('records_tags', {
'tagname': tag,
'recordid': recid
})
except UniqueViolationError:
pass
return json_response({'info': 'Created', 'data': record})
async def wrapper(request):
session = await get_session(request)
if not session.get('anonymous') is False:
return json_response({'info': 'Permission denied'}, status=403)
return await handler(request)
async def get_record_media(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
incoming_data = await request.json()
recordid = incoming_data.get('recordid')
q = db\
.from_table('records')\
.select('recordid', 'username', 'post_date', 'record_headline', 'record_text')\
.where(db.T('records').recordid == db.P('$1'))\
.where(db.T('records').active == True)
result = await db.fetch(q, [
recordid,
])
if len(result) != 1:
return json_response({
'info': 'Not found',
}, status=404)
media_table = db.T('media')
records_media_table = db.T('records_media')
q = db.Q\
.from_(db.table('media').as_('m'))\
.select(
media_table.as_('m').mediaid,
media_table.as_('m').media_type,
media_table.as_('m').media_data,
media_table.as_('m').username,
media_table.as_('m').media_description)\
.join(db.table('records_media').as_('rm'))\
.on_field('mediaid')\
.where(records_media_table.as_('rm').recordid == db.P('$1'))\
.where(records_media_table.as_('rm').active == True)\
.where(media_table.as_('m').active == True)
result = await db.fetch(q, [
recordid,
])
result_dict = [dict(_) for _ in result]
return json_response({'info': 'Media data', 'data': result_dict})
async def session_info(request):
session = await get_session(request)
anonymous = session.get('anonymous')
if anonymous:
return json_response({'info': 'Session info', 'data': {'anonymous': True}})
return json_response({
'info': 'Session info',
'data': {
'anonymous': False,
'accountid': session.get('user').get('accountid'),
'username': session.get('user').get('username'),
'email': session.get('user').get('email')
}
})
async def logout(request):
session = await get_session(request)
if session.get('anonymous') is False:
session = await new_session(request)
session['anonymous'] = True
return json_response({'info': 'You are anonymous'})
async def get_subscriptions(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
session = await get_session(request)
username_self = session.get('user').get('username')
result = await make_qurey_to_subscriptions_by_username(username_self, db=db)
return json_response({'info': 'Subscriptions', 'data': result})
async def get_all_tables(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
try:
information_schema = db.S('information_schema')
tables = db.T('tables')
q = db.Q\
.from_(information_schema.tables)\
.select('*')\
.where(tables.table_schema == db.P('$1'))
result = await db.fetch(q, [cfg.SCHEMA_NAME,])
result_list = await normalize_result(result)
return json_response({'info': 'Tables list', 'data': result_list})
except Exception as e:
return json_response({'info': 'error', 'data': str(e)})
async def insert_values(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
incoming_data = await request.json()
table_name = incoming_data.get('table_name')
values = incoming_data.get('values')
try:
assert isinstance(table_name, str)
assert isinstance(values, dict)
except AssertionError:
return json_response({'info': 'Incorrect data'}, status=400)
# try:
res = await db.insert_to_table(table_name, values)
return json_response({'info': 'Inserted', 'data': str(res)})
# except Exception as e:
# return json_response({'info': 'error', 'data': str(e)})
async def delete_subscription(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
session = await get_session(request)
username_self = session.get('user').get('username')
incoming_data = await request.json()
subgroupid = incoming_data.get('subgroupid')
if not isinstance(subgroupid, str):
return json_response({'Incorrect data'}, status=400)
updating_table = db.table('subscriptions_groups')
q = db.Q\
.update(updating_table).set(updating_table.active, False)\
.where((updating_table.username == username_self) & (updating_table.subgroupid == db.P('$1')))
result = await db.execute(q, [subgroupid, ])
return json_response({'info': 'Deleted'})
async def register(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
try:
incoming_data = await request.json()
valid_user_data = await validate_user_data(incoming_data)
except:
return json_response({'info': 'Incorrect data'}, status=400)
if valid_user_data:
try:
await db.insert_to_table('accounts', valid_user_data)
await db.insert_to_table(
'permission_groups_accounts',
{'username': valid_user_data['username'], 'groupname': 'user'})
return json_response({'info': 'Now you are registered here'})
except UniqueViolationError:
return json_response({'info': 'Already exists'}, status=409)
return json_response({'info': 'Incorrect data'}, status=400)
async def get_table_columns(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
async def fetch_columns(table_name):
information_schema = db.S('information_schema')
columns = db.T('columns')
q = db.Q\
.from_(information_schema.columns)\
.select('*')\
.where(columns.table_schema == db.P('$1'))\
.where(columns.table_name == db.P('$2'))
result = await db.fetch(q, [cfg.SCHEMA_NAME, table_name])
if len(result) > 0:
return await normalize_result(result)
return None
try:
table_name = (await request.json()).get('table_name')
assert isinstance(table_name, str)
except:
return json_response({'info': 'Incorrect data'}, status=400)
try:
result = await fetch_columns(table_name)
if result:
return json_response({'info': 'Columns list', 'data': result})
return json_response({'info': 'Not found'}, status=404)
except UndefinedTableError:
return json_response({'info': 'Table not found'}, status=404)
except Exception as e:
return json_response({'info': 'error', 'data': str(e)})
async def wrapper(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
session = await get_session(request)
username = session.get('user').get('username')
premission_groups_accounts_s = db.table('permission_groups_accounts')
premission_groups_accounts = db.T('permission_groups_accounts')
q = db.Q\
.from_(premission_groups_accounts_s)\
.select('groupname')\
.where(premission_groups_accounts.username == db.P('$1'))\
.where(premission_groups_accounts.active == True)
result = await db.fetch(q, [
username,
])
if len(result) > 0:
if result[0].get('groupname') == 'admin':
return await handler(request)
return json_response({'info': 'Permission denied'}, status=403)
async def get_table_values(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
try:
incoming_data = await request.json()
table_name = incoming_data.get('table_name')
limit = incoming_data.get('limit')
offset = incoming_data.get('offset')
order_by = incoming_data.get('order_by')
asc_desc = incoming_data.get('asc_desc')
conditions = incoming_data.get('conditions') #list of ['%column_name%', '%condition%','%value%', %data_type%]}
if not limit:
limit = 100
if not offset:
offset = 0
assert isinstance(table_name, str)
assert len(table_name) > 0
assert isinstance(order_by, str)
assert len(order_by) > 0
assert isinstance(limit, int)
assert isinstance(offset, int)
if conditions:
assert isinstance(conditions, list)
if not (asc_desc == 'asc' or asc_desc == 'desc'):
asc_desc == 'desc'
except AssertionError:
return json_response({'info': 'Incorrect data'}, status=400)
if asc_desc == 'desc':
order = db.Order.desc
else:
order = db.Order.asc
try:
table_s = db.table(table_name)
table = db.T(table_name)
q = db.Q\
.from_(table_s)\
.select('*')
args_counter = 0
args_list = []
if conditions:
for cond in conditions:
args_counter += 1
if cond[1] == '=':
q = q.where(table.__getattr__(cond[0]) == db.P('$' + str(args_counter)))
elif cond[1] == '<':
q = q.where(table.__getattr__(cond[0]) < db.P('$' + str(args_counter)))
elif cond[1] == '>':
q = q.where(table.__getattr__(cond[0]) > db.P('$' + str(args_counter)))
elif cond[1] == '>=':
q = q.where(table.__getattr__(cond[0]) >= db.P('$' + str(args_counter)))
elif cond[1] == '<=':
q = q.where(table.__getattr__(cond[0]) <= db.P('$' + str(args_counter)))
elif cond[1] == '!=':
q = q.where(table.__getattr__(cond[0]) != db.P('$' + str(args_counter)))
elif cond[1] == 'like':
q = q.where(table.__getattr__(cond[0]).like(db.P('$' + str(args_counter))))
elif cond[1] == 'ilike':
q = q.where(table.__getattr__(cond[0]).ilike(db.P('$' + str(args_counter))))
if cond[3] == 'timestamp':
args_list.append(datetime.datetime.fromisoformat(cond[2]))
else:
args_list.append(cond[2])
args_counter += 1
q = q.orderby(table.__getattr__(order_by), order=order)\
.limit('$' + str(args_counter)).offset('$' + str(args_counter + 1))
args_list.append(limit)
args_list.append(offset)
result = await db.fetch(q, args_list)
result_list = await normalize_result(result)
return json_response({'info': 'Columns list', 'data': result_list})
except UndefinedTableError:
return json_response({'info': 'Table not found'}, status=404)
async def set_record_media(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
http_session = request.app['http_session']
async def read_incoming_data(incoming_data):
media_type = incoming_data.get('media_type')
media_data = incoming_data.get('media_data')
media_description = incoming_data.get('media_description')
assert isinstance(media_type, str)
assert isinstance(media_data, dict)
return [media_type, media_data, media_description]
session = await get_session(request)
username = session.get('user').get('username')
incoming_data = await request.json()
recordid = incoming_data.get('recordid')
if not isinstance(recordid, str):
return json_response({'info': 'Incorrect data'}, status=400)
q = db\
.from_table('records')\
.select('recordid', 'username', 'post_date', 'record_headline', 'record_text')\
.where(db.T('records').recordid == db.P('$1'))\
.where(db.T('records').active == True)
result = await db.fetch(q, [
recordid,
])
if len(result) != 1:
return json_response({
'info': 'Not found',
}, status=404)
if result[0].get('username') != username:
return json_response({
'info': 'Permission denied',
}, status=403)
try:
media_type, media_data, media_description = await read_incoming_data(
incoming_data)
except AssertionError:
return json_response({'info': 'Incorrect data'}, status=400)
media_data_dict = {}
if media_type == 'embedded_video':
url = media_data.get('url')
if isinstance(url, str):
if re.search(r'^https?\:\/\/([\w\.]*)\/\S*$', url):
try:
async with http_session.get(url) as resp:
if resp.status == 200:
if not resp.headers.get('X-Frame-Options'):
media_data_dict['url'] = url
mediaid = mediaid_gen()
inserting_data = {
'mediaid': mediaid,
'media_type': media_type,
'media_data': ujson.dumps(media_data_dict),
'username': username
}
if media_description:
inserting_data[
'media_description'] = media_description
await db.insert_to_table(
'media', inserting_data)
await db.insert_to_table(
'records_media', {
'recordid': recordid,
'mediaid': mediaid
},
ignore_conflict=['recordid', 'mediaid'])
return json_response({'info': 'Created'})
except:
return json_response({'info': 'Incorrect data'},
status=400)
return json_response({'info': 'Incorrect data'}, status=400)
async def update_table_values(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
async def fetch_columns(table_name):
information_schema = db.S('information_schema')
columns = db.T('columns')
q = db.Q\
.from_(information_schema.columns)\
.select('*')\
.where(columns.table_schema == db.P('$1'))\
.where(columns.table_name == db.P('$2'))
result = await db.fetch(q, [cfg.SCHEMA_NAME, table_name])
if len(result) > 0:
return await normalize_result(result)
return None
incoming_data = await request.json()
value = incoming_data.get('value')
conditions = incoming_data.get('conditions')
table_name = incoming_data.get('table_name')
try:
assert isinstance(value, dict)
assert isinstance(conditions, dict)
assert isinstance(table_name, str)
except AssertionError:
return json_response({'info': 'Incorrect data'}, status=400)
columns = await fetch_columns(table_name)
conditions_filter = {}
for column in columns:
# if column['data_type'] == 'integer':
# if conditions[column['column_name']]:
# conditions_filter[column['column_name']] = conditions[column['column_name']]
# elif column['data_type'] == 'character varying':
# if conditions[column['column_name']]:
# conditions_filter[column['column_name']] = conditions[column['column_name']]
# elif column['data_type'] == 'boolean':
# if conditions[column['column_name']] is True:
# conditions_filter[column['column_name']] = True
# if conditions[column['column_name']] is False:
# conditions_filter[column['column_name']] = False
# try:
if column['ordinal_position'] == 1:
conditions_filter[column['column_name']] = conditions[column['column_name']]
updating_table = db.table(table_name)
print(value, conditions_filter, table_name)
value_key = None
for key in value.keys():
value_key = key
break
args = [value[value_key],]
q = db.Q\
.update(updating_table).set(updating_table.__getattr__(value_key), db.P('$1'))
args_counter = 1
for key in conditions_filter.keys():
args_counter += 1
q = q.where(updating_table.__getattr__(key) == db.P('$' + str(args_counter)))
args.append(conditions_filter[key])
res = await db.execute(q, args)
return json_response({'info': 'Updated', 'data': str(res)})
async def create_subscription(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
session = await get_session(request)
async def read_incoming_data():
user_info = session.get('user')
username_self = user_info.get('username')
request_data = await request.json()
username_sub = request_data.get('username')
tags_sub = request_data.get('tags')
subgroupname = request_data.get('name')
username_sub_lower, tags_sub_lower = None, None
if username_sub:
username_sub_lower = username_sub.lower()
if tags_sub:
tags_sub_lower = [t.lower() for t in tags_sub]
return [username_self, username_sub_lower, tags_sub_lower, subgroupname]
async def verify_incoming_data(username_sub, tags_sub, subgroupname):
try:
assert username_sub or tags_sub
if username_sub:
assert isinstance(username_sub, str)
q = db\
.from_table('accounts')\
.select('username')\
.where(db.T('accounts').username == db.P('$1'))
res = await db.fetch(q, [username_sub, ])
assert len(res) == 1
if tags_sub:
assert isinstance(tags_sub, list)
for tag in tags_sub:
assert isinstance(tag, str)
await db.insert_to_table('tags', {'tagname': tag}, ignore_conflict=['tagname',])
if subgroupname:
assert isinstance(subgroupname, str)
return True
except AssertionError:
return False
async def write_data_to_db(username_self ,username_sub, tags_sub, subgroupname):
subgroupid = subid_gen()
subscriptions_groups_data = {'subgroupid': subgroupid,'username': username_self}
if subgroupname:
subscriptions_groups_data['subgroupname'] = subgroupname
await db.insert_to_table('subscriptions_groups', subscriptions_groups_data)
if username_sub:
await db.insert_to_table(
'accounts_subscriptions', {'subgroupid': subgroupid, 'username': username_sub})
if tags_sub:
for tag in tags_sub:
await db.insert_to_table(
'tags_subscriptions', {'subgroupid': subgroupid, 'tagname': tag})
return subgroupid
try:
username_self, username_sub, tags_sub, subgroupname = await read_incoming_data()
except:
return json_response({'info': 'Incorrect data'}, status=400)
if not await verify_incoming_data(username_sub, tags_sub, subgroupname):
return json_response({'info': 'Incorrect data'}, status=400)
subgroupid = await write_data_to_db(username_self ,username_sub, tags_sub, subgroupname)
return json_response({'info': 'Created', 'data': {'subgroupid': subgroupid}})
async def get_subscriptions_records(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
def generate_query_to_search_records(rec_username, rec_tags, db=None, arg_counter=0, args=[]):
records_table = db.T('records')
records_tags_table = db.table('records_tags')
q = db\
.from_table('records')\
.select('recordid', 'username', 'post_date', 'record_headline', 'record_text')\
.distinct()
if rec_tags and isinstance(rec_tags, list):
tags_counter = 0
for tag in rec_tags:
if not isinstance(tag, str):
raise IncorrectDataException
tags_counter += 1
q = q.join(records_tags_table.as_('tag' + str(tags_counter))).on_field('recordid')
tags_counter = 0
for tag in rec_tags:
tags_counter += 1
arg_counter += 1
q = q.where(records_tags_table.as_('tag' + str(tags_counter)).tagname.ilike(db.P('$' + str(arg_counter))))
args.append(tag)
if rec_username:
if not isinstance(rec_username, str):
raise IncorrectDataException
arg_counter += 1
q = q.where(records_table.username.ilike(db.P('$' + str(arg_counter))))
args.append(rec_username)
# q = q.orderby('post_date', order=db.Order.desc)
return (q, args, arg_counter)
def result_handle(result_raw):
result_list = list(result_raw)
result = []
for record in result_list:
result.append({
'recordid': record.get('recordid'),
'username': record.get('username'),
'post_date': (record.get('post_date')).isoformat(),
'record_headline': record.get('record_headline'),
'record_text': record.get('record_text')})
return result
session = await get_session(request)
username_self = session.get('user').get('username')
subscriptions_self = await make_qurey_to_subscriptions_by_username(username_self, db=db)
q = None
arg_counter = 0
args = []
for sub in subscriptions_self:
if q:
new_q, new_args, new_arg_counter = generate_query_to_search_records(
sub['username'], sub['tags'], db=db, arg_counter=arg_counter, args=[])
q = q + new_q
args.extend(new_args)
arg_counter = new_arg_counter
else:
new_q, new_args, new_arg_counter = generate_query_to_search_records(
sub['username'], sub['tags'], db=db, arg_counter=arg_counter, args=[])
q = new_q
args.extend(new_args)
arg_counter = new_arg_counter
if q:
q = q.orderby('post_date', order=db.Order.desc)
result = result_handle(await db.fetch(q, args))
return json_response({'info': 'Subscriptions', 'data': result})
return json_response({'info': 'Subscriptions', 'data': []})
async def search_records(request):
cfg = request.app['cfg']
db = request.app[cfg.DB_HANDLER]
def db_request_generate(rec_username, rec_tags, search_terms):
if not (rec_username or rec_tags or search_terms):
raise IncorrectDataException
arg_counter = 0
args = []
records_table = db.T('records')
records_tags_table = db.table('records_tags')
q = db\
.from_table('records')\
.select('recordid', 'username', 'post_date', 'record_headline', 'record_text')\
.distinct()
if rec_tags and isinstance(rec_tags, list):
tags_counter = 0
for tag in rec_tags:
if not isinstance(tag, str):
raise IncorrectDataException
tags_counter += 1
q = q.join(records_tags_table.as_(
'tag' + str(tags_counter))).on_field('recordid')
tags_counter = 0
for tag in rec_tags:
tags_counter += 1
arg_counter += 1
q = q.where(
records_tags_table.as_('tag' +
str(tags_counter)).tagname.ilike(
db.P('$' + str(arg_counter))))
args.append(tag)
if rec_username:
if not isinstance(rec_username, str):
raise IncorrectDataException
arg_counter += 1
q = q.where(
records_table.username.ilike(db.P('$' + str(arg_counter))))
args.append(rec_username)
if search_terms:
if not isinstance(search_terms, str):
raise IncorrectDataException
if len(search_terms) < 1:
raise IncorrectDataException
search_iter = re.finditer(r'\W?(\w+)\W?', search_terms)
for term in search_iter:
arg_counter += 1
q = q.where(
records_table.record_text.ilike(
db.P('$' + str(arg_counter)))
| records_table.record_headline.ilike(
db.P('$' + str(arg_counter))))
args.append(f'%{term.group(1).lower()}%')
q = q.where(records_table.active == True)
q = q.orderby('post_date', order=db.Order.desc)
return (q, args)
def result_handle(result_raw):
result_list = list(result_raw)
result = []
for record in result_list:
result.append({
'recordid': record.get('recordid'),
'username': record.get('username'),
'post_date': (record.get('post_date')).isoformat(),
'record_headline': record.get('record_headline'),
'record_text': record.get('record_text')
})
return result
incoming_data = await request.json()
rec_username = incoming_data.get('username')
rec_tags = incoming_data.get('tags')
search_terms = incoming_data.get('search_terms')
print(rec_username, rec_tags, search_terms)
try:
q, args = db_request_generate(rec_username, rec_tags, search_terms)
print(q, args)
except IncorrectDataException:
return json_response({'info': 'Incorrect data'}, status=400)
result = await db.fetch(q, args)
return json_response({'info': 'Results', 'data': result_handle(result)})
