Attachment 123436 Details for Bug 22435 – Bug 22435: (follow-up) Drop account_offset_types table

[patch] Bug 22435: (follow-up) Drop account_offset_types table

Bug-22435-follow-up-Drop-accountoffsettypes-table.patch (text/plain), 5.96 KB, created by Martin Renvoize (ashimema) on 2021-08-03 14:34:15 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Martin Renvoize (ashimema)

Created: 2021-08-03 14:34:15 UTC

Size: 5.96 KB

patch

obsolete

>From 1a819c073fb9b9deb09e7d2e1dec07da2d27a596 Mon Sep 17 00:00:00 2001
>From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
>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.
>
>Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
>---
> .../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 58dadc1541..0ff6005d93 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

Actions: View | Diff | Splinter Review

Attachments on bug 22435: 118340 | 118341 | 118541 | 118542 | 118543 | 118544 | 118545 | 118546 | 118547 | 120040 | 120041 | 120042 | 120043 | 120044 | 120045 | 120046 | 120963 | 120964 | 120965 | 120966 | 120967 | 120968 | 120969 | 120970 | 120979 | 120980 | 120981 | 120982 | 120983 | 120984 | 120985 | 120986 | 120987 | 120988 | 120989 | 120990 | 120991 | 121004 | 121005 | 121006 | 121007 | 121008 | 121009 | 121010 | 121011 | 121012 | 121013 | 121014 | 121015 | 121016 | 121028 | 121757 | 121758 | 121759 | 121760 | 121761 | 121762 | 121763 | 121764 | 121765 | 121766 | 121767 | 121768 | 121769 | 121960 | 121961 | 121962 | 121963 | 121964 | 121965 | 121966 | 121967 | 121968 | 121969 | 121970 | 121971 | 121972 | 122148 | 122149 | 122150 | 122151 | 122152 | 122153 | 122154 | 122155 | 122156 | 122157 | 122158 | 122159 | 122160 | 122161 | 122235 | 122236 | 122237 | 122238 | 122239 | 122240 | 122241 | 122242 | 122243 | 122244 | 122245 | 122246 | 122247 | 122248 | 122249 | 122250 | 122251 | 122252 | 122253 | 122256 | 122257 | 122258 | 122259 | 122260 | 122261 | 122262 | 122263 | 122264 | 122265 | 122266 | 122267 | 122268 | 122269 | 122270 | 122271 | 122272 | 122273 | 122274 | 122553 | 122554 | 122555 | 122556 | 122557 | 122558 | 122559 | 122560 | 122561 | 122562 | 122563 | 122564 | 122565 | 122566 | 122567 | 122568 | 122569 | 122570 | 122571 | 122586 | 122899 | 122900 | 122901 | 122902 | 122903 | 122904 | 122905 | 122906 | 122907 | 122908 | 122909 | 122910 | 122911 | 122912 | 122913 | 122914 | 122915 | 122916 | 122917 | 122918 | 122937 | 123423 | 123424 | 123425 | 123426 | 123427 | 123428 | 123429 | 123430 | 123431 | 123432 | 123433 | 123434 | 123435 | 123436 | 123437 | 123438 | 123439 | 123440 | 123441 | 123442 | 123443