From 7192efd2717e1127f693491bf59e20c64566b53e 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: [22.05.x] Follow-up for dbrev 211200041 from bug
 30449

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/211200041.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/211200041.pl b/installer/data/mysql/db_revs/211200041.pl
index b9c2f73eb5..eb0431b180 100755
--- a/installer/data/mysql/db_revs/211200041.pl
+++ b/installer/data/mysql/db_revs/211200041.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.39.5