From ff71224e75f4ea9045345bd9d7ad3170a300fb6d 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 --- .../atomicupdate/atomic_additional_fields.pl | 45 +++++++++++++++++++ installer/data/mysql/kohastructure.sql | 3 +- 2 files changed, 47 insertions(+), 1 deletion(-) create mode 100755 installer/data/mysql/atomicupdate/atomic_additional_fields.pl diff --git a/installer/data/mysql/atomicupdate/atomic_additional_fields.pl b/installer/data/mysql/atomicupdate/atomic_additional_fields.pl new file mode 100755 index 0000000000..7aadc78125 --- /dev/null +++ b/installer/data/mysql/atomicupdate/atomic_additional_fields.pl @@ -0,0 +1,45 @@ +use Modern::Perl; + +return { + bug_number => "xxx", + 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', 'afv_fk') ) { + # 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 09a6adcc47..78111b8527 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -244,7 +244,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 */; @@ -264,6 +264,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.30.2