From 1306daa0103a07e6478ec58de34f44045e538dc6 Mon Sep 17 00:00:00 2001 From: Agustin Moyano Date: Thu, 5 Sep 2019 14:13:36 -0300 Subject: [PATCH] Bug 19618: Add club_holds and club_holds_to_patron_holds tables This patch adds 2 new tables 1. club_holds 2. club_holds_to_patron_holds They are ment to keep info about hold requests made in name of a club To test: 1) apply this patch 2) perl installer/data/mysql/updatedatabase.pl SUCCESS => 2 new tables were created Sponsored-by: Southeast Kansas Library - SEKLS Signed-off-by: Martin Renvoize Signed-off-by: Kyle M Hall Signed-off-by: Martin Renvoize --- .../data/mysql/atomicupdate/club_holds.perl | 42 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 39 +++++++++++++++++ 2 files changed, 81 insertions(+) create mode 100644 installer/data/mysql/atomicupdate/club_holds.perl diff --git a/installer/data/mysql/atomicupdate/club_holds.perl b/installer/data/mysql/atomicupdate/club_holds.perl new file mode 100644 index 0000000000..e92789c7ba --- /dev/null +++ b/installer/data/mysql/atomicupdate/club_holds.perl @@ -0,0 +1,42 @@ +$DBversion = 'XXX'; # will be replaced by the RM +if( CheckVersion( $DBversion ) ) { + + $dbh->do(q| + CREATE TABLE IF NOT EXISTS club_holds ( + id INT(11) NOT NULL AUTO_INCREMENT, + club_id INT(11) NOT NULL, -- id for the club the hold was generated for + biblio_id INT(11) NOT NULL, -- id for the bibliographic record the hold has been placed against + item_id INT(11) NULL DEFAULT NULL, -- If item-level, the id for the item the hold has been placed agains + date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Timestamp for the placed hold + PRIMARY KEY (id), + -- KEY club_id (club_id), + CONSTRAINT clubs_holds_ibfk_1 FOREIGN KEY (club_id) REFERENCES clubs (id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT clubs_holds_ibfk_2 FOREIGN KEY (biblio_id) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT clubs_holds_ibfk_3 FOREIGN KEY (item_id) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + |); + + $dbh->do(q| + CREATE TABLE IF NOT EXISTS club_holds_to_patron_holds ( + id INT(11) NOT NULL AUTO_INCREMENT, + club_hold_id INT(11) NOT NULL, + patron_id INT(11) NOT NULL, + hold_id INT(11), + error_code ENUM ( 'damaged', 'ageRestricted', 'itemAlreadyOnHold', + 'tooManyHoldsForThisRecord', 'tooManyReservesToday', + 'tooManyReserves', 'notReservable', 'cannotReserveFromOtherBranches', + 'libraryNotFound', 'libraryNotPickupLocation', 'cannotBeTransferred' + ) NULL DEFAULT NULL, + error_message varchar(100) NULL DEFAULT NULL, + PRIMARY KEY (id), + -- 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 + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + |); + + # Always end with this (adjust the bug info) + SetVersion( $DBversion ); + print "Upgrade to $DBversion done (Bug 19618 - add club_holds tables)\n"; +} diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index aa5817d823..94c1c56a1a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4007,6 +4007,45 @@ CREATE TABLE IF NOT EXISTS clubs ( CONSTRAINT clubs_ibfk_2 FOREIGN KEY (branchcode) REFERENCES branches (branchcode) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +-- +-- Table structure for table 'club_holds' +-- + +CREATE TABLE IF NOT EXISTS club_holds ( + id INT(11) NOT NULL AUTO_INCREMENT, + club_id INT(11) NOT NULL, -- id for the club the hold was generated for + biblio_id INT(11) NOT NULL, -- id for the bibliographic record the hold has been placed against + item_id INT(11) NULL DEFAULT NULL, -- If item-level, the id for the item the hold has been placed agains + date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Timestamp for the placed hold + PRIMARY KEY (id), + -- KEY club_id (club_id), + CONSTRAINT clubs_holds_ibfk_1 FOREIGN KEY (club_id) REFERENCES clubs (id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT clubs_holds_ibfk_2 FOREIGN KEY (biblio_id) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT clubs_holds_ibfk_3 FOREIGN KEY (item_id) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table 'club_holds_to_patron_holds' +-- + +CREATE TABLE IF NOT EXISTS club_holds_to_patron_holds ( + id INT(11) NOT NULL AUTO_INCREMENT, + club_hold_id INT(11) NOT NULL, + patron_id INT(11) NOT NULL, + hold_id INT(11), + error_code ENUM ( 'damaged', 'ageRestricted', 'itemAlreadyOnHold', + 'tooManyHoldsForThisRecord', 'tooManyReservesToday', + 'tooManyReserves', 'notReservable', 'cannotReserveFromOtherBranches', + 'libraryNotFound', 'libraryNotPickupLocation', 'cannotBeTransferred' + ) NULL DEFAULT NULL, + error_message varchar(100) NULL DEFAULT NULL, + PRIMARY KEY (id), + -- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + -- -- Table structure for table 'club_enrollments' -- -- 2.39.5