From ec91f6acd2bed52dae77730b752837deebf41694 Mon Sep 17 00:00:00 2001 From: Aleisha Amohia Date: Thu, 26 Oct 2017 21:22:31 +0000 Subject: [PATCH] Bug 19532: Some DB fixes Removing the old_recalls table, as suggested in Comment 5 Renaming 'found' column to 'status' Adding 'old' column --- .../bug_19532_-_add_old_recalls_table.sql | 27 ---------------------- .../atomicupdate/bug_19532_-_add_recalls_table.sql | 8 ++++--- 2 files changed, 5 insertions(+), 30 deletions(-) delete mode 100644 installer/data/mysql/atomicupdate/bug_19532_-_add_old_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 deleted file mode 100644 index f634a93..0000000 --- a/installer/data/mysql/atomicupdate/bug_19532_-_add_old_recalls_table.sql +++ /dev/null @@ -1,27 +0,0 @@ -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 index c760848..a5c575a 100644 --- a/installer/data/mysql/atomicupdate/bug_19532_-_add_recalls_table.sql +++ b/installer/data/mysql/atomicupdate/bug_19532_-_add_recalls_table.sql @@ -1,3 +1,4 @@ +DROP TABLE IF EXISTS `old_recalls`; -- from a previous patch DROP TABLE IF EXISTS `recalls`; CREATE TABLE `recalls` ( -- information related to recalls in Koha `recall_id` int(11) NOT NULL auto_increment, -- primary key @@ -8,14 +9,15 @@ CREATE TABLE `recalls` ( -- information related to recalls in Koha `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 + `status` 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, C=cancelled `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 + `old` int(1) DEFAULT NULL, -- flag if the recall is old and no longer active, i.e. expired or cancelled PRIMARY KEY (`recall_id`), - KEY `priorityfoundidx` (priority,found), + KEY `priorityfoundidx` (priority,status), KEY `borrowernumber` (`borrowernumber`), KEY `biblionumber` (`biblionumber`), KEY `itemnumber` (`itemnumber`), @@ -26,4 +28,4 @@ CREATE TABLE `recalls` ( -- information related to recalls in Koha 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; +) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci; -- 2.1.4