1 -- @tag: requirement_spec_delete_trigger_fix
2 -- @description: Fixes für Delete-Trigger bei Pflichtenheften
3 -- @depends: requirement_spec_items_update_trigger_fix
5 -- Trigger for updating time_estimation of function blocks from their
6 -- children (not for sections, not for sub function blocks).
7 CREATE OR REPLACE FUNCTION update_requirement_spec_item_time_estimation(item_id INTEGER, requirement_spec_id INTEGER) RETURNS BOOLEAN AS $$
11 IF item_id IS NULL THEN
12 RAISE DEBUG 'updateRSIE: item_id IS NULL';
18 FROM trigger_information
19 WHERE ((key = 'deleting_requirement_spec_item') AND (value = CAST(item_id AS TEXT)))
20 OR ((key = 'deleting_requirement_spec') AND (value = CAST(requirement_spec_id AS TEXT)))
23 RAISE DEBUG 'updateRSIE: item_id % or requirement_spec_id % is about to be deleted; do not update', item_id, requirement_spec_id;
27 SELECT * INTO item FROM requirement_spec_items WHERE id = item_id;
28 RAISE DEBUG 'updateRSIE: item_id % item_type %', item_id, item.item_type;
30 IF (item.item_type = 'sub-function-block') THEN
31 -- Don't do anything for sub-function-blocks.
32 RAISE DEBUG 'updateRSIE: this is a sub-function-block, not updating.';
36 RAISE DEBUG 'updateRSIE: will do stuff now';
38 UPDATE requirement_spec_items
39 SET time_estimation = COALESCE((
40 SELECT SUM(time_estimation)
41 FROM requirement_spec_items
42 WHERE parent_id = item_id
46 IF (item.item_type = 'section') THEN
47 RAISE DEBUG 'updateRSIE: updating requirement_spec % itself as well.', item.requirement_spec_id;
48 UPDATE requirement_specs
49 SET time_estimation = COALESCE((
50 SELECT SUM(time_estimation)
51 FROM requirement_spec_items
52 WHERE (parent_id IS NULL)
53 AND (requirement_spec_id = item.requirement_spec_id)
61 CREATE OR REPLACE FUNCTION requirement_spec_item_time_estimation_updater_trigger() RETURNS trigger AS $$
65 RAISE DEBUG 'updateRSITE op %', TG_OP;
66 IF ((TG_OP = 'UPDATE') OR (TG_OP = 'DELETE')) THEN
67 RAISE DEBUG 'UPDATE trigg op % OLD.id % OLD.parent_id %', TG_OP, OLD.id, OLD.parent_id;
68 PERFORM update_requirement_spec_item_time_estimation(OLD.parent_id, OLD.requirement_spec_id);
69 RAISE DEBUG 'UPDATE trigg op % END %', TG_OP, OLD.id;
73 IF (TG_OP = 'UPDATE') THEN
74 do_new = OLD.parent_id <> NEW.parent_id;
77 IF (do_new OR (TG_OP = 'INSERT')) THEN
78 RAISE DEBUG 'UPDATE trigg op % NEW.id % NEW.parent_id %', TG_OP, NEW.id, NEW.parent_id;
79 PERFORM update_requirement_spec_item_time_estimation(NEW.parent_id, NEW.requirement_spec_id);
80 RAISE DEBUG 'UPDATE trigg op % END %', TG_OP, NEW.id;
87 DROP TRIGGER IF EXISTS update_requirement_spec_item_time_estimation ON requirement_spec_items;
88 CREATE TRIGGER update_requirement_spec_item_time_estimation
89 AFTER INSERT OR UPDATE OR DELETE ON requirement_spec_items
90 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_time_estimation_updater_trigger();
93 -- Trigger for deleting depending stuff if a requirement spec item is deleted.
94 CREATE OR REPLACE FUNCTION requirement_spec_item_before_delete_trigger() RETURNS trigger AS $$
96 RAISE DEBUG 'delete trig RSitem old id %', OLD.id;
97 INSERT INTO trigger_information (key, value) VALUES ('deleting_requirement_spec_item', CAST(OLD.id AS TEXT));
98 DELETE FROM requirement_spec_item_dependencies WHERE (depending_item_id = OLD.id) OR (depended_item_id = OLD.id);
99 DELETE FROM requirement_spec_items WHERE (parent_id = OLD.id);
100 DELETE FROM trigger_information WHERE (key = 'deleting_requirement_spec_item') AND (value = CAST(OLD.id AS TEXT));
101 RAISE DEBUG 'delete trig END %', OLD.id;
106 DROP TRIGGER IF EXISTS delete_requirement_spec_item_dependencies ON requirement_spec_items;
107 CREATE TRIGGER delete_requirement_spec_item_dependencies
108 BEFORE DELETE ON requirement_spec_items
109 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_before_delete_trigger();
112 -- Trigger for deleting depending stuff if a requirement spec is deleted.
113 CREATE OR REPLACE FUNCTION requirement_spec_delete_trigger() RETURNS trigger AS $$
117 tname := 'tmp_delete_reqspec' || OLD.id;
119 IF TG_WHEN = 'AFTER' THEN
120 RAISE DEBUG 'after trigger on %; deleting from versions', OLD.id;
121 EXECUTE 'DELETE FROM requirement_spec_versions ' ||
122 'WHERE id IN (SELECT version_id FROM ' || tname || ')';
124 RAISE DEBUG ' dropping table';
125 EXECUTE 'DROP TABLE ' || tname;
127 DELETE FROM trigger_information WHERE (key = 'deleting_requirement_spec') AND (value = CAST(OLD.id AS TEXT));
132 RAISE DEBUG 'before delete trigger on %', OLD.id;
134 INSERT INTO trigger_information (key, value) VALUES ('deleting_requirement_spec', CAST(OLD.id AS TEXT));
136 EXECUTE 'CREATE TEMPORARY TABLE ' || tname || ' AS ' ||
137 'SELECT DISTINCT version_id ' ||
138 'FROM requirement_specs ' ||
139 'WHERE (version_id IS NOT NULL) ' ||
140 ' AND ((id = ' || OLD.id || ') OR (working_copy_id = ' || OLD.id || '))';
142 RAISE DEBUG ' Updating version_id and items for %', OLD.id;
143 UPDATE requirement_specs SET version_id = NULL WHERE (id <> OLD.id) AND (working_copy_id = OLD.id);
144 UPDATE requirement_spec_items SET item_type = 'section', parent_id = NULL WHERE requirement_spec_id = OLD.id;
146 RAISE DEBUG ' Deleting stuff for %', OLD.id;
148 DELETE FROM requirement_spec_text_blocks WHERE (requirement_spec_id = OLD.id);
149 DELETE FROM requirement_spec_items WHERE (requirement_spec_id = OLD.id);
150 DELETE FROM requirement_specs WHERE (working_copy_id = OLD.id);
152 RAISE DEBUG ' And we out for %', OLD.id;
158 DROP TRIGGER IF EXISTS delete_requirement_spec_dependencies ON requirement_specs;
159 CREATE TRIGGER delete_requirement_spec_dependencies
160 BEFORE DELETE ON requirement_specs
161 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger();
163 DROP TRIGGER IF EXISTS after_delete_requirement_spec_dependencies ON requirement_specs;
164 CREATE TRIGGER after_delete_requirement_spec_dependencies
165 AFTER DELETE ON requirement_specs
166 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger();
168 DROP FUNCTION IF EXISTS update_requirement_spec_item_time_estimation(item_id INTEGER);