Attachment 9403 Details for Bug 7065 – Bug 7065 - reserves table needs a primary key

[patch] Bug 7065 - reserves table needs a primary key

Bug-7065---reserves-table-needs-a-primary-key.patch (text/plain), 5.86 KB, created by Kyle M Hall on 2012-05-03 15:27:37 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall

Created: 2012-05-03 15:27:37 UTC

Size: 5.86 KB

patch

obsolete

>From f316bb9d3daf8eb792f77c7d648350f95c7f40e3 Mon Sep 17 00:00:00 2001
>From: Kyle M Hall <kyle@bywatersolutions.com>
>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.
>
>Thanks to gmcharlt and jcamins for contributions.
>---
> 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 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..06311b3 100755
>--- a/installer/data/mysql/updatedatabase.pl
>+++ b/installer/data/mysql/updatedatabase.pl
>@@ -5212,6 +5212,69 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
>     SetVersion($DBversion);
> }
> 
>+$DBversion = "3.08.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.2.5

Actions: View | Diff | Splinter Review

Attachments on bug 7065: 7789 | 8036 | 8300 | 8304 | 8305 | 9388 | 9389 | 9403 | 9404 | 9413 | 9740 | 10140 | 10250