Bugzilla – Attachment 152056 Details for
Bug 21983
Better handling of deleted biblios on ILL requests
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 21983: DB update
Bug-21983-DB-update.patch (text/plain), 4.88 KB, created by
Kyle M Hall (khall)
on 2023-06-06 16:56:08 UTC
(
hide
)
Description:
Bug 21983: DB update
Filename:
MIME Type:
Creator:
Kyle M Hall (khall)
Created:
2023-06-06 16:56:08 UTC
Size:
4.88 KB
patch
obsolete
>From 021350040c3ad90105727e99123cf34c291c9f93 Mon Sep 17 00:00:00 2001 >From: Tomas Cohen Arazi <tomascohen@theke.io> >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 <martin.renvoize@ptfs-europe.com> > >Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> >--- > .../mysql/atomicupdate/bug_21983_ill_fk.pl | 40 +++++++++++++++++++ > installer/data/mysql/db_revs/221200026.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/221200026.pl b/installer/data/mysql/db_revs/221200026.pl >index 5cd09ece8f..fb8ccc9e6e 100755 >--- a/installer/data/mysql/db_revs/221200026.pl >+++ b/installer/data/mysql/db_revs/221200026.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 7e93b6ffc7..3f666d2df9 100644 >--- a/installer/data/mysql/kohastructure.sql >+++ b/installer/data/mysql/kohastructure.sql >@@ -3294,6 +3294,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.30.2
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 21983
:
83226
|
83227
|
152005
|
152006
|
152007
|
152008
|
152010
|
152011
|
152012
|
152013
|
152056
|
152057
|
152058
|
152059
|
152060
|
152063