From b7f0f9d5dd6abc2db4645ceb485446e145697306 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 26 Mar 2020 10:28:50 +0100 Subject: [PATCH] Bug 22887: Warn if duplicates exist --- installer/data/mysql/atomicupdate/bug_22887.perl | 16 ++++++++++++++-- installer/data/mysql/kohastructure.sql | 2 +- 2 files changed, 15 insertions(+), 3 deletions(-) diff --git a/installer/data/mysql/atomicupdate/bug_22887.perl b/installer/data/mysql/atomicupdate/bug_22887.perl index 5744d84756..e4bc712eb2 100644 --- a/installer/data/mysql/atomicupdate/bug_22887.perl +++ b/installer/data/mysql/atomicupdate/bug_22887.perl @@ -1,8 +1,20 @@ $DBversion = 'XXX'; # will be replaced by the RM if( CheckVersion( $DBversion ) ) { - $dbh->do( q{ALTER TABLE `authorised_values` ADD CONSTRAINT `category_code` UNIQUE (category,authorised_value)} ); + unless ( index_exists('authorised_values', 'av_uniq') ) { + my $duplicates = $dbh->selectall_arrayref(q| + SELECT category, authorised_value, COUNT(concat(category, ':', authorised_value)) AS c + FROM authorised_values + GROUP BY category, authorised_value + HAVING c > 1 + |, { Slice => {} }); + if ( @$duplicates ) { + warn "WARNING - Cannot create unique constraint on authorised_value(category, authorised_value)\n"; + warn "The following entries are duplicated: " . join (', ', map { sprintf "%s:%s (%s)", $_->{category}, $_->{authorised_value}, $_->{c} } @$duplicates); + } else { + $dbh->do( q{ALTER TABLE `authorised_values` ADD CONSTRAINT `av_uniq` UNIQUE (category, authorised_value)} ); + } + } - # Always end with this (adjust the bug info) SetVersion( $DBversion ); print "Upgrade to $DBversion done (Bug 22887 - Add unique constraint to authorised_values)\n"; } diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 32e81a43fd..a9ac8fb20e 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -119,7 +119,7 @@ CREATE TABLE `authorised_values` ( -- stores values for authorized values catego KEY `name` (`category`), KEY `lib` (`lib` (191)), KEY `auth_value_idx` (`authorised_value`), - CONSTRAINT `category_code` UNIQUE (`category`,`authorised_value`), + CONSTRAINT `av_uniq` UNIQUE (`category`,`authorised_value`), CONSTRAINT `authorised_values_authorised_values_category` FOREIGN KEY (`category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2.20.1