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 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 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 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}