From 3cca3382bd7dbde298151780b074b6254a6a07ae Mon Sep 17 00:00:00 2001 From: Srdjan Jankovic Date: Fri, 15 Oct 2010 13:58:00 +1300 Subject: [PATCH] bug 5308: subscriptionroutinglist more strict borrowernumber and subscriptionid NOT NULL, UNIQUE(subscriptionid, borrowernumber) Signed-off-by: Galen Charlton Signed-off-by: Chris Nighswonger --- installer/data/mysql/kohastructure.sql | 7 ++++--- installer/data/mysql/updatedatabase.pl | 19 +++++++++++++++++++ 2 files changed, 23 insertions(+), 3 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e42652c486..51e4c88860 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1791,10 +1791,11 @@ CREATE TABLE `subscriptionhistory` ( DROP TABLE IF EXISTS `subscriptionroutinglist`; CREATE TABLE `subscriptionroutinglist` ( `routingid` int(11) NOT NULL auto_increment, - `borrowernumber` int(11) default NULL, + `borrowernumber` int(11) NOT NULL, `ranking` int(11) default NULL, - `subscriptionid` int(11) default NULL, - PRIMARY KEY (`routingid`) + `subscriptionid` int(11) NOT NULL, + PRIMARY KEY (`routingid`), + UNIQUE (`subscriptionid`, `borrowernumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 523c533804..ddf3fa5ca4 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -3799,6 +3799,25 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +$DBversion = "3.01.00.XXX"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do("DELETE FROM subscriptionroutinglist WHERE borrowernumber IS NULL;"); + $dbh->do("ALTER TABLE subscriptionroutinglist MODIFY COLUMN `borrowernumber` int(11) NOT NULL;"); + $dbh->do("DELETE FROM subscriptionroutinglist WHERE subscriptionid IS NULL;"); + $dbh->do("ALTER TABLE subscriptionroutinglist MODIFY COLUMN `subscriptionid` int(11) NOT NULL;"); + $dbh->do("CREATE TEMPORARY TABLE del_subscriptionroutinglist + SELECT s1.routingid FROM subscriptionroutinglist s1 + WHERE EXISTS (SELECT * FROM subscriptionroutinglist s2 + WHERE s2.borrowernumber = s1.borrowernumber + AND s2.subscriptionid = s1.subscriptionid + AND s2.routingid < s1.routingid);"); + $dbh->do("DELETE FROM subscriptionroutinglist + WHERE routingid IN (SELECT routingid FROM del_subscriptionroutinglist);"); + $dbh->do("ALTER TABLE subscriptionroutinglist ADD UNIQUE (subscriptionid, borrowernumber);"); + print "Upgrade to $DBversion done (Make subscriptionroutinglist more strict)\n"; + SetVersion ($DBversion); +} + =item DropAllForeignKeys($table) Drop all foreign keys of the table $table -- 2.39.5