// Auto-generated by test-generator. Do not edit.
// Category: catalog
#[allow(unused_imports)]
use scythe_codegen as _codegen;
#[allow(unused_imports)]
use syn as _syn;
#[allow(dead_code)]
fn normalize_whitespace(s: &str) -> String {
s.split_whitespace().collect::<Vec<_>>().join(" ")
}
#[test]
fn test_alter_add_column() {
// From: testing_data/catalog/alter_table/01_add_column.json
// "ALTER TABLE ADD COLUMN adds a new column to an existing table"
let schema_sql = &[
"CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL)",
"ALTER TABLE users ADD COLUMN email text NOT NULL",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: users
let table_users = catalog.get_table("users").expect("table users should exist");
assert_eq!(table_users.columns.len(), 3, "column count for table users");
assert_eq!(table_users.columns[0].name, "id", "column name");
assert_eq!(table_users.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_users.columns[1].name, "name", "column name");
assert_eq!(table_users.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_users.columns[1].nullable, "column nullable for name");
assert_eq!(table_users.columns[2].name, "email", "column name");
assert_eq!(table_users.columns[2].sql_type, "text", "column sql_type for email");
assert!(!table_users.columns[2].nullable, "column nullable for email");
}
#[test]
fn test_alter_add_constraint() {
// From: testing_data/catalog/alter_table/07_add_constraint.json
// "ALTER TABLE ADD CONSTRAINT adds a foreign key constraint to an existing table"
let schema_sql = &[
"CREATE TABLE departments (id serial PRIMARY KEY, name text NOT NULL)",
"CREATE TABLE employees (id serial PRIMARY KEY, name text NOT NULL, department_id integer NOT NULL)",
"ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (id)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 2, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: departments
let table_departments = catalog
.get_table("departments")
.expect("table departments should exist");
assert_eq!(table_departments.columns.len(), 2, "column count for table departments");
assert_eq!(table_departments.columns[0].name, "id", "column name");
assert_eq!(
table_departments.columns[0].sql_type, "integer",
"column sql_type for id"
);
assert!(!table_departments.columns[0].nullable, "column nullable for id");
assert_eq!(table_departments.columns[1].name, "name", "column name");
assert_eq!(
table_departments.columns[1].sql_type, "text",
"column sql_type for name"
);
assert!(!table_departments.columns[1].nullable, "column nullable for name");
// Assert table: employees
let table_employees = catalog.get_table("employees").expect("table employees should exist");
assert_eq!(table_employees.columns.len(), 3, "column count for table employees");
assert_eq!(table_employees.columns[0].name, "id", "column name");
assert_eq!(table_employees.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_employees.columns[0].nullable, "column nullable for id");
assert_eq!(table_employees.columns[1].name, "name", "column name");
assert_eq!(table_employees.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_employees.columns[1].nullable, "column nullable for name");
assert_eq!(table_employees.columns[2].name, "department_id", "column name");
assert_eq!(
table_employees.columns[2].sql_type, "integer",
"column sql_type for department_id"
);
assert!(
!table_employees.columns[2].nullable,
"column nullable for department_id"
);
}
#[test]
fn test_alter_column_type() {
// From: testing_data/catalog/alter_table/04_alter_type.json
// "ALTER TABLE ALTER COLUMN SET DATA TYPE changes a column type"
let schema_sql = &[
"CREATE TABLE users (id serial PRIMARY KEY, age smallint NOT NULL)",
"ALTER TABLE users ALTER COLUMN age SET DATA TYPE integer",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: users
let table_users = catalog.get_table("users").expect("table users should exist");
assert_eq!(table_users.columns.len(), 2, "column count for table users");
assert_eq!(table_users.columns[0].name, "id", "column name");
assert_eq!(table_users.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_users.columns[1].name, "age", "column name");
assert_eq!(table_users.columns[1].sql_type, "integer", "column sql_type for age");
assert!(!table_users.columns[1].nullable, "column nullable for age");
}
#[test]
fn test_alter_drop_column() {
// From: testing_data/catalog/alter_table/02_drop_column.json
// "ALTER TABLE DROP COLUMN removes a column from an existing table"
let schema_sql = &[
"CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL, nickname text, email text NOT NULL)",
"ALTER TABLE users DROP COLUMN nickname",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: users
let table_users = catalog.get_table("users").expect("table users should exist");
assert_eq!(table_users.columns.len(), 3, "column count for table users");
assert_eq!(table_users.columns[0].name, "id", "column name");
assert_eq!(table_users.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_users.columns[1].name, "name", "column name");
assert_eq!(table_users.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_users.columns[1].nullable, "column nullable for name");
assert_eq!(table_users.columns[2].name, "email", "column name");
assert_eq!(table_users.columns[2].sql_type, "text", "column sql_type for email");
assert!(!table_users.columns[2].nullable, "column nullable for email");
}
#[test]
fn test_alter_drop_not_null() {
// From: testing_data/catalog/alter_table/06_drop_not_null.json
// "ALTER TABLE ALTER COLUMN DROP NOT NULL makes a required column nullable"
let schema_sql = &[
"CREATE TABLE users (id serial PRIMARY KEY, email text NOT NULL)",
"ALTER TABLE users ALTER COLUMN email DROP NOT NULL",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: users
let table_users = catalog.get_table("users").expect("table users should exist");
assert_eq!(table_users.columns.len(), 2, "column count for table users");
assert_eq!(table_users.columns[0].name, "id", "column name");
assert_eq!(table_users.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_users.columns[1].name, "email", "column name");
assert_eq!(table_users.columns[1].sql_type, "text", "column sql_type for email");
assert!(table_users.columns[1].nullable, "column nullable for email");
}
#[test]
fn test_alter_rename_column() {
// From: testing_data/catalog/alter_table/03_rename_column.json
// "ALTER TABLE RENAME COLUMN changes a column name"
let schema_sql = &[
"CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL)",
"ALTER TABLE users RENAME COLUMN name TO full_name",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: users
let table_users = catalog.get_table("users").expect("table users should exist");
assert_eq!(table_users.columns.len(), 2, "column count for table users");
assert_eq!(table_users.columns[0].name, "id", "column name");
assert_eq!(table_users.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_users.columns[1].name, "full_name", "column name");
assert_eq!(table_users.columns[1].sql_type, "text", "column sql_type for full_name");
assert!(!table_users.columns[1].nullable, "column nullable for full_name");
}
#[test]
fn test_alter_rename_table() {
// From: testing_data/catalog/alter_table/08_rename_table.json
// "ALTER TABLE RENAME TO changes the table name"
let schema_sql = &[
"CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL)",
"ALTER TABLE users RENAME TO people",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: people
let table_people = catalog.get_table("people").expect("table people should exist");
assert_eq!(table_people.columns.len(), 2, "column count for table people");
assert_eq!(table_people.columns[0].name, "id", "column name");
assert_eq!(table_people.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_people.columns[0].nullable, "column nullable for id");
assert_eq!(table_people.columns[1].name, "name", "column name");
assert_eq!(table_people.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_people.columns[1].nullable, "column nullable for name");
}
#[test]
fn test_alter_set_not_null() {
// From: testing_data/catalog/alter_table/05_set_not_null.json
// "ALTER TABLE ALTER COLUMN SET NOT NULL makes a nullable column required"
let schema_sql = &[
"CREATE TABLE users (id serial PRIMARY KEY, email text)",
"ALTER TABLE users ALTER COLUMN email SET NOT NULL",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: users
let table_users = catalog.get_table("users").expect("table users should exist");
assert_eq!(table_users.columns.len(), 2, "column count for table users");
assert_eq!(table_users.columns[0].name, "id", "column name");
assert_eq!(table_users.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_users.columns[1].name, "email", "column name");
assert_eq!(table_users.columns[1].sql_type, "text", "column sql_type for email");
assert!(!table_users.columns[1].nullable, "column nullable for email");
}
#[test]
fn test_alter_unknown_table() {
// From: testing_data/catalog/alter_table/09_alter_unknown_table.json
// "ALTER TABLE against a table that was never registered (a typo'd migration) must error, not silently no-op"
let schema_sql = &[
"CREATE TABLE users (id integer NOT NULL)",
"ALTER TABLE userz ADD COLUMN email text NOT NULL",
];
let result = scythe_core::catalog::Catalog::from_ddl(schema_sql);
assert!(result.is_err(), "expected DDL processing to fail");
let err = result.unwrap_err();
let err_msg = err.to_string();
assert!(
err_msg.contains("UNKNOWN_TABLE"),
"error should contain code {:?}, got: {}",
"UNKNOWN_TABLE",
err_msg
);
assert!(
err_msg.contains("\"userz\""),
"error should contain {:?}, got: {}",
"\"userz\"",
err_msg
);
}
#[test]
fn test_basic_composite() {
// From: testing_data/catalog/create_composite/01_basic_composite.json
// "CREATE TYPE AS composite with text fields for an address"
let schema_sql = &["CREATE TYPE address AS (street text, city text, zip text)"];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 0, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 1, "total composite count");
// Assert composite: address
let comp_address = catalog
.get_composite("address")
.expect("composite address should exist");
assert_eq!(comp_address.fields.len(), 3, "field count for composite address");
assert_eq!(comp_address.fields[0].name, "street", "composite field name");
assert_eq!(
comp_address.fields[0].sql_type, "text",
"composite field sql_type for street"
);
assert_eq!(comp_address.fields[1].name, "city", "composite field name");
assert_eq!(
comp_address.fields[1].sql_type, "text",
"composite field sql_type for city"
);
assert_eq!(comp_address.fields[2].name, "zip", "composite field name");
assert_eq!(
comp_address.fields[2].sql_type, "text",
"composite field sql_type for zip"
);
}
#[test]
fn test_composite_with_types() {
// From: testing_data/catalog/create_composite/02_composite_with_types.json
// "Composite type with various field types including arrays"
let schema_sql = &[
"CREATE TYPE inventory_item AS (name text, supplier_id integer, price numeric(10,2), tags text[], in_stock boolean)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 0, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 1, "total composite count");
// Assert composite: inventory_item
let comp_inventory_item = catalog
.get_composite("inventory_item")
.expect("composite inventory_item should exist");
assert_eq!(
comp_inventory_item.fields.len(),
5,
"field count for composite inventory_item"
);
assert_eq!(comp_inventory_item.fields[0].name, "name", "composite field name");
assert_eq!(
comp_inventory_item.fields[0].sql_type, "text",
"composite field sql_type for name"
);
assert_eq!(
comp_inventory_item.fields[1].name, "supplier_id",
"composite field name"
);
assert_eq!(
comp_inventory_item.fields[1].sql_type, "integer",
"composite field sql_type for supplier_id"
);
assert_eq!(comp_inventory_item.fields[2].name, "price", "composite field name");
assert_eq!(
comp_inventory_item.fields[2].sql_type, "numeric(10,2)",
"composite field sql_type for price"
);
assert_eq!(comp_inventory_item.fields[3].name, "tags", "composite field name");
assert_eq!(
comp_inventory_item.fields[3].sql_type, "text[]",
"composite field sql_type for tags"
);
assert_eq!(comp_inventory_item.fields[4].name, "in_stock", "composite field name");
assert_eq!(
comp_inventory_item.fields[4].sql_type, "boolean",
"composite field sql_type for in_stock"
);
}
#[test]
fn test_basic_domain() {
// From: testing_data/catalog/create_domain/01_basic_domain.json
// "CREATE DOMAIN with CHECK constraint for positive integers"
let schema_sql = &["CREATE DOMAIN positive_int AS integer CHECK (VALUE > 0)"];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 0, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
}
#[test]
fn test_domain_as_optional() {
// From: testing_data/catalog/create_domain/06_as_optional.json
// "PostgreSQL's AS between a domain name and its base type is optional -- the previous hand-written parser required it and silently recorded nothing for the AS-less form, so a column using the domain later failed with a misdiagnosed INTERNAL_ERROR"
let schema_sql = &[
"CREATE DOMAIN email_no_as text NOT NULL",
"CREATE TABLE t (b email_no_as)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: t
let table_t = catalog.get_table("t").expect("table t should exist");
assert_eq!(table_t.columns.len(), 1, "column count for table t");
assert_eq!(table_t.columns[0].name, "b", "column name");
assert_eq!(table_t.columns[0].sql_type, "text", "column sql_type for b");
assert!(!table_t.columns[0].nullable, "column nullable for b");
}
#[test]
fn test_domain_check_body_not_null_substring() {
// From: testing_data/catalog/create_domain/05_check_body_not_null_substring.json
// "A CHECK body that merely contains the string NOT NULL (e.g. comparing against the literal text) must not be mistaken for the NOT NULL constraint keyword -- the previous detection scanned the whole remainder text with a plain substring search"
let schema_sql = &[
"CREATE DOMAIN nickname AS text CHECK (VALUE <> 'NOT NULL')",
"CREATE DOMAIN plainname AS text CHECK (VALUE <> 'x')",
"CREATE TABLE users (id integer NOT NULL, nick nickname, plain plainname)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: users
let table_users = catalog.get_table("users").expect("table users should exist");
assert_eq!(table_users.columns.len(), 3, "column count for table users");
assert_eq!(table_users.columns[0].name, "id", "column name");
assert_eq!(table_users.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_users.columns[1].name, "nick", "column name");
assert_eq!(table_users.columns[1].sql_type, "text", "column sql_type for nick");
assert!(table_users.columns[1].nullable, "column nullable for nick");
assert_eq!(table_users.columns[2].name, "plain", "column name");
assert_eq!(table_users.columns[2].sql_type, "text", "column sql_type for plain");
assert!(table_users.columns[2].nullable, "column nullable for plain");
}
#[test]
fn test_domain_named_constraint_check() {
// From: testing_data/catalog/create_domain/07_named_constraint_check.json
// "CONSTRAINT <name> CHECK (...) must terminate the domain's base type just like NOT NULL/CHECK/DEFAULT do -- the previous parser folded the constraint name and keyword into the reported base type string"
let schema_sql = &[
"CREATE DOMAIN money_amount AS numeric(10,2) CONSTRAINT positive_amount CHECK (VALUE > 0)",
"CREATE TABLE t (amt money_amount)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: t
let table_t = catalog.get_table("t").expect("table t should exist");
assert_eq!(table_t.columns.len(), 1, "column count for table t");
assert_eq!(table_t.columns[0].name, "amt", "column name");
assert_eq!(table_t.columns[0].sql_type, "numeric(10,2)", "column sql_type for amt");
assert!(table_t.columns[0].nullable, "column nullable for amt");
}
#[test]
fn test_domain_non_ascii_name() {
// From: testing_data/catalog/create_domain/03_non_ascii_name.json
// "A CREATE DOMAIN name whose uppercase form has a different byte length than its original (e.g. U+FB01 the ligature fi becomes the two-byte-longer FI) must not panic -- a byte offset computed on the uppercased copy was previously sliced directly against the original string"
let schema_sql = &[
"CREATE DOMAIN fifi AS text NOT NULL",
"CREATE TABLE t (id integer NOT NULL)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: t
let table_t = catalog.get_table("t").expect("table t should exist");
assert_eq!(table_t.columns.len(), 1, "column count for table t");
assert_eq!(table_t.columns[0].name, "id", "column name");
assert_eq!(table_t.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_t.columns[0].nullable, "column nullable for id");
}
#[test]
fn test_domain_not_null() {
// From: testing_data/catalog/create_domain/02_domain_not_null.json
// "CREATE DOMAIN with NOT NULL constraint"
let schema_sql = &["CREATE DOMAIN non_empty_text AS text NOT NULL"];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 0, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
}
#[test]
fn test_domain_schema_qualified_not_null() {
// From: testing_data/catalog/create_domain/04_schema_qualified_not_null.json
// "A bare reference to a schema-qualified domain must resolve NOT NULL through the same qualifier-tolerant lookup as its type -- type resolution already tolerated the missing qualifier while NOT NULL resolution used a separate, stricter exact-match lookup and silently disagreed"
let schema_sql = &[
"CREATE DOMAIN app.nn AS text NOT NULL",
"CREATE DOMAIN plain_nn AS text NOT NULL",
"CREATE TABLE t (a nn, b plain_nn, c app.nn)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: t
let table_t = catalog.get_table("t").expect("table t should exist");
assert_eq!(table_t.columns.len(), 3, "column count for table t");
assert_eq!(table_t.columns[0].name, "a", "column name");
assert_eq!(table_t.columns[0].sql_type, "text", "column sql_type for a");
assert!(!table_t.columns[0].nullable, "column nullable for a");
assert_eq!(table_t.columns[1].name, "b", "column name");
assert_eq!(table_t.columns[1].sql_type, "text", "column sql_type for b");
assert!(!table_t.columns[1].nullable, "column nullable for b");
assert_eq!(table_t.columns[2].name, "c", "column name");
assert_eq!(table_t.columns[2].sql_type, "text", "column sql_type for c");
assert!(!table_t.columns[2].nullable, "column nullable for c");
}
#[test]
fn test_alter_enum_add_value() {
// From: testing_data/catalog/create_enum/02_alter_enum_add_value.json
// "ALTER TYPE ADD VALUE appends a new value to an existing enum"
let schema_sql = &[
"CREATE TYPE status AS ENUM ('active', 'inactive')",
"ALTER TYPE status ADD VALUE 'banned'",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 0, "total table count");
assert_eq!(catalog.enums_iter().count(), 1, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert enum: status
let enum_status = catalog.get_enum("status").expect("enum status should exist");
assert_eq!(
enum_status.values,
vec!["active", "inactive", "banned"],
"enum values for status"
);
}
#[test]
fn test_alter_enum_rename_value() {
// From: testing_data/catalog/create_enum/03_alter_enum_rename_value.json
// "ALTER TYPE RENAME VALUE changes an existing enum value name"
let schema_sql = &[
"CREATE TYPE status AS ENUM ('active', 'inactive', 'banned')",
"ALTER TYPE status RENAME VALUE 'banned' TO 'suspended'",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 0, "total table count");
assert_eq!(catalog.enums_iter().count(), 1, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert enum: status
let enum_status = catalog.get_enum("status").expect("enum status should exist");
assert_eq!(
enum_status.values,
vec!["active", "inactive", "suspended"],
"enum values for status"
);
}
#[test]
fn test_basic_enum() {
// From: testing_data/catalog/create_enum/01_basic_enum.json
// "CREATE TYPE AS ENUM with three values"
let schema_sql = &["CREATE TYPE status AS ENUM ('active', 'inactive', 'banned')"];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 0, "total table count");
assert_eq!(catalog.enums_iter().count(), 1, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert enum: status
let enum_status = catalog.get_enum("status").expect("enum status should exist");
assert_eq!(
enum_status.values,
vec!["active", "inactive", "banned"],
"enum values for status"
);
}
#[test]
fn test_multiple_enums() {
// From: testing_data/catalog/create_enum/04_multiple_enums.json
// "Multiple enum types defined together"
let schema_sql = &[
"CREATE TYPE status AS ENUM ('active', 'inactive')",
"CREATE TYPE role AS ENUM ('admin', 'editor', 'viewer')",
"CREATE TYPE priority AS ENUM ('low', 'medium', 'high', 'critical')",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 0, "total table count");
assert_eq!(catalog.enums_iter().count(), 3, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert enum: priority
let enum_priority = catalog.get_enum("priority").expect("enum priority should exist");
assert_eq!(
enum_priority.values,
vec!["low", "medium", "high", "critical"],
"enum values for priority"
);
// Assert enum: role
let enum_role = catalog.get_enum("role").expect("enum role should exist");
assert_eq!(
enum_role.values,
vec!["admin", "editor", "viewer"],
"enum values for role"
);
// Assert enum: status
let enum_status = catalog.get_enum("status").expect("enum status should exist");
assert_eq!(enum_status.values, vec!["active", "inactive"], "enum values for status");
}
#[test]
fn test_all_string_types() {
// From: testing_data/catalog/create_table/03_all_string_types.json
// "Table with all PostgreSQL string types: text, varchar, char, bytea"
let schema_sql = &["CREATE TABLE string_types (a text, b varchar(255), c char(10), d bytea)"];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: string_types
let table_string_types = catalog
.get_table("string_types")
.expect("table string_types should exist");
assert_eq!(
table_string_types.columns.len(),
4,
"column count for table string_types"
);
assert_eq!(table_string_types.columns[0].name, "a", "column name");
assert_eq!(table_string_types.columns[0].sql_type, "text", "column sql_type for a");
assert!(table_string_types.columns[0].nullable, "column nullable for a");
assert_eq!(table_string_types.columns[1].name, "b", "column name");
assert_eq!(
table_string_types.columns[1].sql_type, "varchar(255)",
"column sql_type for b"
);
assert!(table_string_types.columns[1].nullable, "column nullable for b");
assert_eq!(table_string_types.columns[2].name, "c", "column name");
assert_eq!(
table_string_types.columns[2].sql_type, "char(10)",
"column sql_type for c"
);
assert!(table_string_types.columns[2].nullable, "column nullable for c");
assert_eq!(table_string_types.columns[3].name, "d", "column name");
assert_eq!(table_string_types.columns[3].sql_type, "bytea", "column sql_type for d");
assert!(table_string_types.columns[3].nullable, "column nullable for d");
}
#[test]
fn test_array_columns() {
// From: testing_data/catalog/create_table/08_array_columns.json
// "Table with array column types: int[], text[], boolean[]"
let schema_sql = &["CREATE TABLE arrays_example (id serial, int_list int[], tags text[], flags boolean[])"];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: arrays_example
let table_arrays_example = catalog
.get_table("arrays_example")
.expect("table arrays_example should exist");
assert_eq!(
table_arrays_example.columns.len(),
4,
"column count for table arrays_example"
);
assert_eq!(table_arrays_example.columns[0].name, "id", "column name");
assert_eq!(
table_arrays_example.columns[0].sql_type, "integer",
"column sql_type for id"
);
assert!(!table_arrays_example.columns[0].nullable, "column nullable for id");
assert_eq!(table_arrays_example.columns[1].name, "int_list", "column name");
assert_eq!(
table_arrays_example.columns[1].sql_type, "int[]",
"column sql_type for int_list"
);
assert!(table_arrays_example.columns[1].nullable, "column nullable for int_list");
assert_eq!(table_arrays_example.columns[2].name, "tags", "column name");
assert_eq!(
table_arrays_example.columns[2].sql_type, "text[]",
"column sql_type for tags"
);
assert!(table_arrays_example.columns[2].nullable, "column nullable for tags");
assert_eq!(table_arrays_example.columns[3].name, "flags", "column name");
assert_eq!(
table_arrays_example.columns[3].sql_type, "boolean[]",
"column sql_type for flags"
);
assert!(table_arrays_example.columns[3].nullable, "column nullable for flags");
}
#[test]
fn test_basic_table() {
// From: testing_data/catalog/create_table/01_basic_table.json
// "Simple table with id, name, and created_at columns"
let schema_sql = &["CREATE TABLE users (id integer NOT NULL, name text NOT NULL, created_at timestamp NOT NULL)"];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: users
let table_users = catalog.get_table("users").expect("table users should exist");
assert_eq!(table_users.columns.len(), 3, "column count for table users");
assert_eq!(table_users.columns[0].name, "id", "column name");
assert_eq!(table_users.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_users.columns[1].name, "name", "column name");
assert_eq!(table_users.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_users.columns[1].nullable, "column nullable for name");
assert_eq!(table_users.columns[2].name, "created_at", "column name");
assert_eq!(
table_users.columns[2].sql_type, "timestamp",
"column sql_type for created_at"
);
assert!(!table_users.columns[2].nullable, "column nullable for created_at");
}
#[test]
fn test_boolean_and_uuid() {
// From: testing_data/catalog/create_table/05_boolean_and_uuid.json
// "Table with boolean and uuid column types"
let schema_sql = &["CREATE TABLE flags (id uuid NOT NULL, is_active boolean NOT NULL, is_deleted boolean)"];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: flags
let table_flags = catalog.get_table("flags").expect("table flags should exist");
assert_eq!(table_flags.columns.len(), 3, "column count for table flags");
assert_eq!(table_flags.columns[0].name, "id", "column name");
assert_eq!(table_flags.columns[0].sql_type, "uuid", "column sql_type for id");
assert!(!table_flags.columns[0].nullable, "column nullable for id");
assert_eq!(table_flags.columns[1].name, "is_active", "column name");
assert_eq!(
table_flags.columns[1].sql_type, "boolean",
"column sql_type for is_active"
);
assert!(!table_flags.columns[1].nullable, "column nullable for is_active");
assert_eq!(table_flags.columns[2].name, "is_deleted", "column name");
assert_eq!(
table_flags.columns[2].sql_type, "boolean",
"column sql_type for is_deleted"
);
assert!(table_flags.columns[2].nullable, "column nullable for is_deleted");
}
#[test]
fn test_catalog_all_numeric_types() {
// From: testing_data/catalog/create_table/02_all_numeric_types.json
// "Table with all PostgreSQL numeric types: smallint, integer, bigint, real, double precision, numeric, serial, bigserial"
let schema_sql = &[
"CREATE TABLE numeric_types (a smallint, b integer, c bigint, d real, e double precision, f numeric(10,2), g serial, h bigserial)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: numeric_types
let table_numeric_types = catalog
.get_table("numeric_types")
.expect("table numeric_types should exist");
assert_eq!(
table_numeric_types.columns.len(),
8,
"column count for table numeric_types"
);
assert_eq!(table_numeric_types.columns[0].name, "a", "column name");
assert_eq!(
table_numeric_types.columns[0].sql_type, "smallint",
"column sql_type for a"
);
assert!(table_numeric_types.columns[0].nullable, "column nullable for a");
assert_eq!(table_numeric_types.columns[1].name, "b", "column name");
assert_eq!(
table_numeric_types.columns[1].sql_type, "integer",
"column sql_type for b"
);
assert!(table_numeric_types.columns[1].nullable, "column nullable for b");
assert_eq!(table_numeric_types.columns[2].name, "c", "column name");
assert_eq!(
table_numeric_types.columns[2].sql_type, "bigint",
"column sql_type for c"
);
assert!(table_numeric_types.columns[2].nullable, "column nullable for c");
assert_eq!(table_numeric_types.columns[3].name, "d", "column name");
assert_eq!(table_numeric_types.columns[3].sql_type, "real", "column sql_type for d");
assert!(table_numeric_types.columns[3].nullable, "column nullable for d");
assert_eq!(table_numeric_types.columns[4].name, "e", "column name");
assert_eq!(
table_numeric_types.columns[4].sql_type, "double precision",
"column sql_type for e"
);
assert!(table_numeric_types.columns[4].nullable, "column nullable for e");
assert_eq!(table_numeric_types.columns[5].name, "f", "column name");
assert_eq!(
table_numeric_types.columns[5].sql_type, "numeric(10,2)",
"column sql_type for f"
);
assert!(table_numeric_types.columns[5].nullable, "column nullable for f");
assert_eq!(table_numeric_types.columns[6].name, "g", "column name");
assert_eq!(
table_numeric_types.columns[6].sql_type, "integer",
"column sql_type for g"
);
assert!(!table_numeric_types.columns[6].nullable, "column nullable for g");
assert_eq!(table_numeric_types.columns[7].name, "h", "column name");
assert_eq!(
table_numeric_types.columns[7].sql_type, "bigint",
"column sql_type for h"
);
assert!(!table_numeric_types.columns[7].nullable, "column nullable for h");
}
#[test]
fn test_catalog_network_types() {
// From: testing_data/catalog/create_table/07_network_types.json
// "Table with PostgreSQL network types: inet, cidr, macaddr"
let schema_sql = &["CREATE TABLE network_info (id serial, ip_address inet NOT NULL, subnet cidr, mac macaddr)"];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: network_info
let table_network_info = catalog
.get_table("network_info")
.expect("table network_info should exist");
assert_eq!(
table_network_info.columns.len(),
4,
"column count for table network_info"
);
assert_eq!(table_network_info.columns[0].name, "id", "column name");
assert_eq!(
table_network_info.columns[0].sql_type, "integer",
"column sql_type for id"
);
assert!(!table_network_info.columns[0].nullable, "column nullable for id");
assert_eq!(table_network_info.columns[1].name, "ip_address", "column name");
assert_eq!(
table_network_info.columns[1].sql_type, "inet",
"column sql_type for ip_address"
);
assert!(
!table_network_info.columns[1].nullable,
"column nullable for ip_address"
);
assert_eq!(table_network_info.columns[2].name, "subnet", "column name");
assert_eq!(
table_network_info.columns[2].sql_type, "cidr",
"column sql_type for subnet"
);
assert!(table_network_info.columns[2].nullable, "column nullable for subnet");
assert_eq!(table_network_info.columns[3].name, "mac", "column name");
assert_eq!(
table_network_info.columns[3].sql_type, "macaddr",
"column sql_type for mac"
);
assert!(table_network_info.columns[3].nullable, "column nullable for mac");
}
#[test]
fn test_constraints_check() {
// From: testing_data/catalog/create_table/14_constraints_check.json
// "CHECK constraints on columns"
let schema_sql = &[
"CREATE TABLE products (id serial PRIMARY KEY, name text NOT NULL, price numeric(10,2) NOT NULL CHECK (price > 0), quantity integer NOT NULL CHECK (quantity >= 0))",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: products
let table_products = catalog.get_table("products").expect("table products should exist");
assert_eq!(table_products.columns.len(), 4, "column count for table products");
assert_eq!(table_products.columns[0].name, "id", "column name");
assert_eq!(table_products.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_products.columns[0].nullable, "column nullable for id");
assert_eq!(table_products.columns[1].name, "name", "column name");
assert_eq!(table_products.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_products.columns[1].nullable, "column nullable for name");
assert_eq!(table_products.columns[2].name, "price", "column name");
assert_eq!(
table_products.columns[2].sql_type, "numeric(10,2)",
"column sql_type for price"
);
assert!(!table_products.columns[2].nullable, "column nullable for price");
assert_eq!(table_products.columns[3].name, "quantity", "column name");
assert_eq!(
table_products.columns[3].sql_type, "integer",
"column sql_type for quantity"
);
assert!(!table_products.columns[3].nullable, "column nullable for quantity");
}
#[test]
fn test_constraints_default() {
// From: testing_data/catalog/create_table/13_constraints_default.json
// "Columns with DEFAULT values"
let schema_sql = &[
"CREATE TABLE posts (id serial PRIMARY KEY, title text NOT NULL, status text NOT NULL DEFAULT 'draft', view_count integer NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now())",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: posts
let table_posts = catalog.get_table("posts").expect("table posts should exist");
assert_eq!(table_posts.columns.len(), 5, "column count for table posts");
assert_eq!(table_posts.columns[0].name, "id", "column name");
assert_eq!(table_posts.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_posts.columns[0].nullable, "column nullable for id");
assert_eq!(table_posts.columns[1].name, "title", "column name");
assert_eq!(table_posts.columns[1].sql_type, "text", "column sql_type for title");
assert!(!table_posts.columns[1].nullable, "column nullable for title");
assert_eq!(table_posts.columns[2].name, "status", "column name");
assert_eq!(table_posts.columns[2].sql_type, "text", "column sql_type for status");
assert!(!table_posts.columns[2].nullable, "column nullable for status");
assert_eq!(table_posts.columns[3].name, "view_count", "column name");
assert_eq!(
table_posts.columns[3].sql_type, "integer",
"column sql_type for view_count"
);
assert!(!table_posts.columns[3].nullable, "column nullable for view_count");
assert_eq!(table_posts.columns[4].name, "created_at", "column name");
assert_eq!(
table_posts.columns[4].sql_type, "timestamptz",
"column sql_type for created_at"
);
assert!(!table_posts.columns[4].nullable, "column nullable for created_at");
}
#[test]
fn test_constraints_foreign_key() {
// From: testing_data/catalog/create_table/11_constraints_foreign_key.json
// "Foreign key constraint referencing another table"
let schema_sql = &[
"CREATE TABLE authors (id serial PRIMARY KEY, name text NOT NULL)",
"CREATE TABLE books (id serial PRIMARY KEY, title text NOT NULL, author_id integer NOT NULL REFERENCES authors (id))",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 2, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: authors
let table_authors = catalog.get_table("authors").expect("table authors should exist");
assert_eq!(table_authors.columns.len(), 2, "column count for table authors");
assert_eq!(table_authors.columns[0].name, "id", "column name");
assert_eq!(table_authors.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_authors.columns[0].nullable, "column nullable for id");
assert_eq!(table_authors.columns[1].name, "name", "column name");
assert_eq!(table_authors.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_authors.columns[1].nullable, "column nullable for name");
// Assert table: books
let table_books = catalog.get_table("books").expect("table books should exist");
assert_eq!(table_books.columns.len(), 3, "column count for table books");
assert_eq!(table_books.columns[0].name, "id", "column name");
assert_eq!(table_books.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_books.columns[0].nullable, "column nullable for id");
assert_eq!(table_books.columns[1].name, "title", "column name");
assert_eq!(table_books.columns[1].sql_type, "text", "column sql_type for title");
assert!(!table_books.columns[1].nullable, "column nullable for title");
assert_eq!(table_books.columns[2].name, "author_id", "column name");
assert_eq!(
table_books.columns[2].sql_type, "integer",
"column sql_type for author_id"
);
assert!(!table_books.columns[2].nullable, "column nullable for author_id");
}
#[test]
fn test_constraints_not_null() {
// From: testing_data/catalog/create_table/09_constraints_not_null.json
// "Mix of NOT NULL and nullable columns to verify nullable flag"
let schema_sql = &[
"CREATE TABLE profiles (id integer NOT NULL, username text NOT NULL, bio text, email text NOT NULL, avatar_url text)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: profiles
let table_profiles = catalog.get_table("profiles").expect("table profiles should exist");
assert_eq!(table_profiles.columns.len(), 5, "column count for table profiles");
assert_eq!(table_profiles.columns[0].name, "id", "column name");
assert_eq!(table_profiles.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_profiles.columns[0].nullable, "column nullable for id");
assert_eq!(table_profiles.columns[1].name, "username", "column name");
assert_eq!(
table_profiles.columns[1].sql_type, "text",
"column sql_type for username"
);
assert!(!table_profiles.columns[1].nullable, "column nullable for username");
assert_eq!(table_profiles.columns[2].name, "bio", "column name");
assert_eq!(table_profiles.columns[2].sql_type, "text", "column sql_type for bio");
assert!(table_profiles.columns[2].nullable, "column nullable for bio");
assert_eq!(table_profiles.columns[3].name, "email", "column name");
assert_eq!(table_profiles.columns[3].sql_type, "text", "column sql_type for email");
assert!(!table_profiles.columns[3].nullable, "column nullable for email");
assert_eq!(table_profiles.columns[4].name, "avatar_url", "column name");
assert_eq!(
table_profiles.columns[4].sql_type, "text",
"column sql_type for avatar_url"
);
assert!(table_profiles.columns[4].nullable, "column nullable for avatar_url");
}
#[test]
fn test_constraints_primary_key() {
// From: testing_data/catalog/create_table/10_constraints_primary_key.json
// "Single column and composite primary key constraints (PK implies NOT NULL)"
let schema_sql = &[
"CREATE TABLE single_pk (id integer PRIMARY KEY, name text)",
"CREATE TABLE composite_pk (tenant_id integer, user_id integer, data text, PRIMARY KEY (tenant_id, user_id))",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 2, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: composite_pk
let table_composite_pk = catalog
.get_table("composite_pk")
.expect("table composite_pk should exist");
assert_eq!(
table_composite_pk.columns.len(),
3,
"column count for table composite_pk"
);
assert_eq!(table_composite_pk.columns[0].name, "tenant_id", "column name");
assert_eq!(
table_composite_pk.columns[0].sql_type, "integer",
"column sql_type for tenant_id"
);
assert!(!table_composite_pk.columns[0].nullable, "column nullable for tenant_id");
assert!(
table_composite_pk.columns[0].primary_key,
"column primary_key for tenant_id"
);
assert_eq!(table_composite_pk.columns[1].name, "user_id", "column name");
assert_eq!(
table_composite_pk.columns[1].sql_type, "integer",
"column sql_type for user_id"
);
assert!(!table_composite_pk.columns[1].nullable, "column nullable for user_id");
assert!(
table_composite_pk.columns[1].primary_key,
"column primary_key for user_id"
);
assert_eq!(table_composite_pk.columns[2].name, "data", "column name");
assert_eq!(
table_composite_pk.columns[2].sql_type, "text",
"column sql_type for data"
);
assert!(table_composite_pk.columns[2].nullable, "column nullable for data");
assert!(
!table_composite_pk.columns[2].primary_key,
"column primary_key for data"
);
// Assert table: single_pk
let table_single_pk = catalog.get_table("single_pk").expect("table single_pk should exist");
assert_eq!(table_single_pk.columns.len(), 2, "column count for table single_pk");
assert_eq!(table_single_pk.columns[0].name, "id", "column name");
assert_eq!(table_single_pk.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_single_pk.columns[0].nullable, "column nullable for id");
assert!(table_single_pk.columns[0].primary_key, "column primary_key for id");
assert_eq!(table_single_pk.columns[1].name, "name", "column name");
assert_eq!(table_single_pk.columns[1].sql_type, "text", "column sql_type for name");
assert!(table_single_pk.columns[1].nullable, "column nullable for name");
assert!(!table_single_pk.columns[1].primary_key, "column primary_key for name");
}
#[test]
fn test_constraints_unique() {
// From: testing_data/catalog/create_table/12_constraints_unique.json
// "Single column UNIQUE and composite UNIQUE constraints"
let schema_sql = &[
"CREATE TABLE accounts (id serial PRIMARY KEY, email text NOT NULL UNIQUE, first_name text NOT NULL, last_name text NOT NULL, UNIQUE (first_name, last_name))",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: accounts
let table_accounts = catalog.get_table("accounts").expect("table accounts should exist");
assert_eq!(table_accounts.columns.len(), 4, "column count for table accounts");
assert_eq!(table_accounts.columns[0].name, "id", "column name");
assert_eq!(table_accounts.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_accounts.columns[0].nullable, "column nullable for id");
assert_eq!(table_accounts.columns[1].name, "email", "column name");
assert_eq!(table_accounts.columns[1].sql_type, "text", "column sql_type for email");
assert!(!table_accounts.columns[1].nullable, "column nullable for email");
assert_eq!(table_accounts.columns[2].name, "first_name", "column name");
assert_eq!(
table_accounts.columns[2].sql_type, "text",
"column sql_type for first_name"
);
assert!(!table_accounts.columns[2].nullable, "column nullable for first_name");
assert_eq!(table_accounts.columns[3].name, "last_name", "column name");
assert_eq!(
table_accounts.columns[3].sql_type, "text",
"column sql_type for last_name"
);
assert!(!table_accounts.columns[3].nullable, "column nullable for last_name");
}
#[test]
fn test_create_table_as_select() {
// From: testing_data/catalog/create_table/19_create_table_as_select.json
// "CREATE TABLE ... AS SELECT must register the query's projected columns, not a zero-column table"
let schema_sql = &[
"CREATE TABLE base (id integer NOT NULL, name text NOT NULL)",
"CREATE TABLE derived AS SELECT id, name FROM base",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 2, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: base
let table_base = catalog.get_table("base").expect("table base should exist");
assert_eq!(table_base.columns.len(), 2, "column count for table base");
assert_eq!(table_base.columns[0].name, "id", "column name");
assert_eq!(table_base.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_base.columns[0].nullable, "column nullable for id");
assert_eq!(table_base.columns[1].name, "name", "column name");
assert_eq!(table_base.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_base.columns[1].nullable, "column nullable for name");
// Assert table: derived
let table_derived = catalog.get_table("derived").expect("table derived should exist");
assert_eq!(table_derived.columns.len(), 2, "column count for table derived");
assert_eq!(table_derived.columns[0].name, "id", "column name");
assert_eq!(table_derived.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_derived.columns[0].nullable, "column nullable for id");
assert_eq!(table_derived.columns[1].name, "name", "column name");
assert_eq!(table_derived.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_derived.columns[1].nullable, "column nullable for name");
}
#[test]
fn test_generated_columns() {
// From: testing_data/catalog/create_table/16_generated_columns.json
// "GENERATED ALWAYS AS stored columns"
let schema_sql = &[
"CREATE TABLE rectangles (id serial PRIMARY KEY, width real NOT NULL, height real NOT NULL, area real GENERATED ALWAYS AS (width * height) STORED)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: rectangles
let table_rectangles = catalog.get_table("rectangles").expect("table rectangles should exist");
assert_eq!(table_rectangles.columns.len(), 4, "column count for table rectangles");
assert_eq!(table_rectangles.columns[0].name, "id", "column name");
assert_eq!(
table_rectangles.columns[0].sql_type, "integer",
"column sql_type for id"
);
assert!(!table_rectangles.columns[0].nullable, "column nullable for id");
assert_eq!(table_rectangles.columns[1].name, "width", "column name");
assert_eq!(
table_rectangles.columns[1].sql_type, "real",
"column sql_type for width"
);
assert!(!table_rectangles.columns[1].nullable, "column nullable for width");
assert_eq!(table_rectangles.columns[2].name, "height", "column name");
assert_eq!(
table_rectangles.columns[2].sql_type, "real",
"column sql_type for height"
);
assert!(!table_rectangles.columns[2].nullable, "column nullable for height");
assert_eq!(table_rectangles.columns[3].name, "area", "column name");
assert_eq!(table_rectangles.columns[3].sql_type, "real", "column sql_type for area");
assert!(table_rectangles.columns[3].nullable, "column nullable for area");
}
#[test]
fn test_if_not_exists_preserves_existing() {
// From: testing_data/catalog/create_table/20_if_not_exists_preserves_existing.json
// "CREATE TABLE IF NOT EXISTS on an already-registered table is a no-op, matching PostgreSQL -- it must not silently replace the existing definition, which is exactly the idiom used in idempotent migration files"
let schema_sql = &[
"CREATE TABLE u (id integer NOT NULL, a text NOT NULL)",
"CREATE TABLE IF NOT EXISTS u (id integer NOT NULL)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: u
let table_u = catalog.get_table("u").expect("table u should exist");
assert_eq!(table_u.columns.len(), 2, "column count for table u");
assert_eq!(table_u.columns[0].name, "id", "column name");
assert_eq!(table_u.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_u.columns[0].nullable, "column nullable for id");
assert_eq!(table_u.columns[1].name, "a", "column name");
assert_eq!(table_u.columns[1].sql_type, "text", "column sql_type for a");
assert!(!table_u.columns[1].nullable, "column nullable for a");
}
#[test]
fn test_json_types() {
// From: testing_data/catalog/create_table/06_json_types.json
// "Table with json and jsonb column types"
let schema_sql = &["CREATE TABLE documents (id serial, metadata json, payload jsonb NOT NULL)"];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: documents
let table_documents = catalog.get_table("documents").expect("table documents should exist");
assert_eq!(table_documents.columns.len(), 3, "column count for table documents");
assert_eq!(table_documents.columns[0].name, "id", "column name");
assert_eq!(table_documents.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_documents.columns[0].nullable, "column nullable for id");
assert_eq!(table_documents.columns[1].name, "metadata", "column name");
assert_eq!(
table_documents.columns[1].sql_type, "json",
"column sql_type for metadata"
);
assert!(table_documents.columns[1].nullable, "column nullable for metadata");
assert_eq!(table_documents.columns[2].name, "payload", "column name");
assert_eq!(
table_documents.columns[2].sql_type, "jsonb",
"column sql_type for payload"
);
assert!(!table_documents.columns[2].nullable, "column nullable for payload");
}
#[test]
fn test_multiple_tables() {
// From: testing_data/catalog/create_table/17_multiple_tables.json
// "Multiple tables with foreign key relationships between them"
let schema_sql = &[
"CREATE TABLE departments (id serial PRIMARY KEY, name text NOT NULL)",
"CREATE TABLE employees (id serial PRIMARY KEY, name text NOT NULL, department_id integer NOT NULL REFERENCES departments (id))",
"CREATE TABLE projects (id serial PRIMARY KEY, title text NOT NULL, lead_id integer REFERENCES employees (id), department_id integer NOT NULL REFERENCES departments (id))",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 3, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: departments
let table_departments = catalog
.get_table("departments")
.expect("table departments should exist");
assert_eq!(table_departments.columns.len(), 2, "column count for table departments");
assert_eq!(table_departments.columns[0].name, "id", "column name");
assert_eq!(
table_departments.columns[0].sql_type, "integer",
"column sql_type for id"
);
assert!(!table_departments.columns[0].nullable, "column nullable for id");
assert_eq!(table_departments.columns[1].name, "name", "column name");
assert_eq!(
table_departments.columns[1].sql_type, "text",
"column sql_type for name"
);
assert!(!table_departments.columns[1].nullable, "column nullable for name");
// Assert table: employees
let table_employees = catalog.get_table("employees").expect("table employees should exist");
assert_eq!(table_employees.columns.len(), 3, "column count for table employees");
assert_eq!(table_employees.columns[0].name, "id", "column name");
assert_eq!(table_employees.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_employees.columns[0].nullable, "column nullable for id");
assert_eq!(table_employees.columns[1].name, "name", "column name");
assert_eq!(table_employees.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_employees.columns[1].nullable, "column nullable for name");
assert_eq!(table_employees.columns[2].name, "department_id", "column name");
assert_eq!(
table_employees.columns[2].sql_type, "integer",
"column sql_type for department_id"
);
assert!(
!table_employees.columns[2].nullable,
"column nullable for department_id"
);
// Assert table: projects
let table_projects = catalog.get_table("projects").expect("table projects should exist");
assert_eq!(table_projects.columns.len(), 4, "column count for table projects");
assert_eq!(table_projects.columns[0].name, "id", "column name");
assert_eq!(table_projects.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_projects.columns[0].nullable, "column nullable for id");
assert_eq!(table_projects.columns[1].name, "title", "column name");
assert_eq!(table_projects.columns[1].sql_type, "text", "column sql_type for title");
assert!(!table_projects.columns[1].nullable, "column nullable for title");
assert_eq!(table_projects.columns[2].name, "lead_id", "column name");
assert_eq!(
table_projects.columns[2].sql_type, "integer",
"column sql_type for lead_id"
);
assert!(table_projects.columns[2].nullable, "column nullable for lead_id");
assert_eq!(table_projects.columns[3].name, "department_id", "column name");
assert_eq!(
table_projects.columns[3].sql_type, "integer",
"column sql_type for department_id"
);
assert!(!table_projects.columns[3].nullable, "column nullable for department_id");
}
#[test]
fn test_quoted_primary_key() {
// From: testing_data/catalog/create_table/21_quoted_primary_key.json
// "A quoted PRIMARY KEY column name (inline, and via the pg_dump ALTER TABLE ADD CONSTRAINT form) must still match its column and apply NOT NULL -- comparing the constraint's quoted expr.to_string() against the unquoted registered name never matched"
let schema_sql = &[
"CREATE TABLE t (\"Id\" integer, note text, PRIMARY KEY (\"Id\"))",
"CREATE TABLE t2 (\"UserId\" integer, note text)",
"ALTER TABLE t2 ADD CONSTRAINT t2_pkey PRIMARY KEY (\"UserId\")",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 2, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: t
let table_t = catalog.get_table("t").expect("table t should exist");
assert_eq!(table_t.columns.len(), 2, "column count for table t");
assert_eq!(table_t.columns[0].name, "Id", "column name");
assert_eq!(table_t.columns[0].sql_type, "integer", "column sql_type for Id");
assert!(!table_t.columns[0].nullable, "column nullable for Id");
assert!(table_t.columns[0].primary_key, "column primary_key for Id");
assert_eq!(table_t.columns[1].name, "note", "column name");
assert_eq!(table_t.columns[1].sql_type, "text", "column sql_type for note");
assert!(table_t.columns[1].nullable, "column nullable for note");
assert!(!table_t.columns[1].primary_key, "column primary_key for note");
// Assert table: t2
let table_t2 = catalog.get_table("t2").expect("table t2 should exist");
assert_eq!(table_t2.columns.len(), 2, "column count for table t2");
assert_eq!(table_t2.columns[0].name, "UserId", "column name");
assert_eq!(table_t2.columns[0].sql_type, "integer", "column sql_type for UserId");
assert!(!table_t2.columns[0].nullable, "column nullable for UserId");
assert!(table_t2.columns[0].primary_key, "column primary_key for UserId");
assert_eq!(table_t2.columns[1].name, "note", "column name");
assert_eq!(table_t2.columns[1].sql_type, "text", "column sql_type for note");
assert!(table_t2.columns[1].nullable, "column nullable for note");
assert!(!table_t2.columns[1].primary_key, "column primary_key for note");
}
#[test]
fn test_reserved_words() {
// From: testing_data/catalog/create_table/18_reserved_words.json
// "Table and column names that are SQL reserved words using quoted identifiers"
let schema_sql = &[
"CREATE TABLE \"order\" (\"id\" serial PRIMARY KEY, \"select\" text NOT NULL, \"from\" text, \"group\" integer NOT NULL)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: order
let table_order = catalog.get_table("order").expect("table order should exist");
assert_eq!(table_order.columns.len(), 4, "column count for table order");
assert_eq!(table_order.columns[0].name, "id", "column name");
assert_eq!(table_order.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_order.columns[0].nullable, "column nullable for id");
assert_eq!(table_order.columns[1].name, "select", "column name");
assert_eq!(table_order.columns[1].sql_type, "text", "column sql_type for select");
assert!(!table_order.columns[1].nullable, "column nullable for select");
assert_eq!(table_order.columns[2].name, "from", "column name");
assert_eq!(table_order.columns[2].sql_type, "text", "column sql_type for from");
assert!(table_order.columns[2].nullable, "column nullable for from");
assert_eq!(table_order.columns[3].name, "group", "column name");
assert_eq!(table_order.columns[3].sql_type, "integer", "column sql_type for group");
assert!(!table_order.columns[3].nullable, "column nullable for group");
}
#[test]
fn test_serial_types() {
// From: testing_data/catalog/create_table/15_serial_types.json
// "serial, bigserial, smallserial columns with implicit NOT NULL and underlying integer types"
let schema_sql = &["CREATE TABLE serials (a smallserial, b serial, c bigserial)"];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: serials
let table_serials = catalog.get_table("serials").expect("table serials should exist");
assert_eq!(table_serials.columns.len(), 3, "column count for table serials");
assert_eq!(table_serials.columns[0].name, "a", "column name");
assert_eq!(table_serials.columns[0].sql_type, "smallint", "column sql_type for a");
assert!(!table_serials.columns[0].nullable, "column nullable for a");
assert_eq!(table_serials.columns[1].name, "b", "column name");
assert_eq!(table_serials.columns[1].sql_type, "integer", "column sql_type for b");
assert!(!table_serials.columns[1].nullable, "column nullable for b");
assert_eq!(table_serials.columns[2].name, "c", "column name");
assert_eq!(table_serials.columns[2].sql_type, "bigint", "column sql_type for c");
assert!(!table_serials.columns[2].nullable, "column nullable for c");
}
#[test]
fn test_temporal_types() {
// From: testing_data/catalog/create_table/04_temporal_types.json
// "Table with all PostgreSQL temporal types: date, time, timetz, timestamp, timestamptz, interval"
let schema_sql =
&["CREATE TABLE temporal_types (a date, b time, c timetz, d timestamp, e timestamptz, f interval)"];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: temporal_types
let table_temporal_types = catalog
.get_table("temporal_types")
.expect("table temporal_types should exist");
assert_eq!(
table_temporal_types.columns.len(),
6,
"column count for table temporal_types"
);
assert_eq!(table_temporal_types.columns[0].name, "a", "column name");
assert_eq!(
table_temporal_types.columns[0].sql_type, "date",
"column sql_type for a"
);
assert!(table_temporal_types.columns[0].nullable, "column nullable for a");
assert_eq!(table_temporal_types.columns[1].name, "b", "column name");
assert_eq!(
table_temporal_types.columns[1].sql_type, "time",
"column sql_type for b"
);
assert!(table_temporal_types.columns[1].nullable, "column nullable for b");
assert_eq!(table_temporal_types.columns[2].name, "c", "column name");
assert_eq!(
table_temporal_types.columns[2].sql_type, "timetz",
"column sql_type for c"
);
assert!(table_temporal_types.columns[2].nullable, "column nullable for c");
assert_eq!(table_temporal_types.columns[3].name, "d", "column name");
assert_eq!(
table_temporal_types.columns[3].sql_type, "timestamp",
"column sql_type for d"
);
assert!(table_temporal_types.columns[3].nullable, "column nullable for d");
assert_eq!(table_temporal_types.columns[4].name, "e", "column name");
assert_eq!(
table_temporal_types.columns[4].sql_type, "timestamptz",
"column sql_type for e"
);
assert!(table_temporal_types.columns[4].nullable, "column nullable for e");
assert_eq!(table_temporal_types.columns[5].name, "f", "column name");
assert_eq!(
table_temporal_types.columns[5].sql_type, "interval",
"column sql_type for f"
);
assert!(table_temporal_types.columns[5].nullable, "column nullable for f");
}
#[test]
fn test_postgresql_trigger_arguments() {
// From: testing_data/catalog/create_trigger/01_postgresql_trigger_arguments.json
// "PostgreSQL trigger function and procedure arguments do not block catalog construction"
let schema_sql = &[
"CREATE TABLE audit_events (id uuid PRIMARY KEY, payload jsonb NOT NULL); CREATE OR REPLACE FUNCTION audit_row() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE '%', TG_ARGV[0]; RETURN NULL; END; $$; CREATE TRIGGER audit_events_insert AFTER INSERT ON audit_events FOR EACH ROW EXECUTE FUNCTION audit_row('api_key'); CREATE TRIGGER audit_events_update AFTER UPDATE ON audit_events FOR EACH ROW EXECUTE PROCEDURE audit_row('legacy_key'); CREATE TABLE audit_targets (id uuid PRIMARY KEY)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 2, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: audit_events
let table_audit_events = catalog
.get_table("audit_events")
.expect("table audit_events should exist");
assert_eq!(
table_audit_events.columns.len(),
2,
"column count for table audit_events"
);
assert_eq!(table_audit_events.columns[0].name, "id", "column name");
assert_eq!(table_audit_events.columns[0].sql_type, "uuid", "column sql_type for id");
assert!(!table_audit_events.columns[0].nullable, "column nullable for id");
assert_eq!(table_audit_events.columns[1].name, "payload", "column name");
assert_eq!(
table_audit_events.columns[1].sql_type, "jsonb",
"column sql_type for payload"
);
assert!(!table_audit_events.columns[1].nullable, "column nullable for payload");
// Assert table: audit_targets
let table_audit_targets = catalog
.get_table("audit_targets")
.expect("table audit_targets should exist");
assert_eq!(
table_audit_targets.columns.len(),
1,
"column count for table audit_targets"
);
assert_eq!(table_audit_targets.columns[0].name, "id", "column name");
assert_eq!(
table_audit_targets.columns[0].sql_type, "uuid",
"column sql_type for id"
);
assert!(!table_audit_targets.columns[0].nullable, "column nullable for id");
}
#[test]
fn test_basic_view() {
// From: testing_data/catalog/create_view/01_basic_view.json
// "CREATE VIEW with a SELECT from an existing table"
let schema_sql = &[
"CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL, email text NOT NULL, is_active boolean NOT NULL DEFAULT true)",
"CREATE VIEW active_users AS SELECT id, name, email FROM users WHERE is_active = true",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 2, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: active_users
let table_active_users = catalog
.get_table("active_users")
.expect("table active_users should exist");
assert_eq!(
table_active_users.columns.len(),
3,
"column count for table active_users"
);
assert_eq!(table_active_users.columns[0].name, "id", "column name");
assert_eq!(
table_active_users.columns[0].sql_type, "integer",
"column sql_type for id"
);
assert!(!table_active_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_active_users.columns[1].name, "name", "column name");
assert_eq!(
table_active_users.columns[1].sql_type, "text",
"column sql_type for name"
);
assert!(!table_active_users.columns[1].nullable, "column nullable for name");
assert_eq!(table_active_users.columns[2].name, "email", "column name");
assert_eq!(
table_active_users.columns[2].sql_type, "text",
"column sql_type for email"
);
assert!(!table_active_users.columns[2].nullable, "column nullable for email");
// Assert table: users
let table_users = catalog.get_table("users").expect("table users should exist");
assert_eq!(table_users.columns.len(), 4, "column count for table users");
assert_eq!(table_users.columns[0].name, "id", "column name");
assert_eq!(table_users.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_users.columns[1].name, "name", "column name");
assert_eq!(table_users.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_users.columns[1].nullable, "column nullable for name");
assert_eq!(table_users.columns[2].name, "email", "column name");
assert_eq!(table_users.columns[2].sql_type, "text", "column sql_type for email");
assert!(!table_users.columns[2].nullable, "column nullable for email");
assert_eq!(table_users.columns[3].name, "is_active", "column name");
assert_eq!(
table_users.columns[3].sql_type, "boolean",
"column sql_type for is_active"
);
assert!(!table_users.columns[3].nullable, "column nullable for is_active");
}
#[test]
fn test_left_join_nullability() {
// From: testing_data/catalog/create_view/03_left_join_nullability.json
// "A view over a LEFT JOIN must widen the outer side's columns to nullable, exactly as a direct query does -- the previous mini-resolver cloned base-table nullability verbatim and never inspected join kind"
let schema_sql = &[
"CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL)",
"CREATE TABLE profiles (id serial PRIMARY KEY, user_id integer NOT NULL, bio text NOT NULL)",
"CREATE VIEW user_bios AS SELECT u.id, u.name, p.bio FROM users u LEFT JOIN profiles p ON u.id = p.user_id",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 3, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: profiles
let table_profiles = catalog.get_table("profiles").expect("table profiles should exist");
assert_eq!(table_profiles.columns.len(), 3, "column count for table profiles");
assert_eq!(table_profiles.columns[0].name, "id", "column name");
assert_eq!(table_profiles.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_profiles.columns[0].nullable, "column nullable for id");
assert_eq!(table_profiles.columns[1].name, "user_id", "column name");
assert_eq!(
table_profiles.columns[1].sql_type, "integer",
"column sql_type for user_id"
);
assert!(!table_profiles.columns[1].nullable, "column nullable for user_id");
assert_eq!(table_profiles.columns[2].name, "bio", "column name");
assert_eq!(table_profiles.columns[2].sql_type, "text", "column sql_type for bio");
assert!(!table_profiles.columns[2].nullable, "column nullable for bio");
// Assert table: user_bios
let table_user_bios = catalog.get_table("user_bios").expect("table user_bios should exist");
assert_eq!(table_user_bios.columns.len(), 3, "column count for table user_bios");
assert_eq!(table_user_bios.columns[0].name, "id", "column name");
assert_eq!(table_user_bios.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_user_bios.columns[0].nullable, "column nullable for id");
assert_eq!(table_user_bios.columns[1].name, "name", "column name");
assert_eq!(table_user_bios.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_user_bios.columns[1].nullable, "column nullable for name");
assert_eq!(table_user_bios.columns[2].name, "bio", "column name");
assert_eq!(table_user_bios.columns[2].sql_type, "text", "column sql_type for bio");
assert!(table_user_bios.columns[2].nullable, "column nullable for bio");
// Assert table: users
let table_users = catalog.get_table("users").expect("table users should exist");
assert_eq!(table_users.columns.len(), 2, "column count for table users");
assert_eq!(table_users.columns[0].name, "id", "column name");
assert_eq!(table_users.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_users.columns[1].name, "name", "column name");
assert_eq!(table_users.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_users.columns[1].nullable, "column nullable for name");
}
#[test]
fn test_materialized_view() {
// From: testing_data/catalog/create_view/02_materialized_view.json
// "CREATE MATERIALIZED VIEW with an aggregation query; the view's own resolved columns are asserted too, via the same analyzer path an ordinary annotated query uses. sum(numeric(10,2)) resolves to numeric rather than a hardcoded bigint (#182). total_spent is still asserted nullable: PostgreSQL guarantees a GROUP BY group is non-empty, so sum() over a NOT NULL column cannot be NULL, but the analyzer does not yet know that -- the assertion records real behaviour, and tightening it belongs with the aggregate-nullability work in #120."
let schema_sql = &[
"CREATE TABLE orders (id serial PRIMARY KEY, customer_id integer NOT NULL, total numeric(10,2) NOT NULL, created_at date NOT NULL)",
"CREATE MATERIALIZED VIEW order_summary AS SELECT customer_id, count(*) AS order_count, sum(total) AS total_spent FROM orders GROUP BY customer_id",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 2, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: order_summary
let table_order_summary = catalog
.get_table("order_summary")
.expect("table order_summary should exist");
assert_eq!(
table_order_summary.columns.len(),
3,
"column count for table order_summary"
);
assert_eq!(table_order_summary.columns[0].name, "customer_id", "column name");
assert_eq!(
table_order_summary.columns[0].sql_type, "integer",
"column sql_type for customer_id"
);
assert!(
!table_order_summary.columns[0].nullable,
"column nullable for customer_id"
);
assert_eq!(table_order_summary.columns[1].name, "order_count", "column name");
assert_eq!(
table_order_summary.columns[1].sql_type, "bigint",
"column sql_type for order_count"
);
assert!(
!table_order_summary.columns[1].nullable,
"column nullable for order_count"
);
assert_eq!(table_order_summary.columns[2].name, "total_spent", "column name");
assert_eq!(
table_order_summary.columns[2].sql_type, "numeric",
"column sql_type for total_spent"
);
assert!(
table_order_summary.columns[2].nullable,
"column nullable for total_spent"
);
// Assert table: orders
let table_orders = catalog.get_table("orders").expect("table orders should exist");
assert_eq!(table_orders.columns.len(), 4, "column count for table orders");
assert_eq!(table_orders.columns[0].name, "id", "column name");
assert_eq!(table_orders.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_orders.columns[0].nullable, "column nullable for id");
assert_eq!(table_orders.columns[1].name, "customer_id", "column name");
assert_eq!(
table_orders.columns[1].sql_type, "integer",
"column sql_type for customer_id"
);
assert!(!table_orders.columns[1].nullable, "column nullable for customer_id");
assert_eq!(table_orders.columns[2].name, "total", "column name");
assert_eq!(
table_orders.columns[2].sql_type, "numeric(10,2)",
"column sql_type for total"
);
assert!(!table_orders.columns[2].nullable, "column nullable for total");
assert_eq!(table_orders.columns[3].name, "created_at", "column name");
assert_eq!(
table_orders.columns[3].sql_type, "date",
"column sql_type for created_at"
);
assert!(!table_orders.columns[3].nullable, "column nullable for created_at");
}
#[test]
fn test_union_view() {
// From: testing_data/catalog/create_view/04_union_view.json
// "A view whose body is a UNION ALL must resolve its output columns, not register a zero-column table -- the previous mini-resolver only handled SetExpr::Select and returned an empty column list for any other query shape"
let schema_sql = &[
"CREATE TABLE a (id integer NOT NULL, v text NOT NULL)",
"CREATE TABLE b (id integer NOT NULL, v text NOT NULL)",
"CREATE VIEW ab_union AS SELECT id, v FROM a UNION ALL SELECT id, v FROM b",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 3, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: a
let table_a = catalog.get_table("a").expect("table a should exist");
assert_eq!(table_a.columns.len(), 2, "column count for table a");
assert_eq!(table_a.columns[0].name, "id", "column name");
assert_eq!(table_a.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_a.columns[0].nullable, "column nullable for id");
assert_eq!(table_a.columns[1].name, "v", "column name");
assert_eq!(table_a.columns[1].sql_type, "text", "column sql_type for v");
assert!(!table_a.columns[1].nullable, "column nullable for v");
// Assert table: ab_union
let table_ab_union = catalog.get_table("ab_union").expect("table ab_union should exist");
assert_eq!(table_ab_union.columns.len(), 2, "column count for table ab_union");
assert_eq!(table_ab_union.columns[0].name, "id", "column name");
assert_eq!(table_ab_union.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_ab_union.columns[0].nullable, "column nullable for id");
assert_eq!(table_ab_union.columns[1].name, "v", "column name");
assert_eq!(table_ab_union.columns[1].sql_type, "text", "column sql_type for v");
assert!(!table_ab_union.columns[1].nullable, "column nullable for v");
// Assert table: b
let table_b = catalog.get_table("b").expect("table b should exist");
assert_eq!(table_b.columns.len(), 2, "column count for table b");
assert_eq!(table_b.columns[0].name, "id", "column name");
assert_eq!(table_b.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_b.columns[0].nullable, "column nullable for id");
assert_eq!(table_b.columns[1].name, "v", "column name");
assert_eq!(table_b.columns[1].sql_type, "text", "column sql_type for v");
assert!(!table_b.columns[1].nullable, "column nullable for v");
}
#[test]
fn test_cross_schema_reference() {
// From: testing_data/catalog/multi_schema/02_cross_schema_reference.json
// "Tables in different schemas with foreign key references across schemas"
let schema_sql = &[
"CREATE SCHEMA core",
"CREATE SCHEMA billing",
"CREATE TABLE core.users (id serial PRIMARY KEY, name text NOT NULL)",
"CREATE TABLE billing.invoices (id serial PRIMARY KEY, user_id integer NOT NULL REFERENCES core.users (id), amount numeric(10,2) NOT NULL)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 2, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: billing.invoices
let table_billing_invoices = catalog
.get_table("billing.invoices")
.expect("table billing.invoices should exist");
assert_eq!(
table_billing_invoices.columns.len(),
3,
"column count for table billing.invoices"
);
assert_eq!(table_billing_invoices.columns[0].name, "id", "column name");
assert_eq!(
table_billing_invoices.columns[0].sql_type, "integer",
"column sql_type for id"
);
assert!(!table_billing_invoices.columns[0].nullable, "column nullable for id");
assert_eq!(table_billing_invoices.columns[1].name, "user_id", "column name");
assert_eq!(
table_billing_invoices.columns[1].sql_type, "integer",
"column sql_type for user_id"
);
assert!(
!table_billing_invoices.columns[1].nullable,
"column nullable for user_id"
);
assert_eq!(table_billing_invoices.columns[2].name, "amount", "column name");
assert_eq!(
table_billing_invoices.columns[2].sql_type, "numeric(10,2)",
"column sql_type for amount"
);
assert!(
!table_billing_invoices.columns[2].nullable,
"column nullable for amount"
);
// Assert table: core.users
let table_core_users = catalog.get_table("core.users").expect("table core.users should exist");
assert_eq!(table_core_users.columns.len(), 2, "column count for table core.users");
assert_eq!(table_core_users.columns[0].name, "id", "column name");
assert_eq!(
table_core_users.columns[0].sql_type, "integer",
"column sql_type for id"
);
assert!(!table_core_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_core_users.columns[1].name, "name", "column name");
assert_eq!(table_core_users.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_core_users.columns[1].nullable, "column nullable for name");
}
#[test]
fn test_schema_qualified() {
// From: testing_data/catalog/multi_schema/01_schema_qualified.json
// "CREATE SCHEMA and CREATE TABLE with schema-qualified names"
let schema_sql = &[
"CREATE SCHEMA app",
"CREATE TABLE app.users (id serial PRIMARY KEY, name text NOT NULL, email text NOT NULL)",
];
let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
assert_eq!(catalog.tables_iter().count(), 1, "total table count");
assert_eq!(catalog.enums_iter().count(), 0, "total enum count");
assert_eq!(catalog.composites_iter().count(), 0, "total composite count");
// Assert table: app.users
let table_app_users = catalog.get_table("app.users").expect("table app.users should exist");
assert_eq!(table_app_users.columns.len(), 3, "column count for table app.users");
assert_eq!(table_app_users.columns[0].name, "id", "column name");
assert_eq!(table_app_users.columns[0].sql_type, "integer", "column sql_type for id");
assert!(!table_app_users.columns[0].nullable, "column nullable for id");
assert_eq!(table_app_users.columns[1].name, "name", "column name");
assert_eq!(table_app_users.columns[1].sql_type, "text", "column sql_type for name");
assert!(!table_app_users.columns[1].nullable, "column nullable for name");
assert_eq!(table_app_users.columns[2].name, "email", "column name");
assert_eq!(table_app_users.columns[2].sql_type, "text", "column sql_type for email");
assert!(!table_app_users.columns[2].nullable, "column nullable for email");
}