From d17e1b9d1b761bb19d0cb79a742ea70a07028998 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Wed, 30 Jan 2013 10:22:53 +0100 Subject: [PATCH] Pflichtenhefte: Datenbankschema und Rose-DB-Models --- SL/DB/Helper/ALL.pm | 11 + SL/DB/Helper/Mappings.pm | 11 + SL/DB/MetaSetup/Customer.pm | 1 + SL/DB/MetaSetup/Default.pm | 168 ++++---- SL/DB/MetaSetup/RequirementSpec.pm | 68 +++ .../RequirementSpecAcceptanceStatus.pm | 29 ++ SL/DB/MetaSetup/RequirementSpecComplexity.pm | 28 ++ SL/DB/MetaSetup/RequirementSpecDependency.pm | 21 + SL/DB/MetaSetup/RequirementSpecItem.pm | 70 ++++ .../RequirementSpecPredefinedText.pm | 30 ++ SL/DB/MetaSetup/RequirementSpecRisk.pm | 28 ++ SL/DB/MetaSetup/RequirementSpecStatus.pm | 29 ++ SL/DB/MetaSetup/RequirementSpecTextBlock.pm | 29 ++ SL/DB/MetaSetup/RequirementSpecType.pm | 28 ++ SL/DB/MetaSetup/RequirementSpecVersion.pm | 37 ++ SL/DB/RequirementSpec.pm | 35 ++ SL/DB/RequirementSpecAcceptanceStatus.pm | 21 + SL/DB/RequirementSpecComplexity.pm | 21 + SL/DB/RequirementSpecDependency.pm | 13 + SL/DB/RequirementSpecItem.pm | 10 + SL/DB/RequirementSpecPredefinedText.pm | 22 + SL/DB/RequirementSpecRisk.pm | 21 + SL/DB/RequirementSpecStatus.pm | 23 ++ SL/DB/RequirementSpecTextBlock.pm | 24 ++ SL/DB/RequirementSpecType.pm | 21 + SL/DB/RequirementSpecVersion.pm | 21 + locale/de/all | 2 + sql/Pg-upgrade2/requirement_specs.sql | 391 ++++++++++++++++++ 28 files changed, 1130 insertions(+), 83 deletions(-) create mode 100644 SL/DB/MetaSetup/RequirementSpec.pm create mode 100644 SL/DB/MetaSetup/RequirementSpecAcceptanceStatus.pm create mode 100644 SL/DB/MetaSetup/RequirementSpecComplexity.pm create mode 100644 SL/DB/MetaSetup/RequirementSpecDependency.pm create mode 100644 SL/DB/MetaSetup/RequirementSpecItem.pm create mode 100644 SL/DB/MetaSetup/RequirementSpecPredefinedText.pm create mode 100644 SL/DB/MetaSetup/RequirementSpecRisk.pm create mode 100644 SL/DB/MetaSetup/RequirementSpecStatus.pm create mode 100644 SL/DB/MetaSetup/RequirementSpecTextBlock.pm create mode 100644 SL/DB/MetaSetup/RequirementSpecType.pm create mode 100644 SL/DB/MetaSetup/RequirementSpecVersion.pm create mode 100644 SL/DB/RequirementSpec.pm create mode 100644 SL/DB/RequirementSpecAcceptanceStatus.pm create mode 100644 SL/DB/RequirementSpecComplexity.pm create mode 100644 SL/DB/RequirementSpecDependency.pm create mode 100644 SL/DB/RequirementSpecItem.pm create mode 100644 SL/DB/RequirementSpecPredefinedText.pm create mode 100644 SL/DB/RequirementSpecRisk.pm create mode 100644 SL/DB/RequirementSpecStatus.pm create mode 100644 SL/DB/RequirementSpecTextBlock.pm create mode 100644 SL/DB/RequirementSpecType.pm create mode 100644 SL/DB/RequirementSpecVersion.pm create mode 100644 sql/Pg-upgrade2/requirement_specs.sql diff --git a/SL/DB/Helper/ALL.pm b/SL/DB/Helper/ALL.pm index 6bdc3689b..5e29b7149 100644 --- a/SL/DB/Helper/ALL.pm +++ b/SL/DB/Helper/ALL.pm @@ -72,6 +72,17 @@ use SL::DB::Project; use SL::DB::ProjectType; use SL::DB::PurchaseInvoice; use SL::DB::RecordLink; +use SL::DB::RequirementSpecAcceptanceStatus; +use SL::DB::RequirementSpecComplexity; +use SL::DB::RequirementSpecDependency; +use SL::DB::RequirementSpecItem; +use SL::DB::RequirementSpecPredefinedText; +use SL::DB::RequirementSpecRisk; +use SL::DB::RequirementSpecStatus; +use SL::DB::RequirementSpecTextBlock; +use SL::DB::RequirementSpecType; +use SL::DB::RequirementSpecVersion; +use SL::DB::RequirementSpec; use SL::DB::SchemaInfo; use SL::DB::SepaExport; use SL::DB::SepaExportItem; diff --git a/SL/DB/Helper/Mappings.pm b/SL/DB/Helper/Mappings.pm index 022894c16..0008450d6 100644 --- a/SL/DB/Helper/Mappings.pm +++ b/SL/DB/Helper/Mappings.pm @@ -152,6 +152,17 @@ my %kivitendo_package_names = ( project => 'project', project_types => 'ProjectType', record_links => 'record_link', + requirement_spec_acceptance_statuses => 'RequirementSpecAcceptanceStatus', + requirement_spec_complexities => 'RequirementSpecComplexity', + requirement_spec_item_dependencies => 'RequirementSpecDependency', + requirement_spec_items => 'RequirementSpecItem', + requirement_spec_predefined_texts => 'RequirementSpecPredefinedText', + requirement_spec_risks => 'RequirementSpecRisk', + requirement_spec_statuses => 'RequirementSpecStatus', + requirement_spec_text_blocks => 'RequirementSpecTextBlock', + requirement_spec_types => 'RequirementSpecType', + requirement_spec_versions => 'RequirementSpecVersion', + requirement_specs => 'RequirementSpec', sepa_export => 'sepa_export', sepa_export_items => 'sepa_export_item', schema_info => 'schema_info', diff --git a/SL/DB/MetaSetup/Customer.pm b/SL/DB/MetaSetup/Customer.pm index a12aa1050..1537bf6c3 100644 --- a/SL/DB/MetaSetup/Customer.pm +++ b/SL/DB/MetaSetup/Customer.pm @@ -33,6 +33,7 @@ __PACKAGE__->meta->columns( fax => { type => 'varchar', length => 30 }, greeting => { type => 'text' }, homepage => { type => 'text' }, + hourly_rate => { type => 'numeric', precision => 2, scale => 8 }, iban => { type => 'varchar', length => 100 }, id => { type => 'integer', not_null => 1, sequence => 'id' }, itime => { type => 'timestamp', default => 'now()' }, diff --git a/SL/DB/MetaSetup/Default.pm b/SL/DB/MetaSetup/Default.pm index b96def263..c1deb9c34 100644 --- a/SL/DB/MetaSetup/Default.pm +++ b/SL/DB/MetaSetup/Default.pm @@ -9,89 +9,91 @@ use base qw(SL::DB::Object); __PACKAGE__->meta->table('defaults'); __PACKAGE__->meta->columns( - accounting_method => { type => 'text' }, - address => { type => 'text' }, - ap_changeable => { type => 'integer', default => 2, not_null => 1 }, - ap_show_mark_as_paid => { type => 'boolean', default => 'true' }, - ar_changeable => { type => 'integer', default => 2, not_null => 1 }, - ar_paid_accno_id => { type => 'integer' }, - ar_show_mark_as_paid => { type => 'boolean', default => 'true' }, - articlenumber => { type => 'text' }, - assemblynumber => { type => 'text' }, - balance_startdate_method => { type => 'text' }, - bin_id => { type => 'integer' }, - bin_id_ignore_onhand => { type => 'integer' }, - businessnumber => { type => 'text' }, - closedto => { type => 'date' }, - cnnumber => { type => 'text' }, - co_ustid => { type => 'text' }, - coa => { type => 'text' }, - company => { type => 'text' }, - currency_id => { type => 'integer', not_null => 1 }, - customernumber => { type => 'text' }, - datev_check_on_ap_transaction => { type => 'boolean', default => 'true' }, - datev_check_on_ar_transaction => { type => 'boolean', default => 'true' }, - datev_check_on_gl_transaction => { type => 'boolean', default => 'true' }, - datev_check_on_purchase_invoice => { type => 'boolean', default => 'true' }, - datev_check_on_sales_invoice => { type => 'boolean', default => 'true' }, - dunning_ar => { type => 'integer' }, - dunning_ar_amount_fee => { type => 'integer' }, - dunning_ar_amount_interest => { type => 'integer' }, - duns => { type => 'text' }, - expense_accno_id => { type => 'integer' }, - fxgain_accno_id => { type => 'integer' }, - fxloss_accno_id => { type => 'integer' }, - gl_changeable => { type => 'integer', default => 2, not_null => 1 }, - id => { type => 'serial', not_null => 1 }, - income_accno_id => { type => 'integer' }, - inventory_accno_id => { type => 'integer' }, - inventory_system => { type => 'text' }, - invnumber => { type => 'text' }, - ir_changeable => { type => 'integer', default => 2, not_null => 1 }, - ir_show_mark_as_paid => { type => 'boolean', default => 'true' }, - is_changeable => { type => 'integer', default => 2, not_null => 1 }, - is_show_mark_as_paid => { type => 'boolean', default => 'true' }, - itime => { type => 'timestamp', default => 'now()' }, - language_id => { type => 'integer' }, - max_future_booking_interval => { type => 'integer', default => 360 }, - mtime => { type => 'timestamp' }, - normalize_part_descriptions => { type => 'boolean', default => 'true' }, - normalize_vc_names => { type => 'boolean', default => 'true' }, - parts_image_css => { type => 'text', default => 'border:0;float:left;max-width:250px;margin-top:20px:margin-right:10px;margin-left:10px;' }, - parts_listing_image => { type => 'boolean', default => 'true' }, - parts_show_image => { type => 'boolean', default => 'true' }, - payments_changeable => { type => 'integer', default => '0', not_null => 1 }, - pdonumber => { type => 'text' }, - ponumber => { type => 'text' }, - profit_determination => { type => 'text' }, - purchase_delivery_order_show_delete => { type => 'boolean', default => 'true' }, - purchase_order_show_delete => { type => 'boolean', default => 'true' }, - revtrans => { type => 'boolean', default => 'false' }, - rfqnumber => { type => 'text' }, - rmanumber => { type => 'text' }, - sales_delivery_order_show_delete => { type => 'boolean', default => 'true' }, - sales_order_show_delete => { type => 'boolean', default => 'true' }, - sdonumber => { type => 'text' }, - sepa_creditor_id => { type => 'text' }, - servicenumber => { type => 'text' }, - show_bestbefore => { type => 'boolean', default => 'false' }, - show_weight => { type => 'boolean', default => 'false', not_null => 1 }, - signature => { type => 'text' }, - sonumber => { type => 'text' }, - sqnumber => { type => 'text' }, - taxnumber => { type => 'text' }, - templates => { type => 'text' }, - transfer_default => { type => 'boolean', default => 'true' }, - transfer_default_ignore_onhand => { type => 'boolean', default => 'false' }, - transfer_default_use_master_default_bin => { type => 'boolean', default => 'false' }, - vendornumber => { type => 'text' }, - version => { type => 'varchar', length => 8 }, - vertreter => { type => 'boolean', default => 'false' }, - warehouse_id => { type => 'integer' }, - warehouse_id_ignore_onhand => { type => 'integer' }, - webdav => { type => 'boolean', default => 'false' }, - webdav_documents => { type => 'boolean', default => 'false' }, - weightunit => { type => 'varchar', length => 5 }, + accounting_method => { type => 'text' }, + address => { type => 'text' }, + ap_changeable => { type => 'integer', default => 2, not_null => 1 }, + ap_show_mark_as_paid => { type => 'boolean', default => 'true' }, + ar_changeable => { type => 'integer', default => 2, not_null => 1 }, + ar_paid_accno_id => { type => 'integer' }, + ar_show_mark_as_paid => { type => 'boolean', default => 'true' }, + articlenumber => { type => 'text' }, + assemblynumber => { type => 'text' }, + balance_startdate_method => { type => 'text' }, + bin_id => { type => 'integer' }, + bin_id_ignore_onhand => { type => 'integer' }, + businessnumber => { type => 'text' }, + closedto => { type => 'date' }, + cnnumber => { type => 'text' }, + co_ustid => { type => 'text' }, + coa => { type => 'text' }, + company => { type => 'text' }, + currency_id => { type => 'integer', not_null => 1 }, + customernumber => { type => 'text' }, + datev_check_on_ap_transaction => { type => 'boolean', default => 'true' }, + datev_check_on_ar_transaction => { type => 'boolean', default => 'true' }, + datev_check_on_gl_transaction => { type => 'boolean', default => 'true' }, + datev_check_on_purchase_invoice => { type => 'boolean', default => 'true' }, + datev_check_on_sales_invoice => { type => 'boolean', default => 'true' }, + dunning_ar => { type => 'integer' }, + dunning_ar_amount_fee => { type => 'integer' }, + dunning_ar_amount_interest => { type => 'integer' }, + duns => { type => 'text' }, + expense_accno_id => { type => 'integer' }, + fxgain_accno_id => { type => 'integer' }, + fxloss_accno_id => { type => 'integer' }, + gl_changeable => { type => 'integer', default => 2, not_null => 1 }, + id => { type => 'serial', not_null => 1 }, + income_accno_id => { type => 'integer' }, + inventory_accno_id => { type => 'integer' }, + inventory_system => { type => 'text' }, + invnumber => { type => 'text' }, + ir_changeable => { type => 'integer', default => 2, not_null => 1 }, + ir_show_mark_as_paid => { type => 'boolean', default => 'true' }, + is_changeable => { type => 'integer', default => 2, not_null => 1 }, + is_show_mark_as_paid => { type => 'boolean', default => 'true' }, + itime => { type => 'timestamp', default => 'now()' }, + language_id => { type => 'integer' }, + max_future_booking_interval => { type => 'integer', default => 360 }, + mtime => { type => 'timestamp' }, + normalize_part_descriptions => { type => 'boolean', default => 'true' }, + normalize_vc_names => { type => 'boolean', default => 'true' }, + parts_image_css => { type => 'text', default => 'border:0;float:left;max-width:250px;margin-top:20px:margin-right:10px;margin-left:10px;' }, + parts_listing_image => { type => 'boolean', default => 'true' }, + parts_show_image => { type => 'boolean', default => 'true' }, + payments_changeable => { type => 'integer', default => '0', not_null => 1 }, + pdonumber => { type => 'text' }, + ponumber => { type => 'text' }, + profit_determination => { type => 'text' }, + purchase_delivery_order_show_delete => { type => 'boolean', default => 'true' }, + purchase_order_show_delete => { type => 'boolean', default => 'true' }, + requirement_spec_section_number_format => { type => 'text', default => 'A00', not_null => 1 }, + requirement_spec_function_block_number_format => { type => 'text', default => 'FB000', not_null => 1 }, + revtrans => { type => 'boolean', default => 'false' }, + rfqnumber => { type => 'text' }, + rmanumber => { type => 'text' }, + sales_delivery_order_show_delete => { type => 'boolean', default => 'true' }, + sales_order_show_delete => { type => 'boolean', default => 'true' }, + sdonumber => { type => 'text' }, + sepa_creditor_id => { type => 'text' }, + servicenumber => { type => 'text' }, + show_bestbefore => { type => 'boolean', default => 'false' }, + show_weight => { type => 'boolean', default => 'false', not_null => 1 }, + signature => { type => 'text' }, + sonumber => { type => 'text' }, + sqnumber => { type => 'text' }, + taxnumber => { type => 'text' }, + templates => { type => 'text' }, + transfer_default => { type => 'boolean', default => 'true' }, + transfer_default_ignore_onhand => { type => 'boolean', default => 'false' }, + transfer_default_use_master_default_bin => { type => 'boolean', default => 'false' }, + vendornumber => { type => 'text' }, + version => { type => 'varchar', length => 8 }, + vertreter => { type => 'boolean', default => 'false' }, + warehouse_id => { type => 'integer' }, + warehouse_id_ignore_onhand => { type => 'integer' }, + webdav => { type => 'boolean', default => 'false' }, + webdav_documents => { type => 'boolean', default => 'false' }, + weightunit => { type => 'varchar', length => 5 }, ); __PACKAGE__->meta->primary_key_columns([ 'id' ]); diff --git a/SL/DB/MetaSetup/RequirementSpec.pm b/SL/DB/MetaSetup/RequirementSpec.pm new file mode 100644 index 000000000..9971f8662 --- /dev/null +++ b/SL/DB/MetaSetup/RequirementSpec.pm @@ -0,0 +1,68 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RequirementSpec; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'requirement_specs', + + columns => [ + id => { type => 'serial', not_null => 1 }, + type_id => { type => 'integer', not_null => 1 }, + status_id => { type => 'integer', not_null => 1 }, + version_id => { type => 'integer' }, + customer_id => { type => 'integer', not_null => 1 }, + project_id => { type => 'integer' }, + title => { type => 'text', not_null => 1 }, + hourly_rate => { type => 'numeric', default => '0', not_null => 1, precision => 2, scale => 8 }, + net_sum => { type => 'numeric', default => '0', not_null => 1, precision => 2, scale => 12 }, + working_copy_id => { type => 'integer' }, + previous_section_number => { type => 'integer', not_null => 1 }, + previous_fb_number => { type => 'integer', not_null => 1 }, + is_template => { type => 'boolean', default => 'false' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + customer => { + class => 'SL::DB::Customer', + key_columns => { customer_id => 'id' }, + }, + + project => { + class => 'SL::DB::Project', + key_columns => { project_id => 'id' }, + }, + + status => { + class => 'SL::DB::RequirementSpecStatus', + key_columns => { status_id => 'id' }, + }, + + type => { + class => 'SL::DB::RequirementSpecType', + key_columns => { type_id => 'id' }, + }, + + version => { + class => 'SL::DB::RequirementSpecVersion', + key_columns => { version_id => 'id' }, + }, + + working_copy => { + class => 'SL::DB::RequirementSpec', + key_columns => { working_copy_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/RequirementSpecAcceptanceStatus.pm b/SL/DB/MetaSetup/RequirementSpecAcceptanceStatus.pm new file mode 100644 index 000000000..96d2d0b51 --- /dev/null +++ b/SL/DB/MetaSetup/RequirementSpecAcceptanceStatus.pm @@ -0,0 +1,29 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RequirementSpecAcceptanceStatus; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'requirement_spec_acceptance_statuses', + + columns => [ + id => { type => 'serial', not_null => 1 }, + name => { type => 'text', not_null => 1 }, + description => { type => 'text' }, + position => { type => 'integer', not_null => 1 }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + unique_key => [ 'name', 'description' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/RequirementSpecComplexity.pm b/SL/DB/MetaSetup/RequirementSpecComplexity.pm new file mode 100644 index 000000000..60be04003 --- /dev/null +++ b/SL/DB/MetaSetup/RequirementSpecComplexity.pm @@ -0,0 +1,28 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RequirementSpecComplexity; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'requirement_spec_complexities', + + columns => [ + id => { type => 'serial', not_null => 1 }, + description => { type => 'text', not_null => 1 }, + position => { type => 'integer', not_null => 1 }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + unique_key => [ 'description' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/RequirementSpecDependency.pm b/SL/DB/MetaSetup/RequirementSpecDependency.pm new file mode 100644 index 000000000..29f0654df --- /dev/null +++ b/SL/DB/MetaSetup/RequirementSpecDependency.pm @@ -0,0 +1,21 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RequirementSpecDependency; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'requirement_spec_item_dependencies', + + columns => [ + depending_item_id => { type => 'integer', not_null => 1 }, + depended_item_id => { type => 'integer', not_null => 1 }, + ], + + primary_key_columns => [ 'depending_item_id', 'depended_item_id' ], +); + +1; +; diff --git a/SL/DB/MetaSetup/RequirementSpecItem.pm b/SL/DB/MetaSetup/RequirementSpecItem.pm new file mode 100644 index 000000000..85cfc362a --- /dev/null +++ b/SL/DB/MetaSetup/RequirementSpecItem.pm @@ -0,0 +1,70 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RequirementSpecItem; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'requirement_spec_items', + + columns => [ + id => { type => 'serial', not_null => 1 }, + requirement_spec_id => { type => 'integer', not_null => 1 }, + parent_id => { type => 'integer' }, + position => { type => 'integer', not_null => 1 }, + fb_number => { type => 'text', not_null => 1 }, + title => { type => 'text' }, + description => { type => 'text' }, + complexity_id => { type => 'integer' }, + risk_id => { type => 'integer' }, + time_estimation => { type => 'numeric', default => '0', not_null => 1, precision => 2, scale => 12 }, + net_sum => { type => 'numeric', default => '0', not_null => 1, precision => 2, scale => 12 }, + is_flagged => { type => 'boolean', default => 'false', not_null => 1 }, + acceptance_status_id => { type => 'integer' }, + acceptance_text => { type => 'text' }, + itime => { type => 'timestamp', default => 'now()', not_null => 1 }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + acceptance_status => { + class => 'SL::DB::RequirementSpecAcceptanceStatus', + key_columns => { acceptance_status_id => 'id' }, + }, + + complexity => { + class => 'SL::DB::RequirementSpecComplexity', + key_columns => { complexity_id => 'id' }, + }, + + parent => { + class => 'SL::DB::RequirementSpecItem', + key_columns => { parent_id => 'id' }, + }, + ], + + relationships => [ + depended_items => { + map_class => 'SL::DB::RequirementSpecDependency', + map_from => 'depending_item', + map_to => 'depended_item', + type => 'many to many', + }, + + depending_items => { + map_class => 'SL::DB::RequirementSpecDependency', + map_from => 'depended_item', + map_to => 'depending_item', + type => 'many to many', + }, + ], +); + +1; +; diff --git a/SL/DB/MetaSetup/RequirementSpecPredefinedText.pm b/SL/DB/MetaSetup/RequirementSpecPredefinedText.pm new file mode 100644 index 000000000..ca6442223 --- /dev/null +++ b/SL/DB/MetaSetup/RequirementSpecPredefinedText.pm @@ -0,0 +1,30 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RequirementSpecPredefinedText; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'requirement_spec_predefined_texts', + + columns => [ + id => { type => 'serial', not_null => 1 }, + description => { type => 'text', not_null => 1 }, + title => { type => 'text', not_null => 1 }, + text => { type => 'text', not_null => 1 }, + position => { type => 'integer', not_null => 1 }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + unique_key => [ 'description' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/RequirementSpecRisk.pm b/SL/DB/MetaSetup/RequirementSpecRisk.pm new file mode 100644 index 000000000..602413060 --- /dev/null +++ b/SL/DB/MetaSetup/RequirementSpecRisk.pm @@ -0,0 +1,28 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RequirementSpecRisk; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'requirement_spec_risks', + + columns => [ + id => { type => 'serial', not_null => 1 }, + description => { type => 'text', not_null => 1 }, + position => { type => 'integer', not_null => 1 }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + unique_key => [ 'description' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/RequirementSpecStatus.pm b/SL/DB/MetaSetup/RequirementSpecStatus.pm new file mode 100644 index 000000000..9e9d9e221 --- /dev/null +++ b/SL/DB/MetaSetup/RequirementSpecStatus.pm @@ -0,0 +1,29 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RequirementSpecStatus; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'requirement_spec_statuses', + + columns => [ + id => { type => 'serial', not_null => 1 }, + name => { type => 'text', not_null => 1 }, + description => { type => 'text', not_null => 1 }, + position => { type => 'integer', not_null => 1 }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + unique_key => [ 'name', 'description' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/RequirementSpecTextBlock.pm b/SL/DB/MetaSetup/RequirementSpecTextBlock.pm new file mode 100644 index 000000000..adb203e71 --- /dev/null +++ b/SL/DB/MetaSetup/RequirementSpecTextBlock.pm @@ -0,0 +1,29 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RequirementSpecTextBlock; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'requirement_spec_text_blocks', + + columns => [ + id => { type => 'serial', not_null => 1 }, + requirement_spec_id => { type => 'integer', not_null => 1 }, + title => { type => 'text', not_null => 1 }, + text => { type => 'text' }, + position => { type => 'integer', not_null => 1 }, + output_position => { type => 'integer', default => 1, not_null => 1 }, + itime => { type => 'timestamp', default => 'now()', not_null => 1 }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/RequirementSpecType.pm b/SL/DB/MetaSetup/RequirementSpecType.pm new file mode 100644 index 000000000..d3f19dbd6 --- /dev/null +++ b/SL/DB/MetaSetup/RequirementSpecType.pm @@ -0,0 +1,28 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RequirementSpecType; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'requirement_spec_types', + + columns => [ + id => { type => 'serial', not_null => 1 }, + description => { type => 'text', not_null => 1 }, + position => { type => 'integer', not_null => 1 }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + unique_key => [ 'description' ], + + allow_inline_column_values => 1, +); + +1; +; diff --git a/SL/DB/MetaSetup/RequirementSpecVersion.pm b/SL/DB/MetaSetup/RequirementSpecVersion.pm new file mode 100644 index 000000000..ac96df307 --- /dev/null +++ b/SL/DB/MetaSetup/RequirementSpecVersion.pm @@ -0,0 +1,37 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RequirementSpecVersion; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->setup( + table => 'requirement_spec_versions', + + columns => [ + id => { type => 'serial', not_null => 1 }, + version_number => { type => 'integer' }, + description => { type => 'text', not_null => 1 }, + comment => { type => 'text' }, + order_date => { type => 'date' }, + order_number => { type => 'text' }, + order_id => { type => 'integer' }, + itime => { type => 'timestamp', default => 'now()' }, + mtime => { type => 'timestamp' }, + ], + + primary_key_columns => [ 'id' ], + + allow_inline_column_values => 1, + + foreign_keys => [ + order => { + class => 'SL::DB::Order', + key_columns => { order_id => 'id' }, + }, + ], +); + +1; +; diff --git a/SL/DB/RequirementSpec.pm b/SL/DB/RequirementSpec.pm new file mode 100644 index 000000000..99bf11631 --- /dev/null +++ b/SL/DB/RequirementSpec.pm @@ -0,0 +1,35 @@ +package SL::DB::RequirementSpec; + +use strict; + +use SL::DB::MetaSetup::RequirementSpec; +use SL::Locale::String; + +__PACKAGE__->meta->add_relationship( + items => { + type => 'one to many', + class => 'SL::DB::RequirementSpecItem', + column_map => { id => 'requirement_spec_id' }, + }, + text_blocks => { + type => 'one to many', + class => 'SL::DB::RequirementSpecTextBlock', + column_map => { id => 'requirement_spec_id' }, + }, +); + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +__PACKAGE__->meta->initialize; + +sub validate { + my ($self) = @_; + + my @errors; + push @errors, t8('The title is missing.') if !$self->title; + + return @errors; +} + +1; diff --git a/SL/DB/RequirementSpecAcceptanceStatus.pm b/SL/DB/RequirementSpecAcceptanceStatus.pm new file mode 100644 index 000000000..c932e4713 --- /dev/null +++ b/SL/DB/RequirementSpecAcceptanceStatus.pm @@ -0,0 +1,21 @@ +package SL::DB::RequirementSpecAcceptanceStatus; + +use strict; + +use SL::DB::MetaSetup::RequirementSpecAcceptanceStatus; +use SL::DB::Helper::ActsAsList; +use SL::Locale::String; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +sub validate { + my ($self) = @_; + + my @errors; + push @errors, t8('The description is missing.') if !$self->description; + + return @errors; +} + +1; diff --git a/SL/DB/RequirementSpecComplexity.pm b/SL/DB/RequirementSpecComplexity.pm new file mode 100644 index 000000000..eaf74069b --- /dev/null +++ b/SL/DB/RequirementSpecComplexity.pm @@ -0,0 +1,21 @@ +package SL::DB::RequirementSpecComplexity; + +use strict; + +use SL::DB::MetaSetup::RequirementSpecComplexity; +use SL::DB::Helper::ActsAsList; +use SL::Locale::String; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +sub validate { + my ($self) = @_; + + my @errors; + push @errors, t8('The description is missing.') if !$self->description; + + return @errors; +} + +1; diff --git a/SL/DB/RequirementSpecDependency.pm b/SL/DB/RequirementSpecDependency.pm new file mode 100644 index 000000000..029c84eab --- /dev/null +++ b/SL/DB/RequirementSpecDependency.pm @@ -0,0 +1,13 @@ +# This file has been auto-generated only because it didn't exist. +# Feel free to modify it at will; it will not be overwritten automatically. + +package SL::DB::RequirementSpecDependency; + +use strict; + +use SL::DB::MetaSetup::RequirementSpecDependency; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +1; diff --git a/SL/DB/RequirementSpecItem.pm b/SL/DB/RequirementSpecItem.pm new file mode 100644 index 000000000..ee71d7c44 --- /dev/null +++ b/SL/DB/RequirementSpecItem.pm @@ -0,0 +1,10 @@ +package SL::DB::RequirementSpecItem; + +use strict; + +use SL::DB::MetaSetup::RequirementSpecItem; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +1; diff --git a/SL/DB/RequirementSpecPredefinedText.pm b/SL/DB/RequirementSpecPredefinedText.pm new file mode 100644 index 000000000..9faddcd1e --- /dev/null +++ b/SL/DB/RequirementSpecPredefinedText.pm @@ -0,0 +1,22 @@ +package SL::DB::RequirementSpecPredefinedText; + +use strict; + +use SL::DB::MetaSetup::RequirementSpecPredefinedText; +use SL::DB::Helper::ActsAsList; +use SL::Locale::String; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +sub validate { + my ($self) = @_; + + my @errors; + push @errors, t8('The title is missing.') if !$self->title; + push @errors, t8('The description is missing.') if !$self->description; + + return @errors; +} + +1; diff --git a/SL/DB/RequirementSpecRisk.pm b/SL/DB/RequirementSpecRisk.pm new file mode 100644 index 000000000..60409ba2d --- /dev/null +++ b/SL/DB/RequirementSpecRisk.pm @@ -0,0 +1,21 @@ +package SL::DB::RequirementSpecRisk; + +use strict; + +use SL::DB::MetaSetup::RequirementSpecRisk; +use SL::DB::Helper::ActsAsList; +use SL::Locale::String; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +sub validate { + my ($self) = @_; + + my @errors; + push @errors, t8('The description is missing.') if !$self->description; + + return @errors; +} + +1; diff --git a/SL/DB/RequirementSpecStatus.pm b/SL/DB/RequirementSpecStatus.pm new file mode 100644 index 000000000..1fc6f9961 --- /dev/null +++ b/SL/DB/RequirementSpecStatus.pm @@ -0,0 +1,23 @@ +package SL::DB::RequirementSpecStatus; + +use strict; + +use SL::DB::MetaSetup::RequirementSpecStatus; +use SL::DB::Helper::ActsAsList; +use SL::Locale::String; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +sub validate { + my ($self) = @_; + + my @errors; + + push @errors, t8('The name is missing.') if !$self->name; + push @errors, t8('The description is missing.') if !$self->description; + + return @errors; +} + +1; diff --git a/SL/DB/RequirementSpecTextBlock.pm b/SL/DB/RequirementSpecTextBlock.pm new file mode 100644 index 000000000..e417be77b --- /dev/null +++ b/SL/DB/RequirementSpecTextBlock.pm @@ -0,0 +1,24 @@ +package SL::DB::RequirementSpecTextBlock; + +use strict; + +use SL::DB::MetaSetup::RequirementSpecTextBlock; +# ActsAsList does not support position arguments grouped by other +# columns, e.g. by the requirement_spec_id in this case. So we cannot +# use it yet. +# use SL::DB::Helper::ActsAsList; +use SL::Locale::String; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +sub validate { + my ($self) = @_; + + my @errors; + push @errors, t8('The title is missing.') if !$self->title; + + return @errors; +} + +1; diff --git a/SL/DB/RequirementSpecType.pm b/SL/DB/RequirementSpecType.pm new file mode 100644 index 000000000..7ff6b32fa --- /dev/null +++ b/SL/DB/RequirementSpecType.pm @@ -0,0 +1,21 @@ +package SL::DB::RequirementSpecType; + +use strict; + +use SL::DB::MetaSetup::RequirementSpecType; +use SL::DB::Helper::ActsAsList; +use SL::Locale::String; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +sub validate { + my ($self) = @_; + + my @errors; + push @errors, t8('The description is missing.') if !$self->description; + + return @errors; +} + +1; diff --git a/SL/DB/RequirementSpecVersion.pm b/SL/DB/RequirementSpecVersion.pm new file mode 100644 index 000000000..e37b2c7d8 --- /dev/null +++ b/SL/DB/RequirementSpecVersion.pm @@ -0,0 +1,21 @@ +package SL::DB::RequirementSpecVersion; + +use strict; + +use SL::DB::MetaSetup::RequirementSpecVersion; +use SL::Locale::String; + +# Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. +__PACKAGE__->meta->make_manager_class; + +sub validate { + my ($self) = @_; + + my @errors; + push @errors, t8('The version number is missing.') if !$self->version_number; + push @errors, t8('The description is missing.') if !$self->description; + + return @errors; +} + +1; diff --git a/locale/de/all b/locale/de/all index 0a228c412..da7a7ea0e 100755 --- a/locale/de/all +++ b/locale/de/all @@ -2269,6 +2269,7 @@ $self->{texts} = { 'The task server was stopped successfully.' => 'Der Task-Server wurde erfolgreich beendet.', 'The third way is to download the module from the above mentioned URL and to install the module manually following the installations instructions contained in the source archive.' => 'Die dritte Variante besteht darin, das Paket von der oben genannten URL herunterzuladen und es manuell zu installieren. Beachten Sie dabei die im Paket enthaltenen Installationsanweisungen.', 'The three columns "make_X", "model_X" and "lastcost_X" with the same number "X" are used to import vendor part numbers and vendor prices.' => 'Die drei Spalten "make_X", "model_X" und "lastcost_X" mit derselben Nummer "X" werden zum Import von Lieferantenartikelnummern und -preisen genutzt.', + 'The title is missing.' => 'Der Titel fehlt.', 'The transaction is shown below in its current state.' => 'Nachfolgend wird angezeigt, wie die Buchung momentan aussieht.', 'The type is missing.' => 'Der Typ fehlt.', 'The unit has been saved.' => 'Die Einheit wurde gespeichert.', @@ -2285,6 +2286,7 @@ $self->{texts} = { 'The user has been deleted.' => 'Der Benutzer wurde gelöscht.', 'The user has been saved.' => 'Der Benutzer wurde gespeichert.', 'The variable name must only consist of letters, numbers and underscores. It must begin with a letter. Example: send_christmas_present' => 'Der Variablenname darf nur aus Zeichen (keine Umlaute), Ziffern und Unterstrichen bestehen. Er muss mit einem Buchstaben beginnen. Beispiel: weihnachtsgruss_verschicken', + 'The version number is missing.' => 'Die Versionsnummer fehlt.', 'The warehouse could not be deleted because it has already been used.' => 'Das Lager konnte nicht gelöscht werden, da es bereits in Benutzung war.', 'The warehouse does not contain any bins.' => 'Das Lager enthält keine Lagerplätze.', 'The warehouse or the bin is missing.' => 'Das Lager oder der Lagerplatz fehlen.', diff --git a/sql/Pg-upgrade2/requirement_specs.sql b/sql/Pg-upgrade2/requirement_specs.sql new file mode 100644 index 000000000..9271732e4 --- /dev/null +++ b/sql/Pg-upgrade2/requirement_specs.sql @@ -0,0 +1,391 @@ +-- @tag: requirement_specs +-- @description: Pflichtenhefte +-- @depends: release_3_0_0 +-- @charset: utf-8 + +-- Nur für Entwicklungszwecke: + +-- DELETE FROM schema_info WHERE tag = 'requirement_specs'; + +-- BEGIN; +-- DROP TABLE requirement_spec_item_dependencies; +-- DROP TABLE requirement_spec_items; +-- DROP TABLE requirement_spec_text_blocks; +-- DROP TABLE requirement_specs; +-- DROP TABLE requirement_spec_versions; +-- DROP TABLE requirement_spec_predefined_texts; +-- DROP TABLE requirement_spec_types; +-- DROP TABLE requirement_spec_statuses; +-- DROP TABLE requirement_spec_risks; +-- DROP TABLE requirement_spec_complexities; +-- DROP TABLE requirement_spec_acceptance_statuses; +-- ALTER TABLE customer DROP COLUMN hourly_rate; +-- ALTER TABLE defaults DROP COLUMN requirement_spec_section_number_format; +-- ALTER TABLE defaults DROP COLUMN requirement_spec_function_block_number_format; + +CREATE TABLE requirement_spec_acceptance_statuses ( + id SERIAL, + name TEXT NOT NULL, + description TEXT, + position INTEGER NOT NULL, + itime TIMESTAMP DEFAULT now(), + mtime TIMESTAMP, + + PRIMARY KEY (id), + UNIQUE (name, description) +); +CREATE TRIGGER mtime_requirement_spec_acceptance_statuses BEFORE UPDATE ON requirement_spec_acceptance_statuses FOR EACH ROW EXECUTE PROCEDURE set_mtime(); + +INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('accepted', 'Abgenommen', 1); +INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('accepted_with_defects', 'Mit Mängeln abgenommen', 2); +INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('accepted_with_defects_to_be_fixed', 'Mit noch zu behebenden Mängeln abgenommen', 3); +INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('not_accepted', 'Nicht abgenommen', 4); + + + +CREATE TABLE requirement_spec_complexities ( + id SERIAL, + description TEXT NOT NULL, + position INTEGER NOT NULL, + itime TIMESTAMP DEFAULT now(), + mtime TIMESTAMP, + + PRIMARY KEY (id), + UNIQUE (description) +); +CREATE TRIGGER mtime_requirement_spec_complexities BEFORE UPDATE ON requirement_spec_complexities FOR EACH ROW EXECUTE PROCEDURE set_mtime(); + +INSERT INTO requirement_spec_complexities (description, position) VALUES ('nicht bewertet', 1); +INSERT INTO requirement_spec_complexities (description, position) VALUES ('nur Anforderung', 2); +INSERT INTO requirement_spec_complexities (description, position) VALUES ('gering', 3); +INSERT INTO requirement_spec_complexities (description, position) VALUES ('mittel', 4); +INSERT INTO requirement_spec_complexities (description, position) VALUES ('hoch', 5); + + + +CREATE TABLE requirement_spec_risks ( + id SERIAL, + description TEXT NOT NULL, + position INTEGER NOT NULL, + itime TIMESTAMP DEFAULT now(), + mtime TIMESTAMP, + + PRIMARY KEY (id), + UNIQUE (description) +); +CREATE TRIGGER mtime_requirement_spec_risks BEFORE UPDATE ON requirement_spec_risks FOR EACH ROW EXECUTE PROCEDURE set_mtime(); + +INSERT INTO requirement_spec_risks (description, position) VALUES ('nicht bewertet', 1); +INSERT INTO requirement_spec_risks (description, position) VALUES ('nur Anforderung', 2); +INSERT INTO requirement_spec_risks (description, position) VALUES ('gering', 3); +INSERT INTO requirement_spec_risks (description, position) VALUES ('mittel', 4); +INSERT INTO requirement_spec_risks (description, position) VALUES ('hoch', 5); + + + +CREATE TABLE requirement_spec_statuses ( + id SERIAL, + name TEXT NOT NULL, + description TEXT NOT NULL, + position INTEGER NOT NULL, + itime TIMESTAMP DEFAULT now(), + mtime TIMESTAMP, + + PRIMARY KEY (id), + UNIQUE (name, description) +); +CREATE TRIGGER mtime_requirement_spec_statuses BEFORE UPDATE ON requirement_spec_statuses FOR EACH ROW EXECUTE PROCEDURE set_mtime(); + +INSERT INTO requirement_spec_statuses (name, description, position) VALUES ('planning', 'In Planung', 1); +INSERT INTO requirement_spec_statuses (name, description, position) VALUES ('running', 'In Bearbeitung', 2); +INSERT INTO requirement_spec_statuses (name, description, position) VALUES ('done', 'Fertiggestellt', 3); + + + +CREATE TABLE requirement_spec_types ( + id SERIAL, + description TEXT NOT NULL, + position INTEGER NOT NULL, + itime TIMESTAMP DEFAULT now(), + mtime TIMESTAMP, + + PRIMARY KEY (id), + UNIQUE (description) +); +CREATE TRIGGER mtime_requirement_spec_types BEFORE UPDATE ON requirement_spec_types FOR EACH ROW EXECUTE PROCEDURE set_mtime(); + +INSERT INTO requirement_spec_types (description, position) VALUES ('Pflichtenheft', 1); +INSERT INTO requirement_spec_types (description, position) VALUES ('Konzept', 2); + + + +CREATE TABLE requirement_spec_predefined_texts ( + id SERIAL, + description TEXT NOT NULL, + title TEXT NOT NULL, + text TEXT NOT NULL, + position INTEGER NOT NULL, + itime TIMESTAMP DEFAULT now(), + mtime TIMESTAMP, + + PRIMARY KEY (id), + UNIQUE (description) +); +CREATE TRIGGER mtime_requirement_spec_predefined_texts BEFORE UPDATE ON requirement_spec_predefined_texts FOR EACH ROW EXECUTE PROCEDURE set_mtime(); + + + +CREATE TABLE requirement_spec_versions ( + id SERIAL, + version_number INTEGER, + description TEXT NOT NULL, + comment TEXT, + order_date DATE, + order_number TEXT, + order_id INTEGER, + itime TIMESTAMP DEFAULT now(), + mtime TIMESTAMP, + + PRIMARY KEY (id), + FOREIGN KEY (order_id) REFERENCES oe (id) +); +CREATE TRIGGER mtime_requirement_spec_versions BEFORE UPDATE ON requirement_spec_versions FOR EACH ROW EXECUTE PROCEDURE set_mtime(); + + + +CREATE TABLE requirement_specs ( + id SERIAL, + type_id INTEGER NOT NULL, + status_id INTEGER NOT NULL, + version_id INTEGER, + customer_id INTEGER NOT NULL, + project_id INTEGER, + title TEXT NOT NULL, + hourly_rate NUMERIC(8, 2) NOT NULL DEFAULT 0, + net_sum NUMERIC(12, 2) NOT NULL DEFAULT 0, + working_copy_id INTEGER, + previous_section_number INTEGER NOT NULL, + previous_fb_number INTEGER NOT NULL, + is_template BOOLEAN DEFAULT FALSE, + itime TIMESTAMP DEFAULT now(), + mtime TIMESTAMP, + + PRIMARY KEY (id), + FOREIGN KEY (type_id) REFERENCES requirement_spec_types (id), + FOREIGN KEY (status_id) REFERENCES requirement_spec_statuses (id), + FOREIGN KEY (version_id) REFERENCES requirement_spec_versions (id), + FOREIGN KEY (working_copy_id) REFERENCES requirement_specs (id), + FOREIGN KEY (customer_id) REFERENCES customer (id), + FOREIGN KEY (project_id) REFERENCES project (id) +); +CREATE TRIGGER mtime_requirement_specs BEFORE UPDATE ON requirement_specs FOR EACH ROW EXECUTE PROCEDURE set_mtime(); + + + +CREATE TABLE requirement_spec_text_blocks ( + id SERIAL, + requirement_spec_id INTEGER NOT NULL, + title TEXT NOT NULL, + text TEXT, + position INTEGER NOT NULL, + output_position INTEGER NOT NULL DEFAULT 1, + itime TIMESTAMP NOT NULL DEFAULT now(), + mtime TIMESTAMP, + + PRIMARY KEY (id), + FOREIGN KEY (requirement_spec_id) REFERENCES requirement_specs (id) +); +CREATE TRIGGER mtime_requirement_spec_text_blocks BEFORE UPDATE ON requirement_spec_text_blocks FOR EACH ROW EXECUTE PROCEDURE set_mtime(); + + +CREATE TABLE requirement_spec_items ( + id SERIAL, + requirement_spec_id INTEGER NOT NULL, + item_type TEXT NOT NULL, + parent_id INTEGER, + position INTEGER NOT NULL, + fb_number TEXT NOT NULL, + title TEXT, + description TEXT, + complexity_id INTEGER, + risk_id INTEGER, + time_estimation NUMERIC(12, 2) NOT NULL DEFAULT 0, + net_sum NUMERIC(12, 2) NOT NULL DEFAULT 0, + is_flagged BOOLEAN NOT NULL DEFAULT FALSE, + acceptance_status_id INTEGER, + acceptance_text TEXT, + itime TIMESTAMP NOT NULL DEFAULT now(), + mtime TIMESTAMP, + + PRIMARY KEY (id), + FOREIGN KEY (requirement_spec_id) REFERENCES requirement_specs (id), + FOREIGN KEY (parent_id) REFERENCES requirement_spec_items (id), + FOREIGN KEY (complexity_id) REFERENCES requirement_spec_complexities (id), + FOREIGN KEY (risk_id) REFERENCES requirement_spec_risks (id), + FOREIGN KEY (acceptance_status_id) REFERENCES requirement_spec_acceptance_statuses (id), + + CONSTRAINT valid_item_type CHECK ((item_type = 'section') OR (item_type = 'function-block') OR (item_type = 'sub-function-block')), + CONSTRAINT valid_parent_id_for_item_type CHECK (CASE + WHEN (item_type = 'section') THEN parent_id IS NULL + ELSE parent_id IS NOT NULL + END) +); +CREATE TRIGGER mtime_requirement_spec_items BEFORE UPDATE ON requirement_spec_items FOR EACH ROW EXECUTE PROCEDURE set_mtime(); + + + +CREATE TABLE requirement_spec_item_dependencies ( + depending_item_id INTEGER NOT NULL, + depended_item_id INTEGER NOT NULL, + + PRIMARY KEY (depending_item_id, depended_item_id), + FOREIGN KEY (depending_item_id) REFERENCES requirement_spec_items (id), + FOREIGN KEY (depended_item_id) REFERENCES requirement_spec_items (id) +); + +ALTER TABLE customer ADD COLUMN hourly_rate NUMERIC(8, 2); + + +-- 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; + parent RECORD; + BEGIN + IF item_id IS NULL THEN + RAISE DEBUG 'updateRSIE: item_id IS NULL'; + RETURN FALSE; + END IF; + + SELECT * INTO item FROM requirement_spec_items WHERE id = item_id; + RAISE DEBUG 'updateRSIE: item_id % parent_id %', item_id, item.parent_id; + + IF item.parent_id IS NULL THEN + -- Don't do anything for sections. + RAISE DEBUG 'updateRSIE: this is a section.'; + RETURN FALSE; + END IF; + + SELECT * INTO parent FROM requirement_spec_items WHERE id = item.parent_id; + RAISE DEBUG 'updateRSIE: parent_id of parent of item: %', parent.parent_id; + + IF parent.parent_id IS NOT NULL THEN + -- Don't do anything for sub function blocks. + RAISE DEBUG 'updateRSIE: this is sub function block.'; + 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; + + RETURN TRUE; + END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION requirement_spec_item_time_estimation_updater_trigger() RETURNS trigger AS $$ + BEGIN + IF ((TG_OP = 'UPDATE') OR (TG_OP = 'DELETE')) THEN + PERFORM update_requirement_spec_item_time_estimation(OLD.parent_id); + END IF; + IF ((TG_OP = 'UPDATE') OR (TG_OP = 'INSERT')) THEN + PERFORM update_requirement_spec_item_time_estimation(NEW.parent_id); + END IF; + RETURN NULL; + END; +$$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS update_requirement_spec_item_time_estimation ON requirement_spec_items; +CREATE TRIGGER update_requirement_spec_item_time_estimation +AFTER INSERT OR UPDATE OR DELETE ON requirement_spec_items +FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_time_estimation_updater_trigger(); + + +-- Trigger for deleting depending stuff if a requirement spec item is deleted. +CREATE OR REPLACE FUNCTION requirement_spec_item_before_delete_trigger() RETURNS trigger AS $$ + BEGIN + RAISE DEBUG 'delete trig RSitem old id %', OLD.id; + DELETE FROM requirement_spec_item_dependencies WHERE (depending_item_id = OLD.id) OR (depended_item_id = OLD.id); + DELETE FROM requirement_spec_items WHERE (parent_id = OLD.id); + + RETURN OLD; + END; +$$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS delete_requirement_spec_item_dependencies ON requirement_spec_items; +CREATE TRIGGER delete_requirement_spec_item_dependencies +BEFORE DELETE ON requirement_spec_items +FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_before_delete_trigger(); + + +-- Trigger for deleting depending stuff if a requirement spec is deleted. +CREATE OR REPLACE FUNCTION requirement_spec_delete_trigger() RETURNS trigger AS $$ + DECLARE + tname TEXT; + BEGIN + tname := 'tmp_delete_reqspec' || OLD.id; + + IF TG_WHEN = 'AFTER' THEN + RAISE DEBUG 'after trigger on %; deleting from versions', OLD.id; + EXECUTE 'DELETE FROM requirement_spec_versions ' || + 'WHERE id IN (SELECT version_id FROM ' || tname || ')'; + + RAISE DEBUG ' dropping table'; + EXECUTE 'DROP TABLE ' || tname; + + RETURN OLD; + END IF; + + RAISE DEBUG 'before delete trigger on %', OLD.id; + + EXECUTE 'CREATE TEMPORARY TABLE ' || tname || ' AS ' || + 'SELECT DISTINCT version_id ' || + 'FROM requirement_specs ' || + 'WHERE (version_id IS NOT NULL) ' || + ' AND ((id = ' || OLD.id || ') OR (working_copy_id = ' || OLD.id || '))'; + + RAISE DEBUG ' Updating version_id and items for %', OLD.id; + UPDATE requirement_specs SET version_id = NULL WHERE (id <> OLD.id) AND (working_copy_id = OLD.id); + UPDATE requirement_spec_items SET item_type = 'section', parent_id = NULL WHERE requirement_spec_id = OLD.id; + + RAISE DEBUG ' Deleting stuff for %', OLD.id; + + DELETE FROM requirement_spec_text_blocks WHERE (requirement_spec_id = OLD.id); + DELETE FROM requirement_spec_items WHERE (requirement_spec_id = OLD.id); + DELETE FROM requirement_specs WHERE (working_copy_id = OLD.id); + + RAISE DEBUG ' And we out for %', OLD.id; + + RETURN OLD; + END; +$$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS delete_requirement_spec_dependencies ON requirement_specs; +CREATE TRIGGER delete_requirement_spec_dependencies +BEFORE DELETE ON requirement_specs +FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger(); + +DROP TRIGGER IF EXISTS after_delete_requirement_spec_dependencies ON requirement_specs; +CREATE TRIGGER after_delete_requirement_spec_dependencies +AFTER DELETE ON requirement_specs +FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger(); + + +-- Add formats for section/function block numbers to defaults +ALTER TABLE defaults ADD COLUMN requirement_spec_section_number_format TEXT; +ALTER TABLE defaults ALTER COLUMN requirement_spec_section_number_format SET DEFAULT 'A00'; +ALTER TABLE defaults ADD COLUMN requirement_spec_function_block_number_format TEXT; +ALTER TABLE defaults ALTER COLUMN requirement_spec_function_block_number_format SET DEFAULT 'FB000'; + +UPDATE defaults SET requirement_spec_section_number_format = 'A00'; +UPDATE defaults SET requirement_spec_function_block_number_format = 'FB000'; + +ALTER TABLE defaults ALTER COLUMN requirement_spec_section_number_format SET NOT NULL; +ALTER TABLE defaults ALTER COLUMN requirement_spec_function_block_number_format SET NOT NULL; -- 2.20.1