uqa-engine 0.6.3

Engine: schema-aware table store, catalog restore, transactions
Documentation
//
// Unified Query Algebra
//
// Copyright (c) 2023-2026 Cognica, Inc.
//

use super::*;
use uqa_execution::mutation::{insert::table::run_table_insert, insert::table::InsertPlanning};
use uqa_sql::plan::{CommandPlan, InsertPlan, RelationalPlan};

fn insert(sql: &str) -> Box<InsertPlan> {
    let UnifiedPlan::Command(command) =
        UnifiedPlan::lower(uqa_sql::compile(sql).unwrap().remove(0))
    else {
        panic!("command");
    };
    let CommandPlan::Insert(insert) = *command else {
        panic!("insert");
    };
    insert
}

#[rstest::rstest]
#[case::memory(Provider::Memory)]
#[case::sqlite_native(Provider::SQLiteNative)]
#[case::sqlite_kv(Provider::SQLiteKeyValue)]
#[case::redb(Provider::Redb)]
fn native_conflict_inference_admits_the_complete_private_command(#[case] provider: Provider) {
    let directory = tempfile::tempdir().unwrap();
    let engine = provider.open(&directory.path().join("conflict-inference-owner.db"));
    engine.sql("CREATE TABLE items(id integer); CREATE UNIQUE INDEX positive_id ON items(id) WHERE id>0", &[]).unwrap();
    let sql =
        "INSERT INTO public.items VALUES(1) ON CONFLICT(id) WHERE id>0 DO NOTHING RETURNING id";
    let mut statement = insert(sql);
    let UnifiedPlan::Query(mut unused) =
        UnifiedPlan::lower(uqa_sql::compile("SELECT 1").unwrap().remove(0))
    else {
        panic!("query");
    };
    let RelationalPlan::QueryBlock(block) = &mut unused.root else {
        panic!("block");
    };
    block.projections[0].expr = uqa_sql::ScalarExpr::Literal(Value::Str("x".repeat(4 << 20)));
    statement.subqueries.push(*unused);
    let resources = engine.query_retention_control().unwrap();
    let memory = resources.memory();
    {
        let context = engine.mutation_statement_context();
        let baseline = memory.used();
        let occupied = memory.reserve(memory.available() - (64 << 10)).unwrap();
        let mut outcome = None;
        let measured = allocation_counter::measure(|| {
            outcome = Some(
                run_table_insert(
                    &context,
                    InsertPlanning {
                        inference: engine.inference_context(),
                        returning: engine.returning_analysis_context(),
                        prune_source_outputs:
                            uqa_planner::mutation_outputs::prune_unused_query_outputs_owned,
                    },
                    &statement,
                    &[],
                    None,
                )
                .map(drop),
            );
        });
        assert!(
            measured.bytes_total < 256 << 10,
            "{provider:?}: {measured:?}"
        );
        assert_eq!(outcome.unwrap().unwrap_err().sqlstate(), Some("53200"));
        assert_eq!(memory.used(), baseline + occupied.bytes());
        drop(occupied);
    }
    let rows = engine.sql(sql, &[]).unwrap();
    assert_eq!(rows.value_at(0, 0), Some(&Value::Int(1)));
    drop(rows);
    assert!(engine.sql(sql, &[]).unwrap().rows.is_empty());
    engine.close().unwrap();
    drop(engine);
    assert_eq!(memory.used(), 0);
}

