From 53a50118ae5f4fdb83c4318c4acf5514ef3508ab Mon Sep 17 00:00:00 2001 From: Pedro Amorim Date: Thu, 12 Oct 2023 11:10:56 +0000 Subject: [PATCH] Bug 35044: DB changes + atomicupdate file Add the 'repeatable' column to additional_fields Remove the unique key from additional_field_values as we will now be allowing for repeated instances of the same field_id + record_id Signed-off-by: Martin Renvoize Signed-off-by: Julian Maurice Signed-off-by: Katrin Fischer --- .../data/mysql/atomicupdate/bug_35044.pl | 45 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 3 +- 2 files changed, 47 insertions(+), 1 deletion(-) create mode 100755 installer/data/mysql/atomicupdate/bug_35044.pl diff --git a/installer/data/mysql/atomicupdate/bug_35044.pl b/installer/data/mysql/atomicupdate/bug_35044.pl new file mode 100755 index 0000000000..901f38e3be --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_35044.pl @@ -0,0 +1,45 @@ +use Modern::Perl; + +return { + bug_number => "35044", + description => "Add repeatable option to additional_fields", + up => sub { + my ($args) = @_; + my ( $dbh, $out ) = @$args{qw(dbh out)}; + + unless ( column_exists( 'additional_fields', 'repeatable' ) ) { + $dbh->do( + q{ + ALTER TABLE additional_fields ADD COLUMN `repeatable` tinyint(1) NOT NULL DEFAULT 0 COMMENT + 'does the field allow more than one option?' AFTER searchable + } + ); + say $out "Added repeatable column to additional_fields table"; + } + + if ( unique_key_exists ('additional_field_values', 'field_record') ) { + # Need to drop foreign key so that we can then drop the unique key + $dbh->do( + q{ + ALTER TABLE additional_field_values DROP FOREIGN KEY afv_fk + } + ); + + # Drop the unique key + $dbh->do( + q{ + ALTER TABLE additional_field_values DROP INDEX field_record; + } + ); + + # Restore foreign key constraint + $dbh->do( + q{ + ALTER TABLE additional_field_values ADD CONSTRAINT `afv_fk` FOREIGN KEY(`field_id`) REFERENCES + `additional_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE + } + ); + say $out "Removed UNIQUE KEY `field_record` (`field_id`,`record_id`) from the additional_field_values table"; + } + }, +}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 753ae3cc57..f3dd950328 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -245,7 +245,7 @@ CREATE TABLE `additional_field_values` ( `record_id` int(11) NOT NULL COMMENT 'record_id', `value` varchar(255) NOT NULL DEFAULT '' COMMENT 'value for this field', PRIMARY KEY (`id`), - UNIQUE KEY `field_record` (`field_id`,`record_id`), + KEY `afv_fk` (`field_id`), 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 */; @@ -265,6 +265,7 @@ CREATE TABLE `additional_fields` ( `marcfield` varchar(16) NOT NULL DEFAULT '' COMMENT 'contains the marc field to copied into the record', `marcfield_mode` enum('get','set') NOT NULL DEFAULT 'get' COMMENT 'mode of operation (get or set) for marcfield', `searchable` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'is the field searchable?', + `repeatable` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'is the field repeatable?', PRIMARY KEY (`id`), UNIQUE KEY `fields_uniq` (`tablename`(191),`name`(191)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2.39.5