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