Re: Use arrays to store multilanguage texts

http://savannah.gnu.org/cgi-bin/viewcvs/*checkout*/gnumed/gnumed/gnumed/server/sql/gmI18N.sql?rev=1.15

Works for me. You'll have to make sure you get the entire
string for the URL. It may have been wrapped by your mail
handling agent.

> > - i18n_curr_lang holds the (ISO) language string per user
>
> I'm interested in this part. Is there a facility for having variables on the
> postgres side that you can set once at the beginning of a session and then
> refer to in functions?
We set them the first time a user connects to a particular
database. Unless they are changed later (possible anytime with
immediate transparent switching of languages in queries and
views) the language will stay the same across sessions/
clients/ connections.

> I instead made my application insert lang_en,lang_fr etc into the queries as
> appropriate. This made the application code a little uglier than necessary,
> but it's not too bad, I put the function name in a variable and call
> $xlate(column) in a double-quoted string.

We then use select _(fruits.name) style queries. The _()
function uses the language in i18n_curr_lang (for
CURRENT_USER).

> I think having a global postgres variable would be a nice shortcut, though I
> fear it would make it harder to do things like index lang_en() and lang_fr().
I'd think on could index i18n_translations just fine:

create unique index on i18n_translations(trans) where lang='xx';

> Do you cheat and make your _() IMMUTABLE? It's only immutable if you guarantee
> that i18n_currr_lang can never change in the middle of a query.
Given that it'd take a writing query that should run in a
transaction it shouldn't change within a read query. However,
I'm not too sure about views, like we use translated columns
in views like this:

create view a_view as
select
bla as bla,
_(bla) as l10n_bla
...

> But even then
> you're lying to the server and indexing _() would do strange things, for
> example. In fact I think it would produce corrupted indexes if it worked at
> all.
I haven't experienced any of that but it may well happen -
dunno.

> > - v_missing_translations is a view of, well, ...
>
> This is the one thing that the array solution can't do. But in my case it's
> dynamic text and both languages are being provided at the same time by the
> same people. I'm not going to have a translator going through batches of them
> like gettext.
Well, you wouldn't *have* to use that view, it's just for
convenience. You could just as well insert the dynamic text
into i18n_translations (lang, orig, trans) whenever dynamic
text is inserted. We mainly use that (+ i18n()) for gettext
style batch translation of static application text. Except
that all the lookup is handled by _() on the server right
inside the query :-)

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