View | Details | Raw Unified | Return to bug 22887
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_22887.perl (-2 / +14 lines)
Lines 1-8 Link Here
1
$DBversion = 'XXX'; # will be replaced by the RM
1
$DBversion = 'XXX'; # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
2
if( CheckVersion( $DBversion ) ) {
3
    $dbh->do( q{ALTER TABLE `authorised_values` ADD CONSTRAINT `category_code` UNIQUE (category,authorised_value)} );
3
    unless ( index_exists('authorised_values', 'av_uniq') ) {
4
        my $duplicates = $dbh->selectall_arrayref(q|
5
            SELECT category, authorised_value, COUNT(concat(category, ':', authorised_value)) AS c
6
            FROM authorised_values
7
            GROUP BY category, authorised_value
8
            HAVING c > 1
9
        |, { Slice => {} });
10
        if ( @$duplicates ) {
11
            warn "WARNING - Cannot create unique constraint on authorised_value(category, authorised_value)\n";
12
            warn "The following entries are duplicated: " . join (', ', map { sprintf "%s:%s (%s)", $_->{category}, $_->{authorised_value}, $_->{c} } @$duplicates);
13
        } else {
14
            $dbh->do( q{ALTER TABLE `authorised_values` ADD CONSTRAINT `av_uniq` UNIQUE (category, authorised_value)} );
15
        }
16
    }
4
17
5
    # Always end with this (adjust the bug info)
6
    SetVersion( $DBversion );
18
    SetVersion( $DBversion );
7
    print "Upgrade to $DBversion done (Bug 22887 - Add unique constraint to authorised_values)\n";
19
    print "Upgrade to $DBversion done (Bug 22887 - Add unique constraint to authorised_values)\n";
8
}
20
}
(-)a/installer/data/mysql/kohastructure.sql (-2 / +1 lines)
Lines 119-125 CREATE TABLE `authorised_values` ( -- stores values for authorized values catego Link Here
119
  KEY `name` (`category`),
119
  KEY `name` (`category`),
120
  KEY `lib` (`lib` (191)),
120
  KEY `lib` (`lib` (191)),
121
  KEY `auth_value_idx` (`authorised_value`),
121
  KEY `auth_value_idx` (`authorised_value`),
122
  CONSTRAINT `category_code` UNIQUE (`category`,`authorised_value`),
122
  CONSTRAINT `av_uniq` UNIQUE (`category`,`authorised_value`),
123
  CONSTRAINT `authorised_values_authorised_values_category` FOREIGN KEY (`category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
123
  CONSTRAINT `authorised_values_authorised_values_category` FOREIGN KEY (`category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
124
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
124
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
125
125
126
- 

Return to bug 22887