From 4accc7521e596c913e8b64a97148065504946d5f Mon Sep 17 00:00:00 2001 From: Andrew Isherwood 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 --- .../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 8868ca036b..8aef59e2c6 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4130,6 +4130,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 @@ -4150,6 +4151,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