Skip to content

armgit / dbx

Public Imported from GitHub
0

25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。

40.3 KB
Raw Blame
use dbx_core::db::postgres::*;
use std::time::Duration;

#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL database"]
async fn postgres_owned_serial_table_ddl_round_trips_without_external_sequences() {
    let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
    let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
    let suffix = uuid::Uuid::new_v4().simple();
    let schema = format!("dbx 6405 \"{suffix}");
    let schema_ident = pg_quote_ident(&schema);
    let table_name = "order\"items";
    let table_ident = pg_quote_ident(table_name);
    let table = format!("{schema_ident}.{table_ident}");
    let custom_sequence_name = "custom\"source";
    let custom_sequence = format!("{schema_ident}.{}", pg_quote_ident(custom_sequence_name));
    let custom_table_name = "custom defaults";
    let custom_table = format!("{schema_ident}.{}", pg_quote_ident(custom_table_name));
    let owned_custom_sequence_name = "owned custom source";
    let owned_custom_sequence = format!("{schema_ident}.{}", pg_quote_ident(owned_custom_sequence_name));
    let owned_custom_table_name = "owned custom defaults";
    let owned_custom_table = format!("{schema_ident}.{}", pg_quote_ident(owned_custom_table_name));
    let dropped_default_table_name = "dropped default";
    let dropped_default_table = format!("{schema_ident}.{}", pg_quote_ident(dropped_default_table_name));
    let client = pool.get().await.expect("get postgres client");
    client
        .batch_execute(&format!(
            "CREATE SCHEMA {schema_ident}; \
                 CREATE TABLE {table} (\
                   \"small\"\"id\" smallserial, \
                   \"regular id\" serial, \
                   \"large\"\"id\" bigserial, \
                   identity_id bigint GENERATED BY DEFAULT AS IDENTITY, \
                   base bigint DEFAULT 2, \
                   generated bigint GENERATED ALWAYS AS (base * 2) STORED\
                 ); \
                 CREATE SEQUENCE {custom_sequence}; \
                 CREATE TABLE {custom_table} (id bigint DEFAULT nextval({}::regclass)); \
                 CREATE TABLE {owned_custom_table} (id bigint NOT NULL); \
                 CREATE SEQUENCE {owned_custom_sequence} OWNED BY {owned_custom_table}.id; \
                 ALTER TABLE {owned_custom_table} ALTER COLUMN id \
                   SET DEFAULT nextval({}::regclass) + 7; \
                 CREATE TABLE {dropped_default_table} (id serial); \
                 ALTER TABLE {dropped_default_table} ALTER COLUMN id DROP DEFAULT",
            pg_quote_literal(&custom_sequence),
            pg_quote_literal(&owned_custom_sequence),
        ))
        .await
        .expect("create serial probe objects");

    let ddl = dbx_core::schema::pg_ddl(&pool, &schema, table_name).await.expect("read serial table ddl");
    let custom_ddl =
        dbx_core::schema::pg_ddl(&pool, &schema, custom_table_name).await.expect("read custom-default table ddl");
    let owned_custom_ddl = dbx_core::schema::pg_ddl(&pool, &schema, owned_custom_table_name)
        .await
        .expect("read owned custom-default table ddl");
    let dropped_default_ddl = dbx_core::schema::pg_ddl(&pool, &schema, dropped_default_table_name)
        .await
        .expect("read dropped-default table ddl");

    assert!(ddl.contains("\"small\"\"id\" smallserial NOT NULL"), "ddl: {ddl}");
    assert!(ddl.contains("\"regular id\" serial NOT NULL"), "ddl: {ddl}");
    assert!(ddl.contains("\"large\"\"id\" bigserial NOT NULL"), "ddl: {ddl}");
    assert!(ddl.contains("\"identity_id\" bigint generated by default as identity"), "ddl: {ddl}");
    assert!(ddl.contains("\"generated\" bigint generated always as ((base * 2)) stored"), "ddl: {ddl}");
    assert!(!ddl.contains("nextval("), "ddl: {ddl}");
    assert!(custom_ddl.contains("bigint DEFAULT nextval("), "ddl: {custom_ddl}");
    assert!(!custom_ddl.contains("bigserial"), "ddl: {custom_ddl}");
    assert!(owned_custom_ddl.contains("bigint NOT NULL DEFAULT (nextval("), "ddl: {owned_custom_ddl}");
    assert!(owned_custom_ddl.contains(" + 7)"), "ddl: {owned_custom_ddl}");
    assert!(!owned_custom_ddl.contains("bigserial"), "ddl: {owned_custom_ddl}");
    assert!(dropped_default_ddl.contains("\"id\" integer NOT NULL"), "ddl: {dropped_default_ddl}");
    assert!(!dropped_default_ddl.contains("serial"), "ddl: {dropped_default_ddl}");
    assert!(!dropped_default_ddl.contains("DEFAULT"), "ddl: {dropped_default_ddl}");

    client.batch_execute(&format!("DROP TABLE {table} CASCADE; {ddl}")).await.expect("replay copied table ddl");
    let inserted = client
            .query_one(
                &format!(
                    "INSERT INTO {table} DEFAULT VALUES RETURNING \"small\"\"id\", \"regular id\", \"large\"\"id\", identity_id, generated"
                ),
                &[],
            )
            .await
            .expect("insert with replayed sequence defaults");
    assert_eq!(inserted.get::<_, i16>(0), 1);
    assert_eq!(inserted.get::<_, i32>(1), 1);
    assert_eq!(inserted.get::<_, i64>(2), 1);
    assert_eq!(inserted.get::<_, i64>(3), 1);
    assert_eq!(inserted.get::<_, i64>(4), 4);

    client.batch_execute(&format!("DROP SCHEMA {schema_ident} CASCADE")).await.expect("drop serial probe schema");
}

