1 -- @tag: requirement_spec_items_update_trigger_fix2
2 -- @description: Fixes für Update-Trigger bei Pflichtenheften
3 -- @depends: requirement_spec_delete_trigger_fix
5 -- Trigger for updating time_estimation of function blocks from their
6 -- children. item_id is the ID of the item that needs to be updated
7 -- (or NULL if the requirement spec itself must be updated/a section
10 -- This function must be dropped manually because PostgreSQL cannot
11 -- rename function parameters with 'CREATE OR REPLACE FUNCTION ...'
13 DROP FUNCTION update_requirement_spec_item_time_estimation(item_id INTEGER, requirement_spec_id INTEGER);
14 CREATE FUNCTION update_requirement_spec_item_time_estimation(item_id INTEGER, item_requirement_spec_id INTEGER) RETURNS BOOLEAN AS $$
21 FROM trigger_information
22 WHERE ((key = 'deleting_requirement_spec_item') AND (value = CAST(item_id AS TEXT)))
23 OR ((key = 'deleting_requirement_spec') AND (value = CAST(item_requirement_spec_id AS TEXT)))
26 RAISE DEBUG 'updateRSIE: item_id % or requirement_spec_id % is about to be deleted; do not update', item_id, item_requirement_spec_id;
30 -- item_id IS NULL means that a section has been updated. The
31 -- requirement spec itself must therefore be updated.
32 IF item_id IS NULL THEN
33 SELECT COALESCE(time_estimation, 0) AS time_estimation
35 FROM requirement_specs
36 WHERE id = item_requirement_spec_id;
38 SELECT COALESCE(SUM(time_estimation), 0) AS time_estimation
40 FROM requirement_spec_items
41 WHERE (parent_id IS NULL)
42 AND (requirement_spec_id = item_requirement_spec_id);
44 IF current_row.time_estimation <> new_row.time_estimation THEN
45 RAISE DEBUG 'updateRSIE: updating requirement_spec % itself: old estimation % new %.', item_requirement_spec_id, current_row.time_estimation, new_row.time_estimation;
47 UPDATE requirement_specs
48 SET time_estimation = new_row.time_estimation
49 WHERE id = item_requirement_spec_id;
55 -- If we're here it means that either a sub-function-block or a
56 -- function-block has been updated. item_id is the parent's ID of
57 -- the updated item -- meaning the ID of the item that needs to be
60 SELECT COALESCE(time_estimation, 0) AS time_estimation
62 FROM requirement_spec_items
65 SELECT COALESCE(SUM(time_estimation), 0) AS time_estimation
67 FROM requirement_spec_items
68 WHERE (parent_id = item_id);
70 IF current_row.time_estimation = new_row.time_estimation THEN
71 RAISE DEBUG 'updateRSIE: item %: nothing to do', item_id;
75 RAISE DEBUG 'updateRSIE: updating item %: old estimation % new %.', item_id, current_row.time_estimation, new_row.time_estimation;
77 UPDATE requirement_spec_items
78 SET time_estimation = new_row.time_estimation
85 CREATE OR REPLACE FUNCTION recalculate_spec_item_time_estimation(the_requirement_spec_id INTEGER) RETURNS BOOLEAN AS $$
90 SELECT DISTINCT parent_id
91 FROM requirement_spec_items
92 WHERE (requirement_spec_id = the_requirement_spec_id)
93 AND (item_type = 'sub-function-block')
95 RAISE DEBUG 'hmm function-block with sub: %', item.parent_id;
96 PERFORM update_requirement_spec_item_time_estimation(item.parent_id, the_requirement_spec_id);
100 SELECT DISTINCT parent_id
101 FROM requirement_spec_items
102 WHERE (requirement_spec_id = the_requirement_spec_id)
103 AND (item_type = 'function-block')
106 FROM requirement_spec_items
107 WHERE (requirement_spec_id = the_requirement_spec_id)
108 AND (item_type = 'sub-function-block')
111 RAISE DEBUG 'hmm section with function-block: %', item.parent_id;
112 PERFORM update_requirement_spec_item_time_estimation(item.parent_id, the_requirement_spec_id);
115 PERFORM update_requirement_spec_item_time_estimation(NULL, the_requirement_spec_id);
121 CREATE OR REPLACE FUNCTION recalculate_all_spec_item_time_estimations() RETURNS BOOLEAN AS $$
125 FOR rspec IN SELECT id FROM requirement_specs LOOP
126 PERFORM recalculate_spec_item_time_estimation(rspec.id);
133 SELECT recalculate_all_spec_item_time_estimations();