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。

11.2 KB
Raw Blame
use std::time::Duration;

use dbx_core::db::postgres;
use dbx_core::models::connection::DatabaseType;
use dbx_core::table_structure_sql::{
    build_table_structure_change_sql, EditableStructureIndex, TableStructureSqlOptions,
};

fn live_postgres_url() -> String {
    let host = std::env::var("DBX_LIVE_POSTGRES_HOST").unwrap_or_else(|_| "127.0.0.1".to_string());
    let port = std::env::var("DBX_LIVE_POSTGRES_PORT").ok().and_then(|value| value.parse().ok()).unwrap_or(5432);
    let user = std::env::var("DBX_LIVE_POSTGRES_USER").unwrap_or_else(|_| "postgres".to_string());
    let password = std::env::var("DBX_LIVE_POSTGRES_PASSWORD").unwrap_or_default();
    let database = std::env::var("DBX_LIVE_POSTGRES_DATABASE").unwrap_or_else(|_| "postgres".to_string());
    format!("postgresql://{user}:{password}@{host}:{port}/{database}")
}

fn concurrent_index(columns: Vec<&str>) -> EditableStructureIndex {
    EditableStructureIndex {
        id: "idx_users_email".to_string(),
        name: "idx_users_email".to_string(),
        columns: columns.into_iter().map(str::to_string).collect(),
        is_unique: false,
        is_primary: false,
        filter: String::new(),
        index_type: String::new(),
        included_columns: Vec::new(),
        column_opclasses: Vec::new(),
        comment: String::new(),
        concurrently: true,
        original: None,
        oracle_original_missing: false,
        marked_for_drop: false,
    }
}

/// `CREATE INDEX CONCURRENTLY` generated by the table structure editor must
/// succeed on a real PostgreSQL server and leave a valid index
/// (`pg_index.indisvalid = true`).
#[tokio::test]
#[ignore = "requires DBX_LIVE_POSTGRES_HOST/PORT/USER/PASSWORD/DATABASE pointing at a writable PostgreSQL database"]
async fn live_postgres_concurrent_index_builds_valid_index() {
    let pool = postgres::connect(&live_postgres_url(), Duration::from_secs(10)).await.expect("connect PostgreSQL");
    let suffix = uuid::Uuid::new_v4().simple().to_string();
    let schema = format!("dbx_cic_{}", &suffix[..8]);
    let cleanup = vec![format!("DROP SCHEMA IF EXISTS \"{schema}\" CASCADE")];
    let _ = postgres::execute_batch(&pool, &cleanup).await;
    let setup = vec![
        format!("CREATE SCHEMA \"{schema}\""),
        format!("CREATE TABLE \"{schema}\".users (id integer PRIMARY KEY, email text, name text, status text)"),
    ];
    postgres::execute_batch(&pool, &setup).await.expect("create live test schema");

    let result = build_table_structure_change_sql(TableStructureSqlOptions {
        database_type: Some(DatabaseType::Postgres),
        driver_profile: None,
        schema: Some(schema.clone()),
        table_name: "users".to_string(),
        columns: Vec::new(),
        indexes: vec![concurrent_index(vec!["email"])],
        foreign_keys: Vec::new(),
        triggers: Vec::new(),
        table_comment: None,
        original_table_comment: None,
        mysql_engine: None,
        mysql_auto_increment_value: None,
        transwarp_create: None,
        partitioned: false,
        foreign_table: false,
        is_gaussdb_m_mode: false,
        table_collation: None,
        primary_key_name: None,
        primary_key_change: None,
        oracle_foreign_key_changes: None,
        oracle_constraint_changes: None,
    });
    assert!(result.warnings.is_empty(), "{:?}", result.warnings);
    assert_eq!(
        result.statements,
        vec![format!("CREATE INDEX CONCURRENTLY \"idx_users_email\" ON \"{schema}\".\"users\" (\"email\");")]
    );

    postgres::execute_batch(&pool, &result.statements).await.expect("execute concurrent index build");
    let client = pool.get().await.expect("checkout connection");
    let row = client
        .query_one(
            "SELECT indisvalid FROM pg_index WHERE indexrelid = to_regclass($1)",
            &[&format!("{schema}.idx_users_email")],
        )
        .await
        .expect("read pg_index");
    let valid: bool = row.get(0);
    assert!(valid, "concurrently-built index must be valid");

    postgres::execute_batch(&pool, &cleanup).await.expect("cleanup live test schema");
}

