From d0118dc6303e04507a77f3dde2c2bf0565ecb388 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Jan=20B=C3=BCren?= Date: Thu, 16 Jan 2014 16:14:55 +0100 Subject: [PATCH] =?utf8?q?Kleinere=20DB-Details=20(Fremdschl=C3=BCssel=20u?= =?utf8?q?nd=20verwaiste=20Tabelle)=20=20=20=20=20=20-=20audittrails=20wir?= =?utf8?q?d=20nicht=20mehr=20benutzt=20=20=20=20=20=20-=20Fremdschl=C3=BCs?= =?utf8?q?sel=20f=C3=BCr=20employee=20id=20f=C3=BCr=20auch=20f=C3=BCr=20sa?= =?utf8?q?lesman=20in=20customer=20/=20vendor?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/DB/Helper/Mappings.pm | 1 - SL/DB/MetaSetup/AuditTrail.pm | 25 ---------------------- SL/DB/MetaSetup/Customer.pm | 5 +++++ SL/DB/MetaSetup/Default.pm | 1 - SL/DB/MetaSetup/Vendor.pm | 5 +++++ sql/Pg-upgrade2/drop_audittrail.sql | 6 ++++++ sql/Pg-upgrade2/employee_id_foreignkeys.pl | 24 +++++++++++++++++++++ 7 files changed, 40 insertions(+), 27 deletions(-) delete mode 100644 SL/DB/MetaSetup/AuditTrail.pm create mode 100644 sql/Pg-upgrade2/drop_audittrail.sql create mode 100644 sql/Pg-upgrade2/employee_id_foreignkeys.pl diff --git a/SL/DB/Helper/Mappings.pm b/SL/DB/Helper/Mappings.pm index 633a06793..022894c16 100644 --- a/SL/DB/Helper/Mappings.pm +++ b/SL/DB/Helper/Mappings.pm @@ -84,7 +84,6 @@ my @kivitendo_blacklist = (@kivitendo_blacklist_permanent, @kivitendo_blacklist_ my %kivitendo_package_names = ( # TABLE # MODEL (given in C style) acc_trans => 'acc_transaction', - audittrail => 'audit_trail', 'auth.clients' => 'auth_client', 'auth.clients_users' => 'auth_client_user', 'auth.clients_groups' => 'auth_client_group', diff --git a/SL/DB/MetaSetup/AuditTrail.pm b/SL/DB/MetaSetup/AuditTrail.pm deleted file mode 100644 index fd8add028..000000000 --- a/SL/DB/MetaSetup/AuditTrail.pm +++ /dev/null @@ -1,25 +0,0 @@ -# This file has been auto-generated. Do not modify it; it will be overwritten -# by rose_auto_create_model.pl automatically. -package SL::DB::AuditTrail; - -use strict; - -use base qw(SL::DB::Object); - -__PACKAGE__->meta->table('audittrail'); - -__PACKAGE__->meta->columns( - action => { type => 'text' }, - employee_id => { type => 'integer' }, - formname => { type => 'text' }, - id => { type => 'serial', not_null => 1 }, - reference => { type => 'text' }, - tablename => { type => 'text' }, - trans_id => { type => 'integer' }, - transdate => { type => 'timestamp', default => 'now' }, -); - -__PACKAGE__->meta->primary_key_columns([ 'id' ]); - -1; -; diff --git a/SL/DB/MetaSetup/Customer.pm b/SL/DB/MetaSetup/Customer.pm index 0f523a466..d65b0b37b 100644 --- a/SL/DB/MetaSetup/Customer.pm +++ b/SL/DB/MetaSetup/Customer.pm @@ -89,6 +89,11 @@ __PACKAGE__->meta->foreign_keys( class => 'SL::DB::PaymentTerm', key_columns => { payment_id => 'id' }, }, + + salesman => { + class => 'SL::DB::Employee', + key_columns => { salesman_id => 'id' }, + }, ); 1; diff --git a/SL/DB/MetaSetup/Default.pm b/SL/DB/MetaSetup/Default.pm index 48551285c..8c0319cc3 100644 --- a/SL/DB/MetaSetup/Default.pm +++ b/SL/DB/MetaSetup/Default.pm @@ -18,7 +18,6 @@ __PACKAGE__->meta->columns( ar_show_mark_as_paid => { type => 'boolean', default => 'true' }, articlenumber => { type => 'text' }, assemblynumber => { type => 'text' }, - audittrail => { type => 'boolean', default => 'false' }, bin_id => { type => 'integer' }, bin_id_ignore_onhand => { type => 'integer' }, businessnumber => { type => 'text' }, diff --git a/SL/DB/MetaSetup/Vendor.pm b/SL/DB/MetaSetup/Vendor.pm index 0493aa374..aa44b133b 100644 --- a/SL/DB/MetaSetup/Vendor.pm +++ b/SL/DB/MetaSetup/Vendor.pm @@ -85,6 +85,11 @@ __PACKAGE__->meta->foreign_keys( class => 'SL::DB::PaymentTerm', key_columns => { payment_id => 'id' }, }, + + salesman => { + class => 'SL::DB::Employee', + key_columns => { salesman_id => 'id' }, + }, ); 1; diff --git a/sql/Pg-upgrade2/drop_audittrail.sql b/sql/Pg-upgrade2/drop_audittrail.sql new file mode 100644 index 000000000..b998dc690 --- /dev/null +++ b/sql/Pg-upgrade2/drop_audittrail.sql @@ -0,0 +1,6 @@ +-- @tag: drop_audittrail +-- @description: Tabelle audittrail wird nicht mehr benutzt +-- @depends: release_3_0_0 +-- @ignore: 0 +ALTER TABLE defaults DROP COLUMN audittrail; +DROP TABLE audittrail; diff --git a/sql/Pg-upgrade2/employee_id_foreignkeys.pl b/sql/Pg-upgrade2/employee_id_foreignkeys.pl new file mode 100644 index 000000000..3716da694 --- /dev/null +++ b/sql/Pg-upgrade2/employee_id_foreignkeys.pl @@ -0,0 +1,24 @@ +# @tag: employee_id_foreignkeys +# @description: Falls ein Benutzer hart in der Datenbank gelöscht werden soll, entsprechende Fremdschlüssel setzen, entfernt ferner verwaiste Einträge +# @depends: release_3_0_0 +package SL::DBUpgrade2::employee_id_foreignkeys; + +use utf8; + +use parent qw(SL::DBUpgrade2::Base); +use strict; + +sub run { + my ($self) = @_; + + # this query will fail if we have orphaned entries + # should only occur + $self->db_query(qq|UPDATE customer set salesman_id = NULL where salesman_id not in (select id from employee)|, may_fail => 0); + $self->db_query(qq|UPDATE vendor set salesman_id = NULL where salesman_id not in (select id from employee)|, may_fail => 0); + $self->db_query(qq|ALTER TABLE customer ADD FOREIGN KEY (salesman_id) REFERENCES employee (id)|, may_fail => 0); + $self->db_query(qq|ALTER TABLE vendor ADD FOREIGN KEY (salesman_id) REFERENCES employee (id)|, may_fail => 0); + + return 1; +} + +1; -- 2.20.1