From f361338b0163693fe17baede964d037db69eab59 Mon Sep 17 00:00:00 2001 From: Julian Maurice Date: Tue, 3 Dec 2024 14:22:29 +0100 Subject: [PATCH] Bug 38136: Use a single table for all translations We lose data integrity but we gain simplicity. Automatic deletion of translations is handled by the DBIC `cascade_delete` feature, so integrity should be fine as long as no raw SQL update/delete operation is involved. --- Koha/Schema/Component/Localization.pm | 78 +++++++++++-------- Koha/Schema/Result/Itemtype.pm | 25 +----- .../data/mysql/atomicupdate/bug-38136.pl | 32 +++----- installer/data/mysql/kohastructure.sql | 20 +---- svc/localization | 1 + 5 files changed, 59 insertions(+), 97 deletions(-) diff --git a/Koha/Schema/Component/Localization.pm b/Koha/Schema/Component/Localization.pm index 9c485efa2a..6abce2f862 100644 --- a/Koha/Schema/Component/Localization.pm +++ b/Koha/Schema/Component/Localization.pm @@ -98,25 +98,42 @@ use Carp; use base qw(DBIx::Class); +=head2 localization_add_relationships + +Add relationships to the localization table + +=cut + sub localization_add_relationships { - my ($class, $rel_name, $fk_column, $pk_column, @properties) = @_; + my ($class, $pk_column, @properties) = @_; - my $rel_info = $class->relationship_info($rel_name); - unless ($rel_info) { - confess "Unknown relation '$rel_name'"; - } + my $rel_class = 'Koha::Schema::Result::Localization'; + my $source_name = $class =~ s/.*:://r; $class->has_many( 'localizations', - $rel_info->{class}, - { "foreign.$fk_column" => "self.$pk_column" }, - { cascade_copy => 0, cascade_delete => 0, cascade_update => 0 }, + $rel_class, + sub { + my ($args) = @_; + + return ( + { + "$args->{foreign_alias}.code" => { -ident => "$args->{self_alias}.$pk_column" }, + "$args->{foreign_alias}.entity" => $source_name, + }, + !$args->{self_result_object} ? () : { + "$args->{foreign_alias}.code" => $args->{self_result_object}->get_column($pk_column), + "$args->{foreign_alias}.entity" => $source_name, + }, + ); + }, + { cascade_copy => 0, cascade_delete => 1, cascade_update => 0 }, ); foreach my $property (@properties) { $class->might_have( - $property . "_localization", - $rel_info->{class}, + $property . '_localization', + $rel_class, sub { my ($args) = @_; @@ -127,18 +144,17 @@ sub localization_add_relationships { return ( { - "$args->{foreign_alias}.$fk_column" => { -ident => "$args->{self_alias}.$pk_column" }, - "$args->{foreign_alias}.property" => $property, - "$args->{foreign_alias}.lang" => $lang, + "$args->{foreign_alias}.code" => { -ident => "$args->{self_alias}.$pk_column" }, + "$args->{foreign_alias}.entity" => $source_name, + "$args->{foreign_alias}.property" => $property, + "$args->{foreign_alias}.lang" => $lang, }, !$args->{self_result_object} ? () : { - "$args->{foreign_alias}.$fk_column" => $args->{self_result_object}->get_column($pk_column), - "$args->{foreign_alias}.property" => $property, - "$args->{foreign_alias}.lang" => $lang, + "$args->{foreign_alias}.code" => $args->{self_result_object}->get_column($pk_column), + "$args->{foreign_alias}.entity" => $source_name, + "$args->{foreign_alias}.property" => $property, + "$args->{foreign_alias}.lang" => $lang, }, - !$args->{foreign_values} ? () : { - "$args->{foreign_alias}.$fk_column" => $args->{foreign_values}->{$pk_column}, - } ); }, { cascade_copy => 0, cascade_delete => 0, cascade_update => 0 }, @@ -146,22 +162,21 @@ sub localization_add_relationships { $class->has_many( $property . '_localizations', - $rel_info->{class}, + $rel_class, sub { my ($args) = @_; return ( { - "$args->{foreign_alias}.$fk_column" => { -ident => "$args->{self_alias}.$pk_column" }, - "$args->{foreign_alias}.property" => $property, + "$args->{foreign_alias}.code" => { -ident => "$args->{self_alias}.$pk_column" }, + "$args->{foreign_alias}.entity" => $source_name, + "$args->{foreign_alias}.property" => $property, }, !$args->{self_result_object} ? () : { - "$args->{foreign_alias}.$fk_column" => $args->{self_result_object}->get_column($pk_column), - "$args->{foreign_alias}.property" => $property, + "$args->{foreign_alias}.code" => $args->{self_result_object}->get_column($pk_column), + "$args->{foreign_alias}.entity" => $source_name, + "$args->{foreign_alias}.property" => $property, }, - !$args->{foreign_values} ? () : { - "$args->{foreign_alias}.$fk_column" => $args->{foreign_values}->{$pk_column}, - } ); }, { cascade_copy => 0, cascade_delete => 0, cascade_update => 0 }, @@ -180,14 +195,9 @@ sub localization { unless ($localizations_map) { $localizations_map = {}; - my $rel_info = $self->relationship_info('localizations'); - my $rel_source = $rel_info->{class} =~ s/.*:://r; - - my ($fk_column) = map { s/^foreign\.//r } keys %{ $rel_info->{cond} }; - - my $localizations = $result_source->schema->resultset($rel_source)->search({ lang => $lang }); + my $localizations = $result_source->schema->resultset('Localization')->search({ lang => $lang }); while (my $localization = $localizations->next) { - my $fk = $localization->get_column($fk_column); + my $fk = $localization->get_column('code'); my $localization_key = sprintf('%s:%s', $fk, $localization->property); $localizations_map->{$localization_key} = $localization->translation; } diff --git a/Koha/Schema/Result/Itemtype.pm b/Koha/Schema/Result/Itemtype.pm index d532e7a750..a29f154722 100644 --- a/Koha/Schema/Result/Itemtype.pm +++ b/Koha/Schema/Result/Itemtype.pm @@ -293,21 +293,6 @@ __PACKAGE__->has_many( { cascade_copy => 0, cascade_delete => 0 }, ); -=head2 itemtypes_localizations - -Type: has_many - -Related object: L - -=cut - -__PACKAGE__->has_many( - "itemtypes_localizations", - "Koha::Schema::Result::ItemtypesLocalization", - { "foreign.itemtype" => "self.itemtype" }, - { cascade_copy => 0, cascade_delete => 0 }, -); - =head2 old_reserves Type: has_many @@ -359,8 +344,8 @@ __PACKAGE__->has_many( ); -# Created by DBIx::Class::Schema::Loader v0.07051 @ 2024-10-25 13:25:14 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:jd0dYE700dpg1IiRnfbcEg +# Created by DBIx::Class::Schema::Loader v0.07052 @ 2024-12-03 14:37:10 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:l3ycM1L5bmCeM0Pby8GceA __PACKAGE__->add_columns( '+automatic_checkin' => { is_boolean => 1 }, @@ -372,11 +357,7 @@ __PACKAGE__->add_columns( ); __PACKAGE__->load_components('+Koha::Schema::Component::Localization'); -__PACKAGE__->localization_add_relationships( - 'itemtypes_localizations', - 'itemtype' => 'itemtype', - 'description' -); +__PACKAGE__->localization_add_relationships('itemtype', 'description'); sub koha_object_class { 'Koha::ItemType'; diff --git a/installer/data/mysql/atomicupdate/bug-38136.pl b/installer/data/mysql/atomicupdate/bug-38136.pl index 0585f03087..1113b6e559 100644 --- a/installer/data/mysql/atomicupdate/bug-38136.pl +++ b/installer/data/mysql/atomicupdate/bug-38136.pl @@ -3,34 +3,20 @@ use Koha::Installer::Output qw(say_warning say_failure say_success say_info); return { bug_number => '38136', - description => 'Add itemtypes_localizations table', + description => 'Add localization.property', up => sub { my ($args) = @_; my ( $dbh, $out ) = @$args{qw(dbh out)}; - unless ( TableExists('itemtypes_localizations') ) { - $dbh->do( " - CREATE TABLE `itemtypes_localizations` ( - `itemtypes_localizations_id` int(11) NOT NULL AUTO_INCREMENT, - `itemtype` varchar(10) NOT NULL, - `property` varchar(100) NOT NULL, - `lang` varchar(25) NOT NULL, - `translation` mediumtext DEFAULT NULL, - PRIMARY KEY (`itemtypes_localizations_id`), - UNIQUE KEY `itemtype_property_lang` (`itemtype`, `property`, `lang`), - CONSTRAINT `itemtypes_localizations_itemtype_fk` - FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) - ON DELETE CASCADE ON UPDATE CASCADE - ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; - " ); + unless (column_exists('localization', 'property')) { + $dbh->do("alter table `localization` add `property` varchar(100) null after `code`"); + $dbh->do("update `localization` set `property` = 'description', entity = 'Itemtype'"); + $dbh->do("alter table `localization` modify `property` varchar(100) not null"); + $dbh->do("alter table `localization` drop key `entity_code_lang`"); + $dbh->do("alter table `localization` add unique key `entity_code_property_lang` (`entity`, `code`, `property`, `lang`)"); - if ( TableExists('localization') ) { - $dbh->do( " - INSERT INTO `itemtypes_localizations` (`itemtype`, `property`, `lang`, `translation`) - SELECT `code`, 'description', `lang`, `translation` FROM `localization` WHERE `entity` = 'itemtypes' - " ); - $dbh->do('DROP TABLE localization'); - } + say_success($out, 'Added column localization.property and updated localization.entity values'); } + }, }; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 6cf1a35210..6fc19c06c2 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -4180,23 +4180,6 @@ CREATE TABLE `itemtypes_branches` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; --- --- Table structure for table `itemtypes_localizations` --- - -CREATE TABLE `itemtypes_localizations` ( - `itemtypes_localizations_id` int(11) NOT NULL AUTO_INCREMENT, - `itemtype` varchar(10) NOT NULL, - `property` varchar(100) NOT NULL, - `lang` varchar(25) NOT NULL, - `translation` mediumtext DEFAULT NULL, - PRIMARY KEY (`itemtypes_localizations_id`), - UNIQUE KEY `itemtype_property_lang` (`itemtype`, `property`, `lang`), - CONSTRAINT `itemtypes_localizations_itemtype_fk` - FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) - ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; - -- -- Table structure for table `keyboard_shortcuts` -- @@ -4423,10 +4406,11 @@ CREATE TABLE `localization` ( `localization_id` int(11) NOT NULL AUTO_INCREMENT, `entity` varchar(16) NOT NULL, `code` varchar(64) NOT NULL, + `property` varchar(100) NOT NULL, `lang` varchar(25) NOT NULL COMMENT 'could be a foreign key', `translation` mediumtext DEFAULT NULL, PRIMARY KEY (`localization_id`), - UNIQUE KEY `entity_code_lang` (`entity`,`code`,`lang`) + UNIQUE KEY `entity_code_property_lang` (`entity`,`code`,`property`,`lang`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; diff --git a/svc/localization b/svc/localization index 6638dfff3c..d07d3b2635 100755 --- a/svc/localization +++ b/svc/localization @@ -60,6 +60,7 @@ sub add_translation { my $localization = $row->create_related( "${property}_localizations", { + entity => $source, lang => $lang, translation => $translation, } -- 2.39.2