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)
