Attachment 135783 Details for Bug 30899 – Bug 30899: [21.11.x] Follow-up for dbrev 211200041 from bug 30449

[patch] Bug 30899: [21.11.x] Follow-up for dbrev 211200041 from bug 30449

Bug-30899-2111x-Follow-up-for-dbrev-211200041-from.patch (text/plain), 4.29 KB, created by Marcel de Rooy on 2022-06-07 14:50:34 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Marcel de Rooy

Created: 2022-06-07 14:50:34 UTC

Size: 4.29 KB

patch

obsolete

>From ff02d16b57af8459c01d9bc8944b54b44c34458c Mon Sep 17 00:00:00 2001
>From: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
>Date: Tue, 7 Jun 2022 14:31:39 +0000
>Subject: [PATCH] Bug 30899: [21.11.x] Follow-up for dbrev 211200041 from bug
> 30449
>Content-Type: text/plain; charset=utf-8
>
>Replace DROP CONSTRAINT by DROP FOREIGN KEY in old dbrev, and
>submit a new dbrev to fix cases where dbrev went wrong.
>
>Test plan:
>
>NOTE: When you check show create table, verify that you see an index for category_code and a foreign key called borrower_attribute_types_ibfk_1.
>
>[1] run updatedatabase; check show create table
>[2] alter table borrower_attribute_types drop foreign key borrower_attribute_types_ibfk_1; alter table borrower_attribute_types drop index category_code; RUN updatedatabase again; check show create table
>[3] alter table borrower_attribute_types drop foreign key borrower_attribute_types_ibfk_1; ALTER TABLE borrower_attribute_types ADD CONSTRAINT category_code_fk FOREIGN KEY (category_code) REFERENCES categories(categorycode); RUN updatedatabase again; check show create table
>---
> .../data/mysql/atomicupdate/bug_30899.pl      | 57 +++++++++++++++++++
> installer/data/mysql/db_revs/211105004.pl     |  2 +-
> 2 files changed, 58 insertions(+), 1 deletion(-)
> create mode 100755 installer/data/mysql/atomicupdate/bug_30899.pl
>
>diff --git a/installer/data/mysql/atomicupdate/bug_30899.pl b/installer/data/mysql/atomicupdate/bug_30899.pl
>new file mode 100755
>index 0000000000..4701bc4690
>--- /dev/null
>+++ b/installer/data/mysql/atomicupdate/bug_30899.pl
>@@ -0,0 +1,57 @@
>+use Modern::Perl;
>+
>+return {
>+    bug_number => 30899,
>+    description => "Check borrower_attribute_types FK constraint (30449 follow-up)",
>+    up => sub {
>+        my ($args) = @_;
>+        my ($dbh, $out) = @$args{qw(dbh out)};
>+
>+        if( foreign_key_exists('borrower_attribute_types', 'category_code_fk') ) {
>+            $dbh->do( q|ALTER TABLE borrower_attribute_types DROP FOREIGN KEY category_code_fk| );
>+            if( index_exists('borrower_attribute_types', 'category_code_fk') ) {
>+                $dbh->do( q|ALTER TABLE borrower_attribute_types DROP INDEX category_code_fk| );
>+            }
>+        }
>+
>+        if (
>+            !foreign_key_exists(
>+                'borrower_attribute_types', 'borrower_attribute_types_ibfk_1'
>+            )
>+          )
>+        {
>+
>+            my $sth = $dbh->prepare(
>+                q{
>+                SELECT category_code
>+                FROM borrower_attribute_types
>+                WHERE category_code NOT IN (SELECT categorycode FROM categories);
>+            }
>+            );
>+
>+            $sth->execute;
>+
>+            my @invalid_categories;
>+            while ( my $row = $sth->fetchrow_arrayref() ) {
>+                push( @invalid_categories, $row->[0] );
>+            }
>+
>+            if (@invalid_categories) {
>+                die "The 'borrower_attribute_types' table contains "
>+                  . "references to invalid category codes: "
>+                  . join( ', ', @invalid_categories );
>+            }
>+
>+            if ( !index_exists( 'borrower_attribute_types', 'category_code' ) )
>+            {
>+                $dbh->do(q|
>+                    ALTER TABLE borrower_attribute_types ADD INDEX category_code (category_code)
>+                |);
>+            }
>+            $dbh->do(q|
>+                ALTER TABLE borrower_attribute_types
>+                    ADD CONSTRAINT borrower_attribute_types_ibfk_1 FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`)
>+            |);
>+        }
>+    },
>+};
>diff --git a/installer/data/mysql/db_revs/211105004.pl b/installer/data/mysql/db_revs/211105004.pl
>index b9c2f73eb5..eb0431b180 100755
>--- a/installer/data/mysql/db_revs/211105004.pl
>+++ b/installer/data/mysql/db_revs/211105004.pl
>@@ -8,7 +8,7 @@ return {
>         my ($dbh, $out) = @$args{qw(dbh out)};
> 
>         if( foreign_key_exists('borrower_attribute_types', 'category_code_fk') ) {
>-            $dbh->do( q|ALTER TABLE borrower_attribute_types DROP CONSTRAINT category_code_fk| );
>+            $dbh->do( q|ALTER TABLE borrower_attribute_types DROP FOREIGN KEY category_code_fk| );
>             if( index_exists('borrower_attribute_types', 'category_code_fk') ) {
>                 $dbh->do( q|ALTER TABLE borrower_attribute_types DROP INDEX category_code_fk| );
>             }
>-- 
>2.20.1

Actions: View | Diff | Splinter Review

Attachments on bug 30899: 135779 | 135780 | 135783 | 135790