Bugzilla – Attachment 9388 Details for
Bug 7065
reserves table needs a primary key
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 7065 - reserves table needs a primary key
Bug-7065---reserves-table-needs-a-primary-key.patch (text/plain), 9.53 KB, created by
Kyle M Hall
on 2012-05-02 14:05:30 UTC
(
hide
)
Description:
Bug 7065 - reserves table needs a primary key
Filename:
MIME Type:
Creator:
Kyle M Hall
Created:
2012-05-02 14:05:30 UTC
Size:
9.53 KB
patch
obsolete
>From af84c7432357b1d5d2db09d75de92378907c0e84 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. >--- > installer/data/mysql/kohastructure.sql | 4 + > installer/data/mysql/updatedatabase.pl | 111 ++++++++++++++++++++++++++++++++ > 2 files changed, 115 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..025bf4a 100755 >--- a/installer/data/mysql/updatedatabase.pl >+++ b/installer/data/mysql/updatedatabase.pl >@@ -4930,6 +4930,7 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { > SetVersion($DBversion); > } > >+<<<<<<< HEAD > $DBversion = "3.07.00.028"; > if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { > $dbh->do(qq{ >@@ -5212,6 +5213,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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 7065
:
7789
|
8036
|
8300
|
8304
|
8305
|
9388
|
9389
|
9403
|
9404
|
9413
|
9740
|
10140
|
10250