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