Skip to main content

che_orm/migration/
postgres_schema.rs

1use super::*;
2use crate::{PostgresDialect, SqlDialect};
3
4#[derive(Debug, Clone, PartialEq, Eq)]
5pub struct PostgresOperationPlan {
6    pub statements: Vec<String>,
7    /// Foreign keys whose referenced key is not yet available.
8    pub deferred_foreign_keys: Vec<DeferredForeignKey>,
9    pub resulting_state: ProjectState,
10}
11
12#[derive(Debug, Default, Clone, Copy)]
13pub struct PostgresSchemaEditor;
14
15/// A foreign key whose definition is resolved from the current schema state.
16#[derive(Debug, Clone, PartialEq, Eq)]
17pub struct DeferredForeignKey {
18    pub table: String,
19    pub column: String,
20}
21
22impl DeferredForeignKey {
23    fn matches(&self, table: &str, column: &str) -> bool {
24        self.table == table && self.column == column
25    }
26    fn source<'a>(&self, state: &'a ProjectState) -> Option<&'a ColumnState> {
27        state
28            .tables()
29            .iter()
30            .find(|table| table.name == self.table)?
31            .columns
32            .iter()
33            .find(|column| column.name == self.column)
34    }
35    pub fn statement(&self, state: &ProjectState) -> Result<String, MigrationError> {
36        let column = self
37            .source(state)
38            .filter(|column| column.references.is_some())
39            .ok_or_else(|| {
40                MigrationError::InvalidState(format!(
41                    "missing deferred foreign key {}.{}",
42                    self.table, self.column
43                ))
44            })?;
45        add_foreign_key(&self.table, column)
46    }
47    pub(crate) fn exists(&self, state: &ProjectState) -> bool {
48        self.source(state)
49            .is_some_and(|column| column.references.is_some())
50    }
51    pub(crate) fn ready(&self, state: &ProjectState) -> bool {
52        self.source(state)
53            .is_some_and(|column| foreign_ready(column, state))
54    }
55    pub(crate) fn follow(&mut self, operation: &MigrationOperation) {
56        match operation {
57            MigrationOperation::RenameTable { old_name, new_name } if self.table == *old_name => {
58                self.table = new_name.clone()
59            }
60            MigrationOperation::RenameColumn {
61                table,
62                old_name,
63                new_name,
64            } if self.matches(table, old_name) => self.column = new_name.clone(),
65            MigrationOperation::RunSql {
66                state_operations, ..
67            } => {
68                for operation in state_operations {
69                    self.follow(operation);
70                }
71            }
72            _ => {}
73        }
74    }
75}
76
77fn foreign_ready(column: &ColumnState, state: &ProjectState) -> bool {
78    let Some((target, target_column)) = column
79        .references
80        .as_ref()
81        .and_then(ForeignKeyState::target_parts)
82    else {
83        return true;
84    };
85    state
86        .tables()
87        .iter()
88        .find(|table| table.name == target)
89        .is_some_and(|table| reference_target_ready(table, target_column))
90}
91
92fn reference_target_ready(table: &TableState, column: &str) -> bool {
93    table
94        .columns
95        .iter()
96        .any(|candidate| candidate.name == column && (candidate.primary_key || candidate.unique))
97        || table
98            .unique_constraints
99            .iter()
100            .any(|columns| columns.len() == 1 && columns[0] == column)
101}
102
103fn name(table: &str, kind: &str, columns: &[String]) -> String {
104    crate::schema::schema_object_name(
105        table,
106        kind,
107        &columns.iter().map(String::as_str).collect::<Vec<_>>(),
108    )
109}
110fn col_name(table: &str, kind: &str, column: &str) -> String {
111    crate::schema::schema_object_name(table, kind, &[column])
112}
113fn columns_sql(columns: &[String]) -> String {
114    columns
115        .iter()
116        .map(|column| quote_identifier(column))
117        .collect::<Vec<_>>()
118        .join(", ")
119}
120fn type_sql(ty: ColumnType) -> String {
121    let mut sql = String::new();
122    PostgresDialect::column_type(ty, &mut sql);
123    sql
124}
125fn default_sql(column: &ColumnState) -> Option<String> {
126    column
127        .default
128        .clone()
129        .or_else(|| (column.auto_now || column.auto_now_add).then(|| "CURRENT_TIMESTAMP".into()))
130}
131fn choices_sql(column: &ColumnState) -> String {
132    format!(
133        "{} IN ({})",
134        quote_identifier(&column.name),
135        column
136            .choices
137            .as_ref()
138            .unwrap()
139            .iter()
140            .map(|value| format!("'{}'", value.replace('\'', "''")))
141            .collect::<Vec<_>>()
142            .join(", ")
143    )
144}
145fn foreign_sql(column: &ColumnState) -> Result<String, MigrationError> {
146    let reference = column.references.as_ref().unwrap();
147    let (table, col) = reference
148        .target_parts()
149        .ok_or_else(|| MigrationError::InvalidState("invalid foreign key".into()))?;
150    let mut sql = format!(
151        "REFERENCES {}({})",
152        quote_identifier(table),
153        quote_identifier(col)
154    );
155    if let Some(action) = &reference.on_delete {
156        if !matches!(
157            action.to_ascii_lowercase().as_str(),
158            "cascade" | "restrict" | "set null" | "set default" | "no action"
159        ) {
160            return Err(MigrationError::InvalidState(
161                "invalid ON DELETE action".into(),
162            ));
163        }
164        sql.push_str(&format!(" ON DELETE {}", action.to_ascii_uppercase()));
165    }
166    Ok(sql)
167}
168fn add_foreign_key(table: &str, column: &ColumnState) -> Result<String, MigrationError> {
169    Ok(format!(
170        "ALTER TABLE {} ADD CONSTRAINT {} FOREIGN KEY ({}) {}",
171        quote_identifier(table),
172        quote_identifier(&col_name(table, "fk", &column.name)),
173        quote_identifier(&column.name),
174        foreign_sql(column)?
175    ))
176}
177fn column_sql(table: &str, column: &ColumnState, foreign: bool) -> Result<String, MigrationError> {
178    let mut sql = format!(
179        "{} {}",
180        quote_identifier(&column.name),
181        type_sql(column.column_type)
182    );
183    if column.primary_key && column.column_type == ColumnType::Integer {
184        sql.push_str(" GENERATED BY DEFAULT AS IDENTITY");
185    }
186    if column.primary_key {
187        sql.push_str(&format!(
188            " CONSTRAINT {} PRIMARY KEY",
189            quote_identifier(&col_name(table, "pk", &column.name))
190        ));
191    }
192    if !column.nullable && (!column.primary_key || column.column_type == ColumnType::Uuid) {
193        sql.push_str(" NOT NULL");
194    }
195    if column.unique {
196        sql.push_str(&format!(
197            " CONSTRAINT {} UNIQUE",
198            quote_identifier(&col_name(table, "field_unique", &column.name))
199        ));
200    }
201    if let Some(default) = default_sql(column) {
202        sql.push_str(&format!(" DEFAULT {default}"));
203    }
204    if let Some(check) = &column.check {
205        sql.push_str(&format!(
206            " CONSTRAINT {} CHECK ({check})",
207            quote_identifier(&col_name(table, "check", &column.name))
208        ));
209    }
210    if column.choices.is_some() {
211        sql.push_str(&format!(
212            " CONSTRAINT {} CHECK ({})",
213            quote_identifier(&col_name(table, "choices", &column.name)),
214            choices_sql(column)
215        ));
216    }
217    if foreign && column.references.is_some() {
218        sql.push_str(&format!(
219            " CONSTRAINT {} {}",
220            quote_identifier(&col_name(table, "fk", &column.name)),
221            foreign_sql(column)?
222        ));
223    }
224    Ok(sql)
225}
226fn index_sql(table: &str, columns: &[String], position: usize) -> String {
227    format!(
228        "CREATE INDEX {} ON {} ({})",
229        quote_identifier(&PostgresDialect::index_name(table, position)),
230        quote_identifier(table),
231        columns_sql(columns)
232    )
233}
234fn table<'a>(state: &'a ProjectState, name: &str) -> Result<&'a TableState, MigrationError> {
235    state
236        .tables()
237        .iter()
238        .find(|table| table.name == name)
239        .ok_or_else(|| MigrationError::InvalidState(format!("missing table {name}")))
240}
241fn constraints(table: &TableState, pending_columns: &BTreeSet<usize>) -> Vec<String> {
242    let mut names = Vec::new();
243    for (index, column) in table.columns.iter().enumerate() {
244        for (kind, present) in [
245            ("pk", column.primary_key),
246            ("field_unique", column.unique),
247            ("check", column.check.is_some()),
248            ("choices", column.choices.is_some()),
249            (
250                "fk",
251                column.references.is_some() && !pending_columns.contains(&index),
252            ),
253        ] {
254            if present {
255                names.push(col_name(&table.name, kind, &column.name));
256            }
257        }
258    }
259    names.extend(
260        table
261            .unique_constraints
262            .iter()
263            .map(|columns| name(&table.name, "unique", columns)),
264    );
265    names
266}
267
268impl PostgresSchemaEditor {
269    /// Plans a complete historical migration, resolving deferred foreign keys
270    /// in the same order as the executor. Unlike planning isolated operations,
271    /// this handles cyclic table creation and changes to pending references.
272    pub fn plan_migration(
273        operations: &[MigrationOperation],
274        before: &ProjectState,
275    ) -> Result<PostgresOperationPlan, MigrationError> {
276        let mut state = before.clone();
277        let mut statements = Vec::new();
278        let mut deferred = Vec::<DeferredForeignKey>::new();
279        for operation in operations {
280            let plan = Self::plan_with_deferred(operation, &state, &deferred)?;
281            statements.extend(plan.statements);
282            if let MigrationOperation::AlterColumn {
283                table, old_column, ..
284            } = operation
285            {
286                deferred.retain(|key| key.table != *table || key.column != old_column.name);
287            }
288            for key in &mut deferred {
289                key.follow(operation);
290            }
291            state = plan.resulting_state;
292            deferred.retain(|key| key.exists(&state));
293            deferred.extend(plan.deferred_foreign_keys);
294            let mut waiting = Vec::new();
295            for key in deferred {
296                if key.ready(&state) {
297                    statements.push(key.statement(&state)?);
298                } else {
299                    waiting.push(key);
300                }
301            }
302            deferred = waiting;
303        }
304        if !deferred.is_empty() {
305            return Err(MigrationError::InvalidState(
306                "migration leaves unresolved PostgreSQL foreign keys".into(),
307            ));
308        }
309        Ok(PostgresOperationPlan {
310            statements,
311            deferred_foreign_keys: deferred,
312            resulting_state: state,
313        })
314    }
315
316    pub fn plan(
317        operation: &MigrationOperation,
318        before: &ProjectState,
319    ) -> Result<PostgresOperationPlan, MigrationError> {
320        Self::plan_with_deferred(operation, before, &[])
321    }
322
323    pub(crate) fn plan_with_deferred(
324        operation: &MigrationOperation,
325        before: &ProjectState,
326        pending: &[DeferredForeignKey],
327    ) -> Result<PostgresOperationPlan, MigrationError> {
328        let mut after = before.clone();
329        operation.state_forwards(&mut after)?;
330        let mut statements = Vec::new();
331        let mut deferred = Vec::new();
332        match operation {
333            MigrationOperation::CreateModel { model } => {
334                create(&model.table, before, &mut statements, &mut deferred)?
335            }
336            MigrationOperation::CreateTable { table } => {
337                create(table, before, &mut statements, &mut deferred)?
338            }
339            MigrationOperation::DropTable { name } => {
340                statements.push(format!("DROP TABLE {}", quote_identifier(name)))
341            }
342            MigrationOperation::RenameTable { old_name, new_name } => {
343                statements.push(format!(
344                    "ALTER TABLE {} RENAME TO {}",
345                    quote_identifier(old_name),
346                    quote_identifier(new_name)
347                ));
348                rename_constraints(
349                    table(before, old_name)?,
350                    table(&after, new_name)?,
351                    pending,
352                    &mut statements,
353                );
354                for position in 0..table(before, old_name)?.indexes.len() {
355                    statements.push(format!(
356                        "ALTER INDEX {} RENAME TO {}",
357                        quote_identifier(&PostgresDialect::index_name(old_name, position)),
358                        quote_identifier(&PostgresDialect::index_name(new_name, position))
359                    ));
360                }
361            }
362            MigrationOperation::RenameColumn {
363                table: table_name,
364                old_name,
365                new_name,
366            } => {
367                statements.push(format!(
368                    "ALTER TABLE {} RENAME COLUMN {} TO {}",
369                    quote_identifier(table_name),
370                    quote_identifier(old_name),
371                    quote_identifier(new_name)
372                ));
373                rename_constraints(
374                    table(before, table_name)?,
375                    table(&after, table_name)?,
376                    pending,
377                    &mut statements,
378                );
379            }
380            MigrationOperation::AddColumn { table, column } => {
381                let foreign = foreign_ready(column, &after);
382                statements.push(format!(
383                    "ALTER TABLE {} ADD COLUMN {}",
384                    quote_identifier(table),
385                    column_sql(table, column, foreign)?
386                ));
387                if !foreign {
388                    deferred.push(DeferredForeignKey {
389                        table: table.clone(),
390                        column: column.name.clone(),
391                    });
392                }
393            }
394            MigrationOperation::RemoveColumn {
395                table: table_name,
396                column,
397            } => {
398                statements.push(format!(
399                    "ALTER TABLE {} DROP COLUMN {}",
400                    quote_identifier(table_name),
401                    quote_identifier(column)
402                ));
403                renumber_indexes(
404                    table(before, table_name)?,
405                    table(&after, table_name)?,
406                    &mut statements,
407                );
408            }
409            MigrationOperation::AlterColumn {
410                table,
411                old_column,
412                new_column,
413            } => {
414                let absent = pending
415                    .iter()
416                    .any(|key| key.matches(table, &old_column.name));
417                let replacing = absent
418                    || old_column.references != new_column.references
419                    || old_column.column_type != new_column.column_type;
420                let foreign = !replacing || foreign_ready(new_column, &after);
421                let mut old = old_column.clone();
422                let mut new = new_column.clone();
423                if absent {
424                    old.references = None;
425                }
426                if !foreign {
427                    new.references = None;
428                    deferred.push(DeferredForeignKey {
429                        table: table.clone(),
430                        column: new_column.name.clone(),
431                    });
432                }
433                alter(table, &old, &new, &mut statements)?;
434            }
435            MigrationOperation::AddIndex {
436                table: table_name,
437                columns,
438            } => statements.push(index_sql(
439                table_name,
440                columns,
441                table(before, table_name)?.indexes.len(),
442            )),
443            MigrationOperation::RemoveIndex {
444                table: table_name,
445                columns,
446            } => {
447                let position = table(before, table_name)?
448                    .indexes
449                    .iter()
450                    .position(|item| item == columns)
451                    .ok_or_else(|| MigrationError::InvalidState("missing index".into()))?;
452                statements.push(format!(
453                    "DROP INDEX {}",
454                    quote_identifier(&PostgresDialect::index_name(table_name, position))
455                ));
456                renumber_indexes(
457                    table(before, table_name)?,
458                    table(&after, table_name)?,
459                    &mut statements,
460                );
461            }
462            MigrationOperation::AddUniqueConstraint { table, columns } => statements.push(format!(
463                "ALTER TABLE {} ADD CONSTRAINT {} UNIQUE ({})",
464                quote_identifier(table),
465                quote_identifier(&name(table, "unique", columns)),
466                columns_sql(columns)
467            )),
468            MigrationOperation::RemoveUniqueConstraint { table, columns } => {
469                statements.push(format!(
470                    "ALTER TABLE {} DROP CONSTRAINT {}",
471                    quote_identifier(table),
472                    quote_identifier(&name(table, "unique", columns))
473                ))
474            }
475            MigrationOperation::BackfillColumn {
476                table,
477                column,
478                value,
479            } => statements.push(format!(
480                "UPDATE {} SET {} = {value} WHERE {} IS NULL",
481                quote_identifier(table),
482                quote_identifier(column),
483                quote_identifier(column)
484            )),
485            MigrationOperation::CreateSchemaObject { object } => {
486                statements.push(object.sql.clone())
487            }
488            MigrationOperation::DropSchemaObject { kind, name } => {
489                let keyword = match kind {
490                    SchemaObjectKind::Table => "TABLE",
491                    SchemaObjectKind::View => "VIEW",
492                    SchemaObjectKind::Trigger => "TRIGGER",
493                };
494                let mut sql = format!("DROP {keyword} {}", quote_identifier(name));
495                if *kind == SchemaObjectKind::Trigger {
496                    let object = before
497                        .objects()
498                        .iter()
499                        .find(|o| o.kind == *kind && o.name == *name)
500                        .ok_or_else(|| MigrationError::InvalidState("missing trigger".into()))?;
501                    let words = object.sql.split_whitespace().collect::<Vec<_>>();
502                    let target = words
503                        .windows(2)
504                        .find(|w| w[0].eq_ignore_ascii_case("ON"))
505                        .map(|w| w[1].trim_matches('"'))
506                        .ok_or_else(|| {
507                            MigrationError::InvalidState(
508                                "trigger target requires explicit RunSql".into(),
509                            )
510                        })?;
511                    crate::schema::validate_identifier(target).map_err(|error| {
512                        MigrationError::InvalidState(format!("invalid trigger target: {error:?}"))
513                    })?;
514                    sql.push_str(&format!(" ON {}", quote_identifier(target)));
515                }
516                statements.push(sql);
517            }
518            MigrationOperation::NormalizeSqliteDateTimes { .. } => {}
519            MigrationOperation::RunSql { forward, .. } => statements.push(forward.clone()),
520        }
521        Ok(PostgresOperationPlan {
522            statements,
523            deferred_foreign_keys: deferred,
524            resulting_state: after,
525        })
526    }
527}
528fn create(
529    table: &TableState,
530    before: &ProjectState,
531    sql: &mut Vec<String>,
532    deferred: &mut Vec<DeferredForeignKey>,
533) -> Result<(), MigrationError> {
534    let mut definitions = Vec::new();
535    for column in &table.columns {
536        let foreign = column.references.as_ref().is_none_or(|reference| {
537            reference
538                .target_parts()
539                .is_some_and(|(target, target_column)| {
540                    if target == table.name {
541                        reference_target_ready(table, target_column)
542                    } else {
543                        foreign_ready(column, before)
544                    }
545                })
546        });
547        definitions.push(column_sql(&table.name, column, foreign)?);
548        if !foreign {
549            deferred.push(DeferredForeignKey {
550                table: table.name.clone(),
551                column: column.name.clone(),
552            });
553        }
554    }
555    for columns in &table.unique_constraints {
556        definitions.push(format!(
557            "CONSTRAINT {} UNIQUE ({})",
558            quote_identifier(&name(&table.name, "unique", columns)),
559            columns_sql(columns)
560        ));
561    }
562    sql.push(format!(
563        "CREATE TABLE {} ({})",
564        quote_identifier(&table.name),
565        definitions.join(", ")
566    ));
567    sql.extend(
568        table
569            .indexes
570            .iter()
571            .enumerate()
572            .map(|(i, columns)| index_sql(&table.name, columns, i)),
573    );
574    Ok(())
575}
576fn rename_constraints(
577    before: &TableState,
578    after: &TableState,
579    pending: &[DeferredForeignKey],
580    sql: &mut Vec<String>,
581) {
582    let pending_columns = before
583        .columns
584        .iter()
585        .enumerate()
586        .filter_map(|(index, column)| {
587            pending
588                .iter()
589                .any(|key| key.matches(&before.name, &column.name))
590                .then_some(index)
591        })
592        .collect::<BTreeSet<_>>();
593    for (old, new) in constraints(before, &pending_columns)
594        .into_iter()
595        .zip(constraints(after, &pending_columns))
596    {
597        if old != new {
598            sql.push(format!(
599                "ALTER TABLE {} RENAME CONSTRAINT {} TO {}",
600                quote_identifier(&after.name),
601                quote_identifier(&old),
602                quote_identifier(&new)
603            ));
604        }
605    }
606}
607fn renumber_indexes(before: &TableState, after: &TableState, sql: &mut Vec<String>) {
608    for (new, columns) in after.indexes.iter().enumerate() {
609        if let Some(old) = before.indexes.iter().position(|item| item == columns)
610            && old != new
611        {
612            sql.push(format!(
613                "ALTER INDEX {} RENAME TO {}",
614                quote_identifier(&PostgresDialect::index_name(&before.name, old)),
615                quote_identifier(&PostgresDialect::index_name(&after.name, new))
616            ));
617        }
618    }
619}
620fn alter(
621    table: &str,
622    old: &ColumnState,
623    new: &ColumnState,
624    sql: &mut Vec<String>,
625) -> Result<(), MigrationError> {
626    let prefix = format!("ALTER TABLE {}", quote_identifier(table));
627    let column = quote_identifier(&new.name);
628    let drop = |kind: &str| {
629        format!(
630            "{prefix} DROP CONSTRAINT {}",
631            quote_identifier(&col_name(table, kind, &old.name))
632        )
633    };
634    let old_identity = old.primary_key && old.column_type == ColumnType::Integer;
635    let new_identity = new.primary_key && new.column_type == ColumnType::Integer;
636    let type_changed = old.column_type != new.column_type;
637    let replace_check = type_changed || old.check != new.check;
638    let replace_choices = type_changed || old.choices != new.choices;
639    let replace_foreign = type_changed || old.references != new.references;
640    let old_default = default_sql(old);
641    let new_default = default_sql(new);
642    let replace_default =
643        type_changed || old_default != new_default || (!old_identity && new_identity);
644
645    // Drop constraints that may depend on the old type before changing it.
646    if replace_foreign && old.references.is_some() {
647        sql.push(drop("fk"));
648    }
649    if replace_check && old.check.is_some() {
650        sql.push(drop("check"));
651    }
652    if replace_choices && old.choices.is_some() {
653        sql.push(drop("choices"));
654    }
655    if old.unique && !new.unique {
656        sql.push(drop("field_unique"));
657    }
658    if old_identity && !new_identity {
659        sql.push(format!("{prefix} ALTER COLUMN {column} DROP IDENTITY"));
660    }
661    if old.primary_key && !new.primary_key {
662        sql.push(drop("pk"));
663    }
664    if replace_default && old_default.is_some() {
665        sql.push(format!("{prefix} ALTER COLUMN {column} DROP DEFAULT"));
666    }
667    if type_changed {
668        sql.push(format!(
669            "{prefix} ALTER COLUMN {column} TYPE {}",
670            type_sql(new.column_type)
671        ));
672    }
673    if old.nullable != new.nullable {
674        sql.push(format!(
675            "{prefix} ALTER COLUMN {column} {} NOT NULL",
676            if new.nullable { "DROP" } else { "SET" }
677        ));
678    }
679    if !old_identity && new_identity {
680        sql.push(format!(
681            "{prefix} ALTER COLUMN {column} ADD GENERATED BY DEFAULT AS IDENTITY"
682        ));
683    }
684    if !old.primary_key && new.primary_key {
685        sql.push(format!(
686            "{prefix} ADD CONSTRAINT {} PRIMARY KEY ({column})",
687            quote_identifier(&col_name(table, "pk", &new.name))
688        ));
689    }
690    if replace_default && let Some(value) = new_default {
691        sql.push(format!(
692            "{prefix} ALTER COLUMN {column} SET DEFAULT {value}"
693        ));
694    }
695    if !old.unique && new.unique {
696        sql.push(format!(
697            "{prefix} ADD CONSTRAINT {} UNIQUE ({column})",
698            quote_identifier(&col_name(table, "field_unique", &new.name))
699        ));
700    }
701    if replace_check && let Some(check) = &new.check {
702        sql.push(format!(
703            "{prefix} ADD CONSTRAINT {} CHECK ({check})",
704            quote_identifier(&col_name(table, "check", &new.name))
705        ));
706    }
707    if replace_choices && new.choices.is_some() {
708        sql.push(format!(
709            "{prefix} ADD CONSTRAINT {} CHECK ({})",
710            quote_identifier(&col_name(table, "choices", &new.name)),
711            choices_sql(new)
712        ));
713    }
714    if replace_foreign && new.references.is_some() {
715        sql.push(add_foreign_key(table, new)?);
716    }
717    Ok(())
718}