From 1eee62d0f26ceb17fe6750bef14a5aa31b749cf8 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 | 109 ++++++++++++++++++++++++++++++++ 2 files changed, 113 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 153e17b..eacda30 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1386,6 +1386,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 @@ -1402,6 +1403,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`), @@ -1579,6 +1581,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 @@ -1595,6 +1598,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 082d5ca..3cc7060 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4923,6 +4923,115 @@ 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_select = "SELECT * FROM old_reserves ORDER BY timestamp ASC"; + $sth_select = $dbh->prepare( $query_select ); + $sth_select->execute(); + while( my @r = $sth_select->fetchrow_array() ) { + my ( $reservenumber, $borrowernumber, $reservedate, $biblionumber, $constrainttype, $branchcode, + $notificationdate, $reminderdate, $cancellationdate, $reservenotes, $priority, $found, + $timestamp, $itemnumber, $waitingdate, $expirationdate, $lowestPriority, $suspend, $suspend_until ) = @r; + + $query_update = "UPDATE old_reserves SET reservenumber = $i WHERE "; + $query_update .= ( defined $borrowernumber ) ? "borrowernumber = '$borrowernumber'" : "borrowernumber IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $reservedate ) ? "reservedate = '$reservedate'" : "reservedate IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $biblionumber ) ? "biblionumber = '$biblionumber'" : "biblionumber IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $constrainttype ) ? "constrainttype = '$constrainttype'" : "constrainttype IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $branchcode ) ? "branchcode = '$branchcode'" : "branchcode IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $notificationdate ) ? "notificationdate = '$notificationdate'" : "notificationdate IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $reminderdate ) ? "reminderdate = '$reminderdate'" : "reminderdate IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $cancellationdate ) ? "cancellationdate = '$cancellationdate'" : "cancellationdate IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $reservenotes ) ? "reservenotes = '$reservenotes'" : "reservenotes IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $priority ) ? "priority = '$priority'" : "priority IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $found ) ? "found = '$found'" : "found IS NULL"; + $query_update .= " AND "; + $query_update .= "timestamp = '$timestamp'"; + $query_update .= " AND "; + $query_update .= ( defined $itemnumber ) ? "itemnumber = '$itemnumber'" : "itemnumber IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $waitingdate ) ? "waitingdate = '$waitingdate'" : "waitingdate IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $expirationdate ) ? "expirationdate = '$expirationdate'" : "expirationdate IS NULL"; + $query_update .= " AND "; + $query_update .= "lowestPriority = '$lowestPriority'"; + $query_update .= " LIMIT 1"; + + $dbh->do( $query_update ); + $i++; + } + + $query_select = "SELECT * FROM reserves ORDER BY timestamp ASC"; + $sth_select = $dbh->prepare( $query_select ); + $sth_select->execute(); + while( my @r = $sth_select->fetchrow_array() ) { + my ( $reservenumber, $borrowernumber, $reservedate, $biblionumber, $constrainttype, $branchcode, + $notificationdate, $reminderdate, $cancellationdate, $reservenotes, $priority, $found, + $timestamp, $itemnumber, $waitingdate, $expirationdate, $lowestPriority, $suspend, $suspend_until ) = @r; + + $query_update = "UPDATE reserves SET reservenumber = $i WHERE "; + $query_update .= ( defined $borrowernumber ) ? "borrowernumber = '$borrowernumber'" : "borrowernumber IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $reservedate ) ? "reservedate = '$reservedate'" : "reservedate IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $biblionumber ) ? "biblionumber = '$biblionumber'" : "biblionumber IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $constrainttype ) ? "constrainttype = '$constrainttype'" : "constrainttype IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $branchcode ) ? "branchcode = '$branchcode'" : "branchcode IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $notificationdate ) ? "notificationdate = '$notificationdate'" : "notificationdate IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $reminderdate ) ? "reminderdate = '$reminderdate'" : "reminderdate IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $cancellationdate ) ? "cancellationdate = '$cancellationdate'" : "cancellationdate IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $reservenotes ) ? "reservenotes = '$reservenotes'" : "reservenotes IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $priority ) ? "priority = '$priority'" : "priority IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $found ) ? "found = '$found'" : "found IS NULL"; + $query_update .= " AND "; + $query_update .= "timestamp = '$timestamp'"; + $query_update .= " AND "; + $query_update .= ( defined $itemnumber ) ? "itemnumber = '$itemnumber'" : "itemnumber IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $waitingdate ) ? "waitingdate = '$waitingdate'" : "waitingdate IS NULL"; + $query_update .= " AND "; + $query_update .= ( defined $expirationdate ) ? "expirationdate = '$expirationdate'" : "expirationdate IS NULL"; + $query_update .= " AND "; + $query_update .= "lowestPriority = '$lowestPriority'"; + $query_update .= " LIMIT 1"; + + $dbh->do( $query_update ); + $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 -- 1.7.2.5