@@ -, +, @@ --- .../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 --- a/installer/data/mysql/atomicupdate/bug_22435.perl +++ a/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"); } --- a/installer/data/mysql/kohastructure.sql +++ a/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 */; --- a/installer/data/mysql/mandatory/account_offset_types.sql +++ a/installer/data/mysql/mandatory/account_offset_types.sql @@ -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'); --