From 91bd08d8315e733c3390088f3187b4c5b1fb9a52 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Tue, 15 Jan 2013 13:49:37 +0100 Subject: [PATCH] =?utf8?q?Diverse=20Fremdschl=C3=BCssel=20f=C3=BCr=20Tabel?= =?utf8?q?len=20ar=20und=20ap?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/DB/Invoice.pm | 33 ++------------------------ SL/DB/MetaSetup/Invoice.pm | 32 ++++++++++++++++++++++++- SL/DB/MetaSetup/PurchaseInvoice.pm | 27 ++++++++++++++++++++- SL/DB/PurchaseInvoice.pm | 3 ++- sql/Pg-upgrade2/ar_ap_foreign_keys.sql | 32 +++++++++++++++++++++++++ 5 files changed, 93 insertions(+), 34 deletions(-) create mode 100644 sql/Pg-upgrade2/ar_ap_foreign_keys.sql diff --git a/SL/DB/Invoice.pm b/SL/DB/Invoice.pm index c66584135..ff821ef75 100644 --- a/SL/DB/Invoice.pm +++ b/SL/DB/Invoice.pm @@ -28,43 +28,14 @@ __PACKAGE__->meta->add_relationship( with_objects => [ 'part' ] } }, - 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' }, - }, - employee => { - type => 'one to one', - class => 'SL::DB::Employee', - column_map => { employee_id => 'id' }, - }, ); __PACKAGE__->meta->initialize; # methods -sub items { goto &invoiceitems; } +sub items { goto &invoiceitems; } +sub payment_term { goto &payment; } # it is assumed, that ordnumbers are unique here. sub first_order_by_ordnumber { diff --git a/SL/DB/MetaSetup/Invoice.pm b/SL/DB/MetaSetup/Invoice.pm index 839c67bfe..1111a980b 100644 --- a/SL/DB/MetaSetup/Invoice.pm +++ b/SL/DB/MetaSetup/Invoice.pm @@ -32,7 +32,7 @@ __PACKAGE__->meta->setup( quonumber => { type => 'text' }, cusordnumber => { type => 'text' }, intnotes => { type => 'text' }, - department_id => { type => 'integer', default => '0' }, + department_id => { type => 'integer' }, shipvia => { type => 'text' }, itime => { type => 'timestamp', default => 'now()' }, mtime => { type => 'timestamp' }, @@ -64,26 +64,56 @@ __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' }, }, + department => { + class => 'SL::DB::Department', + key_columns => { department_id => 'id' }, + }, + dunning_config => { class => 'SL::DB::DunningConfig', key_columns => { dunning_config_id => 'id' }, }, + employee => { + 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' }, + }, + storno_obj => { class => 'SL::DB::Invoice', key_columns => { storno_id => 'id' }, diff --git a/SL/DB/MetaSetup/PurchaseInvoice.pm b/SL/DB/MetaSetup/PurchaseInvoice.pm index eb18f7058..636a5d823 100644 --- a/SL/DB/MetaSetup/PurchaseInvoice.pm +++ b/SL/DB/MetaSetup/PurchaseInvoice.pm @@ -28,7 +28,7 @@ __PACKAGE__->meta->setup( employee_id => { type => 'integer' }, quonumber => { 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' }, @@ -50,11 +50,36 @@ __PACKAGE__->meta->setup( allow_inline_column_values => 1, foreign_keys => [ + contact => { + class => 'SL::DB::Contact', + key_columns => { cp_id => 'cp_id' }, + }, + + department => { + class => 'SL::DB::Department', + key_columns => { department_id => 'id' }, + }, + + employee => { + 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' }, + }, + storno_obj => { class => 'SL::DB::PurchaseInvoice', key_columns => { storno_id => 'id' }, diff --git a/SL/DB/PurchaseInvoice.pm b/SL/DB/PurchaseInvoice.pm index 5123d681e..9c6f54e86 100644 --- a/SL/DB/PurchaseInvoice.pm +++ b/SL/DB/PurchaseInvoice.pm @@ -17,6 +17,7 @@ __PACKAGE__->meta->add_relationship(invoiceitems => { type => 'one to ma __PACKAGE__->meta->initialize; -sub items { goto &invoiceitems; } +sub items { goto &invoiceitems; } +sub payment_term { goto &payment; } 1; diff --git a/sql/Pg-upgrade2/ar_ap_foreign_keys.sql b/sql/Pg-upgrade2/ar_ap_foreign_keys.sql new file mode 100644 index 000000000..cd813dec0 --- /dev/null +++ b/sql/Pg-upgrade2/ar_ap_foreign_keys.sql @@ -0,0 +1,32 @@ +-- @tag: ar_ap_foreign_keys +-- @description: Fremdschlüsselverweise für diverse Spalten in ar und ap +-- @depends: release_3_0_0 +-- @charset: utf-8 +ALTER TABLE ar ALTER COLUMN department_id DROP DEFAULT; +ALTER TABLE ap ALTER COLUMN department_id DROP DEFAULT; + +UPDATE ar SET cp_id = NULL WHERE (cp_id IS NOT NULL) AND (cp_id NOT IN (SELECT cp_id FROM contacts)); +UPDATE ar SET department_id = NULL WHERE (department_id IS NOT NULL) AND (department_id NOT IN (SELECT id FROM department)); +UPDATE ar SET employee_id = NULL WHERE (employee_id IS NOT NULL) AND (employee_id NOT IN (SELECT id FROM employee)); +UPDATE ar SET language_id = NULL WHERE (language_id IS NOT NULL) AND (language_id NOT IN (SELECT id FROM language)); +UPDATE ar SET payment_id = NULL WHERE (payment_id IS NOT NULL) AND (payment_id NOT IN (SELECT id FROM payment_terms)); +UPDATE ar SET shipto_id = NULL WHERE (shipto_id IS NOT NULL) AND (shipto_id NOT IN (SELECT shipto_id FROM shipto)); + +UPDATE ap SET cp_id = NULL WHERE (cp_id IS NOT NULL) AND (cp_id NOT IN (SELECT cp_id FROM contacts)); +UPDATE ap SET department_id = NULL WHERE (department_id IS NOT NULL) AND (department_id NOT IN (SELECT id FROM department)); +UPDATE ap SET employee_id = NULL WHERE (employee_id IS NOT NULL) AND (employee_id NOT IN (SELECT id FROM employee)); +UPDATE ap SET language_id = NULL WHERE (language_id IS NOT NULL) AND (language_id NOT IN (SELECT id FROM language)); +UPDATE ap SET payment_id = NULL WHERE (payment_id IS NOT NULL) AND (payment_id NOT IN (SELECT id FROM payment_terms)); + +ALTER TABLE ar ADD FOREIGN KEY (cp_id) REFERENCES contacts (cp_id); +ALTER TABLE ar ADD FOREIGN KEY (department_id) REFERENCES department (id); +ALTER TABLE ar ADD FOREIGN KEY (employee_id) REFERENCES employee (id); +ALTER TABLE ar ADD FOREIGN KEY (language_id) REFERENCES language (id); +ALTER TABLE ar ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id); +ALTER TABLE ar ADD FOREIGN KEY (shipto_id) REFERENCES shipto (shipto_id); + +ALTER TABLE ap ADD FOREIGN KEY (cp_id) REFERENCES contacts (cp_id); +ALTER TABLE ap ADD FOREIGN KEY (employee_id) REFERENCES employee (id); +ALTER TABLE ap ADD FOREIGN KEY (department_id) REFERENCES department (id); +ALTER TABLE ap ADD FOREIGN KEY (language_id) REFERENCES language (id); +ALTER TABLE ap ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id); -- 2.20.1