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

(-)a/installer/data/mysql/atomicupdate/bug_21983.perl (+24 lines)
Line 0 Link Here
1
$DBversion = 'XXX';  # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
    unless ( foreign_key_exists('illrequests', 'illrequests_bifk') ) {
4
        # There may be requests already in the DB that have orphan
5
        # bib IDs, these will prevent the creation of the foreign key
6
        # So we need to idenfify and NULL them
7
        my @orphans;
8
        my $sth = $dbh->prepare( "SELECT illrequest_id FROM illrequests WHERE biblio_id NOT IN (SELECT biblionumber FROM biblio)" );
9
        $sth->execute();
10
        while (( my $id ) = $sth->fetchrow_array) {
11
            push @orphans, $id;
12
        }
13
        if (scalar @orphans > 0) {
14
            $dbh->do( "UPDATE illrequests SET biblio_id = NULL WHERE illrequest_id IN ( ? )", undef, join(",", @orphans) );
15
        }
16
17
        # Now we can create the foreign key
18
        $dbh->do( "ALTER TABLE illrequests ADD CONSTRAINT illrequests_bifk FOREIGN KEY (biblio_id) REFERENCES biblio (biblionumber) ON DELETE SET NULL ON UPDATE CASCADE;" );
19
    }
20
21
    # Always end with this (adjust the bug info)
22
    SetVersion( $DBversion );
23
    print "Upgrade to $DBversion done (Bug 21983 - Add foreign key constraint on biblio_id)\n";
24
}
(-)a/installer/data/mysql/kohastructure.sql (-2 / +5 lines)
Lines 4187-4193 CREATE TABLE illrequests ( Link Here
4187
    CONSTRAINT `illrequests_bcfk_2`
4187
    CONSTRAINT `illrequests_bcfk_2`
4188
      FOREIGN KEY (`branchcode`)
4188
      FOREIGN KEY (`branchcode`)
4189
      REFERENCES `branches` (`branchcode`)
4189
      REFERENCES `branches` (`branchcode`)
4190
      ON UPDATE CASCADE ON DELETE CASCADE
4190
      ON UPDATE CASCADE ON DELETE CASCADE,
4191
    CONSTRAINT `illrequests_bifk`
4192
      FOREIGN KEY (`biblio_id`)
4193
      REFERENCES `biblio` (`biblionumber`)
4194
      ON UPDATE CASCADE ON DELETE SET NULL
4191
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4195
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4192
4196
4193
--
4197
--
4194
- 

Return to bug 21983