Thread: How can I easily and effectively support (language) translation of column values?

Hi,

I have some problems supporting "translatable columns" in a way that
is both efficient and comfortable for the programmer who has to write
SQL-Queries (we use iBatis)

Suppose I have a simple table that contains an id field and (amongst
others) a name field. Let's call this table foo. The names are english
names.

These english names need to be translatable (and the translations have
to be "by id", not "by name"; for example: the pair (1, 'foobar') has
to be translated as (1, 'barfoo'), but (2, 'foobar') might be (2,
'baz') and not (2, 'foobar')

I've tried to solve the problem in the following way (if you know of a
better method, please tell me)

I created a table foo_translations, that contains the columns foo_id,
lang_id, and (translated) name:

 SELECT * FROM foo WHERE id = 1;
  id |  name
 ----+--------
   1 | foobar

 SELECT * FROM foo_translations WHERE foo_id=1;
 foo_id | lang_id |  name
 --------+---------+--------
       1 |       1 | barfoo
       1 |       2 | boofoo

Now, whenever I create a query (I use iBatis and therefore more or
less write each query by hand), I can get the result I want by writing
something like this:

 SELECT foo.id, COALESCE(foo_translations.name, foo.name) AS name
FROM foo LEFT JOIN foo_translations ON foo.id=foo_id AND lang_id=?
WHERE id = ?

While this works, it's quite cumbersome having to do this for every
query that involves tables with "translatable names".

So my first idea was to create a view that will give me the correctly
translated fields, like

 CREATE VIEW foo1 AS SELECT foo.id, COALESCE(foo_translations.name,
foo.name) AS name FROM foo LEFT JOIN foo_translations ON foo.id =
foo_translations.foo_id AND foo_translations.lang_id = 1;

This way I could rewrite the last SELECT-statement (assuming the
language-code is 1) as simply

 SELECT * FROM foo1 WHERE id = ?

But this would mean I'd have to define (potentially) 50+ VIEWS for
every table with translatable fields and I'd have the create a new
SQL-Query everytime I use it (because I'd have to insert the language
code right after "FROM foo", and I don't think PreparedStatement (we
use Java) can handle this kind of placeholder "FROM foo?" )

So what I really need is a view that takes a parameter. Unfortunately,
it seems like these don't exist. I can work around it by using a
stored proc:

 CREATE FUNCTION foo(int) RETURNS SETOF foo AS $$ SELECT foo.id,
   COALESCE(foo_translations.name, foo.name) AS name FROM foo LEFT JOIN
   foo_translations ON foo.id=foo_id AND lang_id=$1$$ LANGUAGE SQL;

While this works - I can now define a query like

 SELECT * FROM foo(?) WHERE id=?

- the planner has no clue how to optimize this (dummy table contains 1
million entries):

 EXPLAIN ANALYSE SELECT * FROM foo(1) WHERE id=1;
                                                 QUERY PLAN
 ----------------------------------------------------------------------------------------------------------
  Function Scan on foo  (cost=0.00..15.00 rows=5 width=36) (actual
time=2588.982..3088.498 rows=1 loops=1)
    Filter: (id = 1)
  Total runtime: 3100.398 ms
 (3 rows)

which obviosly is intolerable. So, do I have to bite the bullet and
use the ugly "COALESCE/JOIN" statements everywhere or is there a
better way?

Any help is appreciated. Thanks in advance,
 Dennis

On Sun, Oct 28, 2007 at 01:59:22AM +0200, Dennis Brakhane wrote:

> I have some problems supporting "translatable columns" in a way that
> is both efficient and comfortable for the programmer who has to write
> SQL-Queries (we use iBatis)

Maybe this helps a bit:

    http://salaam.homeunix.com/~ncq/gnumed/schema/gnumed-schema.html

Look at the tables und functions under the i18n schema.
Basically it provides a _() reimplementation in SQL, so

select
    _(translatable_column) as translated_column
from
    table_with_translatable_columns
...

works as expected. Which language to translate to is set in
another table which is evaluated by the _() function at
runtime.

You could evaluate that table from a view base on
current_user() if you don't want to write 50+ views for the
different languages.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346