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

(-)a/installer/data/mysql/atomicupdate/bug_22368.perl (+91 lines)
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
}
(-)a/installer/data/mysql/kohastructure.sql (-2 / +10 lines)
Lines 3068-3077 CREATE TABLE `suggestions` ( -- purchase suggestions Link Here
3068
  PRIMARY KEY  (`suggestionid`),
3068
  PRIMARY KEY  (`suggestionid`),
3069
  KEY `suggestedby` (`suggestedby`),
3069
  KEY `suggestedby` (`suggestedby`),
3070
  KEY `managedby` (`managedby`),
3070
  KEY `managedby` (`managedby`),
3071
  KEY `status` (`STATUS`),
3071
  KEY `acceptedby` (`acceptedby`),
3072
  KEY `rejectedby` (`rejectedby`),
3072
  KEY `biblionumber` (`biblionumber`),
3073
  KEY `biblionumber` (`biblionumber`),
3074
  KEY `budgetid` (`budgetid`),
3073
  KEY `branchcode` (`branchcode`),
3075
  KEY `branchcode` (`branchcode`),
3076
  KEY `status` (`STATUS`),
3077
  CONSTRAINT `suggestions_ibfk_suggestedby` FOREIGN KEY (`suggestedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET 0 ON UPDATE CASCADE,
3078
  CONSTRAINT `suggestions_ibfk_managedby` FOREIGN KEY (`managedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
3079
  CONSTRAINT `suggestions_ibfk_acceptedby` FOREIGN KEY (`acceptedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
3080
  CONSTRAINT `suggestions_ibfk_rejectedby` FOREIGN KEY (`rejectedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,
3081
  CONSTRAINT `suggestions_ibfk_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,
3074
  CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE
3082
  CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE
3083
  CONSTRAINT `suggestions_ibfk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE,
3075
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3084
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
3076
3085
3077
--
3086
--
3078
- 

Return to bug 22368