#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL database"]
async fn postgres_column_metadata_dependency_joins_return_each_column_once() {
    let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
    let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
    let suffix = uuid::Uuid::new_v4().simple();
    let schema = format!("dbx 6547 \"{suffix}");
    let schema_ident = pg_quote_ident(&schema);
    let table_name = "issue6547_repro";
    let table_ident = pg_quote_ident(table_name);
    let table = format!("{schema_ident}.{table_ident}");
    let client = pool.get().await.expect("get postgres client");
    client
        .batch_execute(&format!(
            "CREATE SCHEMA {schema_ident}; \
                 CREATE TABLE {table} (\
                   id serial PRIMARY KEY, \
                   value text, \
                   small_id smallserial, \
                   big_id bigserial, \
                   ident_id bigint GENERATED BY DEFAULT AS IDENTITY, \
                   plain_int integer, \
                   vc varchar(32), \
                   num numeric(10, 2), \
                   ts timestamp\
                 ); \
                 CREATE SEQUENCE {schema_ident}.serial_decoy OWNED BY {table}.id; \
                 CREATE SEQUENCE {schema_ident}.identity_decoy OWNED BY {table}.ident_id; \
                 CREATE INDEX idx_issue6547_repro_id ON {table}(id); \
                 CREATE INDEX idx_issue6547_repro_big ON {table}(big_id); \
                 CREATE UNIQUE INDEX uniq_issue6547_repro_vc ON {table}(vc);"
        ))
        .await
        .expect("create repro table");

    let columns = get_columns(&pool, &schema, table_name).await.expect("get columns");
    // One pg_attribute row must produce exactly one ColumnInfo even when
    // the column has multiple sequence dependencies and several indexes.
    // Only the sequence used by the default, or the internal identity
    // sequence, is semantic metadata for the column.
    let mut seen: std::collections::HashSet<&str> = std::collections::HashSet::new();
    for column in &columns {
        assert!(seen.insert(column.name.as_str()), "duplicate column {:?} in {columns:?}", column.name);
    }
    assert_eq!(columns.len(), 9, "expected 9 columns, got {columns:?}");

    for (name, expected_extra) in [("id", "serial"), ("small_id", "smallserial"), ("big_id", "bigserial")] {
        let column = columns.iter().find(|c| c.name == name).unwrap_or_else(|| panic!("missing {name}"));
        assert_eq!(column.extra.as_deref(), Some(expected_extra), "{name} extra mismatch");
    }
    let ident = columns.iter().find(|c| c.name == "ident_id").expect("ident_id column");
    assert!(
        ident.extra.as_deref().is_some_and(|extra| extra.starts_with("generated by default as identity")),
        "ident_id extra: {:?}",
        ident.extra
    );
    // Ordinary columns keep their plain metadata.
    let vc = columns.iter().find(|c| c.name == "vc").expect("vc column");
    assert_eq!(vc.data_type, "character varying(32)");
    let num = columns.iter().find(|c| c.name == "num").expect("num column");
    assert_eq!(num.data_type, "numeric(10,2)");
    assert_eq!(num.numeric_precision, Some(10));
    assert_eq!(num.numeric_scale, Some(2));
    let ts = columns.iter().find(|c| c.name == "ts").expect("ts column");
    assert_eq!(ts.data_type, "timestamp without time zone");

    // The rendered column section of the DDL must contain each column
    // definition exactly once. Before the dependency-join fix these
    // queries could return "id" (serial + PRIMARY KEY + secondary index
    // = several pg_depend rows) any number of times.
    let ddl = dbx_core::schema::pg_ddl(&pool, &schema, table_name).await.expect("read repro table ddl");
    for needle in [r#"  "id" serial NOT NULL"#, r#"  "value" text"#, r#"  "vc" character varying(32)"#] {
        assert_eq!(ddl.matches(needle).count(), 1, "needle {needle:?} not unique in ddl:\n{ddl}");
    }

    drop(client);
    execute_query(&pool, &format!("DROP SCHEMA {schema_ident} CASCADE")).await.expect("drop repro schema");
}

