From da5d9b71e4afd9d70d153de89064a8e7c0e52a37 Mon Sep 17 00:00:00 2001 From: Srdjan Jankovic Date: Wed, 13 Apr 2011 17:11:30 +1200 Subject: [PATCH] bug_4002: issues referential integrity --- installer/data/mysql/kohastructure.sql | 12 ++++++------ installer/data/mysql/updatedatabase.pl | 22 ++++++++++++++++++++++ 2 files changed, 28 insertions(+), 6 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e3c6f42..7ecf65a 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`), + CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ) 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 3180cfc..5e3a229 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4304,6 +4304,28 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +$DBversion = "3.03.00.XXX"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do("DELETE FROM issues WHERE borrowernumber IS NULL"); + $dbh->do("DELETE FROM issues WHERE itemnumber IS NULL"); + $dbh->do("DELETE FROM issues WHERE NOT EXISTS (SELECT * FROM borrowers WHERE borrowernumber = issues.borrowernumber)"); + $dbh->do("DELETE FROM issues WHERE NOT EXISTS (SELECT * FROM items WHERE itemnumber = issues.itemnumber)"); + $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 DROP FOREIGN KEY `issues_ibfk_1`"); + $dbh->do("ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_2`"); + $dbh->do("ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)"); + $dbh->do("ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`)"); + + + print "Upgrade to $DBversion done (issues referential integrity)\n"; + SetVersion ($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) -- 1.6.5