1 -- @tag: requirement_spec_items_update_trigger_fix
2 -- @description: Fixes für Update-Trigger bei Pflichtenheft-Funktionsblöcken
3 -- @depends: requirement_specs
5 ALTER TABLE requirement_specs ADD COLUMN time_estimation NUMERIC(12, 2);
6 UPDATE requirement_specs
7 SET time_estimation = COALESCE((
8 SELECT SUM(rsi.time_estimation)
9 FROM requirement_spec_items rsi
10 WHERE (rsi.parent_id IS NULL)
11 AND (rsi.requirement_spec_id = requirement_specs.id)
13 ALTER TABLE requirement_specs ALTER COLUMN time_estimation SET DEFAULT 0;
14 ALTER TABLE requirement_specs ALTER COLUMN time_estimation SET NOT NULL;
16 ALTER TABLE requirement_specs DROP COLUMN net_sum;
17 ALTER TABLE requirement_spec_items DROP COLUMN net_sum;
19 -- Trigger for updating time_estimation of function blocks from their
20 -- children (not for sections, not for sub function blocks).
21 CREATE OR REPLACE FUNCTION update_requirement_spec_item_time_estimation(item_id INTEGER) RETURNS BOOLEAN AS $$
25 IF item_id IS NULL THEN
26 RAISE DEBUG 'updateRSIE: item_id IS NULL';
32 FROM trigger_information
33 WHERE (key = 'deleting_requirement_spec_item')
34 AND (value = CAST(item_id AS TEXT))
37 RAISE DEBUG 'updateRSIE: item_id % is about to be deleted; do not update', item_id;
41 SELECT * INTO item FROM requirement_spec_items WHERE id = item_id;
42 RAISE DEBUG 'updateRSIE: item_id % item_type %', item_id, item.item_type;
44 IF (item.item_type = 'sub-function-block') THEN
45 -- Don't do anything for sub-function-blocks.
46 RAISE DEBUG 'updateRSIE: this is a sub-function-block, not updating.';
50 RAISE DEBUG 'updateRSIE: will do stuff now';
52 UPDATE requirement_spec_items
53 SET time_estimation = COALESCE((
54 SELECT SUM(time_estimation)
55 FROM requirement_spec_items
56 WHERE parent_id = item_id
60 IF (item.item_type = 'section') THEN
61 RAISE DEBUG 'updateRSIE: updating requirement_spec % itself as well.', item.requirement_spec_id;
62 UPDATE requirement_specs
63 SET time_estimation = COALESCE((
64 SELECT SUM(time_estimation)
65 FROM requirement_spec_items
66 WHERE (parent_id IS NULL)
67 AND (requirement_spec_id = item.requirement_spec_id)