From 567a0e5cf2b1263cd2836188981928ac9e1c196a Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Date: Tue, 7 Mar 2023 21:38:52 +0100
Subject: [PATCH] Bug 31383: DB changes

Sponsored-by: Rijksmuseum, Netherlands
---
 .../data/mysql/atomicupdate/bug_31383.pl      | 99 +++++++++++++++++++
 .../mysql/en/mandatory/sample_notices.yml     | 13 ++-
 .../data/mysql/en/optional/sample_news.yml    | 63 +++++++-----
 installer/data/mysql/kohastructure.sql        | 21 ++--
 4 files changed, 159 insertions(+), 37 deletions(-)
 create mode 100755 installer/data/mysql/atomicupdate/bug_31383.pl

diff --git a/installer/data/mysql/atomicupdate/bug_31383.pl b/installer/data/mysql/atomicupdate/bug_31383.pl
new file mode 100755
index 00000000000..2bda07cb888
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_31383.pl
@@ -0,0 +1,99 @@
+use Modern::Perl;
+
+return {
+    bug_number => "31383",
+    description => "Split the additional_contents table",
+    up => sub {
+        my ($args) = @_;
+        my ($dbh, $out) = @$args{qw(dbh out)};
+        unless ( TableExists('additional_contents_localizations') ) {
+            $dbh->do(q{
+                ALTER TABLE additional_contents
+                CHANGE COLUMN idnew `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content category'
+            });
+            say $out "Renamed additional_contents.idnew with 'id'";
+
+            $dbh->do(q{
+                CREATE TABLE `additional_contents_localizations` (
+                    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content',
+                    `additional_content_id` int(10) unsigned NOT NULL COMMENT 'link to the additional content',
+                    `title` varchar(250) NOT NULL DEFAULT '' COMMENT 'title of the additional content',
+                    `content` mediumtext NOT NULL COMMENT 'the body of your additional content',
+                    `lang` varchar(50) NOT NULL DEFAULT '' COMMENT 'lang',
+                    PRIMARY KEY (`id`),
+                    UNIQUE KEY `additional_contents_localizations_uniq` (`additional_content_id`,`lang`),
+                    CONSTRAINT `additional_contents_localizations_ibfk1` FOREIGN KEY (`additional_content_id`) REFERENCES `additional_contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+            });
+            say $out "Added new table 'additional_contents_localizations'";
+
+            my $contents = $dbh->selectall_arrayref(q{SELECT MIN(id) AS id, category, code, branchcode FROM additional_contents GROUP BY category, code, branchcode}, { Slice => {} });
+            my $sth_insert = $dbh->prepare(q{
+                INSERT INTO additional_contents_localizations(additional_content_id, title, content, lang)
+                VALUES(?, ?, ?, ?)
+            });
+            for my $content ( @$contents ) {
+                my $q = q{
+                    SELECT title, content, lang, branchcode
+                    FROM additional_contents
+                    WHERE category=? AND code=? AND
+                };
+                $q .= defined $content->{branchcode} ? " branchcode = ?" : " branchcode IS NULL";
+                my $translated_contents = $dbh->selectall_arrayref($q, { Slice => {} }, $content->{category}, $content->{code}, defined $content->{branchcode} ? $content->{branchcode} : ());
+                for my $translated_content ( @$translated_contents ) {
+                    $sth_insert->execute($content->{id}, $translated_content->{title}, $translated_content->{content}, $translated_content->{lang});
+                }
+            }
+            $dbh->do(q{
+                ALTER TABLE additional_contents
+                DROP INDEX additional_contents_uniq
+            });
+            $dbh->do(q{
+                ALTER TABLE additional_contents
+                ADD UNIQUE KEY `additional_contents_uniq` (`category`,`code`,`branchcode`)
+            });
+            $dbh->do(q{
+                ALTER TABLE additional_contents
+                DROP COLUMN title,
+                DROP COLUMN content,
+                DROP COLUMN lang
+            });
+            say $out "Removed 'title', 'content', 'lang' columns from additional_contents";
+        }
+
+        my $notice_templates = $dbh->selectall_arrayref(q{
+            SELECT id, module, code, content
+            FROM letter
+            WHERE content LIKE "%<news>%"
+        }, { Slice => {} });
+        for my $template ( @$notice_templates ) {
+            my $new_content = $template->{content};
+            $new_content =~ s|<news>|[% FOR content IN additional_contents %]|g;
+            $new_content =~ s|</news>|[% END %]|g;
+            $new_content =~ s{<<\s*additional_contents\.title\s*>>}{[% content.title | html %]}g;
+            $new_content =~ s{<<\s*additional_contents\.content\s*>>}{[% content.content | html %]}g;
+            $new_content =~ s{<<\s*additional_contents\.published_on\s*>>}{[% content.published_on | \$KohaDates %]}g;
+            $dbh->do(q{
+                UPDATE letter
+                SET content = ?
+                WHERE id = ?
+            }, undef, $new_content, $template->{id});
+
+            say $out "Adjusted notice template '" . $template->{code} . "'";
+        }
+
+        $notice_templates = $dbh->selectall_arrayref(q{
+            SELECT id, code
+            FROM letter
+            WHERE content LIKE "%<<additional_content%" OR content LIKE "%<< additional_content%"
+        });
+
+        if ( @$notice_templates ) {
+            say $out "WARNING - Some notice templates need manual adjustments!";
+            for my $template ( @$notice_templates ) {
+                say $out sprintf "\t %s (id=%s)", $template->{code}, $template->{id};
+            }
+        }
+
+    },
+};
diff --git a/installer/data/mysql/en/mandatory/sample_notices.yml b/installer/data/mysql/en/mandatory/sample_notices.yml
index 15adee50904..438d2793a86 100644
--- a/installer/data/mysql/en/mandatory/sample_notices.yml
+++ b/installer/data/mysql/en/mandatory/sample_notices.yml
@@ -718,6 +718,7 @@ tables:
           message_transport_type: email
           lang: default
           content:
