Bugzilla – Attachment 7789 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), 5.63 KB, created by
Kyle M Hall
on 2012-02-21 17:37:11 UTC
(
hide
)
Description:
Bug 7065 - reserves table needs a primary key
Filename:
MIME Type:
Creator:
Kyle M Hall
Created:
2012-02-21 17:37:11 UTC
Size:
5.63 KB
patch
obsolete
>From 42237342c54fa67ec1797a6f6ccc37d3cfa20a29 Mon Sep 17 00:00:00 2001 >From: Kyle M Hall <kyle@bywatersolutions.com> >Date: Tue, 21 Feb 2012 12:35:42 -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 | 50 ++++++++++++++++++++++++++++++++ > 2 files changed, 54 insertions(+), 0 deletions(-) > >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index 67149c9d..4ecf7dd 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -1384,6 +1384,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, > `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 >@@ -1400,6 +1401,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`), >@@ -1559,6 +1561,7 @@ CREATE TABLE `reserveconstraints` ( > > DROP TABLE IF EXISTS `reserves`; > CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha >+ `reservenumber` int(11) NOT NULL AUTO_INCREMENT, > `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 >@@ -1575,6 +1578,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 d2d9d97..f0edd93 100755 >--- a/installer/data/mysql/updatedatabase.pl >+++ b/installer/data/mysql/updatedatabase.pl >@@ -4712,6 +4712,56 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { > SetVersion($DBversion); > } > >+$DBversion = "3.07.00.XXX"; >+if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { >+ $dbh->do("ALTER TABLE reserves ADD reservenumber INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST"); >+ $dbh->do("ALTER TABLE old_reserves ADD reservenumber INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST"); >+ >+ my ( $sth, $row ); >+ >+ $sth = $dbh->prepare("SELECT MAX( reservenumber ) AS last_old_reservenumber FROM old_reserves"); >+ $sth->execute(); >+ $row = $sth->fetchrow_hashref(); >+ my $last_old_reservenumber = $row->{'last_old_reservenumber'}; >+ $sth->finish(); >+ >+ $sth = $dbh->prepare("SELECT MAX( reservenumber ) AS last_reservenumber FROM reserves"); >+ $sth->execute(); >+ $row = $sth->fetchrow_hashref(); >+ my $last_bad_reservenumber = $row->{'last_reservenumber'}; >+ my $reserves_count = $last_bad_reservenumber; >+ $sth->finish(); >+ >+ my $query = "UPDATE reserves SET reservenumber = ? WHERE reservenumber = ?"; >+ my $update_sth = $dbh->prepare( $query ); >+ >+ ## First, change all reservenumbers to new ones above the highest that was intially added. >+ ## That way, we can renumber them without the risk of using a duplicate reservenumber >+ my $new_reservenumber = $last_bad_reservenumber + 1; >+ $sth = $dbh->prepare("SELECT reservenumber FROM reserves"); >+ $sth->execute(); >+ while ( $row = $sth->fetchrow_hashref() ) { >+ $update_sth->execute( $new_reservenumber, $row->{'reservenumber'} ); >+ $new_reservenumber++; >+ } >+ >+ ## First, change all reservenumbers to new ones above the highest that was intially added. >+ ## That way, we can renumber them without the risk of using a duplicate reservenumber >+ $new_reservenumber = $last_old_reservenumber + 1; >+ $sth = $dbh->prepare("SELECT reservenumber FROM reserves"); >+ $sth->execute(); >+ while ( $row = $sth->fetchrow_hashref() ) { >+ $update_sth->execute( $new_reservenumber, $row->{'reservenumber'} ); >+ $new_reservenumber++; >+ } >+ >+ ## Drop the auto_increment from the old_reserves table. >+ $dbh->do("ALTER TABLE old_reserves CHANGE reservenumber reservenumber INT( 11 ) NOT NULL"); >+ >+ print "Upgrade to $DBversion done (Added reservenumber to reserves and old_reserves tables)\n"; >+ SetVersion($DBversion); >+} >+ > =head1 FUNCTIONS > > =head2 DropAllForeignKeys($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