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")]