From 29fa0d50debce1682ab3d9da52fc9f227974da27 Mon Sep 17 00:00:00 2001 From: Jonathan Druart 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 "%%" + }, { Slice => {} }); + for my $template ( @$notice_templates ) { + my $new_content = $template->{content}; + $new_content =~ s||[% FOR content IN additional_contents %]|g; + $new_content =~ s||[% 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 "%<{code}, $template->{id}; + } + } + + }, +}; diff --git a/installer/data/mysql/en/mandatory/sample_notices.yml b/installer/data/mysql/en/mandatory/sample_notices.yml index e73f6b81676..802409e6d02 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 ~%]" - "

<>

" - "Checked out to <> <> <> <>
" - "(<>)
" @@ -768,15 +769,17 @@ tables: - "" - "
" - "" + - "[% IF additional_contents.count %]" - "

News

" - - "" + - "[% FOREACH content IN additional_contents %]" - "
" - - "
<>
" - - "

<>

" - - "

Posted on <>

" + - "
[% content.title %]
" + - "

[% content.content %]

" + - "

Posted on [% content.published_on | $KohaDates %]

" - "
" - "
" - - "
" + - "[% 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 db10ad18e9d..341ee8f3bcf 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:" - "
    " @@ -52,25 +76,15 @@ tables: - "
" - "" 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 cba889d5e0f..d149e3a8762 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -191,21 +191,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, @@ -213,6 +210,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