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