From 5e157250e6c70ab880f2b1657671d02da4a9fc4f 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
---
 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.20.1