Attachment 85075 Details for Bug 20581 – Bug 20581: Modifications to database schema

[patch] Bug 20581: Modifications to database schema

Bug-20581-Modifications-to-database-schema.patch (text/plain), 4.12 KB, created by Katrin Fischer on 2019-02-14 06:43:17 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Katrin Fischer

Created: 2019-02-14 06:43:17 UTC

Size: 4.12 KB

patch

obsolete

>From 6d650ecd9e2618bc741291cd055835ecb4ecb5a6 Mon Sep 17 00:00:00 2001
>From: Andrew Isherwood <andrew.isherwood@ptfs-europe.com>
>Date: Tue, 17 Apr 2018 11:57:09 +0100
>Subject: [PATCH] Bug 20581: Modifications to database schema
>
>- This patch provides an illrequests.status_alias column that is a foreign key to
>authorised_values.id. This provides the ability for an ILL request to
>have an optional status alias that is defined as an authorised valued.
>- A new ILLSTATUS authorised value category is also made available during install.
>
>Signed-off-by: Niamh.Walker-Headon@it-tallaght.ie
>
>Following commit was squashed into this one:
>
>Bug 20581: (follow-up) Modify DB updgrade
>
>As per
>https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=20581#c56
>
>Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
>
>Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
>---
> .../bug_20581-add_new_illrequests_status_alias_column.perl | 14 ++++++++++++++
> installer/data/mysql/kohastructure.sql                     |  5 +++++
> installer/data/mysql/mandatory/auth_val_cat.sql            |  4 ++++
> 3 files changed, 23 insertions(+)
> create mode 100644 installer/data/mysql/atomicupdate/bug_20581-add_new_illrequests_status_alias_column.perl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_20581-add_new_illrequests_status_alias_column.perl b/installer/data/mysql/atomicupdate/bug_20581-add_new_illrequests_status_alias_column.perl
>new file mode 100644
>index 0000000000..881d2cd13e
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_20581-add_new_illrequests_status_alias_column.perl
>@@ -0,0 +1,14 @@
>+$DBversion = 'XXX';  # will be replaced by the RM
>+if( CheckVersion( $DBversion ) ) {
>+
>+    if ( !column_exists( 'illrequests', 'status_alias' ) ) {
>+        $dbh->do( "ALTER TABLE illrequests ADD COLUMN status_alias integer DEFAULT NULL AFTER status" );
>+    }
>+    if ( !foreign_key_exists( 'illrequests', 'illrequests_safk' ) ) {
>+        $dbh->do( "ALTER TABLE illrequests ADD CONSTRAINT illrequests_safk FOREIGN KEY (status_alias) REFERENCES authorised_values(id) ON UPDATE CASCADE ON DELETE SET NULL" );
>+    }
>+    $dbh->do( "INSERT IGNORE INTO authorised_value_categories SET category_name = 'ILLSTATUS'");
>+
>+    SetVersion( $DBversion );
>+    print "Upgrade to $DBversion done (Bug 20581 - Allow manual selection of custom ILL request statuses)\n";
>+}
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 89e71c94a5..9914d46f15 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -4171,6 +4171,7 @@ CREATE TABLE illrequests (
>     biblio_id integer DEFAULT NULL,             -- Potential bib linked to request
>     branchcode varchar(50) NOT NULL,            -- The branch associated with the request
>     status varchar(50) DEFAULT NULL,            -- Current Koha status of request
>+    status_alias integer DEFAULT NULL,          -- Foreign key to relevant authorised_values.id
>     placed date DEFAULT NULL,                   -- Date the request was placed
>     replied date DEFAULT NULL,                  -- Last API response
>     updated timestamp DEFAULT CURRENT_TIMESTAMP -- Last modification to request
>@@ -4192,6 +4193,10 @@ CREATE TABLE illrequests (
>       FOREIGN KEY (`branchcode`)
>       REFERENCES `branches` (`branchcode`)
>       ON UPDATE CASCADE ON DELETE CASCADE
>+    CONSTRAINT `illrequests_safk`
>+      FOREIGN KEY (`status_alias`)
>+      REFERENCES `authorised_values` (`id`)
>+      ON UPDATE CASCADE ON DELETE SET NULL
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> 
> --
>diff --git a/installer/data/mysql/mandatory/auth_val_cat.sql b/installer/data/mysql/mandatory/auth_val_cat.sql
>index c4ce39a634..70e7d19a95 100644
>--- a/installer/data/mysql/mandatory/auth_val_cat.sql
>+++ b/installer/data/mysql/mandatory/auth_val_cat.sql
>@@ -58,3 +58,7 @@ INSERT IGNORE INTO authorised_value_categories( category_name ) VALUES
> -- For Housebound
> INSERT IGNORE INTO authorised_value_categories( category_name ) VALUES
>     ('HSBND_FREQ');
>+
>+-- For Interlibrary loans
>+INSERT IGNORE INTO authorised_value_categories( category_name ) VALUES
>+    ('ILLSTATUS');
>-- 
>2.11.0

Actions: View | Diff | Splinter Review

Attachments on bug 20581: 74323 | 74324 | 74325 | 74326 | 74327 | 74362 | 75333 | 75334 | 75810 | 77819 | 77820 | 77821 | 77822 | 77823 | 77824 | 77825 | 77826 | 78376 | 78377 | 78378 | 78379 | 78380 | 78381 | 78382 | 78383 | 78384 | 78431 | 78515 | 78516 | 78517 | 78518 | 78519 | 78520 | 78521 | 78522 | 78523 | 80279 | 80280 | 80281 | 80282 | 80283 | 80284 | 80285 | 80286 | 80474 | 80475 | 80501 | 80503 | 80505 | 80598 | 80599 | 80600 | 82063 | 82064 | 82065 | 82066 | 82067 | 82068 | 82069 | 82070 | 82071 | 82072 | 82073 | 82074 | 82075 | 82076 | 82443 | 82444 | 82445 | 82446 | 82447 | 82448 | 82449 | 82450 | 82451 | 82452 | 82453 | 82454 | 82455 | 82456 | 82457 | 82458 | 82459 | 82460 | 82461 | 82462 | 82463 | 82464 | 82465 | 82466 | 82467 | 82468 | 82472 | 82483 | 82484 | 82485 | 82486 | 82487 | 82488 | 82489 | 82490 | 82491 | 82492 | 82493 | 82494 | 82495 | 82496 | 82733 | 84202 | 84404 | 84768 | 84770 | 84771 | 84772 | 84773 | 84774 | 84775 | 84776 | 84777 | 84778 | 84779 | 84780 | 84781 | 84782 | 84783 | 84784 | 84785 | 84791 | 84814 | 84818 | 84819 | 84844 | 84911 | 84912 | 84913 | 84914 | 84915 | 84916 | 84917 | 84918 | 84919 | 84920 | 84921 | 84922 | 84923 | 84924 | 84925 | 84926 | 84927 | 84928 | 84929 | 84930 | 84931 | 84964 | 85075 | 85076 | 85077 | 85078 | 85079 | 85080 | 85081 | 85082 | 85083 | 85084 | 85085 | 85086 | 85087 | 85088 | 85089 | 85090 | 85091 | 85092 | 85093 | 85094 | 85095 | 85096