sql/Pg-upgrade2/requirement_spec_items_update_trigger_fix2.sql

   1 -- @tag: requirement_spec_items_update_trigger_fix2
   2 -- @description: Fixes für Update-Trigger bei Pflichtenheften
   3 -- @depends: requirement_spec_delete_trigger_fix
   4
   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
   8 -- was changed).
   9
  10 -- This function must be dropped manually because PostgreSQL cannot
  11 -- rename function parameters with 'CREATE OR REPLACE FUNCTION ...'
  12 -- anymore.
  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 $$
  15   DECLARE
  16     current_row RECORD;
  17     new_row     RECORD;
  18   BEGIN
  19     IF EXISTS(
  20       SELECT *
  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)))
  24       LIMIT 1
  25     ) THEN
  26       RAISE DEBUG 'updateRSIE: item_id % or requirement_spec_id % is about to be deleted; do not update', item_id, item_requirement_spec_id;
  27       RETURN FALSE;
  28     END IF;
  29
  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
  34       INTO current_row
  35       FROM requirement_specs
  36       WHERE id = item_requirement_spec_id;
  37
  38       SELECT COALESCE(SUM(time_estimation), 0) AS time_estimation
  39       INTO new_row
  40       FROM requirement_spec_items
  41       WHERE (parent_id IS NULL)
  42         AND (requirement_spec_id = item_requirement_spec_id);
  43
  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;
  46
  47         UPDATE requirement_specs
  48         SET time_estimation = new_row.time_estimation
  49         WHERE id = item_requirement_spec_id;
  50       END IF;
  51
  52       RETURN TRUE;
  53     END IF;
  54
  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
  58     -- updated now.
  59
  60     SELECT COALESCE(time_estimation, 0) AS time_estimation
  61     INTO current_row
  62     FROM requirement_spec_items
  63     WHERE id = item_id;
  64
  65     SELECT COALESCE(SUM(time_estimation), 0) AS time_estimation
  66     INTO new_row
  67     FROM requirement_spec_items
  68     WHERE (parent_id = item_id);
  69
  70     IF current_row.time_estimation = new_row.time_estimation THEN
  71       RAISE DEBUG 'updateRSIE: item %: nothing to do', item_id;
  72       RETURN TRUE;
  73     END IF;
  74
  75     RAISE DEBUG 'updateRSIE: updating item %: old estimation % new %.', item_id, current_row.time_estimation, new_row.time_estimation;
  76
  77     UPDATE requirement_spec_items
  78     SET time_estimation = new_row.time_estimation
  79     WHERE id = item_id;
  80
  81     RETURN TRUE;
  82   END;
  83 $$ LANGUAGE plpgsql;
  84
  85 CREATE OR REPLACE FUNCTION recalculate_spec_item_time_estimation(the_requirement_spec_id INTEGER) RETURNS BOOLEAN AS $$
  86   DECLARE
  87     item RECORD;
  88   BEGIN
  89     FOR item IN
  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')
  94     LOOP
  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);
  97     END LOOP;
  98
  99     FOR item IN
 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')
 104         AND (id NOT IN (
 105           SELECT parent_id
 106           FROM requirement_spec_items
 107           WHERE (requirement_spec_id = the_requirement_spec_id)
 108             AND (item_type           = 'sub-function-block')
 109         ))
 110     LOOP
 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);
 113     END LOOP;
 114
 115     PERFORM update_requirement_spec_item_time_estimation(NULL, the_requirement_spec_id);
 116
 117     RETURN TRUE;
 118   END;
 119 $$ LANGUAGE plpgsql;
 120
 121 CREATE OR REPLACE FUNCTION recalculate_all_spec_item_time_estimations() RETURNS BOOLEAN AS $$
 122   DECLARE
 123     rspec RECORD;
 124   BEGIN
 125     FOR rspec IN SELECT id FROM requirement_specs LOOP
 126       PERFORM recalculate_spec_item_time_estimation(rspec.id);
 127     END LOOP;
 128
 129     RETURN TRUE;
 130   END;
 131 $$ LANGUAGE plpgsql;
 132
 133 SELECT recalculate_all_spec_item_time_estimations();