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

(-)a/installer/data/mysql/atomicupdate/bug_30899.pl (+57 lines)
Line 0 Link Here
1
use Modern::Perl;
2
3
return {
4
    bug_number => 30899,
5
    description => "Check borrower_attribute_types FK constraint (30449 follow-up)",
6
    up => sub {
7
        my ($args) = @_;
8
        my ($dbh, $out) = @$args{qw(dbh out)};
9
10
        if( foreign_key_exists('borrower_attribute_types', 'category_code_fk') ) {
11
            $dbh->do( q|ALTER TABLE borrower_attribute_types DROP FOREIGN KEY category_code_fk| );
12
            if( index_exists('borrower_attribute_types', 'category_code_fk') ) {
13
                $dbh->do( q|ALTER TABLE borrower_attribute_types DROP INDEX category_code_fk| );
14
            }
15
        }
16
17
        if (
18
            !foreign_key_exists(
19
                'borrower_attribute_types', 'borrower_attribute_types_ibfk_1'
20
            )
21
          )
22
        {
23
24
            my $sth = $dbh->prepare(
25
                q{
26
                SELECT category_code
27
                FROM borrower_attribute_types
28
                WHERE category_code NOT IN (SELECT categorycode FROM categories);
29
            }
30
            );
31
32
            $sth->execute;
33
34
            my @invalid_categories;
35
            while ( my $row = $sth->fetchrow_arrayref() ) {
36
                push( @invalid_categories, $row->[0] );
37
            }
38
39
            if (@invalid_categories) {
40
                die "The 'borrower_attribute_types' table contains "
41
                  . "references to invalid category codes: "
42
                  . join( ', ', @invalid_categories );
43
            }
44
45
            if ( !index_exists( 'borrower_attribute_types', 'category_code' ) )
46
            {
47
                $dbh->do(q|
48
                    ALTER TABLE borrower_attribute_types ADD INDEX category_code (category_code)
49
                |);
50
            }
51
            $dbh->do(q|
52
                ALTER TABLE borrower_attribute_types
53
                    ADD CONSTRAINT borrower_attribute_types_ibfk_1 FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`)
54
            |);
55
        }
56
    },
57
};
(-)a/installer/data/mysql/db_revs/211105004.pl (-2 / +1 lines)
Lines 8-14 return { Link Here
8
        my ($dbh, $out) = @$args{qw(dbh out)};
8
        my ($dbh, $out) = @$args{qw(dbh out)};
9
9
10
        if( foreign_key_exists('borrower_attribute_types', 'category_code_fk') ) {
10
        if( foreign_key_exists('borrower_attribute_types', 'category_code_fk') ) {
11
            $dbh->do( q|ALTER TABLE borrower_attribute_types DROP CONSTRAINT category_code_fk| );
11
            $dbh->do( q|ALTER TABLE borrower_attribute_types DROP FOREIGN KEY category_code_fk| );
12
            if( index_exists('borrower_attribute_types', 'category_code_fk') ) {
12
            if( index_exists('borrower_attribute_types', 'category_code_fk') ) {
13
                $dbh->do( q|ALTER TABLE borrower_attribute_types DROP INDEX category_code_fk| );
13
                $dbh->do( q|ALTER TABLE borrower_attribute_types DROP INDEX category_code_fk| );
14
            }
14
            }
15
- 

Return to bug 30899