Subject Re: [firebird-support] database content internationalization
Author Werner F. Bruhin
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
> The definition for the view would look like this:
> ID,
> ISO2,
> ISO3,
> AS
> select,
> coalesce(,,
> ctry.iso2,
> ctry.iso3,
> ctry.created_at,
> ctry.updated_at
> from countries ctry
> left outer join countries_t trans on = 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.