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

(-)a/installer/data/mysql/atomicupdate/bug_29336_2.pl (+58 lines)
Line 0 Link Here
1
use Modern::Perl;
2
use Data::Dumper;
3
4
return {
5
    bug_number => 29336,
6
    description => "Add missing FK constraints to authorised value categories",
7
    up => sub {
8
        my ($args) = @_;
9
        my ($dbh, $out) = @$args{qw(dbh out)};
10
        my %fix_tables = (
11
            'additional_fields' => 'authorised_value_category',
12
            'auth_subfield_structure' => 'authorised_value'
13
            'auth_tag_structure' => 'authorised_value'
14
            'borrower_attribute_types' => 'authorised_value_category',
15
            'club_template_enrollment_fields' => 'authorised_value_category'
16
            'club_template_fields' => 'authorised_value_category',
17
            'marc_tag_structure' => 'authorised_value',
18
        );
19
20
        my $sql;
21
        foreach my $table ( sort keys %fix_tables ) {
22
            my $field = $fix_tables{$table};
23
            next if foreign_key_exists( $table, undef, $field );
24
25
            # Before setting the FK constraint we need to correct wrong links
26
            $sql = qq|
27
                SELECT DISTINCT $field FROM $table t1
28
                LEFT JOIN authorised_value_categories avc ON t1.$field=avc.category_name
29
                WHERE t1.$field IS NOT NULL AND  avc.category_name IS NULL
30
            |;
31
            my @bad_values = map { $_->[0] } @{$dbh->selectall_arrayref($sql)};
32
            if( @bad_values ) {
33
                say $out "Found the following bad authorised values categories in $table: ". join(',', @bad_values);
34
                if( $table eq 'additional_fields' ) {
35
                    say $out "For this reason we will not set the foreign key constraint on additional_fields. Please fix manually.";
36
                    next;
37
                }
38
                $sql = qq|
39
                    UPDATE $table t1
40
                    LEFT JOIN authorised_value_categories avc ON t1.$field=avc.category_name
41
                    SET t1.$field=NULL
42
                    WHERE avc.category_name IS NULL
43
                |;
44
                $dbh->do($sql);
45
                say $out "Have set these values in $table to NULL\n";
46
            }
47
48
            # Ready to set constraint
49
            $sql = qq|
50
                ALTER TABLE $table
51
                ADD CONSTRAINT ${table}_authorised_value_categories_fk FOREIGN KEY ($field)
52
                REFERENCES `authorised_value_categories` (`category_name`)
53
                ON DELETE CASCADE ON UPDATE CASCADE
54
            |;
55
            $dbh->do($sql);
56
        }
57
    },
58
}
(-)a/installer/data/mysql/kohastructure.sql (-8 / +14 lines)
Lines 248-254 CREATE TABLE `additional_fields` ( Link Here
248
  `marcfield_mode` enum('get','set') NOT NULL DEFAULT 'get' COMMENT 'mode of operation (get or set) for marcfield',
248
  `marcfield_mode` enum('get','set') NOT NULL DEFAULT 'get' COMMENT 'mode of operation (get or set) for marcfield',
249
  `searchable` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'is the field searchable?',
249
  `searchable` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'is the field searchable?',
250
  PRIMARY KEY (`id`),
250
  PRIMARY KEY (`id`),
251
  UNIQUE KEY `fields_uniq` (`tablename`(191),`name`(191))
251
  UNIQUE KEY `fields_uniq` (`tablename`(191),`name`(191)),
252
  CONSTRAINT `additional_fields_authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
252
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
253
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
253
/*!40101 SET character_set_client = @saved_cs_client */;
254
/*!40101 SET character_set_client = @saved_cs_client */;
254
255
Lines 921-927 CREATE TABLE `auth_subfield_structure` ( Link Here
921
  `display_order` int(2) NOT NULL DEFAULT 0,
922
  `display_order` int(2) NOT NULL DEFAULT 0,
922
  PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
923
  PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
923
  KEY `tab` (`authtypecode`,`tab`),
924
  KEY `tab` (`authtypecode`,`tab`),
924
  CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
925
  CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE,
926
  CONSTRAINT `auth_subfield_structure_authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
925
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
927
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
926
/*!40101 SET character_set_client = @saved_cs_client */;
928
/*!40101 SET character_set_client = @saved_cs_client */;
927
929
Lines 941-947 CREATE TABLE `auth_tag_structure` ( Link Here
941
  `mandatory` tinyint(4) NOT NULL DEFAULT 0,
943
  `mandatory` tinyint(4) NOT NULL DEFAULT 0,
942
  `authorised_value` varchar(32) DEFAULT NULL,
944
  `authorised_value` varchar(32) DEFAULT NULL,
943
  PRIMARY KEY (`authtypecode`,`tagfield`),
945
  PRIMARY KEY (`authtypecode`,`tagfield`),
944
  CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
946
  CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE,
947
  CONSTRAINT `auth_tag_structure_authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
945
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
948
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
946
/*!40101 SET character_set_client = @saved_cs_client */;
949
/*!40101 SET character_set_client = @saved_cs_client */;
947
950
Lines 1183-1189 CREATE TABLE `borrower_attribute_types` ( Link Here
1183
  PRIMARY KEY (`code`),
1186
  PRIMARY KEY (`code`),
1184
  KEY `auth_val_cat_idx` (`authorised_value_category`),
1187
  KEY `auth_val_cat_idx` (`authorised_value_category`),
1185
  KEY `category_code` (`category_code`),
1188
  KEY `category_code` (`category_code`),
1186
  CONSTRAINT `borrower_attribute_types_ibfk_1` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`)
1189
  CONSTRAINT `borrower_attribute_types_ibfk_1` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`),
1190
  CONSTRAINT `borrower_attribute_types_authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
1187
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1191
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1188
/*!40101 SET character_set_client = @saved_cs_client */;
1192
/*!40101 SET character_set_client = @saved_cs_client */;
1189
1193
Lines 1999-2005 CREATE TABLE `club_template_enrollment_fields` ( Link Here
1999
  `authorised_value_category` varchar(32) DEFAULT NULL,
2003
  `authorised_value_category` varchar(32) DEFAULT NULL,
2000
  PRIMARY KEY (`id`),
2004
  PRIMARY KEY (`id`),
2001
  KEY `club_template_id` (`club_template_id`),
2005
  KEY `club_template_id` (`club_template_id`),
2002
  CONSTRAINT `club_template_enrollment_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2006
  CONSTRAINT `club_template_enrollment_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
2007
  CONSTRAINT `club_template_enrollment_fields_authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
2003
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2008
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2004
/*!40101 SET character_set_client = @saved_cs_client */;
2009
/*!40101 SET character_set_client = @saved_cs_client */;
2005
2010
Lines 2018-2024 CREATE TABLE `club_template_fields` ( Link Here
2018
  `authorised_value_category` varchar(32) DEFAULT NULL,
2023
  `authorised_value_category` varchar(32) DEFAULT NULL,
2019
  PRIMARY KEY (`id`),
2024
  PRIMARY KEY (`id`),
2020
  KEY `club_template_id` (`club_template_id`),
2025
  KEY `club_template_id` (`club_template_id`),
2021
  CONSTRAINT `club_template_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2026
  CONSTRAINT `club_template_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
2027
  CONSTRAINT `club_template_fields_authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
2022
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2028
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2023
/*!40101 SET character_set_client = @saved_cs_client */;
2029
/*!40101 SET character_set_client = @saved_cs_client */;
2024
2030
Lines 4147-4153 CREATE TABLE `marc_tag_structure` ( Link Here
4147
  `ind1_defaultvalue` varchar(1) NOT NULL DEFAULT '',
4153
  `ind1_defaultvalue` varchar(1) NOT NULL DEFAULT '',
4148
  `ind2_defaultvalue` varchar(1) NOT NULL DEFAULT '',
4154
  `ind2_defaultvalue` varchar(1) NOT NULL DEFAULT '',
4149
  `frameworkcode` varchar(4) NOT NULL DEFAULT '',
4155
  `frameworkcode` varchar(4) NOT NULL DEFAULT '',
4150
  PRIMARY KEY (`frameworkcode`,`tagfield`)
4156
  PRIMARY KEY (`frameworkcode`,`tagfield`),
4157
  CONSTRAINT `marc_tag_structure_authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
4151
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4158
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4152
/*!40101 SET character_set_client = @saved_cs_client */;
4159
/*!40101 SET character_set_client = @saved_cs_client */;
4153
4160
4154
- 

Return to bug 29390