/// PostgreSQL forbids `CREATE INDEX CONCURRENTLY` inside an explicit
/// transaction block; the editor relies on per-statement autocommit, so the
/// generated statement must only ever run outside `BEGIN`.
#[tokio::test]
#[ignore = "requires DBX_LIVE_POSTGRES_HOST/PORT/USER/PASSWORD/DATABASE pointing at a writable PostgreSQL database"]
async fn live_postgres_concurrent_index_rejects_transaction_block() {
    let pool = postgres::connect(&live_postgres_url(), Duration::from_secs(10)).await.expect("connect PostgreSQL");
    let suffix = uuid::Uuid::new_v4().simple().to_string();
    let schema = format!("dbx_cic_{}", &suffix[..8]);
    let cleanup = vec![format!("DROP SCHEMA IF EXISTS \"{schema}\" CASCADE")];
    let _ = postgres::execute_batch(&pool, &cleanup).await;
    let setup = vec![
        format!("CREATE SCHEMA \"{schema}\""),
        format!("CREATE TABLE \"{schema}\".users (id integer PRIMARY KEY, email text)"),
    ];
    postgres::execute_batch(&pool, &setup).await.expect("create live test schema");

    let client = pool.get().await.expect("checkout connection");
    client.batch_execute("BEGIN").await.expect("begin transaction");
    let err = client
        .batch_execute(&format!("CREATE INDEX CONCURRENTLY \"idx_users_email\" ON \"{schema}\".\"users\" (\"email\")"))
        .await
        .expect_err("CREATE INDEX CONCURRENTLY must fail inside a transaction block");
    let _ = client.batch_execute("ROLLBACK").await;
    let message = err.as_db_error().map(|db_error| db_error.message().to_string()).unwrap_or_else(|| err.to_string());
    assert!(
        message.contains("cannot run inside a transaction block"),
        "expected transaction-block error, got: {message}"
    );

    postgres::execute_batch(&pool, &cleanup).await.expect("cleanup live test schema");
}

/// A cancelled `CREATE INDEX CONCURRENTLY` leaves a same-name INVALID index
/// behind (`pg_index.indisvalid = false`); `list_invalid_indexes` must report
/// it so the editor can block a doomed retry and point the user at the fix.
#[tokio::test]
#[ignore = "requires DBX_LIVE_POSTGRES_HOST/PORT/USER/PASSWORD/DATABASE pointing at a writable PostgreSQL database"]
async fn live_postgres_invalid_index_leftover_is_detected() {
    let pool = postgres::connect(&live_postgres_url(), Duration::from_secs(10)).await.expect("connect PostgreSQL");
    let suffix = uuid::Uuid::new_v4().simple().to_string();
    let schema = format!("dbx_cic_{}", &suffix[..8]);
    let cleanup = vec![format!("DROP SCHEMA IF EXISTS \"{schema}\" CASCADE")];
    let _ = postgres::execute_batch(&pool, &cleanup).await;
    let setup = vec![
        format!("CREATE SCHEMA \"{schema}\""),
        format!("CREATE TABLE \"{schema}\".users (id integer PRIMARY KEY, email text)"),
        // Simulate the leftover of a cancelled concurrent build. PostgreSQL
        // does not accept a schema-qualified index name in CREATE INDEX; the
        // index inherits the table's schema, so only the table is qualified.
        format!("CREATE INDEX \"idx_users_email\" ON \"{schema}\".\"users\" (\"email\")"),
    ];
    postgres::execute_batch(&pool, &setup).await.expect("create live test schema");

    let invalid = postgres::list_invalid_indexes(&pool, &schema, "users").await.expect("list invalid indexes");
    assert!(invalid.is_empty(), "fresh index must be valid, got: {invalid:?}");

    // Flip indisvalid to false the same way an interrupted concurrent build does.
    let client = pool.get().await.expect("checkout connection");
    client
        .batch_execute(&format!(
            "UPDATE pg_index SET indisvalid = false WHERE indexrelid = to_regclass('{schema}.idx_users_email')"
        ))
        .await
        .expect("mark index invalid");

    let invalid = postgres::list_invalid_indexes(&pool, &schema, "users").await.expect("list invalid indexes");
    assert_eq!(invalid, vec!["idx_users_email".to_string()], "invalid leftover must be reported");

    postgres::execute_batch(&pool, &cleanup).await.expect("cleanup live test schema");
}

