From 293ed9a8d0058406fcdea76dd0470a5f05ebf735 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 16 Aug 2021 09:01:28 +0000 Subject: [PATCH] Bug 24387: DBRev 21.06.00.016 Signed-off-by: Jonathan Druart --- Koha.pm | 2 +- .../data/mysql/atomicupdate/bug_24387.perl | 139 ----------------- installer/data/mysql/updatedatabase.pl | 140 ++++++++++++++++++ 3 files changed, 141 insertions(+), 140 deletions(-) delete mode 100644 installer/data/mysql/atomicupdate/bug_24387.perl diff --git a/Koha.pm b/Koha.pm index 038adc912d..4bbd1958f0 100644 --- a/Koha.pm +++ b/Koha.pm @@ -29,7 +29,7 @@ use vars qw{ $VERSION }; # - #4 : the developer version. The 4th number is the database subversion. # used by developers when the database changes. updatedatabase take care of the changes itself # and is automatically called by Auth.pm when needed. -$VERSION = "21.06.00.015"; +$VERSION = "21.06.00.016"; sub version { return $VERSION; diff --git a/installer/data/mysql/atomicupdate/bug_24387.perl b/installer/data/mysql/atomicupdate/bug_24387.perl deleted file mode 100644 index 26e3681ae8..0000000000 --- a/installer/data/mysql/atomicupdate/bug_24387.perl +++ /dev/null @@ -1,139 +0,0 @@ -$DBversion = 'XXX'; # will be replaced by the RM -if( CheckVersion( $DBversion ) ) { - - if( TableExists('opac_news') ) { - $dbh->do(q| - ALTER TABLE opac_news RENAME additional_contents - |); - } - - if ( foreign_key_exists('additional_contents', 'opac_news_branchcode_ibfk') ) { - - $dbh->do(q| - ALTER TABLE additional_contents - DROP KEY borrowernumber_fk, - DROP KEY opac_news_branchcode_ibfk, - DROP FOREIGN KEY borrowernumber_fk, - DROP FOREIGN KEY opac_news_branchcode_ibfk - |); - - $dbh->do(q| - ALTER TABLE additional_contents - ADD CONSTRAINT additional_contents_borrowernumber_fk - FOREIGN KEY (borrowernumber) - REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE - |); - - $dbh->do(q| - ALTER TABLE additional_contents - ADD CONSTRAINT additional_contents_branchcode_ibfk - FOREIGN KEY (branchcode) - REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE - |); - } - - $dbh->do(q| - UPDATE letter - SET content = REGEXP_REPLACE(content, '<<\\\\s*opac_news\.', '<do(q| - UPDATE letter - SET content = REGEXP_REPLACE(content, '\\\\[%\\\\s*opac_news\.', '[% additional_contents.') - |); - - $dbh->do(q| - UPDATE systempreferences - SET variable="AdditionalContentsEditor" - WHERE variable="NewsToolEditor" - |); - - $dbh->do(q| - UPDATE permissions - SET code="edit_additional_contents" - WHERE code="edit_news" - |); - - unless ( column_exists('additional_contents', 'category' ) ) { - $dbh->do(q| - ALTER TABLE additional_contents - ADD COLUMN `category` VARCHAR(20) NOT NULL COMMENT 'category for the additional content' - AFTER `idnew` - |); - } - unless ( column_exists('additional_contents', 'location' ) ) { - $dbh->do(q| - ALTER TABLE additional_contents - ADD COLUMN `location` VARCHAR(255) NOT NULL COMMENT 'location of the additional content' - AFTER `category` - |); - } - - unless ( column_exists('additional_contents', 'code' ) ) { - $dbh->do(q| - ALTER TABLE additional_contents - ADD COLUMN `code` VARCHAR(100) NOT NULL COMMENT 'code to group content per lang' - AFTER `category` - |); - } - - my $contents = $dbh->selectall_arrayref(q|SELECT * FROM additional_contents|, { Slice => {} }); - for my $c ( @$contents ) { - my ( $category, $location, $new_lang ); - if ( $c->{lang} eq '' ) { - $category = 'news'; - $location = 'staff_and_opac'; - $new_lang = 'default'; - } elsif ( $c->{lang} eq 'koha' ) { - $category = 'news'; - $location = 'staff_only'; - $new_lang = 'default'; - } elsif ( $c->{lang} eq 'slip' ) { - $category = 'news'; - $location = 'slip'; - $new_lang = 'default'; - } elsif ( $c->{lang} =~ m|_| ) { - ( $location, $new_lang ) = split '_', $c->{lang}; - $category = 'html_customizations' - } else { - $category = 'news'; - $location = 'opac_only'; - $new_lang = $c->{lang}; - } - - die "There is something wrong here, we didn't find a valid category for idnew=" . $c->{idnew} unless $category; - - # Now this is getting weird - # We are adding an extra news with the same code when the lang is not "default" (/"en") - - $new_lang = "default" if $new_lang eq 'en'; # Assume that "en" is "default" - - my $sth_update = $dbh->prepare(q| - UPDATE additional_contents - SET category=?, location=?, lang=? - WHERE idnew=? - |); - - my $parent_idnew; - if ( $new_lang ne 'default' ) { - $dbh->do(q| - INSERT INTO additional_contents(category, code, location, branchcode, title, content, lang, published_on, updated_on, expirationdate, number, borrowernumber) - VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) - |, undef, $category, 'tmp_code', $location, $c->{branchcode}, $c->{title}, $c->{content}, 'default', $c->{published_on}, $c->{updated_on}, $c->{expirationdate}, $c->{number}, $c->{borrowernumber}); - - $parent_idnew = $dbh->last_insert_id(undef, undef, 'additional_contents', undef); - } - $sth_update->execute($category, $location, $new_lang, $c->{idnew}); - - my $idnew = $parent_idnew || $c->{idnew}; - my $code = ( grep {$_ eq $location} qw( staff_and_opac staff_only opac_only slip ) ) ? "${location}_$idnew" : "News_$idnew"; - $dbh->do(q|UPDATE additional_contents SET code=? WHERE idnew = ?|, undef, $code, $idnew) if $parent_idnew; - $dbh->do(q|UPDATE additional_contents SET code=? WHERE idnew = ?|, undef, $code, $idnew); - } - - $dbh->do(q| - ALTER TABLE additional_contents - ADD UNIQUE KEY additional_contents_uniq (`category`,`code`,`branchcode`,`lang`) - |); - - NewVersion( $DBversion, 24387, "Rename opac_news with additional_contents"); -} diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 89db6b46e0..6225ee0b7b 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -24605,6 +24605,146 @@ if( CheckVersion( $DBversion ) ) { NewVersion( $DBversion, 12561, "Remove system preferences HighlightOwnItemsOnOPAC and HighlightOwnItemsOnOPACWhich"); } +$DBversion = '21.06.00.016'; +if( CheckVersion( $DBversion ) ) { + + if( TableExists('opac_news') ) { + $dbh->do(q| + ALTER TABLE opac_news RENAME additional_contents + |); + } + + if ( foreign_key_exists('additional_contents', 'opac_news_branchcode_ibfk') ) { + + $dbh->do(q| + ALTER TABLE additional_contents + DROP KEY borrowernumber_fk, + DROP KEY opac_news_branchcode_ibfk, + DROP FOREIGN KEY borrowernumber_fk, + DROP FOREIGN KEY opac_news_branchcode_ibfk + |); + + $dbh->do(q| + ALTER TABLE additional_contents + ADD CONSTRAINT additional_contents_borrowernumber_fk + FOREIGN KEY (borrowernumber) + REFERENCES borrowers (borrowernumber) ON DELETE SET NULL ON UPDATE CASCADE + |); + + $dbh->do(q| + ALTER TABLE additional_contents + ADD CONSTRAINT additional_contents_branchcode_ibfk + FOREIGN KEY (branchcode) + REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE + |); + } + + $dbh->do(q| + UPDATE letter + SET content = REGEXP_REPLACE(content, '<<\\\\s*opac_news\.', '<do(q| + UPDATE letter + SET content = REGEXP_REPLACE(content, '\\\\[%\\\\s*opac_news\.', '[% additional_contents.') + |); + + $dbh->do(q| + UPDATE systempreferences + SET variable="AdditionalContentsEditor" + WHERE variable="NewsToolEditor" + |); + + $dbh->do(q| + UPDATE permissions + SET code="edit_additional_contents" + WHERE code="edit_news" + |); + + unless ( column_exists('additional_contents', 'category' ) ) { + $dbh->do(q| + ALTER TABLE additional_contents + ADD COLUMN `category` VARCHAR(20) NOT NULL COMMENT 'category for the additional content' + AFTER `idnew` + |); + } + unless ( column_exists('additional_contents', 'location' ) ) { + $dbh->do(q| + ALTER TABLE additional_contents + ADD COLUMN `location` VARCHAR(255) NOT NULL COMMENT 'location of the additional content' + AFTER `category` + |); + } + + unless ( column_exists('additional_contents', 'code' ) ) { + $dbh->do(q| + ALTER TABLE additional_contents + ADD COLUMN `code` VARCHAR(100) NOT NULL COMMENT 'code to group content per lang' + AFTER `category` + |); + } + + my $contents = $dbh->selectall_arrayref(q|SELECT * FROM additional_contents|, { Slice => {} }); + for my $c ( @$contents ) { + my ( $category, $location, $new_lang ); + if ( $c->{lang} eq '' ) { + $category = 'news'; + $location = 'staff_and_opac'; + $new_lang = 'default'; + } elsif ( $c->{lang} eq 'koha' ) { + $category = 'news'; + $location = 'staff_only'; + $new_lang = 'default'; + } elsif ( $c->{lang} eq 'slip' ) { + $category = 'news'; + $location = 'slip'; + $new_lang = 'default'; + } elsif ( $c->{lang} =~ m|_| ) { + ( $location, $new_lang ) = split '_', $c->{lang}; + $category = 'html_customizations' + } else { + $category = 'news'; + $location = 'opac_only'; + $new_lang = $c->{lang}; + } + + die "There is something wrong here, we didn't find a valid category for idnew=" . $c->{idnew} unless $category; + + # Now this is getting weird + # We are adding an extra news with the same code when the lang is not "default" (/"en") + + $new_lang = "default" if $new_lang eq 'en'; # Assume that "en" is "default" + + my $sth_update = $dbh->prepare(q| + UPDATE additional_contents + SET category=?, location=?, lang=? + WHERE idnew=? + |); + + my $parent_idnew; + if ( $new_lang ne 'default' ) { + $dbh->do(q| + INSERT INTO additional_contents(category, code, location, branchcode, title, content, lang, published_on, updated_on, expirationdate, number, borrowernumber) + VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + |, undef, $category, 'tmp_code', $location, $c->{branchcode}, $c->{title}, $c->{content}, 'default', $c->{published_on}, $c->{updated_on}, $c->{expirationdate}, $c->{number}, $c->{borrowernumber}); + + $parent_idnew = $dbh->last_insert_id(undef, undef, 'additional_contents', undef); + } + $sth_update->execute($category, $location, $new_lang, $c->{idnew}); + + my $idnew = $parent_idnew || $c->{idnew}; + my $code = ( grep {$_ eq $location} qw( staff_and_opac staff_only opac_only slip ) ) ? "${location}_$idnew" : "News_$idnew"; + $dbh->do(q|UPDATE additional_contents SET code=? WHERE idnew = ?|, undef, $code, $idnew) if $parent_idnew; + $dbh->do(q|UPDATE additional_contents SET code=? WHERE idnew = ?|, undef, $code, $idnew); + } + + $dbh->do(q| + ALTER TABLE additional_contents + ADD UNIQUE KEY additional_contents_uniq (`category`,`code`,`branchcode`,`lang`) + |); + + NewVersion( $DBversion, 24387, "Rename opac_news with additional_contents"); +} + # SEE bug 13068 # if there is anything in the atomicupdate, read and execute it. my $update_dir = C4::Context->config('intranetdir') . '/installer/data/mysql/atomicupdate/'; -- 2.39.5