use super::*;
#[test]
fn test_column_type_postgres() {
assert_eq!(ColumnType::Integer.to_postgres_sql(), "INTEGER");
assert_eq!(ColumnType::BigInteger.to_postgres_sql(), "BIGINT");
assert_eq!(ColumnType::String.to_postgres_sql(), "VARCHAR(255)");
assert_eq!(ColumnType::Text.to_postgres_sql(), "TEXT");
assert_eq!(ColumnType::Boolean.to_postgres_sql(), "BOOLEAN");
assert_eq!(ColumnType::Jsonb.to_postgres_sql(), "JSONB");
assert_eq!(ColumnType::IntegerArray.to_postgres_sql(), "INTEGER[]");
assert_eq!(ColumnType::Timestamp.to_postgres_sql(), "TIMESTAMP");
assert_eq!(ColumnType::TimestampTz.to_postgres_sql(), "TIMESTAMPTZ");
assert_eq!(ColumnType::Date.to_postgres_sql(), "DATE");
assert_eq!(ColumnType::Time.to_postgres_sql(), "TIME");
}
#[test]
fn test_column_type_mysql() {
assert_eq!(ColumnType::Integer.to_mysql_sql(), "INT");
assert_eq!(ColumnType::BigInteger.to_mysql_sql(), "BIGINT");
assert_eq!(ColumnType::Boolean.to_mysql_sql(), "TINYINT(1)");
assert_eq!(ColumnType::Jsonb.to_mysql_sql(), "JSON");
assert_eq!(ColumnType::Timestamp.to_mysql_sql(), "TIMESTAMP");
assert_eq!(ColumnType::TimestampTz.to_mysql_sql(), "TIMESTAMP");
assert_eq!(ColumnType::Date.to_mysql_sql(), "DATE");
assert_eq!(ColumnType::Time.to_mysql_sql(), "TIME");
}
#[test]
fn test_column_type_sqlite() {
assert_eq!(ColumnType::Integer.to_sqlite_sql(), "INTEGER");
assert_eq!(ColumnType::BigInteger.to_sqlite_sql(), "INTEGER");
assert_eq!(ColumnType::String.to_sqlite_sql(), "TEXT");
assert_eq!(ColumnType::Boolean.to_sqlite_sql(), "INTEGER");
assert_eq!(ColumnType::Timestamp.to_sqlite_sql(), "TEXT");
assert_eq!(ColumnType::TimestampTz.to_sqlite_sql(), "TEXT");
assert_eq!(ColumnType::Date.to_sqlite_sql(), "TEXT");
assert_eq!(ColumnType::Time.to_sqlite_sql(), "TEXT");
}
#[test]
fn test_default_value() {
assert_eq!(DefaultValue::String("test".to_string()).to_sql(), "'test'");
assert_eq!(DefaultValue::Integer(42).to_sql(), "42");
assert_eq!(DefaultValue::Boolean(true).to_sql(), "TRUE");
assert_eq!(DefaultValue::Boolean(false).to_sql(), "FALSE");
assert_eq!(DefaultValue::Null.to_sql(), "NULL");
}
#[test]
fn test_table_builder_create() {
let mut builder = TableBuilder::new("users", DatabaseType::Postgres);
builder.id();
builder.string("email").unique().not_null();
builder.string("name").not_null();
builder.boolean("active").default(true);
builder.timestamps();
let sql = builder.build_create();
assert!(sql.contains("CREATE TABLE"));
assert!(sql.contains("\"users\""));
assert!(sql.contains("\"id\" BIGSERIAL"));
assert!(sql.contains("\"email\""));
assert!(sql.contains("\"name\""));
assert!(sql.contains("\"active\""));
assert!(sql.contains("\"created_at\""));
assert!(sql.contains("\"updated_at\""));
}
#[test]
fn test_timestamps_feature() {
let mut builder = TableBuilder::new("posts", DatabaseType::Postgres);
builder.id();
builder.string("title").not_null();
builder.timestamps();
let sql = builder.build_create();
assert!(
sql.contains("\"created_at\" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP"),
"PostgreSQL should have created_at with TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP. Got: {}",
sql
);
assert!(
sql.contains("\"updated_at\" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP"),
"PostgreSQL should have updated_at with TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP. Got: {}",
sql
);
let mut builder = TableBuilder::new("posts", DatabaseType::MySQL);
builder.id();
builder.string("title").not_null();
builder.timestamps();
let sql = builder.build_create();
assert!(
sql.contains("`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"),
"MySQL should have created_at with TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP. Got: {}",
sql
);
assert!(
sql.contains("`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"),
"MySQL should have updated_at with TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP. Got: {}",
sql
);
let mut builder = TableBuilder::new("posts", DatabaseType::MariaDB);
builder.id();
builder.string("title").not_null();
builder.timestamps();
let sql = builder.build_create();
assert!(
sql.contains("`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"),
"MariaDB should have created_at with TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP. Got: {}",
sql
);
assert!(
sql.contains("`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"),
"MariaDB should have updated_at with TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP. Got: {}",
sql
);
let mut builder = TableBuilder::new("posts", DatabaseType::SQLite);
builder.id();
builder.string("title").not_null();
builder.timestamps();
let sql = builder.build_create();
assert!(
sql.contains("\"created_at\" TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP"),
"SQLite should have created_at with TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP. Got: {}",
sql
);
assert!(
sql.contains("\"updated_at\" TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP"),
"SQLite should have updated_at with TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP. Got: {}",
sql
);
}
#[test]
fn test_timestamps_naive_feature() {
let mut builder = TableBuilder::new("logs", DatabaseType::Postgres);
builder.id();
builder.text("message").not_null();
builder.timestamps_naive();
let sql = builder.build_create();
assert!(
sql.contains("\"created_at\" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"),
"PostgreSQL timestamps_naive should use TIMESTAMP. Got: {}",
sql
);
assert!(
sql.contains("\"updated_at\" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"),
"PostgreSQL timestamps_naive should use TIMESTAMP. Got: {}",
sql
);
}
#[test]
fn test_timestamptz_column() {
let mut builder = TableBuilder::new("sessions", DatabaseType::Postgres);
builder.id();
builder.string("token").not_null();
builder.timestamptz("expires_at").not_null();
builder.timestamptz("last_activity").nullable();
let sql = builder.build_create();
assert!(
sql.contains("\"expires_at\" TIMESTAMPTZ NOT NULL"),
"Should have expires_at as TIMESTAMPTZ NOT NULL. Got: {}",
sql
);
assert!(
sql.contains("\"last_activity\" TIMESTAMPTZ"),
"Should have last_activity as TIMESTAMPTZ. Got: {}",
sql
);
assert!(
!sql.contains("\"last_activity\" TIMESTAMPTZ NOT NULL"),
"last_activity should be nullable. Got: {}",
sql
);
}
#[test]
fn test_timestamp_vs_timestamptz() {
let mut builder = TableBuilder::new("events", DatabaseType::Postgres);
builder.id();
builder.timestamp("local_time");
builder.timestamptz("utc_time");
let sql = builder.build_create();
assert!(
sql.contains("\"local_time\" TIMESTAMP"),
"timestamp() should produce TIMESTAMP. Got: {}",
sql
);
assert!(
sql.contains("\"utc_time\" TIMESTAMPTZ"),
"timestamptz() should produce TIMESTAMPTZ. Got: {}",
sql
);
}
#[test]
fn test_date_time_columns() {
let mut builder = TableBuilder::new("schedules", DatabaseType::Postgres);
builder.id();
builder.date("event_date");
builder.time("start_time");
builder.datetime("local_datetime");
builder.timestamp("naive_timestamp");
builder.timestamptz("utc_timestamp");
let sql = builder.build_create();
assert!(
sql.contains("\"event_date\" DATE"),
"Should have DATE column. Got: {}",
sql
);
assert!(
sql.contains("\"start_time\" TIME"),
"Should have TIME column. Got: {}",
sql
);
assert!(
sql.contains("\"local_datetime\" TIMESTAMP"),
"Should have TIMESTAMP for datetime. Got: {}",
sql
);
assert!(
sql.contains("\"naive_timestamp\" TIMESTAMP"),
"Should have TIMESTAMP. Got: {}",
sql
);
assert!(
sql.contains("\"utc_timestamp\" TIMESTAMPTZ"),
"Should have TIMESTAMPTZ. Got: {}",
sql
);
}
#[test]
fn test_soft_deletes_feature() {
let mut builder = TableBuilder::new("posts", DatabaseType::Postgres);
builder.id();
builder.soft_deletes();
let sql = builder.build_create();
assert!(
sql.contains("\"deleted_at\" TIMESTAMPTZ"),
"Should have deleted_at TIMESTAMPTZ column. Got: {}",
sql
);
assert!(
!sql.contains("\"deleted_at\" TIMESTAMPTZ NOT NULL"),
"deleted_at should be nullable (no NOT NULL). Got: {}",
sql
);
}
#[test]
fn test_alter_table_builder() {
let mut builder = AlterTableBuilder::new("users", DatabaseType::Postgres);
builder.add_column("phone", ColumnType::String).nullable();
builder.drop_column("legacy");
builder.rename_column("name", "full_name");
let statements = builder.build();
assert_eq!(statements.len(), 3);
assert!(statements[0].contains("ADD COLUMN"));
assert!(statements[1].contains("DROP COLUMN"));
assert!(statements[2].contains("RENAME COLUMN"));
}
#[test]
fn test_multi_column_unique_constraint() {
let mut builder = TableBuilder::new("user_roles", DatabaseType::Postgres);
builder.big_integer("user_id").not_null();
builder.big_integer("role_id").not_null();
builder.unique(&["user_id", "role_id"]);
let sql = builder.build_create();
assert!(
sql.contains("UNIQUE (\"user_id\", \"role_id\")"),
"Should have multi-column unique constraint. Got: {}",
sql
);
let mut builder = TableBuilder::new("users", DatabaseType::Postgres);
builder.id();
builder.string("email").not_null();
builder.big_integer("tenant_id").not_null();
builder.unique_named("uq_user_email_tenant", &["email", "tenant_id"]);
let sql = builder.build_create();
assert!(
sql.contains("CONSTRAINT \"uq_user_email_tenant\" UNIQUE (\"email\", \"tenant_id\")"),
"Should have named unique constraint. Got: {}",
sql
);
}
#[test]
fn test_composite_primary_key() {
let mut builder = TableBuilder::new("user_roles", DatabaseType::Postgres);
builder.big_integer("user_id").not_null();
builder.big_integer("role_id").not_null();
builder.timestamps();
builder.primary_key(&["user_id", "role_id"]);
let sql = builder.build_create();
assert!(
sql.contains("PRIMARY KEY (\"user_id\", \"role_id\")"),
"Should have composite primary key. Got: {}",
sql
);
assert!(
!sql.contains("BIGINT PRIMARY KEY"),
"Individual columns should not be marked as primary key. Got: {}",
sql
);
}
#[test]
fn test_check_constraint() {
let mut builder = TableBuilder::new("products", DatabaseType::Postgres);
builder.id();
builder.decimal("price").check("price >= 0");
builder.integer("quantity").check("quantity >= 0");
let sql = builder.build_create();
assert!(
sql.contains("CHECK (price >= 0)"),
"Should have CHECK constraint on price. Got: {}",
sql
);
assert!(
sql.contains("CHECK (quantity >= 0)"),
"Should have CHECK constraint on quantity. Got: {}",
sql
);
}
#[test]
fn test_extra_sql_attribute() {
let mut builder = TableBuilder::new("logs", DatabaseType::MySQL);
builder.id();
builder.text("message").extra("COLLATE utf8mb4_unicode_ci");
let sql = builder.build_create();
assert!(
sql.contains("COLLATE utf8mb4_unicode_ci"),
"Should include extra SQL. Got: {}",
sql
);
let mut builder = TableBuilder::new("logs", DatabaseType::MariaDB);
builder.id();
builder.text("message").extra("COLLATE utf8mb4_unicode_ci");
let sql = builder.build_create();
assert!(
sql.contains("COLLATE utf8mb4_unicode_ci"),
"MariaDB should include extra SQL. Got: {}",
sql
);
}
#[test]
fn test_column_type_mariadb() {
assert_eq!(ColumnType::Integer.to_mysql_sql(), "INT");
assert_eq!(ColumnType::BigInteger.to_mysql_sql(), "BIGINT");
assert_eq!(ColumnType::Boolean.to_mysql_sql(), "TINYINT(1)");
assert_eq!(ColumnType::Jsonb.to_mysql_sql(), "JSON");
assert_eq!(ColumnType::Timestamp.to_mysql_sql(), "TIMESTAMP");
assert_eq!(ColumnType::TimestampTz.to_mysql_sql(), "TIMESTAMP");
assert_eq!(ColumnType::Date.to_mysql_sql(), "DATE");
assert_eq!(ColumnType::Time.to_mysql_sql(), "TIME");
}
#[test]
fn test_mariadb_table_builder_create() {
let mut builder = TableBuilder::new("users", DatabaseType::MariaDB);
builder.id();
builder.string("email").unique().not_null();
builder.string("name").not_null();
builder.boolean("active").default(true);
builder.timestamps();
let sql = builder.build_create();
assert!(sql.contains("CREATE TABLE"));
assert!(sql.contains("`users`"));
assert!(sql.contains("`id` BIGINT AUTO_INCREMENT"));
assert!(sql.contains("`email`"));
assert!(sql.contains("`name`"));
assert!(sql.contains("`active`"));
assert!(sql.contains("`created_at`"));
assert!(sql.contains("`updated_at`"));
}
#[test]
fn test_mariadb_alter_table_builder() {
let mut builder = AlterTableBuilder::new("users", DatabaseType::MariaDB);
builder.add_column("phone", ColumnType::String).nullable();
builder.drop_column("legacy");
builder.rename_column("name", "full_name");
let statements = builder.build();
assert_eq!(statements.len(), 3);
assert!(statements[0].contains("ADD COLUMN"));
assert!(statements[0].contains("`phone`"));
assert!(statements[1].contains("DROP COLUMN"));
assert!(statements[1].contains("`legacy`"));
assert!(statements[2].contains("RENAME COLUMN"));
assert!(statements[2].contains("`name`"));
assert!(statements[2].contains("`full_name`"));
}
#[test]
fn test_mariadb_quoting() {
let schema = Schema::new(DatabaseType::MariaDB);
assert_eq!(schema.quote_identifier("users"), "`users`");
assert_eq!(schema.quote_identifier("email"), "`email`");
}
#[test]
fn test_postgres_identifier_quoting_escapes_inner_quotes() {
let schema = Schema::new(DatabaseType::Postgres);
assert_eq!(schema.quote_identifier("user\"roles"), "\"user\"\"roles\"");
assert_eq!(
quote_migration_identifier("col\"name", DatabaseType::Postgres),
"\"col\"\"name\""
);
let mut builder = TableBuilder::new("user\"roles", DatabaseType::Postgres);
builder.string("display\"name");
let sql = builder.build_create();
assert!(
sql.contains("CREATE TABLE \"user\"\"roles\""),
"SQL should escape embedded double quotes in table names. Got: {}",
sql
);
assert!(
sql.contains("\"display\"\"name\" VARCHAR(255)"),
"SQL should escape embedded double quotes in column names. Got: {}",
sql
);
}
#[test]
fn test_mysql_identifier_quoting_escapes_inner_backticks() {
let schema = Schema::new(DatabaseType::MySQL);
assert_eq!(schema.quote_identifier("user`roles"), "`user``roles`");
assert_eq!(
quote_migration_identifier("col`name", DatabaseType::MySQL),
"`col``name`"
);
let mut builder = AlterTableBuilder::new("user`roles", DatabaseType::MySQL);
builder.rename_column("old`name", "new`name");
let statements = builder.build();
assert!(
statements[0].contains("ALTER TABLE `user``roles`"),
"SQL should escape embedded backticks in table names. Got: {}",
statements[0]
);
assert!(
statements[0].contains("`old``name` TO `new``name`"),
"SQL should escape embedded backticks in column names. Got: {}",
statements[0]
);
}