From d973977b2e8bc575f87d8596c196fcd948f86a6c Mon Sep 17 00:00:00 2001 From: Aleisha Amohia 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 Signed-off-by: Nick Clemens --- .../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 62c0dce..f566cbc 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1870,6 +1870,72 @@ CREATE TABLE `patronimage` ( -- information related to patron images ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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