use sqltrail_core::{ColumnModel, DangerLevel, MigrationOperation, MigrationPlan, TableModel};
pub fn render_plan(plan: &MigrationPlan) -> String {
let mut out = String::new();
out.push_str("-- 本文件由 sqltrail-cli 生成,请人工审核后再执行\n");
for op in &plan.operations {
let statements = render_op(op);
for (i, stmt) in statements.iter().enumerate() {
match op.danger() {
DangerLevel::Dangerous if i == 0 => {
out.push_str("-- @danger 危险操作:apply 需要显式传入 --allow-destructive\n");
}
DangerLevel::Caution if i == 0 => {
out.push_str("-- @caution 需注意的操作:请确认不影响既有数据\n");
}
_ => {}
}
out.push_str(stmt);
out.push('\n');
}
out.push('\n');
}
out
}
pub fn render_op(op: &MigrationOperation) -> Vec<String> {
use MigrationOperation::*;
match op {
CreateSchema { name } => vec![format!(
"CREATE SCHEMA IF NOT EXISTS {};",
quote_ident(name)
)],
CreateTable { table } => vec![render_create_table(table)],
RenameTable {
schema,
old_name,
new_name,
} => vec![format!(
"ALTER TABLE {} RENAME TO {};",
quote_table(schema, old_name),
quote_ident(new_name)
)],
DropTable { schema, name } => vec![format!("DROP TABLE {};", quote_table(schema, name))],
AddColumn {
schema,
table,
column,
} => vec![format!(
"ALTER TABLE {} ADD COLUMN {};",
quote_table(schema, table),
render_column_def(column, false, false)
)],
DropColumn {
schema,
table,
name,
} => vec![format!(
"ALTER TABLE {} DROP COLUMN {};",
quote_table(schema, table),
quote_ident(name)
)],
RenameColumn {
schema,
table,
old_name,
new_name,
} => vec![format!(
"ALTER TABLE {} RENAME COLUMN {} TO {};",
quote_table(schema, table),
quote_ident(old_name),
quote_ident(new_name)
)],
AlterColumnType {
schema,
table,
column,
new_type,
..
} => vec![format!(
"ALTER TABLE {} ALTER COLUMN {} TYPE {};",
quote_table(schema, table),
quote_ident(column),
new_type
)],
SetNotNull {
schema,
table,
column,
} => vec![format!(
"ALTER TABLE {} ALTER COLUMN {} SET NOT NULL;",
quote_table(schema, table),
quote_ident(column)
)],
DropNotNull {
schema,
table,
column,
} => vec![format!(
"ALTER TABLE {} ALTER COLUMN {} DROP NOT NULL;",
quote_table(schema, table),
quote_ident(column)
)],
SetDefault {
schema,
table,
column,
default_sql,
} => vec![format!(
"ALTER TABLE {} ALTER COLUMN {} SET DEFAULT {};",
quote_table(schema, table),
quote_ident(column),
default_sql
)],
DropDefault {
schema,
table,
column,
} => vec![format!(
"ALTER TABLE {} ALTER COLUMN {} DROP DEFAULT;",
quote_table(schema, table),
quote_ident(column)
)],
AddPrimaryKey {
schema,
table,
columns,
} => vec![format!(
"ALTER TABLE {} ADD PRIMARY KEY ({});",
quote_table(schema, table),
quote_cols(columns)
)],
DropPrimaryKey {
schema,
table,
name,
} => vec![format!(
"ALTER TABLE {} DROP CONSTRAINT {};",
quote_table(schema, table),
quote_ident(name)
)],
AddUnique {
schema,
table,
name,
columns,
} => {
let cols = quote_cols(columns);
match name {
Some(n) => vec![format!(
"ALTER TABLE {} ADD CONSTRAINT {} UNIQUE ({});",
quote_table(schema, table),
quote_ident(n),
cols
)],
None => vec![format!(
"ALTER TABLE {} ADD UNIQUE ({});",
quote_table(schema, table),
cols
)],
}
}
DropUnique {
schema,
table,
name,
} => {
let constraint = name.clone().unwrap_or_else(|| format!("{table}_key"));
vec![format!(
"ALTER TABLE {} DROP CONSTRAINT {};",
quote_table(schema, table),
quote_ident(&constraint)
)]
}
AddForeignKey { schema, table, fk } => vec![format!(
"ALTER TABLE {} ADD FOREIGN KEY ({}) REFERENCES {}({}){};",
quote_table(schema, table),
quote_cols(&fk.columns),
quote_ref_table(&fk.ref_table),
quote_cols(&fk.ref_columns),
render_fk_rules(fk.on_delete.as_deref(), fk.on_update.as_deref()),
)],
DropForeignKey {
schema,
table,
name,
} => {
let constraint = name.clone().unwrap_or_else(|| format!("{table}_fkey"));
vec![format!(
"ALTER TABLE {} DROP CONSTRAINT {};",
quote_table(schema, table),
quote_ident(&constraint)
)]
}
AddIndex {
schema,
table,
index,
} => {
let unique = if index.unique { "UNIQUE " } else { "" };
let predicate = match &index.predicate {
Some(p) => format!(" WHERE {}", strip_where(p)),
None => String::new(),
};
let generated_name;
let name = match index.name.as_deref() {
Some(name) => name,
None => {
generated_name = format!("{table}_{}_idx", index.columns.join("_"));
&generated_name
}
};
vec![format!(
"CREATE {unique}INDEX {} ON {} ({}){predicate};",
quote_ident(name),
quote_table(schema, table),
quote_cols(&index.columns),
)]
}
DropIndex {
schema,
table: _table,
name,
} => vec![format!(
"DROP INDEX IF EXISTS {}.{};",
quote_ident(schema),
quote_ident(name)
)],
SetTableComment {
schema,
table,
comment,
} => vec![format!(
"COMMENT ON TABLE {} IS {};",
quote_table(schema, table),
render_comment(comment.as_deref())
)],
SetColumnComment {
schema,
table,
column,
comment,
} => vec![format!(
"COMMENT ON COLUMN {}.{} IS {};",
quote_table(schema, table),
quote_ident(column),
render_comment(comment.as_deref())
)],
CreateFunction { function } | ReplaceFunction { function } => {
vec![ensure_statement(&function.definition_sql)]
}
DropFunction { function } => vec![format!(
"DROP FUNCTION {}({});",
quote_table(&function.schema, &function.name),
function.identity_arguments.trim()
)],
SetFunctionComment {
schema,
name,
identity_arguments,
comment,
} => vec![format!(
"COMMENT ON FUNCTION {}({}) IS {};",
quote_table(schema, name),
identity_arguments.trim(),
render_comment(comment.as_deref())
)],
}
}
pub fn render_create_table(table: &TableModel) -> String {
let mut body: Vec<String> = Vec::new();
let single_pk: Option<&str> = table
.primary_key
.as_ref()
.filter(|p| p.columns.len() == 1)
.map(|p| p.columns[0].as_str());
let inline_unique = |col: &str| {
table
.unique_constraints
.iter()
.any(|u| u.name.is_none() && u.columns.len() == 1 && u.columns[0] == col)
};
for col in &table.columns {
body.push(render_column_def(
col,
single_pk == Some(col.name.as_str()),
inline_unique(&col.name),
));
}
if let Some(pk) = &table.primary_key {
if pk.columns.len() > 1 {
body.push(format!("PRIMARY KEY ({})", quote_cols(&pk.columns)));
}
}
for u in &table.unique_constraints {
if u.name.is_none() && u.columns.len() == 1 {
continue; }
let cols = quote_cols(&u.columns);
match &u.name {
Some(n) => body.push(format!("CONSTRAINT {} UNIQUE ({cols})", quote_ident(n))),
None => body.push(format!("UNIQUE ({cols})")),
}
}
for fk in &table.foreign_keys {
body.push(format!(
"FOREIGN KEY ({}) REFERENCES {}({}){}",
quote_cols(&fk.columns),
quote_ref_table(&fk.ref_table),
quote_cols(&fk.ref_columns),
render_fk_rules(fk.on_delete.as_deref(), fk.on_update.as_deref()),
));
}
format!(
"CREATE TABLE {} (\n {}\n);",
quote_table(&table.schema, &table.name),
body.join(",\n ")
)
}
pub fn render_column_def(col: &ColumnModel, inline_pk: bool, inline_unique: bool) -> String {
let mut s = format!("{} {}", quote_ident(&col.name), col.sql_type);
if !is_serial(&col.sql_type) && !col.nullable {
s.push_str(" NOT NULL");
}
if let Some(d) = &col.default_sql {
s.push_str(&format!(" DEFAULT {d}"));
}
if inline_unique {
s.push_str(" UNIQUE");
}
if inline_pk {
s.push_str(" PRIMARY KEY");
}
s
}
fn render_fk_rules(on_delete: Option<&str>, on_update: Option<&str>) -> String {
let mut s = String::new();
if let Some(d) = on_delete {
s.push_str(&format!(" ON DELETE {}", d.to_uppercase()));
}
if let Some(u) = on_update {
s.push_str(&format!(" ON UPDATE {}", u.to_uppercase()));
}
s
}
fn strip_where(p: &str) -> String {
let trimmed = p.trim();
if trimmed.to_lowercase().starts_with("where ") {
trimmed[6..].trim().to_string()
} else {
trimmed.to_string()
}
}
fn is_serial(t: &str) -> bool {
matches!(t.trim(), "serial" | "bigserial" | "smallserial")
}
fn quote_ident(name: &str) -> String {
format!("\"{}\"", name.replace('"', "\"\""))
}
fn quote_table(schema: &str, table: &str) -> String {
format!("{}.{}", quote_ident(schema), quote_ident(table))
}
fn quote_ref_table(reference: &str) -> String {
match reference.rsplit_once('.') {
Some((schema, table)) => format!("{}.{}", quote_ident(schema), quote_ident(table)),
None => quote_ident(reference),
}
}
fn quote_cols(cols: &[String]) -> String {
cols.iter()
.map(|c| quote_ident(c))
.collect::<Vec<_>>()
.join(", ")
}
fn render_comment(comment: Option<&str>) -> String {
match comment {
Some(comment) => format!("'{}'", comment.replace('\'', "''")),
None => "NULL".to_string(),
}
}
fn ensure_statement(sql: &str) -> String {
let sql = sql.trim();
if sql.ends_with(';') {
sql.to_string()
} else {
format!("{sql};")
}
}
#[cfg(test)]
mod tests {
use super::*;
use sqltrail_core::{
ColumnModel, ForeignKeyModel, FunctionModel, IndexModel, PrimaryKeyModel,
UniqueConstraintModel,
};
fn employee_table() -> TableModel {
TableModel {
schema: "auth".into(),
name: "employees".into(),
rename_from: None,
comment: None,
columns: vec![
ColumnModel {
name: "id".into(),
sql_type: "bigserial".into(),
nullable: false,
primary_key: true,
rename_from: None,
default_sql: None,
comment: None,
},
ColumnModel {
name: "employee_no".into(),
sql_type: "varchar(64)".into(),
nullable: false,
primary_key: false,
rename_from: None,
default_sql: None,
comment: None,
},
ColumnModel {
name: "avatar".into(),
sql_type: "text".into(),
nullable: true,
primary_key: false,
rename_from: None,
default_sql: None,
comment: None,
},
],
primary_key: Some(PrimaryKeyModel {
name: None,
columns: vec!["id".into()],
}),
unique_constraints: vec![UniqueConstraintModel {
name: None,
columns: vec!["employee_no".into()],
}],
foreign_keys: vec![],
indexes: vec![],
}
}
#[test]
fn 渲染建表语句() {
let sql = render_create_table(&employee_table());
assert!(sql.starts_with("CREATE TABLE \"auth\".\"employees\" ("));
assert!(sql.contains("\"id\" bigserial PRIMARY KEY"));
assert!(sql.contains("\"employee_no\" varchar(64) NOT NULL UNIQUE"));
assert!(sql.contains("\"avatar\" text"));
assert!(sql.ends_with(");"));
}
#[test]
fn 渲染联合主键与外键() {
let t = TableModel {
schema: "auth".into(),
name: "employee_departments".into(),
rename_from: None,
comment: None,
columns: vec![
ColumnModel {
name: "employee_id".into(),
sql_type: "bigint".into(),
nullable: false,
primary_key: true,
rename_from: None,
default_sql: None,
comment: None,
},
ColumnModel {
name: "department_id".into(),
sql_type: "bigint".into(),
nullable: false,
primary_key: true,
rename_from: None,
default_sql: None,
comment: None,
},
],
primary_key: Some(PrimaryKeyModel {
name: None,
columns: vec!["employee_id".into(), "department_id".into()],
}),
unique_constraints: vec![],
foreign_keys: vec![ForeignKeyModel {
name: None,
columns: vec!["employee_id".into()],
ref_table: "auth.employees".into(),
ref_columns: vec!["id".into()],
on_delete: Some("CASCADE".into()),
on_update: None,
}],
indexes: vec![],
};
let sql = render_create_table(&t);
assert!(sql.contains("PRIMARY KEY (\"employee_id\", \"department_id\")"));
assert!(
sql.contains(
"FOREIGN KEY (\"employee_id\") REFERENCES \"auth\".\"employees\"(\"id\") ON DELETE CASCADE"
)
);
}
#[test]
fn 渲染部分索引() {
let t = TableModel {
schema: "auth".into(),
name: "employees".into(),
rename_from: None,
comment: None,
columns: vec![ColumnModel {
name: "deleted_at".into(),
sql_type: "timestamptz".into(),
nullable: true,
primary_key: false,
rename_from: None,
default_sql: None,
comment: None,
}],
primary_key: None,
unique_constraints: vec![],
foreign_keys: vec![],
indexes: vec![IndexModel {
name: Some("idx_employees_deleted".into()),
columns: vec!["deleted_at".into()],
unique: false,
predicate: Some("deleted_at IS NOT NULL".into()),
}],
};
let sql = render_op(&MigrationOperation::AddIndex {
schema: "auth".into(),
table: "employees".into(),
index: t.indexes[0].clone(),
});
assert_eq!(
sql[0],
"CREATE INDEX \"idx_employees_deleted\" ON \"auth\".\"employees\" (\"deleted_at\") WHERE deleted_at IS NOT NULL;"
);
}
#[test]
fn 危险操作带标记() {
let plan = MigrationPlan {
operations: vec![MigrationOperation::DropTable {
schema: "public".into(),
name: "legacy".into(),
}],
};
let sql = render_plan(&plan);
assert!(sql.contains("-- @danger"));
assert!(sql.contains("DROP TABLE \"public\".\"legacy\";"));
}
#[test]
fn 渲染注释会转义单引号() {
let sql = render_op(&MigrationOperation::SetColumnComment {
schema: "public".into(),
table: "users".into(),
column: "name".into(),
comment: Some("用户的显示名".into()),
});
assert_eq!(
sql[0],
"COMMENT ON COLUMN \"public\".\"users\".\"name\" IS '用户的显示名';"
);
let escaped = render_op(&MigrationOperation::SetTableComment {
schema: "public".into(),
table: "users".into(),
comment: Some("owner's table".into()),
});
assert!(escaped[0].contains("'owner''s table'"));
}
#[test]
fn 渲染函数与稳定索引名() {
let function = FunctionModel {
schema: "public".into(),
name: "answer".into(),
identity_arguments: String::new(),
definition_sql: "CREATE OR REPLACE FUNCTION public.answer() RETURNS integer LANGUAGE sql AS $$ SELECT 42 $$".into(),
comment: Some("答案".into()),
};
let sql = render_op(&MigrationOperation::CreateFunction { function });
assert!(sql[0].ends_with(';'));
let index = render_op(&MigrationOperation::AddIndex {
schema: "public".into(),
table: "users".into(),
index: IndexModel {
name: None,
columns: vec!["email".into()],
unique: false,
predicate: None,
},
});
assert!(index[0].contains("\"users_email_idx\""));
}
}