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).

Werner