/// The structure editor must never emit `CREATE INDEX CONCURRENTLY` for a
/// partitioned parent table — the builder rejects the request up front
/// (fail closed) and no SQL reaches the server at all, instead of downgrading
/// to a blocking `CREATE INDEX`.
#[tokio::test]
#[ignore = "requires DBX_LIVE_POSTGRES_HOST/PORT/USER/PASSWORD/DATABASE pointing at a writable PostgreSQL database"]
async fn live_postgres_partitioned_parent_concurrent_request_rejected() {
    let pool = postgres::connect(&live_postgres_url(), Duration::from_secs(10)).await.expect("connect PostgreSQL");
    let suffix = uuid::Uuid::new_v4().simple().to_string();
    let schema = format!("dbx_cic_{}", &suffix[..8]);
    let cleanup = vec![format!("DROP SCHEMA IF EXISTS \"{schema}\" CASCADE")];
    let _ = postgres::execute_batch(&pool, &cleanup).await;
    let setup = vec![
        format!("CREATE SCHEMA \"{schema}\""),
        format!(
            "CREATE TABLE \"{schema}\".events (id integer, created_at date) PARTITION BY RANGE (created_at)"
        ),
        format!("CREATE TABLE \"{schema}\".events_2026 PARTITION OF \"{schema}\".events FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')"),
    ];
    postgres::execute_batch(&pool, &setup).await.expect("create live test schema");

    let mut idx = concurrent_index(vec!["id"]);
    idx.name = "idx_events_id".to_string();
    idx.id = "idx_events_id".to_string();
    let result = build_table_structure_change_sql(TableStructureSqlOptions {
        database_type: Some(DatabaseType::Postgres),
        driver_profile: None,
        schema: Some(schema.clone()),
        table_name: "events".to_string(),
        columns: Vec::new(),
        indexes: vec![idx],
        foreign_keys: Vec::new(),
        triggers: Vec::new(),
        table_comment: None,
        original_table_comment: None,
        mysql_engine: None,
        mysql_auto_increment_value: None,
        transwarp_create: None,
        partitioned: true,
        foreign_table: false,
        is_gaussdb_m_mode: false,
        table_collation: None,
        primary_key_name: None,
        primary_key_change: None,
        oracle_foreign_key_changes: None,
        oracle_constraint_changes: None,
    });
    assert_eq!(
        result.warnings,
        vec![
            "CREATE INDEX CONCURRENTLY is not supported on PostgreSQL partitioned parent tables. Create indexes concurrently on individual partitions and attach them separately."
        ]
    );
    assert!(
        result.statements.is_empty(),
        "no SQL may be generated for an unsupported concurrent request, got: {:?}",
        result.statements
    );
    // Nothing was executed: the partitioned parent still has no such index.
    let client = pool.get().await.expect("checkout connection");
    let row = client
        .query_one(
            "SELECT count(*) FROM pg_catalog.pg_class WHERE relname = $1 AND relnamespace = to_regnamespace($2)",
            &[&"idx_events_id", &schema],
        )
        .await
        .expect("count index relations");
    let count: i64 = row.get(0);
    assert_eq!(count, 0, "rejected request must not create any index on the server");

    postgres::execute_batch(&pool, &cleanup).await.expect("cleanup live test schema");
}