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}
55
56impl TriggerGenerator for PostgresTriggerGenerator {
57    fn output_triggers(&self) {
58        let database_model = self.context.settings().database_model();
59
60        for table in database_model.all_tables() {
61            self.output_triggers_for_table(table);
62        }
63    }
64
65    fn output_triggers_for_table(&self, table: &Table) {
66        let separator = self.context.settings().statement_separator();
67
68        if self.should_output_delete_trigger(table) && table.primary_key().is_some() {
69            self.output_delete_trigger(table, separator);
70        }
71
72        if self.should_output_update_trigger(table) {
73            self.output_update_trigger(table, separator);
74        }
75    }
76}
77
78impl PostgresTriggerGenerator {
79    fn output_delete_trigger(&self, table: &Table, separator: &str) {
80        let database_type = self.context.settings().database_type();
81        let table_name = table.name().to_lowercase();
82        let fn_name = format!("{}_delete", table_name);
83        let fully_qualified_table = table.fully_qualified_table_name(database_type);
84        let fully_qualified_fn = format!(
85            "{}.{}",
86            table.schema_name().unwrap_or(DatabaseType::Postgresql.default_schema().unwrap()),
87            fn_name
88        );
89
90        self.context.with_writer(|writer| {
91            sql_println!(writer, "/* {} */", fully_qualified_fn);
92            sql_println!(
93                writer,
94                "create or replace function {}() returns trigger as $BODY$"
95            ,
96                fully_qualified_fn
97            );
98            sql_println!(writer, "begin");
99
100            if self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers {
101                // A reverse relation carries the *original* relation's fields unchanged
102                // (attached to the parent for convenient lookup): `from_table_name`/
103                // `from_column_name` identify the child table and its FK column, while
104                // `to_table_name`/`to_column_name` still refer to this table (the parent)
105                // itself. The child table must be resolved via `from_table_name`, not
106                // `to_table_name` - using `to_table_name` here would resolve back to this
107                // same table and generate a trigger that deletes/updates/checks itself.
108                for relation in table.reverse_relations() {
109                    let where_clause = old_match_where_clause(relation);
110                    match relation.relation_type() {
111                        RelationType::Enforce => {
112                            let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
113                            sql_println!(
114                                writer,
115                                "   if (select count(*) from {} where {}) > 0 then",
116                                child_table.fully_qualified_table_name(database_type),
117                                where_clause
118                            );
119                            sql_println!(
120                                writer,
121                                "      raise exception 'The row in {} cannot be deleted. It is being used by a row in the {} table.';",
122                                fully_qualified_table,
123                                child_table.fully_qualified_table_name(database_type)
124                            );
125                            sql_println!(writer, "   end if;");
126                        }
127                        RelationType::SetNull => {
128                            let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
129                            let set_clause = relation
130                                .column_pairs()
131                                .iter()
132                                .map(|(from_col, _)| format!("{} = null", from_col))
133                                .collect::<Vec<_>>()
134                                .join(", ");
135                            sql_println!(
136                                writer,
137                                "   update {} set {} where {};",
138                                child_table.fully_qualified_table_name(database_type),
139                                set_clause,
140                                where_clause
141                            );
142                        }
143                        RelationType::Cascade => {
144                            let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
145                            sql_println!(
146                                writer,
147                                "   delete from {} where {};",
148                                child_table.fully_qualified_table_name(database_type),
149                                where_clause
150                            );
151                        }
152                        RelationType::DoNothing => {}
153                    }
154                }
155            }
156
157            for custom_trigger in table.triggers() {
158                if custom_trigger.trigger_type() == TriggerType::Delete
159                    && custom_trigger.database_type() == DatabaseType::Postgresql
160                {
161                    sql_println!(writer, "{}", custom_trigger.trigger_text());
162                }
163            }
164
165            sql_println!(writer, "   return null;");
166            sql_println!(writer, "end;");
167            sql_println!(writer, "$BODY$ language plpgsql{}", separator);
168            sql_println!(writer, "");
169
170            sql_println!(writer, "drop trigger if exists {} on {} cascade{}", fn_name, fully_qualified_table, separator);
171            sql_println!(
172                writer,
173                "create trigger {} after delete on {}",
174                fn_name,
175                fully_qualified_table
176            );
177            sql_println!(writer, "   for each row execute procedure {}(){}", fully_qualified_fn, separator);
178            sql_println!(writer, "");
179        });
180    }
181
182    fn output_update_trigger(&self, table: &Table, separator: &str) {
183        let database_type = self.context.settings().database_type();
184        let table_name = table.name().to_lowercase();
185        let fn_name = format!("{}_update", table_name);
186        let fully_qualified_table = table.fully_qualified_table_name(database_type);
187        let fully_qualified_fn = format!(
188            "{}.{}",
189            table.schema_name().unwrap_or(DatabaseType::Postgresql.default_schema().unwrap()),
190            fn_name
191        );
192
193        self.context.with_writer(|writer| {
194            sql_println!(writer, "/* {} */", fully_qualified_fn);
195            sql_println!(
196                writer,
197                "create or replace function {}() returns trigger as $BODY$"
198            ,
199                fully_qualified_fn
200            );
201            sql_println!(writer, "begin");
202
203            if self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers {
204                for relation in table.relations() {
205                    match relation.relation_type() {
206                        RelationType::Enforce | RelationType::SetNull | RelationType::Cascade => {
207                            let to_table = self.database_model().find_table_by_qualified_name(relation.to_table_name());
208                            let not_null_clause = relation
209                                .column_pairs()
210                                .iter()
211                                .map(|(from_col, _)| format!("new.{} is not null", from_col))
212                                .collect::<Vec<_>>()
213                                .join(" and ");
214                            let new_match_clause = relation
215                                .column_pairs()
216                                .iter()
217                                .map(|(from_col, to_col)| format!("{} = new.{}", to_col, from_col))
218                                .collect::<Vec<_>>()
219                                .join(" and ");
220                            let column_list = relation
221                                .column_pairs()
222                                .iter()
223                                .map(|(from_col, _)| from_col.as_str())
224                                .collect::<Vec<_>>()
225                                .join(", ");
226                            sql_println!(writer, "   if {} then", not_null_clause);
227                            sql_println!(
228                                writer,
229                                "      if (select count(*) from {} where {}) = 0 then",
230                                to_table.fully_qualified_table_name(database_type),
231                                new_match_clause
232                            );
233                            sql_println!(
234                                writer,
235                                "         raise exception 'The value of {} was not found in the {} table.';",
236                                column_list,
237                                to_table.fully_qualified_table_name(database_type)
238                            );
239                            sql_println!(writer, "      end if;");
240                            sql_println!(writer, "   end if;");
241                        }
242                        RelationType::DoNothing => {}
243                    }
244                }
245            }
246
247            for custom_trigger in table.triggers() {
248                if custom_trigger.trigger_type() == TriggerType::Update
249                    && custom_trigger.database_type() == DatabaseType::Postgresql
250                {
251                    sql_println!(writer, "{}", custom_trigger.trigger_text());
252                }
253            }
254
255            sql_println!(writer, "   return new;");
256            sql_println!(writer, "end;");
257            sql_println!(writer, "$BODY$ language plpgsql{}", separator);
258            sql_println!(writer, "");
259
260            sql_println!(writer, "drop trigger if exists {} on {} cascade{}", fn_name, fully_qualified_table, separator);
261            sql_println!(
262                writer,
263                "create trigger {} after insert or update on {}",
264                fn_name,
265                fully_qualified_table
266            );
267            sql_println!(writer, "   for each row execute procedure {}(){}", fully_qualified_fn, separator);
268            sql_println!(writer, "");
269        });
270    }
271
272    fn database_model(&self) -> &schema_model::model::database_model::DatabaseModel {
273        self.context.settings().database_model()
274    }
275}
276
277#[cfg(test)]
278mod tests {
279    use super::*;
280    use crate::common::test_support::make_context_with_fk_mode;
281    use schema_model::builder::{ColumnBuilder, SchemaBuilder, TableBuilder};
282    use schema_model::model::column_type::ColumnType;
283    use schema_model::model::database_model::DatabaseModel;
284    use schema_model::model::relation::Relation;
285    use schema_model::model::types::{BooleanMode, DatabaseType, ForeignKeyMode, RelationType};
286
287    fn build_model_with_relation() -> DatabaseModel {
288        let parent = TableBuilder::new(Some("app"), "parent")
289            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
290            .build();
291        let child = TableBuilder::new(Some("app"), "child")
292            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
293            .add_relation(Relation::new("app.parent", "id", "child", "parent_id", RelationType::Enforce, false))
294            .build();
295        let schema = SchemaBuilder::new(Some("app"))
296            .add_table(parent)
297            .add_table(child)
298            .build();
299        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
300    }
301
302    fn build_model_with_unqualified_relation() -> DatabaseModel {
303        let parent = TableBuilder::new(None::<&str>, "parent")
304            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
305            .build();
306        let child = TableBuilder::new(None::<&str>, "child")
307            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
308            .add_relation(Relation::new("parent", "id", "child", "parent_id", RelationType::Enforce, false))
309            .build();
310        let schema = SchemaBuilder::new(None::<&str>)
311            .add_table(parent)
312            .add_table(child)
313            .build();
314        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
315    }
316
317    #[test]
318    fn output_triggers_renders_update_validation_trigger_for_relations_mode_triggers() {
319        let model = build_model_with_relation();
320        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
321
322        let generator = PostgresTriggerGenerator::new(ctx);
323        generator.output_triggers();
324
325        let output = buffer.contents();
326        assert!(output.contains("create or replace function app.child_update() returns trigger"));
327        assert!(output.contains("was not found in the app.parent table"));
328        assert!(output.contains("create trigger child_update after insert or update on app.child"));
329    }
330
331    #[test]
332    fn output_triggers_resolves_unqualified_to_table_name_in_default_schema() {
333        let model = build_model_with_unqualified_relation();
334        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
335
336        let generator = PostgresTriggerGenerator::new(ctx);
337        generator.output_triggers();
338
339        let output = buffer.contents();
340        assert!(output.contains("was not found in the public.parent table"));
341    }
342
343    #[test]
344    fn output_triggers_does_nothing_when_relations_mode_is_not_triggers() {
345        let model = build_model_with_relation();
346        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Relations);
347
348        let generator = PostgresTriggerGenerator::new(ctx);
349        generator.output_triggers();
350
351        assert_eq!(buffer.contents(), "");
352    }
353
354    /// Builds parent/child tables with both the forward relation (on `child`, used for the
355    /// update trigger) and the matching reverse relation attached to `parent` (used for the
356    /// delete trigger) - mirroring what `convert::reverse_relations()` does for a real parsed
357    /// schema, since these unit tests build the model directly via builders rather than XML.
358    fn build_model_with_reverse_relation(relation_type: RelationType) -> DatabaseModel {
359        let mut parent = TableBuilder::new(Some("app"), "parent")
360            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
361            .add_key(schema_model::builder::KeyBuilder::new(schema_model::model::types::KeyType::Primary).add_column("id").build())
362            .build();
363        parent.add_reverse_relation(Relation::new("app.parent", "id", "app.child", "parent_id", relation_type, false));
364
365        let child = TableBuilder::new(Some("app"), "child")
366            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
367            .add_relation(Relation::new("app.parent", "id", "child", "parent_id", relation_type, false))
368            .build();
369        let schema = SchemaBuilder::new(Some("app"))
370            .add_table(parent)
371            .add_table(child)
372            .build();
373        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
374    }
375
376    #[test]
377    fn output_delete_trigger_enforce_checks_the_child_table_not_itself() {
378        let model = build_model_with_reverse_relation(RelationType::Enforce);
379        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
380
381        let generator = PostgresTriggerGenerator::new(ctx);
382        generator.output_triggers();
383
384        let output = buffer.contents();
385        assert!(output.contains("create or replace function app.parent_delete"));
386        assert!(output.contains("if (select count(*) from app.child where parent_id = OLD.id) > 0"));
387        assert!(output.contains("cannot be deleted. It is being used by a row in the app.child table"));
388    }
389
390    #[test]
391    fn output_delete_trigger_setnull_updates_the_child_table_not_itself() {
392        let model = build_model_with_reverse_relation(RelationType::SetNull);
393        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
394
395        let generator = PostgresTriggerGenerator::new(ctx);
396        generator.output_triggers();
397
398        let output = buffer.contents();
399        assert!(output.contains("update app.child set parent_id = null where parent_id = OLD.id;"));
400    }
401
402    #[test]
403    fn output_delete_trigger_cascade_deletes_from_the_child_table_not_itself() {
404        let model = build_model_with_reverse_relation(RelationType::Cascade);
405        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
406
407        let generator = PostgresTriggerGenerator::new(ctx);
408        generator.output_triggers();
409
410        let output = buffer.contents();
411        assert!(output.contains("delete from app.child where parent_id = OLD.id;"));
412    }
413
414    #[test]
415    fn output_delete_trigger_uses_the_relations_own_column_for_a_composite_primary_key() {
416        // The parent has a composite primary key (id, tenant_id); the relation specifically
417        // references `id`. The delete trigger must match on the relation's own referenced
418        // column, not an arbitrarily-picked primary key column.
419        let mut parent = TableBuilder::new(Some("app"), "parent")
420            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
421            .add_column(ColumnBuilder::new(None::<&str>, "tenant_id", ColumnType::Int).required(true).build())
422            .add_key(
423                schema_model::builder::KeyBuilder::new(schema_model::model::types::KeyType::Primary)
424                    .add_column("id")
425                    .add_column("tenant_id")
426                    .build(),
427            )
428            .build();
429        parent.add_reverse_relation(Relation::new("app.parent", "id", "app.child", "parent_id", RelationType::Cascade, false));
430
431        let child = TableBuilder::new(Some("app"), "child")
432            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
433            .build();
434        let schema = SchemaBuilder::new(Some("app"))
435            .add_table(parent)
436            .add_table(child)
437            .build();
438        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
439        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
440
441        let generator = PostgresTriggerGenerator::new(ctx);
442        generator.output_triggers();
443
444        let output = buffer.contents();
445        assert!(output.contains("delete from app.child where parent_id = OLD.id;"));
446    }
447
448    fn build_model_with_composite_reverse_relation(relation_type: RelationType) -> DatabaseModel {
449        let mut parent = TableBuilder::new(Some("app"), "parent")
450            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
451            .add_column(ColumnBuilder::new(None::<&str>, "tenant_id", ColumnType::Int).required(true).build())
452            .add_key(schema_model::builder::KeyBuilder::new(schema_model::model::types::KeyType::Primary).add_column("id").build())
453            .build();
454        parent.add_reverse_relation(
455            Relation::new_composite(
456                "app.parent",
457                "app.child",
458                vec![("child_id", "id"), ("child_tenant_id", "tenant_id")],
459                relation_type,
460                false,
461            )
462            .unwrap(),
463        );
464
465        let child = TableBuilder::new(Some("app"), "child")
466            .add_column(ColumnBuilder::new(None::<&str>, "child_id", ColumnType::Int).build())
467            .add_column(ColumnBuilder::new(None::<&str>, "child_tenant_id", ColumnType::Int).build())
468            .add_relation(
469                Relation::new_composite(
470                    "app.parent",
471                    "child",
472                    vec![("child_id", "id"), ("child_tenant_id", "tenant_id")],
473                    relation_type,
474                    false,
475                )
476                .unwrap(),
477            )
478            .build();
479        let schema = SchemaBuilder::new(Some("app"))
480            .add_table(parent)
481            .add_table(child)
482            .build();
483        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
484    }
485
486    #[test]
487    fn output_delete_trigger_enforce_composite_checks_all_column_pairs() {
488        let model = build_model_with_composite_reverse_relation(RelationType::Enforce);
489        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
490
491        let generator = PostgresTriggerGenerator::new(ctx);
492        generator.output_triggers();
493
494        let output = buffer.contents();
495        assert!(output.contains(
496            "if (select count(*) from app.child where child_id = OLD.id and child_tenant_id = OLD.tenant_id) > 0 then"
497        ));
498    }
499
500    #[test]
501    fn output_delete_trigger_setnull_composite_nulls_all_from_columns() {
502        let model = build_model_with_composite_reverse_relation(RelationType::SetNull);
503        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
504
505        let generator = PostgresTriggerGenerator::new(ctx);
506        generator.output_triggers();
507
508        let output = buffer.contents();
509        assert!(output.contains(
510            "update app.child set child_id = null, child_tenant_id = null where child_id = OLD.id and child_tenant_id = OLD.tenant_id;"
511        ));
512    }
513
514    #[test]
515    fn output_delete_trigger_cascade_composite_matches_all_column_pairs() {
516        let model = build_model_with_composite_reverse_relation(RelationType::Cascade);
517        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
518
519        let generator = PostgresTriggerGenerator::new(ctx);
520        generator.output_triggers();
521
522        let output = buffer.contents();
523        assert!(output.contains(
524            "delete from app.child where child_id = OLD.id and child_tenant_id = OLD.tenant_id;"
525        ));
526    }
527
528    #[test]
529    fn output_update_trigger_composite_checks_all_columns_not_null_then_matches_all_pairs() {
530        let model = build_model_with_composite_reverse_relation(RelationType::Enforce);
531        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
532
533        let generator = PostgresTriggerGenerator::new(ctx);
534        generator.output_triggers();
535
536        let output = buffer.contents();
537        assert!(output.contains("if new.child_id is not null and new.child_tenant_id is not null then"));
538        assert!(output.contains(
539            "if (select count(*) from app.parent where id = new.child_id and tenant_id = new.child_tenant_id) = 0 then"
540        ));
541        assert!(output.contains("was not found in the app.parent table"));
542        assert!(output.contains("child_id, child_tenant_id"));
543    }
544}
545
546