On 15/09/2010 22:42, Werner F. Bruhin wrote:

> I am trying to come up with a way of having database content
> translated into different languages and have "transparent" access to
> this translation with a fall back to a default language if an item is
> not translated from tools such as Python/sqlalchemy (uses kinterbasdb)
> or ODBC or ....
>
> I come up with the following and wonder what might "bite" me in the
> future with this.
>
> Using "countries" as an example I would have:
>
> "countries" - a table containing all the information for a country
> (name, iso2, iso2, teldialcode, .... etc)
> "countries_t" - a table containing all the columns to be translated
> (with the above just "name")
> "countries_v" a view over the top with a where clause using
> "RDB$GET_CONTEXT"
>
> The definition for the view would look like this:
>
> CREATE VIEW COUNTRY_V(
> ID,
> NAME,
> ISO2,
> ISO3,
> CREATED_AT,
> UPDATED_AT)
> AS
> select ctry.id,
> coalesce(trans.name, ctry.name),
> ctry.iso2,
> ctry.iso3,
> ctry.created_at,
> ctry.updated_at
> from countries ctry
> left outer join countries_t trans on ctry.id = trans.fk_country_id
> where trans.lang_code5 = (select rdb$get_context('USER_SESSION', 'LANG_CODE') from rdb$database);
>
> Before using this view the application would e.g. on user login do a "rdb$set_context('USER_SESSION', 'LANG_CODE', 'DE_de').
>
> I would have about 20 tables where I need the above and they would have a few tens of thousands of rows all together.
>
> Done very limited test of the above and so far it works.
>
> Would be interested to hear what other people think about this approach or if they have used a different approach for this type of problem (btw, I am aware of the gnumed stored procedure implementation of a gettext approach).
>
>

Oops, was a bit to quick with this. The above does not handle the fall
back, i.e. just take the data from "countries" - back to the drawing
board, but that will be tomorrow.

Werner