From a67929227c0b42d98796ba579f7b0da6712dae2f Mon Sep 17 00:00:00 2001 From: Martin Renvoize 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. --- 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