def test_parse_foreignkey():
with pytest.raises(InvalidQueryConditionValue):
QueryInfo.from_json(User, {'$fks': {'topic': {'id.eq': '$user.id'}}})
q = QueryInfo.from_json(
User, {
'$fks': {
'topic': {
'$select': 'id, title, user_id',
'user_id.eq': '$user:id'
}
}
})
assert 'topic' in q.foreign_keys
t = q.foreign_keys['topic']
assert t.from_table == Topic
assert t.select == [Topic.id, Topic.title, Topic.user_id]
assert len(t.conditions.items) == 1
c = t.conditions.items[0]
assert c.column == Topic.user_id
assert c.op == QUERY_OP_COMPARE.EQ
assert c.value == User.id
async def test_crud_perm_query_disallow_and_allow_simple():
db, MUsers, MTopics, MTopics2 = crud_db_init()
permission = {
'visitor': RoleDefine({
User: TablePerm({
User.id: {A.READ},
User.password: {A.READ}
})
}, match=None),
'user': RoleDefine({
User: TablePerm({
User.id: {A.READ, A.QUERY},
User.password: {A.READ}
})
}),
}
c = PeeweeCrud(permission, {User: MUsers}, db)
info = QueryInfo.from_json(User, {
'id.eq': 5,
})
ret = await c.get_list_with_perm(info, perm=PermInfo(True, None, permission['visitor']))
assert len(ret) == 5
# 注意这里，权限过滤会改变info内部的样子
info = QueryInfo.from_json(User, {
'id.eq': 5,
})
ret = await c.get_list_with_perm(info, perm=PermInfo(True, None, permission['user']))
assert len(ret) == 1
async def test_crud_perm_write():
db, MUsers, MTopics, MTopics2 = crud_db_init()
permission = {
'visitor': RoleDefine({
User: TablePerm({
User.id: {A.READ, A.QUERY},
User.nickname: {A.READ},
User.password: {A.READ}
})
}, match=None),
'user': RoleDefine({
User: TablePerm({
User.id: {A.READ, A.QUERY},
User.nickname: {A.READ, A.UPDATE},
User.password: {A.READ}
})
}, match=None)
}
c = PeeweeCrud(permission, {User: MUsers}, db)
# perm visitor
with pytest.raises(InvalidQueryValue):
ret = await c.update_with_perm(
QueryInfo.from_json(User, {'id.eq': 5}),
ValuesToWrite({'nickname': 'aaa'}, User).bind(),
perm=PermInfo(True, None, permission['visitor'])
)
assert len(ret) == 0 # all filtered
# not check
ret = await c.update_with_perm(
QueryInfo.from_json(User, {'id.eq': 5}),
ValuesToWrite({'nickname': 'aaa'}, User).bind(),
perm=PermInfo(False, None, permission['visitor'])
)
assert len(ret) == 1
assert ret[0] == 5
# perm user
ret = await c.update_with_perm(
QueryInfo.from_json(User, {'id.eq': 5}),
ValuesToWrite({'nickname': 'ccc'}, User).bind(),
perm=PermInfo(True, None, permission['user'])
)
assert len(ret) == 1
assert ret[0] == 5
# returning
ret = await c.update_with_perm(
QueryInfo.from_json(User, {'id.eq': 5}),
ValuesToWrite({'nickname': 'ccc'}, User).bind(),
perm=PermInfo(True, None, permission['user']),
returning=True
)
assert len(ret) == 1
assert isinstance(ret[0], QueryResultRow)
async def test_bytes_read():
db, c, TestModel = crud_db_init()
info = QueryInfo(ATest)
info.select = [ATest.token]
info.conditions = QueryConditions([])
ret = await c.get_list(info)
assert ret[0].to_dict()['token'] == b'abcd'
assert len(ret) == TestModel.select().count()
def test_condition_simple2_from_http():
q = QueryInfo.from_json(User, {'test.eq': '"111"'}, from_http_query=True)
cond = q.conditions.items[0]
assert cond.column == User.test
assert cond.op == QUERY_OP_COMPARE.EQ
assert cond.value == 111
q = QueryInfo.from_json(User, {'test.eq': '222'}, from_http_query=True)
cond = q.conditions.items[0]
assert cond.column == User.test
assert cond.op == QUERY_OP_COMPARE.EQ
assert cond.value == 222
async def solve(ret_lst, main_table, fk_queries, depth=0):
if fk_queries is None:
return
if depth == 0:
pk_items = [x.id for x in ret_lst]
else:
pk_items = [x.raw_data[0] for x in ret_lst]
for raw_name, query in fk_queries.items():
query: QueryInfo
limit = -1 if raw_name.endswith('[]') else 1
# 上级ID，数据，查询条件
q = QueryInfo(main_table, [query.from_table.id, *query.select])
q.conditions = QueryConditions([
ConditionExpr(main_table.id, QUERY_OP_RELATION.IN,
pk_items)
])
q.join = [
QueryJoinInfo(query.from_table,
query.conditions,
limit=limit)
]
elist = []
for x in await self.get_list_with_perm(q, perm=perm):
x.base = query.from_table
elist.append(x)
extra: Dict[Any, Union[List, QueryResultRow]] = {}
if limit != 1:
for x in elist:
extra.setdefault(x.id, [])
extra[x.id].append(x)
else:
for x in elist:
extra[x.id] = x
if depth == 0:
for i in ret_lst:
i.extra[raw_name] = extra.get(i.id)
else:
for i in ret_lst:
i.extra[raw_name] = extra.get(i.raw_data[0])
if query.foreign_keys:
await solve(elist, query.from_table, query.foreign_keys,
depth + 1)
async def test_crud_simple():
db, MUsers, MTopics, MTopics2 = crud_db_init()
info = QueryInfo(User)
info.select = [User.id, User.username, User.password, User.nickname]
info.conditions = QueryConditions([])
c = PeeweeCrud(None, {
User: '******',
Topic: 'topic',
}, db)
ret = await c.get_list(info)
assert len(ret) == MUsers.select().count()
for i in ret:
assert isinstance(i, QueryResultRow)
assert isinstance(i.id, int)
info.conditions.items.append(ConditionExpr(User.id, QUERY_OP_COMPARE.EQ,
2))
ret = await c.get_list(info)
assert len(ret) == 1
info.foreign_keys = {
'topic[]':
QueryInfo(Topic, [Topic.id, Topic.title, Topic.user_id],
conditions=QueryConditions([
ConditionExpr(Topic.user_id, QUERY_OP_RELATION.IN,
[2, 3]),
])),
'topic':
QueryInfo(Topic, [Topic.id, Topic.title, Topic.user_id],
conditions=QueryConditions([
ConditionExpr(Topic.user_id, QUERY_OP_RELATION.IN,
[2, 3]),
]),
foreign_keys={
'user':
QueryInfo(User, [User.id, User.nickname],
conditions=QueryConditions([
ConditionExpr(Topic.user_id,
QUERY_OP_COMPARE.EQ,
User.id),
]))
}),
}
ret = await c.get_list_with_foreign_keys(info)
assert ret[0].id == 2
d = ret[0].to_dict()
assert d['$extra']['topic']['id'] == 3
assert d['$extra']['topic']['title'] == 'test3'
assert d['$extra']['topic']['$extra']['user']
assert d['$extra']['topic']['$extra']['user']['id'] == 2
assert len(d['$extra']['topic[]']) == 2
def test_values_bug_2():
qi = QueryInfo.from_json(
User, {'values.contains': json.dumps(["5ef99253000000041d4164ef"])},
from_http_query=True)
assert qi.conditions.items[0].value[0] == binascii.unhexlify(
'5ef99253000000041d4164ef')
def test_condition_logic_failed_1(key):
q = QueryInfo.from_json(User,
{'$' + key: {
'nickname.eq': 'test',
'test.lt': 5
}})
assert len(q.conditions.items) == 0
def test_parse_order_by():
q = QueryInfo.from_json(User, {'$order-by': 'id, username.desc, test'})
assert q.order_by == [
QueryOrder(User.id),
QueryOrder(User.username, 'desc'),
QueryOrder(User.test)
]
def test_condition_simple_from_http():
q = QueryInfo.from_json(User, {'nickname.eq': '"test"'},
from_http_query=True)
cond = q.conditions.items[0]
assert cond.column == User.nickname
assert cond.op == QUERY_OP_COMPARE.EQ
assert cond.value == 'test'
def test_condition_is():
q = QueryInfo.from_table_raw(User, where=[
f(User.nickname).is_(None)
])
cond = q.conditions.items[0]
assert cond.op == QUERY_OP_RELATION.IS
async def test_crud_array_extend():
c, db, TableOneModel = crud_db_init()
await c.update(QueryInfo.from_table_raw(TableOne),
values=ValuesToWrite({'arr.array_extend': ['aa', 'bb']},
table=TableOne,
try_parse=True))
assert c.last_sql == 'UPDATE "table_one" SET "arr"="arr"||? WHERE "id" IN (?)'
async def delete(self, info: QueryInfo, *, _perm=None) -> IDList:
model = self.mapping2model[info.from_table]
when_before_delete, when_complete = [], []
await info.from_table.on_delete(info, when_before_delete,
when_complete, _perm)
qi = info.clone()
qi.select = []
lst = await self.get_list(qi, _perm=_perm)
# 选择项
id_lst = [x.id for x in lst]
for i in when_before_delete:
await i(id_lst)
if id_lst:
phg = self.get_placeholder_generator()
sql = Query().from_(model).delete().where(
model.id.isin(phg.next(id_lst)))
await self.execute_sql(sql.get_sql(), phg)
for i in when_complete:
await i()
return id_lst
def test_parse_negated():
q = QueryInfo.from_json(User, {'$not': {
'id.eq': 1,
}})
conds1 = q.conditions
conds2 = QueryConditions([~(ConditionLogicExpr('and', [f(User.id) == 1]))])
assert check_same_expr(conds1, conds2)
def test_hexstr_in():
q = QueryInfo.from_json(User, {
'$select': 'id, nickname, token',
'token.in': json.dumps(['aabb', '22'])
},
from_http_query=True)
assert q.conditions.items[0].value[0] == b'\xaa\xbb'
async def test_crud_array_contains_any():
c, db, TableOneModel = crud_db_init()
await c.get_list(
QueryInfo.from_json(TableOne, {
'$select': 'id',
'arr.contains_any': ['a']
}))
assert c.last_sql == 'SELECT "id","id" FROM "table_one" WHERE "arr"&&? LIMIT 20'
async def test_crud_array_prune_distinct():
c, db, TableOneModel = crud_db_init()
await c.update(QueryInfo.from_table_raw(TableOne),
values=ValuesToWrite(
{'arr.array_prune_distinct': ['aa', 'bb']},
table=TableOne,
try_parse=True))
assert c.last_sql == 'UPDATE "table_one" SET "arr"=array(SELECT unnest("arr") EXCEPT SELECT unnest(?)) WHERE "id" IN (?)'
async def test_bytes_query_memoryview():
db, c, TestModel = crud_db_init()
info = QueryInfo.from_json(ATest, {'token.eq': memoryview(b'abcd')})
ret = await c.get_list(info)
assert ret[0].to_dict()['token'] == b'abcd'
assert len(ret) == TestModel.select().where(
TestModel.token == b'abcd').count()
async def test_crud_read_by_prefix():
db, MUsers, MTopics, MTopics2 = crud_db_init()
c = PeeweeCrud(None, {User: MUsers}, db)
n = QueryInfo.from_json(User, {'username.prefix': 'test4'})
ret = await c.get_list(n)
assert ret[0].to_dict()['username'] == 'test4'
def test_dsl_condition_simple():
q = QueryInfo.from_table_raw(User, where=[
User.nickname == 'test'
])
cond = q.conditions.items[0]
assert cond.column == User.nickname
assert cond.op == QUERY_OP_COMPARE.EQ
assert cond.value == 'test'
def test_condition_simple2():
q = QueryInfo.from_json(User, {'nickname.eq': 'test', 'test.lt': 5})
cond = q.conditions.items[0]
assert cond.column == User.nickname
assert cond.op == QUERY_OP_COMPARE.EQ
assert cond.value == 'test'
cond = q.conditions.items[1]
assert cond.column == User.test
assert cond.op == QUERY_OP_COMPARE.LT
assert cond.value == 5
async def _solve_query(info: QueryInfo, perm: PermInfo):
if perm.is_check:
allow_query = perm.role.get_perm_avail(info.from_table, A.QUERY)
allow_read = perm.role.get_perm_avail(info.from_table, A.READ)
def sub_solve_items(items):
if items:
r = [solve_condition(x) for x in items if x is not None]
# solve_condition 返回值仍有 None 的可能
return [x for x in r if x is not None]
return []
def solve_condition(c):
if isinstance(c, QueryConditions):
return QueryConditions(sub_solve_items(c.items))
elif isinstance(c, ConditionLogicExpr):
items = sub_solve_items(c.items)
if items:
return ConditionLogicExpr(c.type, items)
return None
elif isinstance(c, ConditionExpr):
if c.column not in allow_query:
# permission
return None
if isinstance(c.value, RecordMappingField):
if c.value not in allow_query:
# permission
return None
return c
elif isinstance(c, UnaryExpr):
return c
select_new = [x for x in info.select if x in allow_read]
info.select = select_new
info.conditions = solve_condition(info.conditions)
return info
async def test_bytes_query_from_http():
db, c, TestModel = crud_db_init()
info = QueryInfo.from_json(ATest, {'token.eq': '"eeff"'},
from_http_query=True)
ret = await c.get_list(info)
assert ret[0].to_dict()['token'] == b'\xee\xff'
assert len(ret) == TestModel.select().where(
TestModel.token == b'\xee\xff').count()
async def test_crud_is_null():
c, db, TableOneModel = crud_db_init()
await c.get_list(
QueryInfo.from_json(TableOne, {
'$select': 'id',
'arr.is': 'null',
},
from_http_query=True))
assert c.last_sql == 'SELECT "id","id" FROM "table_one" WHERE "arr" IS ? LIMIT 20'
async def test_crud_read_with_count():
db, MUsers, MTopics, MTopics2 = crud_db_init()
c = PeeweeCrud(None, {
Topic: MTopics,
}, db)
i = QueryInfo.from_json(Topic, {})
i.limit = 1
ret = await c.get_list(i, with_count=True)
assert len(ret) == 1
assert ret.rows_count == MTopics.select().count()
async def test_crud_read_3():
db, MUsers, MTopics, MTopics2 = crud_db_init()
c = PeeweeCrud(None, {Topic: MTopics}, db)
q = QueryInfo.from_json(Topic, {'$not': {'id.eq': 1}})
ret = await c.get_list(q)
v1 = {x.id for x in ret}
v2 = {x.id for x in MTopics.select().where(MTopics.id != 1)}
assert v1 == v2
async def test_crud_read_2():
db, MUsers, MTopics, MTopics2 = crud_db_init()
c = PeeweeCrud(None, {
User: MUsers,
Topic: MTopics,
}, db)
n0 = QueryInfo.from_json(Topic, {'id.eq': 1})
n = n0.clone()
ret = await c.get_list(n)
assert len(ret) == 1
def test_condition_logic_1(key, op_name):
q = QueryInfo.from_json(User,
{'$' + key: {
'nickname.eq': 'test',
'test.lt': 5
}})
cond = q.conditions.items[0]
assert isinstance(cond, ConditionLogicExpr)
assert cond.type == op_name
cond1 = cond.items[0]
cond2 = cond.items[1]
assert cond1.op == QUERY_OP_COMPARE.EQ
assert cond2.op == QUERY_OP_COMPARE.LT
async def test_crud_delete_success():
db, MUsers, MTopics, MTopics2 = crud_db_init()
c = PeeweeCrud(None, {
User: MUsers,
Topic: MTopics,
}, db)
assert MTopics.select().where(MTopics.id == 1).count() == 1
ret = await c.delete(QueryInfo.from_json(Topic, {'id.eq': 1}))
assert len(ret) == 1
assert MTopics.select().where(MTopics.id == 1).count() == 0