Attachment 117311 Details for Bug 14233 – Bug 14233: Add 'id' field to `letter` table to allow linking

[patch] Bug 14233: Add 'id' field to `letter` table to allow linking

Bug-14233-Add-id-field-to-letter-table-to-allow-li.patch (text/plain), 3.47 KB, created by Kelly McElligott on 2021-02-25 13:38:00 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kelly McElligott

Created: 2021-02-25 13:38:00 UTC

Size: 3.47 KB

patch

obsolete

>From a3d294cff7ce5ec8c0721ebdfd42da032f4bdea1 Mon Sep 17 00:00:00 2001
>From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
>Date: Wed, 10 Feb 2021 11:53:53 +0000
>Subject: [PATCH] Bug 14233: Add 'id' field to `letter` table to allow linking
>
>To allow for 'object' linking from the action_logs table we need an
>int(11) identifier. This patch updates the letter table to add such a
>field as a replacement for the multi-field primary key and moves that
>key to a unique constraint.
>
>Test plan
>1/ Run the database update
>2/ Check that the 'letter' table now includes an 'id' primary key and
>'letter_uniq_1' multi-field unique key
>3/ Signoff
>
>Signed-off-by: Kelly McElligott <kelly@bywatersolutions.com>
>---
> installer/data/mysql/atomicupdate/bug_14233.perl | 15 +++++++++++++++
> installer/data/mysql/kohastructure.sql           |  4 +++-
> 2 files changed, 18 insertions(+), 1 deletion(-)
> create mode 100644 installer/data/mysql/atomicupdate/bug_14233.perl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_14233.perl b/installer/data/mysql/atomicupdate/bug_14233.perl
>new file mode 100644
>index 0000000000..6e2b6bfc24
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_14233.perl
>@@ -0,0 +1,15 @@
>+$DBversion = 'XXX';    # will be replaced by the RM
>+if ( CheckVersion($DBversion) ) {
>+
>+    if ( !column_exists( 'letter', 'id' ) ) {
>+        $dbh->do(q{ALTER TABLE letter DROP PRIMARY KEY});
>+        $dbh->do(
>+q{ALTER TABLE letter ADD COLUMN `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST}
>+        );
>+        $dbh->do(
>+q{ALTER TABLE letter ADD UNIQUE KEY letter_uniq_1 (`module`,`code`,`branchcode`,`message_transport_type`,`lang`)}
>+        );
>+    }
>+
>+    NewVersion( $DBversion, 14233, "Add id field to letter table" );
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 952cd9d296..ba422e16cf 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -3257,6 +3257,7 @@ DROP TABLE IF EXISTS `letter`;
> /*!40101 SET @saved_cs_client     = @@character_set_client */;
> /*!40101 SET character_set_client = utf8 */;
> CREATE TABLE `letter` (
>+  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key identifier',
>   `module` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Koha module that triggers this notice or slip',
>   `code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'unique identifier for this notice or slip',
>   `branchcode` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'the branch this notice or slip is used at (branches.branchcode)',
>@@ -3267,7 +3268,8 @@ CREATE TABLE `letter` (
>   `message_transport_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'email' COMMENT 'transport type for this notice',
>   `lang` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'default' COMMENT 'lang of the notice',
>   `updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'last modification',
>-  PRIMARY KEY (`module`,`code`,`branchcode`,`message_transport_type`,`lang`),
>+  PRIMARY KEY (`id`),
>+  UNIQUE KEY `letter_uniq_1` (`module`,`code`,`branchcode`,`message_transport_type`,`lang`),
>   KEY `message_transport_type_fk` (`message_transport_type`),
>   CONSTRAINT `message_transport_type_fk` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>-- 
>2.11.0

Actions: View | Diff | Splinter Review

Attachments on bug 14233: 116649 | 116650 | 116651 | 116660 | 116661 | 116662 | 116668 | 116671 | 116672 | 116673 | 116674 | 116713 | 116760 | 116761 | 117251 | 117252 | 117253 | 117254 | 117255 | 117256 | 117257 | 117258 | 117259 | 117294 | 117295 | 117296 | 117297 | 117298 | 117299 | 117300 | 117301 | 117302 | 117303 | 117304 | 117311 | 117312 | 117313 | 117314 | 117315 | 117316 | 117317 | 117318 | 117319 | 117320 | 117321 | 117345 | 118132 | 118133 | 118134 | 118135 | 118136 | 118137 | 118138 | 118139 | 118140 | 118141 | 118142 | 118143 | 118155 | 118156 | 118157 | 118158 | 118159 | 118160 | 118161 | 118162 | 118163 | 118164 | 118165 | 118166 | 118167 | 118323 | 119291 | 119292 | 119293 | 119294 | 119312 | 119313 | 119314 | 119315 | 119359 | 119360 | 119361 | 119362 | 119510