Lines 2-12
$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 |
my $sth = $dbh->prepare( |
5 |
unless( foreign_key_exists( 'suggestions', 'suggestions_ibfk_suggestedby' ) ) { |
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("ALTER TABLE suggestions CHANGE COLUMN suggestedby suggestedby INT(11) NULL DEFAULT NULL;"); |
6 |
$dbh->do("ALTER TABLE suggestions CHANGE COLUMN suggestedby suggestedby INT(11) NULL DEFAULT NULL;"); |
11 |
$dbh->do( |
7 |
$dbh->do( |
12 |
"UPDATE suggestions SET suggestedby = NULL where suggestedby NOT IN (SELECT borrowernumber FROM borrowers)" |
8 |
"UPDATE suggestions SET suggestedby = NULL where suggestedby NOT IN (SELECT borrowernumber FROM borrowers)" |
Lines 17-27
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='sugg
Link Here
|
17 |
} |
13 |
} |
18 |
|
14 |
|
19 |
# Add constraint for managedby |
15 |
# Add constraint for managedby |
20 |
$sth = $dbh->prepare( |
16 |
unless( foreign_key_exists( 'suggestions', 'suggestions_ibfk_managedby' ) ) { |
21 |
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_managedby'| |
|
|
22 |
); |
23 |
$sth->execute; |
24 |
unless ( $sth->fetchrow_hashref ) { |
25 |
$dbh->do( |
17 |
$dbh->do( |
26 |
"UPDATE suggestions SET managedby = NULL where managedby NOT IN (SELECT borrowernumber FROM borrowers)" |
18 |
"UPDATE suggestions SET managedby = NULL where managedby NOT IN (SELECT borrowernumber FROM borrowers)" |
27 |
); |
19 |
); |
Lines 31-41
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='sugg
Link Here
|
31 |
} |
23 |
} |
32 |
|
24 |
|
33 |
# Add constraint for acceptedby |
25 |
# Add constraint for acceptedby |
34 |
$sth = $dbh->prepare( |
26 |
unless( foreign_key_exists( 'suggestions', 'suggestions_ibfk_acceptedby' ) ) { |
35 |
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_acceptedby'| |
|
|
36 |
); |
37 |
$sth->execute; |
38 |
unless ( $sth->fetchrow_hashref ) { |
39 |
$dbh->do( |
27 |
$dbh->do( |
40 |
"UPDATE suggestions SET acceptedby = NULL where acceptedby NOT IN (SELECT borrowernumber FROM borrowers)" |
28 |
"UPDATE suggestions SET acceptedby = NULL where acceptedby NOT IN (SELECT borrowernumber FROM borrowers)" |
41 |
); |
29 |
); |
Lines 45-55
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='sugg
Link Here
|
45 |
} |
33 |
} |
46 |
|
34 |
|
47 |
# Add constraint for rejectedby |
35 |
# Add constraint for rejectedby |
48 |
$sth = $dbh->prepare( |
36 |
unless( foreign_key_exists( 'suggestions', 'suggestions_ibfk_rejectedby' ) ) { |
49 |
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_rejectedby'| |
|
|
50 |
); |
51 |
$sth->execute; |
52 |
unless ( $sth->fetchrow_hashref ) { |
53 |
$dbh->do( |
37 |
$dbh->do( |
54 |
"UPDATE suggestions SET rejectedby = NULL where rejectedby NOT IN (SELECT borrowernumber FROM borrowers)" |
38 |
"UPDATE suggestions SET rejectedby = NULL where rejectedby NOT IN (SELECT borrowernumber FROM borrowers)" |
55 |
); |
39 |
); |
Lines 59-69
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='sugg
Link Here
|
59 |
} |
43 |
} |
60 |
|
44 |
|
61 |
# Add constraint for biblionumber |
45 |
# Add constraint for biblionumber |
62 |
$sth = $dbh->prepare( |
46 |
unless( foreign_key_exists( 'suggestions', 'suggestions_ibfk_biblionumber' ) ) { |
63 |
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_biblionumber'| |
|
|
64 |
); |
65 |
$sth->execute; |
66 |
unless ( $sth->fetchrow_hashref ) { |
67 |
$dbh->do( |
47 |
$dbh->do( |
68 |
"UPDATE suggestions SET biblionumber = NULL where biblionumber NOT IN (SELECT biblionumber FROM biblio)" |
48 |
"UPDATE suggestions SET biblionumber = NULL where biblionumber NOT IN (SELECT biblionumber FROM biblio)" |
69 |
); |
49 |
); |
Lines 73-83
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='sugg
Link Here
|
73 |
} |
53 |
} |
74 |
|
54 |
|
75 |
# Add constraint for branchcode |
55 |
# Add constraint for branchcode |
76 |
$sth = $dbh->prepare( |
56 |
unless( foreign_key_exists( 'suggestions', 'suggestions_ibfk_branchcode' ) ) { |
77 |
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='suggestions_ibfk_branchcode'| |
|
|
78 |
); |
79 |
$sth->execute; |
80 |
unless ( $sth->fetchrow_hashref ) { |
81 |
$dbh->do( |
57 |
$dbh->do( |
82 |
"UPDATE suggestions SET branchcode = NULL where branchcode NOT IN (SELECT branchcode FROM branches)" |
58 |
"UPDATE suggestions SET branchcode = NULL where branchcode NOT IN (SELECT branchcode FROM branches)" |
83 |
); |
59 |
); |
Lines 87-92
q|SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME='sugg
Link Here
|
87 |
} |
63 |
} |
88 |
|
64 |
|
89 |
SetVersion($DBversion); |
65 |
SetVersion($DBversion); |
90 |
print |
66 |
print "Upgrade to $DBversion done (Bug 22368 - Add missing constraints to suggestions)\n"; |
91 |
"Upgrade to $DBversion done (Bug 22368 - Add missing constraints to suggestions)\n"; |
|
|
92 |
} |
67 |
} |
93 |
- |
|
|