From a10d5729e45899b28d0cd310124899245aa4a2d2 Mon Sep 17 00:00:00 2001 From: Andrew Isherwood Date: Tue, 5 Feb 2019 14:10:36 +0000 Subject: [PATCH] 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. --- ill/ill-requests.pl | 2 +- ...81-add_new_illrequests_status_alias_column.perl | 31 ++++++++++++++++++++-- installer/data/mysql/kohastructure.sql | 4 +-- .../prog/en/modules/ill/ill-requests.tt | 8 +++--- 4 files changed, 36 insertions(+), 9 deletions(-) diff --git a/ill/ill-requests.pl b/ill/ill-requests.pl index bf951058e1..acbdda29c8 100755 --- 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 881d2cd13e..442cddbe07 100644 --- 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 325ec2f421..9b245e0c33 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4171,7 +4171,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 @@ -4195,7 +4195,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 b124e8bda9..4adffa4382 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/ill/ill-requests.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/ill/ill-requests.tt @@ -204,10 +204,10 @@ [% request.capabilities.$stat.name | html %] [% FOREACH alias IN AuthorisedValues.Get('ILLSTATUS') %] - [% IF alias.id == current_alias %] - @@ -874,7 +874,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 -- 2.11.0