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>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>

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 (file)
index 0000000..881d2cd
--- /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 fa5044e..8c88771 100644 (file)
--- 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 c4ce39a..70e7d19 100644 (file)
--- 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');