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

(-)a/installer/data/mysql/atomicupdate/bug_22368.perl (+92 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("ALTER TABLE suggestions CHANGE COLUMN suggestedby suggestedby INT(11) NULL DEFAULT NULL;");
11
        $dbh->do(
12
"UPDATE suggestions SET suggestedby = NULL where suggestedby NOT IN (SELECT borrowernumber FROM borrowers)"
13
        );
14
        $dbh->do(
15
"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_suggestedby` FOREIGN KEY (`suggestedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE"
16
        );
17
    }
18
19
    # Add constraint for managedby
20
    $sth = $dbh->prepare(
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(
26
"UPDATE suggestions SET managedby = NULL where managedby NOT IN (SELECT borrowernumber FROM borrowers)"
27
        );
28
        $dbh->do(
29
"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_managedby` FOREIGN KEY (`managedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE"
30
        );
31
    }
32
33
    # Add constraint for acceptedby
34
    $sth = $dbh->prepare(
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(
40
"UPDATE suggestions SET acceptedby = NULL where acceptedby NOT IN (SELECT borrowernumber FROM borrowers)"
41
        );
42
        $dbh->do(
43
"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_acceptedby` FOREIGN KEY (`acceptedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE"
44
        );
45
    }
46
47
    # Add constraint for rejectedby
48
    $sth = $dbh->prepare(
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(
54
"UPDATE suggestions SET rejectedby = NULL where rejectedby NOT IN (SELECT borrowernumber FROM borrowers)"
55
        );
56
        $dbh->do(
57
"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_rejectedby` FOREIGN KEY (`rejectedby`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE"
58
        );
59
    }
60
61
    # Add constraint for biblionumber
62
    $sth = $dbh->prepare(
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(
68
"UPDATE suggestions SET biblionumber = NULL where biblionumber NOT IN (SELECT biblionumber FROM biblio)"
69
        );
70
        $dbh->do(
71
"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE"
72
        );
73
    }
74
75
    # Add constraint for branchcode
76
    $sth = $dbh->prepare(
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(
82
"UPDATE suggestions SET branchcode = NULL where branchcode NOT IN (SELECT branchcode FROM branches)"
83
        );
84
        $dbh->do(
85
"ALTER TABLE suggestions ADD CONSTRAINT `suggestions_ibfk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE"
86
        );
87
    }
88
89
    SetVersion($DBversion);
90
    print
91
"Upgrade to $DBversion done (Bug 22368 - Add missing constraints to suggestions)\n";
92
}
(-)a/installer/data/mysql/kohastructure.sql (-3 / +11 lines)
Lines 3035-3041 CREATE TABLE aqbasketusers ( Link Here
3035
DROP TABLE IF EXISTS `suggestions`;
3035
DROP TABLE IF EXISTS `suggestions`;
3036
CREATE TABLE `suggestions` ( -- purchase suggestions
3036
CREATE TABLE `suggestions` ( -- purchase suggestions
3037
  `suggestionid` int(8) NOT NULL auto_increment, -- unique identifier assigned automatically by Koha
3037
  `suggestionid` int(8) NOT NULL auto_increment, -- unique identifier assigned automatically by Koha
3038
  `suggestedby` int(11) NOT NULL default 0, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table
3038
  `suggestedby` int(11) DEFAULT NULL, -- borrowernumber for the person making the suggestion, foreign key linking to the borrowers table
3039
  `suggesteddate` date NOT NULL, -- date the suggestion was submitted
3039
  `suggesteddate` date NOT NULL, -- date the suggestion was submitted
3040
  `managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table
3040
  `managedby` int(11) default NULL, -- borrowernumber for the librarian managing the suggestion, foreign key linking to the borrowers table
3041
  `manageddate` date default NULL, -- date the suggestion was updated
3041
  `manageddate` date default NULL, -- date the suggestion was updated
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 NULL 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