From 358d5f2ab2856322f3138a35fd08fd879e16bf24 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 16 Feb 2018 18:10:23 -0300 Subject: [PATCH] Bug 18336: DBRev 17.12.00.016 Signed-off-by: Jonathan Druart --- Koha.pm | 2 +- .../mysql/atomicupdate/bug_18336_utf8mb4.perl | 157 ------------------ installer/data/mysql/updatedatabase.pl | 157 ++++++++++++++++++ 3 files changed, 158 insertions(+), 158 deletions(-) delete mode 100644 installer/data/mysql/atomicupdate/bug_18336_utf8mb4.perl diff --git a/Koha.pm b/Koha.pm index a8f7961afe..c618fdc236 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 = "17.12.00.015"; +$VERSION = "17.12.00.016"; sub version { return $VERSION; diff --git a/installer/data/mysql/atomicupdate/bug_18336_utf8mb4.perl b/installer/data/mysql/atomicupdate/bug_18336_utf8mb4.perl deleted file mode 100644 index 1bccc97e04..0000000000 --- a/installer/data/mysql/atomicupdate/bug_18336_utf8mb4.perl +++ /dev/null @@ -1,157 +0,0 @@ -$DBversion = 'XXX'; -if( CheckVersion( $DBversion ) ) { - - $dbh->do(q|SET foreign_key_checks = 0|); - my $sth = $dbh->table_info( '','','','TABLE' ); - - while ( my ( $cat, $schema, $name, $type, $remarks ) = $sth->fetchrow_array ) { - my $table_sth = $dbh->prepare(qq|SHOW CREATE TABLE $name|); - $table_sth->execute; - my @table = $table_sth->fetchrow_array; - unless ( $table[1] =~ /COLLATE=utf8mb4_unicode_ci/ ) { - # Some users might have done the upgrade to utf8mb4 on their own - # to support supplemental chars (japanese, chinese, etc) - if ( $name eq 'additional_fields' ) { - $dbh->do(qq| - ALTER TABLE $name - DROP KEY `fields_uniq`, - ADD UNIQUE KEY `fields_uniq` (`tablename` (191), `name` (191)) - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'authorised_values' ) { - $dbh->do(qq| - ALTER TABLE $name - DROP KEY `lib`, - ADD KEY `lib` (`lib` (191)) - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'borrower_modifications' ) { - $dbh->do(qq| - ALTER TABLE $name - DROP PRIMARY KEY, - DROP KEY `verification_token`, - ADD PRIMARY KEY (`verification_token` (191),`borrowernumber`), - ADD KEY `verification_token` (`verification_token` (191)) - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'columns_settings' ) { - $dbh->do(qq| - ALTER TABLE $name - DROP PRIMARY KEY, - ADD PRIMARY KEY (`module` (191), `page` (191), `tablename` (191), `columnname` (191)) - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'illrequestattributes' ) { - $dbh->do(qq| - ALTER TABLE $name - DROP PRIMARY KEY, - ADD PRIMARY KEY (`illrequest_id`, `type` (191)) - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'items_search_fields' ) { - $dbh->do(qq| - ALTER TABLE $name - DROP PRIMARY KEY, - ADD PRIMARY KEY (`name` (191)) - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'marc_subfield_structure' ) { - # In this case we convert each column explicitly - # to preserve 'tagsubield' collation (utf8mb4_bin) - $dbh->do(qq| - ALTER TABLE $name - MODIFY COLUMN tagfield - VARCHAR(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', - MODIFY COLUMN tagsubfield - VARCHAR(1) COLLATE utf8mb4_bin NOT NULL DEFAULT '', - MODIFY COLUMN liblibrarian - VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', - MODIFY COLUMN libopac - VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', - MODIFY COLUMN kohafield - VARCHAR(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - MODIFY COLUMN authorised_value - VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - MODIFY COLUMN authtypecode - VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - MODIFY COLUMN value_builder - VARCHAR(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - MODIFY COLUMN frameworkcode - VARCHAR(4) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', - MODIFY COLUMN seealso - VARCHAR(1100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - MODIFY COLUMN link - VARCHAR(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL - |); - $dbh->do(qq|ALTER TABLE $name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'plugin_data' ) { - $dbh->do(qq| - ALTER TABLE $name - DROP PRIMARY KEY, - ADD PRIMARY KEY (`plugin_class` (191), `plugin_key` (191)) - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'search_field' ) { - $dbh->do(qq| - ALTER TABLE $name - DROP KEY `name`, - ADD UNIQUE KEY `name` (`name` (191)) - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'search_marc_map' ) { - $dbh->do(qq| - ALTER TABLE $name - DROP KEY `index_name`, - ADD UNIQUE KEY `index_name` (`index_name`, `marc_field` (191), `marc_type`) - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'sms_providers' ) { - $dbh->do(qq| - ALTER TABLE $name - DROP KEY `name`, - ADD UNIQUE KEY `name` (`name` (191)) - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'tags' ) { - $dbh->do(qq| - ALTER TABLE $name - DROP PRIMARY KEY, - ADD PRIMARY KEY (`entry` (191)) - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'tags_approval' ) { - $dbh->do(qq| - ALTER TABLE $name - MODIFY COLUMN `term` VARCHAR(191) NOT NULL - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - elsif ( $name eq 'tags_index' ) { - $dbh->do(qq| - ALTER TABLE $name - MODIFY COLUMN `term` VARCHAR(191) NOT NULL - |); - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - else { - $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); - } - } - } - $dbh->do(q|SET foreign_key_checks = 1|);; - - print "Upgrade to $DBversion done (Bug 18336: Convert DB tables to utf8mb4)\n"; - SetVersion($DBversion); -} diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index ff79092627..f0733da8fd 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -15390,6 +15390,163 @@ if( CheckVersion( $DBversion ) ) { print "Upgrade to $DBversion done (Bug 20144 - Adapt DB structure to work with new SQL modes)\n"; } +$DBversion = '17.12.00.016'; +if( CheckVersion( $DBversion ) ) { + $dbh->do(q|SET foreign_key_checks = 0|); + my $sth = $dbh->table_info( '','','','TABLE' ); + + while ( my ( $cat, $schema, $name, $type, $remarks ) = $sth->fetchrow_array ) { + my $table_sth = $dbh->prepare(qq|SHOW CREATE TABLE $name|); + $table_sth->execute; + my @table = $table_sth->fetchrow_array; + unless ( $table[1] =~ /COLLATE=utf8mb4_unicode_ci/ ) { + # Some users might have done the upgrade to utf8mb4 on their own + # to support supplemental chars (japanese, chinese, etc) + if ( $name eq 'additional_fields' ) { + $dbh->do(qq| + ALTER TABLE $name + DROP KEY `fields_uniq`, + ADD UNIQUE KEY `fields_uniq` (`tablename` (191), `name` (191)) + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'authorised_values' ) { + $dbh->do(qq| + ALTER TABLE $name + DROP KEY `lib`, + ADD KEY `lib` (`lib` (191)) + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'borrower_modifications' ) { + $dbh->do(qq| + ALTER TABLE $name + DROP PRIMARY KEY, + DROP KEY `verification_token`, + ADD PRIMARY KEY (`verification_token` (191),`borrowernumber`), + ADD KEY `verification_token` (`verification_token` (191)) + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'columns_settings' ) { + $dbh->do(qq| + ALTER TABLE $name + DROP PRIMARY KEY, + ADD PRIMARY KEY (`module` (191), `page` (191), `tablename` (191), `columnname` (191)) + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'illrequestattributes' ) { + $dbh->do(qq| + ALTER TABLE $name + DROP PRIMARY KEY, + ADD PRIMARY KEY (`illrequest_id`, `type` (191)) + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'items_search_fields' ) { + $dbh->do(qq| + ALTER TABLE $name + DROP PRIMARY KEY, + ADD PRIMARY KEY (`name` (191)) + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'marc_subfield_structure' ) { + # In this case we convert each column explicitly + # to preserve 'tagsubield' collation (utf8mb4_bin) + $dbh->do(qq| + ALTER TABLE $name + MODIFY COLUMN tagfield + VARCHAR(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', + MODIFY COLUMN tagsubfield + VARCHAR(1) COLLATE utf8mb4_bin NOT NULL DEFAULT '', + MODIFY COLUMN liblibrarian + VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', + MODIFY COLUMN libopac + VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', + MODIFY COLUMN kohafield + VARCHAR(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + MODIFY COLUMN authorised_value + VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + MODIFY COLUMN authtypecode + VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + MODIFY COLUMN value_builder + VARCHAR(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + MODIFY COLUMN frameworkcode + VARCHAR(4) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', + MODIFY COLUMN seealso + VARCHAR(1100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + MODIFY COLUMN link + VARCHAR(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL + |); + $dbh->do(qq|ALTER TABLE $name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'plugin_data' ) { + $dbh->do(qq| + ALTER TABLE $name + DROP PRIMARY KEY, + ADD PRIMARY KEY (`plugin_class` (191), `plugin_key` (191)) + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'search_field' ) { + $dbh->do(qq| + ALTER TABLE $name + DROP KEY `name`, + ADD UNIQUE KEY `name` (`name` (191)) + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'search_marc_map' ) { + $dbh->do(qq| + ALTER TABLE $name + DROP KEY `index_name`, + ADD UNIQUE KEY `index_name` (`index_name`, `marc_field` (191), `marc_type`) + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'sms_providers' ) { + $dbh->do(qq| + ALTER TABLE $name + DROP KEY `name`, + ADD UNIQUE KEY `name` (`name` (191)) + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'tags' ) { + $dbh->do(qq| + ALTER TABLE $name + DROP PRIMARY KEY, + ADD PRIMARY KEY (`entry` (191)) + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'tags_approval' ) { + $dbh->do(qq| + ALTER TABLE $name + MODIFY COLUMN `term` VARCHAR(191) NOT NULL + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + elsif ( $name eq 'tags_index' ) { + $dbh->do(qq| + ALTER TABLE $name + MODIFY COLUMN `term` VARCHAR(191) NOT NULL + |); + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + else { + $dbh->do(qq|ALTER TABLE $name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci|); + } + } + } + $dbh->do(q|SET foreign_key_checks = 1|); + + print "Upgrade to $DBversion done (Bug 18336: Convert DB tables to utf8mb4 💩)\n"; + SetVersion($DBversion); +} + # DEVELOPER PROCESS, search for anything to execute in the db_update directory # SEE bug 13068 # if there is anything in the atomicupdate, read and execute it. -- 2.39.5