|
Line 0
Link Here
|
|
|
1 |
$DBversion = 'XXX'; # will be replaced by the RM |
| 2 |
if ( CheckVersion($DBversion) ) { |
| 3 |
|
| 4 |
# Add constraint for suggestedby |
| 5 |
my $sth = $dbh->prepare( |
| 6 |
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_suggestedby'| |
| 7 |
); |
| 8 |
$sth->execute; |
| 9 |
unless ( $sth->fetchrow_hashref ) { |
| 10 |
$dbh->do( |
| 11 |
"UPDATE suggestions SET suggestedby = 0 where suggestedby NOT IN (SELECT borrowernumber FROM borrowers)" |
| 12 |
); |
| 13 |
$dbh->do( |
| 14 |
"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_suggestedby` FOREIGN KEY (`suggestedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET 0 ON UPDATE CASCADE" |
| 15 |
); |
| 16 |
} |
| 17 |
|
| 18 |
# Add constraint for managedby |
| 19 |
my $sth = $dbh->prepare( |
| 20 |
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_managedby'| |
| 21 |
); |
| 22 |
$sth->execute; |
| 23 |
unless ( $sth->fetchrow_hashref ) { |
| 24 |
$dbh->do( |
| 25 |
"UPDATE suggestions SET managedby = NULL where managedby NOT IN (SELECT borrowernumber FROM borrowers)" |
| 26 |
); |
| 27 |
$dbh->do( |
| 28 |
"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_managedby` FOREIGN KEY (`managedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE" |
| 29 |
); |
| 30 |
} |
| 31 |
|
| 32 |
# Add constraint for acceptedby |
| 33 |
my $sth = $dbh->prepare( |
| 34 |
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_acceptedby'| |
| 35 |
); |
| 36 |
$sth->execute; |
| 37 |
unless ( $sth->fetchrow_hashref ) { |
| 38 |
$dbh->do( |
| 39 |
"UPDATE suggestions SET acceptedby = NULL where acceptedby NOT IN (SELECT borrowernumber FROM borrowers)" |
| 40 |
); |
| 41 |
$dbh->do( |
| 42 |
"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_acceptedby` FOREIGN KEY (`acceptedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE" |
| 43 |
); |
| 44 |
} |
| 45 |
|
| 46 |
# Add constraint for rejectedby |
| 47 |
my $sth = $dbh->prepare( |
| 48 |
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_rejectedby'| |
| 49 |
); |
| 50 |
$sth->execute; |
| 51 |
unless ( $sth->fetchrow_hashref ) { |
| 52 |
$dbh->do( |
| 53 |
"UPDATE suggestions SET rejectedby = NULL where rejectedby NOT IN (SELECT borrowernumber FROM borrowers)" |
| 54 |
); |
| 55 |
$dbh->do( |
| 56 |
"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_rejectedby` FOREIGN KEY (`rejectedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE" |
| 57 |
); |
| 58 |
} |
| 59 |
|
| 60 |
# Add constraint for biblionumber |
| 61 |
$sth = $dbh->prepare( |
| 62 |
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_biblionumber'| |
| 63 |
); |
| 64 |
$sth->execute; |
| 65 |
unless ( $sth->fetchrow_hashref ) { |
| 66 |
$dbh->do( |
| 67 |
"UPDATE suggestions SET biblionumber = NULL where biblionumber NOT IN (SELECT biblionumber FROM biblio)" |
| 68 |
); |
| 69 |
$dbh->do( |
| 70 |
"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE" |
| 71 |
); |
| 72 |
} |
| 73 |
|
| 74 |
# Add constraint for branchcode |
| 75 |
$sth = $dbh->prepare( |
| 76 |
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_branchcode'| |
| 77 |
); |
| 78 |
$sth->execute; |
| 79 |
unless ( $sth->fetchrow_hashref ) { |
| 80 |
$dbh->do( |
| 81 |
"UPDATE suggestions SET branchcode = NULL where branchcode NOT IN (SELECT branchcode FROM branches)" |
| 82 |
); |
| 83 |
$dbh->do( |
| 84 |
"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `biblio` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE" |
| 85 |
); |
| 86 |
} |
| 87 |
|
| 88 |
SetVersion($DBversion); |
| 89 |
print |
| 90 |
"Upgrade to $DBversion done (Bug 22368 - Add missing constraints to suggestions)\n"; |
| 91 |
} |