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

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

Return to bug 22368