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)
