use ormdantic_dialects::{
AnyDialect, Dialect, MariaDbDialect, MsSqlDialect, MySqlDialect, OracleDialect,
PostgresDialect, SqliteDialect,
};
use ormdantic_schema::{
CheckConstraintDef, ColumnDef, ComputedDef, ConstraintDef, ConstraintTiming,
ExclusionConstraintDef, ExclusionElementDef, FieldKind, ForeignKeyAction, ForeignKeyDef,
ForeignKeyMatch, IdentityDef, IndexDef, NamespaceDef, SchemaOperation, TableDef,
UniqueConstraintDef,
};
fn sample_table() -> TableDef {
TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.autoincrement(true),
ColumnDef::new("name", FieldKind::String)
.with_server_default("'vanilla'")
.with_collation("NOCASE"),
ColumnDef::new("supplier_id", FieldKind::Integer).nullable(true),
ColumnDef::new("rating", FieldKind::Decimal).numeric(5, 2),
],
vec![IndexDef::new("flavor_name_idx", vec!["name".to_string()])
.unique(true)
.method("btree")
.expressions(vec!["LOWER(name)".to_string()])
.include_columns(vec!["rating".to_string()])
.postgres_with(vec![("fillfactor".to_string(), "70".to_string())])
.where_expr("name IS NOT NULL")],
vec![
UniqueConstraintDef::new("flavor_name_unique", vec!["name".to_string()])
.with_timing(ConstraintTiming::new(Some(true), false))
.nulls_not_distinct(),
],
Vec::new(),
)
.with_check_constraints(vec![
CheckConstraintDef::new("rating >= 0").named("flavor_rating_check")
])
.with_foreign_keys(vec![ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.named("flavor_supplier_fk")
.on_delete(ForeignKeyAction::SetNull)
.on_update(ForeignKeyAction::Cascade)
.with_timing(ConstraintTiming::new(Some(true), true))])
.with_exclusion_constraints(vec![ExclusionConstraintDef::new(
"flavor_name_no_overlap",
vec![
ExclusionElementDef::column("name", "="),
ExclusionElementDef::expression("tsrange(created_at, updated_at)", "&&"),
],
)
.method("gist")
.where_expr("name IS NOT NULL")
.with_timing(ConstraintTiming::new(Some(true), false))])
}
#[test]
fn renders_create_table_with_constraints_and_indexes() {
let statements = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(sample_table()))
.unwrap();
assert_eq!(
statements,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY NOT NULL, "name" TEXT NOT NULL DEFAULT 'vanilla' COLLATE NOCASE, "supplier_id" INTEGER, "rating" NUMERIC(5, 2) NOT NULL, CONSTRAINT "flavor_name_unique" UNIQUE NULLS NOT DISTINCT ("name") DEFERRABLE, CONSTRAINT "flavor_rating_check" CHECK (rating >= 0), CONSTRAINT "flavor_supplier_fk" FOREIGN KEY ("supplier_id") REFERENCES "supplier" ("id") ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT "flavor_name_no_overlap" EXCLUDE USING gist ("name" WITH =, tsrange(created_at, updated_at) WITH &&) WHERE (name IS NOT NULL) DEFERRABLE)"#.to_string(),
r#"CREATE UNIQUE INDEX IF NOT EXISTS "flavor_name_idx" ON "flavor" USING btree ("name", LOWER(name)) INCLUDE ("rating") WITH (fillfactor = 70) WHERE name IS NOT NULL"#.to_string(),
]
);
}
#[test]
fn renders_schema_qualified_table_operations() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("name", FieldKind::String).with_comment("display name"),
],
vec![IndexDef::new("flavor_name_idx", vec!["name".to_string()])],
Vec::new(),
Vec::new(),
)
.with_schema("inventory")
.with_foreign_keys(vec![ForeignKeyDef::new(
vec!["id".to_string(), "name".to_string()],
"inventory.supplier",
vec!["id".to_string(), "name".to_string()],
)
.named("flavor_supplier_fk")])
.with_comment("schema-owned table");
let statements = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.expect("schema-qualified table should compile");
let drop = PostgresDialect
.compile_schema_operation(&SchemaOperation::DropTable {
name: table.qualified_name().to_string(),
})
.expect("schema-qualified drop should compile");
let oracle_drop = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::DropTable {
name: table.qualified_name().to_string(),
})
.expect("oracle schema-qualified drop should compile");
assert_eq!(
statements,
vec![
r#"CREATE TABLE IF NOT EXISTS "inventory"."flavor" ("id" INTEGER PRIMARY KEY NOT NULL, "name" TEXT NOT NULL, CONSTRAINT "flavor_supplier_fk" FOREIGN KEY ("id", "name") REFERENCES "inventory"."supplier" ("id", "name"))"#.to_string(),
r#"COMMENT ON TABLE "inventory"."flavor" IS 'schema-owned table'"#.to_string(),
r#"COMMENT ON COLUMN "inventory"."flavor"."name" IS 'display name'"#.to_string(),
r#"CREATE INDEX IF NOT EXISTS "flavor_name_idx" ON "inventory"."flavor" ("name")"#.to_string(),
]
);
assert_eq!(
drop,
vec![r#"DROP TABLE IF EXISTS "inventory"."flavor""#.to_string()]
);
assert_eq!(
oracle_drop,
vec![r#"DROP TABLE "inventory"."flavor""#.to_string()]
);
}
#[test]
fn renders_mssql_schema_qualified_create_table_object_name() {
let table = TableDef::from_parts(
"flav'or",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_schema("inv'entory");
let statements = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect("mssql should escape schema-qualified object names");
assert_eq!(
statements,
vec![
"IF OBJECT_ID(N'inv''entory.flav''or', N'U') IS NULL CREATE TABLE [inv'entory].[flav'or] ([id] INTEGER PRIMARY KEY NOT NULL)".to_string()
]
);
}
#[test]
fn renders_namespace_schema_operations_for_supported_dialects() {
let create = SchemaOperation::CreateNamespace(NamespaceDef::new("inventory"));
let drop = SchemaOperation::DropNamespace {
name: "inventory".to_string(),
};
let cases = [
(
"postgresql",
r#"CREATE SCHEMA IF NOT EXISTS "inventory""#,
r#"DROP SCHEMA IF EXISTS "inventory""#,
),
(
"mysql",
"CREATE SCHEMA IF NOT EXISTS `inventory`",
"DROP SCHEMA IF EXISTS `inventory`",
),
(
"mariadb",
"CREATE SCHEMA IF NOT EXISTS `inventory`",
"DROP SCHEMA IF EXISTS `inventory`",
),
(
"mssql",
"IF SCHEMA_ID(N'inventory') IS NULL EXEC(N'CREATE SCHEMA [inventory]')",
"DROP SCHEMA IF EXISTS [inventory]",
),
];
for (dialect, create_sql, drop_sql) in cases {
let dialect = AnyDialect::parse(dialect).unwrap();
assert_eq!(
dialect.compile_schema_operation(&create).unwrap(),
vec![create_sql.to_string()]
);
assert_eq!(
dialect.compile_schema_operation(&drop).unwrap(),
vec![drop_sql.to_string()]
);
}
}
#[test]
fn renders_namespace_comments_for_supported_dialects() {
let create = SchemaOperation::CreateNamespace(
NamespaceDef::new("inventory").with_comment("warehouse schema"),
);
let set = SchemaOperation::SetNamespaceComment {
name: "inventory".to_string(),
comment: Some("new schema".to_string()),
};
let clear = SchemaOperation::SetNamespaceComment {
name: "inventory".to_string(),
comment: None,
};
let postgres_create = AnyDialect::parse("postgresql")
.unwrap()
.compile_schema_operation(&create)
.unwrap();
let postgres_set = AnyDialect::parse("postgresql")
.unwrap()
.compile_schema_operation(&set)
.unwrap();
let postgres_clear = AnyDialect::parse("postgresql")
.unwrap()
.compile_schema_operation(&clear)
.unwrap();
assert_eq!(
postgres_create,
vec![
r#"CREATE SCHEMA IF NOT EXISTS "inventory""#.to_string(),
r#"COMMENT ON SCHEMA "inventory" IS 'warehouse schema'"#.to_string(),
]
);
assert_eq!(
postgres_set,
vec![r#"COMMENT ON SCHEMA "inventory" IS 'new schema'"#.to_string()]
);
assert_eq!(
postgres_clear,
vec![r#"COMMENT ON SCHEMA "inventory" IS NULL"#.to_string()]
);
let mssql_set = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&set)
.unwrap();
let mssql_clear = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&clear)
.unwrap();
assert!(mssql_set[0].contains("sys.sp_updateextendedproperty"));
assert!(mssql_set[0].contains("sys.sp_addextendedproperty"));
assert!(mssql_set[0].contains("@level0type = N'SCHEMA'"));
assert!(mssql_set[0].contains("@value = N'new schema'"));
assert!(mssql_clear[0].contains("sys.sp_dropextendedproperty"));
assert!(mssql_clear[0].contains("@level0type = N'SCHEMA'"));
let mysql_error = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&create)
.expect_err("mysql schema comments are unsupported");
assert!(mysql_error.to_string().contains("namespace comments"));
}
#[test]
fn rejects_namespace_schema_operations_on_unsupported_dialects() {
let create = SchemaOperation::CreateNamespace(NamespaceDef::new("inventory"));
let drop = SchemaOperation::DropNamespace {
name: "inventory".to_string(),
};
for dialect in ["sqlite", "oracle"] {
let create_error = AnyDialect::parse(dialect)
.unwrap()
.compile_schema_operation(&create)
.expect_err("namespace DDL should be dialect-gated");
let drop_error = AnyDialect::parse(dialect)
.unwrap()
.compile_schema_operation(&drop)
.expect_err("namespace drop should be dialect-gated");
assert!(create_error.to_string().contains("namespaces/schemas"));
assert!(drop_error.to_string().contains("namespaces/schemas"));
}
}
#[test]
fn rejects_postgres_index_storage_parameters_on_other_dialects() {
let index = IndexDef::new("flavor_name_idx", vec!["name".to_string()])
.postgres_with(vec![("fillfactor".to_string(), "70".to_string())]);
let error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateIndex {
table: "flavor".to_string(),
index,
})
.expect_err("sqlite cannot render PostgreSQL index storage parameters");
assert!(error
.to_string()
.contains("PostgreSQL index storage parameters"));
}
#[test]
fn renders_sqlite_expression_and_partial_indexes() {
let index = IndexDef::new("flavor_lower_name_idx", Vec::new())
.expressions(vec!["LOWER(name)".to_string()])
.where_expr("deleted_at IS NULL");
let sql = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateIndex {
table: "flavor".to_string(),
index,
})
.unwrap();
assert_eq!(
sql,
vec![
r#"CREATE INDEX IF NOT EXISTS "flavor_lower_name_idx" ON "flavor" (LOWER(name)) WHERE deleted_at IS NULL"#.to_string()
]
);
}
#[test]
fn renders_create_index_by_backend() {
let operation = SchemaOperation::CreateIndex {
table: "flavor".to_string(),
index: IndexDef::new("flavor_name_idx", vec!["name".to_string()]),
};
let cases = [
(
"sqlite",
r#"CREATE INDEX IF NOT EXISTS "flavor_name_idx" ON "flavor" ("name")"#,
),
(
"postgresql",
r#"CREATE INDEX IF NOT EXISTS "flavor_name_idx" ON "flavor" ("name")"#,
),
(
"mysql",
"CREATE INDEX `flavor_name_idx` ON `flavor` (`name`)",
),
(
"mariadb",
"CREATE INDEX `flavor_name_idx` ON `flavor` (`name`)",
),
(
"mssql",
"CREATE INDEX [flavor_name_idx] ON [flavor] ([name])",
),
(
"oracle",
r#"CREATE INDEX "flavor_name_idx" ON "flavor" ("name")"#,
),
];
for (dialect, expected) in cases {
let sql = AnyDialect::parse(dialect)
.unwrap()
.compile_schema_operation(&operation)
.unwrap();
assert_eq!(sql, vec![expected.to_string()]);
}
}
#[test]
fn renders_mssql_filtered_include_indexes() {
let index = IndexDef::new("flavor_name_idx", vec!["name".to_string()])
.include_columns(vec!["rating".to_string()])
.where_expr("[name] IS NOT NULL");
let sql = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateIndex {
table: "flavor".to_string(),
index,
})
.unwrap();
assert_eq!(
sql,
vec![
"CREATE INDEX [flavor_name_idx] ON [flavor] ([name]) INCLUDE ([rating]) WHERE [name] IS NOT NULL"
.to_string()
]
);
}
#[test]
fn rejects_empty_index_keys() {
let index = IndexDef::new("empty_idx", Vec::new());
let error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateIndex {
table: "flavor".to_string(),
index,
})
.expect_err("empty indexes should fail");
assert!(error
.to_string()
.contains("must reference at least one column or SQL expression"));
}
#[test]
fn rejects_unsupported_advanced_index_metadata_by_dialect() {
let cases = vec![
(
"sqlite",
IndexDef::new("flavor_name_include_idx", vec!["name".to_string()])
.include_columns(vec!["rating".to_string()]),
"index INCLUDE columns",
),
(
"sqlite",
IndexDef::new("flavor_name_method_idx", vec!["name".to_string()]).method("btree"),
"index methods",
),
(
"mysql",
IndexDef::new("flavor_name_partial_idx", vec!["name".to_string()])
.where_expr("deleted_at IS NULL"),
"filtered or partial indexes",
),
(
"mysql",
IndexDef::new("flavor_lower_name_idx", Vec::new())
.expressions(vec!["LOWER(name)".to_string()]),
"expression indexes",
),
];
for (dialect, index, feature) in cases {
let error = AnyDialect::parse(dialect)
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateIndex {
table: "flavor".to_string(),
index,
})
.expect_err("unsupported advanced index metadata should fail");
assert!(
error.to_string().contains(feature),
"expected {dialect} error to mention {feature}, got {error}"
);
}
}
#[test]
fn rejects_invalid_index_method_names() {
let index =
IndexDef::new("flavor_name_method_idx", vec!["name".to_string()]).method("btree;drop");
let error = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateIndex {
table: "flavor".to_string(),
index,
})
.expect_err("invalid index method names should fail");
assert!(error.to_string().contains("invalid index method"));
}
#[test]
fn rejects_postgres_unique_nulls_not_distinct_on_other_dialects() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
vec![
UniqueConstraintDef::new("flavor_id_unique", vec!["id".to_string()])
.nulls_not_distinct(),
],
Vec::new(),
);
let error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("sqlite cannot render PostgreSQL unique NULLS NOT DISTINCT");
assert!(error
.to_string()
.contains("PostgreSQL unique NULLS NOT DISTINCT"));
}
#[test]
fn renders_alter_column_by_backend() {
let operation = SchemaOperation::AlterColumn {
table: "flavor".to_string(),
column: ColumnDef::new("rating", FieldKind::Integer).nullable(true),
};
let cases = [
(
"postgresql",
r#"ALTER TABLE "flavor" ALTER COLUMN "rating" TYPE INTEGER"#,
),
(
"mysql",
"ALTER TABLE `flavor` MODIFY COLUMN `rating` INTEGER COMMENT ''",
),
(
"mariadb",
"ALTER TABLE `flavor` MODIFY COLUMN `rating` INTEGER COMMENT ''",
),
(
"mssql",
"ALTER TABLE [flavor] ALTER COLUMN [rating] INTEGER NULL",
),
(
"oracle",
r#"ALTER TABLE "flavor" MODIFY ("rating" INTEGER)"#,
),
];
for (dialect, expected) in cases {
let sql = AnyDialect::parse(dialect)
.unwrap()
.compile_schema_operation(&operation)
.unwrap();
assert_eq!(sql, vec![expected.to_string()]);
}
}
#[test]
fn renders_identity_columns_by_backend() {
let column = ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.with_identity(IdentityDef::new().always(true).start(10).increment(5));
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![column],
Vec::new(),
Vec::new(),
Vec::new(),
);
let cases = [
(
"sqlite",
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)"#,
),
(
"postgresql",
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 5) PRIMARY KEY NOT NULL)"#,
),
(
"mysql",
"CREATE TABLE IF NOT EXISTS `flavor` (`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)",
),
(
"mariadb",
"CREATE TABLE IF NOT EXISTS `flavor` (`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)",
),
(
"mssql",
"IF OBJECT_ID(N'flavor', N'U') IS NULL CREATE TABLE [flavor] ([id] INTEGER IDENTITY(10, 5) PRIMARY KEY NOT NULL)",
),
(
"oracle",
r#"CREATE TABLE "flavor" ("id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 5) PRIMARY KEY NOT NULL)"#,
),
];
for (dialect, expected) in cases {
let sql = AnyDialect::parse(dialect)
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
assert_eq!(sql, vec![expected.to_string()]);
}
}
#[test]
fn renders_identity_sequence_options_by_backend() {
let column = ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.with_identity(
IdentityDef::new()
.always(true)
.start(10)
.increment(5)
.min_value(1)
.max_value(1000)
.cycle(true)
.cache(20),
);
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![column],
Vec::new(),
Vec::new(),
Vec::new(),
);
let cases = [
(
"postgresql",
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 5 MINVALUE 1 MAXVALUE 1000 CYCLE CACHE 20) PRIMARY KEY NOT NULL)"#,
),
(
"oracle",
r#"CREATE TABLE "flavor" ("id" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 5 MINVALUE 1 MAXVALUE 1000 CYCLE CACHE 20) PRIMARY KEY NOT NULL)"#,
),
];
for (dialect, expected) in cases {
let sql = AnyDialect::parse(dialect)
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
assert_eq!(sql, vec![expected.to_string()]);
}
for dialect in ["sqlite", "mysql", "mariadb", "mssql"] {
let error = AnyDialect::parse(dialect)
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.expect_err("dialect cannot render identity sequence options");
assert!(error.to_string().contains("identity sequence options"));
}
}
#[test]
fn renders_identity_no_bound_options_by_backend() {
let column = ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.with_identity(
IdentityDef::new()
.always(true)
.no_min_value(true)
.no_max_value(true),
);
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![column],
Vec::new(),
Vec::new(),
Vec::new(),
);
let cases = [
(
"postgresql",
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER GENERATED ALWAYS AS IDENTITY (NO MINVALUE NO MAXVALUE) PRIMARY KEY NOT NULL)"#,
),
(
"oracle",
r#"CREATE TABLE "flavor" ("id" INTEGER GENERATED ALWAYS AS IDENTITY (NOMINVALUE NOMAXVALUE) PRIMARY KEY NOT NULL)"#,
),
];
for (dialect, expected) in cases {
let sql = AnyDialect::parse(dialect)
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
assert_eq!(sql, vec![expected.to_string()]);
}
for dialect in ["sqlite", "mysql", "mariadb", "mssql"] {
let error = AnyDialect::parse(dialect)
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.expect_err("dialect cannot render identity no-bound options");
assert!(error.to_string().contains("identity sequence options"));
}
}
#[test]
fn rejects_conflicting_identity_no_bound_options() {
let column = ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.with_identity(IdentityDef::new().min_value(1).no_min_value(true));
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![column],
Vec::new(),
Vec::new(),
Vec::new(),
);
let error = AnyDialect::parse("postgresql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("conflicting identity no-bound options are invalid");
assert!(error.to_string().contains("no_min_value"));
}
#[test]
fn renders_oracle_identity_ordering() {
let column = ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.with_identity(IdentityDef::new().always(true).order(true));
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![column],
Vec::new(),
Vec::new(),
Vec::new(),
);
let sql = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
assert_eq!(
sql,
vec![
r#"CREATE TABLE "flavor" ("id" INTEGER GENERATED ALWAYS AS IDENTITY (ORDER) PRIMARY KEY NOT NULL)"#.to_string()
]
);
let error = AnyDialect::parse("postgresql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("PostgreSQL cannot render Oracle identity ordering");
assert!(error.to_string().contains("Oracle identity ordering"));
}
#[test]
fn renders_oracle_identity_on_null() {
let column = ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.with_identity(IdentityDef::new().on_null(true));
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![column],
Vec::new(),
Vec::new(),
Vec::new(),
);
let sql = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
assert_eq!(
sql,
vec![
r#"CREATE TABLE "flavor" ("id" INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY NOT NULL)"#.to_string()
]
);
let error = AnyDialect::parse("postgresql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.expect_err("PostgreSQL cannot render Oracle identity ON NULL");
assert!(error.to_string().contains("Oracle identity ON NULL"));
let invalid = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.with_identity(IdentityDef::new().always(true).on_null(true))],
Vec::new(),
Vec::new(),
Vec::new(),
);
let error = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(invalid))
.expect_err("ON NULL requires BY DEFAULT identity");
assert!(error.to_string().contains("requires BY DEFAULT"));
}
#[test]
fn renders_oracle_defaults_before_nullability() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::String)
.with_max_length(255)
.primary_key(true),
ColumnDef::new("rating", FieldKind::Integer).with_server_default("0"),
],
Vec::new(),
Vec::new(),
Vec::new(),
);
let sql = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect("oracle should render column defaults");
assert_eq!(
sql,
vec![
r#"CREATE TABLE "flavor" ("id" VARCHAR2(255) PRIMARY KEY NOT NULL, "rating" INTEGER DEFAULT 0 NOT NULL)"#.to_string()
]
);
}
#[test]
fn renders_table_comments_by_backend() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_comment("chef's table");
let cases = [
(
"postgresql",
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL)"#,
r#"COMMENT ON TABLE "flavor" IS 'chef''s table'"#,
],
),
(
"mysql",
vec![
"CREATE TABLE IF NOT EXISTS `flavor` (`id` INTEGER NOT NULL PRIMARY KEY)",
"ALTER TABLE `flavor` COMMENT = 'chef''s table'",
],
),
(
"mariadb",
vec![
"CREATE TABLE IF NOT EXISTS `flavor` (`id` INTEGER NOT NULL PRIMARY KEY)",
"ALTER TABLE `flavor` COMMENT = 'chef''s table'",
],
),
(
"oracle",
vec![
r#"CREATE TABLE "flavor" ("id" INTEGER PRIMARY KEY NOT NULL)"#,
r#"COMMENT ON TABLE "flavor" IS 'chef''s table'"#,
],
),
];
for (dialect, expected) in cases {
let sql = AnyDialect::parse(dialect)
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
assert_eq!(
sql,
expected.into_iter().map(str::to_string).collect::<Vec<_>>()
);
}
let sqlite = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
assert_eq!(
sqlite,
vec![r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL)"#]
);
let mssql = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.unwrap();
assert_eq!(
mssql[0],
"IF OBJECT_ID(N'flavor', N'U') IS NULL CREATE TABLE [flavor] ([id] INTEGER PRIMARY KEY NOT NULL)"
);
assert!(mssql[1].contains("sys.sp_updateextendedproperty"));
assert!(mssql[1].contains("sys.sp_addextendedproperty"));
assert!(mssql[1].contains("@value = N'chef''s table'"));
}
#[test]
fn renders_column_comments_by_backend() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("name", FieldKind::String).with_comment("chef's name"),
],
Vec::new(),
Vec::new(),
Vec::new(),
);
let postgres = AnyDialect::parse("postgresql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
assert_eq!(
postgres,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL, "name" TEXT NOT NULL)"#.to_string(),
r#"COMMENT ON COLUMN "flavor"."name" IS 'chef''s name'"#.to_string(),
]
);
let mysql = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
assert_eq!(
mysql,
vec![
"CREATE TABLE IF NOT EXISTS `flavor` (`id` INTEGER NOT NULL PRIMARY KEY, `name` TEXT NOT NULL COMMENT 'chef''s name')".to_string()
]
);
let sqlite = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
assert_eq!(
sqlite,
vec![r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL, "name" TEXT NOT NULL)"#.to_string()]
);
let mssql = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.unwrap();
assert_eq!(
mssql[0],
"IF OBJECT_ID(N'flavor', N'U') IS NULL CREATE TABLE [flavor] ([id] INTEGER PRIMARY KEY NOT NULL, [name] NVARCHAR(255) NOT NULL)"
);
assert!(mssql[1].contains("sys.sp_updateextendedproperty"));
assert!(mssql[1].contains("sys.sp_addextendedproperty"));
assert!(mssql[1].contains("@level2type = N'COLUMN'"));
assert!(mssql[1].contains("@value = N'chef''s name'"));
}
#[test]
fn renders_drop_index_by_backend() {
let operation = SchemaOperation::DropIndex {
table: "flavor".to_string(),
name: "flavor_name_idx".to_string(),
};
let cases = [
("sqlite", r#"DROP INDEX IF EXISTS "flavor_name_idx""#),
("postgresql", r#"DROP INDEX IF EXISTS "flavor_name_idx""#),
("mysql", "DROP INDEX `flavor_name_idx` ON `flavor`"),
("mariadb", "DROP INDEX `flavor_name_idx` ON `flavor`"),
("mssql", "DROP INDEX [flavor_name_idx] ON [flavor]"),
("oracle", r#"DROP INDEX "flavor_name_idx""#),
];
for (dialect, expected) in cases {
let sql = AnyDialect::parse(dialect)
.unwrap()
.compile_schema_operation(&operation)
.unwrap();
assert_eq!(sql, vec![expected.to_string()]);
}
}
#[test]
fn renders_table_comment_changes() {
let set = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTableComment {
table: "flavor".to_string(),
comment: Some("new table".to_string()),
})
.unwrap();
let clear = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTableComment {
table: "flavor".to_string(),
comment: None,
})
.unwrap();
assert_eq!(
set,
vec![r#"COMMENT ON TABLE "flavor" IS 'new table'"#.to_string()]
);
assert_eq!(
clear,
vec![r#"COMMENT ON TABLE "flavor" IS NULL"#.to_string()]
);
let mssql_set = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTableComment {
table: "flavor".to_string(),
comment: Some("new table".to_string()),
})
.unwrap();
let mssql_clear = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTableComment {
table: "flavor".to_string(),
comment: None,
})
.unwrap();
assert!(mssql_set[0].contains("sys.sp_updateextendedproperty"));
assert!(mssql_set[0].contains("sys.sp_addextendedproperty"));
assert!(mssql_set[0].contains("@value = N'new table'"));
assert!(mssql_clear[0].contains("sys.sp_dropextendedproperty"));
assert!(mssql_clear[0].contains("ep.name = N'MS_Description'"));
}
#[test]
fn renders_column_comment_changes() {
let commented_column = ColumnDef::new("name", FieldKind::String).with_comment("new column");
let plain_column = ColumnDef::new("name", FieldKind::String);
let set = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetColumnComment {
table: "flavor".to_string(),
column: commented_column.clone(),
})
.unwrap();
let clear = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetColumnComment {
table: "flavor".to_string(),
column: plain_column.clone(),
})
.unwrap();
assert_eq!(
set,
vec![r#"COMMENT ON COLUMN "flavor"."name" IS 'new column'"#.to_string()]
);
assert_eq!(
clear,
vec![r#"COMMENT ON COLUMN "flavor"."name" IS NULL"#.to_string()]
);
let mssql_set = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetColumnComment {
table: "flavor".to_string(),
column: commented_column.clone(),
})
.unwrap();
let mysql_set = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetColumnComment {
table: "flavor".to_string(),
column: commented_column,
})
.unwrap();
let mysql_clear = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetColumnComment {
table: "flavor".to_string(),
column: plain_column,
})
.unwrap();
assert!(mssql_set[0].contains("sys.sp_updateextendedproperty"));
assert!(mssql_set[0].contains("@level2name = N'name'"));
assert!(mssql_set[0].contains("@value = N'new column'"));
assert_eq!(
mysql_set,
vec!["ALTER TABLE `flavor` MODIFY COLUMN `name` TEXT NOT NULL COMMENT 'new column'"]
);
assert_eq!(
mysql_clear,
vec!["ALTER TABLE `flavor` MODIFY COLUMN `name` TEXT NOT NULL COMMENT ''"]
);
}
#[test]
fn renders_table_tablespaces_by_backend() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_tablespace("fastspace");
let postgres_create = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let oracle_create = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let sqlite_create = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let mysql_create = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let mariadb_create = AnyDialect::parse("mariadb")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let mssql_create = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.unwrap();
let set = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTableTablespace {
table: "flavor".to_string(),
tablespace: Some("fastspace".to_string()),
})
.unwrap();
let clear = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTableTablespace {
table: "flavor".to_string(),
tablespace: None,
})
.unwrap();
let oracle_set = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTableTablespace {
table: "flavor".to_string(),
tablespace: Some("fastspace".to_string()),
})
.unwrap();
let oracle_clear = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTableTablespace {
table: "flavor".to_string(),
tablespace: None,
})
.unwrap();
let mysql_set = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTableTablespace {
table: "flavor".to_string(),
tablespace: Some("fastspace".to_string()),
})
.unwrap();
let mysql_clear = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTableTablespace {
table: "flavor".to_string(),
tablespace: None,
})
.unwrap();
let mariadb_set = AnyDialect::parse("mariadb")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTableTablespace {
table: "flavor".to_string(),
tablespace: Some("fastspace".to_string()),
})
.unwrap();
let mariadb_clear = AnyDialect::parse("mariadb")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTableTablespace {
table: "flavor".to_string(),
tablespace: None,
})
.unwrap();
let mssql_change_error = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTableTablespace {
table: "flavor".to_string(),
tablespace: Some("fastspace".to_string()),
})
.expect_err("sql server table filegroup changes are unsupported");
assert_eq!(
postgres_create,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL) TABLESPACE "fastspace""#
.to_string()
]
);
assert_eq!(
sqlite_create,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL)"#
.to_string()
]
);
assert_eq!(
oracle_create,
vec![
r#"CREATE TABLE "flavor" ("id" INTEGER PRIMARY KEY NOT NULL) TABLESPACE "fastspace""#
.to_string()
]
);
assert_eq!(
mysql_create,
vec![
"CREATE TABLE IF NOT EXISTS `flavor` (`id` INTEGER NOT NULL PRIMARY KEY) TABLESPACE `fastspace`".to_string()
]
);
assert_eq!(mariadb_create, mysql_create);
assert_eq!(
mssql_create,
vec![
"IF OBJECT_ID(N'flavor', N'U') IS NULL CREATE TABLE [flavor] ([id] INTEGER PRIMARY KEY NOT NULL) ON [fastspace]"
.to_string()
]
);
assert_eq!(
set,
vec![r#"ALTER TABLE "flavor" SET TABLESPACE "fastspace""#.to_string()]
);
assert_eq!(
clear,
vec![r#"ALTER TABLE "flavor" SET TABLESPACE "pg_default""#.to_string()]
);
assert_eq!(
oracle_set,
vec![r#"ALTER TABLE "flavor" MOVE TABLESPACE "fastspace""#.to_string()]
);
assert_eq!(
oracle_clear,
vec![r#"ALTER TABLE "flavor" MOVE"#.to_string()]
);
assert_eq!(
mysql_set,
vec!["ALTER TABLE `flavor` TABLESPACE `fastspace`".to_string()]
);
assert_eq!(
mysql_clear,
vec!["ALTER TABLE `flavor` TABLESPACE `innodb_file_per_table`".to_string()]
);
assert_eq!(mariadb_set, mysql_set);
assert_eq!(mariadb_clear, mysql_clear);
assert!(mssql_change_error
.to_string()
.contains("SQL Server table filegroup changes"));
}
#[test]
fn renders_oracle_table_compression() {
let basic = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_oracle_compress();
let compressed_tablespace = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_oracle_compress_level(6)
.with_tablespace("fastspace");
let basic_create = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(basic))
.unwrap();
let compressed_create = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(compressed_tablespace.clone()))
.unwrap();
let sqlite_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(compressed_tablespace))
.expect_err("sqlite oracle compression is unsupported");
assert_eq!(
basic_create,
vec![r#"CREATE TABLE "flavor" ("id" INTEGER PRIMARY KEY NOT NULL) COMPRESS"#.to_string()]
);
assert_eq!(
compressed_create,
vec![
r#"CREATE TABLE "flavor" ("id" INTEGER PRIMARY KEY NOT NULL) COMPRESS FOR 6 TABLESPACE "fastspace""#
.to_string()
]
);
assert!(sqlite_error
.to_string()
.contains("Oracle table compression"));
}
#[test]
fn renders_mysql_table_options() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_mysql_engine("InnoDB")
.with_mysql_charset("utf8mb4")
.with_mysql_collation("utf8mb4_unicode_ci")
.with_mysql_row_format("DYNAMIC")
.with_mysql_key_block_size(8)
.with_mysql_pack_keys(true)
.with_mysql_checksum(true)
.with_mysql_delay_key_write(true)
.with_mysql_stats_persistent(true)
.with_mysql_stats_auto_recalc(false)
.with_mysql_stats_sample_pages(32)
.with_mysql_avg_row_length(64)
.with_mysql_max_rows(1000)
.with_mysql_min_rows(10)
.with_mysql_insert_method("LAST")
.with_mysql_data_directory("/var/lib/mysql/data")
.with_mysql_index_directory("/var/lib/mysql/index")
.with_mysql_connection("mysql://remote.example/db/flavor")
.with_mysql_union(vec!["flavor_hot".to_string(), "flavor_cold".to_string()])
.with_mysql_partition_by("HASH (id)")
.with_mysql_partitions(4)
.with_mysql_subpartition_by("KEY (id)")
.with_mysql_subpartitions(2)
.with_mysql_auto_increment(101);
let mysql_create = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let mariadb_create = AnyDialect::parse("mariadb")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let postgres_create = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.unwrap();
let alter = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTableMysqlOptions {
table: "flavor".to_string(),
engine: Some("MyISAM".to_string()),
charset: Some("latin1".to_string()),
collation: Some("latin1_swedish_ci".to_string()),
row_format: Some("COMPACT".to_string()),
key_block_size: Some(4),
pack_keys: Some(false),
checksum: Some(false),
delay_key_write: Some(false),
stats_persistent: Some(false),
stats_auto_recalc: Some(true),
stats_sample_pages: Some(16),
avg_row_length: Some(32),
max_rows: Some(500),
min_rows: Some(5),
insert_method: Some("FIRST".to_string()),
data_directory: Some("/srv/mysql/data".to_string()),
index_directory: Some("/srv/mysql/index".to_string()),
connection: Some("mysql://remote.example/db/flavor_archive".to_string()),
union: vec![
"flavor_archive_hot".to_string(),
"flavor_archive_cold".to_string(),
],
partition_by: Some("LINEAR HASH (id)".to_string()),
partitions: Some(8),
subpartition_by: Some("KEY (code)".to_string()),
subpartitions: Some(4),
auto_increment: Some(202),
})
.unwrap();
let postgres_error = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTableMysqlOptions {
table: "flavor".to_string(),
engine: Some("InnoDB".to_string()),
charset: None,
collation: None,
row_format: None,
key_block_size: None,
pack_keys: None,
checksum: None,
delay_key_write: None,
stats_persistent: None,
stats_auto_recalc: None,
stats_sample_pages: None,
avg_row_length: None,
max_rows: None,
min_rows: None,
insert_method: None,
data_directory: None,
index_directory: None,
connection: None,
union: Vec::new(),
partition_by: None,
partitions: None,
subpartition_by: None,
subpartitions: None,
auto_increment: None,
})
.expect_err("postgres mysql table options are unsupported");
assert_eq!(
mysql_create,
vec![
"CREATE TABLE IF NOT EXISTS `flavor` (`id` INTEGER NOT NULL PRIMARY KEY) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC KEY_BLOCK_SIZE = 8 PACK_KEYS = 1 CHECKSUM = 1 DELAY_KEY_WRITE = 1 STATS_PERSISTENT = 1 STATS_AUTO_RECALC = 0 STATS_SAMPLE_PAGES = 32 AVG_ROW_LENGTH = 64 MAX_ROWS = 1000 MIN_ROWS = 10 INSERT_METHOD = LAST DATA DIRECTORY = '/var/lib/mysql/data' INDEX DIRECTORY = '/var/lib/mysql/index' CONNECTION = 'mysql://remote.example/db/flavor' UNION = (`flavor_hot`, `flavor_cold`) PARTITION BY HASH (id) PARTITIONS 4 SUBPARTITION BY KEY (id) SUBPARTITIONS 2 AUTO_INCREMENT = 101".to_string()
]
);
assert_eq!(mariadb_create, mysql_create);
assert_eq!(
postgres_create,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL)"#
.to_string()
]
);
assert_eq!(
alter,
vec![
"ALTER TABLE `flavor` ENGINE = MyISAM DEFAULT CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = COMPACT KEY_BLOCK_SIZE = 4 PACK_KEYS = 0 CHECKSUM = 0 DELAY_KEY_WRITE = 0 STATS_PERSISTENT = 0 STATS_AUTO_RECALC = 1 STATS_SAMPLE_PAGES = 16 AVG_ROW_LENGTH = 32 MAX_ROWS = 500 MIN_ROWS = 5 INSERT_METHOD = FIRST DATA DIRECTORY = '/srv/mysql/data' INDEX DIRECTORY = '/srv/mysql/index' CONNECTION = 'mysql://remote.example/db/flavor_archive' UNION = (`flavor_archive_hot`, `flavor_archive_cold`) PARTITION BY LINEAR HASH (id) PARTITIONS 8 SUBPARTITION BY KEY (code) SUBPARTITIONS 4 AUTO_INCREMENT = 202".to_string()
]
);
assert!(postgres_error.to_string().contains("mysql table options"));
}
#[test]
fn renders_postgres_table_inheritance() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_postgres_inherits(vec!["base_flavor".to_string()]);
let postgres_create = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let sqlite_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("sqlite postgres inheritance is unsupported");
let alter = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresInherits {
table: "flavor".to_string(),
add: vec!["base_flavor".to_string()],
drop: vec!["old_base".to_string()],
})
.unwrap();
let mysql_error = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTablePostgresInherits {
table: "flavor".to_string(),
add: vec!["base_flavor".to_string()],
drop: Vec::new(),
})
.expect_err("mysql postgres inheritance is unsupported");
assert_eq!(
postgres_create,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL) INHERITS ("base_flavor")"#
.to_string()
]
);
assert_eq!(
alter,
vec![
r#"ALTER TABLE "flavor" NO INHERIT "old_base""#.to_string(),
r#"ALTER TABLE "flavor" INHERIT "base_flavor""#.to_string(),
]
);
assert!(sqlite_error
.to_string()
.contains("PostgreSQL table inheritance"));
assert!(mysql_error
.to_string()
.contains("PostgreSQL table inheritance"));
}
#[test]
fn renders_postgres_table_storage_parameters() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_postgres_with(vec![
("fillfactor".to_string(), "70".to_string()),
("toast.autovacuum_enabled".to_string(), "false".to_string()),
]);
let postgres_create = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let sqlite_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("sqlite postgres storage parameters are unsupported");
let alter = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresWith {
table: "flavor".to_string(),
set: vec![
("fillfactor".to_string(), "70".to_string()),
("autovacuum_enabled".to_string(), "false".to_string()),
],
reset: vec!["toast.autovacuum_enabled".to_string()],
})
.unwrap();
let mysql_error = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTablePostgresWith {
table: "flavor".to_string(),
set: vec![("fillfactor".to_string(), "70".to_string())],
reset: Vec::new(),
})
.expect_err("mysql postgres storage parameters are unsupported");
assert_eq!(
postgres_create,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL) WITH (fillfactor = 70, toast.autovacuum_enabled = false)"#
.to_string()
]
);
assert_eq!(
alter,
vec![
r#"ALTER TABLE "flavor" SET (fillfactor = 70, autovacuum_enabled = false)"#.to_string(),
r#"ALTER TABLE "flavor" RESET (toast.autovacuum_enabled)"#.to_string(),
]
);
assert!(sqlite_error
.to_string()
.contains("PostgreSQL table storage parameters"));
assert!(mysql_error
.to_string()
.contains("PostgreSQL table storage parameters"));
}
#[test]
fn renders_postgres_table_access_methods() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_postgres_using("heap");
let postgres_create = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let sqlite_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("sqlite postgres table access methods are unsupported");
let alter = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresUsing {
table: "flavor".to_string(),
using: Some("custom_heap".to_string()),
})
.unwrap();
let reset = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresUsing {
table: "flavor".to_string(),
using: None,
})
.unwrap();
let mysql_error = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTablePostgresUsing {
table: "flavor".to_string(),
using: Some("heap".to_string()),
})
.expect_err("mysql postgres table access methods are unsupported");
assert_eq!(
postgres_create,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL) USING "heap""#
.to_string()
]
);
assert_eq!(
alter,
vec![r#"ALTER TABLE "flavor" SET ACCESS METHOD "custom_heap""#.to_string()]
);
assert_eq!(
reset,
vec![r#"ALTER TABLE "flavor" SET ACCESS METHOD "heap""#.to_string()]
);
assert!(sqlite_error
.to_string()
.contains("PostgreSQL table access methods"));
assert!(mysql_error
.to_string()
.contains("PostgreSQL table access methods"));
}
#[test]
fn renders_postgres_unlogged_tables() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.postgres_unlogged();
let postgres_create = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let set_unlogged = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresUnlogged {
table: "flavor".to_string(),
unlogged: true,
})
.unwrap();
let set_logged = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresUnlogged {
table: "flavor".to_string(),
unlogged: false,
})
.unwrap();
let sqlite_create_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("sqlite postgres unlogged tables are unsupported");
let sqlite_alter_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTablePostgresUnlogged {
table: "flavor".to_string(),
unlogged: true,
})
.expect_err("sqlite postgres unlogged tables are unsupported");
assert_eq!(
postgres_create,
vec![
r#"CREATE UNLOGGED TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL)"#
.to_string()
]
);
assert_eq!(
set_unlogged,
vec![r#"ALTER TABLE "flavor" SET UNLOGGED"#.to_string()]
);
assert_eq!(
set_logged,
vec![r#"ALTER TABLE "flavor" SET LOGGED"#.to_string()]
);
assert!(sqlite_create_error
.to_string()
.contains("PostgreSQL unlogged tables"));
assert!(sqlite_alter_error
.to_string()
.contains("PostgreSQL unlogged tables"));
}
#[test]
fn renders_sqlite_conflict_clauses() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.with_sqlite_on_conflict_primary_key("REPLACE"),
ColumnDef::new("name", FieldKind::String).with_sqlite_on_conflict_not_null("FAIL"),
ColumnDef::new("slug", FieldKind::String).with_sqlite_on_conflict_unique("ABORT"),
],
Vec::new(),
vec![
UniqueConstraintDef::new("flavor_name_unique", vec!["name".to_string()])
.with_sqlite_on_conflict("IGNORE"),
],
Vec::new(),
);
let sqlite = AnyDialect::parse("sqlite").unwrap();
let sqlite_create = sqlite
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let postgres_column_error = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("postgres sqlite conflict clauses are unsupported");
let unique_only = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
vec![
UniqueConstraintDef::new("flavor_name_unique", vec!["name".to_string()])
.with_sqlite_on_conflict("IGNORE"),
],
Vec::new(),
);
let postgres_unique_error = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(unique_only))
.expect_err("postgres sqlite unique conflict clauses are unsupported");
assert_eq!(
sqlite_create,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY ON CONFLICT REPLACE NOT NULL, "name" TEXT NOT NULL ON CONFLICT FAIL, "slug" TEXT NOT NULL, CONSTRAINT "flavor_name_unique" UNIQUE ("name") ON CONFLICT IGNORE)"#.to_string()
]
);
assert!(postgres_column_error.to_string().contains("SQLite"));
assert!(postgres_unique_error.to_string().contains("SQLite"));
}
#[test]
fn renders_sqlite_table_options() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("name", FieldKind::String),
],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_sqlite_strict(true)
.with_sqlite_without_rowid(true);
let sqlite_create = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let postgres_error = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("postgres sqlite table options are unsupported");
assert_eq!(
sqlite_create,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL, "name" TEXT NOT NULL) STRICT, WITHOUT ROWID"#.to_string()
]
);
assert!(postgres_error.to_string().contains("SQLite table options"));
}
#[test]
fn renders_postgres_table_partition_keys() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_postgres_partition_by("RANGE (id)");
let postgres_create = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let recreate = PostgresDialect
.compile_schema_operation(&SchemaOperation::RecreateTable(table.clone()))
.unwrap();
let sqlite_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("sqlite postgres table partitioning is unsupported");
assert_eq!(
postgres_create,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL) PARTITION BY RANGE (id)"#
.to_string()
]
);
assert_eq!(
recreate,
vec![
r#"DROP TABLE IF EXISTS "flavor""#.to_string(),
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL) PARTITION BY RANGE (id)"#
.to_string(),
]
);
assert!(sqlite_error
.to_string()
.contains("PostgreSQL table partitioning"));
}
#[test]
fn renders_postgres_child_partitions() {
let table = TableDef::from_parts(
"flavor_2026",
"Flavor2026",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("name", FieldKind::String),
],
Vec::new(),
vec![UniqueConstraintDef::new(
"flavor_2026_id_unique",
vec!["id".to_string()],
)],
Vec::new(),
)
.with_postgres_partition_of("flavor")
.with_postgres_partition_for("FOR VALUES FROM (2026) TO (2027)")
.with_postgres_with(vec![("fillfactor".to_string(), "70".to_string())]);
let postgres_create = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let empty_child_create = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(
TableDef::from_parts(
"flavor_2027",
"Flavor2027",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_postgres_partition_of("flavor")
.with_postgres_partition_for("FOR VALUES FROM (2027) TO (2028)"),
))
.unwrap();
let sqlite_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.expect_err("sqlite postgres table partitions are unsupported");
let missing_parent_error = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(
TableDef::from_parts(
"flavor_2026",
"Flavor2026",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_postgres_partition_for("FOR VALUES FROM (2026) TO (2027)"),
))
.expect_err("partition bound without parent is invalid");
let inherits_error = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(
table.with_postgres_inherits(vec!["base_flavor".to_string()]),
))
.expect_err("partition children cannot also inherit");
assert_eq!(
postgres_create,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor_2026" PARTITION OF "flavor" (CONSTRAINT "flavor_2026_id_unique" UNIQUE ("id")) FOR VALUES FROM (2026) TO (2027) WITH (fillfactor = 70)"#
.to_string()
]
);
assert_eq!(
empty_child_create,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor_2027" PARTITION OF "flavor" FOR VALUES FROM (2027) TO (2028)"#
.to_string()
]
);
assert!(sqlite_error
.to_string()
.contains("PostgreSQL table partitions"));
assert!(missing_parent_error
.to_string()
.contains("missing a partition parent"));
assert!(inherits_error
.to_string()
.contains("cannot also use INHERITS"));
}
#[test]
fn renders_postgres_partition_attach_and_detach() {
let attach = PostgresDialect
.compile_schema_operation(&SchemaOperation::AttachPostgresPartition {
table: "flavor_2026".to_string(),
parent: "flavor".to_string(),
bound: "FOR VALUES FROM (2026) TO (2027)".to_string(),
})
.unwrap();
let detach = PostgresDialect
.compile_schema_operation(&SchemaOperation::DetachPostgresPartition {
table: "flavor_2026".to_string(),
parent: "flavor".to_string(),
})
.unwrap();
let sqlite_attach_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::AttachPostgresPartition {
table: "flavor_2026".to_string(),
parent: "flavor".to_string(),
bound: "FOR VALUES FROM (2026) TO (2027)".to_string(),
})
.expect_err("sqlite postgres table partitions are unsupported");
let sqlite_detach_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::DetachPostgresPartition {
table: "flavor_2026".to_string(),
parent: "flavor".to_string(),
})
.expect_err("sqlite postgres table partitions are unsupported");
assert_eq!(
attach,
vec![
r#"ALTER TABLE "flavor" ATTACH PARTITION "flavor_2026" FOR VALUES FROM (2026) TO (2027)"#
.to_string()
]
);
assert_eq!(
detach,
vec![r#"ALTER TABLE "flavor" DETACH PARTITION "flavor_2026""#.to_string()]
);
assert!(sqlite_attach_error
.to_string()
.contains("PostgreSQL table partitions"));
assert!(sqlite_detach_error
.to_string()
.contains("PostgreSQL table partitions"));
}
#[test]
fn renders_add_and_drop_constraints() {
let add = PostgresDialect
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Check(
CheckConstraintDef::new("rating >= 0").named("rating_check"),
),
})
.unwrap();
let drop = PostgresDialect
.compile_schema_operation(&SchemaOperation::DropConstraint {
table: "flavor".to_string(),
name: "rating_check".to_string(),
})
.unwrap();
assert_eq!(
add,
vec![
r#"ALTER TABLE "flavor" ADD CONSTRAINT "rating_check" CHECK (rating >= 0)"#.to_string()
]
);
assert_eq!(
drop,
vec![r#"ALTER TABLE "flavor" DROP CONSTRAINT "rating_check""#.to_string()]
);
}
#[test]
fn renders_regex_check_constraints_by_backend() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::String).primary_key(true),
ColumnDef::new("code", FieldKind::String),
],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_check_constraints(vec![CheckConstraintDef::new(
"ormdantic_regex_match(code, '^[A-Z]{2}$')",
)
.named("flavor_code_pattern_check")]);
let sqlite = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let postgres = AnyDialect::parse("postgresql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let mysql = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let oracle = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let mssql_error = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("mssql regular expression checks are unsupported");
assert!(sqlite[0].contains(
r#"CONSTRAINT "flavor_code_pattern_check" CHECK (ormdantic_regex_match(code, '^[A-Z]{2}$') = 1)"#
));
assert!(postgres[0]
.contains(r#"CONSTRAINT "flavor_code_pattern_check" CHECK (code ~ '^[A-Z]{2}$')"#));
assert!(mysql[0]
.contains("CONSTRAINT `flavor_code_pattern_check` CHECK (code REGEXP '^[A-Z]{2}$')"));
assert!(oracle[0].contains(
r#"CONSTRAINT "flavor_code_pattern_check" CHECK (REGEXP_LIKE(code, '^[A-Z]{2}$'))"#
));
assert!(mssql_error
.to_string()
.contains("regular expression CHECK constraints"));
}
#[test]
fn renders_multiple_of_check_constraints_by_backend() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::String).primary_key(true),
ColumnDef::new("quantity", FieldKind::Integer),
],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_check_constraints(vec![CheckConstraintDef::new(
"ormdantic_multiple_of(quantity, 5)",
)
.named("flavor_quantity_multiple_of_check")]);
let sqlite = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let postgres = AnyDialect::parse("postgresql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.unwrap();
let mssql = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.unwrap();
assert!(sqlite[0].contains(
r#"CONSTRAINT "flavor_quantity_multiple_of_check" CHECK (ormdantic_decimal_multiple_of(quantity, 5) = 1)"#
));
assert!(postgres[0].contains(
r#"CONSTRAINT "flavor_quantity_multiple_of_check" CHECK (MOD(quantity, 5) = 0)"#
));
assert!(mssql[0]
.contains("CONSTRAINT [flavor_quantity_multiple_of_check] CHECK (quantity % 5 = 0)"));
}
#[test]
fn renders_check_constraint_expression_edge_cases() {
let decimal_table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("rating", FieldKind::Decimal).numeric(5, 2),
],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_check_constraints(vec![
CheckConstraintDef::new("rating >= 1.25").named("flavor_rating_min")
]);
let sqlite_decimal = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(decimal_table))
.expect("sqlite should rewrite decimal comparisons when columns are available");
assert!(sqlite_decimal[0].contains(
r#"CONSTRAINT "flavor_rating_min" CHECK (ormdantic_decimal_cmp(rating, '1.25') >= 0)"#
));
let sqlite_add_decimal = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Check(CheckConstraintDef::new("rating >= 1.25")),
})
.expect("sqlite add constraint has no column metadata for decimal rewrites");
let sqlite_bad_regex = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Check(CheckConstraintDef::new(
"ormdantic_regex_match(, 'x')",
)),
})
.expect("malformed regex sentinel should remain an ordinary check");
let sqlite_bad_multiple = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Check(CheckConstraintDef::new(
"ormdantic_multiple_of(quantity, )",
)),
})
.expect("malformed multiple-of sentinel should remain an ordinary check");
let not_deferrable_unique = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Unique(
UniqueConstraintDef::new("flavor_code_unique", vec!["code".to_string()])
.with_timing(ConstraintTiming::new(Some(false), false)),
),
})
.expect("oracle should render explicit NOT DEFERRABLE timing");
assert_eq!(
sqlite_add_decimal,
vec![r#"ALTER TABLE "flavor" ADD CHECK (rating >= 1.25)"#.to_string()]
);
assert_eq!(
sqlite_bad_regex,
vec![r#"ALTER TABLE "flavor" ADD CHECK (ormdantic_regex_match(, 'x'))"#.to_string()]
);
assert_eq!(
sqlite_bad_multiple,
vec![r#"ALTER TABLE "flavor" ADD CHECK (ormdantic_multiple_of(quantity, ))"#.to_string()]
);
assert_eq!(
not_deferrable_unique,
vec![
r#"ALTER TABLE "flavor" ADD CONSTRAINT "flavor_code_unique" UNIQUE ("code") NOT DEFERRABLE"#
.to_string()
]
);
}
#[test]
fn renders_not_valid_constraints_for_supported_dialects() {
let add_check = PostgresDialect
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Check(
CheckConstraintDef::new("rating >= 0")
.named("rating_check")
.no_inherit()
.not_validated(),
),
})
.unwrap();
let add_fk = PostgresDialect
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::ForeignKey(
ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.named("flavor_supplier_fk")
.not_validated(),
),
})
.unwrap();
let oracle_add_check = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Check(
CheckConstraintDef::new("rating >= 0")
.named("rating_check")
.not_validated(),
),
})
.unwrap();
let oracle_add_fk = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::ForeignKey(
ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.named("flavor_supplier_fk")
.not_validated(),
),
})
.unwrap();
let mssql_add_check = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Check(
CheckConstraintDef::new("rating >= 0")
.named("rating_check")
.not_validated(),
),
})
.unwrap();
let mssql_add_fk = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::ForeignKey(
ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.named("flavor_supplier_fk")
.not_validated(),
),
})
.unwrap();
let mssql_table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("rating", FieldKind::Integer),
ColumnDef::new("supplier_id", FieldKind::Integer),
],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_check_constraints(vec![CheckConstraintDef::new("rating >= 0")
.named("rating_check")
.not_validated()])
.with_foreign_keys(vec![ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.named("flavor_supplier_fk")
.not_validated()]);
let mssql_create = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(mssql_table))
.unwrap();
let mysql_add_check = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Check(
CheckConstraintDef::new("rating >= 0")
.named("rating_check")
.not_validated(),
),
})
.unwrap();
let mysql_table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("rating", FieldKind::Integer),
],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_check_constraints(vec![CheckConstraintDef::new("rating >= 0")
.named("rating_check")
.not_validated()]);
let mysql_create = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(mysql_table))
.unwrap();
let mysql_fk_error = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::ForeignKey(
ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.named("flavor_supplier_fk")
.not_validated(),
),
})
.expect_err("mysql foreign key validation toggles are unsupported");
let mariadb_error = AnyDialect::parse("mariadb")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Check(
CheckConstraintDef::new("rating >= 0")
.named("rating_check")
.not_validated(),
),
})
.expect_err("mariadb validation toggles are unsupported");
let sqlite_no_inherit_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Check(
CheckConstraintDef::new("rating >= 0")
.named("rating_check")
.no_inherit(),
),
})
.expect_err("sqlite check NO INHERIT is unsupported");
assert_eq!(
add_check,
vec![
r#"ALTER TABLE "flavor" ADD CONSTRAINT "rating_check" CHECK (rating >= 0) NO INHERIT NOT VALID"#
.to_string()
]
);
assert_eq!(
add_fk,
vec![
r#"ALTER TABLE "flavor" ADD CONSTRAINT "flavor_supplier_fk" FOREIGN KEY ("supplier_id") REFERENCES "supplier" ("id") NOT VALID"#.to_string()
]
);
assert_eq!(
oracle_add_check,
vec![
r#"ALTER TABLE "flavor" ADD CONSTRAINT "rating_check" CHECK (rating >= 0) ENABLE NOVALIDATE"#
.to_string()
]
);
assert_eq!(
oracle_add_fk,
vec![
r#"ALTER TABLE "flavor" ADD CONSTRAINT "flavor_supplier_fk" FOREIGN KEY ("supplier_id") REFERENCES "supplier" ("id") ENABLE NOVALIDATE"#.to_string()
]
);
assert_eq!(
mssql_add_check,
vec![
"ALTER TABLE [flavor] WITH NOCHECK ADD CONSTRAINT [rating_check] CHECK (rating >= 0)"
.to_string()
]
);
assert_eq!(
mssql_add_fk,
vec![
"ALTER TABLE [flavor] WITH NOCHECK ADD CONSTRAINT [flavor_supplier_fk] FOREIGN KEY ([supplier_id]) REFERENCES [supplier] ([id])".to_string()
]
);
assert_eq!(
mysql_add_check,
vec![
"ALTER TABLE `flavor` ADD CONSTRAINT `rating_check` CHECK (rating >= 0) NOT ENFORCED"
.to_string()
]
);
assert_eq!(
mysql_create,
vec![
"CREATE TABLE IF NOT EXISTS `flavor` (`id` INTEGER NOT NULL PRIMARY KEY, `rating` INTEGER NOT NULL, CONSTRAINT `rating_check` CHECK (rating >= 0) NOT ENFORCED)"
.to_string()
]
);
assert!(!mssql_create[0].contains("WITH NOCHECK"));
assert!(!mssql_create[0].contains("[rating_check]"));
assert_eq!(
mssql_create[1],
"ALTER TABLE [flavor] WITH NOCHECK ADD CONSTRAINT [rating_check] CHECK (rating >= 0)"
);
assert_eq!(
mssql_create[2],
"ALTER TABLE [flavor] WITH NOCHECK ADD CONSTRAINT [flavor_supplier_fk] FOREIGN KEY ([supplier_id]) REFERENCES [supplier] ([id])"
);
assert!(mysql_fk_error
.to_string()
.contains("constraint validation toggles"));
assert!(mariadb_error
.to_string()
.contains("constraint validation toggles"));
assert!(sqlite_no_inherit_error
.to_string()
.contains("check constraint NO INHERIT"));
}
#[test]
fn renders_deferrable_timing_only_on_supported_dialects() {
let oracle_unique = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Unique(
UniqueConstraintDef::new("flavor_code_unique", vec!["code".to_string()])
.with_timing(ConstraintTiming::new(Some(true), true)),
),
})
.expect("oracle should render deferrable unique constraints");
let sqlite_table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("supplier_id", FieldKind::Integer),
],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_foreign_keys(vec![ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.named("flavor_supplier_fk")
.with_timing(ConstraintTiming::new(Some(true), true))]);
let sqlite_fk = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(sqlite_table))
.expect("sqlite should render deferrable foreign keys");
assert_eq!(
oracle_unique,
vec![
r#"ALTER TABLE "flavor" ADD CONSTRAINT "flavor_code_unique" UNIQUE ("code") DEFERRABLE INITIALLY DEFERRED"#.to_string()
]
);
assert_eq!(
sqlite_fk,
vec![
r#"CREATE TABLE IF NOT EXISTS "flavor" ("id" INTEGER PRIMARY KEY NOT NULL, "supplier_id" INTEGER NOT NULL, CONSTRAINT "flavor_supplier_fk" FOREIGN KEY ("supplier_id") REFERENCES "supplier" ("id") DEFERRABLE INITIALLY DEFERRED)"#.to_string()
]
);
}
#[test]
fn renders_mssql_unique_constraint_clustering_only_for_mssql() {
let clustered = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Unique(
UniqueConstraintDef::new("flavor_code_unique", vec!["code".to_string()])
.with_mssql_filegroup("constraintspace")
.with_mssql_clustered(true),
),
})
.expect("mssql should render clustered unique constraints");
let nonclustered = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Unique(
UniqueConstraintDef::new("flavor_name_unique", vec!["name".to_string()])
.with_mssql_clustered(false),
),
})
.expect("mssql should render nonclustered unique constraints");
let postgres_error = AnyDialect::parse("postgresql")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Unique(
UniqueConstraintDef::new("flavor_code_unique", vec!["code".to_string()])
.with_mssql_clustered(true),
),
})
.expect_err("postgres should reject SQL Server clustering metadata");
let sqlite_filegroup_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Unique(
UniqueConstraintDef::new("flavor_code_unique", vec!["code".to_string()])
.with_mssql_filegroup("constraintspace"),
),
})
.expect_err("sqlite should reject SQL Server filegroup metadata");
assert_eq!(
clustered,
vec![
"ALTER TABLE [flavor] ADD CONSTRAINT [flavor_code_unique] UNIQUE CLUSTERED ([code]) ON [constraintspace]"
.to_string()
]
);
assert_eq!(
nonclustered,
vec![
"ALTER TABLE [flavor] ADD CONSTRAINT [flavor_name_unique] UNIQUE NONCLUSTERED ([name])"
.to_string()
]
);
assert!(postgres_error
.to_string()
.contains("SQL Server unique constraint clustering"));
assert!(sqlite_filegroup_error
.to_string()
.contains("SQL Server unique constraint filegroups"));
}
#[test]
fn renders_oracle_unique_constraint_tablespaces_only_for_oracle() {
let oracle_unique = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Unique(
UniqueConstraintDef::new("flavor_code_unique", vec!["code".to_string()])
.with_oracle_tablespace("constraintspace")
.with_oracle_compress_prefix(2)
.with_timing(ConstraintTiming::new(Some(true), true)),
),
})
.expect("oracle should render unique constraint index tablespaces");
let postgres_error = AnyDialect::parse("postgresql")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Unique(
UniqueConstraintDef::new("flavor_code_unique", vec!["code".to_string()])
.with_oracle_tablespace("constraintspace"),
),
})
.expect_err("postgres should reject Oracle unique constraint tablespaces");
let sqlite_compress_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Unique(
UniqueConstraintDef::new("flavor_name_unique", vec!["name".to_string()])
.with_oracle_compress(),
),
})
.expect_err("sqlite should reject Oracle unique constraint compression");
assert_eq!(
oracle_unique,
vec![
r#"ALTER TABLE "flavor" ADD CONSTRAINT "flavor_code_unique" UNIQUE ("code") USING INDEX COMPRESS 2 TABLESPACE "constraintspace" DEFERRABLE INITIALLY DEFERRED"#.to_string()
]
);
assert!(postgres_error
.to_string()
.contains("Oracle unique constraint tablespaces"));
assert!(sqlite_compress_error
.to_string()
.contains("Oracle unique constraint compression"));
}
#[test]
fn rejects_deferrable_timing_on_unsupported_dialects() {
let sqlite_unique_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Unique(
UniqueConstraintDef::new("flavor_code_unique", vec!["code".to_string()])
.with_timing(ConstraintTiming::new(Some(true), false)),
),
})
.expect_err("sqlite deferrable unique constraints are unsupported");
let mysql_fk_error = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::ForeignKey(
ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.named("flavor_supplier_fk")
.with_timing(ConstraintTiming::new(Some(true), true)),
),
})
.expect_err("mysql deferrable foreign keys are unsupported");
assert!(sqlite_unique_error
.to_string()
.contains("deferrable unique constraints"));
assert!(mysql_fk_error
.to_string()
.contains("deferrable foreign keys"));
}
#[test]
fn renders_foreign_key_match_for_postgres_and_sqlite() {
let operation = SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::ForeignKey(
ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.named("flavor_supplier_fk")
.with_match(ForeignKeyMatch::Full),
),
};
assert_eq!(
PostgresDialect
.compile_schema_operation(&operation)
.expect("postgres should render foreign key match options"),
vec![
r#"ALTER TABLE "flavor" ADD CONSTRAINT "flavor_supplier_fk" FOREIGN KEY ("supplier_id") REFERENCES "supplier" ("id") MATCH FULL"#.to_string()
]
);
assert_eq!(
AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&operation)
.expect("sqlite should render foreign key match options"),
vec![
r#"ALTER TABLE "flavor" ADD CONSTRAINT "flavor_supplier_fk" FOREIGN KEY ("supplier_id") REFERENCES "supplier" ("id") MATCH FULL"#.to_string()
]
);
let error = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&operation)
.expect_err("mysql foreign key match options are unsupported");
assert!(error.to_string().contains("foreign key match types"));
}
#[test]
fn renders_postgres_exclusion_constraints_only_for_postgres() {
let constraint = ConstraintDef::Exclusion(
ExclusionConstraintDef::new(
"booking_room_overlap",
vec![
ExclusionElementDef::column("room_id", "=").opclass("gist_int4_ops"),
ExclusionElementDef::column("during", "&&"),
],
)
.method("gist")
.where_expr("cancelled = false")
.with_timing(ConstraintTiming::new(Some(true), true)),
);
let operation = SchemaOperation::AddConstraint {
table: "booking".to_string(),
constraint,
};
assert_eq!(
PostgresDialect
.compile_schema_operation(&operation)
.expect("postgres should render exclusion constraints"),
vec![
r#"ALTER TABLE "booking" ADD CONSTRAINT "booking_room_overlap" EXCLUDE USING gist ("room_id" gist_int4_ops WITH =, "during" WITH &&) WHERE (cancelled = false) DEFERRABLE INITIALLY DEFERRED"#.to_string()
]
);
let error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&operation)
.expect_err("sqlite should reject exclusion constraints");
assert!(error
.to_string()
.contains("feature 'exclusion constraints' is not supported by dialect 'sqlite'"));
}
#[test]
fn rejects_invalid_postgres_partition_child_shapes() {
let missing_bound = TableDef::from_parts(
"flavor_2026",
"Flavor2026",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_postgres_partition_of("flavor");
let inherited_child = TableDef::from_parts(
"flavor_2026",
"Flavor2026",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_postgres_partition_of("flavor")
.with_postgres_partition_for("FOR VALUES FROM (2026) TO (2027)")
.with_postgres_inherits(vec!["base_flavor".to_string()]);
let missing_bound_error = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(missing_bound))
.expect_err("partition child missing bound should fail");
let inherited_child_error = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(inherited_child))
.expect_err("partition child cannot inherit");
assert!(missing_bound_error
.to_string()
.contains("missing a partition bound"));
assert!(inherited_child_error
.to_string()
.contains("cannot also use"));
}
#[test]
fn renders_mssql_nonclustered_primary_key_and_mysql_column_modify_edges() {
let mssql_table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_mssql_primary_key_nonclustered(true);
let mssql_sql = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(mssql_table))
.unwrap();
assert!(mssql_sql[0].contains("PRIMARY KEY NONCLUSTERED"));
let computed_column = ColumnDef::new("score", FieldKind::Integer)
.with_server_default("0")
.with_collation("utf8mb4_bin")
.with_computed(ComputedDef::new("base_score + bonus").persisted(true))
.with_comment("score cache");
let mysql_comment = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetColumnComment {
table: "flavor".to_string(),
column: computed_column,
})
.unwrap();
assert_eq!(
mysql_comment,
vec![
"ALTER TABLE `flavor` MODIFY COLUMN `score` INTEGER NOT NULL DEFAULT 0 COLLATE utf8mb4_bin GENERATED ALWAYS AS (base_score + bonus) STORED COMMENT 'score cache'".to_string()
]
);
let clear_comment = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetColumnComment {
table: "flavor".to_string(),
column: ColumnDef::new("score", FieldKind::Integer),
})
.unwrap();
assert_eq!(
clear_comment,
vec!["ALTER TABLE `flavor` MODIFY COLUMN `score` INTEGER NOT NULL COMMENT ''".to_string()]
);
let identity_comment = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetColumnComment {
table: "flavor".to_string(),
column: ColumnDef::new("id", FieldKind::Integer)
.with_identity(IdentityDef::new())
.with_comment("surrogate key"),
})
.unwrap();
assert_eq!(
identity_comment,
vec![
"ALTER TABLE `flavor` MODIFY COLUMN `id` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'surrogate key'"
.to_string()
]
);
}
#[test]
fn rejects_invalid_sqlite_conflict_policy_and_unsupported_check_inheritance() {
let invalid_conflict = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.with_sqlite_on_conflict_primary_key("explode")],
Vec::new(),
Vec::new(),
Vec::new(),
);
let conflict_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(invalid_conflict))
.expect_err("invalid sqlite conflict policy should fail");
assert!(conflict_error
.to_string()
.contains("invalid SQLite conflict policy"));
let invalid_not_null = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("name", FieldKind::String).with_sqlite_on_conflict_not_null("explode"),
],
Vec::new(),
Vec::new(),
Vec::new(),
);
let invalid_unique = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("name", FieldKind::String).with_sqlite_on_conflict_unique("explode"),
],
Vec::new(),
Vec::new(),
Vec::new(),
);
for table in [invalid_not_null, invalid_unique] {
let error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("invalid sqlite conflict policy should fail");
assert!(error.to_string().contains("invalid SQLite conflict policy"));
}
let no_inherit = SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Check(
CheckConstraintDef::new("rating >= 0")
.named("flavor_rating_check")
.no_inherit(),
),
};
let sqlite_error = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&no_inherit)
.expect_err("sqlite should reject no inherit checks");
assert!(sqlite_error.to_string().contains("NO INHERIT"));
}
#[test]
fn rejects_deferred_create_table_constraint_followup_errors() {
let mssql_check = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_check_constraints(vec![CheckConstraintDef::new("id > 0")
.named("flavor_id_check")
.no_inherit()
.not_validated()]);
let mssql_fk = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("supplier_id", FieldKind::Integer),
],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_foreign_keys(vec![ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.named("flavor_supplier_fk")
.with_timing(ConstraintTiming::new(Some(true), false))
.not_validated()]);
let check_error = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(mssql_check))
.expect_err("deferred check follow-up should validate NO INHERIT");
let fk_error = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(mssql_fk))
.expect_err("deferred foreign-key follow-up should validate timing");
assert!(check_error.to_string().contains("NO INHERIT"));
assert!(fk_error.to_string().contains("deferrable foreign keys"));
}
#[test]
fn rejects_inline_create_table_foreign_key_timing_errors() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("supplier_id", FieldKind::Integer),
],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_foreign_keys(vec![ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.named("flavor_supplier_fk")
.with_timing(ConstraintTiming::new(Some(true), false))]);
let error = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("inline foreign-key timing should be validated");
assert!(error.to_string().contains("deferrable foreign keys"));
}
#[test]
fn renders_remaining_foreign_key_actions_and_match_simple() {
let operation = SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::ForeignKey(
ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.named("flavor_supplier_fk")
.with_match(ForeignKeyMatch::Simple)
.on_delete(ForeignKeyAction::Restrict)
.on_update(ForeignKeyAction::SetDefault),
),
};
let sql = PostgresDialect
.compile_schema_operation(&operation)
.expect("postgres should render all foreign key action variants");
assert_eq!(
sql,
vec![
r#"ALTER TABLE "flavor" ADD CONSTRAINT "flavor_supplier_fk" FOREIGN KEY ("supplier_id") REFERENCES "supplier" ("id") MATCH SIMPLE ON DELETE RESTRICT ON UPDATE SET DEFAULT"#.to_string()
]
);
let no_action = SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::ForeignKey(
ForeignKeyDef::new(
vec!["supplier_id".to_string()],
"supplier",
vec!["id".to_string()],
)
.on_delete(ForeignKeyAction::NoAction),
),
};
let no_action_sql = PostgresDialect
.compile_schema_operation(&no_action)
.expect("postgres should render NO ACTION");
assert!(no_action_sql[0].contains("ON DELETE NO ACTION"));
}
#[test]
fn renders_column_operation_comment_and_nullability_edges() {
let add = PostgresDialect
.compile_schema_operation(&SchemaOperation::AddColumn {
table: "inventory.flavor".to_string(),
column: ColumnDef::new("name", FieldKind::String).with_comment("display name"),
})
.expect("postgres should render column add comments");
assert_eq!(
add,
vec![
r#"ALTER TABLE "inventory"."flavor" ADD COLUMN "name" TEXT NOT NULL"#.to_string(),
r#"COMMENT ON COLUMN "inventory"."flavor"."name" IS 'display name'"#.to_string(),
]
);
let drop = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::DropColumn {
table: "inventory.flavor".to_string(),
column: "old_name".to_string(),
})
.expect("mysql should render schema-qualified column drops");
assert_eq!(
drop,
vec!["ALTER TABLE `inventory`.`flavor` DROP COLUMN `old_name`".to_string()]
);
let mssql_not_null = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::AlterColumn {
table: "flavor".to_string(),
column: ColumnDef::new("rating", FieldKind::Integer),
})
.expect("mssql should render non-null alter column");
assert_eq!(
mssql_not_null,
vec!["ALTER TABLE [flavor] ALTER COLUMN [rating] INTEGER NOT NULL".to_string()]
);
}
#[test]
fn renders_schema_qualified_column_comments_for_oracle_and_mssql_clear() {
let oracle_set = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetColumnComment {
table: "inventory.flavor".to_string(),
column: ColumnDef::new("name", FieldKind::String).with_comment("localized label"),
})
.expect("oracle should render column comments");
let oracle_clear = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetColumnComment {
table: "inventory.flavor".to_string(),
column: ColumnDef::new("name", FieldKind::String),
})
.expect("oracle should render clearing column comments");
let mssql_clear = AnyDialect::parse("mssql")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetColumnComment {
table: "inventory.flavor".to_string(),
column: ColumnDef::new("name", FieldKind::String),
})
.expect("mssql should render clearing schema-qualified column comments");
assert_eq!(
oracle_set,
vec![r#"COMMENT ON COLUMN "inventory"."flavor"."name" IS 'localized label'"#.to_string()]
);
assert_eq!(
oracle_clear,
vec![r#"COMMENT ON COLUMN "inventory"."flavor"."name" IS ''"#.to_string()]
);
assert!(mssql_clear[0].contains("DECLARE @schema sysname = N'inventory'"));
assert!(mssql_clear[0].contains("t.name = N'flavor'"));
assert!(mssql_clear[0].contains("c.name = N'name'"));
assert!(mssql_clear[0].contains("sys.sp_dropextendedproperty"));
}
#[test]
fn renders_computed_create_table_columns_and_identity_no_max_conflict() {
let table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer).primary_key(true),
ColumnDef::new("score", FieldKind::Integer)
.with_computed(ComputedDef::new("base_score + bonus").persisted(true)),
],
Vec::new(),
Vec::new(),
Vec::new(),
);
let postgres = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(table.clone()))
.expect("postgres should render computed columns");
let mysql = AnyDialect::parse("mysql")
.unwrap()
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect("mysql should render computed columns");
assert!(postgres[0]
.contains(r#""score" INTEGER NOT NULL GENERATED ALWAYS AS (base_score + bonus) STORED"#));
assert!(mysql[0]
.contains("`score` INTEGER NOT NULL GENERATED ALWAYS AS (base_score + bonus) STORED"));
let conflict = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.with_identity(IdentityDef::new().max_value(100).no_max_value(true))],
Vec::new(),
Vec::new(),
Vec::new(),
);
let error = PostgresDialect
.compile_schema_operation(&SchemaOperation::CreateTable(conflict))
.expect_err("identity no_max_value and max_value should be mutually exclusive");
assert!(error.to_string().contains("no_max_value"));
}
#[test]
fn renders_noop_and_single_sided_postgres_table_storage_changes() {
let inherits_noop = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresInherits {
table: "flavor".to_string(),
add: Vec::new(),
drop: Vec::new(),
})
.expect("empty inheritance changes should be a no-op");
let storage_noop = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresWith {
table: "flavor".to_string(),
set: Vec::new(),
reset: Vec::new(),
})
.expect("empty storage changes should be a no-op");
let set_only = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresWith {
table: "flavor".to_string(),
set: vec![("fillfactor".to_string(), "80".to_string())],
reset: Vec::new(),
})
.expect("postgres should render SET storage parameters");
let reset_only = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresWith {
table: "flavor".to_string(),
set: Vec::new(),
reset: vec!["toast.autovacuum_enabled".to_string()],
})
.expect("postgres should render RESET storage parameters");
assert!(inherits_noop.is_empty());
assert!(storage_noop.is_empty());
assert_eq!(
set_only,
vec![r#"ALTER TABLE "flavor" SET (fillfactor = 80)"#.to_string()]
);
assert_eq!(
reset_only,
vec![r#"ALTER TABLE "flavor" RESET (toast.autovacuum_enabled)"#.to_string()]
);
}
#[test]
fn rejects_invalid_postgres_storage_parameters() {
let invalid_set_name = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresWith {
table: "flavor".to_string(),
set: vec![("9fillfactor".to_string(), "70".to_string())],
reset: Vec::new(),
})
.expect_err("invalid postgres storage names should fail");
let invalid_set_value = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresWith {
table: "flavor".to_string(),
set: vec![("fillfactor".to_string(), "70;DROP".to_string())],
reset: Vec::new(),
})
.expect_err("invalid postgres storage values should fail");
let invalid_reset_name = PostgresDialect
.compile_schema_operation(&SchemaOperation::SetTablePostgresWith {
table: "flavor".to_string(),
set: Vec::new(),
reset: vec!["toast.bad-name".to_string()],
})
.expect_err("invalid postgres reset names should fail");
assert!(invalid_set_name
.to_string()
.contains("invalid PostgreSQL table storage parameter name"));
assert!(invalid_set_value
.to_string()
.contains("invalid PostgreSQL table storage parameter value"));
assert!(invalid_reset_name
.to_string()
.contains("invalid PostgreSQL table storage parameter name"));
}
#[test]
fn rejects_invalid_mysql_table_option_tokens_and_partition_clauses() {
fn base_table() -> TableDef {
TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
}
let mysql = AnyDialect::parse("mysql").unwrap();
let cases = vec![
("mysql engine", base_table().with_mysql_engine("Inno-DB")),
("mysql charset", base_table().with_mysql_charset("utf8-mb4")),
(
"mysql collation",
base_table().with_mysql_collation("utf8mb4;unicode_ci"),
),
(
"mysql row format",
base_table().with_mysql_row_format("DYNAMIC ROW"),
),
(
"mysql insert method",
base_table().with_mysql_insert_method("LAST;"),
),
(
"mysql partition by",
base_table().with_mysql_partition_by("HASH (id); DROP TABLE flavor"),
),
(
"mysql subpartition by",
base_table().with_mysql_subpartition_by("KEY (id) /* comment */"),
),
];
for (feature, table) in cases {
let error = mysql
.compile_schema_operation(&SchemaOperation::CreateTable(table))
.expect_err("invalid mysql table option should fail");
assert!(
error.to_string().contains(feature),
"expected {feature} in {error}"
);
}
}
#[test]
fn renders_oracle_unique_compress_enabled_and_sqlite_tablespace_noop() {
let oracle = AnyDialect::parse("oracle")
.unwrap()
.compile_schema_operation(&SchemaOperation::AddConstraint {
table: "flavor".to_string(),
constraint: ConstraintDef::Unique(
UniqueConstraintDef::new("flavor_code_unique", vec!["code".to_string()])
.with_oracle_compress(),
),
})
.expect("oracle should render enabled unique index compression");
let sqlite = AnyDialect::parse("sqlite")
.unwrap()
.compile_schema_operation(&SchemaOperation::SetTableTablespace {
table: "flavor".to_string(),
tablespace: Some("ignored".to_string()),
})
.expect("sqlite table tablespace changes should be ignored");
assert_eq!(
oracle,
vec![
r#"ALTER TABLE "flavor" ADD CONSTRAINT "flavor_code_unique" UNIQUE ("code") USING INDEX COMPRESS"#.to_string()
]
);
assert!(sqlite.is_empty());
}
#[test]
fn concrete_dialect_create_table_paths_cover_backend_specific_regions() {
let sqlite_table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![
ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.autoincrement(true)
.with_sqlite_on_conflict_primary_key("REPLACE"),
ColumnDef::new("name", FieldKind::String)
.with_sqlite_on_conflict_not_null("FAIL")
.with_collation("NOCASE"),
ColumnDef::new("rating", FieldKind::Decimal).numeric(6, 2),
],
vec![IndexDef::new("flavor_name_idx", vec!["name".to_string()])],
vec![
UniqueConstraintDef::new("flavor_name_unique", vec!["name".to_string()])
.with_sqlite_on_conflict("IGNORE"),
],
Vec::new(),
)
.with_check_constraints(vec![
CheckConstraintDef::new("rating >= 1.25").named("flavor_rating_min")
]);
let sqlite = SqliteDialect
.compile_schema_operation(&SchemaOperation::CreateTable(sqlite_table))
.expect("concrete sqlite should compile table metadata");
assert!(sqlite[0].contains("PRIMARY KEY ON CONFLICT REPLACE"));
assert!(sqlite[0].contains("NOT NULL ON CONFLICT FAIL"));
assert!(sqlite[0].contains("UNIQUE (\"name\") ON CONFLICT IGNORE"));
assert!(sqlite[0].contains("ormdantic_decimal_cmp(rating, '1.25') >= 0"));
let mysql_table = TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer)
.primary_key(true)
.autoincrement(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_mysql_engine("InnoDB")
.with_mysql_partition_by("HASH (id)")
.with_mysql_partitions(4)
.with_mysql_subpartition_by("KEY (id)")
.with_mysql_subpartitions(2)
.with_mysql_auto_increment(42);
let mysql = MySqlDialect
.compile_schema_operation(&SchemaOperation::CreateTable(mysql_table.clone()))
.expect("concrete mysql should compile partition options");
let mariadb = MariaDbDialect
.compile_schema_operation(&SchemaOperation::CreateTable(mysql_table))
.expect("concrete mariadb should compile partition options");
for sql in [mysql[0].as_str(), mariadb[0].as_str()] {
assert!(sql.contains("PARTITION BY HASH (id)"));
assert!(sql.contains("SUBPARTITION BY KEY (id)"));
assert!(sql.contains("AUTO_INCREMENT = 42"));
}
let mssql = MsSqlDialect
.compile_schema_operation(&SchemaOperation::CreateTable(
TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
Vec::new(),
Vec::new(),
)
.with_schema("inventory"),
))
.expect("concrete mssql should compile object guard");
assert!(mssql[0].contains("IF OBJECT_ID(N'inventory.flavor', N'U') IS NULL"));
let oracle = OracleDialect
.compile_schema_operation(&SchemaOperation::CreateTable(TableDef::from_parts(
"flavor",
"Flavor",
"id",
vec![ColumnDef::new("id", FieldKind::Integer).primary_key(true)],
Vec::new(),
vec![
UniqueConstraintDef::new("flavor_id_unique", vec!["id".to_string()])
.with_oracle_compress_prefix(1)
.with_oracle_tablespace("USERS"),
],
Vec::new(),
)))
.expect("concrete oracle should compile unique index metadata");
assert!(oracle[0].contains("USING INDEX COMPRESS 1 TABLESPACE \"USERS\""));
let method_error = MySqlDialect
.compile_schema_operation(&SchemaOperation::CreateIndex {
table: "flavor".to_string(),
index: IndexDef::new("flavor_name_idx", vec!["name".to_string()]).method("btree"),
})
.expect_err("concrete mysql cannot render index methods");
assert!(method_error.to_string().contains("index methods"));
}