Bug 20581: (follow-up) Do not use PK for AV FK

As per:
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=20581#c133

We switch from using authorised_values.id when creating the foreign key
illrequests.status_alias, we now use authorised_values.authorised_value

I have added a migration from using id -> authorised_value, so when
existing users of this bug get this version, their DB migrates nicely
instead of just breaking.

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/ill/ill-requests.pl b/ill/ill-requests.pl
index bf95105..acbdda2 100755 (executable)
--- a/ill/ill-requests.pl
+++ b/ill/ill-requests.pl
@@ -174,7 +174,7 @@ if ( $backends_available ) {
             $request->price_paid($params->{price_paid});
             $request->notesopac($params->{notesopac});
             $request->notesstaff($params->{notesstaff});
-            my $alias = ($params->{status_alias} =~ /\d/) ?
+            my $alias = ($params->{status_alias}) ?
                 $params->{status_alias} :
                 undef;
             $request->status_alias($alias);

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
index 881d2cd..442cddb 100644 (file)
--- 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
@@ -2,10 +2,37 @@ $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" );
+        # Fresh upgrade, just add the column and constraint
+        $dbh->do( "ALTER TABLE illrequests ADD COLUMN status_alias varchar(80) DEFAULT NULL AFTER status" );
+    } else {
+        # Migrate all existing foreign keys from referencing authorised_values.id
+        # to referencing authorised_values.authorised_value
+        # First remove the foreign key constraint and index
+        if ( foreign_key_exists( 'illrequests', 'illrequests_safk' ) ) {
+            $dbh->do( "ALTER TABLE illrequests DROP FOREIGN KEY illrequests_safk");
+        }
+        if ( index_exists( 'illrequests', 'illrequests_safk' ) ) {
+            $dbh->do( "DROP INDEX illrequests_safk IN illrequests" );
+        }
+        # Now change the illrequests.status_alias column definition from int to varchar
+        $dbh->do( "ALTER TABLE illrequests MODIFY COLUMN status_alias varchar(80)" );
+        # Now replace all references to authorised_values.id with their
+        # corresponding authorised_values.authorised_value
+        my $sth = $dbh->prepare( "SELECT illrequest_id, status_alias FROM illrequests WHERE status_alias IS NOT NULL" );
+        $sth->execute();
+        while (my @row = $sth->fetchrow_array()) {
+            my $r_id = $row[0];
+            my $av_id = $row[1];
+            # Get the authorised value's authorised_value value
+            my ($av_val) = $dbh->selectrow_array( "SELECT authorised_value FROM authorised_values WHERE id = ?", {}, $av_id );
+            # Now update illrequests.status_alias
+            if ($av_val) {
+                $dbh->do( "UPDATE illrequests SET status_alias = ? WHERE illrequest_id = ?", {}, ($av_val, $r_id) );
+            }
+        }
     }
     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( "ALTER TABLE illrequests ADD CONSTRAINT illrequests_safk FOREIGN KEY (status_alias) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE SET NULL" );
     }
     $dbh->do( "INSERT IGNORE INTO authorised_value_categories SET category_name = 'ILLSTATUS'");
 

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 0f26f2d..76798c3 100644 (file)
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -4172,7 +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
+    status_alias varchar(80) DEFAULT NULL,      -- Foreign key to relevant authorised_values.authorised_value
     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
@@ -4196,7 +4196,7 @@ CREATE TABLE illrequests (
       ON UPDATE CASCADE ON DELETE CASCADE,
     CONSTRAINT `illrequests_safk`
       FOREIGN KEY (`status_alias`)
-      REFERENCES `authorised_values` (`id`)
+      REFERENCES `authorised_values` (`authorised_value`)
       ON UPDATE CASCADE ON DELETE SET NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 

diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/ill/ill-requests.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/ill/ill-requests.tt
index 730e4c0..2041cf0 100644 (file)
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/ill/ill-requests.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/ill/ill-requests.tt
@@ -203,10 +203,10 @@
                                             [% request.capabilities.$stat.name | html %]
                                         </option>
                                         [% FOREACH alias IN AuthorisedValues.Get('ILLSTATUS') %]
-                                            [% IF alias.id == current_alias %]
-                                            <option value="[% alias.id | html %]" selected>
+                                            [% IF alias.authorised_value == current_alias %]
+                                            <option value="[% alias.authorised_value | html %]" selected>
                                             [% ELSE %]
-                                            <option value="[% alias.id | html %]">
+                                            <option value="[% alias.authorised_value | html %]">
                                             [% END %]
                                                 [% alias.lib | html %]
                                             </option>
@@ -873,7 +873,7 @@
             // Get our data from the API and process it prior to passing
             // it to datatables
             var ajax = $.ajax(
-                '/api/v1/illrequests?embed=metadata,patron,capabilities,library'
+                '/api/v1/illrequests?embed=metadata,patron,capabilities,library,status_alias'
                 ).done(function() {
                     var data = JSON.parse(ajax.responseText);
                     // Make a copy, we'll be removing columns next and need