i18n/l10n handling in the backend


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.


  • 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


  • 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
        _(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
Topic revision: 20 Jan 2013, JamesBusser
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback
Powered by Olark