NAME
Rose::DBx::Object::I18N - set of modules to deal with multilingual database
SYNOPSIS
# create user with multilingual data
my $u = User->new(
name => 'ppp',
orig_lang => 'en',
signature => 'hello'
);
$u->save();
# load german translation
$u->load( i18n => 'de' );
$u->signature; # hello
# retrieve available translations
$u->i18n_available_translations; # undef
# update translation
$u->i18n->signature( 'hallo' );
$u->save();
$u->i18n_available_translations; # [ 'en' ]
# update original
$u->i18n( 'en' )->signature( 'hi' );
$u->save();
# check if original translation is loaded
$u->is_original_loaded; # 1
$u->i18n( 'de' );
# delete loaded translation
$u->delete_i18n();
$u->i18n_available_translations; # undef
$u->i18n( 'de' )->signature; # hiDESCRIPTION
There are different ways to deal with multilingual problem. We will look at a few of them.
Separate Data For Each Language
articles
+----+-----------+----------+-------+
| id | author_id | language | title |
+----+-----------+----------+-------+
| 1 | 1 | en | foo |
+----+-----------+----------+-------+
| 2 | 1 | de | bar |
+----+-----------+----------+-------+
| 3 | 2 | en | foo |
+----+-----------+----------+-------+This is a easiest one to imagine. You have all data separated. If user wants something in English just give him what he wants. There is no relation between data, so if nothing is found in English there is no way how to know if there is something in German, etc.
Also, the data that is shared between translations, like link, author id, something else that can't be translated should be synchronized on every change in other translations.
The good is the speed. No joins, no lookups in other tables, etc.
Static Data, Language Data, Translation Data
articles
+----+-----------+-------------------+
| id | author_id | original_language |
+----+-----------+-------------------+
| 1 | 1 | en |
+----+-----------+-------------------+
| 2 | 2 | de |
+----+-----------+-------------------+
languages
+------------+---------+----------+
| article_id | i18n_id | language |
+------------+---------+----------+
| 1 | 1 | en |
+------------+---------+----------+
| 1 | 2 | de |
+------------+---------+----------+
| 2 | 3 | de |
+------------+---------+----------+
i18n
+----+-------+
| id | title |
+----+-------+
| 1 | foo |
+----+-------+
| 2 | bar |
+----+-------+
| 3 | foo |
+----+-------+Here we have three tables. One is for static data that is not going to be translated, one is for languages that will hold what language is mapped to what translation in translations table and the translation table, that holds translatable information.
The problem is that there too many thins to do even for the one request. We should make 3 joins and one IF statement in a join.
One Static, Many Translations
articles
+----+-----------+-------------------+
| id | author_id | original_language |
+----+-----------+-------------------+
| 1 | 1 | en |
+----+-----------+-------------------+
| 2 | 2 | de |
+----+-----------+-------------------+
i18n
+------------+----------+-------+
| article_id | language | title |
+------------+----------+-------+
| 1 | en | foo |
+------------+----------+-------+
| 1 | de | bar |
+------------+----------+-------+
| 2 | de | foo |
+------------+----------+-------+Current approach for Rose::DBx::Object::I18N is to have two tables, one is for the static data, and another for all translations.
Rose::DBx::Object::I18N
Plugging in Rose::DBx::Object::I18N is simply, instead of subclassing from Rose::DB::Object use this namespace. But you must have two tables: one for the Static data and another for Translation data.
package DB::Object::I18N;
use strict;
use base qw/ Rose::DBx::Object::I18N / ;
use DB;
sub init_db {
my $self = shift;
DB->new_or_cached( @_ );
}
sub i18n_languages {
my @languages = qw/ en de ru /;
wantarray ? @languages : \@languages;
}Class for Static data can look like this.
package User;
use strict;
use base qw(DB::Object::I18N::Static);
use Rose::DBx::Object::I18N::Metadata;
sub meta_class { 'Rose::DBx::Object::I18N::Metadata' };
__PACKAGE__->meta->setup(
table => 'user',
columns => [
qw/ id name /,
orig_lang => { type => 'i18n_language' }
],
primary_key_columns => [ qw/ id / ],
unique_key => [ qw/ name / ],
relationships => [
user_i18n => {
type => 'one to many',
class => 'UserI18N',
column_map => { id => 'user_id' }
}
],
i18n_translation_rel_name => 'user_i18n'
);And class for Translation
package UserI18N;
use strict;
use base qw/ DB::Object::I18N::Translation /;
use Rose::DBx::Object::I18N::Metadata;
sub meta_class { 'Rose::DBx::Object::I18N::Metadata' };
__PACKAGE__->meta->setup(
table => 'user_i18n',
columns => [
qw/
i18nid
user_id
signature
/,
lang => { type => 'i18n_language' },
istran => { type => 'i18n_is_translation' }
],
primary_key_columns => [ 'i18nid' ],
foreign_keys => [
user => {
class => 'User',
key_columns => { user_id => 'id' },
rel_type => 'many to one',
},
],
i18n_static_rel_name => 'user'
);There is also I18N::Manager that can help you with selection i18n data.
package User::Manager;
use strict;
use base 'Rose::DBx::Object::I18N::Manager';
sub object_class { 'User' }
__PACKAGE__->make_manager_methods( 'users' );METHODS
new
Rose::DB::Object init method is overloaded, so you can use one of these examples:
my $u = User->new(
name => 'vti',
orig_lang => 'en',
user_i18n => { signature => 'hello' }
);
or
my $u = User->new(
name => 'fake',
orig_lang => 'en',
signature => 'hello'
);
or even
my $u = User->new(
name => 'foo',
orig_lang => 'en'
);
and then
$u->user_i18n( { signature => 'hello' } );save
CREATE
Data that is static is added to static table, then for each language translatable data is added to translations table with a flag (istran) that there is no translation.
UPDATE
If updating original language data update it and then synchronize with all translations that are not translations (the data is the same, istran flag is 0)
If updating translation set istran to 1 and update all columns as usual.
load
When you want to load default language ($ENV{LANG} or original) just load as you always do:
$u = User->new( id => 1 );
$u->load();When you want to load en translation:
$u = User->new( id => 1 );
$u->load( i18n => 'en' );i18n PARAM
Returns preloaded i18n object or, if the last was not found, preloads it taking the default language or language that is provided as a parameter.
$u = User->new( id => 1 );
# let's assume that the original language is English ('en').
$u->load();
$u->i18n->title; # title is in English
$u->i18n('de')->title; # title is in German
$u->i18n('en')->title; # title is back in Englishi18n_available_translations
Returns array reference of another available translations.
i18n_is_original_loaded
Returns if loaded translation is original.
not_translated_i18n
Return array reference of languages that have no translation.
delete_i18n
Delete currently loaded translation and loads original.
Rose::DBx::Object::I18N::Manager
On selection there is only one join, no need to do any logic selection, because we have all data ready for selection at the right place. If there was no translation, anyway data will be there, it will be original, because no translation was updated.
get_objects method is overloaded, so you don't have to provide query with the language selection and table to join, just use is transparently:
User::Manager->get_objects( i18n => 'en' );COPYRIGHT & LICENSE
Copyright 2008 Viacheslav Tikhanovskii, all rights reserved.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.