Attachment 77819 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), 3.87 KB, created by Andrew Isherwood on 2018-08-15 14:56:24 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Andrew Isherwood

Created: 2018-08-15 14:56:24 UTC

Size: 3.87 KB

patch

obsolete

>From e2a7846116e401dbdda611f2bfa4bba1e61ed72e 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.
>---
> .../bug_20581-add_new_illrequests_status_alias_column.perl     | 10 ++++++++++
> installer/data/mysql/en/optional/ill_auth_cat.sql              |  1 +
> installer/data/mysql/en/optional/ill_auth_cat.txt              |  1 +
> installer/data/mysql/kohastructure.sql                         |  5 +++++
> 4 files changed, 17 insertions(+)
> create mode 100644 installer/data/mysql/atomicupdate/bug_20581-add_new_illrequests_status_alias_column.perl
> create mode 100644 installer/data/mysql/en/optional/ill_auth_cat.sql
> create mode 100644 installer/data/mysql/en/optional/ill_auth_cat.txt
>
>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..64a4824e36
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_20581-add_new_illrequests_status_alias_column.perl
>@@ -0,0 +1,10 @@
>+$DBversion = 'XXX';  # will be replaced by the RM
>+if( CheckVersion( $DBversion ) ) {
>+
>+    $dbh->do( "ALTER TABLE illrequests ADD COLUMN status_alias integer DEFAULT NULL" );
>+    $dbh->do( "ALTER TABLE illrequests ADD CONSTRAINT illrequests_safk FOREIGN KEY (status_alias) REFERENCES authorised_values(id) 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/en/optional/ill_auth_cat.sql b/installer/data/mysql/en/optional/ill_auth_cat.sql
>new file mode 100644
>index 0000000000..96e44900be
>--- /dev/null
>+++ b/installer/data/mysql/en/optional/ill_auth_cat.sql
>@@ -0,0 +1 @@
>+INSERT IGNORE INTO authorised_value_categories (category_name) VALUES ('ILLSTATUS');
>diff --git a/installer/data/mysql/en/optional/ill_auth_cat.txt b/installer/data/mysql/en/optional/ill_auth_cat.txt
>new file mode 100644
>index 0000000000..934903a884
>--- /dev/null
>+++ b/installer/data/mysql/en/optional/ill_auth_cat.txt
>@@ -0,0 +1 @@
>+Default ILL request status authorised value category name
>diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
>index 92b86350a1..623e4feab2 100644
>--- a/installer/data/mysql/kohastructure.sql
>+++ b/installer/data/mysql/kohastructure.sql
>@@ -4145,6 +4145,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
>@@ -4165,6 +4166,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 DELETE SET NULL
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
> 
> --
>-- 
>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