Exemple #1
0
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,)]
Exemple #2
0
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")]
Exemple #3
0
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)]
Exemple #4
0
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", [])]
Exemple #6
0
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")]