From 97e9a408d50da9c882913cb40a8edc480aed502c Mon Sep 17 00:00:00 2001
From: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Date: Thu, 30 Nov 2023 11:31:02 +0000
Subject: [PATCH] Bug 35451: Add record_table to additional_field_values

This patch updates the database to include a record_table field in the
additional_field_values table and adds an index on that field.

This should facilitate more performant joins.

Signed-off-by: David Nind <david@davidnind.com>
---
 .../data/mysql/atomicupdate/bug_35451.pl      | 35 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  2 ++
 2 files changed, 37 insertions(+)
 create mode 100755 installer/data/mysql/atomicupdate/bug_35451.pl

diff --git a/installer/data/mysql/atomicupdate/bug_35451.pl b/installer/data/mysql/atomicupdate/bug_35451.pl
new file mode 100755
index 0000000000..82a8c80371
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_35451.pl
@@ -0,0 +1,35 @@
+use Modern::Perl;
+
+return {
+    bug_number  => "35451",
+    description => "Add record_table field and index to additional_field_values",
+    up          => sub {
+        my ($args) = @_;
+        my ( $dbh, $out ) = @$args{qw(dbh out)};
+
+        unless ( column_exists('additional_field_values', 'record_table') ) {
+            $dbh->do(q{
+                ALTER TABLE additional_field_values ADD record_table varchar(255) NOT NULL DEFAULT '' COMMENT 'tablename of the related record' AFTER field_id
+            });
+
+            $dbh->do(q{
+                UPDATE
+                    additional_field_values AS v
+                INNER JOIN
+                    additional_fields AS f ON f.id = v.field_id
+                SET
+                    v.record_table = f.tablename
+            });
+            say $out "Added column 'additional_field_values.record_table'";
+        }
+
+        unless ( index_exists( 'additional_field_values', 'record_table' ) ) {
+            $dbh->do(q{
+                ALTER TABLE `additional_field_values`
+                ADD KEY `record_table` (`record_table`)
+            });
+
+            say $out "Added index 'record_table' to 'additional_field_values'";
+        }
+    },
+};
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 49c1aeb59d..9656854f76 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -242,10 +242,12 @@ DROP TABLE IF EXISTS `additional_field_values`;
 CREATE TABLE `additional_field_values` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key identifier',
   `field_id` int(11) NOT NULL COMMENT 'foreign key references additional_fields(id)',
+  `record_table` varchar(255) NOT NULL DEFAULT '' COMMENT 'tablename of the related record',
   `record_id` int(11) NOT NULL COMMENT 'record_id',
   `value` varchar(255) NOT NULL DEFAULT '' COMMENT 'value for this field',
   PRIMARY KEY (`id`),
   KEY `afv_fk` (`field_id`),
+  KEY `record_table` (`record_table`),
   CONSTRAINT `afv_fk` FOREIGN KEY (`field_id`) REFERENCES `additional_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
-- 
2.39.5