# 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

- translations are not to affect data in any way
- allow for translations transparent to a SELECT
- allow for on-demand translations in a SELECT
- allow user to select a default output language
- allow for switching the default output language per user as desired
- allow for incomplete translations by falling back to a "default" language if no translation is available for a given string
- translations should refer to the same row in the translated column

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

- `i18n.i18n_curr_lang`
  - stores the per-user default output language
- `i18n.i18n_keys`
  - lists all the source strings that should be translated
- `i18n.i18n_translations`
  - holds all the string translations
- `i18n.v_missing_translations`
  - lists those strings that do not have a translation for a language found in `i18n.i18n_curr_lang`

### Functions

- `i18n.i18n(text)`
  - used by database DDL scripts to register strings for translation
- `i18n._(text)` and `i18n._(text, text)`
  - used in =SELECT=s and view definitions to translate a given string
  - pretty much like `gettext()` in other programming languages, usually aliased as `_()`
  - there are convenience wrappers in the schema `public`
- `i18n.set_curr_lang(text)`
  - sets the default output language for the current user
- `i18n.set_curr_lang(text, name)`
  - sets the default output language for the user `name`
- `i18n.force_curr_lang(text)`
  - forces setting the default output language for the current user even if there are no translations available

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

- use `select i18n.force_curr_lang('klg_PLUTO');` to set the language for the current user to klg_PLUTO
- use `gnumed/server/locale/dump-missing-db_translations.py` to get the missing translations
- translate the strings in the SQL script that was generated
- run `psql -h farout -d gnumed_vXX -U gm-dbo -f the-file.sql` (replace XX with the version in question)
- contact the developers so they can add your translation to the bootstrapping procedure