Diverse Fremdschlüssel für Tabellen ar und ap

diff --git a/SL/DB/Invoice.pm b/SL/DB/Invoice.pm
index c6658413568220b28d3a3b58a34dcab42903ada5..ff821ef75245e573b14fe9fee9143ac6a0d9b524 100644 (file)
--- a/SL/DB/Invoice.pm
+++ b/SL/DB/Invoice.pm
@@ -28,43 +28,14 @@ __PACKAGE__->meta->add_relationship(
       with_objects => [ 'part' ]
     }
   },
       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
 
 );
 
 __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 {
 
 # 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 839c67bfe6a4d791b67e741e70f11643518b11c8..1111a980ba0192cec3b2aecbbc34bb2021dbf344 100644 (file)
--- 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' },
     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' },
     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 => [
   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' },
     },
 
     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' },
     },
 
     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' },
     },
 
     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' },
     },
 
     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' },
     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 eb18f7058ca93f1213e0defbc982feb1997da84c..636a5d823a329499a3d7e3345287b1984f9e11ad 100644 (file)
--- 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' },
     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' },
     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 => [
   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' },
     },
 
     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' },
     storno_obj => {
       class       => 'SL::DB::PurchaseInvoice',
       key_columns => { storno_id => 'id' },

diff --git a/SL/DB/PurchaseInvoice.pm b/SL/DB/PurchaseInvoice.pm
index 5123d681ec2ba93ad0783a4eb21ce396e2915cbe..9c6f54e86e8f50beeea6abf819fd110ab3327a4e 100644 (file)
--- 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;
 
 
 __PACKAGE__->meta->initialize;
 

-sub items { goto &invoiceitems; }
+sub items        { goto &invoiceitems; }
+sub payment_term { goto &payment;      }

 
 1;
 
 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 (file)
index 0000000..cd813de
--- /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);