#[rstest::rstest]
#[case::memory(Provider::Memory)]
#[case::sqlite_native(Provider::SQLiteNative)]
#[case::sqlite_kv(Provider::SQLiteKeyValue)]
#[case::redb(Provider::Redb)]
fn native_conflict_metadata_borrows_defaults_and_releases_catalog_guards(
    #[case] provider: Provider,
) {
    use uqa_core::{memory::ProductionControl, RelationIdentity};
    use uqa_sql::semantics::conflict::{prepare_inference_predicate_with_control, ConflictCatalog};
    let directory = tempfile::tempdir().unwrap();
    let engine = provider.open(&directory.path().join("conflict-metadata-owner.db"));
    engine
        .sql("CREATE TABLE items(id integer, payload text)", &[])
        .unwrap();
    let statement =
        insert("INSERT INTO public.items VALUES(1,'x') ON CONFLICT(id) WHERE id>0 DO NOTHING");
    let resources = engine.query_retention_control().unwrap();
    let memory = resources.memory();
    let runtime = engine.query_runtime_view();
    let control = ProductionControl::new(memory, resources.cancellation(), runtime.cancellation);
    drop(
        prepare_inference_predicate_with_control(
            engine.inference_context(),
            &statement,
            &[],
            &control,
        )
        .unwrap(),
    );
    let table = engine.storage.tables.read()[&RelationIdentity::new("public", "items")].clone();
    table.columns.write()[1].default =
        Some(uqa_sql::ast::Expr::Literal(Value::Str("x".repeat(4 << 20))));
    let baseline = memory.used();
    let occupied = memory.reserve(memory.available() - (64 << 10)).unwrap();
    let mut output = None;
    let measured = allocation_counter::measure(|| {
        output = Some(
            prepare_inference_predicate_with_control(
                engine.inference_context(),
                &statement,
                &[],
                &control,
            )
            .unwrap()
            .unwrap(),
        );
    });
    assert!(
        measured.bytes_total < 128 << 10,
        "{provider:?}: {measured:?}"
    );
    let output = output.unwrap();
    assert_eq!(
        output.reserved_bytes(),
        usize::try_from(measured.bytes_current).unwrap(),
        "{provider:?}: {measured:?}"
    );
    assert_eq!(
        memory.used(),
        baseline + occupied.bytes() + output.reserved_bytes()
    );
    drop(output);
    assert_eq!(memory.used(), baseline + occupied.bytes());
    for token in [resources.cancellation(), runtime.cancellation] {
        token.cancel();
        assert_eq!(
            prepare_inference_predicate_with_control(
                engine.inference_context(),
                &statement,
                &[],
                &control
            )
            .unwrap_err()
            .sqlstate(),
            Some("57014")
        );
        assert_eq!(memory.used(), baseline + occupied.bytes());
        token.reset();
    }
    drop(occupied);
    table.columns.write()[1].default = None;
    assert!(engine
        .with_column_metadata_with_control("public.items", &control, &mut |columns| {
            assert!(!table.columns.is_locked());
            table.columns.write()[1].name = "changed".into();
            assert_eq!(columns[1].name, "payload");
            Ok(())
        })
        .unwrap());
    table.columns.write()[1].name = "payload".into();
    drop(table);
    engine
        .sql("INSERT INTO items VALUES(1,'x'); SELECT * FROM items", &[])
        .unwrap();
    engine.close().unwrap();
    drop(engine);
    assert_eq!(memory.used(), 0);
}

#[rstest::rstest]
#[case::memory(Provider::Memory)]
#[case::sqlite_native(Provider::SQLiteNative)]
#[case::sqlite_kv(Provider::SQLiteKeyValue)]
#[case::redb(Provider::Redb)]
fn native_conflict_metadata_preserves_sequence_dependency_observation(#[case] provider: Provider) {
    use uqa_core::{memory::ProductionControl, RelationIdentity};
    use uqa_sql::semantics::conflict::ConflictCatalog;
    let directory = tempfile::tempdir().unwrap();
    let engine = provider.open(&directory.path().join("conflict-sequence-observation.db"));
    engine.sql("CREATE SEQUENCE inference_seq; CREATE TABLE items(id integer, n bigint DEFAULT nextval('inference_seq')); ALTER SEQUENCE inference_seq RENAME TO renamed_seq", &[]).unwrap();
    let resources = engine.query_retention_control().unwrap();
    let memory = resources.memory();
    let runtime = engine.query_runtime_view();
    let control = ProductionControl::new(memory, resources.cancellation(), runtime.cancellation);
    let baseline = memory.used();
    assert!(engine
        .with_column_metadata_with_control("public.items", &control, &mut |columns| {
            assert_eq!(columns.len(), 2);
            Ok(())
        })
        .unwrap());
    let table = engine.storage.tables.read()[&RelationIdentity::new("public", "items")].clone();
    let original = table.columns.read()[1].default.clone();
    let uqa_sql::Statement::CreateTable(mut missing) =
        uqa_sql::compile("CREATE TABLE t(n bigint DEFAULT nextval('missing_sequence'))")
            .unwrap()
            .remove(0)
    else {
        panic!("table")
    };
    table.columns.write()[1].default = missing.columns.remove(0).default;
    let ordinary = engine
        .describe_table_in_execution("public.items")
        .unwrap_err();
    let controlled = engine
        .with_column_metadata_with_control("public.items", &control, &mut |_| {
            panic!("dependency error precedes metadata use")
        })
        .unwrap_err();
    assert_eq!(
        controlled.to_string(),
        uqa_sql::SQLError::Internal(ordinary.to_string()).to_string()
    );
    assert_eq!(memory.used(), baseline);
    table.columns.write()[1].default = original;
    drop(table);
    assert_eq!(
        engine
            .sql("INSERT INTO items(id) VALUES(1) RETURNING n", &[])
            .unwrap()
            .value_at(0, 0),
        Some(&Value::Int(1))
    );
    engine.close().unwrap();
    drop(engine);
    assert_eq!(memory.used(), 0);
}

