def test_join_key(pg_database):
with temp_file("denorm-") as schema_file:
with connection("") as conn, transaction(conn) as cur:
cur.execute(_SCHEMA_SQL)
with open(schema_file, "w") as f:
json.dump(_SCHEMA_JSON, f)
output = run_process(
[
"denorm",
"create-join",
"--schema",
schema_file,
]
)
with connection("") as conn, transaction(conn) as cur:
cur.execute(output.decode("utf-8"))
with connection("") as conn, transaction(conn) as cur:
cur.execute(
"""
INSERT INTO parent (id, name)
VALUES (1, 'A'), (2, 'B');
INSERT INTO child (id, parent_id)
VALUES (1, 1), (2, 1), (3, 2);
"""
)
with connection("") as conn, transaction(conn) as cur:
cur.execute("SELECT * FROM child_key ORDER BY id")
result = cur.fetchall()
assert result == [(1,), (2,), (3,)]
def test_join_deferred(pg_database):
with temp_file("denorm-") as schema_file:
with connection("") as conn, transaction(conn) as cur:
cur.execute(_SCHEMA_SQL)
with open(schema_file, "w") as f:
schema_json = copy.deepcopy(_SCHEMA_JSON)
schema_json["consistency"] = "deferred"
json.dump(schema_json, f)
output = run_process([
"denorm",
"create-join",
"--schema",
schema_file,
])
with connection("") as conn, transaction(conn) as cur:
cur.execute(output.decode("utf-8"))
with connection("") as conn, transaction(conn) as cur:
cur.execute("""
INSERT INTO parent (id, name)
VALUES (1, 'A'), (2, 'B');
INSERT INTO child (id, parent_id)
VALUES (1, 1), (2, 1), (3, 2);
""")
with connection("") as conn, transaction(conn) as cur:
cur.execute("SELECT * FROM child_full ORDER BY id")
result = cur.fetchall()
assert result == [(1, "A"), (2, "A"), (3, "B")]
def test_agg_defer(pg_database):
with temp_file("denorm-") as schema_file, connection("") as conn:
with transaction(conn) as cur:
cur.execute(_SCHEMA_SQL)
with open(schema_file, "w") as f:
schema_json = copy.deepcopy(_SCHEMA_JSON)
schema_json["consistency"] = "deferred"
json.dump(schema_json, f)
output = run_process([
"denorm",
"create-agg",
"--schema",
schema_file,
])
with transaction(conn) as cur:
cur.execute(output.decode("utf-8"))
with transaction(conn) as cur:
cur.execute("""
INSERT INTO child (id, parent_id)
VALUES (1, 1), (2, 1), (3, 2);
""")
cur.execute("TABLE parent_child_stat")
result = cur.fetchall()
assert result == []
with transaction(conn) as cur:
cur.execute("SELECT * FROM parent_child_stat ORDER BY parent_id")
result = cur.fetchall()
assert result == [(1, 2, 2), (2, 1, 1)]
def test_join_example(pg_database):
with temp_file("denorm-") as schema_file:
with connection("") as conn, transaction(conn) as cur:
cur.execute(_SCHEMA_SQL)
with open(schema_file, "w") as f:
json.dump(_SCHEMA_JSON, f)
output = run_process(
[
"denorm",
"create-join",
"--schema",
schema_file,
]
)
with connection("") as conn, transaction(conn) as cur:
cur.execute(output.decode("utf-8"))
with connection("") as conn, transaction(conn) as cur:
cur.execute(
"""
INSERT INTO author (id, name)
VALUES
(1, 'Neil Gaiman'),
(2, 'Terry Pratchett');
INSERT INTO book (id, title)
VALUES
(1, 'Good Omens'),
(2, 'The Color of Magic');
INSERT INTO book_author (id, book_id, author_id, ordinal)
VALUES
(1, 1, 1, 1),
(2, 1, 2, 2),
(3, 2, 2, 1);
"""
)
with connection("") as conn, transaction(conn) as cur:
cur.execute("SELECT * FROM book_full ORDER BY id")
result = cur.fetchall()
assert result == [
(1, "Good Omens", ["Neil Gaiman", "Terry Pratchett"]),
(2, "The Color of Magic", ["Terry Pratchett"]),
]
def test_join(pg_database):
with temp_file("denorm-") as schema_file:
with connection("") as conn, transaction(conn) as cur:
cur.execute(_SCHEMA_SQL)
with open(schema_file, "w") as f:
json.dump(_SCHEMA_JSON, f)
output = run_process([
"denorm",
"create-join",
"--schema",
schema_file,
])
# print(output.decode("utf-8"))
with connection("") as conn, transaction(conn) as cur:
cur.execute(output.decode("utf-8"))
with connection("") as conn, transaction(conn) as cur:
cur.execute("""
INSERT INTO parent (id, name)
VALUES (1, 'A'), (2, 'B');
INSERT INTO other (id, name)
VALUES (1, 'Other');
INSERT INTO child (id, parent_id)
VALUES (1, 1), (2, 1), (3, 2);
INSERT INTO grandchild (id, child_id, other_id)
VALUES (1, 2, 1);
""")
with connection("") as conn, transaction(conn) as cur:
cur.execute("SELECT * FROM child_full ORDER BY id")
result = cur.fetchall()
assert result == [(1, "A", []), (2, "A", ["Other"]), (3, "B", [])]
with connection("") as conn, transaction(conn) as cur:
cur.execute("""
INSERT INTO other_override (id, parent_id, other_id, name)
VALUES (1, 1, 1, 'Override');
""")
with connection("") as conn, transaction(conn) as cur:
cur.execute("SELECT * FROM child_full ORDER BY id")
result = cur.fetchall()
assert result == [(1, "A", []), (2, "A", ["Override"]),
(3, "B", [])]
def pg_database(pg_server):
with pg.connection("dbname=postgres") as conn:
conn.autocommit = True
with pg.open_database(conn, "test"):
yield
def test_join_async(pg_database):
with temp_file("denorm-") as schema_file:
with connection("") as conn, transaction(conn) as cur:
cur.execute(_SCHEMA_SQL)
with open(schema_file, "w") as f:
json.dump(_SCHEMA_JSON, f)
output = run_process([
"denorm",
"create-join",
"--schema",
schema_file,
])
with connection("") as conn, transaction(conn) as cur:
# print(output.decode("utf-8"))
cur.execute(output.decode("utf-8"))
with connection("") as conn, transaction(conn) as cur:
cur.execute("""
INSERT INTO parent (id, name)
VALUES (1, 'A'), (2, 'B');
INSERT INTO child (id, parent_id)
VALUES (1, 1), (2, 1), (3, 2);
""")
with connection("") as conn:
conn.autocommit = True
with conn.cursor() as cur:
while True:
cur.execute("SELECT test__pcs__parent(10)")
(result, ) = cur.fetchone()
if not result:
break
with connection("") as conn, transaction(conn) as cur:
cur.execute("SELECT * FROM child_full ORDER BY id")
result = cur.fetchall()
assert result == [(1, "A"), (2, "A"), (3, "B")]
with connection("") as conn, transaction(conn) as cur:
cur.execute("UPDATE parent SET name = 'C' WHERE id = 2")
with connection("") as conn, transaction(conn) as cur:
cur.execute("SELECT * FROM child_full ORDER BY id")
result = cur.fetchall()
assert result == [(1, "A"), (2, "A"), (3, "B")]
# import time
# time.sleep(1000000)
with connection("") as conn:
conn.autocommit = True
with conn.cursor() as cur:
while True:
cur.execute("SELECT test__pcs__parent(10)")
(result, ) = cur.fetchone()
if not result:
break
with connection("") as conn, transaction(conn) as cur:
cur.execute("TABLE test__que__parent")
result = cur.fetchall()
assert result == []
with connection("") as conn, transaction(conn) as cur:
cur.execute("SELECT * FROM child_full ORDER BY id")
result = cur.fetchall()
assert result == [(1, "A"), (2, "A"), (3, "C")]
