From 6dfd1d5ba05cf1c001c3412af87f7aee8ea7bdac Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Wed, 4 Mar 2015 01:16:24 -0800 Subject: [PATCH] Bug 13790 - Add unique id issue_id to issues and old_issues tables Content-Type: text/plain; charset=utf-8 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 Signed-off-by: Nick Clemens Signed-off-by: Marcel de Rooy --- 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 a74d81a..0ad257d 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) @@ -1152,6 +1153,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) @@ -1626,6 +1629,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 ca27cde..356e379 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"; @@ -9916,6 +9917,7 @@ if(CheckVersion($DBversion)) { SetVersion($DBversion); } + $DBversion = '3.19.00.017'; if (C4::Context->preference("Version") < TransformToNum($DBversion)) { # First create the column @@ -10048,6 +10050,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.7.10.4