From f18482f17294e0ce406074e976b402b435849cc2 Mon Sep 17 00:00:00 2001 From: Ryan Higgins Date: Tue, 8 Jan 2008 15:17:00 -0600 Subject: [PATCH] CHANGE TO PREVIOUS .046 DATABASE UPDATE This commit changes .046 updatedatebase , where itemnumber was changed to int and keyed on items table. As there is code in serials module that uses itemnumber (text) as a list of itemnumbers, this change would cause data loss if that field had multiple items in it. Instead, we add a new table to link serial and items tables, so that (1) we can have many items per serial issue, and (2) staged upgrades that use the serial.itemnumber (text) list won't be affected. Further commits will make use of the serialitems linking table. Signed-off-by: Chris Cormack Signed-off-by: Joshua Ferraro --- C4/Items.pm | 6 ++++-- installer/data/mysql/kohastructure.sql | 10 ++++++++-- installer/data/mysql/updatedatabase.pl | 5 +++-- 3 files changed, 15 insertions(+), 6 deletions(-) diff --git a/C4/Items.pm b/C4/Items.pm index f263629cc0..975e0c89e6 100644 --- a/C4/Items.pm +++ b/C4/Items.pm @@ -1586,7 +1586,9 @@ Perform the actual insert into the C table. sub _koha_new_item { my ( $dbh, $item, $barcode ) = @_; my $error; - +use Data::Dumper; +warn Dumper($item); +warn $barcode; my $query = "INSERT INTO items SET biblionumber = ?, @@ -1620,7 +1622,7 @@ sub _koha_new_item { ccode = ?, itype = ?, materials = ?, - uri = ?, + uri = ? "; my $sth = $dbh->prepare($query); $sth->execute( diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 741122c3b2..26a327295e 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1535,11 +1535,10 @@ CREATE TABLE `serial` ( `planneddate` date default NULL, `notes` text, `publisheddate` date default NULL, - `itemnumber` int(11) default NULL, + `itemnumber` text default NULL, `claimdate` date default NULL, `routingnotes` text, PRIMARY KEY (`serialid`), - KEY serialitem (`itemnumber`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- @@ -1885,6 +1884,13 @@ CREATE TABLE language_script_mapping ( KEY `language_subtag` (`language_subtag`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +DROP TABLE IF EXISTS serialitems; +CREATE TABLE serialitem ( + serialid int(11) NOT NULL, + itemnumber int(11) NOT NULL, + UNIQUE KEY `serialididx` (`serialid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 11ea46f469..47a1ce3fd1 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -932,10 +932,11 @@ VALUES( 'he', 'Hebr')"); $DBversion = "3.00.00.046"; if (C4::Context->preference("Version") < TransformToNum($DBversion)) { - $dbh->do("ALTER TABLE `serial` CHANGE `itemnumber` `itemnumber` int(11) default NULL"); $dbh->do("ALTER TABLE `subscription` CHANGE `numberlength` `numberlength` int(11) default NULL , CHANGE `weeklength` `weeklength` int(11) default NULL"); - $dbh->do("ALTER TABLE `serial` ADD UNIQUE KEY `serialitemidx` (`itemnumber`)"); + $dbh->do("ALTER TABLE `serial` ADD UNIQUE KEY `serialitemidx` (`serialitem`)"); + $dbh->do("CREATE TABLE `serialitems` (`serialid` int(11) NOT NULL, `itemnumber` int(11) NOT NULL, UNIQUE KEY (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8"); + $dbh->do("INSERT INTO `serialitems` SELECT `serialid`,`itemnumber` from serial where NOT ISNULL(itemnumber) && itemnumber <> '' && itemnumber NOT LIKE '%,%'"); print "Upgrade to $DBversion done (Update serial table, add index on itemnumber. )\n"; SetVersion ($DBversion); } -- 2.39.2