+            - "[%~ USE KohaDates ~%]"
             - "<h3><<branches.branchname>></h3>"
             - "Checked out to <<borrowers.title>> <<borrowers.firstname>> <<borrowers.initials>> <<borrowers.surname>> <br />"
             - "(<<borrowers.cardnumber>>) <br />"
@@ -768,15 +769,17 @@ tables:
             - ""
             - "<hr>"
             - ""
+            - "[% IF additional_contents.count %]"
             - "<h4 style=\"text-align: center; font-style:italic;\">News</h4>"
-            - "<news>"
+            - "[% FOREACH content IN additional_contents %]"
             - "<div class=\"newsitem\">"
-            - "<h5 style=\"margin-bottom: 1px; margin-top: 1px\"><b><<additional_contents.title>></b></h5>"
-            - "<p style=\"margin-bottom: 1px; margin-top: 1px\"><<additional_contents.content>></p>"
-            - "<p class=\"newsfooter\" style=\"font-size: 8pt; font-style:italic; margin-bottom: 1px; margin-top: 1px\">Posted on <<additional_contents.published_on>></p>"
+            - "<h5 style=\"margin-bottom: 1px; margin-top: 1px\"><b>[% content.title %]</b></h5>"
+            - "<p style=\"margin-bottom: 1px; margin-top: 1px\">[% content.content %]</p>"
+            - "<p class=\"newsfooter\" style=\"font-size: 8pt; font-style:italic; margin-bottom: 1px; margin-top: 1px\">Posted on [% content.published_on | $KohaDates %]</p>"
             - "<hr />"
             - "</div>"
-            - "</news>"
+            - "[% END %]"
+            - "[% END %]"
 
         - module: circulation
           code: ODUE
diff --git a/installer/data/mysql/en/optional/sample_news.yml b/installer/data/mysql/en/optional/sample_news.yml
index 9bbf06c7338..b2f8db7dd2b 100644
--- a/installer/data/mysql/en/optional/sample_news.yml
+++ b/installer/data/mysql/en/optional/sample_news.yml
@@ -22,24 +22,48 @@ description:
 
 tables:
   - additional_contents:
-      translatable: [ title, content ]
-      multiline: [ content ]
+      translatable: []
+      multiline: []
       rows:
-        - title: "Welcome to Koha"
+        - code: "News_1"
           category: "news"
-          code: "News_1"
           location: "staff_only"
-          content:
-            - "Welcome to Koha. Koha is a full-featured open-source ILS. Developed initially in New Zealand by Katipo Communications Ltd and first deployed in January of 2000 for Horowhenua Library Trust, Koha is currently maintained by a team of software providers and library technology staff from around the globe."
-          lang: "default"
           published_on: "2007-10-29 05:25:58"
           expirationdate: "2099-01-10"
           number: 1
 
-        - title: "What's Next?"
+        - code: "News_2"
           category: "news"
           location: "staff_only"
