From e7a136546559c587b29aa51d717b2802eb00014e Mon Sep 17 00:00:00 2001 From: Tomas Cohen Arazi Date: Wed, 27 Jan 2021 16:46:39 -0300 Subject: [PATCH] Bug 25260: Add a 'holds' table This patch introduces a new table for holds. It also creates views for reseves and oldreserves to retain the current behaviour. This views will not be used in the codebase, but kept for backwards compatibility in reports. Sponsored-by: ByWater Solutions Sponsored-by: Theke Solutions Signed-off-by: Martin Renvoize --- Koha/Schema/Result/Hold.pm | 437 +++++++++++++++++++++++++ installer/data/mysql/kohastructure.sql | 267 ++++++++++----- 2 files changed, 620 insertions(+), 84 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..f3e0ab6fdf --- /dev/null +++ b/Koha/Schema/Result/Hold.pm @@ -0,0 +1,437 @@ +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 + +internal hold identifier + +=head2 patron_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 1 + +foreign key from the borrowers table defining which patron this hold is for + +=head2 pickup_library_id + + data_type: 'varchar' + is_foreign_key: 1 + is_nullable: 1 + size: 10 + +foreign key from the branches table defining which branch the patron wishes to pick this hold up at + +=head2 desk_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 1 + +foreign key from the desks table defining which desk the patron should pick this hold up at + +=head2 created_date + + data_type: 'date' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +the date the hold was placed + +=head2 biblio_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 1 + +foreign key from the biblio table defining which bib record this hold is on + +=head2 item_id + + data_type: 'integer' + is_foreign_key: 1 + is_nullable: 1 + +foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with + +=head2 item_type + + data_type: 'varchar' + is_foreign_key: 1 + is_nullable: 1 + size: 10 + +If record level hold, the optional itemtype of the item the patron is requesting + +=head2 item_level + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +Is the hold was placed at item level + +=head2 completed_date + + data_type: 'date' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +the date this hold was completed (fulfilled or cancelled) + +=head2 notes + + data_type: 'longtext' + is_nullable: 1 + +notes related to this hold + +=head2 priority + + data_type: 'smallint' + default_value: 1 + is_nullable: 0 + +where in the queue the patron sits + +=head2 status + + data_type: 'enum' + default_value: 'placed' + extra: {list => ["placed","fulfilled","processing","waiting","in_transit","cancelled"]} + is_nullable: 0 + +the status the hold is ('placed', 'fulfilled', 'waiting', 'in_transit', 'cancelled') + +=head2 timestamp + + data_type: 'timestamp' + datetime_undef_if_invalid: 1 + default_value: current_timestamp + is_nullable: 0 + +the date and time this hold was last updated + +=head2 waiting_date + + data_type: 'date' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +the date the item was marked as waiting for the patron at the library + +=head2 expiration_date + + data_type: 'date' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date) + +=head2 lowest_priority + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +if it was assigned the lowest priority + +=head2 non_priority + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +if it was assigned no priority + +=head2 suspended + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +If it is a non-priority hold + +=head2 suspended_until_date + + data_type: 'datetime' + datetime_undef_if_invalid: 1 + is_nullable: 1 + +Date until it is suspended + +=head2 completed + + data_type: 'tinyint' + default_value: 0 + is_nullable: 0 + +If it has been completed (i.e. either 'fulfilled' or 'cancelled') + +=head2 cancellation_reason + + data_type: 'varchar' + is_nullable: 1 + size: 80 + +optional authorised value CANCELLATION_REASON + +=cut + +__PACKAGE__->add_columns( + "id", + { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, + "patron_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, + "pickup_library_id", + { data_type => "varchar", is_foreign_key => 1, is_nullable => 1, size => 10 }, + "desk_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, + "created_date", + { data_type => "date", datetime_undef_if_invalid => 1, is_nullable => 1 }, + "biblio_id", + { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, + "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 }, + "completed_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", + "processing", + "waiting", + "in_transit", + "cancelled", + ], + }, + is_nullable => 0, + }, + "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 }, + "non_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 }, + "cancellation_reason", + { data_type => "varchar", is_nullable => 1, size => 80 }, +); + +=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, + join_type => "LEFT", + on_delete => "SET NULL", + 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 desk + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "desk", + "Koha::Schema::Result::Desk", + { desk_id => "desk_id" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "SET NULL", + on_update => "CASCADE", + }, +); + +=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 => "SET NULL", + 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 => "SET NULL", + 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, + join_type => "LEFT", + on_delete => "SET NULL", + 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 => "SET NULL", + on_update => "CASCADE", + }, +); + + +# Created by DBIx::Class::Schema::Loader v0.07046 @ 2021-01-27 19:43:42 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:EjBk5LQLG2761uSkk/35Sw + +__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 58dadc1541..9437352430 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1865,7 +1865,7 @@ CREATE TABLE `club_holds_to_patron_holds` ( KEY `clubs_holds_paton_holds_ibfk_3` (`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; /*!40101 SET character_set_client = @saved_cs_client */; @@ -2645,6 +2645,53 @@ CREATE TABLE `hold_fill_targets` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `holds` +-- + +DROP TABLE IF EXISTS `holds`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `holds` ( + `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'internal hold identifier', + `patron_id` int(11) DEFAULT NULL COMMENT 'foreign key from the borrowers table defining which patron this hold is for', + `pickup_library_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table defining which branch the patron wishes to pick this hold up at', + `desk_id` int(11) DEFAULT NULL COMMENT 'foreign key from the desks table defining which desk the patron should pick this hold up at', + `created_date` date DEFAULT NULL COMMENT 'the date the hold was placed', + `biblio_id` int(11) DEFAULT NULL COMMENT 'foreign key from the biblio table defining which bib record this hold is on', + `item_id` int(11) DEFAULT NULL COMMENT '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) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting', + `item_level` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is the hold was placed at item level', + `completed_date` date DEFAULT NULL COMMENT 'the date this hold was completed (fulfilled or cancelled)', + `notes` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'notes related to this hold', + `priority` smallint(6) NOT NULL DEFAULT 1 COMMENT 'where in the queue the patron sits', + `status` enum('placed','fulfilled','processing','waiting','in_transit','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'placed' COMMENT 'the status the hold is (''placed'', ''fulfilled'', ''waiting'', ''in_transit'', ''cancelled'')', + `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'the date and time this hold was last updated', + `waiting_date` date DEFAULT NULL COMMENT 'the date the item was marked as waiting for the patron at the library', + `expiration_date` date DEFAULT NULL COMMENT '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 COMMENT 'if it was assigned the lowest priority', + `non_priority` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'if it was assigned no priority', + `suspended` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'If it is a non-priority hold', + `suspended_until_date` datetime DEFAULT NULL COMMENT 'Date until it is suspended', + `completed` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'If it has been completed (i.e. either ''fulfilled'' or ''cancelled'')', + `cancellation_reason` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'optional authorised value CANCELLATION_REASON', + 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`), + KEY `holds_ibfk_2` (`desk_id`), + CONSTRAINT `holds_ibfk_1` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_2` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) 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 (`item_type`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_5` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `holds_ibfk_6` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +/*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `housebound_profile` -- @@ -3862,48 +3909,37 @@ CREATE TABLE `old_issues` ( /*!40101 SET character_set_client = @saved_cs_client */; -- --- Table structure for table `old_reserves` +-- Temporary table structure for view `old_reserves` -- DROP TABLE IF EXISTS `old_reserves`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `old_reserves` ( - `reserve_id` int(11) NOT NULL COMMENT 'primary key', - `borrowernumber` int(11) DEFAULT NULL COMMENT 'foreign key from the borrowers table defining which patron this hold is for', - `reservedate` date DEFAULT NULL COMMENT 'the date the hold was places', - `biblionumber` int(11) DEFAULT NULL COMMENT 'foreign key from the biblio table defining which bib record this hold is on', - `branchcode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table defining which branch the patron wishes to pick this hold up at', - `desk_id` int(11) DEFAULT NULL COMMENT 'foreign key from the desks table defining which desk the patron should pick this hold up at', - `notificationdate` date DEFAULT NULL COMMENT 'currently unused', - `reminderdate` date DEFAULT NULL COMMENT 'currently unused', - `cancellationdate` date DEFAULT NULL COMMENT 'the date this hold was cancelled', - `cancellation_reason` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'optional authorised value CANCELLATION_REASON', - `reservenotes` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'notes related to this hold', - `priority` smallint(6) NOT NULL DEFAULT 1 COMMENT 'where in the queue the patron sits', - `found` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '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() COMMENT 'the date and time this hold was last updated', - `itemnumber` int(11) DEFAULT NULL COMMENT '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 COMMENT 'the date the item was marked as waiting for the patron at the library', - `expirationdate` date DEFAULT NULL COMMENT '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 COMMENT '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 COMMENT 'in this hold suspended (1 for yes, 0 for no)', - `suspend_until` datetime DEFAULT NULL COMMENT 'the date this hold is suspended until (NULL for infinitely)', - `itemtype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting', - `item_level_hold` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is the hpld placed at item level', - `non_priority` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is this a non priority hold', - 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; -/*!40101 SET character_set_client = @saved_cs_client */; +/*!50001 DROP VIEW IF EXISTS `old_reserves`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `old_reserves` ( + `reserve_id` tinyint NOT NULL, + `borrowernumber` tinyint NOT NULL, + `reservedate` tinyint NOT NULL, + `biblionumber` tinyint NOT NULL, + `branchcode` tinyint NOT NULL, + `desk_id` tinyint NOT NULL, + `cancellationdate` tinyint NOT NULL, + `cancellation_reason` tinyint NOT NULL, + `reservenotes` tinyint NOT NULL, + `priority` tinyint NOT NULL, + `found` tinyint NOT NULL, + `timestamp` tinyint NOT NULL, + `itemnumber` tinyint NOT NULL, + `waitingdate` tinyint NOT NULL, + `expirationdate` tinyint NOT NULL, + `lowestPriority` tinyint NOT NULL, + `suspend` tinyint NOT NULL, + `suspend_until` tinyint NOT NULL, + `itemtype` tinyint NOT NULL, + `item_level_hold` tinyint NOT NULL, + `non_priority` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; -- -- Table structure for table `opac_news` @@ -4301,52 +4337,37 @@ CREATE TABLE `reports_dictionary` ( /*!40101 SET character_set_client = @saved_cs_client */; -- --- Table structure for table `reserves` +-- Temporary table structure for view `reserves` -- DROP TABLE IF EXISTS `reserves`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `reserves` ( - `reserve_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', - `borrowernumber` int(11) NOT NULL DEFAULT 0 COMMENT 'foreign key from the borrowers table defining which patron this hold is for', - `reservedate` date DEFAULT NULL COMMENT 'the date the hold was placed', - `biblionumber` int(11) NOT NULL DEFAULT 0 COMMENT 'foreign key from the biblio table defining which bib record this hold is on', - `branchcode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'foreign key from the branches table defining which branch the patron wishes to pick this hold up at', - `desk_id` int(11) DEFAULT NULL COMMENT 'foreign key from the desks table defining which desk the patron should pick this hold up at', - `notificationdate` date DEFAULT NULL COMMENT 'currently unused', - `reminderdate` date DEFAULT NULL COMMENT 'currently unused', - `cancellationdate` date DEFAULT NULL COMMENT 'the date this hold was cancelled', - `cancellation_reason` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'optional authorised value CANCELLATION_REASON', - `reservenotes` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'notes related to this hold', - `priority` smallint(6) NOT NULL DEFAULT 1 COMMENT 'where in the queue the patron sits', - `found` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '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() COMMENT 'the date and time this hold was last updated', - `itemnumber` int(11) DEFAULT NULL COMMENT '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 COMMENT 'the date the item was marked as waiting for the patron at the library', - `expirationdate` date DEFAULT NULL COMMENT '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 DEFAULT NULL, - `itemtype` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'If record level hold, the optional itemtype of the item the patron is requesting', - `item_level_hold` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is the hpld placed at item level', - `non_priority` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Is this a non priority hold', - PRIMARY KEY (`reserve_id`), - KEY `priorityfoundidx` (`priority`,`found`), - KEY `borrowernumber` (`borrowernumber`), - KEY `biblionumber` (`biblionumber`), - KEY `itemnumber` (`itemnumber`), - KEY `branchcode` (`branchcode`), - KEY `desk_id` (`desk_id`), - 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, - CONSTRAINT `reserves_ibfk_6` FOREIGN KEY (`desk_id`) REFERENCES `desks` (`desk_id`) ON DELETE SET NULL ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -/*!40101 SET character_set_client = @saved_cs_client */; +/*!50001 DROP VIEW IF EXISTS `reserves`*/; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +/*!50001 CREATE TABLE `reserves` ( + `reserve_id` tinyint NOT NULL, + `borrowernumber` tinyint NOT NULL, + `reservedate` tinyint NOT NULL, + `biblionumber` tinyint NOT NULL, + `branchcode` tinyint NOT NULL, + `desk_id` tinyint NOT NULL, + `cancellationdate` tinyint NOT NULL, + `cancellation_reason` tinyint NOT NULL, + `reservenotes` tinyint NOT NULL, + `priority` tinyint NOT NULL, + `found` tinyint NOT NULL, + `timestamp` tinyint NOT NULL, + `itemnumber` tinyint NOT NULL, + `waitingdate` tinyint NOT NULL, + `expirationdate` tinyint NOT NULL, + `lowestPriority` tinyint NOT NULL, + `suspend` tinyint NOT NULL, + `suspend_until` tinyint NOT NULL, + `itemtype` tinyint NOT NULL, + `item_level_hold` tinyint NOT NULL, + `non_priority` tinyint NOT NULL +) ENGINE=MyISAM */; +SET character_set_client = @saved_cs_client; -- -- Table structure for table `return_claims` @@ -5339,7 +5360,85 @@ CREATE TABLE `zebraqueue` ( KEY `zebraqueue_lookup` (`server`,`biblio_auth_number`,`operation`,`done`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +-- +-- Table structure for table `reserves` +-- + +CREATE VIEW `reserves` +AS + SELECT + `id` AS `reserve_id`, + `patron_id` AS `borrowernumber`, + `created_date` AS `reservedate`, + `biblio_id` AS `biblionumber`, + `pickup_library_id` AS `branchcode`, + `desk_id`, + NULL AS `cancellationdate`, + `cancellation_reason`, + `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`, + `non_priority` + 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`, + `created_date` AS `reservedate`, + `biblio_id` AS `biblionumber`, + `pickup_library_id` AS `branchcode`, + `desk_id`, + CASE + WHEN `status` = 'cancelled' THEN + `completed_date` + ELSE + NULL + END AS `cancellationdate`, + `cancellation_reason`, + `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`, + `non_priority` + FROM `holds` + WHERE completed=1; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; -- 2.32.0