On 16/09/2010 00:12, Werner F. Bruhin wrote:

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

Can't make it work with a view - very very likely me now knowing what I
am doing :-( .

Got it to work with a stored procedure.

CREATE OR ALTER PROCEDURE COUNTRIES_P
returns (
id bigint,
name varchar(30),
iso2 varchar(2),
iso3 varchar(3),
telcode smallint,
created_at date,
updated_at timestamp)
as
declare variable trans_name varchar(30);
begin
/* Procedure Text */
for select id, name, iso2, iso3, telcode, created_at, updated_at from
countries into
:id, :name, :iso2, :iso3, :telcode, created_at, updated_at
do
begin
select name from countries_t where :id = countries_t.fk_country_id and
countries_t.fk_lang_code5 =
rdb$get_context('USER_SESSION', 'LANG_CODE')
into
:trans_name;
end
if (:trans_name is not Null) then
begin
name = :trans_name;
end
suspend;
end

Shouldn't I be able to do the same functionality with a view?

Appreciate any tips.

Werner