From 908d2f189b617a43c9d2fcd812722ebe555f403c Mon Sep 17 00:00:00 2001
From: Aleisha Amohia <aleishaamohia@hotmail.com>
Date: Wed, 25 Oct 2017 23:42:33 +0000
Subject: [PATCH] Bug 19532: Database updates

Adding the recalls table and the old_recalls table.

Sponsored-by: Catalyst IT
---
 .../bug_19532_-_add_old_recalls_table.sql          | 27 +++++++++
 .../atomicupdate/bug_19532_-_add_recalls_table.sql | 29 ++++++++++
 installer/data/mysql/kohastructure.sql             | 66 ++++++++++++++++++++++
 3 files changed, 122 insertions(+)
 create mode 100644 installer/data/mysql/atomicupdate/bug_19532_-_add_old_recalls_table.sql
 create mode 100644 installer/data/mysql/atomicupdate/bug_19532_-_add_recalls_table.sql

diff --git a/installer/data/mysql/atomicupdate/bug_19532_-_add_old_recalls_table.sql b/installer/data/mysql/atomicupdate/bug_19532_-_add_old_recalls_table.sql
new file mode 100644
index 0000000..f634a93
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_19532_-_add_old_recalls_table.sql
@@ -0,0 +1,27 @@
+DROP TABLE IF EXISTS `old_recalls`;
+CREATE TABLE `old_recalls` ( -- information related to old recalls in Koha for statistics purposes
+    `recall_id` int(11) NOT NULL auto_increment, -- primary key
+    `borrowernumber` int(11) NULL default 0, -- foreign key from the borrowers table defining which patron requested a recall
+    `recalldate` date default NULL, -- the date the recall request was placed
+    `biblionumber` int(11) NULL default 0, -- foreign key from the biblio table defining which bib record this request is for
+    `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick up their recall from
+    `cancellationdate` date default NULL, -- the date this recall was cancelled
+    `recallnotes` mediumtext, -- notes related to this recall
+    `priority` smallint(6) default NULL, -- where in the queue the patron sits
+    `found` varchar(1) default NULL, -- a one letter code defining what the status is of the recall is after it has been requested. R=requested, O=overdue, W=awaiting pickup, E=expired
+    `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this recall was last updated
+    `itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the recall request was placed on
+    `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
+    `expirationdate` DATE DEFAULT NULL, -- the date the recall expires
+    `itemtype` VARCHAR(10) NULL DEFAULT NULL, -- the optional itemtype of the item the patron is requesting
+    PRIMARY KEY (`recall_id`),
+    KEY `old_recalls_borrowernumber` (`borrowernumber`),
+    KEY `old_recalls_biblionumber` (`biblionumber`),
+    KEY `old_recalls_itemnumber` (`itemnumber`),
+    KEY `old_recalls_branchcode` (`branchcode`),
+    KEY `old_recalls_itemtype` (`itemtype`),
+    CONSTRAINT `old_recalls_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
+    CONSTRAINT `old_recalls_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL,
+    CONSTRAINT `old_recalls_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,
+    CONSTRAINT `old_recalls_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
diff --git a/installer/data/mysql/atomicupdate/bug_19532_-_add_recalls_table.sql b/installer/data/mysql/atomicupdate/bug_19532_-_add_recalls_table.sql
new file mode 100644
index 0000000..c760848
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_19532_-_add_recalls_table.sql
@@ -0,0 +1,29 @@
+DROP TABLE IF EXISTS `recalls`;
+CREATE TABLE `recalls` ( -- information related to recalls in Koha
+    `recall_id` int(11) NOT NULL auto_increment, -- primary key
+    `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron requested a recall
+    `recalldate` date default NULL, -- the date the recall request was placed
+    `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this request is for
+    `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick up their recall from
+    `cancellationdate` date default NULL, -- the date this recall was cancelled
+    `recallnotes` mediumtext, -- notes related to this recall
+    `priority` smallint(6) default NULL, -- where in the queue the patron sits
+    `found` varchar(1) default NULL, -- a one letter code defining what the status is of the recall is after it has been requested. R=requested, O=overdue, W=awaiting pickup, E=expired
+    `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this recall was last updated
+    `itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the recall request was placed on
+    `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
+    `expirationdate` DATE DEFAULT NULL, -- the date the recall expires
+    `itemtype` VARCHAR(10) NULL DEFAULT NULL, -- the optional itemtype of the item the patron is requesting
+    PRIMARY KEY (`recall_id`),
+    KEY `priorityfoundidx` (priority,found),
+    KEY `borrowernumber` (`borrowernumber`),
+    KEY `biblionumber` (`biblionumber`),
+    KEY `itemnumber` (`itemnumber`),
+    KEY `branchcode` (`branchcode`),
+    KEY `itemtype` (`itemtype`),
+    CONSTRAINT `recalls_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+    CONSTRAINT `recalls_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+    CONSTRAINT `recalls_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+    CONSTRAINT `recalls_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
+    CONSTRAINT `recalls_ibfk_5` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 379c100..78e4d19 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1890,6 +1890,72 @@ CREATE TABLE `patronimage` ( -- information related to patron images
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 --
+-- Table structure for table `recalls`
+--
+
+DROP TABLE IF EXISTS `recalls`;
+CREATE TABLE `recalls` ( -- information related to recalls in Koha
+    `recall_id` int(11) NOT NULL auto_increment, -- primary key
+    `borrowernumber` int(11) NOT NULL default 0, -- foreign key from the borrowers table defining which patron requested a recall
+    `recalldate` date default NULL, -- the date the recall request was placed
+    `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this request is for
+    `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick up their recall from
+    `cancellationdate` date default NULL, -- the date this recall was cancelled
+    `recallnotes` mediumtext, -- notes related to this recall
+    `priority` smallint(6) default NULL, -- where in the queue the patron sits
+    `found` varchar(1) default NULL, -- a one letter code defining what the status is of the recall is after it has been requested. R=requested, O=overdue, W=awaiting pickup, E=expired
+    `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this recall was last updated
+    `itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the recall request was placed on
+    `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
+    `expirationdate` DATE DEFAULT NULL, -- the date the recall expires
+    `itemtype` VARCHAR(10) NULL DEFAULT NULL, -- the optional itemtype of the item the patron is requesting
+    PRIMARY KEY (`recall_id`),
+    KEY `priorityfoundidx` (priority,found),
+    KEY `borrowernumber` (`borrowernumber`),
+    KEY `biblionumber` (`biblionumber`),
+    KEY `itemnumber` (`itemnumber`),
+    KEY `branchcode` (`branchcode`),
+    KEY `itemtype` (`itemtype`),
+    CONSTRAINT `recalls_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+    CONSTRAINT `recalls_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+    CONSTRAINT `recalls_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
+    CONSTRAINT `recalls_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
+    CONSTRAINT `recalls_ibfk_5` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
+-- Table structure for table `old_recalls`
+--
+
+DROP TABLE IF EXISTS `old_recalls`;
+CREATE TABLE `old_recalls` ( -- information related to recalls in Koha
+    `recall_id` int(11) NOT NULL auto_increment, -- primary key
+    `borrowernumber` int(11) NULL default 0, -- foreign key from the borrowers table defining which patron requested a recall
+    `recalldate` date default NULL, -- the date the recall request was placed
+    `biblionumber` int(11) NULL default 0, -- foreign key from the biblio table defining which bib record this request is for
+    `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick up their recall from
+    `cancellationdate` date default NULL, -- the date this recall was cancelled
+    `recallnotes` mediumtext, -- notes related to this recall
+    `priority` smallint(6) default NULL, -- where in the queue the patron sits
+    `found` varchar(1) default NULL, -- a one letter code defining what the status is of the recall is after it has been requested. R=requested, O=overdue, W=awaiting pickup, E=expired
+    `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this recall was last updated
+    `itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the recall request was placed on
+    `waitingdate` date default NULL, -- the date the item was marked as waiting for the patron at the library
+    `expirationdate` DATE DEFAULT NULL, -- the date the recall expires
+    `itemtype` VARCHAR(10) NULL DEFAULT NULL, -- the optional itemtype of the item the patron is requesting
+    PRIMARY KEY (`recall_id`),
+    KEY `old_recalls_borrowernumber` (`borrowernumber`),
+    KEY `old_recalls_biblionumber` (`biblionumber`),
+    KEY `old_recalls_itemnumber` (`itemnumber`),
+    KEY `old_recalls_branchcode` (`branchcode`),
+    KEY `old_recalls_itemtype` (`itemtype`),
+    CONSTRAINT `old_recalls_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL,
+    CONSTRAINT `old_recalls_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE SET NULL,
+    CONSTRAINT `old_recalls_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,
+    CONSTRAINT `old_recalls_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE SET NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+--
 -- Table structure for table `reserves`
 --
 
-- 
2.1.4