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