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