From 687a09d87aee39ab016bf2ec410da006f5044bc4 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Tue, 9 Jul 2013 10:41:57 +0200 Subject: [PATCH] Pflichtenheft-Angebot/Auftrag: DB-Schema & Models --- SL/DB/Helper/ALL.pm | 1 + SL/DB/Helper/Mappings.pm | 1 + SL/DB/Manager/RequirementSpecOrder.pm | 15 +++++++ SL/DB/MetaSetup/Default.pm | 6 +++ SL/DB/MetaSetup/RequirementSpecItem.pm | 6 +++ SL/DB/MetaSetup/RequirementSpecOrder.pm | 44 ++++++++++++++++++++ SL/DB/MetaSetup/RequirementSpecVersion.pm | 10 ----- SL/DB/RequirementSpecOrder.pm | 13 ++++++ sql/Pg-upgrade2/requirement_specs_orders.sql | 35 ++++++++++++++++ 9 files changed, 121 insertions(+), 10 deletions(-) create mode 100644 SL/DB/Manager/RequirementSpecOrder.pm create mode 100644 SL/DB/MetaSetup/RequirementSpecOrder.pm create mode 100644 SL/DB/RequirementSpecOrder.pm create mode 100644 sql/Pg-upgrade2/requirement_specs_orders.sql diff --git a/SL/DB/Helper/ALL.pm b/SL/DB/Helper/ALL.pm index 190a52e9e..5e78a731f 100644 --- a/SL/DB/Helper/ALL.pm +++ b/SL/DB/Helper/ALL.pm @@ -81,6 +81,7 @@ use SL::DB::RequirementSpecAcceptanceStatus; use SL::DB::RequirementSpecComplexity; use SL::DB::RequirementSpecDependency; use SL::DB::RequirementSpecItem; +use SL::DB::RequirementSpecOrder; use SL::DB::RequirementSpecPredefinedText; use SL::DB::RequirementSpecRisk; use SL::DB::RequirementSpecStatus; diff --git a/SL/DB/Helper/Mappings.pm b/SL/DB/Helper/Mappings.pm index 6d7d8d068..3ccfb577f 100644 --- a/SL/DB/Helper/Mappings.pm +++ b/SL/DB/Helper/Mappings.pm @@ -161,6 +161,7 @@ my %kivitendo_package_names = ( requirement_spec_complexities => 'RequirementSpecComplexity', requirement_spec_item_dependencies => 'RequirementSpecDependency', requirement_spec_items => 'RequirementSpecItem', + requirement_spec_orders => 'RequirementSpecOrder', requirement_spec_predefined_texts => 'RequirementSpecPredefinedText', requirement_spec_risks => 'RequirementSpecRisk', requirement_spec_statuses => 'RequirementSpecStatus', diff --git a/SL/DB/Manager/RequirementSpecOrder.pm b/SL/DB/Manager/RequirementSpecOrder.pm new file mode 100644 index 000000000..d2a1086c7 --- /dev/null +++ b/SL/DB/Manager/RequirementSpecOrder.pm @@ -0,0 +1,15 @@ +# 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::Manager::RequirementSpecOrder; + +use strict; + +use SL::DB::Helper::Manager; +use base qw(SL::DB::Helper::Manager); + +sub object_class { 'SL::DB::RequirementSpecOrder' } + +__PACKAGE__->make_manager_methods; + +1; diff --git a/SL/DB/MetaSetup/Default.pm b/SL/DB/MetaSetup/Default.pm index c8f4ed408..5b547efc4 100644 --- a/SL/DB/MetaSetup/Default.pm +++ b/SL/DB/MetaSetup/Default.pm @@ -69,6 +69,7 @@ __PACKAGE__->meta->columns( purchase_order_show_delete => { type => 'boolean', default => 'true' }, requirement_spec_function_block_number_format => { type => 'text', default => 'FB000', not_null => 1 }, requirement_spec_section_number_format => { type => 'text', default => 'A00', not_null => 1 }, + requirement_spec_section_order_part_id => { type => 'integer' }, revtrans => { type => 'boolean', default => 'false' }, rfqnumber => { type => 'text' }, rmanumber => { type => 'text' }, @@ -117,6 +118,11 @@ __PACKAGE__->meta->foreign_keys( key_columns => { currency_id => 'id' }, }, + requirement_spec_section_order_part => { + class => 'SL::DB::Part', + key_columns => { requirement_spec_section_order_part_id => 'id' }, + }, + warehouse => { class => 'SL::DB::Warehouse', key_columns => { warehouse_id => 'id' }, diff --git a/SL/DB/MetaSetup/RequirementSpecItem.pm b/SL/DB/MetaSetup/RequirementSpecItem.pm index dfce58166..63e4eecda 100644 --- a/SL/DB/MetaSetup/RequirementSpecItem.pm +++ b/SL/DB/MetaSetup/RequirementSpecItem.pm @@ -19,6 +19,7 @@ __PACKAGE__->meta->columns( item_type => { type => 'text', not_null => 1 }, itime => { type => 'timestamp', default => 'now()', not_null => 1 }, mtime => { type => 'timestamp' }, + order_part_id => { type => 'integer' }, parent_id => { type => 'integer' }, position => { type => 'integer', not_null => 1 }, requirement_spec_id => { type => 'integer', not_null => 1 }, @@ -42,6 +43,11 @@ __PACKAGE__->meta->foreign_keys( key_columns => { complexity_id => 'id' }, }, + order_part => { + class => 'SL::DB::Part', + key_columns => { order_part_id => 'id' }, + }, + parent => { class => 'SL::DB::RequirementSpecItem', key_columns => { parent_id => 'id' }, diff --git a/SL/DB/MetaSetup/RequirementSpecOrder.pm b/SL/DB/MetaSetup/RequirementSpecOrder.pm new file mode 100644 index 000000000..33499bfd8 --- /dev/null +++ b/SL/DB/MetaSetup/RequirementSpecOrder.pm @@ -0,0 +1,44 @@ +# This file has been auto-generated. Do not modify it; it will be overwritten +# by rose_auto_create_model.pl automatically. +package SL::DB::RequirementSpecOrder; + +use strict; + +use base qw(SL::DB::Object); + +__PACKAGE__->meta->table('requirement_spec_orders'); + +__PACKAGE__->meta->columns( + id => { type => 'serial', not_null => 1 }, + itime => { type => 'timestamp', default => 'now()', not_null => 1 }, + mtime => { type => 'timestamp', default => 'now()', not_null => 1 }, + order_id => { type => 'integer', not_null => 1 }, + requirement_spec_id => { type => 'integer', not_null => 1 }, + version_id => { type => 'integer' }, +); + +__PACKAGE__->meta->primary_key_columns([ 'id' ]); + +__PACKAGE__->meta->unique_keys([ 'requirement_spec_id', 'order_id' ]); + +__PACKAGE__->meta->allow_inline_column_values(1); + +__PACKAGE__->meta->foreign_keys( + order => { + class => 'SL::DB::Order', + key_columns => { order_id => 'id' }, + }, + + requirement_spec => { + class => 'SL::DB::RequirementSpec', + key_columns => { requirement_spec_id => 'id' }, + }, + + version => { + class => 'SL::DB::RequirementSpecVersion', + key_columns => { version_id => 'id' }, + }, +); + +1; +; diff --git a/SL/DB/MetaSetup/RequirementSpecVersion.pm b/SL/DB/MetaSetup/RequirementSpecVersion.pm index fd0dddf21..3c285e30a 100644 --- a/SL/DB/MetaSetup/RequirementSpecVersion.pm +++ b/SL/DB/MetaSetup/RequirementSpecVersion.pm @@ -14,9 +14,6 @@ __PACKAGE__->meta->columns( id => { type => 'serial', not_null => 1 }, itime => { type => 'timestamp', default => 'now()' }, mtime => { type => 'timestamp' }, - order_date => { type => 'date' }, - order_id => { type => 'integer' }, - order_number => { type => 'text' }, version_number => { type => 'integer' }, ); @@ -24,12 +21,5 @@ __PACKAGE__->meta->primary_key_columns([ 'id' ]); __PACKAGE__->meta->allow_inline_column_values(1); -__PACKAGE__->meta->foreign_keys( - order => { - class => 'SL::DB::Order', - key_columns => { order_id => 'id' }, - }, -); - 1; ; diff --git a/SL/DB/RequirementSpecOrder.pm b/SL/DB/RequirementSpecOrder.pm new file mode 100644 index 000000000..7a31eaea1 --- /dev/null +++ b/SL/DB/RequirementSpecOrder.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::RequirementSpecOrder; + +use strict; + +use SL::DB::MetaSetup::RequirementSpecOrder; +use SL::DB::Manager::RequirementSpecOrder; + +__PACKAGE__->meta->initialize; + +1; diff --git a/sql/Pg-upgrade2/requirement_specs_orders.sql b/sql/Pg-upgrade2/requirement_specs_orders.sql new file mode 100644 index 000000000..92deba257 --- /dev/null +++ b/sql/Pg-upgrade2/requirement_specs_orders.sql @@ -0,0 +1,35 @@ +-- @tag: requirement_specs_orders +-- @description: requirement_specs_orders +-- @depends: requirement_specs requirement_specs_section_templates + +-- Remove unneeded columns +ALTER TABLE requirement_spec_versions DROP CONSTRAINT requirement_spec_versions_order_id_fkey; + +ALTER TABLE requirement_spec_versions DROP COLUMN order_date; +ALTER TABLE requirement_spec_versions DROP COLUMN order_number; +ALTER TABLE requirement_spec_versions DROP COLUMN order_id; + +-- Add new columns to existing tables +ALTER TABLE requirement_spec_items ADD COLUMN order_part_id INTEGER; +ALTER TABLE requirement_spec_items ADD FOREIGN KEY (order_part_id) REFERENCES parts (id) ON DELETE SET NULL; + +ALTER TABLE defaults ADD COLUMN requirement_spec_section_order_part_id INTEGER; +ALTER TABLE defaults ADD FOREIGN KEY (requirement_spec_section_order_part_id) REFERENCES parts (id) ON DELETE SET NULL; + +-- Create new tables +CREATE TABLE requirement_spec_orders ( + id SERIAL, + requirement_spec_id INTEGER NOT NULL, + order_id INTEGER NOT NULL, + version_id INTEGER, + itime TIMESTAMP NOT NULL DEFAULT now(), + mtime TIMESTAMP NOT NULL DEFAULT now(), + + PRIMARY KEY (id), + FOREIGN KEY (requirement_spec_id) REFERENCES requirement_specs (id) ON DELETE CASCADE, + FOREIGN KEY (order_id) REFERENCES oe (id) ON DELETE CASCADE, + FOREIGN KEY (version_id) REFERENCES requirement_spec_versions (id) ON DELETE SET NULL, + CONSTRAINT requirement_spec_id_order_id_unique UNIQUE (requirement_spec_id, order_id) +); + +CREATE TRIGGER mtime_requirement_spec_orders BEFORE UPDATE ON requirement_spec_orders FOR EACH ROW EXECUTE PROCEDURE set_mtime(); -- 2.20.1