From 42237342c54fa67ec1797a6f6ccc37d3cfa20a29 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 21 Feb 2012 12:35:42 -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 | 50 ++++++++++++++++++++++++++++++++ 2 files changed, 54 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 67149c9d..4ecf7dd 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, `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`), @@ -1559,6 +1561,7 @@ CREATE TABLE `reserveconstraints` ( DROP TABLE IF EXISTS `reserves`; CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha + `reservenumber` int(11) NOT NULL AUTO_INCREMENT, `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 @@ -1575,6 +1578,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 d2d9d97..f0edd93 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4712,6 +4712,56 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.07.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do("ALTER TABLE reserves ADD reservenumber INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST"); + $dbh->do("ALTER TABLE old_reserves ADD reservenumber INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST"); + + my ( $sth, $row ); + + $sth = $dbh->prepare("SELECT MAX( reservenumber ) AS last_old_reservenumber FROM old_reserves"); + $sth->execute(); + $row = $sth->fetchrow_hashref(); + my $last_old_reservenumber = $row->{'last_old_reservenumber'}; + $sth->finish(); + + $sth = $dbh->prepare("SELECT MAX( reservenumber ) AS last_reservenumber FROM reserves"); + $sth->execute(); + $row = $sth->fetchrow_hashref(); + my $last_bad_reservenumber = $row->{'last_reservenumber'}; + my $reserves_count = $last_bad_reservenumber; + $sth->finish(); + + my $query = "UPDATE reserves SET reservenumber = ? WHERE reservenumber = ?"; + my $update_sth = $dbh->prepare( $query ); + + ## First, change all reservenumbers to new ones above the highest that was intially added. + ## That way, we can renumber them without the risk of using a duplicate reservenumber + my $new_reservenumber = $last_bad_reservenumber + 1; + $sth = $dbh->prepare("SELECT reservenumber FROM reserves"); + $sth->execute(); + while ( $row = $sth->fetchrow_hashref() ) { + $update_sth->execute( $new_reservenumber, $row->{'reservenumber'} ); + $new_reservenumber++; + } + + ## First, change all reservenumbers to new ones above the highest that was intially added. + ## That way, we can renumber them without the risk of using a duplicate reservenumber + $new_reservenumber = $last_old_reservenumber + 1; + $sth = $dbh->prepare("SELECT reservenumber FROM reserves"); + $sth->execute(); + while ( $row = $sth->fetchrow_hashref() ) { + $update_sth->execute( $new_reservenumber, $row->{'reservenumber'} ); + $new_reservenumber++; + } + + ## Drop the auto_increment from the old_reserves table. + $dbh->do("ALTER TABLE old_reserves CHANGE reservenumber reservenumber INT( 11 ) NOT NULL"); + + print "Upgrade to $DBversion done (Added reservenumber to reserves and old_reserves tables)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) -- 1.7.2.5