async def test_callable_default(conn): await conn.execute("DROP SCHEMA IF EXISTS _private CASCADE") await conn.execute("DROP SCHEMA IF EXISTS execution CASCADE") await conn.execute("DROP SCHEMA IF EXISTS execution_private CASCADE") reg = Registry() class CallableDefault(Entity, registry=reg, schema="execution"): id: Serial creator_id: Int = lambda: 1 result = await sync(conn, reg) assert result == """CREATE SCHEMA IF NOT EXISTS "execution"; CREATE SEQUENCE "execution"."CallableDefault_id_seq"; CREATE TABLE "execution"."CallableDefault" ( "id" INT4 NOT NULL DEFAULT nextval('"execution"."CallableDefault_id_seq"'::regclass), "creator_id" INT4, PRIMARY KEY("id") );""" await conn.execute(result) result = await sync(conn, reg) assert bool(result) is False reg = Registry() class CallableDefault(Entity, registry=reg, schema="execution"): id: Serial creator_id: Int = Field(size=4, nullable=False, default=lambda: 1) result = await sync(conn, reg) assert result == """ALTER TABLE "execution"."CallableDefault"
def test_get_foreign_key_refs(): registry = Registry() class A(Entity, registry=registry): id: Serial class B(Entity, registry=registry): id: Serial a_id: Auto = ForeignKey(A.id) class C(Entity, registry=registry): id: Serial a_id: Auto = ForeignKey(A.id) b_id: Auto = ForeignKey("B.id") class D(Entity, registry=registry): id: Serial a_id_1: Auto = ForeignKey(A.id) a_id_2: Auto = ForeignKey(A.id) result = registry.get_foreign_key_refs(A.id) assert result[0] == (B, ["a_id"]) assert result[1] == (C, ["a_id"]) assert result[2] == (D, ["a_id_1", "a_id_2"]) result = registry.get_foreign_key_refs(B.id) assert result[0] == (C, ["b_id"])
async def test_change_composite_pk(conn, pgclean): R1 = Registry() class CompositePk(Entity, registry=R1, schema="execution"): id1: Int = Field(nullable=False) // PrimaryKey() later_id: Int = Field(nullable=False) id2: Int = Field(nullable=False) // PrimaryKey() result = await sync(conn, R1) assert result == """CREATE SCHEMA IF NOT EXISTS "execution"; CREATE TABLE "execution"."CompositePk" ( "id1" INT4 NOT NULL, "later_id" INT4 NOT NULL, "id2" INT4 NOT NULL, PRIMARY KEY("id1", "id2") );""" await conn.execute(result) R2 = Registry() class CompositePk(Entity, registry=R2, schema="execution"): id1: Int = Field(nullable=False) // PrimaryKey() later_id: Int = Field(nullable=False) // PrimaryKey() id2: Int = Field(nullable=False) result = await sync(conn, R2) assert result == """ALTER TABLE "execution"."CompositePk"
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"]
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_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
async def test_json_fix(conn, pgclean): reg_a = Registry() class JsonName(Entity, registry=reg_a, schema="execution"): given: String family: String class JsonUser(Entity, registry=reg_a, schema="execution"): id: Serial name: Json[JsonName] JsonUser.__fix_entries__ = [ JsonUser(id=1, name={ "given": "Given", "family": "Family" }), JsonUser(id=2), ] result = await sync(conn, reg_a) assert result == """CREATE SCHEMA IF NOT EXISTS "execution"; CREATE SEQUENCE "execution"."JsonUser_id_seq"; CREATE TABLE "execution"."JsonUser" ( "id" INT4 NOT NULL DEFAULT nextval('"execution"."JsonUser_id_seq"'::regclass), "name" JSONB, PRIMARY KEY("id") ); INSERT INTO "execution"."JsonUser" ("id", "name") VALUES (1, '{"given":"Given","family":"Family"}') ON CONFLICT ("id") DO UPDATE SET "name"='{"given":"Given","family":"Family"}'; INSERT INTO "execution"."JsonUser" ("id", "name") VALUES (2, '{"given":null,"family":null}') ON CONFLICT ("id") DO UPDATE SET "name"='{"given":null,"family":null}';""" await conn.execute(result) result = await sync(conn, reg_a) assert result is None
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_same_seq(conn, pgclean): reg = Registry() class Entity1(Entity, registry=reg, schema="execution"): id: Int = AutoIncrement(("execution", "same_seq")) class Entity2(Entity, registry=reg, schema="execution"): id: Int = AutoIncrement(("execution", "same_seq")) result = await sync(conn, reg) assert result == """CREATE SCHEMA IF NOT EXISTS "execution"; CREATE SEQUENCE "execution"."same_seq"; CREATE TABLE "execution"."Entity1" ( "id" INT4 DEFAULT nextval('"execution"."same_seq"'::regclass) ); CREATE TABLE "execution"."Entity2" ( "id" INT4 DEFAULT nextval('"execution"."same_seq"'::regclass) );""" await conn.execute(result) result = await sync(conn, reg) assert result is None reg2 = Registry() class Entity1(Entity, registry=reg2, schema="execution"): id: Serial result = await sync(conn, reg2) assert result == """DROP SEQUENCE "execution"."same_seq" CASCADE; DROP TABLE "execution"."Entity2" CASCADE; CREATE SEQUENCE "execution"."Entity1_id_seq"; ALTER TABLE "execution"."Entity1" ALTER COLUMN "id" SET DEFAULT nextval('"execution"."Entity1_id_seq"'::regclass), ALTER COLUMN "id" SET NOT NULL, ADD PRIMARY KEY("id");""" await conn.execute(result) result = await sync(conn, reg2) assert result is None
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_ambiguous_load(conn, pgclean): registry = Registry() class PolyBase(Entity, schema="poly", registry=registry, polymorph="type"): id: Serial type: String base_field: String class PolyChild(PolyBase, polymorph_id="child"): child_field: String class Something(Entity, schema="poly", registry=registry): id: Serial pid1: Auto = ForeignKey(PolyChild.id) p1: One[PolyChild] = "Something.pid1 == PolyChild.id" pid2: Auto = ForeignKey(PolyChild.id) p2: One[PolyChild] = "Something.pid2 == PolyChild.id" result = await sync(conn, registry) # print(result) await conn.execute(result) p1 = PolyChild() p1.base_field = "base field 1" p1.child_field = "child field 1" p2 = PolyChild() p2.base_field = "base field 2" p2.child_field = "child field 2" await conn.save(p1) await conn.save(p2) s = Something(pid1=p1.id, pid2=p2.id) await conn.save(s) q = Query().select_from(Something).load( Something, Something.p1, Something.p2).where(Something.id == s.id) sql, params = conn.dialect.create_query_compiler().compile_select(q) # print(sql) s = await conn.select(q).first() assert s.p1.id == p1.id assert s.p1.base_field == "base field 1" assert s.p1.child_field == "child field 1" assert s.p2.id == p2.id assert s.p2.base_field == "base field 2" assert s.p2.child_field == "child field 2"
async def test_date_types(conn): reg = Registry() class DateTest(Entity, registry=reg, schema="execution"): id: Serial date: Date date_time: DateTime date_time_tz: DateTimeTz time: Time time_tz: TimeTz class FixedTz(tzinfo): def __init__(self, utcoffset): self._utcoffset = timedelta(hours=utcoffset) self._dst = timedelta(hours=0) def utcoffset(self, dt): return self._utcoffset def dst(self, dt): return self._dst result = await sync(conn, reg) await conn.execute(result) inst = DateTest( date=date(2001, 12, 21), date_time=datetime(2019, 6, 1, 12, 23, 34), date_time_tz=datetime(2019, 6, 1, 12, 23, 34, tzinfo=FixedTz(5)), time=time(12, 23, 34), time_tz=time(12, 23, 34, tzinfo=FixedTz(6)), ) await conn.save(inst) obj = await conn.select(Query(DateTest)).first() assert obj.date == date(2001, 12, 21) assert obj.date_time == datetime(2019, 6, 1, 12, 23, 34) assert obj.date_time_tz == datetime(2019, 6, 1, 12, 23, 34, tzinfo=FixedTz(5)) assert obj.time == time(12, 23, 34) assert obj.time_tz == time(12, 23, 34, tzinfo=FixedTz(6))
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"""
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'
async def test_create_later(conn, pgclean): r = Registry() class TriggerX(Entity, schema="_trigger", registry=r): id: Serial result = await sync(conn, r) assert result == """CREATE SCHEMA IF NOT EXISTS "_trigger"; CREATE SEQUENCE "_trigger"."TriggerX_id_seq"; CREATE TABLE "_trigger"."TriggerX" ( "id" INT4 NOT NULL DEFAULT nextval('"_trigger"."TriggerX_id_seq"'::regclass), PRIMARY KEY("id") );""" await conn.execute(result) TriggerX.__triggers__.append( PostgreTrigger( name="update_time", before="UPDATE", for_each="ROW", when="OLD.* IS DISTINCT FROM NEW.*", body=""" NEW.updated_time = NOW(); RETURN NEW; """, )) result = await sync(conn, r) assert result == """CREATE OR REPLACE FUNCTION "_trigger"."YT-TriggerX-update_time-8085b1-af0df2"() RETURNS TRIGGER AS $$ BEGIN NEW.updated_time = NOW(); RETURN NEW; END; $$ language 'plpgsql' ; CREATE TRIGGER "update_time" BEFORE UPDATE ON "_trigger"."TriggerX" FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE FUNCTION "_trigger"."YT-TriggerX-update_time-8085b1-af0df2"();""" await conn.execute(result) result = await sync(conn, r) assert not result
def test_join_type_in_or(): R = Registry() class A(Entity, registry=R): id: Serial class B(Entity, registry=R): id: Serial a_id: Auto = ForeignKey(A.id) a: One[A] q = Query(B).where(B.a.id == 2) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."a_id" FROM "B" "t0" INNER JOIN "A" "t1" ON "t0"."a_id" = "t1"."id" WHERE "t1"."id" = $1""" assert params == (2, ) q = Query(B).where(or_(B.a.id == 2, B.id == 3)) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."a_id" FROM "B" "t0" LEFT JOIN "A" "t1" ON "t0"."a_id" = "t1"."id" WHERE "t1"."id" = $1 OR "t0"."id" = $2""" assert params == (2, 3)
def test_array(): R = Registry() class A(Entity, registry=R): id: Serial ints: IntArray q = Query(A).where(A.ints[0] == 1) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."ints" FROM "A" "t0" WHERE ("t0"."ints")[1] = $1""" assert params == (1, ) q = Query(A).where(A.ints.contains(1)) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."ints" FROM "A" "t0" WHERE $1=ANY("t0"."ints")""" assert params == (1, ) q = Query(A).where(~A.ints.contains(1)) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."ints" FROM "A" "t0" WHERE NOT($1=ANY("t0"."ints"))"""
def test_entity_state(): registry = Registry() class User(Entity, registry=registry): id: Serial name: String user = User(id=1, name="Almafa") user.__state__.changes() user.__state__.changes_with_previous() user.__state__.changed_realtions() if user.__state__.is_dirty: pass if user.__state__.is_empty: pass user2 = User(id=2, name="Almafa") if user.__state__ == user2.__state__: pass
async def test_diff_defaults(conn, pgclean): reg = Registry() class Defaults(Entity, registry=reg, schema="execution"): int_w_def: Int = 0 int2_w_def: Int = Field(size=2, default=1) string_w_def: String = "Hello" bool_w_def: Bool = True interval: Int = Field(nullable=False) result = await sync(conn, reg) assert result == """CREATE SCHEMA IF NOT EXISTS "execution"; CREATE TABLE "execution"."Defaults" ( "int_w_def" INT4 NOT NULL DEFAULT 0, "int2_w_def" INT2 NOT NULL DEFAULT 1, "string_w_def" TEXT NOT NULL DEFAULT 'Hello', "bool_w_def" BOOLEAN NOT NULL DEFAULT TRUE, "interval" INT4 NOT NULL );""" await conn.execute(result) result = await sync(conn, reg) assert bool(result) is False
def test_entity(): registry = Registry() class User(Entity, registry=registry): pass
class BaseEntity(Entity, registry=Registry(), _root=True): pass
import pytest from yapic.entity.sql import sync, PostgreDialect from yapic.entity import (Entity, Serial, Int, String, ForeignKey, PrimaryKey, One, Many, ManyAcross, Registry, DependencyList, Json, Composite, save_operations, Auto, Query, Loading, Relation, raw) from yapic import json pytestmark = pytest.mark.asyncio # type: ignore _registry = Registry() class Address(Entity, registry=_registry, schema="ent_load"): id: Serial addr: String class FullName(Entity, registry=_registry, schema="ent_load"): title: String family: String given: String class User(Entity, registry=_registry, schema="ent_load"): id: Serial name: Composite[FullName] address_id: Auto = ForeignKey(Address.id) address: One[Address]
# flake8: noqa: E501 import pytest from datetime import datetime, date, time, tzinfo, timedelta from decimal import Decimal from yapic.entity.sql import sync, PostgreTrigger from yapic.entity import (Entity, Field, Serial, Int, String, Bytes, Date, DateTime, DateTimeTz, Time, TimeTz, Bool, ForeignKey, PrimaryKey, One, Query, func, EntityDiff, Registry, Json, JsonArray, Composite, Auto, Numeric, Float, Point, UUID, virtual) pytestmark = pytest.mark.asyncio REGISTRY = Registry() class TriggerTable(Entity, schema="_trigger", registry=REGISTRY): id: Serial updated_time: DateTimeTz TriggerTable.__triggers__.append( PostgreTrigger( name="update_time", before="UPDATE", for_each="ROW", when="OLD.* IS DISTINCT FROM NEW.*", body=""" NEW.updated_time = NOW(); RETURN NEW; """,
async def test_diff(conn): new_reg = Registry() class Address(Entity, schema="execution", registry=new_reg): id: Serial title: String class User(Entity, schema="execution", registry=new_reg): id: Serial uuid: UUID name_x: String = Field(size=100) bio: String = Field(size=200) fixed_char: Bytes secret: Bytes address_id: Auto = ForeignKey(Address.id) address: One[Address] salary: Numeric = Field(size=[15, 3]) distance_mm: Float distance_km: Float = Field(size=4) point: Point is_active: Bool = True birth_date: String naive_date: DateTimeTz = func.now() created_time: DateTimeTz = func.CURRENT_TIMESTAMP updated_time: DateTimeTz time: Time time_tz: TimeTz class NewTable(Entity, registry=new_reg, schema="_private"): id: Serial class Gender(Entity, registry=new_reg, schema="execution"): value: String = PrimaryKey() title: String Gender.__fix_entries__ = [ Gender(value="male", title="Male"), Gender(value="female", title="Female"), Gender(value="other", title="Other"), ] await conn.execute("DROP SCHEMA IF EXISTS _private CASCADE") result = await sync(conn, new_reg) assert result == """DROP SEQUENCE "execution_private"."User_id_seq" CASCADE; DROP TABLE "execution_private"."User" CASCADE; CREATE SCHEMA IF NOT EXISTS "_private"; CREATE SEQUENCE "_private"."NewTable_id_seq"; CREATE TABLE "_private"."NewTable" ( "id" INT4 NOT NULL DEFAULT nextval('"_private"."NewTable_id_seq"'::regclass), PRIMARY KEY("id") ); CREATE TABLE "execution"."Gender" ( "value" TEXT NOT NULL, "title" TEXT, PRIMARY KEY("value") ); INSERT INTO "execution"."Gender" ("value", "title") VALUES ('male', 'Male') ON CONFLICT ("value") DO UPDATE SET "title"='Male'; INSERT INTO "execution"."Gender" ("value", "title") VALUES ('female', 'Female') ON CONFLICT ("value") DO UPDATE SET "title"='Female'; INSERT INTO "execution"."Gender" ("value", "title") VALUES ('other', 'Other') ON CONFLICT ("value") DO UPDATE SET "title"='Other'; ALTER TABLE "execution"."User" DROP COLUMN "name", ADD COLUMN "name_x" VARCHAR(100), ALTER COLUMN "bio" TYPE VARCHAR(200) USING "bio"::VARCHAR(200), ALTER COLUMN "fixed_char" TYPE BYTEA USING "fixed_char"::BYTEA, ALTER COLUMN "salary" TYPE NUMERIC(15, 3) USING "salary"::NUMERIC(15, 3), ALTER COLUMN "distance_km" TYPE FLOAT4 USING "distance_km"::FLOAT4, ALTER COLUMN "birth_date" TYPE TEXT USING "birth_date"::TEXT, ALTER COLUMN "naive_date" TYPE TIMESTAMPTZ USING "naive_date"::TIMESTAMPTZ, ALTER COLUMN "naive_date" SET DEFAULT now(), ALTER COLUMN "created_time" SET DEFAULT CURRENT_TIMESTAMP;""" await conn.execute(result) result = await sync(conn, new_reg) assert bool(result) is False # remove gender value Gender.__fix_entries__ = [ Gender(value="male", title="Male"), Gender(value="female", title="Female"), ] result = await sync(conn, new_reg) assert result == """DELETE FROM "execution"."Gender" WHERE "value"='other';""" await conn.execute(result) Gender.__fix_entries__ = [ Gender(value="male", title="MaleX"), Gender(value="female", title="FemaleY"), Gender(value="insert"), ] result = await sync(conn, new_reg) assert result == """INSERT INTO "execution"."Gender" ("value") VALUES ('insert') ON CONFLICT ("value") DO NOTHING; UPDATE "execution"."Gender" SET "title"='MaleX' WHERE "value"='male'; UPDATE "execution"."Gender" SET "title"='FemaleY' WHERE "value"='female';""" await conn.execute(result)
import pytest from yapic.entity.sql import sync from yapic.entity import (Entity, Serial, Int, String, ForeignKey, PrimaryKey, One, Many, ManyAcross, Registry, DependencyList, Json, Composite, save_operations, Auto, Query) pytestmark = pytest.mark.asyncio # type: ignore REGISTRY = Registry() _registry = Registry() class Employee(Entity, schema="poly", polymorph="variant", registry=REGISTRY): id: Serial variant: String employee_field: String class Manager(Employee, polymorph_id="manager", registry=REGISTRY): manager_field: String class Worker(Employee, polymorph_id="worker", registry=REGISTRY): worker_field: String class WorkerX(Worker, polymorph_id="workerx", registry=REGISTRY): workerx_field: String class WorkerY(Worker, polymorph_id="workery", registry=REGISTRY):
async def test_json(conn, pgclean): reg_a = Registry() reg_b = Registry() class JsonTyped(TypedDict): field1: str field2: int class JsonXY(Entity, registry=reg_a, schema="execution"): x: Int y: Int class JsonName(Entity, registry=reg_a, schema="execution"): given: String family: String xy: Json[JsonXY] class JsonUser(Entity, registry=reg_a, schema="execution"): id: Serial name: Json[JsonName] points: Json[List[JsonXY]] typed: Json[JsonTyped] result = await sync(conn, reg_a) assert result == """CREATE SCHEMA IF NOT EXISTS "execution"; CREATE SEQUENCE "execution"."JsonUser_id_seq"; CREATE TABLE "execution"."JsonUser" ( "id" INT4 NOT NULL DEFAULT nextval('"execution"."JsonUser_id_seq"'::regclass), "name" JSONB, "points" JSONB, "typed" JSONB, PRIMARY KEY("id") );""" await conn.execute(result) points = [ { "x": 1, "y": 2 }, { "x": 10, "y": 20 }, { "x": 30, "y": 42 }, ] user = JsonUser( name={ "given": "Given", "family": "Family", "xy": { "x": 1, "y": 2 } }, points=points, typed={ "field1": "str", "field2": 42 }, ) await conn.insert(user) assert user.name.given == "Given" assert user.name.family == "Family" assert user.name.xy.x == 1 assert user.name.xy.y == 2 assert isinstance(user.points[0], JsonXY) assert user.points[0].x == 1 assert user.points[0].y == 2 assert isinstance(user.points[1], JsonXY) assert user.points[1].x == 10 assert user.points[1].y == 20 assert isinstance(user.points[2], JsonXY) assert user.points[2].x == 30 assert user.points[2].y == 42 assert isinstance(user.typed, dict) assert user.typed["field1"] == "str" assert user.typed["field2"] == 42 result = await sync(conn, reg_a) assert bool(result) is False
author: One[UserComp2] q = Query().select_from(UserComp2).where(UserComp2.name.family == "Kiss") 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 "UserComp2" "t0" WHERE ("t0"."name")."family" = $1""" assert params == ("Kiss", ) q = Query().select_from(Article2) \ .where(Article2.author.name.family == "Kiss") \ .where(Article2.author.name.xyz.z == 1) sql, params = dialect.create_query_compiler().compile_select(q) assert sql == """SELECT "t0"."id", "t0"."author_id" FROM "Article2" "t0" INNER JOIN "UserComp2" "t1" ON "t0"."author_id" = "t1"."id" WHERE ("t1"."name")."family" = $1 AND jsonb_extract_path(("t1"."name")."xyz", 'z') = $2""" assert params == ("Kiss", 1) reg_ambiguous = Registry() class UserA(Entity, registry=reg_ambiguous): id: Serial class ArticleA(Entity, registry=reg_ambiguous): id: Serial creator_id: Auto = ForeignKey(UserA.id) creator: One[UserA] = "UserA.id == ArticleA.creator_id" updater_id: Auto = ForeignKey(User.id) updater: One[UserA] = "UserA.id == ArticleA.updater_id"
async def test_composite(conn): await conn.execute("DROP SCHEMA IF EXISTS _private CASCADE") await conn.execute("DROP SCHEMA IF EXISTS execution CASCADE") reg_a = Registry() reg_b = Registry() class CompXY(Entity, registry=reg_a, schema="execution"): x: String y: String class CompName(Entity, registry=reg_a, schema="execution"): given: String family: String xy: Composite[CompXY] class CompUser(Entity, registry=reg_a, schema="execution"): id: Serial name: Composite[CompName] class Article(Entity, registry=reg_a, schema="execution"): id: Serial author_id: Auto = ForeignKey(CompUser.id) author: One[CompUser] result = await sync(conn, reg_a) assert result == """CREATE SCHEMA IF NOT EXISTS "execution"; CREATE SEQUENCE "execution"."Article_id_seq"; CREATE TYPE "execution"."CompXY" AS ( "x" TEXT, "y" TEXT ); CREATE TYPE "execution"."CompName" AS ( "given" TEXT, "family" TEXT, "xy" "execution"."CompXY" ); CREATE SEQUENCE "execution"."CompUser_id_seq"; CREATE TABLE "execution"."CompUser" ( "id" INT4 NOT NULL DEFAULT nextval('"execution"."CompUser_id_seq"'::regclass), "name" "execution"."CompName", PRIMARY KEY("id") ); CREATE TABLE "execution"."Article" ( "id" INT4 NOT NULL DEFAULT nextval('"execution"."Article_id_seq"'::regclass), "author_id" INT4, PRIMARY KEY("id") ); CREATE INDEX "idx_Article__author_id" ON "execution"."Article" USING btree ("author_id"); ALTER TABLE "execution"."Article" ADD CONSTRAINT "fk_Article__author_id-CompUser__id" FOREIGN KEY ("author_id") REFERENCES "execution"."CompUser" ("id") ON UPDATE RESTRICT ON DELETE RESTRICT;""" await conn.execute(result) # TODO: kitalálni, hogyan lehet módosítani a composite typeot # class CompName(Entity, registry=reg_b, schema="execution"): # _given: String # family: String = Field(size=50) # new_column: Int # class CompUser(Entity, registry=reg_b, schema="execution"): # id: Serial # name: Composite[CompName] # result = await sync(conn, reg_b) # assert result == """ALTER TYPE "execution"."CompName" # DROP ATTRIBUTE "given", # ADD ATTRIBUTE "_given" TEXT, # ADD ATTRIBUTE "new_column" INT4, # ALTER ATTRIBUTE "family" TYPE VARCHAR(50);""" # await conn.execute(result) # result = await sync(conn, reg_b) # assert result is None user = CompUser(name={ "family": "Family", "given": "Given", "xy": { "x": "X", "y": "Y" } }) assert user.name.family == "Family" assert user.name.given == "Given" assert user.name.xy.x == "X" assert user.name.xy.y == "Y" await conn.insert(user) assert user.name.family == "Family" assert user.name.given == "Given" assert user.name.xy.x == "X" assert user.name.xy.y == "Y" assert user.__state__.changes() == {} assert user.name.__state__.changes() == {} user.name.family = "FamilyModified" user.name.xy.y = "Y MOD" assert user.__state__.changes() == {"name": user.name} assert user.name.__state__.changes() == { "family": "FamilyModified", "xy": user.name.xy } await conn.update(user) assert user.name.family == "FamilyModified" assert user.name.given == "Given" assert user.name.xy.x == "X" assert user.name.xy.y == "Y MOD" assert user.__state__.changes() == {} assert user.name.__state__.changes() == {} user.name.family = "Family IOU" user.name.xy.y = "Y IOU" await conn.insert_or_update(user) assert user.name.family == "Family IOU" assert user.name.given == "Given" assert user.name.xy.x == "X" assert user.name.xy.y == "Y IOU" assert user.__state__.changes() == {} assert user.name.__state__.changes() == {} q = Query().select_from(CompUser).where(CompUser.id == user.id) user = await conn.select(q).first() assert user.name.family == "Family IOU" assert user.name.given == "Given" assert user.name.xy.x == "X" assert user.name.xy.y == "Y IOU" q = Query().select_from(CompUser).columns( CompUser.id, CompUser.name.xy.y).where(CompUser.id == user.id) res = await conn.select(q).first() assert res == (user.id, "Y IOU") q = Query().select_from(CompUser).columns( CompUser.id, CompUser.name).where(CompUser.id == user.id) res = await conn.select(q).first() assert res[0] == user.id assert res[1].family == "Family IOU" assert res[1].given == "Given" assert res[1].xy.x == "X" assert res[1].xy.y == "Y IOU" q = Query().select_from(CompUser).columns( CompUser.id, CompUser.name.xy).where(CompUser.id == user.id) res = await conn.select(q).first() assert res[0] == user.id assert res[1].x == "X" assert res[1].y == "Y IOU" article = Article() article.author_id = user.id await conn.save(article) q = Query().select_from(Article).columns( Article.id, Article.author.name).where(Article.id == article.id) res = await conn.select(q).first() assert res[0] == article.id assert res[1].family == "Family IOU" assert res[1].given == "Given" assert res[1].xy.x == "X" assert res[1].xy.y == "Y IOU"
async def test_pk_change(conn): await conn.execute("DROP SCHEMA IF EXISTS _private CASCADE") await conn.execute("DROP SCHEMA IF EXISTS execution CASCADE") await conn.execute("DROP SCHEMA IF EXISTS execution_private CASCADE") reg = Registry() class Address(Entity, registry=reg, schema="execution"): id: Int result = await sync(conn, reg) assert result == """CREATE SCHEMA IF NOT EXISTS "execution"; CREATE TABLE "execution"."Address" ( "id" INT4 );""" await conn.execute(result) # ADD PRIMARY KEY reg = Registry() class Address(Entity, registry=reg, schema="execution"): id: Serial result = await sync(conn, reg) assert result == """CREATE SEQUENCE "execution"."Address_id_seq"; ALTER TABLE "execution"."Address" ALTER COLUMN "id" SET DEFAULT nextval('"execution"."Address_id_seq"'::regclass), ALTER COLUMN "id" SET NOT NULL, ADD PRIMARY KEY("id");""" await conn.execute(result) # DROP PRIMARY KEY reg = Registry() class Address(Entity, registry=reg, schema="execution"): id: String result = await sync(conn, reg) assert result == """DROP SEQUENCE "execution"."Address_id_seq" CASCADE; ALTER TABLE "execution"."Address" DROP CONSTRAINT IF EXISTS "Address_pkey", ALTER COLUMN "id" DROP NOT NULL, ALTER COLUMN "id" TYPE TEXT USING "id"::TEXT, ALTER COLUMN "id" DROP DEFAULT;""" await conn.execute(result) # CHANGE PRIMARY KEY reg = Registry() class Address(Entity, registry=reg, schema="execution"): id: Serial result = await sync(conn, reg) assert result == """CREATE SEQUENCE "execution"."Address_id_seq"; ALTER TABLE "execution"."Address" ALTER COLUMN "id" TYPE INT4 USING "id"::INT4, ALTER COLUMN "id" SET DEFAULT nextval('"execution"."Address_id_seq"'::regclass), ALTER COLUMN "id" SET NOT NULL, ADD PRIMARY KEY("id");""" await conn.execute(result) reg = Registry() class Address(Entity, registry=reg, schema="execution"): id: Serial id2: Serial result = await sync(conn, reg) assert result == """CREATE SEQUENCE "execution"."Address_id2_seq"; ALTER TABLE "execution"."Address" DROP CONSTRAINT IF EXISTS "Address_pkey", ADD COLUMN "id2" INT4 NOT NULL DEFAULT nextval('"execution"."Address_id2_seq"'::regclass), ADD PRIMARY KEY("id", "id2");""" await conn.execute(result)
async def test_fk_change(conn): await conn.execute("DROP SCHEMA IF EXISTS _private CASCADE") await conn.execute("DROP SCHEMA IF EXISTS execution CASCADE") await conn.execute("DROP SCHEMA IF EXISTS execution_private CASCADE") reg = Registry() class Address(Entity, registry=reg, schema="execution"): id: Serial class User(Entity, registry=reg, schema="execution"): id: Serial address_id: Auto = ForeignKey(Address.id) result = await sync(conn, reg) assert result == """CREATE SCHEMA IF NOT EXISTS "execution"; CREATE SEQUENCE "execution"."Address_id_seq"; CREATE TABLE "execution"."Address" ( "id" INT4 NOT NULL DEFAULT nextval('"execution"."Address_id_seq"'::regclass), PRIMARY KEY("id") ); CREATE SEQUENCE "execution"."User_id_seq"; CREATE TABLE "execution"."User" ( "id" INT4 NOT NULL DEFAULT nextval('"execution"."User_id_seq"'::regclass), "address_id" INT4, PRIMARY KEY("id") ); CREATE INDEX "idx_User__address_id" ON "execution"."User" USING btree ("address_id"); ALTER TABLE "execution"."User" ADD CONSTRAINT "fk_User__address_id-Address__id" FOREIGN KEY ("address_id") REFERENCES "execution"."Address" ("id") ON UPDATE RESTRICT ON DELETE RESTRICT;""" await conn.execute(result) # DROP FK reg = Registry() class Address(Entity, registry=reg, schema="execution"): id: Serial class User(Entity, registry=reg, schema="execution"): id: Serial address_id: Int result = await sync(conn, reg) assert result == """DROP INDEX IF EXISTS "execution"."idx_User__address_id"; ALTER TABLE "execution"."User" DROP CONSTRAINT IF EXISTS "fk_User__address_id-Address__id";""" await conn.execute(result) # ADD FK reg = Registry() class Address(Entity, registry=reg, schema="execution"): id: Serial class User(Entity, registry=reg, schema="execution"): id: Serial address_id: Auto = ForeignKey(Address.id) result = await sync(conn, reg) assert result == """ALTER TABLE "execution"."User" ADD CONSTRAINT "fk_User__address_id-Address__id" FOREIGN KEY ("address_id") REFERENCES "execution"."Address" ("id") ON UPDATE RESTRICT ON DELETE RESTRICT; CREATE INDEX "idx_User__address_id" ON "execution"."User" USING btree ("address_id");""" await conn.execute(result)