#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL 9.x database"]
async fn postgres_legacy_table_ddl_uses_compatible_partition_probe() {
    let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
    let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
    let version = execute_query(&pool, "SHOW server_version_num").await.expect("query PostgreSQL version");
    let version_num = version.rows[0][0]
        .as_str()
        .expect("server_version_num should be text")
        .parse::<u32>()
        .expect("server_version_num should be numeric");
    assert!((90000..100000).contains(&version_num), "expected PostgreSQL 9.x, got {version_num}");

    let schema = format!("dbx_legacy_ddl_{}", uuid::Uuid::new_v4().simple());
    let schema_ident = pg_quote_ident(&schema);
    let parent = format!("{schema_ident}.parent");
    let child = format!("{schema_ident}.child");
    let function = format!("{schema_ident}.fill_child_name");
    let client = pool.get().await.expect("get postgres client");
    client
            .batch_execute(&format!(
                "CREATE SCHEMA {schema_ident}; \
                 CREATE TABLE {parent} (id integer PRIMARY KEY); \
                 CREATE TABLE {child} (parent_id integer REFERENCES {parent}(id), name text) INHERITS ({parent}); \
                 ALTER TABLE {child} ADD PRIMARY KEY (id); \
                 CREATE INDEX child_name_idx ON {child}(name); \
                 CREATE FUNCTION {function}() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN NEW.name := COALESCE(NEW.name, chr(120)); RETURN NEW; END$$; \
                 CREATE TRIGGER child_bi BEFORE INSERT ON {child} FOR EACH ROW EXECUTE PROCEDURE {function}()"
            ))
            .await
            .expect("create PostgreSQL 9.x DDL fixtures");
    drop(client);

    let partition_info =
        get_table_partition_info(&pool, &schema, "child").await.expect("classify PostgreSQL 9.x inherited table");
    let ddl = dbx_core::schema::pg_ddl(&pool, &schema, "child").await;
    // 查看表 DDL(DISPLAY 路径)在 9.x 上不能因 relispartition 报错,
    // INHERITS 子表也不能被当成声明式分区渲染。
    // The view-DDL path must not fail on the missing relispartition
    // column pre-10, and INHERITS children must render as plain tables.
    let ddl_with_partitions = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "child").await;
    let parent_ddl = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "parent").await;
    execute_query(&pool, &format!("DROP SCHEMA {schema_ident} CASCADE"))
        .await
        .expect("drop PostgreSQL 9.x DDL fixtures");
    let ddl = ddl.expect("generate PostgreSQL 9.x table DDL");
    let ddl_with_partitions = ddl_with_partitions.expect("generate PostgreSQL 9.x partition-tree DDL");
    let parent_ddl = parent_ddl.expect("generate PostgreSQL 9.x partition-tree DDL for parent");

    assert!(ddl.contains("CREATE TABLE"), "ddl: {ddl}");
    assert!(ddl.contains("PRIMARY KEY"), "ddl: {ddl}");
    assert!(ddl.contains("FOREIGN KEY"), "ddl: {ddl}");
    assert!(ddl.contains("CREATE INDEX \"child_name_idx\""), "ddl: {ddl}");
    assert!(ddl.contains("CREATE TRIGGER child_bi"), "ddl: {ddl}");
    // The 9.x child uses traditional `INHERITS`, so it is not a declarative
    // partition, but it must carry its parent in `inherits_parents` and the
    // rendered DDL must emit `INHERITS (parent)` (issue #10803).
    assert!(!partition_info.is_partition, "partition_info: {partition_info:?}");
    assert!(!partition_info.is_foreign, "partition_info: {partition_info:?}");
    assert_eq!(partition_info.inherits_parents.len(), 1, "partition_info: {partition_info:?}");
    assert_eq!(partition_info.inherits_parents[0].table, "parent", "partition_info: {partition_info:?}");
    assert!(partition_info.parent_schema.is_none(), "partition_info: {partition_info:?}");
    assert!(partition_info.bound.is_none(), "partition_info: {partition_info:?}");
    assert!(ddl.contains("INHERITS"), "ddl: {ddl}");
    assert!(ddl.contains("\"parent\""), "ddl: {ddl}");
    assert!(!ddl.contains("PARTITION OF"), "ddl: {ddl}");
    assert!(ddl_with_partitions.contains("CREATE TABLE"), "ddl_with_partitions: {ddl_with_partitions}");
    assert!(!ddl_with_partitions.contains("PARTITION OF"), "ddl_with_partitions: {ddl_with_partitions}");
    // 9.x 没有声明式分区:父表的分区树只含父表自身(子表按普通表)。
    assert_eq!(parent_ddl.matches("CREATE TABLE").count(), 1, "parent_ddl: {parent_ddl}");
    assert!(!parent_ddl.contains("PARTITION OF"), "parent_ddl: {parent_ddl}");
}

