Bugzilla – Attachment 37108 Details for
Bug 13790
Add unique id issue_id to issues and old_issues tables
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 13790 - Add unique id issue_id to issues and old_issues tables
Bug-13790---Add-unique-id-issueid-to-issues-and-ol.patch (text/plain), 4.73 KB, created by
Kyle M Hall (khall)
on 2015-03-23 11:23:44 UTC
(
hide
)
Description:
Bug 13790 - Add unique id issue_id to issues and old_issues tables
Filename:
MIME Type:
Creator:
Kyle M Hall (khall)
Created:
2015-03-23 11:23:44 UTC
Size:
4.73 KB
patch
obsolete
>From bf8e98437d2843a48319fbcdcdf1eb470296ae8c Mon Sep 17 00:00:00 2001 >From: Kyle M Hall <kyle@bywatersolutions.com> >Date: Wed, 4 Mar 2015 01:16:24 -0800 >Subject: [PATCH] Bug 13790 - Add unique id issue_id to issues and old_issues tables > >Test Plan: >1) Use a database with existing issues and old issues >2) Apply this patch >3) Run updatedatabase >4) Inspect the database > a) old_issues should now have a column issue_id starting with 1 > b) issues should now have a column issue_id starting with the number > of rows in the old_issues table plus one >5) Perform a checkout, note it is written to the database >6) Check in the checked out item, note it is moved to the old_issues table > >Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> >--- > installer/data/mysql/kohastructure.sql | 4 ++++ > installer/data/mysql/updatedatabase.pl | 28 ++++++++++++++++++++++++++++ > 2 files changed, 32 insertions(+), 0 deletions(-) > >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index 047b22a..7904562 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -1140,6 +1140,7 @@ CREATE TABLE `import_items` ( > > DROP TABLE IF EXISTS `issues`; > CREATE TABLE `issues` ( -- information related to check outs or issues >+ `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table > `borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to > `itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out > `date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss) >@@ -1153,6 +1154,7 @@ CREATE TABLE `issues` ( -- information related to check outs or issues > `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched > `issuedate` datetime default NULL, -- date the item was checked out or issued > `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag >+ PRIMARY KEY (`issue_id`), > KEY `issuesborridx` (`borrowernumber`), > KEY `itemnumber_idx` (`itemnumber`), > KEY `branchcode_idx` (`branchcode`), >@@ -1614,6 +1616,7 @@ CREATE TABLE `oai_sets_biblios` ( > > DROP TABLE IF EXISTS `old_issues`; > CREATE TABLE `old_issues` ( -- lists items that were checked out and have been returned >+ `issue_id` int(11) NOT NULL, -- primary key for issues table > `borrowernumber` int(11) default NULL, -- foreign key, linking this to the borrowers table for the patron this item was checked out to > `itemnumber` int(11) default NULL, -- foreign key, linking this to the items table for the item that was checked out > `date_due` datetime default NULL, -- date the item is due (yyyy-mm-dd) >@@ -1627,6 +1630,7 @@ CREATE TABLE `old_issues` ( -- lists items that were checked out and have been r > `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched > `issuedate` datetime default NULL, -- date the item was checked out or issued > `onsite_checkout` int(1) NOT NULL default 0, -- in house use flag >+ PRIMARY KEY (`issue_id`), > KEY `old_issuesborridx` (`borrowernumber`), > KEY `old_issuesitemidx` (`itemnumber`), > KEY `branchcode_idx` (`branchcode`), >diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl >index aa5bbbf..f85f646 100755 >--- a/installer/data/mysql/updatedatabase.pl >+++ b/installer/data/mysql/updatedatabase.pl >@@ -37,6 +37,7 @@ use Getopt::Long; > use C4::Context; > use C4::Installer; > use C4::Dates; >+use Koha::Database; > > use MARC::Record; > use MARC::File::XML ( BinaryEncoding => 'utf8' ); >@@ -9867,6 +9868,33 @@ if(CheckVersion($DBversion)) { > SetVersion($DBversion); > } > >+$DBversion = "XXX"; >+if(CheckVersion($DBversion)) { >+ $dbh->do(q{ >+ ALTER TABLE old_issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST >+ }); >+ >+ $dbh->do(q{ >+ ALTER TABLE issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST >+ }); >+ >+ $dbh->do(q{ >+ UPDATE issues SET issue_id = issue_id + ( SELECT COUNT(*) FROM old_issues ) ORDER BY issue_id DESC >+ }); >+ >+ my $schema = Koha::Database->new()->schema(); >+ my $max_issue_id = $schema->resultset('Issue')->get_column('issue_id')->max(); >+ $max_issue_id ||= $schema->resultset('OldIssue')->get_column('issue_id')->max(); >+ $max_issue_id ||= 0; >+ $max_issue_id++; >+ $dbh->do(qq{ >+ ALTER TABLE issues AUTO_INCREMENT = $max_issue_id} >+ ); >+ >+ print "Upgrade to $DBversion done (Bug 13790 - Add unique id issue_id to issues and oldissues 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 13790
:
36441
|
36442
|
37108
|
37109
|
37709
|
37710
|
38073
|
38081
|
38082
|
38155
|
38156
|
38157
|
38158
|
38182
|
38184
|
38349
|
38350
|
38351
|
38352
|
38353
|
38354
|
38429