From 0901fce057da2f9d5f5c293b1cb77294de4faf75 Mon Sep 17 00:00:00 2001
From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Date: Wed, 7 Apr 2021 15:02:09 +0200
Subject: [PATCH] Bug 14233: DB changes - NoticesLog pref + letter.id PK

---
 .../data/mysql/atomicupdate/bug_14233.perl    | 20 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  4 +++-
 installer/data/mysql/mandatory/sysprefs.sql   |  1 +
 .../en/modules/admin/preferences/logs.pref    |  6 ++++++
 4 files changed, 30 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..5b4bf48d51
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_14233.perl
@@ -0,0 +1,20 @@
+$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`)}
+        );
+    }
+
+    $dbh->do(q{
+        INSERT IGNORE INTO systempreferences (variable, value, explanation, options, type)
+        VALUES ('NoticesLog','',NULL,'If enabled, log changes to notice templates','YesNo')
+    });
+
+    NewVersion( $DBversion, 14233, "Add id field to letter table" );
+}
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index ae247e67c8..f66a00f04f 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -3260,6 +3260,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)',
@@ -3270,7 +3271,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;
diff --git a/installer/data/mysql/mandatory/sysprefs.sql b/installer/data/mysql/mandatory/sysprefs.sql
index c48ad18d60..045c6df647 100644
--- a/installer/data/mysql/mandatory/sysprefs.sql
+++ b/installer/data/mysql/mandatory/sysprefs.sql
@@ -353,6 +353,7 @@ INSERT INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `
 ('NotHighlightedWords','and|or|not',NULL,'List of words to NOT highlight when OpacHitHighlight is enabled','free'),
 ('NoticeBcc','','','Email address to bcc outgoing notices sent by email','free'),
 ('NoticeCSS','',NULL,'Notices CSS url.','free'),
+('NoticesLog','',NULL,'If enabled, log changes to notice templates','YesNo'),
 ('NotifyBorrowerDeparture','30',NULL,'Define number of days before expiry where circulation is warned about patron account expiry','Integer'),
 ('NovelistSelectEnabled','0',NULL,'Enable Novelist Select content.  Requires Novelist Profile and Password','YesNo'),
 ('NovelistSelectPassword','',NULL,'Novelist select user Password','free'),
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/logs.pref b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/logs.pref
index 490a69569d..6488e62e57 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/logs.pref
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/logs.pref
@@ -90,6 +90,12 @@ Logging:
                   1: Log
                   0: "Don't log"
             - " successful authentications."
+        -
+            - pref: NoticesLog
+              choices:
+                  on: Log
+                  off: "Don't log"
+            - " changes to notice templates."
 
     Debugging:
         -
-- 
2.20.1