From dc3a5f3f19db5e944d1c997b9ae4ab044f1b379b 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 reserve_id to reserves and old_reserves. --- installer/data/mysql/kohastructure.sql | 4 + installer/data/mysql/updatedatabase.pl | 110 ++++++++++++++++++++++++++++++++ 2 files changed, 114 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index a210293..ccdf1c7 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1446,6 +1446,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) + `reserve_id` 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 @@ -1464,6 +1465,7 @@ CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have b `lowestPriority` tinyint(1) NOT NULL, `suspend` BOOLEAN NOT NULL DEFAULT 0, `suspend_until` DATETIME NULL DEFAULT NULL, + PRIMARY KEY (`reserve_id`), KEY `old_reserves_borrowernumber` (`borrowernumber`), KEY `old_reserves_biblionumber` (`biblionumber`), KEY `old_reserves_itemnumber` (`itemnumber`), @@ -1642,6 +1644,7 @@ CREATE TABLE `reserveconstraints` ( DROP TABLE IF EXISTS `reserves`; CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha + `reserve_id` 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 @@ -1660,6 +1663,7 @@ CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha `lowestPriority` tinyint(1) NOT NULL, `suspend` BOOLEAN NOT NULL DEFAULT 0, `suspend_until` DATETIME NULL DEFAULT NULL, + PRIMARY KEY (`reserve_id`), 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 84f0437..bb4de0b 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5212,6 +5212,116 @@ 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 reserve_id INT NOT NULL FIRST"); + $dbh->do("ALTER TABLE old_reserves ADD reserve_id 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 ( $reserve_id, $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 reserve_id = $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 .= "AND reserve_id = 0 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 ( $reserve_id, $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 reserve_id = $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 .= "AND reserve_id = 0 LIMIT 1"; + + $dbh->do( $query_update ); + $i++; + } + + $dbh->do("ALTER TABLE reserves ADD PRIMARY KEY ( reserve_id )"); + $dbh->do("ALTER TABLE old_reserves ADD PRIMARY KEY ( reserve_id )"); + + $dbh->do("ALTER TABLE reserves CHANGE reserve_id reserve_id INT( 11 ) NOT NULL AUTO_INCREMENT"); + + print "Upgrade to $DBversion done ( add reserve_id to reserves and old_reserves tables )\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) -- 1.7.2.5