From 4f15333b8cc0ae1b0b4089adc2e516ba002b298b Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Thu, 17 Jan 2013 15:18:03 +0100 Subject: [PATCH] =?utf8?q?Fremdschl=C3=BCssel=20f=C3=BCr=20Tabellen=20oe,?= =?utf8?q?=20delivery=5Forders,=20parts,=20translation?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/DB/DeliveryOrder.pm | 8 ---- SL/DB/MetaSetup/DeliveryOrder.pm | 10 +++++ SL/DB/MetaSetup/Order.pm | 42 ++++++++++++++++++- SL/DB/MetaSetup/Part.pm | 15 +++++++ SL/DB/MetaSetup/Translation.pm | 7 ++++ SL/DB/Order.pm | 28 +------------ SL/DB/Part.pm | 10 ----- .../oe_delivery_orders_foreign_keys.sql | 28 +++++++++++++ .../parts_translation_foreign_keys.sql | 14 +++++++ 9 files changed, 117 insertions(+), 45 deletions(-) create mode 100644 sql/Pg-upgrade2/oe_delivery_orders_foreign_keys.sql create mode 100644 sql/Pg-upgrade2/parts_translation_foreign_keys.sql diff --git a/SL/DB/DeliveryOrder.pm b/SL/DB/DeliveryOrder.pm index 9c860bf7e..a48dad74c 100644 --- a/SL/DB/DeliveryOrder.pm +++ b/SL/DB/DeliveryOrder.pm @@ -15,14 +15,6 @@ __PACKAGE__->meta->add_relationship(orderitems => { type => 'one to many column_map => { id => 'delivery_order_id' }, manager_args => { with_objects => [ 'parts' ] } }, - shipto => { type => 'one to one', - class => 'SL::DB::Shipto', - column_map => { shipto_id => 'shipto_id' }, - }, - department => { type => 'one to one', - class => 'SL::DB::Department', - column_map => { department_id => 'id' }, - }, ); __PACKAGE__->meta->initialize; diff --git a/SL/DB/MetaSetup/DeliveryOrder.pm b/SL/DB/MetaSetup/DeliveryOrder.pm index fb4b82818..523312160 100644 --- a/SL/DB/MetaSetup/DeliveryOrder.pm +++ b/SL/DB/MetaSetup/DeliveryOrder.pm @@ -57,6 +57,11 @@ __PACKAGE__->meta->setup( key_columns => { customer_id => 'id' }, }, + department => { + class => 'SL::DB::Department', + key_columns => { department_id => 'id' }, + }, + employee => { class => 'SL::DB::Employee', key_columns => { employee_id => 'id' }, @@ -77,6 +82,11 @@ __PACKAGE__->meta->setup( key_columns => { salesman_id => 'id' }, }, + shipto => { + class => 'SL::DB::Shipto', + key_columns => { shipto_id => 'shipto_id' }, + }, + vendor => { class => 'SL::DB::Vendor', key_columns => { vendor_id => 'id' }, diff --git a/SL/DB/MetaSetup/Order.pm b/SL/DB/MetaSetup/Order.pm index 3595026b0..de072ec70 100644 --- a/SL/DB/MetaSetup/Order.pm +++ b/SL/DB/MetaSetup/Order.pm @@ -28,7 +28,7 @@ __PACKAGE__->meta->setup( quonumber => { type => 'text' }, cusordnumber => { type => 'text' }, intnotes => { type => 'text' }, - department_id => { type => 'integer', default => '0' }, + department_id => { type => 'integer' }, itime => { type => 'timestamp', default => 'now()' }, mtime => { type => 'timestamp' }, shipvia => { type => 'text' }, @@ -53,26 +53,66 @@ __PACKAGE__->meta->setup( allow_inline_column_values => 1, foreign_keys => [ + contact => { + class => 'SL::DB::Contact', + key_columns => { cp_id => 'cp_id' }, + }, + customer => { class => 'SL::DB::Customer', key_columns => { customer_id => 'id' }, }, + delivery_customer => { + class => 'SL::DB::Customer', + key_columns => { delivery_customer_id => 'id' }, + }, + + delivery_vendor => { + class => 'SL::DB::Vendor', + key_columns => { delivery_vendor_id => 'id' }, + }, + + department => { + class => 'SL::DB::Department', + key_columns => { department_id => 'id' }, + }, + employee => { class => 'SL::DB::Employee', key_columns => { employee_id => 'id' }, }, + employee_obj => { + class => 'SL::DB::Employee', + key_columns => { employee_id => 'id' }, + }, + globalproject => { class => 'SL::DB::Project', key_columns => { globalproject_id => 'id' }, }, + language => { + class => 'SL::DB::Language', + key_columns => { language_id => 'id' }, + }, + + payment => { + class => 'SL::DB::PaymentTerm', + key_columns => { payment_id => 'id' }, + }, + salesman => { class => 'SL::DB::Employee', key_columns => { salesman_id => 'id' }, }, + shipto => { + class => 'SL::DB::Shipto', + key_columns => { shipto_id => 'shipto_id' }, + }, + vendor => { class => 'SL::DB::Vendor', key_columns => { vendor_id => 'id' }, diff --git a/SL/DB/MetaSetup/Part.pm b/SL/DB/MetaSetup/Part.pm index d4e142b74..c94c66c38 100644 --- a/SL/DB/MetaSetup/Part.pm +++ b/SL/DB/MetaSetup/Part.pm @@ -60,6 +60,21 @@ __PACKAGE__->meta->setup( key_columns => { buchungsgruppen_id => 'id' }, }, + partsgroup => { + class => 'SL::DB::PartsGroup', + key_columns => { partsgroup_id => 'id' }, + }, + + payment => { + class => 'SL::DB::PaymentTerm', + key_columns => { payment_id => 'id' }, + }, + + price_factor => { + class => 'SL::DB::PriceFactor', + key_columns => { price_factor_id => 'id' }, + }, + unit_obj => { class => 'SL::DB::Unit', key_columns => { unit => 'name' }, diff --git a/SL/DB/MetaSetup/Translation.pm b/SL/DB/MetaSetup/Translation.pm index acd9606ac..54165bb77 100644 --- a/SL/DB/MetaSetup/Translation.pm +++ b/SL/DB/MetaSetup/Translation.pm @@ -22,6 +22,13 @@ __PACKAGE__->meta->setup( primary_key_columns => [ 'id' ], allow_inline_column_values => 1, + + foreign_keys => [ + language => { + class => 'SL::DB::Language', + key_columns => { language_id => 'id' }, + }, + ], ); 1; diff --git a/SL/DB/Order.pm b/SL/DB/Order.pm index b5e8c90f8..b3edda5dc 100644 --- a/SL/DB/Order.pm +++ b/SL/DB/Order.pm @@ -31,38 +31,14 @@ __PACKAGE__->meta->add_relationship( class => 'SL::DB::PeriodicInvoicesConfig', column_map => { id => 'oe_id' }, }, - payment_term => { - type => 'one to one', - class => 'SL::DB::PaymentTerm', - column_map => { payment_id => 'id' }, - }, - contact => { - type => 'one to one', - class => 'SL::DB::Contact', - column_map => { cp_id => 'cp_id' }, - }, - shipto => { - type => 'one to one', - class => 'SL::DB::Shipto', - column_map => { shipto_id => 'shipto_id' }, - }, - department => { - type => 'one to one', - class => 'SL::DB::Department', - column_map => { department_id => 'id' }, - }, - language => { - type => 'one to one', - class => 'SL::DB::Language', - column_map => { language_id => 'id' }, - }, ); __PACKAGE__->meta->initialize; # methods -sub items { goto &orderitems; } +sub items { goto &orderitems; } +sub payment_terms { goto &payment; } sub type { my $self = shift; diff --git a/SL/DB/Part.pm b/SL/DB/Part.pm index 1ef2c1b68..5c795f197 100644 --- a/SL/DB/Part.pm +++ b/SL/DB/Part.pm @@ -21,16 +21,6 @@ __PACKAGE__->meta->add_relationships( class => 'SL::DB::Assembly', column_map => { id => 'id' }, }, - partsgroup => { - type => 'one to one', - class => 'SL::DB::PartsGroup', - column_map => { partsgroup_id => 'id' }, - }, - price_factor => { - type => 'one to one', - class => 'SL::DB::PriceFactor', - column_map => { price_factor_id => 'id' }, - }, prices => { type => 'one to many', class => 'SL::DB::Price', diff --git a/sql/Pg-upgrade2/oe_delivery_orders_foreign_keys.sql b/sql/Pg-upgrade2/oe_delivery_orders_foreign_keys.sql new file mode 100644 index 000000000..2e2e8c971 --- /dev/null +++ b/sql/Pg-upgrade2/oe_delivery_orders_foreign_keys.sql @@ -0,0 +1,28 @@ +-- @tag: oe_delivery_orders_foreign_keys +-- @description: Fremdschlüsseldefinitionen für oe und delivery_orders +-- @depends: release_3_0_0 +-- @charset: utf-8 +ALTER TABLE oe ALTER COLUMN department_id DROP DEFAULT; + +UPDATE oe SET cp_id = NULL WHERE (cp_id IS NOT NULL) AND (cp_id NOT IN (SELECT cp_id FROM contacts)); +UPDATE oe SET delivery_customer_id = NULL WHERE (delivery_customer_id IS NOT NULL) AND (delivery_customer_id NOT IN (SELECT id FROM customer)); +UPDATE oe SET delivery_vendor_id = NULL WHERE (delivery_vendor_id IS NOT NULL) AND (delivery_vendor_id NOT IN (SELECT id FROM vendor)); +UPDATE oe SET department_id = NULL WHERE (department_id IS NOT NULL) AND (department_id NOT IN (SELECT id FROM department)); +UPDATE oe SET language_id = NULL WHERE (language_id IS NOT NULL) AND (language_id NOT IN (SELECT id FROM language)); +UPDATE oe SET payment_id = NULL WHERE (payment_id IS NOT NULL) AND (payment_id NOT IN (SELECT id FROM payment_terms)); +UPDATE oe SET shipto_id = NULL WHERE (shipto_id IS NOT NULL) AND (shipto_id NOT IN (SELECT shipto_id FROM shipto)); + +UPDATE delivery_orders SET department_id = NULL WHERE (department_id IS NOT NULL) AND (department_id NOT IN (SELECT id FROM department)); +UPDATE delivery_orders SET shipto_id = NULL WHERE (shipto_id IS NOT NULL) AND (shipto_id NOT IN (SELECT shipto_id FROM shipto)); + +ALTER TABLE oe ADD FOREIGN KEY (cp_id) REFERENCES contacts (cp_id); +ALTER TABLE oe ADD FOREIGN KEY (delivery_customer_id) REFERENCES customer (id); +ALTER TABLE oe ADD FOREIGN KEY (delivery_vendor_id) REFERENCES vendor (id); +ALTER TABLE oe ADD FOREIGN KEY (department_id) REFERENCES department (id); +ALTER TABLE oe ADD FOREIGN KEY (employee_id) REFERENCES employee (id); +ALTER TABLE oe ADD FOREIGN KEY (language_id) REFERENCES language (id); +ALTER TABLE oe ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id); +ALTER TABLE oe ADD FOREIGN KEY (shipto_id) REFERENCES shipto (shipto_id); + +ALTER TABLE delivery_orders ADD FOREIGN KEY (department_id) REFERENCES department (id); +ALTER TABLE delivery_orders ADD FOREIGN KEY (shipto_id) REFERENCES shipto (shipto_id); diff --git a/sql/Pg-upgrade2/parts_translation_foreign_keys.sql b/sql/Pg-upgrade2/parts_translation_foreign_keys.sql new file mode 100644 index 000000000..77b6a7296 --- /dev/null +++ b/sql/Pg-upgrade2/parts_translation_foreign_keys.sql @@ -0,0 +1,14 @@ +-- @tag: parts_translation_foreign_keys +-- @description: Fremdschlüsseldefinitionen für parts, translation +-- @depends: release_3_0_0 +-- @charset: utf-8 + +UPDATE parts SET partsgroup_id = NULL WHERE (partsgroup_id IS NOT NULL) AND (partsgroup_id NOT IN (SELECT id FROM partsgroup)); +UPDATE parts SET payment_id = NULL WHERE (payment_id IS NOT NULL) AND (payment_id NOT IN (SELECT id FROM payment_terms)); +UPDATE parts SET price_factor_id = NULL WHERE (price_factor_id IS NOT NULL) AND (price_factor_id NOT IN (SELECT id FROM price_factors)); + +ALTER TABLE parts ADD FOREIGN KEY (partsgroup_id) REFERENCES partsgroup (id); +ALTER TABLE parts ADD FOREIGN KEY (price_factor_id) REFERENCES price_factors (id); +ALTER TABLE parts ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id); + +ALTER TABLE translation ADD FOREIGN KEY (language_id) REFERENCES language (id); -- 2.20.1