Bugzilla – Attachment 102877 Details for
Bug 22887
authorised_values is missing a unique constraint on category + authorised_value
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 22887: Warn if duplicates exist
Bug-22887-Warn-if-duplicates-exist.patch (text/plain), 2.78 KB, created by
Katrin Fischer
on 2020-04-13 17:32:07 UTC
(
hide
)
Description:
Bug 22887: Warn if duplicates exist
Filename:
MIME Type:
Creator:
Katrin Fischer
Created:
2020-04-13 17:32:07 UTC
Size:
2.78 KB
patch
obsolete
>From 4982f34b7601654e2c8210f2b991c54213bc273e Mon Sep 17 00:00:00 2001 >From: Jonathan Druart <jonathan.druart@bugs.koha-community.org> >Date: Thu, 26 Mar 2020 10:28:50 +0100 >Subject: [PATCH] Bug 22887: Warn if duplicates exist > >Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> > >Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de> >--- > 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 1fafa6a903..033ec7e865 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.11.0
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 22887
:
89550
|
101790
|
101791
|
101792
|
101793
|
101889
|
101890
|
101891
|
101892
|
102409
|
102410
|
102411
|
102412
|
102511
|
102876
| 102877 |
102878
|
102879
|
102880
|
102898
|
102939
|
102999
|
103000