From 40c82ec8557aa38e4c39a6e0f6fcd70df8f70121 Mon Sep 17 00:00:00 2001 From: Martin Renvoize Date: Mon, 21 Jun 2021 16:15:12 +0100 Subject: [PATCH] Bug 22435: (follow-up) Drop account_offset_types table This patch drops the now defunkt account_offset_types table and ensure the type field in account_offsets is instead an enum type column. --- .../data/mysql/atomicupdate/bug_22435.perl | 34 +++++++++++++++++-- installer/data/mysql/kohastructure.sql | 19 ++--------- .../mysql/mandatory/account_offset_types.sql | 28 --------------- 3 files changed, 33 insertions(+), 48 deletions(-) delete mode 100644 installer/data/mysql/mandatory/account_offset_types.sql diff --git a/installer/data/mysql/atomicupdate/bug_22435.perl b/installer/data/mysql/atomicupdate/bug_22435.perl index d7dbbdd568..9564445b38 100644 --- a/installer/data/mysql/atomicupdate/bug_22435.perl +++ b/installer/data/mysql/atomicupdate/bug_22435.perl @@ -1,8 +1,36 @@ $DBversion = 'XXX'; # will be replaced by the RM if( CheckVersion( $DBversion ) ) { - $dbh->do( "INSERT IGNORE INTO account_offset_types ( type ) VALUES ( 'CREATE' ), ( 'APPLY' )" ); + # Remove foreign key for offset types + if ( foreign_key_exists( 'account_offsets', 'account_offsets_ibfk_t' ) ) { + $dbh->do( "ALTER TABLE account_offsets DROP FOREIGN KEY account_offsets_ibfk_t" ); + } + + # Drop account_offset_types table + $dbh->do( "DROP TABLE IF EXISTS account_offset_types" ); + + # Update offset_types to 'CREATE' where appropriate $dbh->do( "UPDATE account_offsets SET type = 'CREATE' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND ( debit_id IS NULL OR credit_id IS NULL)" ); - $dbh->do( "UPDATE account_offsets SET type = 'APPLY' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND type != 'CREATE'" ); - NewVersion( $DBversion, 22435, "Update offsets to CREATE type"); + # Update offset_types to 'APPLY' where appropriate + $dbh->do( "UPDATE account_offsets SET type = 'APPLY' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND type != 'CREATE' AND type != 'VOID'" ); + + # Update table to ENUM + $dbh->do( + q{ + ALTER TABLE + `account_offsets` + MODIFY COLUMN + `type` enum( + 'CREATE', + 'APPLY', + 'VOID', + 'OVERDUE_INCREASE', + 'OVERDUE_DECREASE' + ) + AFTER `debit_id` + } + ); + + + NewVersion( $DBversion, 22435, "Update existing offsets"); } diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 8922beb8df..ead6886617 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -86,19 +86,6 @@ CREATE TABLE `account_debit_types_branches` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; --- --- Table structure for table `account_offset_types` --- - -DROP TABLE IF EXISTS `account_offset_types`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `account_offset_types` ( - `type` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The type of offset this is', - PRIMARY KEY (`type`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -/*!40101 SET character_set_client = @saved_cs_client */; - -- -- Table structure for table `account_offsets` -- @@ -110,16 +97,14 @@ CREATE TABLE `account_offsets` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for each offset', `credit_id` int(11) DEFAULT NULL COMMENT 'The id of the accountline the increased the patron''s balance', `debit_id` int(11) DEFAULT NULL COMMENT 'The id of the accountline that decreased the patron''s balance', - `type` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The type of offset this is', + `type` enum('CREATE','APPLY','VOID','OVERDUE_INCREASE','OVERDUE_DECREASE') COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The type of offset this is', `amount` decimal(26,6) NOT NULL COMMENT 'The amount of the change', `created_on` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `account_offsets_ibfk_p` (`credit_id`), KEY `account_offsets_ibfk_f` (`debit_id`), - KEY `account_offsets_ibfk_t` (`type`), CONSTRAINT `account_offsets_ibfk_f` FOREIGN KEY (`debit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `account_offsets_ibfk_p` FOREIGN KEY (`credit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `account_offsets_ibfk_t` FOREIGN KEY (`type`) REFERENCES `account_offset_types` (`type`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `account_offsets_ibfk_p` FOREIGN KEY (`credit_id`) REFERENCES `accountlines` (`accountlines_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; diff --git a/installer/data/mysql/mandatory/account_offset_types.sql b/installer/data/mysql/mandatory/account_offset_types.sql deleted file mode 100644 index c608ecd21f..0000000000 --- a/installer/data/mysql/mandatory/account_offset_types.sql +++ /dev/null @@ -1,28 +0,0 @@ -INSERT INTO account_offset_types ( type ) VALUES -('CREATE'), -('Writeoff'), -('Payment'), -('Purchase'), -('Lost Item'), -('Lost Item Found'), -('Processing Fee'), -('Manual Credit'), -('Manual Debit'), -('Reverse Payment'), -('Forgiven'), -('Dropbox'), -('Account Fee'), -('Rental Fee'), -('Reserve Fee'), -('Hold Expired'), -('Overpayment'), -('OVERDUE_INCREASE'), -('OVERDUE_DECREASE'), -('OVERDUE'), -('Void Payment'), -('Credit Applied'), -('PAYOUT'), -('DISCOUNT'), -('REFUND'), -('CANCELLATION'), -('VOID'); -- 2.20.1