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

(-)a/installer/data/mysql/atomicupdate/16170_dbrev.perl (+25 lines)
Line 0 Link Here
1
my $dbh = C4::Context->dbh;
2
my $DBversion = "XXX";
3
4
# RM: Copy/paste from here, and uncomment
5
6
#if ( CheckVersion($DBversion) ) {
7
    # this should normally not be needed, but just in case
8
    my ( $cnt ) = $dbh->selectrow_array( q|
9
SELECT COUNT(*) FROM items it
10
LEFT JOIN biblio bi ON bi.biblionumber=it.biblionumber
11
LEFT JOIN biblioitems bii USING (biblioitemnumber)
12
WHERE bi.biblionumber IS NULL
13
    |);
14
    if( $cnt ) {
15
        print "WARNING: You have corrupted data in your items table!! The table contains $cnt references to biblio records that do not exist.\nPlease correct your data IMMEDIATELY after this upgrade and manually add the foreign key constraint for biblionumber in the items table.\n";
16
    } else {
17
        # now add FK
18
        $dbh->do( q|
19
ALTER TABLE items
20
ADD FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
21
        |);
22
        print "Upgrade to $DBversion done (Bug 1xxxx - Add FK for biblionumber in items)\n";
23
    }
24
    #SetVersion($DBversion);
25
#}
(-)a/installer/data/mysql/kohastructure.sql (-2 / +2 lines)
Lines 1284-1290 CREATE TABLE `items` ( -- holdings/item information Link Here
1284
  KEY `itype_idx` (`itype`),
1284
  KEY `itype_idx` (`itype`),
1285
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1285
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
1286
  CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1286
  CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1287
  CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE
1287
  CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE,
1288
  CONSTRAINT `items_ibfk_4` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
1288
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1289
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1289
1290
1290
--
1291
--
1291
- 

Return to bug 16170