@@ -, +, @@ --- installer/data/mysql/kohastructure.sql | 4 +++ installer/data/mysql/updatedatabase.pl | 42 ++++++++++++++++++++++++++++++++ 2 files changed, 46 insertions(+), 0 deletions(-) --- a/installer/data/mysql/kohastructure.sql +++ a/installer/data/mysql/kohastructure.sql @@ -1384,6 +1384,7 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r -- DROP TABLE IF EXISTS `old_reserves`; CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled) + `reservenumber` int(11) NOT NULL, -- primary key `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for `reservedate` date default NULL, -- the date the hold was places `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on @@ -1400,6 +1401,7 @@ CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library `expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) `lowestPriority` tinyint(1) NOT NULL, + PRIMARY KEY (`reservenumber`), KEY `old_reserves_borrowernumber` (`borrowernumber`), KEY `old_reserves_biblionumber` (`biblionumber`), KEY `old_reserves_itemnumber` (`itemnumber`), @@ -1577,6 +1579,7 @@ CREATE TABLE `reserveconstraints` ( DROP TABLE IF EXISTS `reserves`; CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha + `reservenumber` int(11) NOT NULL, -- primary key `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron this hold is for `reservedate` date default NULL, -- the date the hold was places `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on @@ -1593,6 +1596,7 @@ CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library `expirationdate` DATE DEFAULT NULL, -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) `lowestPriority` tinyint(1) NOT NULL, + PRIMARY KEY (`reservenumber`), KEY priorityfoundidx (priority,found), KEY `borrowernumber` (`borrowernumber`), KEY `biblionumber` (`biblionumber`), --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -4734,6 +4734,48 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.07.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + my $i = 1; + + my ( $query_update, $sth_update, $query_select, $sth_select ); + + $dbh->do("ALTER TABLE reserves ADD reservenumber INT NOT NULL FIRST"); + $dbh->do("ALTER TABLE old_reserves ADD reservenumber INT NOT NULL FIRST"); + + $query_update = "UPDATE old_reserves SET reservenumber = ? WHERE borrowernumber = ? AND reservedate = ? AND biblionumber = ? AND branchcode = ? AND timestamp = ? LIMIT 1"; + $sth_update = $dbh->prepare( $query_update ); + + $query_select = "SELECT borrowernumber, reservedate, biblionumber, branchcode, timestamp FROM old_reserves ORDER BY timestamp ASC"; + $sth_select = $dbh->prepare( $query_select ); + $sth_select->execute(); + while( my @r = $sth_select->fetchrow_array() ) { + print "Updating old_reserves row with key $i\n"; + $sth_update->execute( $i, @r ); + $i++; + } + + $query_update = "UPDATE reserves SET reservenumber = ? WHERE borrowernumber = ? AND reservedate = ? AND biblionumber = ? AND branchcode = ? AND timestamp = ? LIMIT 1"; + $sth_update = $dbh->prepare( $query_update ); + + $query_select = "SELECT borrowernumber, reservedate, biblionumber, branchcode, timestamp FROM reserves ORDER BY timestamp ASC"; + $sth_select = $dbh->prepare( $query_select ); + $sth_select->execute(); + while( my @r = $sth_select->fetchrow_array() ) { + print "Updating reserves row with key $i\n"; + $sth_update->execute( $i, @r ); + $i++; + } + + $dbh->do("ALTER TABLE reserves ADD PRIMARY KEY ( reservenumber )"); + $dbh->do("ALTER TABLE old_reserves ADD PRIMARY KEY ( reservenumber )"); + + $dbh->do("ALTER TABLE reserves CHANGE reservenumber reservenumber INT( 11 ) NOT NULL AUTO_INCREMENT"); + + print "Upgrade to $DBversion done ( add reservenumber to reserves and old_reserves tables )\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) --