From 96f9e93243129c1df6d5373dfe6b210e343127e7 Mon Sep 17 00:00:00 2001 From: Tomas Cohen Arazi Date: Fri, 17 Apr 2020 17:50:49 -0300 Subject: [PATCH] Bug 25260: Add a 'holds' table Signed-off-by: Tomas Cohen Arazi --- Koha/Schema/Result/Hold.pm | 344 +++++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 206 +++++++++------ 2 files changed, 472 insertions(+), 78 deletions(-) create mode 100644 Koha/Schema/Result/Hold.pm diff --git a/Koha/Schema/Result/Hold.pm b/Koha/Schema/Result/Hold.pm new file mode 100644 index 0000000000..65ffe5989a --- /dev/null +++ b/Koha/Schema/Result/Hold.pm @@ -0,0 +1,344 @@ +use utf8; +package Koha::Schema::Result::Hold; + +# Created by DBIx::Class::Schema::Loader +# DO NOT MODIFY THE FIRST PART OF THIS FILE + +=head1 NAME + +Koha::Schema::Result::Hold + +=cut + +use strict; +use warnings; + +use base 'DBIx::Class::Core'; + +=head1 TABLE: C + +=cut + +__PACKAGE__->table("holds"); + +=head1 ACCESSORS + +=head2 id + + data_type: 'integer' + is_auto_increment: 1 + is_nullable: 0 + +=head2 patron_id + + data_type: 'integer' + default_value: 0 + is_foreign_key: 1 + is_nullable: 0 + +=head2 pickup_library_id + + data_type: 'varchar' + is_foreign_key: 1 + is_nullable: 1 + size: 10 + +=head2 placing_date + + data_type: 'date' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +=head2 biblio_id + + data_type: 'integer' + default_value: 0 + is_foreign_key: 1 + is_nullable: 0 + +=head2 item_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 1 + +=head2 item_type + + data_type: 'varchar' + is_foreign_key: 1 + is_nullable: 1 + size: 10 + +=head2 item_level + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +=head2 completion_date + + data_type: 'date' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +=head2 notes + + data_type: 'longtext' + is_nullable: 1 + +=head2 priority + + data_type: 'smallint' + default_value: 1 + is_nullable: 0 + +=head2 status + + data_type: 'enum' + default_value: 'placed' + extra: {list => ["placed","fulfilled","waiting","in_transit","cancelled"]} + is_nullable: 1 + +=head2 timestamp + + data_type: 'timestamp' + datetime_undef_if_invalid: 1 + default_value: current_timestamp + is_nullable: 0 + +=head2 waiting_date + + data_type: 'date' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +=head2 expiration_date + + data_type: 'date' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +=head2 lowest_priority + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +=head2 suspended + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +=head2 suspended_until_date + + data_type: 'datetime' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +=head2 completed + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +=cut + +__PACKAGE__->add_columns( + "id", + { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, + "patron_id", + { + data_type => "integer", + default_value => 0, + is_foreign_key => 1, + is_nullable => 0, + }, + "pickup_library_id", + { data_type => "varchar", is_foreign_key => 1, is_nullable => 1, size => 10 }, + "placing_date", + { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, + "biblio_id", + { + data_type => "integer", + default_value => 0, + is_foreign_key => 1, + is_nullable => 0, + }, + "item_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, + "item_type", + { data_type => "varchar", is_foreign_key => 1, is_nullable => 1, size => 10 }, + "item_level", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, + "completion_date", + { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, + "notes", + { data_type => "longtext", is_nullable => 1 }, + "priority", + { data_type => "smallint", default_value => 1, is_nullable => 0 }, + "status", + { + data_type => "enum", + default_value => "placed", + extra => { + list => ["placed", "fulfilled", "waiting", "in_transit", "cancelled"], + }, + is_nullable => 1, + }, + "timestamp", + { + data_type => "timestamp", + datetime_undef_if_invalid => 1, + default_value => \"current_timestamp", + is_nullable => 0, + }, + "waiting_date", + { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, + "expiration_date", + { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, + "lowest_priority", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, + "suspended", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, + "suspended_until_date", + { + data_type => "datetime", + datetime_undef_if_invalid => 1, + is_nullable => 1, + }, + "completed", + { data_type => "tinyint", default_value => 0, is_nullable => 0 }, +); + +=head1 PRIMARY KEY + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("id"); + +=head1 RELATIONS + +=head2 biblio + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "biblio", + "Koha::Schema::Result::Biblio", + { biblionumber => "biblio_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); + +=head2 club_holds_to_patron_holds + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "club_holds_to_patron_holds", + "Koha::Schema::Result::ClubHoldsToPatronHold", + { "foreign.hold_id" => "self.id" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + +=head2 item + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "item", + "Koha::Schema::Result::Item", + { itemnumber => "item_id" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "CASCADE", + on_update => "CASCADE", + }, +); + +=head2 item_type + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "item_type", + "Koha::Schema::Result::Itemtype", + { itemtype => "item_type" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "CASCADE", + on_update => "CASCADE", + }, +); + +=head2 patron + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "patron", + "Koha::Schema::Result::Borrower", + { borrowernumber => "patron_id" }, + { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" }, +); + +=head2 pickup_library + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "pickup_library", + "Koha::Schema::Result::Branch", + { branchcode => "pickup_library_id" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "CASCADE", + on_update => "CASCADE", + }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07046 @ 2020-04-22 19:21:03 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:rpFBfRe1swRgNQsdQPiTtg + +__PACKAGE__->add_columns( + '+completed' => { is_boolean => 1 }, + '+item_level' => { is_boolean => 1 }, + '+suspended' => { is_boolean => 1 }, + '+lowest_priority' => { is_boolean => 1 }, +); + +1; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 3f8d490fe6..5e8866a9b7 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1737,87 +1737,137 @@ CREATE TABLE `patronimage` ( -- information related to patron images CONSTRAINT `patronimage_fk1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- +-- Table structure for table `holds` +-- + +DROP TABLE IF EXISTS `holds`; +CREATE TABLE `holds` ( + -- information related to holds in Koha + `id` INT(11) NOT NULL AUTO_INCREMENT, + -- primary key + `patron_id` INT(11) DEFAULT NULL, + -- foreign key from the borrowers table defining which patron this hold is for + `pickup_library_id` VARCHAR(10) DEFAULT NULL, + -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at + `placing_date` DATE DEFAULT NULL, + -- the date the hold was placed + `biblio_id` INT(11) DEFAULT NULL, + -- foreign key from the biblio table defining which bib record this hold is on + `item_id` INT(11) DEFAULT NULL, + -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with + `item_type` VARCHAR(10) DEFAULT NULL, + -- If record level hold, the optional itemtype of the item the patron is requesting + `item_level` TINYINT(1) NOT NULL DEFAULT 0, + -- Is the hold was placed at item level + `completion_date` DATE DEFAULT NULL, + -- the date this hold was completed (fulfilled or cancelled) + `notes` LONGTEXT, + -- notes related to this hold + `priority` SMALLINT(6) NOT NULL DEFAULT 1, + -- where in the queue the patron sits + `status` ENUM('placed', 'fulfilled', 'waiting', 'in_transit', 'cancelled') DEFAULT 'placed' NOT NULL, + -- the status the hold is ('placed', 'fulfilled', 'waiting', 'in_transit', 'cancelled') + `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + -- the date and time this hold was last updated + `waiting_date` DATE DEFAULT NULL, + -- the date the item was marked as waiting for the patron at the library + `expiration_date` DATE DEFAULT NULL, + -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) + `lowest_priority` TINYINT(1) NOT NULL DEFAULT 0, + -- If it was assigned the lowest priority + `suspended` TINYINT(1) NOT NULL DEFAULT 0, + -- If it is suspended + `suspended_until_date` DATETIME NULL DEFAULT NULL, + -- Date until it is suspended + `completed` TINYINT(1) NOT NULL DEFAULT 0, + -- If it has been completed (i.e. either 'fulfilled' or 'cancelled') + PRIMARY KEY (`id`), + KEY `priority_status_idx` (`priority`, `status`), + KEY `patron_id` (`patron_id`), + KEY `biblio_id` (`biblio_id`), + KEY `item_id` (`item_id`), + KEY `pickup_library_id` (`pickup_library_id`), + KEY `item_type` (`item_type`), + CONSTRAINT `holds_ibfk_1` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_2` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_3` FOREIGN KEY (`item_id`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_4` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_5` FOREIGN KEY (`item_type`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; + -- -- Table structure for table `reserves` -- -DROP TABLE IF EXISTS `reserves`; -CREATE TABLE `reserves` ( -- information related to holds/reserves in Koha - `reserve_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 this hold is for - `reservedate` date default NULL, -- the date the hold was placed - `biblionumber` int(11) NOT NULL default 0, -- foreign key from the biblio table defining which bib record this hold is on - `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at - `notificationdate` date default NULL, -- currently unused - `reminderdate` date default NULL, -- currently unused - `cancellationdate` date default NULL, -- the date this hold was cancelled - `reservenotes` LONGTEXT, -- notes related to this hold - `priority` smallint(6) NOT NULL DEFAULT 1, -- where in the queue the patron sits - `found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated - `itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with - `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 hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) - `lowestPriority` tinyint(1) NOT NULL DEFAULT 0, - `suspend` tinyint(1) NOT NULL DEFAULT 0, - `suspend_until` DATETIME NULL DEFAULT NULL, - `itemtype` VARCHAR(10) NULL DEFAULT NULL, -- If record level hold, the optional itemtype of the item the patron is requesting - `item_level_hold` tinyint(1) NOT NULL DEFAULT 0, -- Is the hpld placed at item level - PRIMARY KEY (`reserve_id`), - KEY priorityfoundidx (priority,found), - KEY `borrowernumber` (`borrowernumber`), - KEY `biblionumber` (`biblionumber`), - KEY `itemnumber` (`itemnumber`), - KEY `branchcode` (`branchcode`), - KEY `itemtype` (`itemtype`), - CONSTRAINT `reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `reserves_ibfk_4` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `reserves_ibfk_5` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; - --- --- Table structure for table `old_reserves` --- - -DROP TABLE IF EXISTS `old_reserves`; -CREATE TABLE `old_reserves` ( -- this table holds all holds/reserves that have been completed (either filled or cancelled) - `reserve_id` int(11) NOT NULL, -- primary key - `borrowernumber` int(11) default NULL, -- foreign key from the borrowers table defining which patron this hold is for - `reservedate` date default NULL, -- the date the hold was places - `biblionumber` int(11) default NULL, -- foreign key from the biblio table defining which bib record this hold is on - `branchcode` varchar(10) default NULL, -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at - `notificationdate` date default NULL, -- currently unused - `reminderdate` date default NULL, -- currently unused - `cancellationdate` date default NULL, -- the date this hold was cancelled - `reservenotes` LONGTEXT, -- notes related to this hold - `priority` smallint(6) NOT NULL DEFAULT 1, -- where in the queue the patron sits - `found` varchar(1) default NULL, -- a one letter code defining what the status is of the hold is after it has been confirmed - `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this hold was last updated - `itemnumber` int(11) default NULL, -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with - `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 hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) - `lowestPriority` tinyint(1) NOT NULL DEFAULT 0, -- has this hold been pinned to the lowest priority in the holds queue (1 for yes, 0 for no) - `suspend` tinyint(1) NOT NULL DEFAULT 0, -- in this hold suspended (1 for yes, 0 for no) - `suspend_until` DATETIME NULL DEFAULT NULL, -- the date this hold is suspended until (NULL for infinitely) - `itemtype` VARCHAR(10) NULL DEFAULT NULL, -- If record level hold, the optional itemtype of the item the patron is requesting - `item_level_hold` tinyint(1) NOT NULL DEFAULT 0, -- Is the hpld placed at item level - PRIMARY KEY (`reserve_id`), - KEY `old_reserves_borrowernumber` (`borrowernumber`), - KEY `old_reserves_biblionumber` (`biblionumber`), - KEY `old_reserves_itemnumber` (`itemnumber`), - KEY `old_reserves_branchcode` (`branchcode`), - KEY `old_reserves_itemtype` (`itemtype`), - CONSTRAINT `old_reserves_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) - ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_reserves_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) - ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) - ON DELETE SET NULL ON UPDATE SET NULL, - CONSTRAINT `old_reserves_ibfk_4` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) - ON DELETE SET NULL ON UPDATE SET NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +CREATE VIEW `reserves` +AS + SELECT + `id` AS `reserve_id`, + `patron_id` AS `borrowernumber`, + `placing_date` AS `reservedate`, + `biblio_id` AS `biblionumber`, + `pickup_library_id` AS `branchcode`, + NULL AS `cancellationdate`, + `notes` AS `reservenotes`, + `priority`, + CASE + when `status` = 'waiting' then + 'W' + when `status` = 'in_transit' then + 'T' + else + NULL + END AS `found`, + `timestamp`, + `item_id` AS `itemnumber`, + `waiting_date` AS `waitingdate`, + `expiration_date` AS `expirationdate`, + `lowest_priority` AS `lowestPriority`, + `suspended` AS `suspend`, + `suspended_until_date` AS `suspend_until`, + `item_type` AS `itemtype`, + `item_level` AS `item_level_hold` + FROM `holds` + WHERE completed=0; + +-- +-- Table structure for view `old_reserves` +-- + +CREATE VIEW `old_reserves` +AS + SELECT + `id` AS `reserve_id`, + `patron_id` AS `borrowernumber`, + `placing_date` AS `reservedate`, + `biblio_id` AS `biblionumber`, + `pickup_library_id` AS `branchcode`, + CASE + WHEN `status` = 'cancelled' THEN + `completion_date` + ELSE + NULL + END AS `cancellationdate`, + `notes` AS `reservenotes`, + `priority`, + CASE + WHEN `status` = 'fulfilled' THEN + 'F' + ELSE + NULL + END AS `found`, + `timestamp`, + `item_id` AS `itemnumber`, + `waiting_date` AS `waitingdate`, + `expiration_date` AS `expirationdate`, + `lowest_priority` AS `lowestPriority`, + `suspended` AS `suspend`, + `suspended_until_date` AS `suspend_until`, + `item_type` AS `itemtype`, + `item_level` AS `item_level_hold` + FROM `holds` + WHERE completed=1; -- -- Table structure for table `reviews` @@ -4108,7 +4158,7 @@ CREATE TABLE IF NOT EXISTS club_holds_to_patron_holds ( -- KEY club_hold_id (club_hold_id), CONSTRAINT clubs_holds_paton_holds_ibfk_1 FOREIGN KEY (club_hold_id) REFERENCES club_holds (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT clubs_holds_paton_holds_ibfk_2 FOREIGN KEY (patron_id) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES reserves (reserve_id) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES holds (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -- 2.25.0