Files
craft-anything/crates/formats/tests/sqlite_adjacent_table_constraints.rs

91 lines
4.4 KiB
Rust

use craft_anything_formats::sqlite::{CellValue, SqliteDb};
use craft_anything_testkit::sqlite::{Val, table_db, without_rowid_db};
/// Verify a rowid-alias table reads the B-tree rowid into its INTEGER primary key column.
fn assert_rowid_alias(sql: &str) {
let bytes = table_db(sql, &[(42, vec![Val::Null, Val::Text("ok".into())])]);
let rows = SqliteDb::parse(&bytes).unwrap().read_rows(2, 0, 10).rows;
assert_eq!(rows.len(), 1, "{sql}");
assert_eq!(rows[0].values, [CellValue::Int(42), CellValue::Text { text: "ok".into(), truncated: false }], "{sql}");
}
/// Verify a WITHOUT ROWID table maps key-first physical records back to declaration order.
fn assert_without_rowid(sql: &str) {
let bytes = without_rowid_db(sql, &[vec![Val::Text("key".into()), Val::Text("value".into())]]);
let rows = SqliteDb::parse(&bytes).unwrap().read_rows(2, 0, 10).rows;
assert_eq!(rows.len(), 1, "{sql}");
assert_eq!(rows[0].values, [CellValue::Text { text: "value".into(), truncated: false }, CellValue::Text { text: "key".into(), truncated: false }], "{sql}");
}
/// A table constraint preceded by UNIQUE without a comma still identifies a rowid alias.
#[test]
fn rowid_alias_after_unique_without_comma() {
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, UNIQUE(v) PRIMARY KEY(id))");
}
/// A PRIMARY KEY followed by UNIQUE without a comma still identifies a rowid alias.
#[test]
fn rowid_alias_before_unique_without_comma() {
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, PRIMARY KEY(id) UNIQUE(v))");
}
/// A named CONSTRAINT PRIMARY KEY after CHECK without a comma still identifies a rowid alias.
#[test]
fn rowid_alias_named_constraint_after_check_without_comma() {
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, CHECK(v != '') CONSTRAINT pk PRIMARY KEY(id))");
}
/// Parenthesized column names in a table-level PRIMARY KEY still identify a rowid alias.
#[test]
fn rowid_alias_with_parenthesized_column_name() {
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, PRIMARY KEY((id)))");
}
/// A WITHOUT ROWID PRIMARY KEY preceded by UNIQUE without a comma projects columns in order.
#[test]
fn without_rowid_after_unique_without_comma() {
assert_without_rowid("CREATE TABLE t (v TEXT, k TEXT, UNIQUE(v) PRIMARY KEY(k)) WITHOUT ROWID");
}
/// A WITHOUT ROWID PRIMARY KEY followed by UNIQUE without a comma avoids spanning both clauses.
#[test]
fn without_rowid_before_unique_without_comma() {
assert_without_rowid("CREATE TABLE t (v TEXT, k TEXT, PRIMARY KEY(k) UNIQUE(v)) WITHOUT ROWID");
}
/// A WITHOUT ROWID named CONSTRAINT PRIMARY KEY after CHECK without a comma projects columns in order.
#[test]
fn without_rowid_named_constraint_after_check_without_comma() {
assert_without_rowid("CREATE TABLE t (v TEXT, k TEXT, CHECK(v != '') CONSTRAINT pk PRIMARY KEY(k)) WITHOUT ROWID");
}
/// Standard comma-separated PRIMARY KEY control works for both rowid and WITHOUT ROWID tables.
#[test]
fn comma_separated_primary_key_control() {
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, UNIQUE(v), PRIMARY KEY(id))");
assert_without_rowid("CREATE TABLE t (v TEXT, k TEXT, UNIQUE(v), PRIMARY KEY(k)) WITHOUT ROWID");
}
/// Table-level PRIMARY KEY(id AUTOINCREMENT) preserves the INTEGER rowid alias.
#[test]
fn rowid_alias_with_table_autoincrement() {
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, PRIMARY KEY(id AUTOINCREMENT))");
}
/// Table-level AUTOINCREMENT keeps working when preceded by UNIQUE without a comma.
#[test]
fn rowid_alias_after_unique_with_table_autoincrement() {
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, UNIQUE(v) PRIMARY KEY(id AUTOINCREMENT))");
}
/// Table-level ASC, DESC, COLLATE, and parenthesized terms combined with AUTOINCREMENT stay rowid aliases.
#[test]
fn rowid_alias_with_indexed_column_modifiers_and_autoincrement() {
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, PRIMARY KEY((id) AUTOINCREMENT))");
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, PRIMARY KEY(id ASC AUTOINCREMENT))");
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, PRIMARY KEY(id DESC AUTOINCREMENT))");
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, PRIMARY KEY(id COLLATE NOCASE AUTOINCREMENT))");
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, PRIMARY KEY((id) COLLATE NOCASE DESC))");
assert_rowid_alias("CREATE TABLE t (id INTEGER, v TEXT, UNIQUE(v) PRIMARY KEY((id) COLLATE NOCASE DESC AUTOINCREMENT))");
}