#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL database"]
async fn postgres_legacy_batched_ddl_metadata_preserves_relation_pairs_across_compat_tiers() {
    let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
    let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
    let schema = format!("dbx_batch_fkeys_{}", uuid::Uuid::new_v4().simple());
    let schema_ident = pg_quote_ident(&schema);
    let client = pool.get().await.expect("get postgres client");
    client
        .batch_execute(&format!(
            "CREATE SCHEMA {schema_ident}; \
                 CREATE TABLE {schema_ident}.parent_a (id integer PRIMARY KEY); \
                 CREATE TABLE {schema_ident}.parent_b (id integer PRIMARY KEY); \
                 CREATE TABLE {schema_ident}.child_a ( \
                   id integer PRIMARY KEY, parent_id integer, \
                   CONSTRAINT child_a_parent_fk FOREIGN KEY (parent_id) REFERENCES {schema_ident}.parent_a(id) \
                 ); \
                 CREATE TABLE {schema_ident}.child_b ( \
                   id integer PRIMARY KEY, parent_id integer, \
                   CONSTRAINT child_b_parent_fk FOREIGN KEY (parent_id) REFERENCES {schema_ident}.parent_b(id) \
                 ); \
                 CREATE TABLE {schema_ident}.without_fk (id integer PRIMARY KEY)"
        ))
        .await
        .expect("create batched foreign-key fixtures");

    let version_num = client
        .query_one("SHOW server_version_num", &[])
        .await
        .expect("query PostgreSQL version")
        .get::<_, String>(0)
        .parse::<u32>()
        .expect("server_version_num should be numeric");
    let has_conparentid = client
        .query_one(
            "SELECT EXISTS ( \
                   SELECT 1 FROM pg_catalog.pg_attribute \
                   WHERE attrelid = 'pg_catalog.pg_constraint'::pg_catalog.regclass \
                     AND attname = 'conparentid' AND NOT attisdropped \
                 )",
            &[],
        )
        .await
        .expect("probe pg_constraint.conparentid")
        .get::<_, bool>(0);
    if version_num < 110000 {
        assert!(!has_conparentid, "pre-11 PostgreSQL must not have parent-linked constraint clones");
    }

    let empty = list_foreign_keys_for_relations(&pool, &[]).await.expect("empty relation batch");
    assert!(empty.is_empty());

    let single_relations = vec![(schema.clone(), "child_a".to_string())];
    let single = list_foreign_keys_for_relations(&pool, &single_relations).await.expect("single relation batch");
    let single_fk = &single[&(schema.clone(), "child_a".to_string())][0];
    assert_eq!(single_fk.name, "child_a_parent_fk");
    assert_eq!(single_fk.ref_table, "parent_a");

    // Keep the pairs deliberately out of name order. A cross product or
    // independently unnested array would duplicate or misattribute keys.
    let relations = vec![
        (schema.clone(), "child_b".to_string()),
        (schema.clone(), "without_fk".to_string()),
        (schema.clone(), "child_a".to_string()),
    ];
    let batched = list_foreign_keys_for_relations(&pool, &relations).await.expect("multi-relation batch");
    assert_eq!(batched.len(), 2, "batched keys: {batched:?}");
    assert!(!batched.contains_key(&(schema.clone(), "without_fk".to_string())));
    assert_eq!(batched[&(schema.clone(), "child_a".to_string())][0].ref_table, "parent_a");
    assert_eq!(batched[&(schema.clone(), "child_b".to_string())][0].ref_table, "parent_b");

    let schemas: Vec<&str> = relations.iter().map(|(schema, _)| schema.as_str()).collect();
    let tables: Vec<&str> = relations.iter().map(|(_, table)| table.as_str()).collect();
    let compat = list_foreign_keys_for_relations_with_sql(
        &client,
        postgres_foreign_keys_for_relations_compat_sql(),
        &schemas,
        &tables,
    )
    .await
    .expect("subscript-paired compatibility query");
    assert_eq!(compat[&(schema.clone(), "child_a".to_string())][0].ref_table, "parent_a");
    assert_eq!(compat[&(schema.clone(), "child_b".to_string())][0].ref_table, "parent_b");

    let modern =
        list_foreign_keys_for_relations_with_sql(&client, postgres_foreign_keys_for_relations_sql(), &schemas, &tables)
            .await;
    if version_num >= 90400 {
        let modern = modern.expect("multi-array unnest primary query");
        assert_eq!(modern[&(schema.clone(), "child_a".to_string())][0].ref_table, "parent_a");
        assert_eq!(modern[&(schema.clone(), "child_b".to_string())][0].ref_table, "parent_b");
    } else {
        modern.expect_err("pre-9.4 server must reject multi-array unnest");
    }

    let relation_oids: Vec<i64> = client
        .query(
            "SELECT c.oid::bigint \
                 FROM pg_catalog.pg_class c \
                 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \
                 WHERE n.nspname = $1 ORDER BY c.relname",
            &[&schema],
        )
        .await
        .expect("query fixture relation oids")
        .into_iter()
        .map(|row| row.get(0))
        .collect();
    get_table_partition_local_objects_for_relations_with_sql(
        &client,
        postgres_table_partition_local_objects_for_relations_compat_sql(),
        &relation_oids,
    )
    .await
    .expect("pre-parent-clone local-object query");
    let modern_local_objects = get_table_partition_local_objects_for_relations_with_sql(
        &client,
        postgres_table_partition_local_objects_for_relations_sql(),
        &relation_oids,
    )
    .await;
    if version_num >= 90300 {
        modern_local_objects.expect("JSON-extraction local-object query");
    } else {
        modern_local_objects.expect_err("PostgreSQL 9.2 must reject the JSON ->> operator");
    }
    drop(client);

    let child_ddl = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "child_a")
        .await
        .expect("display DDL with foreign key");
    let without_fk_ddl = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "without_fk")
        .await
        .expect("display DDL without foreign key");
    execute_query(&pool, &format!("DROP SCHEMA {schema_ident} CASCADE"))
        .await
        .expect("drop batched foreign-key fixtures");

    assert!(child_ddl.contains("FOREIGN KEY"), "child ddl: {child_ddl}");
    assert!(!without_fk_ddl.contains("FOREIGN KEY"), "without-fk ddl: {without_fk_ddl}");
}

