this fix serialitems to have a foreign key to items(if an item is deleted,
serialitem must be deleted)
and it fixes issues to have only one issue by itemnumber(strange to have more
than one).
---
installer/data/mysql/kohastructure.sql | 5 +++--
installer/data/mysql/updatedatabase30.pl | 8 ++++++++
2 files changed, 11 insertions(+), 2 deletions(-)
diff --git a/installer/data/mysql/kohastructure.sql
b/installer/data/mysql/kohastructure.sql
index d41b5e6..5a98d86 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1104,7 +1104,7 @@ CREATE TABLE `import_items` (
DROP TABLE IF EXISTS `issues`;
CREATE TABLE `issues` (
`borrowernumber` int(11) default NULL,
- `itemnumber` int(11) default NULL,
+ `itemnumber` int(11) UNIQUE default NULL,
`date_due` date default NULL,
`branchcode` varchar(10) default NULL,
`issuingbranch` varchar(18) default NULL,
@@ -2195,7 +2195,8 @@ CREATE TABLE `serialitems` (
`serialid` int(11) NOT NULL,
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_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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `user_permissions`;
diff --git a/installer/data/mysql/updatedatabase30.pl
b/installer/data/mysql/updatedatabase30.pl
index 941153e..d326379 100644
--- a/installer/data/mysql/updatedatabase30.pl
+++ b/installer/data/mysql/updatedatabase30.pl
@@ -672,6 +672,14 @@ if (C4::Context->preference("Version") <
TransformToNum($DBversion)) {
SetVersion ($DBversion);
}
+$DBversion = "3.00.05.003";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+ $dbh->do("ALTER TABLE issues CHANGE COLUMN `itemnumber` `itemnumber`
int(11) UNIQUE DEFAULT NULL;");
+ $dbh->do("ALTER TABLE serialitems ADD CONSTRAINT `serialitems_sfk_2`
FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE
ON UPDATE CASCADE;");
+ print "Upgrade to $DBversion done (Improve serialitems table security)\n";
+ SetVersion ($DBversion);
+}
+
=item DropAllForeignKeys($table)
Drop all foreign keys of the table $table
--
1.6.3.3
_______________________________________________
Koha-patches mailing list
[email protected]
http://lists.koha.org/mailman/listinfo/koha-patches
