From 50aabb483692039941096440a354d77b49ce0236 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Thu, 11 Jul 2013 11:04:46 +0200 Subject: [PATCH] =?utf8?q?time=5Festimation=20in=20Sections=20richtig=20ak?= =?utf8?q?tualisieren=20&=20in=20Pflichtenheften=20einf=C3=BChren?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/DB/MetaSetup/RequirementSpec.pm | 2 +- SL/DB/MetaSetup/RequirementSpecItem.pm | 1 - SL/DB/RequirementSpec.pm | 2 +- ...uirement_spec_items_update_trigger_fix.sql | 73 +++++++++++++++++++ sql/Pg-upgrade2/requirement_specs.sql | 6 +- 5 files changed, 78 insertions(+), 6 deletions(-) create mode 100644 sql/Pg-upgrade2/requirement_spec_items_update_trigger_fix.sql diff --git a/SL/DB/MetaSetup/RequirementSpec.pm b/SL/DB/MetaSetup/RequirementSpec.pm index a08ee183a..797137afe 100644 --- a/SL/DB/MetaSetup/RequirementSpec.pm +++ b/SL/DB/MetaSetup/RequirementSpec.pm @@ -15,11 +15,11 @@ __PACKAGE__->meta->columns( is_template => { type => 'boolean', default => 'false' }, itime => { type => 'timestamp', default => 'now()' }, mtime => { type => 'timestamp' }, - net_sum => { type => 'numeric', default => '0', not_null => 1, precision => 2, scale => 12 }, previous_fb_number => { type => 'integer', not_null => 1 }, previous_section_number => { type => 'integer', not_null => 1 }, project_id => { type => 'integer' }, status_id => { type => 'integer' }, + time_estimation => { type => 'numeric', default => '0', not_null => 1, precision => 2, scale => 12 }, title => { type => 'text', not_null => 1 }, type_id => { type => 'integer' }, version_id => { type => 'integer' }, diff --git a/SL/DB/MetaSetup/RequirementSpecItem.pm b/SL/DB/MetaSetup/RequirementSpecItem.pm index ba2d4cbb1..dfce58166 100644 --- a/SL/DB/MetaSetup/RequirementSpecItem.pm +++ b/SL/DB/MetaSetup/RequirementSpecItem.pm @@ -19,7 +19,6 @@ __PACKAGE__->meta->columns( item_type => { type => 'text', not_null => 1 }, itime => { type => 'timestamp', default => 'now()', not_null => 1 }, mtime => { type => 'timestamp' }, - net_sum => { type => 'numeric', default => '0', not_null => 1, precision => 2, scale => 12 }, parent_id => { type => 'integer' }, position => { type => 'integer', not_null => 1 }, requirement_spec_id => { type => 'integer', not_null => 1 }, diff --git a/SL/DB/RequirementSpec.pm b/SL/DB/RequirementSpec.pm index db757e22f..cd1c837f6 100644 --- a/SL/DB/RequirementSpec.pm +++ b/SL/DB/RequirementSpec.pm @@ -119,7 +119,7 @@ sub _copy_from { # Copy attributes. if (!$params->{paste_template}) { - $self->assign_attributes(map({ ($_ => $source->$_) } qw(type_id status_id customer_id project_id title hourly_rate net_sum previous_section_number previous_fb_number is_template)), + $self->assign_attributes(map({ ($_ => $source->$_) } qw(type_id status_id customer_id project_id title hourly_rate time_estimation previous_section_number previous_fb_number is_template)), %attributes); } diff --git a/sql/Pg-upgrade2/requirement_spec_items_update_trigger_fix.sql b/sql/Pg-upgrade2/requirement_spec_items_update_trigger_fix.sql new file mode 100644 index 000000000..764b4516c --- /dev/null +++ b/sql/Pg-upgrade2/requirement_spec_items_update_trigger_fix.sql @@ -0,0 +1,73 @@ +-- @tag: requirement_spec_items_update_trigger_fix +-- @description: Fixes für Update-Trigger bei Pflichtenheft-Funktionsblöcken +-- @depends: requirement_specs + +ALTER TABLE requirement_specs ADD COLUMN time_estimation NUMERIC(12, 2); +UPDATE requirement_specs +SET time_estimation = COALESCE(( + SELECT SUM(rsi.time_estimation) + FROM requirement_spec_items rsi + WHERE (rsi.parent_id IS NULL) + AND (rsi.requirement_spec_id = requirement_specs.id) +), 0); +ALTER TABLE requirement_specs ALTER COLUMN time_estimation SET DEFAULT 0; +ALTER TABLE requirement_specs ALTER COLUMN time_estimation SET NOT NULL; + +ALTER TABLE requirement_specs DROP COLUMN net_sum; +ALTER TABLE requirement_spec_items DROP COLUMN net_sum; + +-- Trigger for updating time_estimation of function blocks from their +-- children (not for sections, not for sub function blocks). +CREATE OR REPLACE FUNCTION update_requirement_spec_item_time_estimation(item_id INTEGER) RETURNS BOOLEAN AS $$ + DECLARE + item RECORD; + BEGIN + IF item_id IS NULL THEN + RAISE DEBUG 'updateRSIE: item_id IS NULL'; + RETURN FALSE; + END IF; + + IF EXISTS( + SELECT * + FROM trigger_information + WHERE (key = 'deleting_requirement_spec_item') + AND (value = CAST(item_id AS TEXT)) + LIMIT 1 + ) THEN + RAISE DEBUG 'updateRSIE: item_id % is about to be deleted; do not update', item_id; + RETURN FALSE; + END IF; + + SELECT * INTO item FROM requirement_spec_items WHERE id = item_id; + RAISE DEBUG 'updateRSIE: item_id % item_type %', item_id, item.item_type; + + IF (item.item_type = 'sub-function-block') THEN + -- Don't do anything for sub-function-blocks. + RAISE DEBUG 'updateRSIE: this is a sub-function-block, not updating.'; + RETURN FALSE; + END IF; + + RAISE DEBUG 'updateRSIE: will do stuff now'; + + UPDATE requirement_spec_items + SET time_estimation = COALESCE(( + SELECT SUM(time_estimation) + FROM requirement_spec_items + WHERE parent_id = item_id + ), 0) + WHERE id = item_id; + + IF (item.item_type = 'section') THEN + RAISE DEBUG 'updateRSIE: updating requirement_spec % itself as well.', item.requirement_spec_id; + UPDATE requirement_specs + SET time_estimation = COALESCE(( + SELECT SUM(time_estimation) + FROM requirement_spec_items + WHERE (parent_id IS NULL) + AND (requirement_spec_id = item.requirement_spec_id) + ), 0); + END IF; + + RETURN TRUE; + END; +$$ LANGUAGE plpgsql; diff --git a/sql/Pg-upgrade2/requirement_specs.sql b/sql/Pg-upgrade2/requirement_specs.sql index 7647c2e91..be7acb5e2 100644 --- a/sql/Pg-upgrade2/requirement_specs.sql +++ b/sql/Pg-upgrade2/requirement_specs.sql @@ -279,9 +279,9 @@ CREATE OR REPLACE FUNCTION update_requirement_spec_item_time_estimation(item_id SELECT * INTO item FROM requirement_spec_items WHERE id = item_id; RAISE DEBUG 'updateRSIE: item_id % item_type %', item_id, item.item_type; - IF (item.item_type = 'section') OR (item.item_type = 'sub-function-block') THEN - -- Don't do anything for sections and sub-function-blocks. - RAISE DEBUG 'updateRSIE: this is a section/sub-function-block, not updating.'; + IF (item.item_type = 'sub-function-block') THEN + -- Don't do anything for sub-function-blocks. + RAISE DEBUG 'updateRSIE: this is a sub-function-block, not updating.'; RETURN FALSE; END IF; -- 2.20.1