From e631207eaa7df02601353d34ade406ec9c8a5bc3 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 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 Signed-off-by: Katrin Fischer Signed-off-by: Nick Clemens --- ...81-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 fa5044e9fe..8c88771079 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4172,6 +4172,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 @@ -4193,6 +4194,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.39.5