Bugzilla – Attachment 157541 Details for
Bug 31383
Additional contents: We need a parent and child table
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 31383: DB changes
Bug-31383-DB-changes.patch (text/plain), 15.21 KB, created by
Jonathan Druart
on 2023-10-20 15:06:47 UTC
(
hide
)
Description:
Bug 31383: DB changes
Filename:
MIME Type:
Creator:
Jonathan Druart
Created:
2023-10-20 15:06:47 UTC
Size:
15.21 KB
patch
obsolete
>From 0aa4e3a3cb0ee27132beeba1ae4f9bb8ab02071f 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 >Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> >Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> >--- > .../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 7a65191e077..8d368e659ed 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 29ff4c0c61e..3e37e15b35c 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.34.1
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 31383
:
147891
|
147892
|
147893
|
147894
|
147895
|
147896
|
149813
|
149814
|
149815
|
149816
|
149817
|
149818
|
152423
|
152424
|
152425
|
152426
|
152427
|
152428
|
152545
|
152546
|
152547
|
152566
|
152660
|
152661
|
152662
|
152663
|
152664
|
152665
|
152666
|
152667
|
152668
|
152669
|
153013
|
153666
|
153667
|
153668
|
153669
|
153670
|
153671
|
153672
|
153673
|
153674
|
153675
|
153676
|
156048
|
156049
|
156050
|
156051
|
156052
|
156053
|
156054
|
156055
|
156056
|
156057
|
156058
|
156265
|
156266
|
156267
|
156268
|
156269
|
156270
|
156271
|
156272
|
156273
|
156274
|
156275
|
156276
|
157539
|
157540
| 157541 |
157542
|
157543
|
157544
|
157545
|
157546
|
157547
|
157548
|
157549
|
157550
|
157580
|
157585
|
157640
|
157741
|
157742