View | Details | Raw Unified | Return to bug 22368
Collapse All | Expand All

(-)a/installer/data/mysql/atomicupdate/bug_22368.perl (-33 / +7 lines)
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
- 

Return to bug 22368