From d335343837dbcd9f953d2aecb590b0ef45092b33 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 6 Mar 2012 10:53:30 -0500 Subject: [PATCH] Bug 7065 - reserves table needs a primary key Adds the primary key reservenumber to reserves and old_reserves. --- installer/data/mysql/kohastructure.sql | 4 +++ installer/data/mysql/updatedatabase.pl | 42 ++++++++++++++++++++++++++++++++ 2 files changed, 46 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 597a8be..698ab4f 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/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`), diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index e540874..135cdf7 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/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) -- 1.7.2.5