[Koha-patches] [PATCH] bug 2522 [1/3]: DB rev 3.1/001 - hold request targetting

[Galen Charlton]

For bug 2522, add a new table called hold_fill_targets to
implement hold request targeting.  This table
has the following columns:

  borrowernumber     - identify request
  biblionumber       - ditto
  itemnumber         - item targeted to fill request
  source_branchcode  - current location of item
  item_level_request - if 1, request is item-level

For bug 2331, adds a column called item_level_request
to tmp_holdsqueue.

Note: both of the item_level_request columns can
go away once reserves is modified so that
it is clear whether a request is item-level or
bib-level.
---
 installer/data/mysql/kohastructure.sql |   25 ++++++++++++++++++++++++-
 installer/data/mysql/updatedatabase.pl |   32 +++++++++++++++++++++++++++++++-
 kohaversion.pl                         |    2 +-
 3 files changed, 56 insertions(+), 3 deletions(-)

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 53da4fe..09aaf97 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -939,6 +939,28 @@ CREATE TABLE `ethnicity` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
+-- Table structure for table `hold_fill_targets`
+--
+
+CREATE TABLE hold_fill_targets (
+  `borrowernumber` int(11) NOT NULL,
+  `biblionumber` int(11) NOT NULL,
+  `itemnumber` int(11) NOT NULL,
+  `source_branchcode`  varchar(10) default NULL,
+  `item_level_request` tinyint(4) NOT NULL default 0,
+  PRIMARY KEY `itemnumber` (`itemnumber`),
+  KEY `bib_branch` (`biblionumber`, `source_branchcode`),
+  CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`) 
+    REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`) 
+    REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`) 
+    REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`) 
+    REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
 -- Table structure for table `import_batches`
 --
 
@@ -2172,7 +2194,8 @@ CREATE TABLE `tmp_holdsqueue` (
   `itemcallnumber` varchar(30) default NULL,
   `holdingbranch` varchar(10) default NULL,
   `pickbranch` varchar(10) default NULL,
-  `notes` text
+  `notes` text,
+  `item_level_request` tinyint(4) NOT NULL default 0
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 --
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 042a80b..1dd86a1 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -15,7 +15,7 @@
 # NOTE: Please keep the version in kohaversion.pl up-to-date!
 
 use strict;
-# use warnings;
+use warnings;
 
 # CPAN modules
 use DBI;
@@ -1967,6 +1967,36 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     SetVersion ($DBversion);
 }
 
+$DBversion = '3.01.00.001';
+if ( C4::Context->preference('Version') < TransformToNum($DBversion) ) {
+    $dbh->do("
+        CREATE TABLE hold_fill_targets (
+            `borrowernumber` int(11) NOT NULL,
+            `biblionumber` int(11) NOT NULL,
+            `itemnumber` int(11) NOT NULL,
+            `source_branchcode`  varchar(10) default NULL,
+            `item_level_request` tinyint(4) NOT NULL default 0,
+            PRIMARY KEY `itemnumber` (`itemnumber`),
+            KEY `bib_branch` (`biblionumber`, `source_branchcode`),
+            CONSTRAINT `hold_fill_targets_ibfk_1` FOREIGN KEY (`borrowernumber`) 
+                REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+            CONSTRAINT `hold_fill_targets_ibfk_2` FOREIGN KEY (`biblionumber`) 
+                REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+            CONSTRAINT `hold_fill_targets_ibfk_3` FOREIGN KEY (`itemnumber`) 
+                REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+            CONSTRAINT `hold_fill_targets_ibfk_4` FOREIGN KEY (`source_branchcode`) 
+                REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE
+        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
+    ");
+    $dbh->do("
+        ALTER TABLE tmp_holdsqueue
+            ADD item_level_request tinyint(4) NOT NULL default 0
+    ");
+
+    print "Upgrade to $DBversion done (add hold_fill_targets table and a column to tmp_holdsqueue)";
+    SetVersion($DBversion);
+}
+
 =item DropAllForeignKeys($table)
 
   Drop all foreign keys of the table $table
diff --git a/kohaversion.pl b/kohaversion.pl
index a0404b2..9a9221f 100644
--- a/kohaversion.pl
+++ b/kohaversion.pl
@@ -10,7 +10,7 @@
 use strict;
 
 sub kohaversion {
-    our $VERSION = '3.01.00.000';
+    our $VERSION = '3.01.00.001';
     # version needs to be set this way
     # so that it can be picked up by Makefile.PL
     # during install
-- 
1.5.5.GIT