3.6.3. Backend I18N for non-dynamic ("fixed") strings in the backend.

In enumerations we often see fixed strings being stored in the backend.
There's no good way a client can translate those to the local language.
Nevertheless we need to provide a translation. Consider the following
example:

We want a table that enumerates family relations. The obvious table
design would be

create table member (
	id serial primary key,
	name varchar(20)
);

Other tables will obviously reference table.id but we want the frontend
to be able to show a spelled-out name for the family member type. A simple

 select name from member where id='some ID';

will, however, always return the version that was put into the database in
upon installation. Typically this would be done by statements such as

 insert into member(name) values('sister');

Hence, queries would always return the English 'sister'.

PostgreSQL does not directly support localization of database content.
Therefor the following scheme has been devised:

At the top of your psql script schema definition files include the
file gnumed/server/gmI18N.sql which provides a localization infrastructure.
For your convenience, just copy/paste the following two lines:

 -- do fixed string i18n()ing
 \i gmI18N.sql

The database will then contain several new tables starting with i18n_* and
a few functions.

i18n_curr_lang
--------------
Here you can/should set the currently preferred language on a per-user
basis. Only one language per user is allowed at any one time. Switching
the language here will enable the user to see another translation (if
provided).

i18n_keys
---------
This is just a convenience table listing all the strings that need
translations. Dump this and give to translation teams. A tool will be
provided to make use of this table. It is of no importance to the
actual online translation process.

i18n_translations
-----------------
This is where translations actually live. As in gettext the original
string is used as the key and the language code (which should correspond
with those used in i18n_curr_lang) as a discrimator.

How to translate strings
------------------------
Make your string insertions aware of i18n issues. This is what the
function i18n(text) is for. Regarding the above example insertions
need to be rewritten from
 insert into member(name) values('sister');
to
 insert into member(name) values(i18n('sister'));

The i18n() function will take care of inserting the string 'sister'
into the i18n_keys table where translation teams will find it and
provide a translation. Later on, when a translation is available it
will be inserted into i18n_translations:

 insert into i18n_translations(lang, orig, trans) values ('de_DE', 'sister', 'Schwester');

How to make your tables translate strings
-----------------------------------------
Now that we have translations available in i18n_translations we
can start making our tables aware of them. Unfortunately,
PostgreSQL does not yet support column-level select rules. We
therefor have to create views wrapping the original tables. Note
that the original table will still be useable. Original tables
which have translated strings should be named "_tablename" while
views translating them should be named "v_i18n_tablename". Going
back to our previous example, the table

create table member (
	id serial primary key,
	name varchar(20)
);

should be renamed to "_member" and a view created on it:

create view v_i18n_member (id, name) as
	select _member.id, _(_member.name)
	from member;

By making sure to use the same column names in the view we
minimize frontend coding changes.

You will notice how the function _() is used to access
the translation for the attribute "name". This function is
provided by gmI18N.sql and provides nearly the same
functionality as gettext.gettext() which is often aliased
to _() in Python and other languages. It will return
a translation based on the user's currently selected language
in i18n_curr_lang and the translation for that language
in i18n_translations using the original string as the key.

 NOTE: We used to use _() where we use i18n() now. This
 was contrary to _()'s standard use and has been changed !

If no translation is available for a given string _()
will return the original string. Also, if the user did
not select a language in i18n_curr_lang the original
is returned.

How to make the frontend use translated strings
-----------------------------------------------
All the backend infrastructure is in place now so we
can make frontends aware of translated strings. The first
step is to make frontends use the v_i18n_* views instead
of the tables. If we fail to do that everything will still
work. We just won't get translations :-)

The second step is to make sure the current user has
a language selected in i18n_curr_lang. Use something like

 insert into i18n_curr_lang(lang) values ('de_DE');

This will default to the CURRENT_USER. The actual value need
not conform to anything in particular. It can be "Klingon" for
that matter. Make sure then to have "Klingon" translations
available in i18n_translations.

This i18n technique does not take care of strings that are
inserted into the database dynamically (at runtime). It only
makes sense for strings that are inserted once such as are
often used for enumerations.

All this crap isn't necessary anymore once PostgreSQL supports
native internationalization of 'fixed' strings.