From 01fb62e42d6739c2ea5cbddbc8b6fddea72953c6 Mon Sep 17 00:00:00 2001 From: Paul Poulain Date: Wed, 3 Aug 2011 11:29:01 +0200 Subject: [PATCH] bug_4002: issues referential integrity Signed-off-by: Chris Cormack Signed-off-by: Paul Poulain --- installer/data/mysql/kohastructure.sql | 12 +++++----- installer/data/mysql/updatedatabase.pl | 33 ++++++++++++++++++++++++++++++++ kohaversion.pl | 2 +- 3 files changed, 40 insertions(+), 7 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e3c6f42..cd0d722 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -936,8 +936,8 @@ CREATE TABLE `import_items` ( DROP TABLE IF EXISTS `issues`; CREATE TABLE `issues` ( - `borrowernumber` int(11) default NULL, - `itemnumber` int(11) default NULL, + `borrowernumber` int(11), + `itemnumber` int(11), `date_due` date default NULL, `branchcode` varchar(10) default NULL, `issuingbranch` varchar(18) default NULL, @@ -947,11 +947,11 @@ CREATE TABLE `issues` ( `renewals` tinyint(4) default NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `issuedate` date default NULL, + PRIMARY KEY (`itemnumber`), KEY `issuesborridx` (`borrowernumber`), - KEY `issuesitemidx` (`itemnumber`), KEY `bordate` (`borrowernumber`,`timestamp`), - CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL + CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE, + CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -2098,7 +2098,7 @@ CREATE TABLE `serialitems` ( UNIQUE KEY `serialitemsidx` (`itemnumber`), KEY `serialitems_sfk_1` (`serialid`), CONSTRAINT `serialitems_sfk_1` FOREIGN KEY (`serialid`) REFERENCES `serial` (`serialid`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT serialitems_sfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `serialitems_sfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `user_permissions`; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 9cbc8db..ab313ea 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4370,6 +4370,39 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion($DBversion); } +$DBversion = "3.05.00.005"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate) + SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE borrowernumber IS NULL"); + $dbh->do("DELETE FROM issues WHERE borrowernumber IS NULL"); + + $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate) + SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE itemnumber IS NULL"); + $dbh->do("DELETE FROM issues WHERE itemnumber IS NULL"); + + $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate) + SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE NOT EXISTS (SELECT * FROM borrowers WHERE borrowernumber = issues.borrowernumber)"); + $dbh->do("DELETE FROM issues WHERE NOT EXISTS (SELECT * FROM borrowers WHERE borrowernumber = issues.borrowernumber)"); + + $dbh->do("INSERT INTO old_issues (borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate) + SELECT borrowernumber, itemnumber, date_due, branchcode, issuingbranch, returndate, lastreneweddate, `return`, renewals, timestamp, issuedate FROM issues WHERE NOT EXISTS (SELECT * FROM items WHERE itemnumber = issues.itemnumber)"); + $dbh->do("DELETE FROM issues WHERE NOT EXISTS (SELECT * FROM items WHERE itemnumber = issues.itemnumber)"); + + $dbh->do("ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_1`"); + $dbh->do("ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_2`"); + $dbh->do("ALTER TABLE issues ALTER COLUMN borrowernumber DROP DEFAULT"); + $dbh->do("ALTER TABLE issues ALTER COLUMN itemnumber DROP DEFAULT"); + $dbh->do("ALTER TABLE issues MODIFY COLUMN borrowernumber int(11) NOT NULL"); + $dbh->do("ALTER TABLE issues MODIFY COLUMN itemnumber int(11) NOT NULL"); + $dbh->do("ALTER TABLE issues DROP KEY `issuesitemidx`"); + $dbh->do("ALTER TABLE issues ADD PRIMARY KEY (`itemnumber`)"); + $dbh->do("ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE"); + $dbh->do("ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE"); + + print "Upgrade to $DBversion done (issues referential integrity)\n"; + SetVersion ($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) diff --git a/kohaversion.pl b/kohaversion.pl index 00bdbec..9021eb0 100644 --- a/kohaversion.pl +++ b/kohaversion.pl @@ -16,7 +16,7 @@ the kohaversion is divided in 4 parts : use strict; sub kohaversion { - our $VERSION = '3.05.00.004'; + our $VERSION = '3.05.00.005'; # version needs to be set this way # so that it can be picked up by Makefile.PL # during install -- 1.7.4.1