From 5af57eb9234affc84539f759f5329d2b780768e5 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Wed, 23 Mar 2016 16:59:26 +0100 Subject: [PATCH] Bug 16170: Pseudo foreign key in Items Content-Type: text/plain; charset=utf-8 While many of us would like to get rid of biblioitems one day, the current scheme includes a biblioitemnumber and a biblionumber in Items. (Which is not so great..) But also note that biblionumber is NOT defined as a foreign key in Items, although a belongs_to relation has been added to the DBIx scheme! This inconsistency should be resolved. The "remove biblioitem table" operation is a large one, but in the meantime we better make biblionumber a regular FK not a 'pseudo' one. Note: If in an (very) exceptional case biblionumbers are found in items, that do not exist in biblio, this patch prints a warning at upgrade time and does not add the constraint. @RM: Please update the DBIx scheme accordingly. Test plan: [1] Run the upgrade. Check if the FK constraint has been added. [2] Remove the FK constraint. Change the biblionumber of one item to an unexisting record. Run the upgrade again. Notice the warning. Signed-off-by: Marcel de Rooy Tested both cases: constraint added as well as warning printed. --- installer/data/mysql/atomicupdate/16170_dbrev.sql | 25 +++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 3 ++- 2 files changed, 27 insertions(+), 1 deletion(-) create mode 100644 installer/data/mysql/atomicupdate/16170_dbrev.sql diff --git a/installer/data/mysql/atomicupdate/16170_dbrev.sql b/installer/data/mysql/atomicupdate/16170_dbrev.sql new file mode 100644 index 0000000..7cbfdeb --- /dev/null +++ b/installer/data/mysql/atomicupdate/16170_dbrev.sql @@ -0,0 +1,25 @@ +my $dbh = C4::Context->dbh; +my $DBversion = "XXX"; + +# RM: Copy/paste from here, and uncomment + +#if ( CheckVersion($DBversion) ) { + # this should normally not be needed, but just in case + my ( $cnt ) = $dbh->selectrow_array( q| +SELECT COUNT(*) FROM items it +LEFT JOIN biblio bi ON bi.biblionumber=it.biblionumber +LEFT JOIN biblioitems bii USING (biblioitemnumber) +WHERE bi.biblionumber IS NULL + |); + if( $cnt ) { + 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"; + } else { + # now add FK + $dbh->do( q| +ALTER TABLE items +ADD FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE + |); + print "Upgrade to $DBversion done (Bug 1xxxx - Add FK for biblionumber in items)\n"; + } + #SetVersion($DBversion); +#} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 63fefe7..14a7019 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1282,7 +1282,8 @@ CREATE TABLE `items` ( -- holdings/item information KEY `itype_idx` (`itype`), CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE, - CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE + CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE, + CONSTRAINT `items_ibfk_4` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- 1.7.10.4