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