From c99a9c8f835bacb7a339bb5669641443e6b76d41 Mon Sep 17 00:00:00 2001
From: Pedro Amorim <pedro.amorim@ptfs-europe.com>
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 <martin.renvoize@ptfs-europe.com>
---
 .../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 00000000000..7aadc781258
--- /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 0b59ad8d85c..f7078124876 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.43.0