Attachment 164163 Details for Bug 35044 – Bug 35044: DB changes + atomicupdate file

[patch] Bug 35044: DB changes + atomicupdate file

Bug-35044-DB-changes--atomicupdate-file.patch (text/plain), 3.95 KB, created by Pedro Amorim on 2024-04-01 09:14:37 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Pedro Amorim

Created: 2024-04-01 09:14:37 UTC

Size: 3.95 KB

patch

obsolete

>From a9ef96ea7268b0759a36e42529ff61bd922adc44 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 ea540c0819d..1b0e0a4b42f 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

Actions: View | Diff | Splinter Review

Attachments on bug 35044: 157324 | 157325 | 157326 | 157327 | 157328 | 157329 | 157330 | 157331 | 157332 | 157333 | 157334 | 158113 | 158498 | 158499 | 158500 | 158501 | 158502 | 158503 | 158504 | 158505 | 158506 | 158507 | 158508 | 158857 | 158858 | 158859 | 158860 | 158861 | 158862 | 158863 | 158864 | 158865 | 158866 | 158867 | 158868 | 159268 | 160177 | 160178 | 160179 | 160180 | 160181 | 160182 | 160183 | 160184 | 160185 | 160186 | 160187 | 160188 | 160189 | 164162 | 164163 | 164164 | 164165 | 164166 | 164167 | 164168 | 164169 | 164170 | 164171 | 164172 | 164173 | 164174 | 167690 | 168537 | 168538 | 168539 | 168540 | 168541 | 168542 | 168543 | 168544 | 168545 | 168546 | 168547 | 168548 | 169545 | 169546 | 169547 | 169603 | 169604 | 169605 | 169606 | 169607 | 169643 | 169644 | 169645 | 169646 | 169647 | 169648 | 169649 | 169650 | 169651 | 169652 | 169653 | 169654 | 169655 | 169656 | 169657 | 169658 | 169659 | 169660 | 169661 | 169662 | 169663 | 169664 | 169665 | 169666 | 169667 | 169668 | 169669 | 169670 | 169671 | 169672 | 169673 | 169674 | 169675 | 169676 | 169677 | 169678 | 169679 | 169680 | 169681 | 169682 | 169683 | 169684 | 169697 | 170501