From 9f21a6c640da417b27c6655f0f4d1307b06708a5 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 6 Mar 2012 10:53:30 -0500 Subject: [PATCH] [PASSED QA] Bug 7065 - reserves table needs a primary key Adds the primary key reserve_id to reserves and old_reserves. Thanks to gmcharlt and jcamins for contributions. Signed-off-by: MJ Ray Signed-off-by: Ian Walls Updated DBrev to start with 3.09... instead of 3.08... --- installer/data/mysql/kohastructure.sql | 4 ++ installer/data/mysql/updatedatabase.pl | 63 ++++++++++++++++++++++++++++++++ 2 files changed, 67 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 9a94e0f..00df1ee 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 auto_increment, -- 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 38aa70c..33c5397 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5246,6 +5246,69 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.09.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do('START TRANSACTION'); + $dbh->do('CREATE TABLE tmp_reserves AS SELECT * FROM reserves LIMIT 0'); + $dbh->do('ALTER TABLE tmp_reserves ADD reserve_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST'); + $dbh->do(" + INSERT INTO tmp_reserves ( + borrowernumber, reservedate, biblionumber, + constrainttype, branchcode, notificationdate, + reminderdate, cancellationdate, reservenotes, + priority, found, timestamp, itemnumber, + waitingdate, expirationdate, lowestPriority + ) SELECT + borrowernumber, reservedate, biblionumber, + constrainttype, branchcode, notificationdate, + reminderdate, cancellationdate, reservenotes, + priority, found, timestamp, itemnumber, + waitingdate, expirationdate, lowestPriority + FROM old_reserves ORDER BY reservedate + "); + $dbh->do('SET @ai = ( SELECT MAX( reserve_id ) FROM tmp_reserves )'); + $dbh->do('TRUNCATE old_reserves'); + $dbh->do('ALTER TABLE old_reserves ADD reserve_id INT( 11 ) NOT NULL PRIMARY KEY FIRST'); + $dbh->do('INSERT INTO old_reserves SELECT * FROM tmp_reserves WHERE reserve_id <= @ai'); + $dbh->do(" + INSERT INTO tmp_reserves ( + borrowernumber, reservedate, biblionumber, + constrainttype, branchcode, notificationdate, + reminderdate, cancellationdate, reservenotes, + priority, found, timestamp, itemnumber, + waitingdate, expirationdate, lowestPriority + ) SELECT + borrowernumber, reservedate, biblionumber, + constrainttype, branchcode, notificationdate, + reminderdate, cancellationdate, reservenotes, + priority, found, timestamp, itemnumber, + waitingdate, expirationdate, lowestPriority + FROM reserves ORDER BY reservedate + "); + $dbh->do('TRUNCATE reserves'); + $dbh->do('ALTER TABLE reserves ADD reserve_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST'); + $dbh->do('INSERT INTO reserves SELECT * FROM tmp_reserves WHERE reserve_id > @ai'); + $dbh->do('DROP TABLE tmp_reserves'); + $dbh->do('COMMIT'); + + my $sth = $dbh->prepare(" + SELECT COUNT( * ) AS count + FROM information_schema.COLUMNS + WHERE COLUMN_NAME = 'reserve_id' + AND ( + TABLE_NAME LIKE 'reserves' + OR + TABLE_NAME LIKE 'old_reserves' + ) + "); + $sth->execute(); + my $row = $sth->fetchrow_hashref(); + die("Failed to add reserve_id to reserves tables, please refresh the page to try again.") unless ( $row->{'count'} ); + + print "Upgrade to $DBversion done (add reserve_id to reserves & old_reserves tables)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) -- 1.7.0.4