def test_query_ex_filter(): sqi = SQLQueryInfo() sqi.select = sqi.parse_select('username, nickname, password') sqi.parse_then_add_condition('username', 'like', 'b') sqi.parse_then_add_condition('nickname', 'like', 'b') sqi.check_query_permission_full(None, 'user', ab, None) assert sqi.conditions == [['nickname', SQL_OP.LIKE, 'b']]
async def check(data, records): for column, fkvalues_lst in data.items(): for fkvalues in fkvalues_lst: # got nothing, skip if not records: continue pks = [] all_ni = True for i in records: val = i.get(column, NotImplemented) if val != NotImplemented: all_ni = False pks.append(val) if all_ni: logger.debug( "load foreign key failed, do you have read permission to the column %r?" % column) continue # 3. query foreign keys vcls = self.app.tables[fkvalues['table']] v = vcls(self.app, self._request) # fake view await v._prepare() info2 = SQLQueryInfo() info2.set_select(ALL_COLUMNS) info2.add_condition(PRIMARY_KEY, SQL_OP.IN, pks) info2.bind(v) # ability = vcls.permission.request_role(self.current_user, fkvalues['role']) # info2.check_query_permission_full(self.current_user, fktable, ability) try: fk_records, count = await v._sql.select_page(info2, size=-1) except RecordNotFound: # 外键没有找到值,也许全部都是null,这很常见 continue if not fk_records: continue fk_records = list(fk_records) await v.check_records_permission(info2, fk_records) fk_dict = {} for i in fk_records: # 主键: 数据 fk_dict[i[vcls.primary_key]] = i column_to_set = fkvalues.get('as', column) or column for _, record in enumerate(records): k = record.get(column, NotImplemented) if k in fk_dict: record[column_to_set] = fk_dict[k] if fkvalues['loadfk']: await check(fkvalues['loadfk'], fk_records)
def test_query_condition_add2(): """ 测试添加多个条件 """ ab2 = Ability({}, based_on=ab) ab2.add_query_condition('user', [ ['username', 'like', '1%'], ['nickname', 'like', '1%'], ]) sqi = SQLQueryInfo() sqi.select = sqi.parse_select('username, nickname, password') sqi.parse_then_add_condition('username', '=', 'b') sqi.check_query_permission_full(None, 'user', ab2, None) assert sqi.conditions == [['username', SQL_OP.EQ, 'b'], ['username', SQL_OP.LIKE, '1%'], ['nickname', SQL_OP.LIKE, '1%']]
def test_query_condition_add1(): """ 测试添加单个条件 :return: """ ab1 = Ability({}, based_on=ab) ab1.add_query_condition('user', ['phone', '>=', '123456']) sqi = SQLQueryInfo() sqi.select = sqi.parse_select('username, nickname, password') sqi.parse_then_add_condition('username', '=', 'b') assert sqi.conditions[-1] == ['username', SQL_OP.EQ, 'b'] sqi.check_query_permission_full(None, 'user', ab1, None) assert sqi.conditions[-1] == ['phone', SQL_OP.GE, '123456']
async def test_condition_bind_error_column_not_found(): sqi = SQLQueryInfo() sqi.parse_then_add_condition('name1', '=', '1') view: PeeweeView = await make_mocked_view_instance(app, ATestView, 'GET', '/api/test1') with pytest.raises(ColumnNotFound) as e: sqi.bind(view) assert 'name1' in e.value.args[0]
async def test_condition_bind_error_convert_failed(): sqi = SQLQueryInfo() sqi.parse_then_add_condition('name', '=', {}) view: PeeweeView = await make_mocked_view_instance(app, ATestView, 'GET', '/api/test1') with pytest.raises(InvalidParams) as e: sqi.bind(view) assert 'name' in e.value.args[0] assert "Couldn't interpret" in str(e.value)
async def get(self): """ 获取单项记录接口,查询规则参考 https://fy0.github.io/slim/#/quickstart/query_and_modify """ self.current_interface = InnerInterfaceName.GET with ErrorCatchContext(self): info = SQLQueryInfo(self.params, view=self) await self._call_handle(self.before_query, info) record = await self._sql.select_one(info) if record: records = [record] # , exception_cls=RecordNotFound await self.check_records_permission(info, records) data_dict = await self.load_fk(info, records) self.finish(RETCODE.SUCCESS, data_dict[0]) else: self.finish(RETCODE.NOT_FOUND)
async def set(self): """ 更新数据接口 查询规则参考 https://fy0.github.io/slim/#/quickstart/query_and_modify 赋值规则参考 https://fy0.github.io/slim/#/quickstart/query_and_modify?id=修改新建 """ self.current_interface = InnerInterfaceName.SET with ErrorCatchContext(self): info = SQLQueryInfo(self.params, self) await self._call_handle(self.before_query, info) records, count = await self._sql.select_page(info, size=self.bulk_num()) if records: # 确保 before_update 时得到list records = list(records) values = SQLValuesToWrite(await self.post_data()) values.bind(self, A.WRITE, records) await self._call_handle(self.before_update, values, records) # 如果 before_update 之后,不再有values,那么抛出invalid_postdata if len(values) == 0: raise InvalidPostData("No value to set for table: %s" % self.table_name) if logger.isEnabledFor(logging.DEBUG): logger.debug('update record(s): %s' % values) # 注:此处returning为true是因为后续要检查数据的权限,和前端要求无关 new_records = await self._sql.update(records, values, returning=True) await self.check_records_permission(None, new_records) await self._call_handle(self.after_update, values, records, new_records) if await self.is_returning(): self.finish(RETCODE.SUCCESS, new_records) else: self.finish(RETCODE.SUCCESS, len(new_records)) else: self.finish(RETCODE.NOT_FOUND)
async def list(self): """ 获取分页记录接口,查询规则参考 https://fy0.github.io/slim/#/quickstart/query_and_modify """ self.current_interface = InnerInterfaceName.LIST with ErrorCatchContext(self): page, size = self._get_list_page_and_size() info = SQLQueryInfo(self.params, view=self) await self._call_handle(self.before_query, info) records, count = await self._sql.select_page(info, page, size) await self.check_records_permission(info, records) if size == -1: size = count if count != 0 else 1 pg = pagination_calc(count, size, page) records = await self.load_fk(info, records) pg["items"] = records self.finish(RETCODE.SUCCESS, pg)
async def delete(self): """ 删除记录接口 查询规则参考 https://fy0.github.io/slim/#/quickstart/query_and_modify 赋值规则参考 https://fy0.github.io/slim/#/quickstart/query_and_modify?id=修改新建 """ self.current_interface = InnerInterfaceName.DELETE with ErrorCatchContext(self): info = SQLQueryInfo(self.params, self) await self._call_handle(self.before_query, info) records, count = await self._sql.select_page(info, size=self.bulk_num()) if records: records = list(records) user = self.current_user if self.can_get_user else None logger.debug('request permission as %r: [%s] of table %r' % (self.ability.role, A.DELETE, self.table_name)) for record in records: valid = self.ability.can_with_record( user, A.DELETE, record, available=record.keys()) if len(valid) == len(record.keys()): logger.debug("request permission successed as %r: %r" % (self.ability.role, list(record.keys()))) else: logger.debug( "request permission failed as %r. valid / requested: %r, %r" % (self.ability.role, valid, list(record.keys()))) return self.finish(RETCODE.PERMISSION_DENIED) await self._call_handle(self.before_delete, records) num = await self._sql.delete(records) await self._call_handle(self.after_delete, records) self.finish(RETCODE.SUCCESS, num) else: self.finish(RETCODE.NOT_FOUND)
async def test_pg_array_contains_ok2(): view = ATestView(app) sqi = SQLQueryInfo() sqi.add_condition('name', SQL_OP.CONTAINS, [b'aa', b'bb']) sqi.bind(view)
async def test_pg_array_contains_bad_type3(): view = ATestView(app) sqi = SQLQueryInfo() with pytest.raises(InvalidParams): sqi.add_condition('name', SQL_OP.CONTAINS, [b'aa', 11]) sqi.bind(view)
def test_query_add_func(): ab1 = Ability({}, based_on=ab) def func1(ability: Ability, user, query: 'SQLQueryInfo', view: "AbstractSQLView"): query.add_condition('nickname', '=', 'aa') ab1.add_query_condition('user', func=func1) sqi = SQLQueryInfo() sqi.select = sqi.parse_select('username, nickname, password') sqi.parse_then_add_condition('username', '=', 'b') sqi.check_query_permission_full(None, 'user', ab1, None) assert sqi.conditions == [['username', SQL_OP.EQ, 'b'], ['nickname', SQL_OP.EQ, 'aa'],] ab2 = Ability({}, based_on=ab) def func2(ability: Ability, user, query: 'SQLQueryInfo'): query.add_condition('nickname', '=', 'aa') ab2.add_query_condition('user', func=func2) sqi = SQLQueryInfo() sqi.select = sqi.parse_select('username, nickname, password') sqi.parse_then_add_condition('username', '=', 'b') sqi.check_query_permission_full(None, 'user', ab2, None) assert sqi.conditions == [['username', SQL_OP.EQ, 'b'], ['nickname', SQL_OP.EQ, 'aa'],]
async def test_very_simple_condition(): sqi = SQLQueryInfo() sqi.parse_then_add_condition('a', '=', 'b') assert sqi.conditions[0] == ['a', SQL_OP.EQ, 'b'] sqi = SQLQueryInfo() sqi.parse_then_add_condition('a', 'like', 'b') assert sqi.conditions[0] == ['a', SQL_OP.LIKE, 'b'] for i in SQL_OP.ALL: sqi = SQLQueryInfo() if i in SQL_OP.IN.value or i in SQL_OP.CONTAINS.value or i in SQL_OP.CONTAINS_ANY.value: sqi.parse_then_add_condition('a', i, '[1,2]') assert sqi.conditions[0] == ['a', SQL_OP.txt2op[i], [1, 2]] else: sqi.parse_then_add_condition('a', i, 'b') assert sqi.conditions[0] == ['a', SQL_OP.txt2op[i], 'b']
async def test_select(): assert SQLQueryInfo.parse_select('aa') == {'aa'} assert SQLQueryInfo.parse_select('aa,') == {'aa'} assert SQLQueryInfo.parse_select('aa,bbb') == {'aa', 'bbb'} assert SQLQueryInfo.parse_select('aa, bbb') == {'aa', 'bbb'} assert SQLQueryInfo.parse_select('aa, \nbbb') == {'aa', 'bbb'} assert SQLQueryInfo.parse_select('*') == ALL_COLUMNS try: SQLQueryInfo.parse_select(',') assert False except Exception as e: assert isinstance(e, InvalidParams) try: SQLQueryInfo.parse_select(',,,') assert False except Exception as e: assert isinstance(e, InvalidParams) sqi = SQLQueryInfo() try: sqi.set_select([1, 2, '3']) assert False except Exception as e: assert isinstance(e, AssertionError) try: sqi.set_select(None) assert False except Exception as e: assert isinstance(e, InvalidParams) assert sqi.set_select(ALL_COLUMNS) is None assert sqi.set_select(['1', '2', '3']) is None assert sqi.set_select({'1', '2', '3'}) is None
async def test_order(): assert SQLQueryInfo.parse_order('a') == [] assert SQLQueryInfo.parse_order('a,b,c') == [] assert SQLQueryInfo.parse_order('a, b, c') == [] assert SQLQueryInfo.parse_order('a, b, c') == [] assert SQLQueryInfo.parse_order('a, b,') == [] assert SQLQueryInfo.parse_order('a.asc') == [SQLQueryOrder('a', 'asc')] assert SQLQueryInfo.parse_order('a.AsC') == [SQLQueryOrder('a', 'asc')] assert SQLQueryInfo.parse_order('a.asc, b,') == [SQLQueryOrder('a', 'asc')] assert SQLQueryInfo.parse_order('a.asc,b,c.desc') == [ SQLQueryOrder('a', 'asc'), SQLQueryOrder('c', 'desc') ] try: SQLQueryInfo.parse_order('a.a.a') assert False except Exception as e: assert isinstance(e, InvalidParams) try: SQLQueryInfo.parse_order('a.?sc') assert False except Exception as e: assert isinstance(e, InvalidParams) sqi = SQLQueryInfo() sqi.set_orders([]) try: sqi.set_orders([1]) assert False except Exception as e: assert isinstance(e, AssertionError) sqi.set_orders([SQLQueryOrder('A', 'asc')]) assert sqi.orders == [SQLQueryOrder('A', 'asc')]
async def test_condition_bind_error_in_or_not_in_value(): sqi = SQLQueryInfo() with pytest.raises(InvalidParams) as e: sqi.parse_then_add_condition('name', 'in', [1, 2]) assert 'name' in e.value.args[0]
async def test_new(): sqi = SQLQueryInfo()
def test_query_condition_clear_by_check(): """ 查询权限被检查机制清空的情况 :return: """ sqi = SQLQueryInfo() sqi.select = sqi.parse_select('username, nickname, password') sqi.parse_then_add_condition('phone', '=', 'b') sqi.parse_then_add_condition('phone', '>', '100') # # 所有查询条件都被权限机制清空 with pytest.raises(PermissionDenied) as excinfo: sqi.check_query_permission_full(None, 'user', ab, None, ignore_error=True) assert sqi.conditions == [] sqi = SQLQueryInfo() sqi.select = sqi.parse_select('username, nickname, password') sqi.parse_then_add_condition('phone', '=', 'b') sqi.parse_then_add_condition('username', '=', 'b') sqi.check_query_permission_full(None, 'user', ab, None, ignore_error=True) with pytest.raises(PermissionDenied) as excinfo: sqi = SQLQueryInfo() sqi.select = sqi.parse_select('username, nickname, password') sqi.parse_then_add_condition('phone', '=', 'b') sqi.parse_then_add_condition('username', '=', 'b') sqi.check_query_permission_full(None, 'user', ab, None, ignore_error=False)
async def test_condition_bind(): sqi = SQLQueryInfo() sqi.parse_then_add_condition('name', '=', '1') view: PeeweeView = await make_mocked_view_instance(app, ATestView, 'GET', '/api/test1') sqi.bind(view)