|
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 |
- |
|
|