View | Details | Raw Unified | Return to bug 29336
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 ( 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 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 214-220 CREATE TABLE `additional_fields` ( Link Here
214
  `marcfield` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'contains the marc field to copied into the record',
214
  `marcfield` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'contains the marc field to copied into the record',
215
  `searchable` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'is the field searchable?',
215
  `searchable` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'is the field searchable?',
216
  PRIMARY KEY (`id`),
216
  PRIMARY KEY (`id`),
217
  UNIQUE KEY `fields_uniq` (`tablename`(191),`name`(191))
217
  UNIQUE KEY `fields_uniq` (`tablename`(191),`name`(191)),
218
  CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
218
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
219
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
219
/*!40101 SET character_set_client = @saved_cs_client */;
220
/*!40101 SET character_set_client = @saved_cs_client */;
220
221
Lines 836-842 CREATE TABLE `auth_subfield_structure` ( Link Here
836
  `display_order` int(2) NOT NULL DEFAULT 0,
837
  `display_order` int(2) NOT NULL DEFAULT 0,
837
  PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
838
  PRIMARY KEY (`authtypecode`,`tagfield`,`tagsubfield`),
838
  KEY `tab` (`authtypecode`,`tab`),
839
  KEY `tab` (`authtypecode`,`tab`),
839
  CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
840
  CONSTRAINT `auth_subfield_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE,
841
  CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
840
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
842
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
841
/*!40101 SET character_set_client = @saved_cs_client */;
843
/*!40101 SET character_set_client = @saved_cs_client */;
842
844
Lines 856-862 CREATE TABLE `auth_tag_structure` ( Link Here
856
  `mandatory` tinyint(4) NOT NULL DEFAULT 0,
858
  `mandatory` tinyint(4) NOT NULL DEFAULT 0,
857
  `authorised_value` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '#TODO This is a AV category!',
859
  `authorised_value` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '#TODO This is a AV category!',
858
  PRIMARY KEY (`authtypecode`,`tagfield`),
860
  PRIMARY KEY (`authtypecode`,`tagfield`),
859
  CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE
861
  CONSTRAINT `auth_tag_structure_ibfk_1` FOREIGN KEY (`authtypecode`) REFERENCES `auth_types` (`authtypecode`) ON DELETE CASCADE ON UPDATE CASCADE,
862
  CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
860
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
863
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
861
/*!40101 SET character_set_client = @saved_cs_client */;
864
/*!40101 SET character_set_client = @saved_cs_client */;
862
865
Lines 1091-1097 CREATE TABLE `borrower_attribute_types` ( Link Here
1091
  `keep_for_pseudonymization` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'defines if this field is copied to anonymized_borrower_attributes (1 for yes, 0 for no)',
1094
  `keep_for_pseudonymization` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'defines if this field is copied to anonymized_borrower_attributes (1 for yes, 0 for no)',
1092
  `mandatory` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'defines if the attribute is mandatory or not',
1095
  `mandatory` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'defines if the attribute is mandatory or not',
1093
  PRIMARY KEY (`code`),
1096
  PRIMARY KEY (`code`),
1094
  KEY `auth_val_cat_idx` (`authorised_value_category`)
1097
  KEY `auth_val_cat_idx` (`authorised_value_category`),
1098
  CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
1095
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1099
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1096
/*!40101 SET character_set_client = @saved_cs_client */;
1100
/*!40101 SET character_set_client = @saved_cs_client */;
1097
1101
Lines 1871-1877 CREATE TABLE `club_template_enrollment_fields` ( Link Here
1871
  `authorised_value_category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1875
  `authorised_value_category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1872
  PRIMARY KEY (`id`),
1876
  PRIMARY KEY (`id`),
1873
  KEY `club_template_id` (`club_template_id`),
1877
  KEY `club_template_id` (`club_template_id`),
1874
  CONSTRAINT `club_template_enrollment_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
1878
  CONSTRAINT `club_template_enrollment_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
1879
  CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
1875
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1880
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1876
/*!40101 SET character_set_client = @saved_cs_client */;
1881
/*!40101 SET character_set_client = @saved_cs_client */;
1877
1882
Lines 1890-1896 CREATE TABLE `club_template_fields` ( Link Here
1890
  `authorised_value_category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1895
  `authorised_value_category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
1891
  PRIMARY KEY (`id`),
1896
  PRIMARY KEY (`id`),
1892
  KEY `club_template_id` (`club_template_id`),
1897
  KEY `club_template_id` (`club_template_id`),
1893
  CONSTRAINT `club_template_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
1898
  CONSTRAINT `club_template_fields_ibfk_1` FOREIGN KEY (`club_template_id`) REFERENCES `club_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
1899
  CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value_category`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
1894
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1900
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1895
/*!40101 SET character_set_client = @saved_cs_client */;
1901
/*!40101 SET character_set_client = @saved_cs_client */;
1896
1902
Lines 3505-3511 CREATE TABLE `marc_tag_structure` ( Link Here
3505
  `ind1_defaultvalue` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
3511
  `ind1_defaultvalue` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
3506
  `ind2_defaultvalue` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
3512
  `ind2_defaultvalue` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
3507
  `frameworkcode` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
3513
  `frameworkcode` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
3508
  PRIMARY KEY (`frameworkcode`,`tagfield`)
3514
  PRIMARY KEY (`frameworkcode`,`tagfield`),
3515
  CONSTRAINT `authorised_value_categories_fk` FOREIGN KEY (`authorised_value`) REFERENCE `authorised_value_categories` (`category_name`) ON DELETE CASCADE ON UPDATE CASCADE
3509
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3516
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3510
/*!40101 SET character_set_client = @saved_cs_client */;
3517
/*!40101 SET character_set_client = @saved_cs_client */;
3511
3518
3512
- 

Return to bug 29336