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