use crate::db::DbError;
pub trait SchemaEditor {
fn create_table(&self, table_name: &str, columns: &[ColumnDef]) -> Result<String, DbError>;
fn drop_table(&self, table_name: &str, cascade: bool) -> Result<String, DbError>;
fn add_column(&self, table_name: &str, column: &ColumnDef) -> Result<String, DbError>;
fn remove_column(&self, table_name: &str, column_name: &str) -> Result<String, DbError>;
fn alter_column(
&self,
table_name: &str,
old: &ColumnDef,
new: &ColumnDef,
) -> Result<String, DbError>;
fn rename_column(
&self,
table_name: &str,
old_name: &str,
new_name: &str,
) -> Result<String, DbError>;
fn create_index(&self, table_name: &str, index: &IndexDef) -> Result<String, DbError>;
fn drop_index(&self, index_name: &str) -> Result<String, DbError>;
fn add_constraint(
&self,
table_name: &str,
constraint: &ConstraintDef,
) -> Result<String, DbError>;
fn remove_constraint(&self, table_name: &str, constraint_name: &str)
-> Result<String, DbError>;
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ColumnDef {
pub name: String,
pub data_type: String,
pub nullable: bool,
pub primary_key: bool,
pub unique: bool,
pub default: Option<String>,
pub foreign_key: Option<ForeignKeyRef>,
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ForeignKeyRef {
pub table: String,
pub column: String,
pub on_delete: String,
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct IndexDef {
pub name: String,
pub columns: Vec<String>,
pub unique: bool,
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ConstraintDef {
pub name: String,
pub constraint_type: ConstraintType,
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub enum ConstraintType {
Unique(Vec<String>),
Check(String),
ForeignKey {
columns: Vec<String>,
ref_table: String,
ref_columns: Vec<String>,
},
}
#[derive(Debug, Default, Clone, Copy)]
pub struct SqliteSchemaEditor;
impl SqliteSchemaEditor {
fn invalid_input(message: impl Into<String>) -> DbError {
DbError::Connection(message.into())
}
fn validate_identifier(kind: &str, value: &str) -> Result<(), DbError> {
if value.trim().is_empty() {
return Err(Self::invalid_input(format!("{kind} cannot be empty")));
}
Ok(())
}
fn quote_identifier(&self, value: &str) -> Result<String, DbError> {
Self::validate_identifier("identifier", value)?;
Ok(format!("\"{}\"", value.replace('"', "\"\"")))
}
fn format_column(&self, column: &ColumnDef) -> Result<String, DbError> {
Self::validate_identifier("column name", &column.name)?;
if column.data_type.trim().is_empty() {
return Err(Self::invalid_input("column data type cannot be empty"));
}
let mut sql = format!(
"{} {}",
self.quote_identifier(&column.name)?,
column.data_type.trim()
);
if !column.nullable {
sql.push_str(" NOT NULL");
}
if column.primary_key {
sql.push_str(" PRIMARY KEY");
}
if column.unique {
sql.push_str(" UNIQUE");
}
if let Some(default) = &column.default {
sql.push_str(" DEFAULT ");
sql.push_str(default.trim());
}
if let Some(foreign_key) = &column.foreign_key {
Self::validate_identifier("foreign key table", &foreign_key.table)?;
Self::validate_identifier("foreign key column", &foreign_key.column)?;
sql.push_str(&format!(
" REFERENCES {} ({})",
self.quote_identifier(&foreign_key.table)?,
self.quote_identifier(&foreign_key.column)?
));
if !foreign_key.on_delete.trim().is_empty() {
sql.push_str(" ON DELETE ");
sql.push_str(foreign_key.on_delete.trim());
}
}
Ok(sql)
}
fn quoted_columns(&self, columns: &[String]) -> Result<String, DbError> {
if columns.is_empty() {
return Err(Self::invalid_input("columns cannot be empty"));
}
columns
.iter()
.map(|column| self.quote_identifier(column))
.collect::<Result<Vec<_>, _>>()
.map(|quoted| quoted.join(", "))
}
fn format_constraint_type(&self, constraint: &ConstraintType) -> Result<String, DbError> {
match constraint {
ConstraintType::Unique(columns) => {
Ok(format!("UNIQUE ({})", self.quoted_columns(columns)?))
}
ConstraintType::Check(check) => {
if check.trim().is_empty() {
return Err(Self::invalid_input("check constraint cannot be empty"));
}
Ok(format!("CHECK ({})", check.trim()))
}
ConstraintType::ForeignKey {
columns,
ref_table,
ref_columns,
} => {
Self::validate_identifier("reference table", ref_table)?;
if ref_columns.is_empty() {
return Err(Self::invalid_input("reference columns cannot be empty"));
}
Ok(format!(
"FOREIGN KEY ({}) REFERENCES {} ({})",
self.quoted_columns(columns)?,
self.quote_identifier(ref_table)?,
self.quoted_columns(ref_columns)?
))
}
}
}
}
impl SchemaEditor for SqliteSchemaEditor {
fn create_table(&self, table_name: &str, columns: &[ColumnDef]) -> Result<String, DbError> {
Self::validate_identifier("table name", table_name)?;
if columns.is_empty() {
return Err(Self::invalid_input(
"create_table requires at least one column",
));
}
let definitions = columns
.iter()
.map(|column| self.format_column(column))
.collect::<Result<Vec<_>, _>>()?
.join(", ");
Ok(format!(
"CREATE TABLE {} ({definitions})",
self.quote_identifier(table_name)?
))
}
fn drop_table(&self, table_name: &str, _cascade: bool) -> Result<String, DbError> {
Ok(format!(
"DROP TABLE IF EXISTS {}",
self.quote_identifier(table_name)?
))
}
fn add_column(&self, table_name: &str, column: &ColumnDef) -> Result<String, DbError> {
Ok(format!(
"ALTER TABLE {} ADD COLUMN {}",
self.quote_identifier(table_name)?,
self.format_column(column)?
))
}
fn remove_column(&self, table_name: &str, column_name: &str) -> Result<String, DbError> {
Ok(format!(
"ALTER TABLE {} DROP COLUMN {}",
self.quote_identifier(table_name)?,
self.quote_identifier(column_name)?
))
}
fn alter_column(
&self,
table_name: &str,
old: &ColumnDef,
new: &ColumnDef,
) -> Result<String, DbError> {
Self::validate_identifier("table name", table_name)?;
Self::validate_identifier("column name", &old.name)?;
Self::validate_identifier("column name", &new.name)?;
if old.name != new.name {
return self.rename_column(table_name, &old.name, &new.name);
}
if old.data_type != new.data_type {
return Ok(format!(
"ALTER TABLE {} ALTER COLUMN {} TYPE {}",
self.quote_identifier(table_name)?,
self.quote_identifier(&new.name)?,
new.data_type.trim()
));
}
if old.nullable != new.nullable {
let action = if new.nullable {
"DROP NOT NULL"
} else {
"SET NOT NULL"
};
return Ok(format!(
"ALTER TABLE {} ALTER COLUMN {} {action}",
self.quote_identifier(table_name)?,
self.quote_identifier(&new.name)?
));
}
if old.default != new.default {
return Ok(match &new.default {
Some(default) => format!(
"ALTER TABLE {} ALTER COLUMN {} SET DEFAULT {}",
self.quote_identifier(table_name)?,
self.quote_identifier(&new.name)?,
default.trim()
),
None => format!(
"ALTER TABLE {} ALTER COLUMN {} DROP DEFAULT",
self.quote_identifier(table_name)?,
self.quote_identifier(&new.name)?
),
});
}
Err(Self::invalid_input("no column changes detected"))
}
fn rename_column(
&self,
table_name: &str,
old_name: &str,
new_name: &str,
) -> Result<String, DbError> {
Ok(format!(
"ALTER TABLE {} RENAME COLUMN {} TO {}",
self.quote_identifier(table_name)?,
self.quote_identifier(old_name)?,
self.quote_identifier(new_name)?
))
}
fn create_index(&self, table_name: &str, index: &IndexDef) -> Result<String, DbError> {
Self::validate_identifier("index name", &index.name)?;
let unique = if index.unique { "UNIQUE " } else { "" };
Ok(format!(
"CREATE {unique}INDEX {} ON {} ({})",
self.quote_identifier(&index.name)?,
self.quote_identifier(table_name)?,
self.quoted_columns(&index.columns)?
))
}
fn drop_index(&self, index_name: &str) -> Result<String, DbError> {
Ok(format!(
"DROP INDEX IF EXISTS {}",
self.quote_identifier(index_name)?
))
}
fn add_constraint(
&self,
table_name: &str,
constraint: &ConstraintDef,
) -> Result<String, DbError> {
Self::validate_identifier("constraint name", &constraint.name)?;
Ok(format!(
"ALTER TABLE {} ADD CONSTRAINT {} {}",
self.quote_identifier(table_name)?,
self.quote_identifier(&constraint.name)?,
self.format_constraint_type(&constraint.constraint_type)?
))
}
fn remove_constraint(
&self,
table_name: &str,
constraint_name: &str,
) -> Result<String, DbError> {
Ok(format!(
"ALTER TABLE {} DROP CONSTRAINT {}",
self.quote_identifier(table_name)?,
self.quote_identifier(constraint_name)?
))
}
}
#[cfg(test)]
mod tests {
use super::{
ColumnDef, ConstraintDef, ConstraintType, ForeignKeyRef, IndexDef, SchemaEditor,
SqliteSchemaEditor,
};
fn id_column() -> ColumnDef {
ColumnDef {
name: "id".to_string(),
data_type: "INTEGER".to_string(),
nullable: false,
primary_key: true,
unique: false,
default: None,
foreign_key: None,
}
}
#[test]
fn create_table_sql_includes_column_attributes() {
let editor = SqliteSchemaEditor;
let columns = vec![
id_column(),
ColumnDef {
name: "name".to_string(),
data_type: "TEXT".to_string(),
nullable: false,
primary_key: false,
unique: true,
default: Some("'unknown'".to_string()),
foreign_key: None,
},
ColumnDef {
name: "owner_id".to_string(),
data_type: "INTEGER".to_string(),
nullable: true,
primary_key: false,
unique: false,
default: None,
foreign_key: Some(ForeignKeyRef {
table: "users".to_string(),
column: "id".to_string(),
on_delete: "CASCADE".to_string(),
}),
},
];
let sql = editor
.create_table("widgets", &columns)
.expect("create table SQL should be generated");
assert_eq!(
sql,
"CREATE TABLE \"widgets\" (\"id\" INTEGER NOT NULL PRIMARY KEY, \"name\" TEXT NOT NULL UNIQUE DEFAULT 'unknown', \"owner_id\" INTEGER REFERENCES \"users\" (\"id\") ON DELETE CASCADE)"
);
}
#[test]
fn create_table_requires_columns() {
let editor = SqliteSchemaEditor;
let error = editor
.create_table("widgets", &[])
.expect_err("empty table definitions should fail");
assert!(error.to_string().contains("at least one column"));
}
#[test]
fn drop_table_sql_uses_if_exists() {
let editor = SqliteSchemaEditor;
let sql = editor
.drop_table("widgets", true)
.expect("drop table SQL should be generated");
assert_eq!(sql, "DROP TABLE IF EXISTS \"widgets\"");
}
#[test]
fn add_column_sql_uses_alter_table() {
let editor = SqliteSchemaEditor;
let sql = editor
.add_column(
"widgets",
&ColumnDef {
name: "slug".to_string(),
data_type: "TEXT".to_string(),
nullable: false,
primary_key: false,
unique: true,
default: None,
foreign_key: None,
},
)
.expect("add column SQL should be generated");
assert_eq!(
sql,
"ALTER TABLE \"widgets\" ADD COLUMN \"slug\" TEXT NOT NULL UNIQUE"
);
}
#[test]
fn remove_column_sql_uses_drop_column() {
let editor = SqliteSchemaEditor;
let sql = editor
.remove_column("widgets", "legacy_slug")
.expect("remove column SQL should be generated");
assert_eq!(sql, "ALTER TABLE \"widgets\" DROP COLUMN \"legacy_slug\"");
}
#[test]
fn rename_column_sql_uses_rename_column() {
let editor = SqliteSchemaEditor;
let sql = editor
.rename_column("widgets", "slug", "public_slug")
.expect("rename column SQL should be generated");
assert_eq!(
sql,
"ALTER TABLE \"widgets\" RENAME COLUMN \"slug\" TO \"public_slug\""
);
}
#[test]
fn create_index_sql_supports_unique_indexes() {
let editor = SqliteSchemaEditor;
let sql = editor
.create_index(
"widgets",
&IndexDef {
name: "widgets_slug_idx".to_string(),
columns: vec!["slug".to_string(), "created_at".to_string()],
unique: true,
},
)
.expect("create index SQL should be generated");
assert_eq!(
sql,
"CREATE UNIQUE INDEX \"widgets_slug_idx\" ON \"widgets\" (\"slug\", \"created_at\")"
);
}
#[test]
fn drop_index_sql_uses_if_exists() {
let editor = SqliteSchemaEditor;
let sql = editor
.drop_index("widgets_slug_idx")
.expect("drop index SQL should be generated");
assert_eq!(sql, "DROP INDEX IF EXISTS \"widgets_slug_idx\"");
}
#[test]
fn add_constraint_sql_supports_unique_constraints() {
let editor = SqliteSchemaEditor;
let sql = editor
.add_constraint(
"widgets",
&ConstraintDef {
name: "widgets_slug_key".to_string(),
constraint_type: ConstraintType::Unique(vec![
"slug".to_string(),
"tenant_id".to_string(),
]),
},
)
.expect("constraint SQL should be generated");
assert_eq!(
sql,
"ALTER TABLE \"widgets\" ADD CONSTRAINT \"widgets_slug_key\" UNIQUE (\"slug\", \"tenant_id\")"
);
}
#[test]
fn remove_constraint_sql_uses_drop_constraint() {
let editor = SqliteSchemaEditor;
let sql = editor
.remove_constraint("widgets", "widgets_slug_key")
.expect("remove constraint SQL should be generated");
assert_eq!(
sql,
"ALTER TABLE \"widgets\" DROP CONSTRAINT \"widgets_slug_key\""
);
}
#[test]
fn alter_column_sql_supports_type_changes() {
let editor = SqliteSchemaEditor;
let sql = editor
.alter_column(
"widgets",
&ColumnDef {
data_type: "TEXT".to_string(),
..id_column()
},
&ColumnDef {
data_type: "INTEGER".to_string(),
..id_column()
},
)
.expect("alter column SQL should be generated");
assert_eq!(
sql,
"ALTER TABLE \"widgets\" ALTER COLUMN \"id\" TYPE INTEGER"
);
}
#[test]
fn column_definition_preserves_configuration() {
let column = ColumnDef {
name: "owner_id".to_string(),
data_type: "INTEGER".to_string(),
nullable: true,
primary_key: false,
unique: false,
default: Some("1".to_string()),
foreign_key: Some(ForeignKeyRef {
table: "users".to_string(),
column: "id".to_string(),
on_delete: "SET NULL".to_string(),
}),
};
assert_eq!(column.name, "owner_id");
assert_eq!(column.data_type, "INTEGER");
assert!(column.nullable);
assert_eq!(column.default.as_deref(), Some("1"));
assert_eq!(
column.foreign_key,
Some(ForeignKeyRef {
table: "users".to_string(),
column: "id".to_string(),
on_delete: "SET NULL".to_string(),
})
);
}
#[test]
fn index_definition_preserves_columns_and_uniqueness() {
let index = IndexDef {
name: "widgets_lookup_idx".to_string(),
columns: vec!["slug".to_string(), "tenant_id".to_string()],
unique: false,
};
assert_eq!(index.name, "widgets_lookup_idx");
assert_eq!(index.columns, vec!["slug", "tenant_id"]);
assert!(!index.unique);
}
}