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