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)})