Skip to main content

schema_sql_generator/postgresql/
postgres_trigger_generator.rs

1use crate::common::generator_context::GeneratorContext;
2use crate::common::trigger_generator::TriggerGenerator;
3use crate::sql_println;
4use schema_model::model::relation::Relation;
5use schema_model::model::table::Table;
6use schema_model::model::types::{DatabaseType, ForeignKeyMode, RelationType, TriggerType};
7
8fn old_match_where_clause(relation: &Relation) -> String {
9    relation
10        .column_pairs()
11        .iter()
12        .map(|(from_col, to_col)| format!("{} = OLD.{}", from_col, to_col))
13        .collect::<Vec<_>>()
14        .join(" and ")
15}
16
17pub struct PostgresTriggerGenerator {
18    context: GeneratorContext,
19}
20
21impl PostgresTriggerGenerator {
22    pub fn new(context: GeneratorContext) -> Self {
23        Self { context }
24    }
25
26    fn should_output_delete_trigger(&self, table: &Table) -> bool {
27        let has_delete_triggers = table
28            .triggers()
29            .iter()
30            .any(|t| t.trigger_type() == TriggerType::Delete);
31
32        let has_reverse_relations_with_triggers = !table.reverse_relations().is_empty()
33            && self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers;
34
35        let has_aggregations = !table.aggregations().is_empty();
36
37        has_delete_triggers || has_reverse_relations_with_triggers || has_aggregations
38    }
39
40    fn should_output_update_trigger(&self, table: &Table) -> bool {
41        let has_update_triggers = table
42            .triggers()
43            .iter()
44            .any(|t| t.trigger_type() == TriggerType::Update);
45
46        let has_relations_with_triggers = !table.relations().is_empty()
47            && self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers;
48
49        let has_aggregations = !table.aggregations().is_empty();
50
51        has_update_triggers || has_relations_with_triggers || has_aggregations
52    }
53}
54
55impl TriggerGenerator for PostgresTriggerGenerator {
56    fn output_triggers(&self) {
57        let database_model = self.context.settings().database_model();
58
59        for table in database_model.all_tables() {
60            self.output_triggers_for_table(table);
61        }
62    }
63
64    fn output_triggers_for_table(&self, table: &Table) {
65        let separator = self.context.settings().statement_separator();
66
67        if self.should_output_delete_trigger(table) {
68            self.output_delete_trigger(table, separator);
69        }
70
71        if self.should_output_update_trigger(table) {
72            self.output_update_trigger(table, separator);
73        }
74    }
75}
76
77impl PostgresTriggerGenerator {
78    fn output_delete_trigger(&self, table: &Table, separator: &str) {
79        let database_type = self.context.settings().database_type();
80        let table_name = table.name().to_lowercase();
81        let fn_name = format!("{}_delete", table_name);
82        let fully_qualified_table = table.fully_qualified_table_name(database_type);
83        let fully_qualified_fn = format!(
84            "{}.{}",
85            table
86                .schema_name()
87                .unwrap_or(DatabaseType::Postgresql.default_schema().unwrap()),
88            fn_name
89        );
90
91        self.context.with_writer(|writer| {
92            sql_println!(writer, "/* {} */", fully_qualified_fn);
93            sql_println!(
94                writer,
95                "create or replace function {}() returns trigger as $BODY$"
96            ,
97                fully_qualified_fn
98            );
99            sql_println!(writer, "begin");
100
101            if self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers {
102                // A reverse relation carries the *original* relation's fields unchanged
103                // (attached to the parent for convenient lookup): `from_table_name`/
104                // `from_column_name` identify the child table and its FK column, while
105                // `to_table_name`/`to_column_name` still refer to this table (the parent)
106                // itself. The child table must be resolved via `from_table_name`, not
107                // `to_table_name` - using `to_table_name` here would resolve back to this
108                // same table and generate a trigger that deletes/updates/checks itself.
109                for relation in table.reverse_relations() {
110                    let where_clause = old_match_where_clause(relation);
111                    match relation.relation_type() {
112                        RelationType::Enforce => {
113                            let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
114                            sql_println!(
115                                writer,
116                                "   if (select count(*) from {} where {}) > 0 then",
117                                child_table.fully_qualified_table_name(database_type),
118                                where_clause
119                            );
120                            sql_println!(
121                                writer,
122                                "      raise exception 'The row in {} cannot be deleted. It is being used by a row in the {} table.';",
123                                fully_qualified_table,
124                                child_table.fully_qualified_table_name(database_type)
125                            );
126                            sql_println!(writer, "   end if;");
127                        }
128                        RelationType::SetNull => {
129                            let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
130                            let set_clause = relation
131                                .column_pairs()
132                                .iter()
133                                .map(|(from_col, _)| format!("{} = null", from_col))
134                                .collect::<Vec<_>>()
135                                .join(", ");
136                            sql_println!(
137                                writer,
138                                "   update {} set {} where {};",
139                                child_table.fully_qualified_table_name(database_type),
140                                set_clause,
141                                where_clause
142                            );
143                        }
144                        RelationType::Cascade => {
145                            let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
146                            sql_println!(
147                                writer,
148                                "   delete from {} where {};",
149                                child_table.fully_qualified_table_name(database_type),
150                                where_clause
151                            );
152                        }
153                        RelationType::DoNothing => {}
154                    }
155                }
156            }
157
158            for custom_trigger in table.triggers() {
159                if custom_trigger.trigger_type() == TriggerType::Delete
160                    && custom_trigger.database_type() == DatabaseType::Postgresql
161                {
162                    sql_println!(writer, "{}", custom_trigger.trigger_text());
163                }
164            }
165
166            sql_println!(writer, "   return null;");
167            sql_println!(writer, "end;");
168            sql_println!(writer, "$BODY$ language plpgsql{}", separator);
169            sql_println!(writer, "");
170
171            sql_println!(writer, "drop trigger if exists {} on {} cascade{}", fn_name, fully_qualified_table, separator);
172            sql_println!(
173                writer,
174                "create trigger {} after delete on {}",
175                fn_name,
176                fully_qualified_table
177            );
178            sql_println!(writer, "   for each row execute procedure {}(){}", fully_qualified_fn, separator);
179            sql_println!(writer, "");
180        });
181    }
182
183    fn output_update_trigger(&self, table: &Table, separator: &str) {
184        let database_type = self.context.settings().database_type();
185        let table_name = table.name().to_lowercase();
186        let fn_name = format!("{}_update", table_name);
187        let fully_qualified_table = table.fully_qualified_table_name(database_type);
188        let fully_qualified_fn = format!(
189            "{}.{}",
190            table
191                .schema_name()
192                .unwrap_or(DatabaseType::Postgresql.default_schema().unwrap()),
193            fn_name
194        );
195
196        self.context.with_writer(|writer| {
197            sql_println!(writer, "/* {} */", fully_qualified_fn);
198            sql_println!(
199                writer,
200                "create or replace function {}() returns trigger as $BODY$"
201            ,
202                fully_qualified_fn
203            );
204            sql_println!(writer, "begin");
205
206            if self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers {
207                for relation in table.relations() {
208                    match relation.relation_type() {
209                        RelationType::Enforce | RelationType::SetNull | RelationType::Cascade => {
210                            let to_table = self.database_model().find_table_by_qualified_name(relation.to_table_name());
211                            let not_null_clause = relation
212                                .column_pairs()
213                                .iter()
214                                .map(|(from_col, _)| format!("new.{} is not null", from_col))
215                                .collect::<Vec<_>>()
216                                .join(" and ");
217                            let new_match_clause = relation
218                                .column_pairs()
219                                .iter()
220                                .map(|(from_col, to_col)| format!("{} = new.{}", to_col, from_col))
221                                .collect::<Vec<_>>()
222                                .join(" and ");
223                            let column_list = relation
224                                .column_pairs()
225                                .iter()
226                                .map(|(from_col, _)| from_col.as_str())
227                                .collect::<Vec<_>>()
228                                .join(", ");
229                            sql_println!(writer, "   if {} then", not_null_clause);
230                            sql_println!(
231                                writer,
232                                "      if (select count(*) from {} where {}) = 0 then",
233                                to_table.fully_qualified_table_name(database_type),
234                                new_match_clause
235                            );
236                            sql_println!(
237                                writer,
238                                "         raise exception 'The value of {} was not found in the {} table.';",
239                                column_list,
240                                to_table.fully_qualified_table_name(database_type)
241                            );
242                            sql_println!(writer, "      end if;");
243                            sql_println!(writer, "   end if;");
244                        }
245                        RelationType::DoNothing => {}
246                    }
247                }
248            }
249
250            for custom_trigger in table.triggers() {
251                if custom_trigger.trigger_type() == TriggerType::Update
252                    && custom_trigger.database_type() == DatabaseType::Postgresql
253                {
254                    sql_println!(writer, "{}", custom_trigger.trigger_text());
255                }
256            }
257
258            sql_println!(writer, "   return new;");
259            sql_println!(writer, "end;");
260            sql_println!(writer, "$BODY$ language plpgsql{}", separator);
261            sql_println!(writer, "");
262
263            sql_println!(writer, "drop trigger if exists {} on {} cascade{}", fn_name, fully_qualified_table, separator);
264            sql_println!(
265                writer,
266                "create trigger {} after insert or update on {}",
267                fn_name,
268                fully_qualified_table
269            );
270            sql_println!(writer, "   for each row execute procedure {}(){}", fully_qualified_fn, separator);
271            sql_println!(writer, "");
272        });
273    }
274
275    fn database_model(&self) -> &schema_model::model::database_model::DatabaseModel {
276        self.context.settings().database_model()
277    }
278}
279
280#[cfg(test)]
281mod tests {
282    use super::*;
283    use crate::common::test_support::make_context_with_fk_mode;
284    use schema_model::builder::{ColumnBuilder, SchemaBuilder, TableBuilder};
285    use schema_model::model::column_type::ColumnType;
286    use schema_model::model::database_model::DatabaseModel;
287    use schema_model::model::relation::Relation;
288    use schema_model::model::types::{BooleanMode, DatabaseType, ForeignKeyMode, RelationType};
289
290    fn build_model_with_relation() -> DatabaseModel {
291        let parent = TableBuilder::new(Some("app"), "parent")
292            .add_column(
293                ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence)
294                    .required(true)
295                    .build(),
296            )
297            .build();
298        let child = TableBuilder::new(Some("app"), "child")
299            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
300            .add_relation(Relation::new(
301                "app.parent",
302                "id",
303                "child",
304                "parent_id",
305                RelationType::Enforce,
306                false,
307            ))
308            .build();
309        let schema = SchemaBuilder::new(Some("app"))
310            .add_table(parent)
311            .add_table(child)
312            .build();
313        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
314    }
315
316    fn build_model_with_unqualified_relation() -> DatabaseModel {
317        let parent = TableBuilder::new(None::<&str>, "parent")
318            .add_column(
319                ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence)
320                    .required(true)
321                    .build(),
322            )
323            .build();
324        let child = TableBuilder::new(None::<&str>, "child")
325            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
326            .add_relation(Relation::new(
327                "parent",
328                "id",
329                "child",
330                "parent_id",
331                RelationType::Enforce,
332                false,
333            ))
334            .build();
335        let schema = SchemaBuilder::new(None::<&str>)
336            .add_table(parent)
337            .add_table(child)
338            .build();
339        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
340    }
341
342    #[test]
343    fn output_triggers_renders_update_validation_trigger_for_relations_mode_triggers() {
344        let model = build_model_with_relation();
345        let (ctx, buffer) =
346            make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
347
348        let generator = PostgresTriggerGenerator::new(ctx);
349        generator.output_triggers();
350
351        let output = buffer.contents();
352        assert!(output.contains("create or replace function app.child_update() returns trigger"));
353        assert!(output.contains("was not found in the app.parent table"));
354        assert!(output.contains("create trigger child_update after insert or update on app.child"));
355    }
356
357    #[test]
358    fn output_triggers_resolves_unqualified_to_table_name_in_default_schema() {
359        let model = build_model_with_unqualified_relation();
360        let (ctx, buffer) =
361            make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
362
363        let generator = PostgresTriggerGenerator::new(ctx);
364        generator.output_triggers();
365
366        let output = buffer.contents();
367        assert!(output.contains("was not found in the public.parent table"));
368    }
369
370    #[test]
371    fn output_triggers_emits_explicit_delete_trigger_for_table_without_primary_key() {
372        use schema_model::model::trigger::Trigger;
373        let table = TableBuilder::new(None::<&str>, "log")
374            .add_column(ColumnBuilder::new(None::<&str>, "msg", ColumnType::Int).build())
375            .add_trigger(Trigger::new(
376                "   -- audit",
377                TriggerType::Delete,
378                DatabaseType::Postgresql,
379            ))
380            .build();
381        let schema = SchemaBuilder::new(None::<&str>).add_table(table).build();
382        let model =
383            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
384        let (ctx, buffer) =
385            make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Relations);
386
387        PostgresTriggerGenerator::new(ctx).output_triggers();
388
389        let output = buffer.contents();
390        assert!(
391            output.contains("create or replace function public.log_delete()"),
392            "{output}"
393        );
394        assert!(output.contains("-- audit"));
395    }
396
397    #[test]
398    fn output_triggers_does_nothing_when_relations_mode_is_not_triggers() {
399        let model = build_model_with_relation();
400        let (ctx, buffer) =
401            make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Relations);
402
403        let generator = PostgresTriggerGenerator::new(ctx);
404        generator.output_triggers();
405
406        assert_eq!(buffer.contents(), "");
407    }
408
409    /// Builds parent/child tables with both the forward relation (on `child`, used for the
410    /// update trigger) and the matching reverse relation attached to `parent` (used for the
411    /// delete trigger) - mirroring what `convert::reverse_relations()` does for a real parsed
412    /// schema, since these unit tests build the model directly via builders rather than XML.
413    fn build_model_with_reverse_relation(relation_type: RelationType) -> DatabaseModel {
414        let mut parent = TableBuilder::new(Some("app"), "parent")
415            .add_column(
416                ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence)
417                    .required(true)
418                    .build(),
419            )
420            .add_key(
421                schema_model::builder::KeyBuilder::new(
422                    schema_model::model::types::KeyType::Primary,
423                )
424                .add_column("id")
425                .build(),
426            )
427            .build();
428        parent.add_reverse_relation(Relation::new(
429            "app.parent",
430            "id",
431            "app.child",
432            "parent_id",
433            relation_type,
434            false,
435        ));
436
437        let child = TableBuilder::new(Some("app"), "child")
438            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
439            .add_relation(Relation::new(
440                "app.parent",
441                "id",
442                "child",
443                "parent_id",
444                relation_type,
445                false,
446            ))
447            .build();
448        let schema = SchemaBuilder::new(Some("app"))
449            .add_table(parent)
450            .add_table(child)
451            .build();
452        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
453    }
454
455    #[test]
456    fn output_delete_trigger_enforce_checks_the_child_table_not_itself() {
457        let model = build_model_with_reverse_relation(RelationType::Enforce);
458        let (ctx, buffer) =
459            make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
460
461        let generator = PostgresTriggerGenerator::new(ctx);
462        generator.output_triggers();
463
464        let output = buffer.contents();
465        assert!(output.contains("create or replace function app.parent_delete"));
466        assert!(
467            output.contains("if (select count(*) from app.child where parent_id = OLD.id) > 0")
468        );
469        assert!(
470            output.contains("cannot be deleted. It is being used by a row in the app.child table")
471        );
472    }
473
474    #[test]
475    fn output_delete_trigger_setnull_updates_the_child_table_not_itself() {
476        let model = build_model_with_reverse_relation(RelationType::SetNull);
477        let (ctx, buffer) =
478            make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
479
480        let generator = PostgresTriggerGenerator::new(ctx);
481        generator.output_triggers();
482
483        let output = buffer.contents();
484        assert!(output.contains("update app.child set parent_id = null where parent_id = OLD.id;"));
485    }
486
487    #[test]
488    fn output_delete_trigger_cascade_deletes_from_the_child_table_not_itself() {
489        let model = build_model_with_reverse_relation(RelationType::Cascade);
490        let (ctx, buffer) =
491            make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
492
493        let generator = PostgresTriggerGenerator::new(ctx);
494        generator.output_triggers();
495
496        let output = buffer.contents();
497        assert!(output.contains("delete from app.child where parent_id = OLD.id;"));
498    }
499
500    #[test]
501    fn output_delete_trigger_uses_the_relations_own_column_for_a_composite_primary_key() {
502        // The parent has a composite primary key (id, tenant_id); the relation specifically
503        // references `id`. The delete trigger must match on the relation's own referenced
504        // column, not an arbitrarily-picked primary key column.
505        let mut parent = TableBuilder::new(Some("app"), "parent")
506            .add_column(
507                ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence)
508                    .required(true)
509                    .build(),
510            )
511            .add_column(
512                ColumnBuilder::new(None::<&str>, "tenant_id", ColumnType::Int)
513                    .required(true)
514                    .build(),
515            )
516            .add_key(
517                schema_model::builder::KeyBuilder::new(
518                    schema_model::model::types::KeyType::Primary,
519                )
520                .add_column("id")
521                .add_column("tenant_id")
522                .build(),
523            )
524            .build();
525        parent.add_reverse_relation(Relation::new(
526            "app.parent",
527            "id",
528            "app.child",
529            "parent_id",
530            RelationType::Cascade,
531            false,
532        ));
533
534        let child = TableBuilder::new(Some("app"), "child")
535            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
536            .build();
537        let schema = SchemaBuilder::new(Some("app"))
538            .add_table(parent)
539            .add_table(child)
540            .build();
541        let model =
542            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
543        let (ctx, buffer) =
544            make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
545
546        let generator = PostgresTriggerGenerator::new(ctx);
547        generator.output_triggers();
548
549        let output = buffer.contents();
550        assert!(output.contains("delete from app.child where parent_id = OLD.id;"));
551    }
552
553    fn build_model_with_composite_reverse_relation(relation_type: RelationType) -> DatabaseModel {
554        let mut parent = TableBuilder::new(Some("app"), "parent")
555            .add_column(
556                ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence)
557                    .required(true)
558                    .build(),
559            )
560            .add_column(
561                ColumnBuilder::new(None::<&str>, "tenant_id", ColumnType::Int)
562                    .required(true)
563                    .build(),
564            )
565            .add_key(
566                schema_model::builder::KeyBuilder::new(
567                    schema_model::model::types::KeyType::Primary,
568                )
569                .add_column("id")
570                .build(),
571            )
572            .build();
573        parent.add_reverse_relation(
574            Relation::new_composite(
575                "app.parent",
576                "app.child",
577                vec![("child_id", "id"), ("child_tenant_id", "tenant_id")],
578                relation_type,
579                false,
580            )
581            .unwrap(),
582        );
583
584        let child = TableBuilder::new(Some("app"), "child")
585            .add_column(ColumnBuilder::new(None::<&str>, "child_id", ColumnType::Int).build())
586            .add_column(
587                ColumnBuilder::new(None::<&str>, "child_tenant_id", ColumnType::Int).build(),
588            )
589            .add_relation(
590                Relation::new_composite(
591                    "app.parent",
592                    "child",
593                    vec![("child_id", "id"), ("child_tenant_id", "tenant_id")],
594                    relation_type,
595                    false,
596                )
597                .unwrap(),
598            )
599            .build();
600        let schema = SchemaBuilder::new(Some("app"))
601            .add_table(parent)
602            .add_table(child)
603            .build();
604        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
605    }
606
607    #[test]
608    fn output_delete_trigger_enforce_composite_checks_all_column_pairs() {
609        let model = build_model_with_composite_reverse_relation(RelationType::Enforce);
610        let (ctx, buffer) =
611            make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
612
613        let generator = PostgresTriggerGenerator::new(ctx);
614        generator.output_triggers();
615
616        let output = buffer.contents();
617        assert!(output.contains(
618            "if (select count(*) from app.child where child_id = OLD.id and child_tenant_id = OLD.tenant_id) > 0 then"
619        ));
620    }
621
622    #[test]
623    fn output_delete_trigger_setnull_composite_nulls_all_from_columns() {
624        let model = build_model_with_composite_reverse_relation(RelationType::SetNull);
625        let (ctx, buffer) =
626            make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
627
628        let generator = PostgresTriggerGenerator::new(ctx);
629        generator.output_triggers();
630
631        let output = buffer.contents();
632        assert!(output.contains(
633            "update app.child set child_id = null, child_tenant_id = null where child_id = OLD.id and child_tenant_id = OLD.tenant_id;"
634        ));
635    }
636
637    #[test]
638    fn output_delete_trigger_cascade_composite_matches_all_column_pairs() {
639        let model = build_model_with_composite_reverse_relation(RelationType::Cascade);
640        let (ctx, buffer) =
641            make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
642
643        let generator = PostgresTriggerGenerator::new(ctx);
644        generator.output_triggers();
645
646        let output = buffer.contents();
647        assert!(output.contains(
648            "delete from app.child where child_id = OLD.id and child_tenant_id = OLD.tenant_id;"
649        ));
650    }
651
652    #[test]
653    fn output_update_trigger_composite_checks_all_columns_not_null_then_matches_all_pairs() {
654        let model = build_model_with_composite_reverse_relation(RelationType::Enforce);
655        let (ctx, buffer) =
656            make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
657
658        let generator = PostgresTriggerGenerator::new(ctx);
659        generator.output_triggers();
660
661        let output = buffer.contents();
662        assert!(
663            output.contains("if new.child_id is not null and new.child_tenant_id is not null then")
664        );
665        assert!(output.contains(
666            "if (select count(*) from app.parent where id = new.child_id and tenant_id = new.child_tenant_id) = 0 then"
667        ));
668        assert!(output.contains("was not found in the app.parent table"));
669        assert!(output.contains("child_id, child_tenant_id"));
670    }
671}