Bugzilla – Attachment 37709 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]
[SIGNED OFF] Bug 13790 - Add unique id issue_id to issues and old_issues tables
SIGNED-OFF-Bug-13790---Add-unique-id-issueid-to-is.patch (text/plain), 5.42 KB, created by
Nick Clemens (kidclamp)
on 2015-04-12 20:44:59 UTC
(
hide
)
Description:
[SIGNED OFF] Bug 13790 - Add unique id issue_id to issues and old_issues tables
Filename:
MIME Type:
Creator:
Nick Clemens (kidclamp)
Created:
2015-04-12 20:44:59 UTC
Size:
5.42 KB
patch
obsolete
>From 09f9124d61b0a466f483b4fff0f1f8b0f2ff1f69 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] [SIGNED OFF] 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> >Signed-off-by: Nick Clemens <nick@quecheelibrary.org> >--- > installer/data/mysql/kohastructure.sql | 4 ++++ > installer/data/mysql/updatedatabase.pl | 32 +++++++++++++++++++++++++++++++- > 2 files changed, 35 insertions(+), 1 deletion(-) > >diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql >index 6ddba43..00b6288 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`), >@@ -1616,6 +1618,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) >@@ -1629,6 +1632,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 0592a94..4ba8bc9 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' ); >@@ -3809,8 +3810,8 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > print "Upgrade to $DBversion done (3.2.0 general release)\n"; > SetVersion ($DBversion); > } >- > # This is the point where 3.2.x and master diverged, we can use $original_version to make sure we don't >+ > # apply updates that have already been done > > $DBversion = "3.03.00.001"; >@@ -9909,6 +9910,7 @@ if(CheckVersion($DBversion)) { > SetVersion($DBversion); > } > >+ > $DBversion = '3.19.00.017'; > if (C4::Context->preference("Version") < TransformToNum($DBversion)) { > # First create the column >@@ -9985,6 +9987,34 @@ while ( my $file = readdir $dirh ) { > my $rv = $installer->load_sql( $update_dir . $file ) ? 0 : 1; > } > >+$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.9.1
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