#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL 15+ database"]
async fn postgres_foreign_key_options_round_trip_through_single_and_partition_tree_ddl() {
    let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
    let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
    let suffix = uuid::Uuid::new_v4().simple().to_string();
    let source = format!("dbx_fk_options_{}", &suffix[..10]);
    let references = format!("{source}_refs");
    let source_ident = pg_quote_ident(&source);
    let references_ident = pg_quote_ident(&references);
    let client = pool.get().await.expect("get postgres client");
    client.batch_execute(&format!(
        "CREATE SCHEMA {source_ident}; CREATE SCHEMA {references_ident};
         CREATE TABLE {references_ident}.\"parent(table\" (\"id)\" integer, version integer, PRIMARY KEY (\"id)\", version));
         CREATE TABLE {source_ident}.children (
           id integer, \"parent)a\" integer, \"parent\"\"b\" integer,
           CONSTRAINT fk_parent FOREIGN KEY (\"parent)a\", \"parent\"\"b\")
             REFERENCES {references_ident}.\"parent(table\" (\"id)\", version)
             MATCH FULL ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
         ) PARTITION BY RANGE (id);
         CREATE TABLE {source_ident}.children_default PARTITION OF {source_ident}.children DEFAULT;
         ALTER TABLE {source_ident}.children_default ADD CONSTRAINT fk_local
           FOREIGN KEY (\"parent)a\", \"parent\"\"b\") REFERENCES {references_ident}.\"parent(table\" (\"id)\", version)
           ON UPDATE CASCADE ON DELETE SET NULL (\"parent)a\") DEFERRABLE INITIALLY IMMEDIATE NOT VALID;"
    )).await.expect("create foreign key option fixtures");

    for include_tree in [false, true] {
        let replay = format!("{source}_{}", if include_tree { "tree" } else { "single" });
        let replay_ident = pg_quote_ident(&replay);
        let ddl = if include_tree {
            dbx_core::schema::pg_ddl_with_partitions(&pool, &source, "children").await
        } else {
            dbx_core::schema::pg_ddl(&pool, &source, "children").await
        }
        .expect("read foreign key DDL");
        client
            .batch_execute(&format!("CREATE SCHEMA {replay_ident}; {}", ddl.replace(&source_ident, &replay_ident)))
            .await
            .unwrap_or_else(|error| panic!("replay foreign key DDL: {error:?}; DDL: {ddl}"));
        let mut snapshots = Vec::new();
        for schema in [&source, &replay] {
            let result = execute_query(
                &pool,
                &format!(
                    "SELECT c.relname::text, con.conname::text, con.condeferrable, con.condeferred,
                        con.confupdtype::text, con.confdeltype::text, con.convalidated,
                        pg_catalog.pg_get_constraintdef(con.oid, true) AS definition
                 FROM pg_catalog.pg_constraint con
                 JOIN pg_catalog.pg_class c ON c.oid = con.conrelid
                 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                 WHERE n.nspname = {} AND con.contype = 'f'
                   AND (c.relname = 'children' OR {})
                 ORDER BY c.relname, con.conname",
                    pg_quote_literal(schema),
                    include_tree
                ),
            )
            .await
            .expect("snapshot restored constraint properties");
            assert!(!result.rows.is_empty());
            snapshots.push(result.rows);
        }
        assert_eq!(snapshots[0], snapshots[1], "foreign key options changed; DDL: {ddl}");
        client.batch_execute(&format!("DROP SCHEMA {replay_ident} CASCADE")).await.expect("drop replay schema");
    }
    client
        .batch_execute(&format!("DROP SCHEMA {source_ident} CASCADE; DROP SCHEMA {references_ident} CASCADE"))
        .await
        .expect("drop fixture schemas");
}

#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL database"]
async fn postgres_ddl_emits_not_valid_check_constraints_as_separate_replayable_alter() {
    let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
    let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
    let pid = std::process::id();
    let schema = format!("dbx_not_valid_check_{pid}");
    let schema_ident = pg_quote_ident(&schema);
    let table = format!("{schema_ident}.events");

    execute_query(&pool, &format!("CREATE SCHEMA {schema_ident}")).await.expect("create schema");
    let client = pool.get().await.expect("get postgres client");
    client
        .batch_execute(&format!(
            "CREATE TABLE {table} (id integer, amount integer); \
                 ALTER TABLE {table} ADD CONSTRAINT c_pos CHECK (amount > 0) NOT VALID"
        ))
        .await
        .expect("create table with unvalidated check constraint");

    let ddl = dbx_core::schema::pg_ddl(&pool, &schema, "events").await.expect("events ddl");

    // A `NOT VALID` CHECK constraint is illegal inline in a `CREATE
    // TABLE` column list — Postgres only accepts it after `ALTER TABLE
    // ADD CONSTRAINT`. Replaying the generated DDL onto a fresh schema
    // exercises that this is real, executable syntax, not just a string
    // match.
    let replay_schema = format!("{schema}_replay");
    let replay_schema_ident = pg_quote_ident(&replay_schema);
    execute_query(&pool, &format!("CREATE SCHEMA {replay_schema_ident}")).await.expect("create replay schema");
    client
        .batch_execute(&ddl.replace(&schema, &replay_schema))
        .await
        .unwrap_or_else(|error| panic!("replay not-valid-check ddl failed: {error:?}; ddl: {ddl}"));

    client
        .batch_execute(&format!("DROP SCHEMA {schema_ident} CASCADE; DROP SCHEMA {replay_schema_ident} CASCADE"))
        .await
        .expect("drop schemas");

    assert!(!ddl.contains("NOT VALID)"), "NOT VALID must not appear inline in the column list: {ddl}");
    assert!(ddl.contains("CREATE TABLE"), "ddl: {ddl}");
    assert!(!ddl.contains("\"amount\" integer,"), "check constraint must not be inlined into columns: {ddl}");
    assert!(ddl.contains("ALTER TABLE"), "ddl: {ddl}");
    assert!(
        ddl.contains("ADD CONSTRAINT \"c_pos\" CHECK (amount > 0) NOT VALID;"),
        "expected a separate ALTER TABLE ADD CONSTRAINT ... NOT VALID statement: {ddl}"
    );
}

#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL database"]
async fn postgres_partition_metadata_renders_replayable_children_and_subpartitions() {
    let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
    let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
    let pid = std::process::id();
    let schema = format!("dbx_partition_meta_{pid}");
    let schema_ident = pg_quote_ident(&schema);
    let replay_schema = format!("{schema}_replay");
    let replay_schema_ident = pg_quote_ident(&replay_schema);
    let parent = format!("{schema_ident}.parent");
    let child = format!("{schema_ident}.child");
    let default_child = format!("{schema_ident}.child_default");
    let subpartition = format!("{schema_ident}.subpartition");
    let inherited_parent = format!("{schema_ident}.inherited_parent");
    let inherited_child = format!("{schema_ident}.inherited_child");
    // Declared in "public" (not the per-test schema) so the same
    // qualified name keeps resolving after `schema_ident` substrings are
    // rewritten to `replay_schema_ident` for the replay pass below.
    let trigger_fn = format!("public.dbx_partition_meta_trigger_fn_{pid}");

    execute_query(&pool, &format!("CREATE SCHEMA {schema_ident}")).await.expect("create schema");
    let client = pool.get().await.expect("get postgres client");
    client
            .batch_execute(&format!(
                "CREATE OR REPLACE FUNCTION {trigger_fn}() RETURNS trigger LANGUAGE plpgsql AS \
                 $$ BEGIN RETURN NEW; END $$; \
                 CREATE TABLE {parent} (id integer, payload text, status text DEFAULT 'pending') PARTITION BY RANGE (id); \
                 CREATE TABLE {child} PARTITION OF {parent} ( \
                   PRIMARY KEY (id), \
                   CONSTRAINT child_payload_not_blank CHECK (payload <> ''), \
                   status WITH OPTIONS DEFAULT 'child-status' \
                 ) FOR VALUES FROM (1) TO (10); \
                 CREATE INDEX child_payload_idx ON {child} (payload); \
                 CREATE TABLE {default_child} PARTITION OF {parent} DEFAULT; \
                 CREATE TABLE {subpartition} PARTITION OF {parent} FOR VALUES FROM (10) TO (20) PARTITION BY HASH (payload); \
                 ALTER TABLE {parent} ADD CONSTRAINT parent_id_positive CHECK (id > 0); \
                 CREATE TRIGGER parent_biu BEFORE INSERT ON {parent} FOR EACH ROW EXECUTE FUNCTION {trigger_fn}(); \
                 CREATE TABLE {inherited_parent} (id integer, bucket integer, PRIMARY KEY (id, bucket)) PARTITION BY RANGE (bucket); \
                 CREATE TABLE {inherited_child} PARTITION OF {inherited_parent} FOR VALUES FROM (1) TO (10)"
            ))
            .await
            .expect("create partitioned tables");

    let parent_info = get_table_partition_info(&pool, &schema, "parent").await.expect("parent metadata");
    let child_info = get_table_partition_info(&pool, &schema, "child").await.expect("child metadata");
    let default_info = get_table_partition_info(&pool, &schema, "child_default").await.expect("default metadata");
    let subpartition_info =
        get_table_partition_info(&pool, &schema, "subpartition").await.expect("subpartition metadata");
    let child_local_objects =
        get_table_partition_local_objects(&pool, &schema, "child").await.expect("child local objects");
    let inherited_child_local_objects = get_table_partition_local_objects(&pool, &schema, "inherited_child")
        .await
        .expect("inherited child local objects");
    let parent_ddl = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "parent").await.expect("parent ddl");
    let child_ddl = dbx_core::schema::pg_ddl(&pool, &schema, "child").await.expect("child ddl");
    let default_ddl = dbx_core::schema::pg_ddl(&pool, &schema, "child_default").await.expect("default ddl");
    let subpartition_ddl = dbx_core::schema::pg_ddl(&pool, &schema, "subpartition").await.expect("subpartition ddl");
    let inherited_parent_ddl = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "inherited_parent")
        .await
        .expect("inherited parent ddl");
    let inherited_child_ddl =
        dbx_core::schema::pg_ddl(&pool, &schema, "inherited_child").await.expect("inherited child ddl");
    // The bare (non-recursive) parent DDL, exactly what database export
    // and table transfer produce per-relation — used below to give the
    // single-relation partition ddls (child_ddl, default_ddl,
    // subpartition_ddl, inherited_child_ddl) a real parent shell to
    // replay against, so their syntax is actually exercised by
    // PostgreSQL rather than only checked with string assertions.
    let bare_parent_ddl = dbx_core::schema::pg_ddl(&pool, &schema, "parent").await.expect("bare parent ddl");
    let bare_inherited_parent_ddl =
        dbx_core::schema::pg_ddl(&pool, &schema, "inherited_parent").await.expect("bare inherited parent ddl");

    // `parent_ddl`/`inherited_parent_ddl` already embed their full
    // partition subtree (that's the fix under test), so replaying the
    // per-partition ddl strings on top would recreate the same relations
    // twice. Only the two top-level requests need replaying; the
    // individually-fetched partition ddls above are replayed separately
    // below, on top of a bare (childless) copy of their parent.
    execute_query(&pool, &format!("CREATE SCHEMA {replay_schema_ident}")).await.expect("create replay schema");
    for ddl in [&parent_ddl, &inherited_parent_ddl] {
        client
            .batch_execute(&ddl.replace(&schema, &replay_schema))
            .await
            .unwrap_or_else(|error| panic!("replay partition ddl failed: {error:?}; ddl: {ddl}"));
    }

    // Single-relation partition ddls are now a real production code
    // path (database export/table transfer fetch each partition this
    // way), so replay them for real too: a fresh schema gets just the
    // bare parents, then each single-relation partition ddl on top.
    let bare_schema = format!("{schema}_bare");
    let bare_schema_ident = pg_quote_ident(&bare_schema);
    execute_query(&pool, &format!("CREATE SCHEMA {bare_schema_ident}")).await.expect("create bare schema");
    for ddl in [&bare_parent_ddl, &bare_inherited_parent_ddl] {
        client
            .batch_execute(&ddl.replace(&schema, &bare_schema))
            .await
            .unwrap_or_else(|error| panic!("replay bare parent ddl failed: {error:?}; ddl: {ddl}"));
    }
    for ddl in [&child_ddl, &default_ddl, &subpartition_ddl, &inherited_child_ddl] {
        client
            .batch_execute(&ddl.replace(&schema, &bare_schema))
            .await
            .unwrap_or_else(|error| panic!("replay single-relation partition ddl failed: {error:?}; ddl: {ddl}"));
    }

    client
        .batch_execute(&format!(
            "DROP SCHEMA {schema_ident} CASCADE; DROP SCHEMA {replay_schema_ident} CASCADE; \
                 DROP SCHEMA {bare_schema_ident} CASCADE; DROP FUNCTION IF EXISTS {trigger_fn}()"
        ))
        .await
        .expect("drop schemas and trigger function");

    assert_eq!(parent_info.key.as_deref(), Some("RANGE (id)"));
    assert!(!parent_info.is_partition);
    assert_eq!(child_info.parent_schema.as_deref(), Some(schema.as_str()));
    assert_eq!(child_info.parent_table.as_deref(), Some("parent"));
    assert_eq!(child_info.bound.as_deref(), Some("FOR VALUES FROM (1) TO (10)"));
    assert!(child_local_objects.has_primary_key);
    assert!(child_local_objects.indexes.contains("child_payload_idx"));
    assert!(child_local_objects.check_constraints.contains("child_payload_not_blank"));
    assert!(!child_local_objects.check_constraints.contains("parent_id_positive"));
    assert_eq!(child_local_objects.column_defaults.get("status"), Some(&PostgresColumnDefaultState::Overridden));
    assert!(!inherited_child_local_objects.has_primary_key);
    assert!(inherited_child_local_objects.indexes.is_empty());
    assert_eq!(default_info.bound.as_deref(), Some("DEFAULT"));
    assert_eq!(subpartition_info.key.as_deref(), Some("HASH (payload)"));
    assert!(parent_ddl.contains(") PARTITION BY RANGE (id);"), "ddl: {parent_ddl}");
    assert!(parent_ddl.contains("CONSTRAINT \"parent_id_positive\" CHECK (id > 0)"), "ddl: {parent_ddl}");
    // The parent's trigger is automatically cloned onto every partition
    // by PostgreSQL itself when the partition is created/attached, so it
    // must appear exactly once in the combined DDL, not once per relation.
    assert_eq!(parent_ddl.matches("CREATE TRIGGER").count(), 1, "ddl: {parent_ddl}");
    assert!(child_ddl.contains("CREATE TABLE"), "ddl: {child_ddl}");
    assert!(child_ddl.contains("PARTITION OF"), "ddl: {child_ddl}");
    assert!(child_ddl.contains("PRIMARY KEY (\"id\")"), "ddl: {child_ddl}");
    assert!(child_ddl.contains("FOR VALUES FROM (1) TO (10);"), "ddl: {child_ddl}");
    assert!(child_ddl.contains("CREATE INDEX \"child_payload_idx\""), "ddl: {child_ddl}");
    assert!(child_ddl.contains("CONSTRAINT \"child_payload_not_blank\" CHECK (payload"), "ddl: {child_ddl}");
    assert!(!child_ddl.contains("parent_id_positive"), "ddl: {child_ddl}");
    assert!(child_ddl.contains("\"status\" WITH OPTIONS DEFAULT 'child-status'"), "ddl: {child_ddl}");
    assert!(!child_ddl.contains("CREATE TRIGGER"), "child must not redeclare the parent's cloned trigger: {child_ddl}");
    assert!(default_ddl.contains(" DEFAULT;"), "ddl: {default_ddl}");
    assert!(
        subpartition_ddl.contains("FOR VALUES FROM (10) TO (20) PARTITION BY HASH (payload);"),
        "ddl: {subpartition_ddl}"
    );
    assert!(!inherited_child_ddl.contains("PRIMARY KEY"), "ddl: {inherited_child_ddl}");
    assert!(!inherited_child_ddl.contains("CREATE INDEX"), "ddl: {inherited_child_ddl}");
}

#[tokio::test]
#[ignore = "requires DBX_TEST_POSTGRES_URL pointing at a writable PostgreSQL database with postgres_fdw available"]
async fn postgres_partition_ddl_uses_foreign_table_syntax_for_foreign_partitions() {
    let url = std::env::var("DBX_TEST_POSTGRES_URL").expect("DBX_TEST_POSTGRES_URL");
    let pool = connect(&url, Duration::from_secs(5)).await.expect("connect postgres");
    let pid = std::process::id();
    let schema = format!("dbx_partition_fdw_{pid}");
    let schema_ident = pg_quote_ident(&schema);
    let server = format!("dbx_partition_fdw_server_{pid}");
    let server_ident = pg_quote_ident(&server);
    let replay_schema = format!("{schema}_replay");
    let replay_schema_ident = pg_quote_ident(&replay_schema);
    let parent = format!("{schema_ident}.parent");
    let local_child = format!("{schema_ident}.local_child");
    let foreign_child = format!("{schema_ident}.foreign_child");

    execute_query(&pool, &format!("CREATE SCHEMA {schema_ident}")).await.expect("create schema");
    let client = pool.get().await.expect("get postgres client");
    client
        .batch_execute(&format!(
            "CREATE EXTENSION IF NOT EXISTS postgres_fdw; \
                 CREATE SERVER {server_ident} FOREIGN DATA WRAPPER postgres_fdw \
                   OPTIONS (host 'localhost', dbname 'postgres', port '5432'); \
                 CREATE USER MAPPING FOR CURRENT_USER SERVER {server_ident} \
                   OPTIONS (user 'postgres', password 'postgres'); \
                 CREATE TABLE {parent} (id integer, payload text) PARTITION BY RANGE (id); \
                 CREATE TABLE {local_child} PARTITION OF {parent} FOR VALUES FROM (1) TO (10); \
                 CREATE FOREIGN TABLE {foreign_child} PARTITION OF {parent} \
                   FOR VALUES FROM (10) TO (20) \
                   SERVER {server_ident} OPTIONS (schema_name 'public', table_name 'foreign_child_data')"
        ))
        .await
        .expect("create partitioned tables with a foreign partition");

    let foreign_info = get_table_partition_info(&pool, &schema, "foreign_child").await.expect("foreign child info");
    let local_info = get_table_partition_info(&pool, &schema, "local_child").await.expect("local child info");
    let parent_ddl = dbx_core::schema::pg_ddl_with_partitions(&pool, &schema, "parent").await.expect("parent ddl");
    let foreign_ddl = dbx_core::schema::pg_ddl(&pool, &schema, "foreign_child").await.expect("foreign child ddl");

    execute_query(&pool, &format!("CREATE SCHEMA {replay_schema_ident}")).await.expect("create replay schema");
    client
        .batch_execute(&parent_ddl.replace(&schema, &replay_schema))
        .await
        .unwrap_or_else(|error| panic!("replay foreign partition ddl failed: {error:?}; ddl: {parent_ddl}"));

    client
        .batch_execute(&format!(
            "DROP SCHEMA {schema_ident} CASCADE; DROP SCHEMA {replay_schema_ident} CASCADE; \
                 DROP SERVER {server_ident} CASCADE"
        ))
        .await
        .expect("drop schemas and foreign server");

    assert!(!local_info.is_foreign);
    assert!(foreign_info.is_foreign);
    assert_eq!(foreign_info.foreign_server.as_deref(), Some(server.as_str()));
    assert!(
        foreign_info.foreign_options.contains(&("schema_name".to_string(), "public".to_string())),
        "options: {:?}",
        foreign_info.foreign_options
    );
    assert!(parent_ddl.contains("CREATE FOREIGN TABLE"), "ddl: {parent_ddl}");
    assert!(foreign_ddl.starts_with("CREATE FOREIGN TABLE"), "ddl: {foreign_ddl}");
    assert!(foreign_ddl.contains("PARTITION OF"), "ddl: {foreign_ddl}");
    assert!(foreign_ddl.contains(&format!("SERVER {}", pg_quote_ident(&server))), "ddl: {foreign_ddl}");
    assert!(foreign_ddl.contains("\"schema_name\" 'public'"), "ddl: {foreign_ddl}");
    assert!(foreign_ddl.contains("\"table_name\" 'foreign_child_data'"), "ddl: {foreign_ddl}");
    assert!(!foreign_ddl.contains("PRIMARY KEY"), "ddl: {foreign_ddl}");
    assert!(!foreign_ddl.contains("CREATE INDEX"), "ddl: {foreign_ddl}");
}