From f4254cca37cc455795a5e2a7f88eb722a09da287 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Thu, 24 Oct 2019 17:30:43 +0100 Subject: [PATCH] Bug 23805: DBRev 19.06.00.044 Signed-off-by: Martin Renvoize --- Koha.pm | 2 +- Koha/Schema/Result/Accountline.pm | 6 +- .../mysql/atomicupdate/bug_23805_credit.perl | 150 ----------------- installer/data/mysql/updatedatabase.pl | 151 ++++++++++++++++++ 4 files changed, 155 insertions(+), 154 deletions(-) delete mode 100644 installer/data/mysql/atomicupdate/bug_23805_credit.perl diff --git a/Koha.pm b/Koha.pm index a75c02cf8d..263a7f269f 100644 --- a/Koha.pm +++ b/Koha.pm @@ -29,7 +29,7 @@ use vars qw{ $VERSION }; # - #4 : the developer version. The 4th number is the database subversion. # used by developers when the database changes. updatedatabase take care of the changes itself # and is automatically called by Auth.pm when needed. -$VERSION = "19.06.00.043"; +$VERSION = "19.06.00.044"; sub version { return $VERSION; diff --git a/Koha/Schema/Result/Accountline.pm b/Koha/Schema/Result/Accountline.pm index e77ff5be55..1d455e655c 100644 --- a/Koha/Schema/Result/Accountline.pm +++ b/Koha/Schema/Result/Accountline.pm @@ -277,7 +277,7 @@ __PACKAGE__->belongs_to( { is_deferrable => 1, join_type => "LEFT", - on_delete => "SET NULL", + on_delete => "RESTRICT", on_update => "CASCADE", }, ); @@ -363,8 +363,8 @@ __PACKAGE__->belongs_to( ); -# Created by DBIx::Class::Schema::Loader v0.07046 @ 2019-10-17 11:33:03 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:5FjSicyeyUnnNh/pGs78zQ +# Created by DBIx::Class::Schema::Loader v0.07046 @ 2019-10-24 16:33:42 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:q4Ahb8xIxAsjT/aF9yjrdQ sub koha_objects_class { 'Koha::Account::Lines'; diff --git a/installer/data/mysql/atomicupdate/bug_23805_credit.perl b/installer/data/mysql/atomicupdate/bug_23805_credit.perl deleted file mode 100644 index e7c84793b2..0000000000 --- a/installer/data/mysql/atomicupdate/bug_23805_credit.perl +++ /dev/null @@ -1,150 +0,0 @@ -$DBversion = 'XXX'; # will be replaced by the RM -if ( CheckVersion($DBversion) ) { - - # Adding account_credit_types - $dbh->do( - qq{ - CREATE TABLE IF NOT EXISTS account_credit_types ( - code varchar(80) NOT NULL, - description varchar(200) NULL, - can_be_added_manually tinyint(4) NOT NULL DEFAULT 1, - is_system tinyint(1) NOT NULL DEFAULT 0, - PRIMARY KEY (code) - ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci - } - ); - - # Adding account_credit_types_branches - $dbh->do( - qq{ - CREATE TABLE IF NOT EXISTS account_credit_types_branches ( - credit_type_code VARCHAR(80), - branchcode VARCHAR(10), - FOREIGN KEY (credit_type_code) REFERENCES account_credit_types(code) ON DELETE CASCADE, - FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci - } - ); - - # Populating account_credit_types - $dbh->do( - qq{ - INSERT IGNORE INTO account_credit_types ( - code, - description, - can_be_added_manually, - is_system - ) - VALUES - ('PAYMENT', 'Payment', 0, 1), - ('WRITEOFF', 'Writeoff', 0, 1), - ('FORGIVEN', 'Forgiven', 1, 1), - ('CREDIT', 'Credit', 1, 1), - ('LOST_RETURN', 'Lost item fee refund', 0, 1) - } - ); - - # Adding credit_type_code to accountlines - unless ( column_exists('accountlines', 'credit_type_code') ) { - $dbh->do( - qq{ - ALTER IGNORE TABLE accountlines - ADD - credit_type_code varchar(80) DEFAULT NULL - AFTER - accounttype - } - ); - } - - # Linking credit_type_code in accountlines to code in account_credit_types - unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_credit_type' ) ) { - $dbh->do( - qq{ - ALTER TABLE accountlines - ADD CONSTRAINT - `accountlines_ibfk_credit_type` - FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) - ON DELETE RESTRICT - ON UPDATE CASCADE - } - ); - } - - # Dropping the check constraint in accountlines - $dbh->do( - qq{ - ALTER TABLE - accountlines - ADD CONSTRAINT - `accountlines_check_type` - CHECK (credit_type_code IS NOT NULL OR debit_type_code IS NOT NULL) - } - ); - - # Update accountype 'C' to 'CREDIT' - $dbh->do( - qq{ - UPDATE accountlines SET accounttype = 'CREDIT' WHERE accounttype = 'C' OR accounttype = 'CR' - } - ); - - # Update accountype 'FOR' to 'FORGIVEN' - $dbh->do( - qq{ - UPDATE accountlines SET accounttype = 'FORGIVEN' WHERE accounttype = 'FOR' OR accounttype = 'FORW' - } - ); - - # Update accountype 'Pay' to 'PAYMENT' - $dbh->do( - qq{ - UPDATE accountlines SET accounttype = 'PAYMENT' WHERE accounttype = 'Pay' OR accounttype = 'PAY' - } - ); - - # Update accountype 'W' to 'WRITEOFF' - $dbh->do( - qq{ - UPDATE accountlines SET accounttype = 'WRITEOFF' WHERE accounttype = 'W' OR accounttype = 'WO' - } - ); - - # Add any unexpected accounttype codes to credit_types as appropriate - $dbh->do( - qq{ - INSERT IGNORE INTO account_credit_types ( - code, - description, - can_be_added_manually, - is_system - ) - SELECT - DISTINCT(accounttype), - "Unexpected type found during upgrade", - 1, - 0 - FROM - accountlines - WHERE - amount < 0 - } - ); - - # Populating credit_type_code - $dbh->do( - qq{ - UPDATE accountlines SET credit_type_code = accounttype, accounttype = NULL WHERE accounttype IN (SELECT code from account_credit_types) - } - ); - - # Adding a check constraints to accountlines - $dbh->do( - qq{ - ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR credit_type_code IS NOT NULL) - } - ); - - SetVersion($DBversion); - print "Upgrade to $DBversion done (Bug 23049 - Add account debit_credit)\n"; -} diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index bac3f54845..c606cae496 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -19937,6 +19937,157 @@ if ( CheckVersion($DBversion) ) { print "Upgrade to $DBversion done (Bug 23049 - Add account debit_types)\n"; } +$DBversion = '19.06.00.044'; +if ( CheckVersion($DBversion) ) { + + # Adding account_credit_types + $dbh->do( + qq{ + CREATE TABLE IF NOT EXISTS account_credit_types ( + code varchar(80) NOT NULL, + description varchar(200) NULL, + can_be_added_manually tinyint(4) NOT NULL DEFAULT 1, + is_system tinyint(1) NOT NULL DEFAULT 0, + PRIMARY KEY (code) + ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci + } + ); + + # Adding account_credit_types_branches + $dbh->do( + qq{ + CREATE TABLE IF NOT EXISTS account_credit_types_branches ( + credit_type_code VARCHAR(80), + branchcode VARCHAR(10), + FOREIGN KEY (credit_type_code) REFERENCES account_credit_types(code) ON DELETE CASCADE, + FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci + } + ); + + # Populating account_credit_types + $dbh->do( + qq{ + INSERT IGNORE INTO account_credit_types ( + code, + description, + can_be_added_manually, + is_system + ) + VALUES + ('PAYMENT', 'Payment', 0, 1), + ('WRITEOFF', 'Writeoff', 0, 1), + ('FORGIVEN', 'Forgiven', 1, 1), + ('CREDIT', 'Credit', 1, 1), + ('LOST_RETURN', 'Lost item fee refund', 0, 1) + } + ); + + # Adding credit_type_code to accountlines + unless ( column_exists('accountlines', 'credit_type_code') ) { + $dbh->do( + qq{ + ALTER IGNORE TABLE accountlines + ADD + credit_type_code varchar(80) DEFAULT NULL + AFTER + accounttype + } + ); + } + + # Linking credit_type_code in accountlines to code in account_credit_types + unless ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_credit_type' ) ) { + $dbh->do( + qq{ + ALTER TABLE accountlines + ADD CONSTRAINT + `accountlines_ibfk_credit_type` + FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) + ON DELETE RESTRICT + ON UPDATE CASCADE + } + ); + } + + # Dropping the check constraint in accountlines + $dbh->do( + qq{ + ALTER TABLE + accountlines + ADD CONSTRAINT + `accountlines_check_type` + CHECK (credit_type_code IS NOT NULL OR debit_type_code IS NOT NULL) + } + ); + + # Update accountype 'C' to 'CREDIT' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'CREDIT' WHERE accounttype = 'C' OR accounttype = 'CR' + } + ); + + # Update accountype 'FOR' to 'FORGIVEN' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'FORGIVEN' WHERE accounttype = 'FOR' OR accounttype = 'FORW' + } + ); + + # Update accountype 'Pay' to 'PAYMENT' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'PAYMENT' WHERE accounttype = 'Pay' OR accounttype = 'PAY' + } + ); + + # Update accountype 'W' to 'WRITEOFF' + $dbh->do( + qq{ + UPDATE accountlines SET accounttype = 'WRITEOFF' WHERE accounttype = 'W' OR accounttype = 'WO' + } + ); + + # Add any unexpected accounttype codes to credit_types as appropriate + $dbh->do( + qq{ + INSERT IGNORE INTO account_credit_types ( + code, + description, + can_be_added_manually, + is_system + ) + SELECT + DISTINCT(accounttype), + "Unexpected type found during upgrade", + 1, + 0 + FROM + accountlines + WHERE + amount < 0 + } + ); + + # Populating credit_type_code + $dbh->do( + qq{ + UPDATE accountlines SET credit_type_code = accounttype, accounttype = NULL WHERE accounttype IN (SELECT code from account_credit_types) + } + ); + + # Adding a check constraints to accountlines + $dbh->do( + qq{ + ALTER TABLE accountlines ADD CONSTRAINT `accountlines_check_type` CHECK (accounttype IS NOT NULL OR credit_type_code IS NOT NULL) + } + ); + + SetVersion($DBversion); + print "Upgrade to $DBversion done (Bug 23049 - Add account credit_types)\n"; +} + # SEE bug 13068 # if there is anything in the atomicupdate, read and execute it. my $update_dir = C4::Context->config('intranetdir') . '/installer/data/mysql/atomicupdate/'; -- 2.39.5