From: Srdjan Jankovic <[email protected]>
borrowernumber and subscriptionid NOT NULL, UNIQUE(subscriptionid,
borrowernumber)
---
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 e42652c..51e4c88 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 2bd4e21..703a0fa 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -3745,6 +3745,25 @@ if (C4::Context->preference("Version") <
TransformToNum($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
--
1.7.1
_______________________________________________
Koha-patches mailing list
[email protected]
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-patches
