Bug 22899: Database update

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>

diff --git a/installer/data/mysql/atomicupdate/bug_22899_add_items_constraint_to_tmpholdsqueue.perl b/installer/data/mysql/atomicupdate/bug_22899_add_items_constraint_to_tmpholdsqueue.perl
new file mode 100644 (file)
index 0000000..1e14394
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_22899_add_items_constraint_to_tmpholdsqueue.perl
@@ -0,0 +1,13 @@
+$DBversion = 'XXX'; # will be replaced by the RM
+if( CheckVersion( $DBversion ) ) {
+    # you can use $dbh here like:
+    unless ( foreign_key_exists( 'tmp_holdsqueue', 'tmp_holdsqueue_ibfk_1' ) ) {
+        $dbh->do(q{
+            ALTER TABLE tmp_holdsqueue
+            ADD CONSTRAINT `tmp_holdsqueue_ibfk_1` FOREIGN KEY (`itemnumber`)
+            REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
+        });
+    }
+    SetVersion( $DBversion );
+    print "Upgrade to $DBversion done (Bug XXXXX - Add items constraint to tmp_holdsqueue)\n";
+}

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index d5864d1..da3ff83 100644 (file)
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2458,7 +2458,9 @@ CREATE TABLE `tmp_holdsqueue` (
   `holdingbranch` varchar(10) default NULL,
   `pickbranch` varchar(10) default NULL,
   `notes` MEDIUMTEXT,
-  `item_level_request` tinyint(4) NOT NULL default 0
+  `item_level_request` tinyint(4) NOT NULL default 0,
+  CONSTRAINT `tmp_holdsqueue_ibfk_1` FOREIGN KEY (`itemnumber`)
+    REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 --