From 92f765fa024605ea203ef5279a64fc29e1ffaa71 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
---
 installer/data/mysql/kohastructure.sql |    4 ++++
 installer/data/mysql/updatedatabase.pl |   18 ++++++++++++++++++
 2 files changed, 22 insertions(+)

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 08ef9ee..2685749 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 AUTO_INCREMENT, -- 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 0ed56c6..bb4b2f7 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -9793,6 +9793,24 @@ if(CheckVersion($DBversion)) {
     SetVersion($DBversion);
 }
 
+$DBversion = "3.19.00.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
+    });
+
+    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