#[rstest::rstest]
#[case::memory(Provider::Memory)]
#[case::sqlite_native(Provider::SQLiteNative)]
#[case::sqlite_kv(Provider::SQLiteKeyValue)]
#[case::redb(Provider::Redb)]
fn native_conflict_inference_preserves_postgresql_binding_effects_and_reopen(
    #[case] provider: Provider,
) {
    let directory = tempfile::tempdir().unwrap();
    let path = directory.path().join("conflict-inference-reference.db");
    let engine = provider.open(&path);
    engine.sql("CREATE SEQUENCE inference_sequence; CREATE TABLE conflict_sources(email text COLLATE \"C\",tenant integer,active boolean,n integer,marker bigint DEFAULT nextval('inference_sequence')); CREATE UNIQUE INDEX folded_active ON conflict_sources(lower(email),tenant) WHERE active", &[]).unwrap();
    for sql in [
        "INSERT INTO conflict_sources(email,tenant,active,n) VALUES('One',1,true,3) RETURNING email,n,marker",
        "INSERT INTO conflict_sources AS target(email,tenant,active,n) VALUES('ONE',1,true,5) ON CONFLICT(lower(email),tenant) WHERE active AND tenant>0 DO UPDATE SET n=excluded.n RETURNING email,n,marker",
        "INSERT INTO conflict_sources(email,tenant,active,n) VALUES('Two',2,true,7) ON CONFLICT(lower(email),tenant) WHERE active DO NOTHING RETURNING email,n,marker",
        "EXPLAIN(COSTS OFF) INSERT INTO conflict_sources(email,tenant,active,n) VALUES('ONE',1,true,9) ON CONFLICT(lower(email),tenant) WHERE active DO NOTHING",
    ] { engine.sql(sql, &[]).unwrap(); }
    assert_eq!(
        engine
            .sql("SELECT last_value FROM inference_sequence", &[])
            .unwrap()
            .value_at(0, 0),
        Some(&Value::Int(3))
    );
    engine.sql("PREPARE inferred(text,integer) AS INSERT INTO conflict_sources(email,tenant,active,n) VALUES($1,$2,true,4) ON CONFLICT(lower(email),tenant) WHERE active DO UPDATE SET n=excluded.n RETURNING email,n,marker; EXECUTE inferred('TWO',2); CREATE VIEW inference_view AS SELECT email AS address,tenant AS account,active,n FROM conflict_sources; INSERT INTO inference_view(address,account,active,n) VALUES('ONE',1,true,8) ON CONFLICT((account::integer),lower(address)) WHERE active DO UPDATE SET n=excluded.n RETURNING address,n", &[]).unwrap();
    engine.sql("BEGIN; SAVEPOINT rejected", &[]).unwrap();
    for (target, predicate, state) in [
        ("upper(email),tenant", " WHERE active", "42P10"),
        ("lower(email),tenant", "", "42P10"),
        ("lower(email),tenant", " WHERE (SELECT true)", "0A000"),
        ("lower(email),tenant", " WHERE sum(tenant)>0", "42803"),
        (
            "lower(email),tenant",
            " WHERE row_number() OVER()>0",
            "42P20",
        ),
        ("lower(email),tenant", " WHERE missing>0", "42703"),
    ] {
        let sql = format!("INSERT INTO conflict_sources(email,tenant,active,n) VALUES('x',3,true,1) ON CONFLICT({target}){predicate} DO NOTHING");
        let error = engine.sql(&sql, &[]).unwrap_err();
        assert_eq!(
            error.sqlstate(),
            Some(state),
            "{provider:?}: {sql}: {error}"
        );
        engine.sql("ROLLBACK TO rejected", &[]).unwrap();
    }
    engine.sql("COMMIT; ALTER SEQUENCE inference_sequence RENAME TO renamed_inference_sequence; EXPLAIN(COSTS OFF) INSERT INTO conflict_sources(email,tenant,active,n) VALUES('THREE',3,true,6) ON CONFLICT(lower(email),tenant) WHERE active DO NOTHING", &[]).unwrap();
    assert_eq!(
        engine
            .sql("SELECT last_value FROM renamed_inference_sequence", &[])
            .unwrap()
            .value_at(0, 0),
        Some(&Value::Int(5))
    );
    engine.sql("INSERT INTO conflict_sources(email,tenant,active,n) VALUES('Three',3,true,6) ON CONFLICT(lower(email),tenant) WHERE active DO NOTHING RETURNING email,n,marker; DEALLOCATE inferred", &[]).unwrap();
    check_conflict_rows(&engine);
    engine.close().unwrap();
    drop(engine);
    if !matches!(provider, Provider::Memory) {
        let engine = provider.open(&path);
        check_conflict_rows(&engine);
        engine.close().unwrap();
    }
}

fn check_conflict_rows(engine: &Engine) {
    let rows = engine
        .sql(
            "SELECT email,tenant,n,marker FROM conflict_sources ORDER BY tenant",
            &[],
        )
        .unwrap();
    assert_eq!(rows.rows.len(), 3);
    for (row, (email, tenant, n, marker)) in
        [("One", 1, 8, 1), ("Two", 2, 4, 3), ("Three", 3, 6, 6)]
            .into_iter()
            .enumerate()
    {
        assert_eq!(rows.value_at(row, 0), Some(&Value::Str(email.into())));
        assert_eq!(rows.value_at(row, 1), Some(&Value::Int(tenant)));
        assert_eq!(rows.value_at(row, 2), Some(&Value::Int(n)));
        assert_eq!(rows.value_at(row, 3), Some(&Value::Int(marker)));
    }
    assert_eq!(
        engine
            .sql("SELECT last_value FROM renamed_inference_sequence", &[])
            .unwrap()
            .value_at(0, 0),
        Some(&Value::Int(6))
    );
}