Lines 6-15
return {
Link Here
|
6 |
up => sub { |
6 |
up => sub { |
7 |
my ($args) = @_; |
7 |
my ($args) = @_; |
8 |
my ($dbh, $out) = @$args{qw(dbh out)}; |
8 |
my ($dbh, $out) = @$args{qw(dbh out)}; |
|
|
9 |
|
10 |
# Count bad records (expecting zero..) |
11 |
my ( $cnt ) = $dbh->selectrow_array(q{ |
12 |
SELECT COUNT(issue_id) FROM issues |
13 |
LEFT JOIN borrowers USING (borrowernumber) |
14 |
LEFT JOIN items USING (itemnumber) |
15 |
WHERE items.itemnumber IS NULL OR borrowers.borrowernumber IS NULL }); |
16 |
|
17 |
# If we found bad records, we will not continue |
18 |
if( $cnt ) { |
19 |
say $out "ERROR: Your issues table contains $cnt records violating foreign key constraints to the borrowers or items table."; |
20 |
say $out "We recommend to remove them with a statement like:"; |
21 |
say $out " DELETE issues FROM issues LEFT JOIN borrowers bo USING (borrowernumber) WHERE bo.borrowernumber IS NULL;"; |
22 |
say $out " DELETE issues FROM issues LEFT JOIN items it USING (itemnumber) WHERE it.itemnumber IS NULL;"; |
23 |
die "Interrupting installer process: database revision for bug 30483 fails!"; |
24 |
} |
25 |
|
26 |
# Green zone: remove FK constraints while changing columns (needed for some SQL server versions) |
27 |
if( foreign_key_exists('issues', 'issues_ibfk_1') ) { |
28 |
$dbh->do( q|ALTER TABLE issues DROP FOREIGN KEY issues_ibfk_1| ); |
29 |
} |
30 |
if( foreign_key_exists('issues', 'issues_ibfk_2') ) { |
31 |
$dbh->do( q|ALTER TABLE issues DROP FOREIGN KEY issues_ibfk_2| ); |
32 |
} |
9 |
$dbh->do(q{ |
33 |
$dbh->do(q{ |
10 |
ALTER TABLE issues |
34 |
ALTER TABLE issues |
11 |
MODIFY COLUMN borrowernumber int(11) NOT NULL COMMENT 'foreign key, linking this to the borrowers table for the patron this item was checked out to', |
35 |
MODIFY COLUMN borrowernumber int(11) NOT NULL COMMENT 'foreign key, linking this to the borrowers table for the patron this item was checked out to', |
12 |
MODIFY COLUMN itemnumber int(11) NOT NULL COMMENT 'foreign key, linking this to the items table for the item that was checked out' |
36 |
MODIFY COLUMN itemnumber int(11) NOT NULL COMMENT 'foreign key, linking this to the items table for the item that was checked out' |
13 |
}); |
37 |
}); |
|
|
38 |
$dbh->do( q|ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE| ); |
39 |
$dbh->do( q|ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE| ); |
14 |
}, |
40 |
}, |
15 |
}; |
41 |
}; |
16 |
- |
|
|