async def test_on_update(conn, pgclean): R = Registry() async def get_user_id(entity): return 2 class OnUpdate(Entity, registry=R, schema="execution"): id: Serial value: String updater_id: Int = Field(on_update=lambda entity: 1) updater_id2: Int = Field(on_update=get_user_id) result = await sync(conn, R) await conn.execute(result) inst = OnUpdate(id=1) await conn.save(inst) inst = await conn.select(Query(OnUpdate).where(OnUpdate.id == 1)).first() assert inst.updater_id is None assert inst.updater_id2 is None inst.value = "X" await conn.save(inst) inst = await conn.select(Query(OnUpdate).where(OnUpdate.id == 1)).first() assert inst.updater_id == 1 assert inst.updater_id2 == 2
async def test_latlng_isu(conn): p = LatLng(location=[47.5135873, 19.0424536]) assert p.location.lat == 47.5135873 assert p.location.lng == 19.0424536 assert await conn.insert(p) is True q = Query().select_from(LatLng).where(LatLng.id == p.id) res = await conn.select(q).first() assert res.location.lat == 47.5135873 assert res.location.lng == 19.0424536 res.location.lat = 47.5187817 res.location.lng = 19.0433738 await conn.insert_or_update(res) is True q = Query().select_from(LatLng).where(LatLng.id == p.id) res = await conn.select(q).first() assert res.location.lat == 47.5187817 assert res.location.lng == 19.0433738 p2 = LatLng(location={"lat": 47.5135873, "lng": 19.0424536}) assert p2.location.lat == 47.5135873 assert p2.location.lng == 19.0424536 assert await conn.insert(p2) is True q = Query().select_from(LatLng).where(LatLng.id == p2.id) res = await conn.select(q).first() assert res.location.lat == 47.5135873 assert res.location.lng == 19.0424536
async def test_exec(conn): user = __User(name="Test User") await conn.save(user) doc_group = DocumentGroup() await conn.save(doc_group) doc = Document(author=user, group=doc_group) await conn.save(doc) user.contract_id = doc.id await conn.save(user) user = await conn.select( Query(__User).where(__User.id == user.id).load(__User, __User.contract) ).first() assert user.name == "Test User" assert user.contract.id == doc.id b = A(id=3) await conn.save(b) a = A(id=1, b=b) await conn.save(a) c = C(id=2, a=a) await conn.save(c) cq = await conn.select(Query(C).load(C, C.a).where(C.id == 2)).first() assert cq.id == 2 assert cq.a.id == 1
async def test_insert_workerx(conn): worker = WorkerX() worker.employee_field = "employee_field: set from workerx" worker.worker_field = "worker_field: set from workerx" worker.workerx_field = "workerx_field: set from workerx" await conn.save(worker) org = Organization() org.employee_id = worker.id await conn.save(org) def test_worker_x_fields(w): assert isinstance(w, WorkerX) assert w.id == worker.id assert w.variant == "workerx" assert w.employee_field == "employee_field: set from workerx" assert w.worker_field == "worker_field: set from workerx" assert w.workerx_field == "workerx_field: set from workerx" w = await conn.select( Query().select_from(WorkerX).where(WorkerX.id == worker.id)).first() test_worker_x_fields(w) w = await conn.select( Query().select_from(Employee).where(WorkerX.id == worker.id)).first() test_worker_x_fields(w) o = await conn.select(Query().select_from(Organization).load( Organization.employee).where(Organization.id == org.id)).first() test_worker_x_fields(o.employee)
async def test_point_isu(conn): p = Point(location=[19.0424536, 47.5135873]) assert p.location.x == 19.0424536 assert p.location.y == 47.5135873 assert await conn.insert(p) is True q = Query().select_from(Point).where(Point.id == p.id) res = await conn.select(q).first() assert res.location.x == 19.0424536 assert res.location.y == 47.5135873 res.location.x = 19.0433738 res.location.y = 47.5187817 await conn.save(res) is True q = Query().select_from(Point).where(Point.id == p.id) res = await conn.select(q).first() assert res.location.x == 19.0433738 assert res.location.y == 47.5187817 p2 = Point(location={"x": 19.0424536, "y": 47.5135873}) assert p2.location.x == 19.0424536 assert p2.location.y == 47.5135873 assert await conn.insert(p2) is True q = Query().select_from(Point).where(Point.id == p2.id) res = await conn.select(q).first() assert res.location.x == 19.0424536 assert res.location.y == 47.5135873
async def test_composite_set_null(conn): await conn.execute("DROP SCHEMA IF EXISTS _private CASCADE") await conn.execute("DROP SCHEMA IF EXISTS execution CASCADE") reg_a = Registry() class CompXY(Entity, registry=reg_a, schema="execution"): x: Int y: Int class Entry(Entity, registry=reg_a, schema="execution"): id: Serial xy: Composite[CompXY] result = await sync(conn, reg_a) await conn.execute(result) entry = Entry(xy={"x": 1, "y": 2}) await conn.save(entry) entry = await conn.select(Query(Entry).where(Entry.id == entry.id)).first() assert entry.xy.x == 1 assert entry.xy.y == 2 entry.xy = None await conn.save(entry) entry = await conn.select(Query(Entry).where(Entry.id == entry.id)).first() assert entry.xy is None
def test_entity_alias(): TEST = User.alias("TEST") q = Query().select_from(TEST).where(TEST.id == 12) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == 'SELECT "TEST"."id", "TEST"."name", "TEST"."email", "TEST"."created_time", "TEST"."address_id" FROM "User" "TEST" WHERE "TEST"."id" = $1' assert params == (12, ) q2 = Query().select_from(User).where(q > 0) sql, params = dialect.create_query_compiler().compile_select(q2) assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE (SELECT "TEST"."id", "TEST"."name", "TEST"."email", "TEST"."created_time", "TEST"."address_id" FROM "User" "TEST" WHERE "TEST"."id" = $1) > $2' assert params == (12, 0) q = Query().select_from(TEST).join(TEST.address) \ .where(TEST.address.title == "OK") \ .where(TEST.id == 42) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == 'SELECT "TEST"."id", "TEST"."name", "TEST"."email", "TEST"."created_time", "TEST"."address_id" FROM "User" "TEST" INNER JOIN "Address" "t2" ON "TEST"."address_id" = "t2"."id" WHERE "t2"."title" = $1 AND "TEST"."id" = $2' assert params == ("OK", 42) q = Query().select_from(TEST).join(TEST.tags).where(TEST.tags.value == 42) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == 'SELECT "TEST"."id", "TEST"."name", "TEST"."email", "TEST"."created_time", "TEST"."address_id" FROM "User" "TEST" INNER JOIN "UserTags" "t2" ON "t2"."user_id" = "TEST"."id" INNER JOIN "Tag" "t4" ON "t2"."tag_id" = "t4"."id" WHERE "t4"."value" = $1' assert params == (42, )
async def test_array(conn, pgclean): registry = Registry() class ArrayTest(Entity, registry=registry, schema="execution"): strings: StringArray ints: IntArray result = await sync(conn, registry) assert result == """CREATE SCHEMA IF NOT EXISTS "execution"; CREATE TABLE "execution"."ArrayTest" ( "strings" TEXT[], "ints" INT[] );""" await conn.execute(result) result = await sync(conn, registry) assert result is None registry2 = Registry() class ArrayTest(Entity, registry=registry2, schema="execution"): id: Serial strings: StringArray ints: StringArray result = await sync(conn, registry2) assert result == """CREATE SEQUENCE "execution"."ArrayTest_id_seq"; ALTER TABLE "execution"."ArrayTest" ADD COLUMN "id" INT4 NOT NULL DEFAULT nextval('"execution"."ArrayTest_id_seq"'::regclass), ALTER COLUMN "ints" TYPE TEXT[] USING "ints"::TEXT[], ADD PRIMARY KEY("id");""" await conn.execute(result) result = await sync(conn, registry2) assert result is None inst = ArrayTest(strings=["Hello", "World"]) await conn.save(inst) inst = await conn.select( Query(ArrayTest).where(ArrayTest.strings.contains("Hello"))).first() assert inst is not None assert inst.strings == ["Hello", "World"] inst.strings.append("Some Value") await conn.save(inst) inst = await conn.select( Query(ArrayTest).where(ArrayTest.strings.contains("Hello"))).first() assert inst.strings == ["Hello", "World", "Some Value"] inst.strings.append("42") await conn.save(inst) inst.strings.append("43") await conn.save(inst) inst = await conn.select( Query(ArrayTest).where(ArrayTest.strings.contains("43"))).first() assert inst.strings == ["Hello", "World", "Some Value", "42", "43"]
def test_having(): q = Query() q.select_from(User) \ .having(User.id == 42) \ .having(~User.name.is_null()) \ sql, params = dialect.create_query_compiler().compile_select(q) assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" HAVING "t0"."id" = $1 AND "t0"."name" IS NOT NULL' assert params == (42, )
def test_group_by(): q = Query() q.select_from(User) \ .group(User.id) \ .group(User.name, User.email) \ .group(User.name == 42) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" GROUP BY "t0"."id", "t0"."name", "t0"."email", "t0"."name" = $1' assert params == (42, )
def test_query_and_or(): q = Query() q.select_from(User) q.where(or_(User.id == 1, User.id == 2, User.id == 3)) q.where(User.email == "email") q.where(or_(and_(User.id == 1, User.id == 2), User.id == 3)) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE ("t0"."id" = $1 OR "t0"."id" = $2 OR "t0"."id" = $3) AND "t0"."email" = $4 AND (("t0"."id" = $1 AND "t0"."id" = $2) OR "t0"."id" = $3)' assert params == (1, 2, 3, "email")
async def test_load_only_relation_field(conn): article_o = Article( id=42, creator=User( name={ "family": "Article", "given": "Creator" }, tags=[ Tag(value="ctag1"), Tag(value="ctag2"), Tag(value="ctag3"), ], address=Address(addr="Creator Addr"), ), updater=User( name={ "family": "Article", "given": "Updater" }, address=Address(addr="Updater Addr"), ), ) await conn.save(article_o) # TODO: remove empty relations from result q = Query(Article).where(Article.id == 42).load(Article.creator.name) article = await conn.select(q).first() data = json.dumps(article) assert data == """{"creator":{"name":{"family":"Article","given":"Creator"},"children":[],"tags":[]}}""" # TODO: ... # q = Query(Article).where(Article.id == 42).load(Article.creator.name.family) # article = await conn.select(q).first() # data = json.dumps(article) # assert data == """{"creator":{"name":{"family":"Article"},"children":[],"tags":[]}}""" # TODO: remove empty name from result q = Query(Article).where(Article.id == 42).load( Article.creator.address.addr) article = await conn.select(q).first() data = json.dumps(article) assert data == """{"creator":{"name":{},"address":{"addr":"Creator Addr"},"children":[],"tags":[]}}""" something2 = Something2(something=Something(article=article_o)) await conn.save(something2) q = Query(Something2).where(Something2.id == something2.id).load( Something2.id, Something2.something) s = await conn.select(q).first() data = json.dumps(s) # TODO: itt lehet nem kéne betöltenie az something.article értékét assert data == """{"id":1,"something":{"id":2,"article_id":42,"article":{"id":42,"creator_id":6,"updater_id":7}}}"""
def test_order_by(): q = Query() q.select_from(User) \ .order(User.id.asc()) \ .order(User.name) \ .order(User.email.desc()) \ .order(User.email == "email") sql, params = dialect.create_query_compiler().compile_select(q) assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" ORDER BY "t0"."id" ASC, "t0"."name" ASC, "t0"."email" DESC, "t0"."email" = $1 ASC' assert params == ("email", )
def test_virtual(): q = Query(User).where(User.name_q.contains("Jane Doe")) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE "t0"."name" ILIKE ('%' || $1 || '%') OR "t0"."name" ILIKE ('%' || $2 || '%')""" assert params == ("Jane", "Doe") q = Query(User).where( or_(User.name_q.contains("Jane Doe"), User.name_q.contains("Jhon Smith"))) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE "t0"."name" ILIKE ('%' || $1 || '%') OR "t0"."name" ILIKE ('%' || $2 || '%') OR "t0"."name" ILIKE ('%' || $3 || '%') OR "t0"."name" ILIKE ('%' || $4 || '%')""" assert params == ("Jane", "Doe", "Jhon", "Smith")
async def test_updated_time(conn, pgclean): R = Registry() class UT(Entity, registry=R, schema="execution"): id: Serial value: String created_time: CreatedTime updated_time: UpdatedTime result = await sync(conn, R) assert result == """CREATE SCHEMA IF NOT EXISTS "execution"; CREATE SEQUENCE "execution"."UT_id_seq"; CREATE TABLE "execution"."UT" ( "id" INT4 NOT NULL DEFAULT nextval('"execution"."UT_id_seq"'::regclass), "value" TEXT, "created_time" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_time" TIMESTAMPTZ, PRIMARY KEY("id") ); CREATE OR REPLACE FUNCTION "execution"."YT-UT-update-updated_time-8085b1-c1c14d"() RETURNS TRIGGER AS $$ BEGIN NEW."updated_time" = CURRENT_TIMESTAMP; RETURN NEW; END; $$ language 'plpgsql' ; CREATE TRIGGER "update-updated_time" BEFORE UPDATE ON "execution"."UT" FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE FUNCTION "execution"."YT-UT-update-updated_time-8085b1-c1c14d"();""" await conn.execute(result) result = await sync(conn, R) assert result is None inst = UT(id=1) await conn.save(inst) inst = await conn.select(Query(UT).where(UT.id == 1)).first() assert inst.updated_time is None inst.value = "Something" await conn.save(inst) inst = await conn.select(Query(UT).where(UT.id == 1)).first() assert inst.updated_time is not None ut1 = inst.updated_time await conn.save(inst) inst = await conn.select(Query(UT).where(UT.id == 1)).first() assert inst.updated_time == ut1 inst.value = "Hello World" await conn.save(inst) inst = await conn.select(Query(UT).where(UT.id == 1)).first() assert inst.updated_time > ut1
async def test_point(conn, pgclean): reg = Registry() class PointTest(Entity, registry=reg, schema="execution"): id: Serial point: Point result = await sync(conn, reg) await conn.execute(result) # TODO: kezelni, hogy adatbázisból van-e betöltve vagy sem p = PointTest(id=1, point=(1.25, 2.25)) assert p.point.x == 1.25 assert p.point.y == 2.25 assert await conn.save(p) is True ps = await conn.select( Query().select_from(PointTest).where(PointTest.id == 1)).first() assert ps.point.x == 1.25 assert ps.point.y == 2.25 p = PointTest(id=1, point=(5.25, 6.25)) assert await conn.save(p) is True assert p.point.x == 5.25 assert p.point.y == 6.25
async def test_deep_relation_where(conn): q = Query(Something2).where( Something2.something.article.creator.name.family == "Article") something2 = await conn.select(q).first() assert bool(something2) is True assert something2.id == 1
async def test_always_load(conn): article = Article( creator=User( name={ "family": "Article", "given": "Creator" }, address=Address(addr="Creator Addr"), ), updater=User( name={ "family": "Article", "given": "Updater" }, address=Address(addr="Updater Addr"), ), ) something = Something() something.article = article await conn.save(something) something = await conn.select(Query(Something)).first() assert something.id == 1 assert something.article is not None assert something.article.id == 1
async def test_query_from_employee(conn): q = Query().select_from(Employee) \ .where(Employee.employee_field == "Nice") \ .where(Worker.worker_field == "WF") sql, params = conn.dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."variant", "t0"."employee_field", "t1"."manager_field", "t2"."worker_field", "t3"."workerx_field", "t4"."workery_field" FROM "poly"."Employee" "t0" LEFT JOIN "poly"."Manager" "t1" ON "t1"."id" = "t0"."id" LEFT JOIN "poly"."Worker" "t2" ON "t2"."id" = "t0"."id" LEFT JOIN "poly"."WorkerX" "t3" ON "t3"."id" = "t2"."id" LEFT JOIN "poly"."WorkerY" "t4" ON "t4"."id" = "t2"."id" WHERE "t0"."employee_field" = $1 AND "t2"."worker_field" = $2""" assert params == ("Nice", "WF")
async def test_insert(conn): xyz_tag = Tag(tag="xyz") await conn.insert(xyz_tag) user = User(name="JDoe") user.address = Address(address="XYZ st. 345") user.tags.append(Tag(tag="some tag")) user.tags.append(xyz_tag) # print("\n".join(map(repr, save_operations(user)))) await conn.save(user) assert user.id == 1 assert user.name == "JDoe" assert user.address_id == 1 assert user.address.id == 1 assert user.address.address == "XYZ st. 345" assert len(user.tags) == 2 assert user.tags[0].id == 2 assert user.tags[0].tag == "some tag" assert user.tags[1].id == 1 assert user.tags[1].tag == "xyz" # automatically set foreign key on user addr = await conn.select( Query().select_from(Address).where(Address.id == 1)).first() user2 = User(name="AddrTest") user2.address = addr await conn.save(user2) assert user2.id == 2 assert user2.address_id == 1
async def test_query_org(conn): q = Query().select_from(Organization) \ .where(Employee.employee_field == "Nice") \ .where(Worker.worker_field == "WF") sql, params = conn.dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."employee_id" FROM "poly"."Organization" "t0" INNER JOIN "poly"."Employee" "t1" ON "t0"."employee_id" = "t1"."id" INNER JOIN "poly"."Worker" "t2" ON "t2"."id" = "t1"."id" WHERE "t1"."employee_field" = $1 AND "t2"."worker_field" = $2""" assert params == ("Nice", "WF")
def test_ambiguous(): q = Query().select_from(ArticleA) \ .where(ArticleA.creator.id == 1) \ .where(ArticleA.updater.id.is_null()) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."creator_id", "t0"."updater_id" FROM "ArticleA" "t0" INNER JOIN "UserA" "t1" ON "t1"."id" = "t0"."creator_id" INNER JOIN "UserA" "t3" ON "t3"."id" = "t0"."updater_id" WHERE "t1"."id" = $1 AND "t3"."id" IS NULL""" assert params == (1, )
def test_op_invert(op, original, inverted): q = Query() q.select_from(User) q.where(op(User.id, User.email)) q.where(~op(User.id, User.email)) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == f'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE "t0"."id" {original} "t0"."email" AND "t0"."id" {inverted} "t0"."email"'
async def test_load_empty_across(conn): user = User(name={"family": "User"}) await conn.save(user) user = await conn.select( Query(User).load(User, User.tags).where(User.id == user.id)).first() assert user.name.family == "User" assert user.tags == []
def test_entity_alias_mixin(): R = Registry() class Mixin: created_time: DateTimeTz class A(Entity, Mixin, registry=R): id: Serial q = Query(A).order(A.created_time) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."created_time" FROM "A" "t0" ORDER BY "t0"."created_time" ASC""" alias = A.alias("XXX") q = Query(alias).order(alias.created_time) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "XXX"."id", "XXX"."created_time" FROM "A" "XXX" ORDER BY "XXX"."created_time" ASC"""
def test_is_true(): q = Query() q.select_from(User) q.where(User.id.is_true()) q.where(~User.id.is_true()) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == 'SELECT "t0"."id", "t0"."name", "t0"."email", "t0"."created_time", "t0"."address_id" FROM "User" "t0" WHERE "t0"."id" IS TRUE AND "t0"."id" IS NOT TRUE' assert params == ()
def test_virtual_composite(): class UserComp3(Entity): id: Serial name: Composite[FullName] q = Query(UserComp3).where(UserComp3.name.formatted.contains("Jane Doe")) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", ("t0"."name")."title", ("t0"."name")."family", ("t0"."name")."given", ("t0"."name")."xyz" FROM "UserComp3" "t0" WHERE ("t0"."name")."family" ILIKE ('%' || $1 || '%') OR ("t0"."name")."family" ILIKE ('%' || $2 || '%') OR ("t0"."name")."given" ILIKE ('%' || $1 || '%') OR ("t0"."name")."given" ILIKE ('%' || $2 || '%')""" assert params == ("Jane", "Doe")
async def test_poly_circular(conn): file = File() file.file_name = "Test" await conn.save(file) fq = await conn.select(Query(File).where(File.id == file.id)).first() assert fq.id == file.id assert fq.type == "file" assert fq.file_name == "Test"
async def test_virtual_load(conn): registry = Registry() class VirtualLoad(Entity, registry=registry, schema="execution"): id: Int data_1: String data_2: String @virtual def data_concat(cls): return "NotLoaded" @data_concat.value def data_concat_val(cls, q): return func.CONCAT_WS(" / ", cls.data_1, cls.data_2) result = await sync(conn, registry) if result: await conn.execute(result) inst = VirtualLoad(id=1, data_1="Hello", data_2="World") await conn.save(inst) query = Query(VirtualLoad).load(VirtualLoad.data_concat) sql, params = dialect.create_query_compiler().compile_select(query) assert sql == 'SELECT CONCAT_WS($1, "t0"."data_1", "t0"."data_2") FROM "execution"."VirtualLoad" "t0"' assert params == (" / ", ) obj = await conn.select(query).first() assert obj.data_concat == "Hello / World" query = Query(VirtualLoad) sql, params = dialect.create_query_compiler().compile_select(query) assert sql == 'SELECT "t0"."id", "t0"."data_1", "t0"."data_2" FROM "execution"."VirtualLoad" "t0"' assert len(params) == 0 obj = await conn.select(query).first() assert obj.data_concat == "NotLoaded" query = Query(VirtualLoad).load(VirtualLoad.data_concat).order( VirtualLoad.data_concat.asc()) sql, params = dialect.create_query_compiler().compile_select(query) assert sql == 'SELECT CONCAT_WS($1, "t0"."data_1", "t0"."data_2") FROM "execution"."VirtualLoad" "t0" ORDER BY 1 ASC'
def test_deep_raltion(): class Deep(Entity): id: Serial user_id: Auto = ForeignKey(User.id) user: One[User] q = Query(Deep).where(Deep.user.tags.value == "OK") sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."user_id" FROM "Deep" "t0" INNER JOIN "User" "t1" ON "t0"."user_id" = "t1"."id" INNER JOIN "UserTags" "t2" ON "t2"."user_id" = "t1"."id" INNER JOIN "Tag" "t3" ON "t2"."tag_id" = "t3"."id" WHERE "t3"."value" = $1""" assert params == ("OK", )