[Catalyst] Multilingual Site

[Joshua McAdams]

> What about user generated content? For example: I have a site with three
> languages "en,de,fr" and the user can make input in one or all of these
> languages.
>
> Is it ok, if I have a row in the DB for each language?

What I've done in the past with some degree of success is just to use
a phrasebook table.  Basically I have three tables in the system for
phrase lookups:

create table phrase_key (
  id int unsigned primary key auto_increment,
  key varchar(255) not null unique key
);

create table language (
  id smallint unsigned primary key auto_increment,
  language varchar(100) not null unique key
);

create table phrase (
  phrase_key_id int unsigned not null references phrase_key(id),
  language_id smallint unsigned not null references language(id),
  phrase varchar(255) not null,
  primary key (phrase_key_id, language_id)
);

Then, for each column that needed to be fed from the phrasebook I
would store the phrase key instead of the actual phrase.  For
instance, instead of having a book.title column, I would have a
book.title_pb column and then create a method in my DBIC wrapper
called 'title' that actually did the lookup.

Also, I put something like this in my main schema file:

<code>
sub fetch_phrase {
    my ( $self, $phrase_key, $language ) = @_;

    get_logger->error_die('a phrase key is required')
      unless defined $phrase_key;

    my $phrase = $self->resultset('Phrases')->search(
        {
            'phrase_key.name' => $phrase_key,
            'language.id'   =>
              ( $language || $self->{__language__} ||
get_logger->error_die("language_id is required") )
        },
        { join => [qw/phrase_key language/] }
    )->first;

    if ( not defined $phrase ) {

        get_logger->warn("no phrase returned for phrase key [$phrase_key]");
        return q{};
    }

    return $phrase->phrase;
}
</code>

I'm not sure how 'correct' this was, but it worked.