Attachment 173147 Details for Bug 35451 – Bug 35451: Add record_table to additional_field_values

[patch] Bug 35451: Add record_table to additional_field_values

Bug-35451-Add-recordtable-to-additionalfieldvalues.patch (text/plain), 3.13 KB, created by Martin Renvoize (ashimema) on 2024-10-22 13:46:00 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Martin Renvoize (ashimema)

Created: 2024-10-22 13:46:00 UTC

Size: 3.13 KB

patch

obsolete

>From a12b6aa2a24d372e20f8b11b60487118f06909a4 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.
>---
> .../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 00000000000..82a8c80371f
>--- /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 31f0828a24e..0c29e31aac0 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.47.0

Actions: View | Diff | Splinter Review

Attachments on bug 35451: 159390 | 159391 | 159392 | 159393 | 159394 | 159747 | 159748 | 159749 | 159750 | 159751 | 173147 | 173148 | 173149 | 173150 | 173151 | 173421 | 173422 | 173423 | 173424 | 173425