-          code: "News_2"
+          published_on: "2007-10-29 05:34:45"
+          expirationdate: "2099-01-10"
+          number: 2
+
+        - code: "CatalogConcernHelp_1"
+          category: "html_customizations"
+          location: "CatalogConcernHelp"
+          published_on: "2007-10-29 05:25:58"
+          expirationdate: "2099-01-10"
+          number: 1
+
+        - code: "CatalogConcernTemplate_1"
+          category: "html_customizations"
+          location: "CatalogConcernTemplate"
+          published_on: "2007-10-29 05:25:58"
+          expirationdate: "2099-01-10"
+          number: 1
+
+  - additional_contents_localizations:
+      translatable: [ title, content ]
+      multiline: [ content ]
+      rows:
+        - additional_content_id: 1
+          title: "Welcome to Koha"
+          content:
+            - "Welcome to Koha. Koha is a full-featured open-source ILS. Developed initially in New Zealand by Katipo Communications Ltd and first deployed in January of 2000 for Horowhenua Library Trust, Koha is currently maintained by a team of software providers and library technology staff from around the globe."
+          lang: "default"
+        - additional_content_id: 2
+          title: "What's Next?"
           content:
             - "Now that you've installed Koha, what's next? Here are some suggestions:"
             - "<ul>"
@@ -52,25 +76,15 @@ tables:
             - "</ul>"
             - ""
           lang: "default"
-          published_on: "2007-10-29 05:34:45"
-          expirationdate: "2099-01-10"
-          number: 2
 
-        - title: "Catalog concern help text"
-          category: "html_customizations"
-          location: "CatalogConcernHelp"
-          code: "CatalogConcernHelp_1"
+        - additional_content_id: 3
+          title: "Catalog concern help text"
           content:
               - "Please describe your concern clearly and the library will try to deal with it as quickly as possible"
           lang: "default"
-          published_on: "2007-10-29 05:25:58"
-          expirationdate: "2099-01-10"
-          number: 1
 
-        - title: "Catalog concern template text"
-          category: "html_customizations"
-          location: "CatalogConcernTemplate"
-          code: "CatalogConcernTemplate_1"
+        - additional_content_id: 4
+          title: "Catalog concern template text"
           content:
             - "**Describe the concern**"
             - "A clear and concise description of what the concern is."
@@ -85,6 +99,3 @@ tables:
             - "**Expected behavior**"
             - "A clear and concise description of what you expected to happen."
           lang: "default"
-          published_on: "2007-10-29 05:25:58"
-          expirationdate: "2099-01-10"
-          number: 1
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 3f666d2df94..05144a86fbd 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -192,21 +192,18 @@ DROP TABLE IF EXISTS `additional_contents`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `additional_contents` (
-  `idnew` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content',
+  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content category',
   `category` varchar(20) NOT NULL COMMENT 'category for the additional content',
   `code` varchar(100) NOT NULL COMMENT 'code to group content per lang',
   `location` varchar(255) NOT NULL COMMENT 'location of the additional content',
   `branchcode` varchar(10) DEFAULT NULL COMMENT 'branch code users to create branch specific additional content, NULL is every branch.',
-  `title` varchar(250) NOT NULL DEFAULT '' COMMENT 'title of the additional content',
-  `content` mediumtext NOT NULL COMMENT 'the body of your additional content',
-  `lang` varchar(50) NOT NULL DEFAULT '' COMMENT 'location for the additional content(koha is the staff interface, slip is the circulation receipt and language codes are for the opac)',
   `published_on` date DEFAULT NULL COMMENT 'publication date',
   `updated_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'last modification',
   `expirationdate` date DEFAULT NULL COMMENT 'date the additional content is set to expire or no longer be visible',
   `number` int(11) DEFAULT NULL COMMENT 'the order in which this additional content appears in that specific location',
   `borrowernumber` int(11) DEFAULT NULL COMMENT 'The user who created the additional content',
-  PRIMARY KEY (`idnew`),
-  UNIQUE KEY `additional_contents_uniq` (`category`,`code`,`branchcode`,`lang`),
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `additional_contents_uniq` (`category`,`code`,`branchcode`),
   KEY `additional_contents_borrowernumber_fk` (`borrowernumber`),
   KEY `additional_contents_branchcode_ibfk` (`branchcode`),
   CONSTRAINT `additional_contents_branchcode_ibfk` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
@@ -214,6 +211,18 @@ CREATE TABLE `additional_contents` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
+CREATE TABLE `additional_contents_localizations` (
+  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier for the additional content',
+  `additional_content_id` int(10) unsigned NOT NULL COMMENT 'link to the additional content',
+  `title` varchar(250) NOT NULL DEFAULT '' COMMENT 'title of the additional content',
+  `content` mediumtext NOT NULL COMMENT 'the body of your additional content',
+  `lang` varchar(50) NOT NULL DEFAULT '' COMMENT 'lang',
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `additional_contents_localizations_uniq` (`additional_content_id`,`lang`),
+  CONSTRAINT `additional_contents_localizations_ibfk1` FOREIGN KEY (`additional_content_id`) REFERENCES `additional_contents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
 --
 -- Table structure for table `additional_field_values`
 --
-- 
2.25.1