From 543664f218ea489390723e96debe8d37d99e7481 Mon Sep 17 00:00:00 2001
From: Kyle M Hall <kyle@bywatersolutions.com>
Date: Fri, 9 May 2014 11:00:10 -0400
Subject: [PATCH] Bug 8836 - Resurrect Rotating Collections - Followup 5 - Link collections.colBranchcode to branches.branchcode

---
 installer/data/mysql/kohastructure.sql |    8 +++++++-
 installer/data/mysql/updatedatabase.pl |   16 ++++++++++++++++
 2 files changed, 23 insertions(+), 1 deletions(-)

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 92ab713..a1cb42d 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -479,11 +479,17 @@ CREATE TABLE collections (
   colId integer(11) NOT NULL auto_increment,
   colTitle varchar(100) NOT NULL DEFAULT '',
   colDesc text NOT NULL,
-  colBranchcode varchar(4) DEFAULT NULL comment 'branchcode for branch where item should be held.',
+  colBranchcode varchar(10) DEFAULT NULL comment 'branchcode for branch where item should be held.',
   PRIMARY KEY (colId)
 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
 
 --
+-- Constraints for table `collections`
+--
+ALTER TABLE `collections`
+  ADD CONSTRAINT `collections_ibfk_1` FOREIGN KEY (`colBranchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE;
+
+--
 -- Table: collections_tracking
 --
 DROP TABLE IF EXISTS collections_tracking;
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 3b1718c..cb57d32 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -8465,6 +8465,22 @@ if ( CheckVersion($DBversion) ) {
     SetVersion($DBversion);
 }
 
+$DBversion = "3.17.00.XXX";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do(q{
+        ALTER TABLE collections CHANGE colBranchcode colBranchcode VARCHAR( 10 ) NULL DEFAULT NULL
+    });
+    $dbh->do(q{
+        ALTER TABLE collections ADD INDEX ( colBranchcode )
+    });
+    $dbh->do(q{
+        ALTER TABLE collections
+            ADD CONSTRAINT collections_ibfk_1 FOREIGN KEY (colBranchcode) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE
+    });
+    print "Upgrade to $DBversion done (Bug 8836 - Resurrect Rotating Collections)\n";
+    SetVersion($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
-- 
1.7.2.5