1use super::*;
2use crate::{PostgresDialect, SqlDialect};
3
4#[derive(Debug, Clone, PartialEq, Eq)]
5pub struct PostgresOperationPlan {
6 pub statements: Vec<String>,
7 pub deferred_foreign_keys: Vec<DeferredForeignKey>,
9 pub resulting_state: ProjectState,
10}
11
12#[derive(Debug, Default, Clone, Copy)]
13pub struct PostgresSchemaEditor;
14
15#[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 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 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}