i18n/l10n handling in the backend

Rationale

Many tables in GNUmed store enumerations such as the types of a document. It is not useful for German users to see a document type referral letter. They would much rather see Arztbrief. This sort of translation could be done at the application level by gettext. However, it would be useful if there was a way to tell the database and the application that referral letter and Arztbrief really are one and the same thing such that users speaking different languages can work with one and the same database and understand each others document types. Hence there is a need to provide this translation capability right in the backend. However, PostgreSQL does not directly support localization of database content yet.

Concepts

Database objects

For all the gory details refer to the database schema docs. All the relevant objects are aggregated in the schema i18n.

Tables and Views

Functions

How it all fits together

How to add translation capabilities to your database

Import gnumed/server/sql/gmI18N.sql into your database. This is typically done during the bootstrapping process via the configuration files.

How to provide a translated column

Suppose we have a table which enumerates family relations. An obvious table design would be 
create table relationship (
    pk serial primary key,
    description text
);

ClinicalOrganizingAndWorkflows tables will reference the table by relationship.pk. Running a query like select description from relationship where pk=1; will return whatever was put into the database with the primary key 1, for example "sister". A German user, however, would prefer to get back the string "Schwester" instead. In other words we want frontends to be able to show a translation for the family member type, eg. for relationship.description. The simplest way owuld be to use the _() SQL function in the SELECT statement, eg.: select description, _(description) as l10n_description from member where pk=1;. This returns the translation for relationship.description as an additional column l10n_description.

In many cases it will be more convenient to define views that add a translation column such as: 
create view v_relationships as
    select
        pk,
        description,
        _(description) as l10n_description
    from relationship
;
One can then simply select from that view by select l10n_description from v_relationships where pk=1;.

How to add translated data to the database

Even if the output language for a user is set and the appropriate columns are generated such that they translate their content we still need translated data in the database.

Typically, data is added by statements like insert into relationship(description) values('sister'); which does not help any with translations. When inserting data that is to be used in translating columns one should do it like this: insert into relationship(description) values(i18n.i18n('sister')); The i18n.i18n() function will take care of additionally inserting the string 'sister' into the i18n.i18n_keys table where translation teams will find it and provide a translation for, say, German like so: insert into i18n_translations(lang, orig, trans) values ('de_DE', 'sister', 'Schwester');. Now an appropriate SELECT should return the translated data.

A script is provided to help with finding and providing missing translations in the database.

How to add a translation target (language) to the database

Suppose you want to add a translation named klg_PLUTO to your host farout.