Attachment 119360 Details for Bug 14233 – Bug 14233: DB changes - NoticesLog pref + letter.id PK

[patch] Bug 14233: DB changes - NoticesLog pref + letter.id PK

Bug-14233-DB-changes---NoticesLog-pref--letterid-P.patch (text/plain), 5.04 KB, created by Martin Renvoize (ashimema) on 2021-04-09 08:28:41 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Martin Renvoize (ashimema)

Created: 2021-04-09 08:28:41 UTC

Size: 5.04 KB

patch

obsolete

>From 0eed17647cb26f444282becee1d9718190ea9ef6 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
>
>Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
>---
> .../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 19111fdfe9..b06c51e9cd 100644
>--- a/installer/data/mysql/mandatory/sysprefs.sql
>+++ b/installer/data/mysql/mandatory/sysprefs.sql
>@@ -356,6 +356,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

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