Attachment 8305 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), 9.71 KB, created by Kyle M Hall on 2012-03-19 16:01:51 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall

Created: 2012-03-19 16:01:51 UTC

Size: 9.71 KB

patch

obsolete

>From a522166e846dff92f43fab3b3a8edb760d04b268 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 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..2dda11f 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 .= "AND reservenumber = 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 ( $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 .= "AND reservenumber = 0 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

Actions: View | Diff | Splinter Review

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