Attachment 134389 Details for Bug 30291 – Bug 30291: Database changes to recalls table

[patch] Bug 30291: Database changes to recalls table

Bug-30291-Database-changes-to-recalls-table.patch (text/plain), 7.65 KB, created by Martin Renvoize (ashimema) on 2022-04-29 15:34:24 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Martin Renvoize (ashimema)

Created: 2022-04-29 15:34:24 UTC

Size: 7.65 KB

patch

obsolete

>From 4e5e9a1020cb51432a0e95b24f093996d94c5300 Mon Sep 17 00:00:00 2001
>From: Aleisha Amohia <aleishaamohia@hotmail.com>
>Date: Mon, 28 Mar 2022 22:10:14 +1300
>Subject: [PATCH] Bug 30291: Database changes to recalls table
>
>Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
>Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
>---
> .../bug_30291_-_rename_recalls_columns.pl     | 25 +++++++++
> installer/data/mysql/kohastructure.sql        | 52 +++++++++----------
> 2 files changed, 51 insertions(+), 26 deletions(-)
> create mode 100644 installer/data/mysql/atomicupdate/bug_30291_-_rename_recalls_columns.pl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_30291_-_rename_recalls_columns.pl b/installer/data/mysql/atomicupdate/bug_30291_-_rename_recalls_columns.pl
>new file mode 100644
>index 0000000000..72a7d2751d
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_30291_-_rename_recalls_columns.pl
>@@ -0,0 +1,25 @@
>+use Modern::Perl;
>+
>+return {
>+    bug_number => "30291",
>+    description => "Renaming recalls table columns",
>+    up => sub {
>+        my ($args) = @_;
>+        my ($dbh, $out) = @$args{qw(dbh out)};
>+
>+        if( TableExists( 'recalls' ) and column_exists( 'recalls', 'recall_id' ) ) {
>+            $dbh->do(q{ ALTER TABLE recalls CHANGE COLUMN recall_id id int(11) NOT NULL AUTO_INCREMENT });
>+            $dbh->do(q{ ALTER TABLE recalls CHANGE COLUMN borrowernumber patron_id int(11) NOT NULL DEFAULT 0 });
>+            $dbh->do(q{ ALTER TABLE recalls CHANGE COLUMN recalldate created_date datetime DEFAULT NULL });
>+            $dbh->do(q{ ALTER TABLE recalls CHANGE COLUMN biblionumber biblio_id int(11) NOT NULL DEFAULT 0 });
>+            $dbh->do(q{ ALTER TABLE recalls CHANGE COLUMN branchcode pickup_library_id varchar(10) DEFAULT NULL });
>+            $dbh->do(q{ ALTER TABLE recalls CHANGE COLUMN cancellationdate completed_date datetime DEFAULT NULL });
>+            $dbh->do(q{ ALTER TABLE recalls CHANGE COLUMN recallnotes notes mediumtext });
>+            $dbh->do(q{ ALTER TABLE recalls CHANGE COLUMN itemnumber item_id int(11) DEFAULT NULL });
>+            $dbh->do(q{ ALTER TABLE recalls CHANGE COLUMN waitingdate waiting_date datetime DEFAULT NULL });
>+            $dbh->do(q{ ALTER TABLE recalls CHANGE COLUMN expirationdate expiration_date datetime DEFAULT NULL });
>+            $dbh->do(q{ ALTER TABLE recalls CHANGE COLUMN old completed TINYINT(1) NOT NULL DEFAULT 0 });
>+            $dbh->do(q{ ALTER TABLE recalls CHANGE COLUMN item_level_recall item_level TINYINT(1) NOT NULL DEFAULT 0 });
>+        }
>+    },
>+};
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index f7ec7186b9..00417e5b92 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -4287,32 +4287,32 @@ CREATE TABLE `ratings` (
> --
> 
> 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 datetime 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 datetime 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
>-    status ENUM('requested','overdue','waiting','in_transit','cancelled','expired','fulfilled') DEFAULT 'requested', -- request status
>-    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 datetime DEFAULT NULL, -- the date the item was marked as waiting for the patron at the library
>-    expirationdate datetime DEFAULT NULL, -- the date the recall expires
>-    old TINYINT(1) NOT NULL DEFAULT 0, -- flag if the recall is old and no longer active, i.e. expired, cancelled or completed
>-    item_level_recall TINYINT(1) NOT NULL DEFAULT 0, -- flag if item-level recall
>-     PRIMARY KEY (recall_id),
>-     KEY borrowernumber (borrowernumber),
>-     KEY biblionumber (biblionumber),
>-     KEY itemnumber (itemnumber),
>-     KEY branchcode (branchcode),
>-     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
>-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
>+CREATE TABLE recalls (
>+    id int(11) NOT NULL AUTO_INCREMENT COMMENT "Unique identifier for this recall",
>+    patron_id int(11) NOT NULL DEFAULT 0 COMMENT "Identifier for patron who requested recall",
>+    created_date datetime DEFAULT NULL COMMENT "Date the recall was requested",
>+    biblio_id int(11) NOT NULL DEFAULT 0 COMMENT "Identifier for bibliographic record that has been recalled",
>+    pickup_library_id varchar(10) DEFAULT NULL COMMENT "Identifier for recall pickup library",
>+    completed_date datetime DEFAULT NULL COMMENT "Date the recall is completed (fulfilled, cancelled or expired)",
>+    notes mediumtext COMMENT "Notes related to the recall",
>+    priority smallint(6) DEFAULT NULL COMMENT "Where in the queue the patron sits",
>+    status ENUM('requested','overdue','waiting','in_transit','cancelled','expired','fulfilled') DEFAULT 'requested' COMMENT "Status of recall",
>+    timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "Date and time the recall was last updated",
>+    item_id int(11) DEFAULT NULL COMMENT "Identifier for item record that was recalled, if an item-level recall",
>+    waiting_date datetime DEFAULT NULL COMMENT "Date an item was marked as waiting for the patron at the library",
>+    expiration_date datetime DEFAULT NULL COMMENT "Date recall is no longer required, or date recall will expire after waiting on shelf for pickup",
>+    completed TINYINT(1) NOT NULL DEFAULT 0 COMMENT "Flag if recall is old and no longer active, i.e. expired, cancelled or completed",
>+    item_level TINYINT(1) NOT NULL DEFAULT 0 COMMENT "Flag if recall is for a specific item",
>+    PRIMARY KEY (id),
>+    KEY recalls_ibfk_1 (patron_id),
>+    KEY recalls_ibfk_2 (biblio_id),
>+    KEY recalls_ibfk_3 (item_id),
>+    KEY recalls_ibfk_4 (pickup_library_id),
>+    CONSTRAINT recalls_ibfk_1 FOREIGN KEY (patron_id) REFERENCES borrowers (borrowernumber) ON DELETE CASCADE ON UPDATE CASCADE,
>+    CONSTRAINT recalls_ibfk_2 FOREIGN KEY (biblio_id) REFERENCES biblio (biblionumber) ON DELETE CASCADE ON UPDATE CASCADE,
>+    CONSTRAINT recalls_ibfk_3 FOREIGN KEY (item_id) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE,
>+    CONSTRAINT recalls_ibfk_4 FOREIGN KEY (pickup_library_id) REFERENCES branches (branchcode) ON DELETE CASCADE ON UPDATE CASCADE
>+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT="Information related to recalls in Koha";
> 
> --
> -- Table structure for table `repeatable_holidays`
>-- 
>2.20.1

Actions: View | Diff | Splinter Review

Attachments on bug 30291: 132379 | 132380 | 132381 | 132382 | 132383 | 132384 | 132385 | 132386 | 134359 | 134360 | 134361 | 134362 | 134363 | 134364 | 134365 | 134366 | 134367 | 134389 | 134390 | 134391 | 134392 | 134393 | 134394 | 134395 | 134396 | 134397 | 134629 | 134630