From 64ccbdb8f223cc7f8320dd09262ecd6724da4266 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 8 Jul 2021 09:43:58 +0200 Subject: [PATCH] Bug 25078: Move DB revs up to 21.06.00.016 Signed-off-by: Kyle M Hall Signed-off-by: Jonathan Druart --- installer/data/mysql/db_revs/210600001.pl | 13 + installer/data/mysql/db_revs/210600002.pl | 28 ++ installer/data/mysql/db_revs/210600003.pl | 33 ++ installer/data/mysql/db_revs/210600004.pl | 21 + installer/data/mysql/db_revs/210600005.pl | 15 + installer/data/mysql/db_revs/210600006.pl | 31 ++ installer/data/mysql/db_revs/210600007.pl | 22 ++ installer/data/mysql/db_revs/210600008.pl | 15 + installer/data/mysql/db_revs/210600009.pl | 23 ++ installer/data/mysql/db_revs/210600010.pl | 15 + installer/data/mysql/db_revs/210600011.pl | 39 ++ installer/data/mysql/db_revs/210600012.pl | 66 ++++ installer/data/mysql/db_revs/210600013.pl | 42 ++ installer/data/mysql/db_revs/210600014.pl | 22 ++ installer/data/mysql/db_revs/210600015.pl | 12 + installer/data/mysql/db_revs/210600016.pl | 146 +++++++ installer/data/mysql/updatedatabase.pl | 459 ---------------------- 17 files changed, 543 insertions(+), 459 deletions(-) create mode 100644 installer/data/mysql/db_revs/210600001.pl create mode 100644 installer/data/mysql/db_revs/210600002.pl create mode 100644 installer/data/mysql/db_revs/210600003.pl create mode 100644 installer/data/mysql/db_revs/210600004.pl create mode 100644 installer/data/mysql/db_revs/210600005.pl create mode 100644 installer/data/mysql/db_revs/210600006.pl create mode 100644 installer/data/mysql/db_revs/210600007.pl create mode 100644 installer/data/mysql/db_revs/210600008.pl create mode 100644 installer/data/mysql/db_revs/210600009.pl create mode 100644 installer/data/mysql/db_revs/210600010.pl create mode 100644 installer/data/mysql/db_revs/210600011.pl create mode 100644 installer/data/mysql/db_revs/210600012.pl create mode 100644 installer/data/mysql/db_revs/210600013.pl create mode 100644 installer/data/mysql/db_revs/210600014.pl create mode 100644 installer/data/mysql/db_revs/210600015.pl create mode 100644 installer/data/mysql/db_revs/210600016.pl diff --git a/installer/data/mysql/db_revs/210600001.pl b/installer/data/mysql/db_revs/210600001.pl new file mode 100644 index 0000000000..ee84b39907 --- /dev/null +++ b/installer/data/mysql/db_revs/210600001.pl @@ -0,0 +1,13 @@ +use Modern::Perl; + +{ + bug_number => "28489", + description => "Modify sessions.a_session from longtext to longblob", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + $dbh->do('DELETE FROM sessions'); + $dbh->do('ALTER TABLE sessions MODIFY a_session LONGBLOB NOT NULL'); + }, +} diff --git a/installer/data/mysql/db_revs/210600002.pl b/installer/data/mysql/db_revs/210600002.pl new file mode 100644 index 0000000000..25e0f6d9e7 --- /dev/null +++ b/installer/data/mysql/db_revs/210600002.pl @@ -0,0 +1,28 @@ +use Modern::Perl; + +{ + bug_number => "28490", + description => "Bring back accidentally deleted relationship columns", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + if( !column_exists( 'borrower_modifications', 'relationship' ) ) { + $dbh->do(q{ + ALTER TABLE borrower_modifications ADD COLUMN `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `borrowernotes` + }); + } + + if( !column_exists( 'borrowers', 'relationship' ) ) { + $dbh->do(q{ + ALTER TABLE borrowers ADD COLUMN `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor' AFTER `borrowernotes` + }); + } + + if( !column_exists( 'deletedborrowers', 'relationship' ) ) { + $dbh->do(q{ + ALTER TABLE deletedborrowers ADD COLUMN `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor' AFTER `borrowernotes` + }); + } + }, +} diff --git a/installer/data/mysql/db_revs/210600003.pl b/installer/data/mysql/db_revs/210600003.pl new file mode 100644 index 0000000000..edaa580737 --- /dev/null +++ b/installer/data/mysql/db_revs/210600003.pl @@ -0,0 +1,33 @@ +use Modern::Perl; + +{ + bug_number => "24434", + description => "Add 'WrongTransfer' to branchtransfers.cancellation_reason enum", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + # add 'wrongtransfer' to branchtransfers cancellation_reason enum + $dbh->do( + q{ + alter table + `branchtransfers` + modify column + `cancellation_reason` enum( + 'manual', + 'stockrotationadvance', + 'stockrotationrepatriation', + 'returntohome', + 'returntoholding', + 'rotatingcollection', + 'reserve', + 'lostreserve', + 'cancelreserve', + 'itemlost', + 'wrongtransfer' + ) + after `comments` + } + ); + }, +} diff --git a/installer/data/mysql/db_revs/210600004.pl b/installer/data/mysql/db_revs/210600004.pl new file mode 100644 index 0000000000..e8a08841f0 --- /dev/null +++ b/installer/data/mysql/db_revs/210600004.pl @@ -0,0 +1,21 @@ +use Modern::Perl; + +{ + bug_number => "15788", + description => "Split edit_borrowers permission", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + $dbh->do(q{ + INSERT IGNORE permissions (module_bit, code, description) + VALUES + (4, 'delete_borrowers', 'Delete borrowers') + }); + + $dbh->do(q{ + INSERT IGNORE INTO user_permissions (borrowernumber, module_bit, code) + SELECT borrowernumber, 4, 'delete_borrowers' FROM user_permissions WHERE code = 'edit_borrowers' + }); + }, +} diff --git a/installer/data/mysql/db_revs/210600005.pl b/installer/data/mysql/db_revs/210600005.pl new file mode 100644 index 0000000000..8386af8878 --- /dev/null +++ b/installer/data/mysql/db_revs/210600005.pl @@ -0,0 +1,15 @@ +use Modern::Perl; + +{ + bug_number => "26205", + description => "Add new system preference NewsLog to log news changes", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + $dbh->do( q{ + INSERT IGNORE INTO systempreferences (variable, value, explanation, options, type) + VALUES ('NewsLog', '0', 'If enabled, log OPAC News changes', '', 'YesNo') + }); + }, +} diff --git a/installer/data/mysql/db_revs/210600006.pl b/installer/data/mysql/db_revs/210600006.pl new file mode 100644 index 0000000000..38536485a7 --- /dev/null +++ b/installer/data/mysql/db_revs/210600006.pl @@ -0,0 +1,31 @@ +use Modern::Perl; + +{ + bug_number => "14237", + description => "Add individual bibliographic records to course reserves", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + my $out = $args->{out}; + + unless( column_exists( 'course_items', 'biblionumber') ) { + $dbh->do(q{ ALTER TABLE course_items ADD `biblionumber` int(11) AFTER `itemnumber` }); + + $dbh->do(q{ + UPDATE course_items + LEFT JOIN items ON items.itemnumber=course_items.itemnumber + SET course_items.biblionumber=items.biblionumber + WHERE items.itemnumber IS NOT NULL + }); + + $dbh->do(q{ ALTER TABLE course_items MODIFY COLUMN `biblionumber` INT(11) NOT NULL }); + + $dbh->do(q{ ALTER TABLE course_items ADD CONSTRAINT `fk_course_items_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE }); + $dbh->do(q{ ALTER TABLE course_items CHANGE `itemnumber` `itemnumber` int(11) DEFAULT NULL }); + + say $out "Add course_items.biblionumber column"; + say $out "Add fk_course_items_biblionumber constraint"; + say $out "Change course_items.itemnumber to allow NULL values"; + } + }, +} diff --git a/installer/data/mysql/db_revs/210600007.pl b/installer/data/mysql/db_revs/210600007.pl new file mode 100644 index 0000000000..7bef60a8cd --- /dev/null +++ b/installer/data/mysql/db_revs/210600007.pl @@ -0,0 +1,22 @@ +use Modern::Perl; + +{ + bug_number => "11879", + description => "Add a new field to patron record: main contact method", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + if( !column_exists( 'borrowers', 'primary_contact_method' ) ) { + $dbh->do( "ALTER TABLE `borrowers` ADD COLUMN `primary_contact_method` VARCHAR(45) DEFAULT NULL AFTER `autorenew_checkouts`" ); + } + + if( !column_exists( 'deletedborrowers', 'primary_contact_method' ) ) { + $dbh->do( "ALTER TABLE `deletedborrowers` ADD COLUMN `primary_contact_method` VARCHAR(45) DEFAULT NULL AFTER `autorenew_checkouts`" ); + } + + if( !column_exists( 'borrower_modifications', 'primary_contact_method' ) ) { + $dbh->do( "ALTER TABLE `borrower_modifications` ADD COLUMN `primary_contact_method` VARCHAR(45) DEFAULT NULL AFTER `gdpr_proc_consent`" ); + } + }, +} diff --git a/installer/data/mysql/db_revs/210600008.pl b/installer/data/mysql/db_revs/210600008.pl new file mode 100644 index 0000000000..1c82f5c94e --- /dev/null +++ b/installer/data/mysql/db_revs/210600008.pl @@ -0,0 +1,15 @@ +use Modern::Perl; + +{ + bug_number => "20310", + description => "Add new system preference ArticleRequestsOpacHostRedirection", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + $dbh->do(q{ + INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES + ('ArticleRequestsOpacHostRedirection', '0', NULL, 'Enables redirection from child to host when requesting article on OPAC', 'YesNo') + }); + }, +} diff --git a/installer/data/mysql/db_revs/210600009.pl b/installer/data/mysql/db_revs/210600009.pl new file mode 100644 index 0000000000..174e2048d1 --- /dev/null +++ b/installer/data/mysql/db_revs/210600009.pl @@ -0,0 +1,23 @@ +use Modern::Perl; + +{ + bug_number => "20472", + description => "Add columns format and urls in article_requests table", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + unless ( column_exists('article_requests', 'format') ) { + $dbh->do(q| + ALTER TABLE article_requests + ADD COLUMN `format` enum('PHOTOCOPY', 'SCAN') NOT NULL DEFAULT 'PHOTOCOPY' AFTER notes + |); + } + unless ( column_exists('article_requests', 'urls') ) { + $dbh->do(q| + ALTER TABLE article_requests + ADD COLUMN `urls` MEDIUMTEXT AFTER format + |); + } + }, +} diff --git a/installer/data/mysql/db_revs/210600010.pl b/installer/data/mysql/db_revs/210600010.pl new file mode 100644 index 0000000000..484ee395e0 --- /dev/null +++ b/installer/data/mysql/db_revs/210600010.pl @@ -0,0 +1,15 @@ +use Modern::Perl; + +{ + bug_number => "20472", + description => "Add new system preference ArticleRequestsSupportedFormats", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + $dbh->do(q{ + INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES + ('ArticleRequestsSupportedFormats', 'PHOTOCOPY', 'PHOTOCOPY|SCAN', 'List supported formats between vertical bars', 'free') + }); + }, +} diff --git a/installer/data/mysql/db_revs/210600011.pl b/installer/data/mysql/db_revs/210600011.pl new file mode 100644 index 0000000000..12f7dd29ba --- /dev/null +++ b/installer/data/mysql/db_revs/210600011.pl @@ -0,0 +1,39 @@ +use Modern::Perl; + +{ + bug_number => "28567", + description => "Set to NULL empty branches fields", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + my @fields = qw( + branchname + branchaddress1 + branchaddress2 + branchaddress3 + branchzip + branchcity + branchstate + branchcountry + branchphone + branchfax + branchemail + branchillemail + branchreplyto + branchreturnpath + branchurl + branchip + branchnotes + opac_info + marcorgcode + ); + for my $f ( @fields ) { + $dbh->do(qq{ + UPDATE branches + SET $f = NULL + WHERE $f = "" + }); + } + }, +} diff --git a/installer/data/mysql/db_revs/210600012.pl b/installer/data/mysql/db_revs/210600012.pl new file mode 100644 index 0000000000..c46644f887 --- /dev/null +++ b/installer/data/mysql/db_revs/210600012.pl @@ -0,0 +1,66 @@ +use Modern::Perl; + +{ + bug_number => "15067", + description => "Add missing languages", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + if( !unique_key_exists( 'language_subtag_registry', 'uniq_lang' ) ) { + $dbh->do(q{ + ALTER TABLE language_subtag_registry + ADD UNIQUE KEY uniq_lang (subtag, type) + }); + }; + + if( !unique_key_exists( 'language_descriptions', 'uniq_desc' ) ) { + $dbh->do(q{ + ALTER TABLE language_descriptions + ADD UNIQUE KEY uniq_desc (subtag, type, lang) + }); + }; + + if( !unique_key_exists( 'language_rfc4646_to_iso639', 'uniq_code' ) ) { + $dbh->do(q{ + ALTER TABLE language_rfc4646_to_iso639 + ADD UNIQUE KEY uniq_code (rfc4646_subtag, iso639_2_code) + }); + }; + + $dbh->do(q{ + INSERT IGNORE INTO language_subtag_registry (subtag, type, description, added) + VALUES + ('et', 'language', 'Estonian', now()), + ('lv', 'language', 'Latvian', now()), + ('lt', 'language', 'Lithuanian', now()), + ('iu', 'language', 'Inuktitut', now()), + ('ik', 'language', 'Inupiaq', now()) + }); + + $dbh->do(q{ + INSERT IGNORE INTO language_descriptions (subtag, type, lang, description) + VALUES + ('et', 'language', 'en', 'Estonian'), + ('et', 'language', 'et', 'Eesti'), + ('lv', 'language', 'en', 'Latvian'), + ('lv', 'language', 'lv', 'Latvija'), + ('lt', 'language', 'en', 'Lithuanian'), + ('lt', 'language', 'lt', 'Lietuvių'), + ('iu', 'language', 'en', 'Inuktitut'), + ('iu', 'language', 'iu', 'ᐃᓄᒃᑎᑐᑦ'), + ('ik', 'language', 'en', 'Inupiaq'), + ('ik', 'language', 'ik', 'Iñupiaq') + }); + + $dbh->do(q{ + INSERT IGNORE INTO language_rfc4646_to_iso639 (rfc4646_subtag, iso639_2_code) + VALUES + ('et', 'est'), + ('lv', 'lav'), + ('lt', 'lit'), + ('iu', 'iku'), + ('ik', 'ipk') + }); + }, +} diff --git a/installer/data/mysql/db_revs/210600013.pl b/installer/data/mysql/db_revs/210600013.pl new file mode 100644 index 0000000000..c73edc7cbb --- /dev/null +++ b/installer/data/mysql/db_revs/210600013.pl @@ -0,0 +1,42 @@ +use Modern::Perl; + +{ + bug_number => "22435", + description => "Update existing offsets", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + # Remove foreign key for offset types + if ( foreign_key_exists( 'account_offsets', 'account_offsets_ibfk_t' ) ) { + $dbh->do( "ALTER TABLE account_offsets DROP FOREIGN KEY account_offsets_ibfk_t" ); + } + + # Drop account_offset_types table + $dbh->do( "DROP TABLE IF EXISTS account_offset_types" ); + + # Update offset_types to 'CREATE' where appropriate + $dbh->do( "UPDATE account_offsets SET type = 'CREATE' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND ( debit_id IS NULL OR credit_id IS NULL)" ); + $dbh->do( "UPDATE account_offsets SET amount = ABS(amount) WHERE type = 'CREATE'" ); + + # Update offset_types to 'APPLY' where appropriate + $dbh->do( "UPDATE account_offsets SET type = 'APPLY' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND type != 'CREATE' AND type != 'VOID'" ); + + # Update table to ENUM + $dbh->do( + q{ + ALTER TABLE + `account_offsets` + MODIFY COLUMN + `type` enum( + 'CREATE', + 'APPLY', + 'VOID', + 'OVERDUE_INCREASE', + 'OVERDUE_DECREASE' + ) + AFTER `debit_id` + } + ); + }, +} diff --git a/installer/data/mysql/db_revs/210600014.pl b/installer/data/mysql/db_revs/210600014.pl new file mode 100644 index 0000000000..76017c718e --- /dev/null +++ b/installer/data/mysql/db_revs/210600014.pl @@ -0,0 +1,22 @@ +use Modern::Perl; + +{ + bug_number => "28813", + description => "Update delivery_note to failure_code in message_queue", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + if ( column_exists('message_queue', 'delivery_note') ) { + $dbh->do(q{ + ALTER TABLE message_queue CHANGE COLUMN delivery_note failure_code MEDIUMTEXT + }); + } + + if( !column_exists( 'message_queue', 'failure_code' ) ) { + $dbh->do(q{ + ALTER TABLE message_queue ADD failure_code mediumtext AFTER content_type + }); + } + }, +} diff --git a/installer/data/mysql/db_revs/210600015.pl b/installer/data/mysql/db_revs/210600015.pl new file mode 100644 index 0000000000..9b53ba0e3a --- /dev/null +++ b/installer/data/mysql/db_revs/210600015.pl @@ -0,0 +1,12 @@ +use Modern::Perl; + +{ + bug_number => "12561", + description => "Remove system preferences HighlightOwnItemsOnOPAC and HighlightOwnItemsOnOPACWhich", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + $dbh->do( q{ DELETE FROM systempreferences WHERE variable IN ('HighlightOwnItemsOnOPAC', 'HighlightOwnItemsOnOPACWhich')} ); + }, +} diff --git a/installer/data/mysql/db_revs/210600016.pl b/installer/data/mysql/db_revs/210600016.pl new file mode 100644 index 0000000000..27aa7d07b0 --- /dev/null +++ b/installer/data/mysql/db_revs/210600016.pl @@ -0,0 +1,146 @@ +use Modern::Perl; + +{ + bug_number => "24387", + description => "Rename opac_news with additional_contents", + up => sub { + my ($args) = @_; + my $dbh = $args->{dbh}; + + 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`) + |); + + + }, +} diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 6225ee0b7b..11f61583eb 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -24286,465 +24286,6 @@ if( CheckVersion( $DBversion ) ) { NewVersion( $DBversion, "", ["🎵 Run, rabbit run. 🎶", "Dig that hole, forget the sun,", "And when at last the work is done", "Don't sit down it's time to dig another one."] ); } -$DBversion = '21.06.00.001'; -if ( CheckVersion($DBversion) ) { - $dbh->do('DELETE FROM sessions'); - $dbh->do('ALTER TABLE sessions MODIFY a_session LONGBLOB NOT NULL'); - - NewVersion( $DBversion, '28489', 'Modify sessions.a_session from longtext to longblob' ); -} - -$DBversion = '21.06.00.002'; -if( CheckVersion( $DBversion ) ) { - if( !column_exists( 'borrower_modifications', 'relationship' ) ) { - $dbh->do(q{ - ALTER TABLE borrower_modifications ADD COLUMN `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `borrowernotes` - }); - } - - if( !column_exists( 'borrowers', 'relationship' ) ) { - $dbh->do(q{ - ALTER TABLE borrowers ADD COLUMN `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor' AFTER `borrowernotes` - }); - } - - if( !column_exists( 'deletedborrowers', 'relationship' ) ) { - $dbh->do(q{ - ALTER TABLE deletedborrowers ADD COLUMN `relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'used for children to include the relationship to their guarantor' AFTER `borrowernotes` - }); - } - - NewVersion( $DBversion, 28490, "Bring back accidentally deleted relationship columns"); -} - -$DBversion = '21.06.00.003'; -if( CheckVersion( $DBversion ) ) { - - # Add 'WrongTransfer' to branchtransfers cancellation_reason enum - $dbh->do( - q{ - ALTER TABLE - `branchtransfers` - MODIFY COLUMN - `cancellation_reason` enum( - 'Manual', - 'StockrotationAdvance', - 'StockrotationRepatriation', - 'ReturnToHome', - 'ReturnToHolding', - 'RotatingCollection', - 'Reserve', - 'LostReserve', - 'CancelReserve', - 'ItemLost', - 'WrongTransfer' - ) - AFTER `comments` - } - ); - - NewVersion( $DBversion, 24434, "Add 'WrongTransfer' to branchtransfers.cancellation_reason enum"); -} - -$DBversion = '21.06.00.004'; -if ( CheckVersion($DBversion) ) { - - $dbh->do(q{ - INSERT IGNORE permissions (module_bit, code, description) - VALUES - (4, 'delete_borrowers', 'Delete borrowers') - }); - - $dbh->do(q{ - INSERT IGNORE INTO user_permissions (borrowernumber, module_bit, code) - SELECT borrowernumber, 4, 'delete_borrowers' FROM user_permissions WHERE code = 'edit_borrowers' - }); - - NewVersion( $DBversion, 15788, "Split edit_borrowers permission" ); -} - -$DBversion = '21.06.00.005'; -if( CheckVersion( $DBversion ) ) { - $dbh->do( q{ - INSERT IGNORE INTO systempreferences (variable, value, explanation, options, type) - VALUES ('NewsLog', '0', 'If enabled, log OPAC News changes', '', 'YesNo') - }); - - NewVersion( $DBversion, 26205, "Add new system preference NewsLog to log news changes"); -} - -$DBversion = '21.06.00.006'; -if( CheckVersion( $DBversion ) ){ - unless( column_exists( 'course_items', 'biblionumber') ) { - $dbh->do(q{ ALTER TABLE course_items ADD `biblionumber` int(11) AFTER `itemnumber` }); - - $dbh->do(q{ - UPDATE course_items - LEFT JOIN items ON items.itemnumber=course_items.itemnumber - SET course_items.biblionumber=items.biblionumber - WHERE items.itemnumber IS NOT NULL - }); - - $dbh->do(q{ ALTER TABLE course_items MODIFY COLUMN `biblionumber` INT(11) NOT NULL }); - - $dbh->do(q{ ALTER TABLE course_items ADD CONSTRAINT `fk_course_items_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE }); - $dbh->do(q{ ALTER TABLE course_items CHANGE `itemnumber` `itemnumber` int(11) DEFAULT NULL }); - } - - NewVersion( $DBversion, 14237, ["Add course_items.biblionumber column", "Add fk_course_items_biblionumber constraint", "Change course_items.itemnumber to allow NULL values"] ); -} - -$DBversion = '21.06.00.007'; -if( CheckVersion( $DBversion ) ) { - if( !column_exists( 'borrowers', 'primary_contact_method' ) ) { - $dbh->do( "ALTER TABLE `borrowers` ADD COLUMN `primary_contact_method` VARCHAR(45) DEFAULT NULL AFTER `autorenew_checkouts`" ); - } - - if( !column_exists( 'deletedborrowers', 'primary_contact_method' ) ) { - $dbh->do( "ALTER TABLE `deletedborrowers` ADD COLUMN `primary_contact_method` VARCHAR(45) DEFAULT NULL AFTER `autorenew_checkouts`" ); - } - - if( !column_exists( 'borrower_modifications', 'primary_contact_method' ) ) { - $dbh->do( "ALTER TABLE `borrower_modifications` ADD COLUMN `primary_contact_method` VARCHAR(45) DEFAULT NULL AFTER `gdpr_proc_consent`" ); - } - - NewVersion( $DBversion, 11879, "Add a new field to patron record: main contact method"); -} - -$DBversion = '21.06.00.008'; -if( CheckVersion( $DBversion ) ) { - $dbh->do(q{ - INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES - ('ArticleRequestsOpacHostRedirection', '0', NULL, 'Enables redirection from child to host when requesting article on OPAC', 'YesNo') - }); - NewVersion( $DBversion, 20310, "Add pref ArticleRequestsOpacHostRedirection"); -} - -$DBversion = '21.06.00.009'; -if( CheckVersion( $DBversion ) ) { - unless ( column_exists('article_requests', 'format') ) { - $dbh->do(q| - ALTER TABLE article_requests - ADD COLUMN `format` enum('PHOTOCOPY', 'SCAN') NOT NULL DEFAULT 'PHOTOCOPY' AFTER notes - |); - } - unless ( column_exists('article_requests', 'urls') ) { - $dbh->do(q| - ALTER TABLE article_requests - ADD COLUMN `urls` MEDIUMTEXT AFTER format - |); - } - NewVersion( $DBversion, 20472, "Add columns format and urls in article_requests table"); -} - -$DBversion = '21.06.00.010'; -if( CheckVersion( $DBversion ) ) { - $dbh->do(q{ - INSERT IGNORE INTO systempreferences ( `variable`, `value`, `options`, `explanation`, `type` ) VALUES - ('ArticleRequestsSupportedFormats', 'PHOTOCOPY', 'PHOTOCOPY|SCAN', 'List supported formats between vertical bars', 'free') - }); - NewVersion( $DBversion, 20472, "Add syspref ArticleRequestsSupportedFormats"); -} - -$DBversion = '21.06.00.011'; -if( CheckVersion( $DBversion ) ) { - - my @fields = qw( - branchname - branchaddress1 - branchaddress2 - branchaddress3 - branchzip - branchcity - branchstate - branchcountry - branchphone - branchfax - branchemail - branchillemail - branchreplyto - branchreturnpath - branchurl - branchip - branchnotes - opac_info - marcorgcode - ); - for my $f ( @fields ) { - $dbh->do(qq{ - UPDATE branches - SET $f = NULL - WHERE $f = "" - }); - } - - NewVersion( $DBversion, 28567, "Set to NULL empty branches fields"); -} - -$DBversion = '21.06.00.012'; -if( CheckVersion( $DBversion ) ) { - if( !unique_key_exists( 'language_subtag_registry', 'uniq_lang' ) ) { - $dbh->do(q{ - ALTER TABLE language_subtag_registry - ADD UNIQUE KEY uniq_lang (subtag, type) - }); - }; - - if( !unique_key_exists( 'language_descriptions', 'uniq_desc' ) ) { - $dbh->do(q{ - ALTER TABLE language_descriptions - ADD UNIQUE KEY uniq_desc (subtag, type, lang) - }); - }; - - if( !unique_key_exists( 'language_rfc4646_to_iso639', 'uniq_code' ) ) { - $dbh->do(q{ - ALTER TABLE language_rfc4646_to_iso639 - ADD UNIQUE KEY uniq_code (rfc4646_subtag, iso639_2_code) - }); - }; - - $dbh->do(q{ - INSERT IGNORE INTO language_subtag_registry (subtag, type, description, added) - VALUES - ('et', 'language', 'Estonian', now()), - ('lv', 'language', 'Latvian', now()), - ('lt', 'language', 'Lithuanian', now()), - ('iu', 'language', 'Inuktitut', now()), - ('ik', 'language', 'Inupiaq', now()) - }); - - $dbh->do(q{ - INSERT IGNORE INTO language_descriptions (subtag, type, lang, description) - VALUES - ('et', 'language', 'en', 'Estonian'), - ('et', 'language', 'et', 'Eesti'), - ('lv', 'language', 'en', 'Latvian'), - ('lv', 'language', 'lv', 'Latvija'), - ('lt', 'language', 'en', 'Lithuanian'), - ('lt', 'language', 'lt', 'Lietuvių'), - ('iu', 'language', 'en', 'Inuktitut'), - ('iu', 'language', 'iu', 'ᐃᓄᒃᑎᑐᑦ'), - ('ik', 'language', 'en', 'Inupiaq'), - ('ik', 'language', 'ik', 'Iñupiaq') - }); - - $dbh->do(q{ - INSERT IGNORE INTO language_rfc4646_to_iso639 (rfc4646_subtag, iso639_2_code) - VALUES - ('et', 'est'), - ('lv', 'lav'), - ('lt', 'lit'), - ('iu', 'iku'), - ('ik', 'ipk') - }); - - NewVersion( $DBversion, 15067, "Add missing languages" ); -} - -$DBversion = '21.06.00.013'; -if( CheckVersion( $DBversion ) ) { - # Remove foreign key for offset types - if ( foreign_key_exists( 'account_offsets', 'account_offsets_ibfk_t' ) ) { - $dbh->do( "ALTER TABLE account_offsets DROP FOREIGN KEY account_offsets_ibfk_t" ); - } - - # Drop account_offset_types table - $dbh->do( "DROP TABLE IF EXISTS account_offset_types" ); - - # Update offset_types to 'CREATE' where appropriate - $dbh->do( "UPDATE account_offsets SET type = 'CREATE' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND ( debit_id IS NULL OR credit_id IS NULL)" ); - $dbh->do( "UPDATE account_offsets SET amount = ABS(amount) WHERE type = 'CREATE'" ); - - # Update offset_types to 'APPLY' where appropriate - $dbh->do( "UPDATE account_offsets SET type = 'APPLY' WHERE type != 'OVERDUE_INCREASE' AND type != 'OVERDUE_DECREASE' AND type != 'CREATE' AND type != 'VOID'" ); - - # Update table to ENUM - $dbh->do( - q{ - ALTER TABLE - `account_offsets` - MODIFY COLUMN - `type` enum( - 'CREATE', - 'APPLY', - 'VOID', - 'OVERDUE_INCREASE', - 'OVERDUE_DECREASE' - ) - AFTER `debit_id` - } - ); - - - NewVersion( $DBversion, 22435, "Update existing offsets"); -} - -$DBversion = '21.06.00.014'; -if( CheckVersion( $DBversion ) ) { - if ( column_exists('message_queue', 'delivery_note') ) { - $dbh->do(q{ - ALTER TABLE message_queue CHANGE COLUMN delivery_note failure_code MEDIUMTEXT - }); - } - - if( !column_exists( 'message_queue', 'failure_code' ) ) { - $dbh->do(q{ - ALTER TABLE message_queue ADD failure_code mediumtext AFTER content_type - }); - } - - NewVersion( $DBversion, 28813, "Update delivery_note to failure_code in message_queue"); -} - -$DBversion = '21.06.00.015'; -if( CheckVersion( $DBversion ) ) { - - $dbh->do( q{ DELETE FROM systempreferences WHERE variable IN ('HighlightOwnItemsOnOPAC', 'HighlightOwnItemsOnOPACWhich')} ); - - 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