[Dbix-class] Advices needed on creating multilingual application

mbit at ukr.net mbit at ukr.net
Wed Sep 19 09:50:26 GMT 2007


Hello.

I need an advice on writing multilangual web-application. Question is not
about i18n or localization, that's was described here many times and i got it
worked pretty easy :) The thing is in organizing database storage.

Let's assume an easy application. We have a blog. But there are three versions
int three languages: en, de and ru for example. But when we don't have a
translation for the en part, we show original part. This way first we add
original post in de, and then if we translate that part we add translation. And
when a user views en version of the site he sees translated part. Otherwize he
sees de version. This way in all three languages we have complete website, just
not everything is always translated, but we know what changed and was added in
all languages.

I am using DBIx::Class and i created classes like this:

__PACKAGE__->table( 'blog' );

__PACKAGE__->add_columns(
    recordid => {
        data_type         => 'integer',
        is_auto_increment => 1,
        default_value     => undef,
        size              => undef,
        is_nullable       => 0
    },
    lang => {
        data_type     => 'character varying',
        default_value => undef,
        size          => 2,
        is_nullable   => 0
    },
    author => {
        data_type     => 'character varying',
        default_value => undef,
        size          => undef,
        is_nullable   => 0,
        widget        => { label => 'Author' }
    }
);

__PACKAGE__->set_primary_key( qw/ recordid / );

__PACKAGE__->has_many(
    'i18n' => 'Blog::I18N',
    'recordid', { accessor => 'single' }
);

This class holds information that is not going to ba translated. Column `lang'
holds oginal language. Then we have Blog::I18N class:

__PACKAGE__->table( 'blog_i18n' );

__PACKAGE__->add_columns(
    i18nid => {
        data_type         => 'integer',
        is_auto_increment => 1,
        default_value     => undef,
        size              => undef,
        is_nullable       => 0
    },
    recordid => {
        data_type     => 'integer',
        default_value => undef,
        size          => undef,
        is_nullable   => 0
    },
    lang => {
        data_type     => 'character varying',
        default_value => undef,
        size          => 2,
        is_nullable   => 0
    },
    title => {
        data_type     => 'character varying',
        default_value => undef,
        size          => undef,
        is_nullable   => 0,
    },
    content => {
        data_type     => 'text',
        default_value => undef,
        size          => undef,
        is_nullable   => 0,
    }
);

__PACKAGE__->set_primary_key( qw/ i18nid / );

__PACKAGE__->belongs_to(
    'record' => 'Blog',
    'recordid', { join_type => 'left' }
);

So this class holds all the translations. For easy fetching columns i wrote
DBIx::Class::ResultSetI18n, where ->search method, for example, is defined as
follows:

sub search {
    my $s = shift;
    my ( $cond, $attrs ) = @_;

    $cond ||= {};
    $attrs ||= {};

    my $lang = delete $cond->{ lang };

    return $s->next::method( @_ ) unless defined $lang;

    if ( $attrs->{ join } ) {
        if ( ref $attrs->{ join } eq 'ARRAY' ) {
            push @{ $attrs->{ join } }, 'i18n';
        } else {
            $attrs->{ join } = [ 'i18n', $attrs->{ join } ];
        }
    } else {
        $attrs->{ join } = 'i18n';
    }

    my ( $primary_key ) = $s->result_source->primary_columns;

    my $alias = $s->{attrs}->{alias} || 'me';

    $attrs->{ group_by } ||= [ "$alias.$primary_key" ];

    return $s->search(
        {
            -and => [ $s->_if_i18n( $lang ) ],
            %$cond
        },
        $attrs
    );
}

You've noticed ->_if_i18n() method. It is created to deside do have a
translation or not. It is defined as:

sub _if_i18n {
    my ( $s, $lang ) = @_;

    my $alias = $s->{attrs}->{alias} || 'me';

    $lang = defined $lang ? ( '\'' . $lang . '\'' ) : "$alias.lang";

    my $table = $s->related_resultset( 'i18n' )->result_source->name;
    my ( $key ) = $s->result_source->primary_columns;

    return \"IF((SELECT i18nid FROM $table WHERE \
            $key=$alias.$key AND lang=$lang), \ 
            i18n.lang=$lang, $alias.lang=i18n.lang)"
}

This way in my application i can fetch data like this (sorry for the
Catalyst model :)

my $rs = $c->model( 'Blog' )->search(
    { lang => $c->language },
    {
        order_by => 'i18n.cdtime DESC'
    }
);

My question is if there is a better approach to do the same stuff. Because i
faced a lot of difficulties in prefetching related object like creator where i
had to do something like this:

prefetch => { i18n => [ qw/ creator / ] }

Or worse when we have a related object that has a translation too... That's a
big mess for me.

And it is still not very easy to maintain. Also there is an overhead in every
database fetch.

I would appreciate any advice.

Thanx

-- 
vti -- Viacheslav Tikhanovskii




More information about the DBIx-Class mailing list