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::table::Table;
5use schema_model::model::types::{DatabaseType, ForeignKeyMode, RelationType, TriggerType};
6
7pub struct PostgresTriggerGenerator {
8    context: GeneratorContext,
9}
10
11impl PostgresTriggerGenerator {
12    pub fn new(context: GeneratorContext) -> Self {
13        Self { context }
14    }
15
16    fn should_output_delete_trigger(&self, table: &Table) -> bool {
17        let has_delete_triggers = table
18            .triggers()
19            .iter()
20            .any(|t| t.trigger_type() == TriggerType::Delete);
21
22        let has_reverse_relations_with_triggers = !table.reverse_relations().is_empty()
23            && self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers;
24
25        let has_aggregations = !table.aggregations().is_empty();
26
27        has_delete_triggers || has_reverse_relations_with_triggers || has_aggregations
28    }
29
30    fn should_output_update_trigger(&self, table: &Table) -> bool {
31        let has_update_triggers = table
32            .triggers()
33            .iter()
34            .any(|t| t.trigger_type() == TriggerType::Update);
35
36        let has_relations_with_triggers = !table.relations().is_empty()
37            && self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers;
38
39        let has_aggregations = !table.aggregations().is_empty();
40
41        has_update_triggers || has_relations_with_triggers || has_aggregations
42    }
43
44}
45
46impl TriggerGenerator for PostgresTriggerGenerator {
47    fn output_triggers(&self) {
48        let database_model = self.context.settings().database_model();
49
50        for table in database_model.all_tables() {
51            self.output_triggers_for_table(table);
52        }
53    }
54
55    fn output_triggers_for_table(&self, table: &Table) {
56        let separator = self.context.settings().statement_separator();
57
58        if self.should_output_delete_trigger(table) && table.primary_key().is_some() {
59            self.output_delete_trigger(table, separator);
60        }
61
62        if self.should_output_update_trigger(table) {
63            self.output_update_trigger(table, separator);
64        }
65    }
66}
67
68impl PostgresTriggerGenerator {
69    fn output_delete_trigger(&self, table: &Table, separator: &str) {
70        let database_type = self.context.settings().database_type();
71        let table_name = table.name().to_lowercase();
72        let fn_name = format!("{}_delete", table_name);
73        let fully_qualified_table = table.fully_qualified_table_name(database_type);
74        let fully_qualified_fn = format!(
75            "{}.{}",
76            table.schema_name().unwrap_or(DatabaseType::Postgresql.default_schema().unwrap()),
77            fn_name
78        );
79
80        self.context.with_writer(|writer| {
81            sql_println!(writer, "/* {} */", fully_qualified_fn);
82            sql_println!(
83                writer,
84                "create or replace function {}() returns trigger as $BODY$"
85            ,
86                fully_qualified_fn
87            );
88            sql_println!(writer, "begin");
89
90            if self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers {
91                // A reverse relation carries the *original* relation's fields unchanged
92                // (attached to the parent for convenient lookup): `from_table_name`/
93                // `from_column_name` identify the child table and its FK column, while
94                // `to_table_name`/`to_column_name` still refer to this table (the parent)
95                // itself. The child table must be resolved via `from_table_name`, not
96                // `to_table_name` - using `to_table_name` here would resolve back to this
97                // same table and generate a trigger that deletes/updates/checks itself.
98                for relation in table.reverse_relations() {
99                    match relation.relation_type() {
100                        RelationType::Enforce => {
101                            let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
102                            sql_println!(
103                                writer,
104                                "   if (select count(*) from {} where {} = OLD.{}) > 0 then",
105                                child_table.fully_qualified_table_name(database_type),
106                                relation.from_column_name(),
107                                relation.to_column_name()
108                            );
109                            sql_println!(
110                                writer,
111                                "      raise exception 'The row in {} cannot be deleted. It is being used by a row in the {} table.';",
112                                fully_qualified_table,
113                                child_table.fully_qualified_table_name(database_type)
114                            );
115                            sql_println!(writer, "   end if;");
116                        }
117                        RelationType::SetNull => {
118                            let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
119                            sql_println!(
120                                writer,
121                                "   update {} set {} = null where {} = OLD.{};",
122                                child_table.fully_qualified_table_name(database_type),
123                                relation.from_column_name(),
124                                relation.from_column_name(),
125                                relation.to_column_name()
126                            );
127                        }
128                        RelationType::Cascade => {
129                            let child_table = self.database_model().find_table_by_qualified_name(relation.from_table_name());
130                            sql_println!(
131                                writer,
132                                "   delete from {} where {} = OLD.{};",
133                                child_table.fully_qualified_table_name(database_type),
134                                relation.from_column_name(),
135                                relation.to_column_name()
136                            );
137                        }
138                        RelationType::DoNothing => {}
139                    }
140                }
141            }
142
143            for custom_trigger in table.triggers() {
144                if custom_trigger.trigger_type() == TriggerType::Delete
145                    && custom_trigger.database_type() == DatabaseType::Postgresql
146                {
147                    sql_println!(writer, "{}", custom_trigger.trigger_text());
148                }
149            }
150
151            sql_println!(writer, "   return null;");
152            sql_println!(writer, "end;");
153            sql_println!(writer, "$BODY$ language plpgsql{}", separator);
154            sql_println!(writer, "");
155
156            sql_println!(writer, "drop trigger if exists {} on {} cascade{}", fn_name, fully_qualified_table, separator);
157            sql_println!(
158                writer,
159                "create trigger {} after delete on {}",
160                fn_name,
161                fully_qualified_table
162            );
163            sql_println!(writer, "   for each row execute procedure {}(){}", fully_qualified_fn, separator);
164            sql_println!(writer, "");
165        });
166    }
167
168    fn output_update_trigger(&self, table: &Table, separator: &str) {
169        let database_type = self.context.settings().database_type();
170        let table_name = table.name().to_lowercase();
171        let fn_name = format!("{}_update", table_name);
172        let fully_qualified_table = table.fully_qualified_table_name(database_type);
173        let fully_qualified_fn = format!(
174            "{}.{}",
175            table.schema_name().unwrap_or(DatabaseType::Postgresql.default_schema().unwrap()),
176            fn_name
177        );
178
179        self.context.with_writer(|writer| {
180            sql_println!(writer, "/* {} */", fully_qualified_fn);
181            sql_println!(
182                writer,
183                "create or replace function {}() returns trigger as $BODY$"
184            ,
185                fully_qualified_fn
186            );
187            sql_println!(writer, "begin");
188
189            if self.context.settings().foreign_key_mode() == ForeignKeyMode::Triggers {
190                for relation in table.relations() {
191                    match relation.relation_type() {
192                        RelationType::Enforce | RelationType::SetNull | RelationType::Cascade => {
193                            let to_table = self.database_model().find_table_by_qualified_name(relation.to_table_name());
194                            sql_println!(
195                                writer,
196                                "   if new.{} is not null then",
197                                relation.from_column_name()
198                            );
199                            sql_println!(
200                                writer,
201                                "      if (select count(*) from {} where {} = new.{}) = 0 then",
202                                to_table.fully_qualified_table_name(database_type),
203                                relation.to_column_name(),
204                                relation.from_column_name()
205                            );
206                            sql_println!(
207                                writer,
208                                "         raise exception 'The value of {} was not found in the {} table.';",
209                                relation.from_column_name(),
210                                to_table.fully_qualified_table_name(database_type)
211                            );
212                            sql_println!(writer, "      end if;");
213                            sql_println!(writer, "   end if;");
214                        }
215                        RelationType::DoNothing => {}
216                    }
217                }
218            }
219
220            for custom_trigger in table.triggers() {
221                if custom_trigger.trigger_type() == TriggerType::Update
222                    && custom_trigger.database_type() == DatabaseType::Postgresql
223                {
224                    sql_println!(writer, "{}", custom_trigger.trigger_text());
225                }
226            }
227
228            sql_println!(writer, "   return new;");
229            sql_println!(writer, "end;");
230            sql_println!(writer, "$BODY$ language plpgsql{}", separator);
231            sql_println!(writer, "");
232
233            sql_println!(writer, "drop trigger if exists {} on {} cascade{}", fn_name, fully_qualified_table, separator);
234            sql_println!(
235                writer,
236                "create trigger {} after insert or update on {}",
237                fn_name,
238                fully_qualified_table
239            );
240            sql_println!(writer, "   for each row execute procedure {}(){}", fully_qualified_fn, separator);
241            sql_println!(writer, "");
242        });
243    }
244
245    fn database_model(&self) -> &schema_model::model::database_model::DatabaseModel {
246        self.context.settings().database_model()
247    }
248}
249
250#[cfg(test)]
251mod tests {
252    use super::*;
253    use crate::common::test_support::make_context_with_fk_mode;
254    use schema_model::builder::{ColumnBuilder, SchemaBuilder, TableBuilder};
255    use schema_model::model::column_type::ColumnType;
256    use schema_model::model::database_model::DatabaseModel;
257    use schema_model::model::relation::Relation;
258    use schema_model::model::types::{BooleanMode, DatabaseType, ForeignKeyMode, RelationType};
259
260    fn build_model_with_relation() -> DatabaseModel {
261        let parent = TableBuilder::new(Some("app"), "parent")
262            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
263            .build();
264        let child = TableBuilder::new(Some("app"), "child")
265            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
266            .add_relation(Relation::new("app.parent", "id", "child", "parent_id", RelationType::Enforce, false))
267            .build();
268        let schema = SchemaBuilder::new(Some("app"))
269            .add_table(parent)
270            .add_table(child)
271            .build();
272        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
273    }
274
275    fn build_model_with_unqualified_relation() -> DatabaseModel {
276        let parent = TableBuilder::new(None::<&str>, "parent")
277            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
278            .build();
279        let child = TableBuilder::new(None::<&str>, "child")
280            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
281            .add_relation(Relation::new("parent", "id", "child", "parent_id", RelationType::Enforce, false))
282            .build();
283        let schema = SchemaBuilder::new(None::<&str>)
284            .add_table(parent)
285            .add_table(child)
286            .build();
287        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
288    }
289
290    #[test]
291    fn output_triggers_renders_update_validation_trigger_for_relations_mode_triggers() {
292        let model = build_model_with_relation();
293        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
294
295        let generator = PostgresTriggerGenerator::new(ctx);
296        generator.output_triggers();
297
298        let output = buffer.contents();
299        assert!(output.contains("create or replace function app.child_update() returns trigger"));
300        assert!(output.contains("was not found in the app.parent table"));
301        assert!(output.contains("create trigger child_update after insert or update on app.child"));
302    }
303
304    #[test]
305    fn output_triggers_resolves_unqualified_to_table_name_in_default_schema() {
306        let model = build_model_with_unqualified_relation();
307        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
308
309        let generator = PostgresTriggerGenerator::new(ctx);
310        generator.output_triggers();
311
312        let output = buffer.contents();
313        assert!(output.contains("was not found in the public.parent table"));
314    }
315
316    #[test]
317    fn output_triggers_does_nothing_when_relations_mode_is_not_triggers() {
318        let model = build_model_with_relation();
319        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Relations);
320
321        let generator = PostgresTriggerGenerator::new(ctx);
322        generator.output_triggers();
323
324        assert_eq!(buffer.contents(), "");
325    }
326
327    /// Builds parent/child tables with both the forward relation (on `child`, used for the
328    /// update trigger) and the matching reverse relation attached to `parent` (used for the
329    /// delete trigger) - mirroring what `convert::reverse_relations()` does for a real parsed
330    /// schema, since these unit tests build the model directly via builders rather than XML.
331    fn build_model_with_reverse_relation(relation_type: RelationType) -> DatabaseModel {
332        let mut parent = TableBuilder::new(Some("app"), "parent")
333            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
334            .add_key(schema_model::builder::KeyBuilder::new(schema_model::model::types::KeyType::Primary).add_column("id").build())
335            .build();
336        parent.add_reverse_relation(Relation::new("app.parent", "id", "app.child", "parent_id", relation_type, false));
337
338        let child = TableBuilder::new(Some("app"), "child")
339            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
340            .add_relation(Relation::new("app.parent", "id", "child", "parent_id", relation_type, false))
341            .build();
342        let schema = SchemaBuilder::new(Some("app"))
343            .add_table(parent)
344            .add_table(child)
345            .build();
346        DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema])
347    }
348
349    #[test]
350    fn output_delete_trigger_enforce_checks_the_child_table_not_itself() {
351        let model = build_model_with_reverse_relation(RelationType::Enforce);
352        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
353
354        let generator = PostgresTriggerGenerator::new(ctx);
355        generator.output_triggers();
356
357        let output = buffer.contents();
358        assert!(output.contains("create or replace function app.parent_delete"));
359        assert!(output.contains("if (select count(*) from app.child where parent_id = OLD.id) > 0"));
360        assert!(output.contains("cannot be deleted. It is being used by a row in the app.child table"));
361    }
362
363    #[test]
364    fn output_delete_trigger_setnull_updates_the_child_table_not_itself() {
365        let model = build_model_with_reverse_relation(RelationType::SetNull);
366        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
367
368        let generator = PostgresTriggerGenerator::new(ctx);
369        generator.output_triggers();
370
371        let output = buffer.contents();
372        assert!(output.contains("update app.child set parent_id = null where parent_id = OLD.id;"));
373    }
374
375    #[test]
376    fn output_delete_trigger_cascade_deletes_from_the_child_table_not_itself() {
377        let model = build_model_with_reverse_relation(RelationType::Cascade);
378        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
379
380        let generator = PostgresTriggerGenerator::new(ctx);
381        generator.output_triggers();
382
383        let output = buffer.contents();
384        assert!(output.contains("delete from app.child where parent_id = OLD.id;"));
385    }
386
387    #[test]
388    fn output_delete_trigger_uses_the_relations_own_column_for_a_composite_primary_key() {
389        // The parent has a composite primary key (id, tenant_id); the relation specifically
390        // references `id`. The delete trigger must match on the relation's own referenced
391        // column, not an arbitrarily-picked primary key column.
392        let mut parent = TableBuilder::new(Some("app"), "parent")
393            .add_column(ColumnBuilder::new(None::<&str>, "id", ColumnType::Sequence).required(true).build())
394            .add_column(ColumnBuilder::new(None::<&str>, "tenant_id", ColumnType::Int).required(true).build())
395            .add_key(
396                schema_model::builder::KeyBuilder::new(schema_model::model::types::KeyType::Primary)
397                    .add_column("id")
398                    .add_column("tenant_id")
399                    .build(),
400            )
401            .build();
402        parent.add_reverse_relation(Relation::new("app.parent", "id", "app.child", "parent_id", RelationType::Cascade, false));
403
404        let child = TableBuilder::new(Some("app"), "child")
405            .add_column(ColumnBuilder::new(None::<&str>, "parent_id", ColumnType::Int).build())
406            .build();
407        let schema = SchemaBuilder::new(Some("app"))
408            .add_table(parent)
409            .add_table(child)
410            .build();
411        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
412        let (ctx, buffer) = make_context_with_fk_mode(model, DatabaseType::Postgresql, ForeignKeyMode::Triggers);
413
414        let generator = PostgresTriggerGenerator::new(ctx);
415        generator.output_triggers();
416
417        let output = buffer.contents();
418        assert!(output.contains("delete from app.child where parent_id = OLD.id;"));
419    }
420}
421
422