Attachment 10250 Details for Bug 7065 – [SIGNED-OFF] Bug 7065 - reserves table needs a primary key

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

0001-Bug-7065-reserves-table-needs-a-primary-key.patch (text/plain), 6.02 KB, created by Julian Maurice on 2012-06-12 13:43:29 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Julian Maurice

Created: 2012-06-12 13:43:29 UTC

Size: 6.02 KB

patch

obsolete

>From 627fbfdbf8474f7373b3cff25778968170e98c79 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.
>
>Signed-off-by: MJ Ray <mjr@phonecoop.coop>
>Signed-off-by: Ian Walls <koha.sekjal@gmail.com>
>
>Updated DBrev to start with 3.09... instead of 3.08...
>Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
>---
> installer/data/mysql/kohastructure.sql |    4 ++
> installer/data/mysql/updatedatabase.pl |   63 ++++++++++++++++++++++++++++++++
> 2 files changed, 67 insertions(+)
>
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 54498c3..9618b5e 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -1448,6 +1448,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
>@@ -1466,6 +1467,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`),
>@@ -1644,6 +1646,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
>@@ -1662,6 +1665,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 9b02cb9..c2ed4d5 100755
>--- a/installer/data/mysql/updatedatabase.pl
>+++ b/installer/data/mysql/updatedatabase.pl
>@@ -5369,6 +5369,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 old_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
> 
>-- 
>1.7.10
>

Actions: View | Diff | Splinter Review

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