From 316d3b8f8a5cb14067df7e7d765777fa87eb7e6e Mon Sep 17 00:00:00 2001 From: Martin Renvoize 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. --- .../data/mysql/atomicupdate/bug_35451.pl | 41 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 4 +- 2 files changed, 44 insertions(+), 1 deletion(-) create mode 100644 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 100644 index 00000000000..3c9951b67dc --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_35451.pl @@ -0,0 +1,41 @@ +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 3bf15c1007d..bb9d9a4772b 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -247,10 +247,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 */; @@ -6755,4 +6757,4 @@ CREATE TABLE `zebraqueue` ( /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2024-11-25 12:13:27 +-- Dump completed on 2024-11-25 12:13:27 \ No newline at end of file -- 2.48.1