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 940-946 CREATE TABLE `auth_subfield_structure` ( Link Here
940
  `display_order` int(2) NOT NULL DEFAULT 0,
941
  `display_order` int(2) NOT NULL DEFAULT 0,
941
  PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
942
  PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
942
  KEY `tab` (`authtypecode`,`tab`),
943
  KEY `tab` (`authtypecode`,`tab`),
943
  CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
944
  CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE,
945
  CONSTRAINT `auth_subfield_structure_authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
944
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
946
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
945
/*!40101 SET character_set_client = @saved_cs_client */;
947
/*!40101 SET character_set_client = @saved_cs_client */;
946
948
Lines 960-966 CREATE TABLE `auth_tag_structure` ( Link Here
960
  `mandatory` tinyint(4) NOT NULL DEFAULT 0,
962
  `mandatory` tinyint(4) NOT NULL DEFAULT 0,
961
  `authorised_value` varchar(32) DEFAULT NULL,
963
  `authorised_value` varchar(32) DEFAULT NULL,
962
  PRIMARY KEY (`authtypecode`,`tagfield`),
964
  PRIMARY KEY (`authtypecode`,`tagfield`),
963
  CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
965
  CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE,
966
  CONSTRAINT `auth_tag_structure_authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
964
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
967
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
965
/*!40101 SET character_set_client = @saved_cs_client */;
968
/*!40101 SET character_set_client = @saved_cs_client */;
966
969
Lines 1202-1208 CREATE TABLE `borrower_attribute_types` ( Link Here
1202
  PRIMARY KEY (`code`),
1205
  PRIMARY KEY (`code`),
1203
  KEY `auth_val_cat_idx` (`authorised_value_category`),
1206
  KEY `auth_val_cat_idx` (`authorised_value_category`),
1204
  KEY `category_code` (`category_code`),
1207
  KEY `category_code` (`category_code`),
1205
  CONSTRAINT `borrower_attribute_types_ibfk_1` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`)
1208
  CONSTRAINT `borrower_attribute_types_ibfk_1` FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`),
1209
  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
1206
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1210
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1207
/*!40101 SET character_set_client = @saved_cs_client */;
1211
/*!40101 SET character_set_client = @saved_cs_client */;
1208
1212
Lines 2018-2024 CREATE TABLE `club_template_enrollment_fields` ( Link Here
2018
  `authorised_value_category` varchar(32) DEFAULT NULL,
2022
  `authorised_value_category` varchar(32) DEFAULT NULL,
2019
  PRIMARY KEY (`id`),
2023
  PRIMARY KEY (`id`),
2020
  KEY `club_template_id` (`club_template_id`),
2024
  KEY `club_template_id` (`club_template_id`),
2021
  CONSTRAINT `club_template_enrollment_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2025
  CONSTRAINT `club_template_enrollment_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
2026
  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
2022
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2027
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2023
/*!40101 SET character_set_client = @saved_cs_client */;
2028
/*!40101 SET character_set_client = @saved_cs_client */;
2024
2029
Lines 2037-2043 CREATE TABLE `club_template_fields` ( Link Here
2037
  `authorised_value_category` varchar(32) DEFAULT NULL,
2042
  `authorised_value_category` varchar(32) DEFAULT NULL,
2038
  PRIMARY KEY (`id`),
2043
  PRIMARY KEY (`id`),
2039
  KEY `club_template_id` (`club_template_id`),
2044
  KEY `club_template_id` (`club_template_id`),
2040
  CONSTRAINT `club_template_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
2045
  CONSTRAINT `club_template_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
2046
  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
2041
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2047
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2042
/*!40101 SET character_set_client = @saved_cs_client */;
2048
/*!40101 SET character_set_client = @saved_cs_client */;
2043
2049
Lines 4166-4172 CREATE TABLE `marc_tag_structure` ( Link Here
4166
  `ind1_defaultvalue` varchar(1) NOT NULL DEFAULT '',
4172
  `ind1_defaultvalue` varchar(1) NOT NULL DEFAULT '',
4167
  `ind2_defaultvalue` varchar(1) NOT NULL DEFAULT '',
4173
  `ind2_defaultvalue` varchar(1) NOT NULL DEFAULT '',
4168
  `frameworkcode` varchar(4) NOT NULL DEFAULT '',
4174
  `frameworkcode` varchar(4) NOT NULL DEFAULT '',
4169
  PRIMARY KEY (`frameworkcode`,`tagfield`)
4175
  PRIMARY KEY (`frameworkcode`,`tagfield`),
4176
  CONSTRAINT `marc_tag_structure_authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCES `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
4170
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4177
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4171
/*!40101 SET character_set_client = @saved_cs_client */;
4178
/*!40101 SET character_set_client = @saved_cs_client */;
4172
4179
4173
- 

Return to bug 29390