From b60671cff3664d59fabdf0799f1c4a19077bab83 Mon Sep 17 00:00:00 2001 From: Tomas Cohen Arazi Date: Mon, 5 Jun 2023 13:50:00 -0300 Subject: [PATCH] Bug 21983: DB update This patch: - Adds a illrequests.deleted_biblio_id column - Adjusts existing db_rev so people upgrading have the biblio_id value moved to the also created column, before attempting to add the FK constraint on the biblios table. - Adds a new db_rev for those who unfortunately already updated, it should be idempotent. Signed-off-by: Martin Renvoize Signed-off-by: Kyle M Hall Signed-off-by: Tomas Cohen Arazi (cherry picked from commit f1346c20257038e523be0405e9d84fe762d502db) --- .../mysql/atomicupdate/bug_21983_ill_fk.pl | 40 +++++++++++++++++++ installer/data/mysql/db_revs/221105004.pl | 20 ++++++++++ installer/data/mysql/kohastructure.sql | 1 + 3 files changed, 61 insertions(+) create mode 100755 installer/data/mysql/atomicupdate/bug_21983_ill_fk.pl diff --git a/installer/data/mysql/atomicupdate/bug_21983_ill_fk.pl b/installer/data/mysql/atomicupdate/bug_21983_ill_fk.pl new file mode 100755 index 0000000000..2453185c68 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_21983_ill_fk.pl @@ -0,0 +1,40 @@ +use Modern::Perl; + +return { + bug_number => "21983", + description => "Deleted biblio handling on ILL", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + + unless ( column_exists( 'illrequests', 'deleted_biblio_id' ) ) { + $dbh->do(q{ + ALTER TABLE illrequests + ADD COLUMN `deleted_biblio_id` int(11) DEFAULT NULL COMMENT 'Deleted bib linked to request' + AFTER `biblio_id`; + }); + say $out "Added column 'illrequests.deleted_biblio_id'"; + } + + # Move deleted biblio_id to deleted_biblio_id before setting the FK + $dbh->do(q{ + UPDATE illrequests + LEFT JOIN biblio + ON illrequests.biblio_id=biblio.biblionumber + SET illrequests.biblio_id=NULL, + illrequests.deleted_biblio_id=illrequests.biblio_id + WHERE biblio.biblionumber IS NULL + AND illrequests.biblio_id IS NOT NULL + }); + + unless ( foreign_key_exists( 'illrequests', 'illrequests_bibfk' ) ) { + $dbh->do(q{ + ALTER TABLE illrequests + ADD KEY `illrequests_bibfk` (`biblio_id`), + ADD FOREIGN KEY illrequests_bibfk (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE; + }); + + say $out "Added foreign key constraint 'illrequests.illrequests_bibfk'"; + } + }, +}; diff --git a/installer/data/mysql/db_revs/221105004.pl b/installer/data/mysql/db_revs/221105004.pl index 0f5148b968..7b840e51da 100755 --- a/installer/data/mysql/db_revs/221105004.pl +++ b/installer/data/mysql/db_revs/221105004.pl @@ -7,6 +7,26 @@ return { my ($args) = @_; my ($dbh, $out) = @$args{qw(dbh out)}; + unless ( column_exists( 'illrequests', 'deleted_biblio_id' ) ) { + $dbh->do(q{ + ALTER TABLE illrequests + ADD COLUMN `deleted_biblio_id` int(11) DEFAULT NULL COMMENT 'Deleted bib linked to request' + AFTER `biblio_id`; + }); + say $out "Added column 'illrequests.deleted_biblio_id'"; + } + + # Move deleted biblio_id to deleted_biblio_id before setting the FK + $dbh->do(q{ + UPDATE illrequests + LEFT JOIN biblio + ON illrequests.biblio_id=biblio.biblionumber + SET illrequests.biblio_id=NULL, + illrequests.deleted_biblio_id=illrequests.biblio_id + WHERE biblio.biblionumber IS NULL + AND illrequests.biblio_id IS NOT NULL + }); + unless ( foreign_key_exists( 'illrequests', 'illrequests_bibfk' ) ) { $dbh->do(q{ ALTER TABLE illrequests diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index e87116644e..b2c23bfdfc 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3247,6 +3247,7 @@ CREATE TABLE `illrequests` ( `illrequest_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ILL request number', `borrowernumber` int(11) DEFAULT NULL COMMENT 'Patron associated with request', `biblio_id` int(11) DEFAULT NULL COMMENT 'Potential bib linked to request', + `deleted_biblio_id` int(11) DEFAULT NULL COMMENT 'Deleted bib linked to request', `due_date` datetime DEFAULT NULL COMMENT 'Custom date due specified by backend, leave NULL for default date_due calculation', `branchcode` varchar(50) NOT NULL COMMENT 'The branch associated with the request', `status` varchar(50) DEFAULT NULL COMMENT 'Current Koha status of request', -- 2.39.5