<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en_US" lang="en_US">
<head>
<title> BackendI18N < Gnumed < Foswiki</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <meta name="robots" content="noindex" /> <link rel="alternate" type="application/rss+xml" title="RSS Feed" href="WebRss.html" />
<link rel="icon" href="../rsrc/System/ProjectLogos/favicon.ico" type="image/x-icon" /> <link rel="shortcut icon" href="../rsrc/System/ProjectLogos/favicon.ico" type="image/x-icon" />
<link rel="alternate" href="http://wiki.gnumed.de/bin/edit/Gnumed/BackendI18N?t=1362919412" type="application/x-wiki" title="edit BackendI18N" />
<meta name="description" content="BackendI18N" />
<!--[if IE]></base><![endif]-->
<style type="text/css" media="all">
@import url('../rsrc/System/SkinTemplates/base.css');
</style>
<style type="text/css" media="all">
@import url('../rsrc/System/SkinTemplates/default.css');
</style>
<!--[if IE]><style type="text/css" media="screen">
pre {
overflow-x:auto;
padding-bottom:expression(this.scrollWidth > this.offsetWidth ? 16 : 0);
}
</style>
<![endif]-->
<meta name="foswiki.PUBURL" content="http://wiki.gnumed.de/pub" /> <!-- PUBURL -->
<meta name="foswiki.PUBURLPATH" content="/pub" /> <!-- PUBURLPATH -->
<meta name="foswiki.SCRIPTSUFFIX" content="" /> <!-- SCRIPTSUFFIX -->
<meta name="foswiki.SCRIPTURL" content="http://wiki.gnumed.de/bin" /> <!-- SCRIPTURL -->
<meta name="foswiki.SCRIPTURLPATH" content="/bin" /> <!-- SCRIPTURLPATH -->
<meta name="foswiki.SERVERTIME" content="10%20Mar%202013%20-%2013:43" /> <!-- SERVERTIME -->
<meta name="foswiki.SKIN" content="twikinet%2c%20pattern" /> <!-- SKIN -->
<meta name="foswiki.SYSTEMWEB" content="System" /> <!-- SYSTEMWEB -->
<meta name="foswiki.TOPIC" content="BackendI18N" /> <!-- TOPIC -->
<meta name="foswiki.USERNAME" content="KarstenHilbert" /> <!-- USERNAME -->
<meta name="foswiki.USERSWEB" content="Main" /> <!-- USERSWEB -->
<meta name="foswiki.WEB" content="Gnumed" /> <!-- WEB -->
<meta name="foswiki.WIKINAME" content="KarstenHilbert" /> <!-- WIKINAME -->
<meta name="foswiki.WIKIUSERNAME" content="Main.KarstenHilbert" /> <!-- WIKIUSERNAME -->
<meta name="foswiki.NAMEFILTER" content="%5b%5cs%5c*%3f~%5e%5c%24%40%25%60%22'%26%3b%7c%3c%3e%5c%5b%5c%5d%23%5cx00-%5cx1f%5d" /> <!-- NAMEFILTER --><!--JQUERYPLUGIN::FOSWIKI::META-->
<script type='text/javascript' src='../rsrc/System/JQueryPlugin/jquery-1.4.3.js'></script><!--JQUERYPLUGIN-->
<script type='text/javascript' src='../rsrc/System/JQueryPlugin/plugins/livequery/jquery.livequery.js'></script><!--JQUERYPLUGIN::LIVEQUERY-->
<script type='text/javascript' src='../rsrc/System/JQueryPlugin/plugins/foswiki/jquery.foswiki.js'></script><!--JQUERYPLUGIN::FOSWIKI-->
<script type='text/javascript' src='../rsrc/System/JSTreeContrib/jquery.jstree.js'></script><!--JQUERYPLUGIN::JSTREE-->
</head>
<body class=""><div class="foswikiPage">
<a name="PageTop"></a>
<p></p>
<p></p>
<h1><a name="i18n_47l10n_handling_in_the_backend"></a> i18n/l10n handling in the backend </h1>
<p></p>
<a name="foswikiTOC"></a><div class="foswikiToc"> <ul>
<li> <a href="#Rationale"> Rationale </a>
</li> <li> <a href="#Concepts"> Concepts </a>
</li> <li> <a href="#Database_objects"> Database objects </a> <ul>
<li> <a href="#Tables_and_Views"> Tables and Views </a>
</li> <li> <a href="#Functions"> Functions </a>
</li></ul>
</li> <li> <a href="#How_it_all_fits_together"> How it all fits together </a> <ul>
<li> <a href="#How_to_add_translation_capabilities_to_your_database"> How to add translation capabilities to your database </a>
</li> <li> <a href="#How_to_provide_a_translated_column"> How to provide a translated column </a>
</li> <li> <a href="#How_to_add_translated_data_to_the_database"> How to add translated data to the database </a>
</li> <li> <a href="#How_to_add_a_translation_target_40language_41_to_the_database"> How to add a translation target (language) to the database </a>
</li></ul>
</li></ul>
</div>
<p></p>
<h2><a name="Rationale"></a> Rationale </h2>
<p></p>
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 <em>referral letter</em>. They would much rather see <em>Arztbrief</em>. This sort of translation could be done at the application level by <code><a href="http://docs.python.org/library/gettext.html" target="_top">gettext</a></code>. However, it would be useful if there was a way to tell the database and the application that <em>referral letter</em> and <em>Arztbrief</em> 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, <a href="PostgreSQL.html">PostgreSQL</a> does not directly support localization of database content yet.
<p></p>
<h2><a name="Concepts"></a> Concepts </h2>
<p></p> <ul>
<li> translations are not to affect data in any way
</li> <li> allow for translations transparent to a SELECT
</li> <li> allow for on-demand translations in a SELECT
</li> <li> allow user to select a default output language
</li> <li> allow for switching the default output language per user as desired
</li> <li> allow for incomplete translations by falling back to a "default" language if no translation is available for a given string
</li> <li> translations should refer to the same row in the translated column
</li></ul>
<p></p>
<h2><a name="Database_objects"></a> Database objects </h2>
<p></p>
For all the gory details refer to the <a href="DbStructure.html">database schema</a> docs. All the relevant objects are aggregated in the schema <code>i18n</code>.
<p></p>
<h3><a name="Tables_and_Views"></a> Tables and Views </h3>
<p></p> <ul>
<li> <code>i18n.i18n_curr_lang</code> <ul>
<li> stores the per-user default output language
</li></ul>
</li> <li> <code>i18n.i18n_keys</code> <ul>
<li> lists all the source strings that should be translated
</li></ul>
</li> <li> <code>i18n.i18n_translations</code> <ul>
<li> holds all the string translations
</li></ul>
</li> <li> <code>i18n.v_missing_translations</code> <ul>
<li> lists those strings that do not have a translation for a language found in <code>i18n.i18n_curr_lang</code>
</li></ul>
</li></ul>
<p></p>
<h3><a name="Functions"></a> Functions </h3>
<p></p> <ul>
<li> <code>i18n.i18n(text)</code> <ul>
<li> used by database DDL scripts to register strings for translation
</li></ul>
</li> <li> <code>i18n._(text)</code> and <code>i18n._(text, text)</code> <ul>
<li> used in =SELECT=s and view definitions to translate a given string
</li> <li> pretty much like <code>gettext()</code> in other programming languages, usually aliased as <code>_()</code>
</li> <li> there are convenience wrappers in the schema <code>public</code>
</li></ul>
</li> <li> <code>i18n.set_curr_lang(text)</code> <ul>
<li> sets the default output language for the current user
</li></ul>
</li> <li> <code>i18n.set_curr_lang(text, name)</code> <ul>
<li> sets the default output language for the user <code>name</code>
</li></ul>
</li> <li> <code>i18n.force_curr_lang(text)</code> <ul>
<li> forces setting the default output language for the current user even if there are no translations available
</li></ul>
</li></ul>
<p></p>
<h2><a name="How_it_all_fits_together"></a> How it all fits together </h2>
<p></p>
<h3><a name="How_to_add_translation_capabilities_to_your_database"></a> How to add translation capabilities to your database </h3>
<p></p>
Import <code>gnumed/server/sql/gmI18N.sql</code> into your database. This is typically done during the bootstrapping process via the configuration files.
<p></p>
<a name="AddTranslatedColumn"></a>
<h3><a name="How_to_provide_a_translated_column"></a> How to provide a translated column </h3>
<p></p>
Suppose we have a table which enumerates family relations. An obvious table design would be
<pre>
create table relationship (
pk serial primary key,
description text
);
</pre>
<p></p>
<a href="ClinicalOrganizingAndWorkflows.html">ClinicalOrganizingAndWorkflows</a> tables will <em>reference</em> the table by <code>relationship.pk</code>. Running a query like <code>select description from relationship where pk=1;</code> 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 <code>_()</code> SQL function in the SELECT statement, eg.: <code>select description, _(description) as l10n_description from member where pk=1;</code>. This returns the translation for <code>relationship.description</code> as an additional column <code>l10n_description</code>.
<p></p>
In many cases it will be more convenient to define views that add a translation column such as:
<pre>
create view v_relationships as
select
pk,
description,
_(description) as l10n_description
from relationship
;
</pre>
One can then simply select from that view by <code>select l10n_description from v_relationships where pk=1;</code>.
<p></p>
<a name="AddTranslatedData"></a>
<h3><a name="How_to_add_translated_data_to_the_database"></a> How to add translated data to the database </h3>
<p></p>
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 <em>data</em> in the database.
<p></p>
Typically, data is added by statements like <code>insert into relationship(description) values('sister');</code> which does not help any with translations. When inserting data that is to be used in translating columns one should do it like this: <code>insert into relationship(description) values(i18n.i18n('sister'));</code> The <code>i18n.i18n()</code> function will take care of additionally inserting the string <em>'sister'</em> into the <code>i18n.i18n_keys</code> table where translation teams will find it and provide a translation for, say, German like so: <code>insert into i18n_translations(lang, orig, trans) values ('de_DE', 'sister', 'Schwester');</code>. Now an appropriate SELECT should return the translated data.
<p></p>
A <a href="http://cvs.savannah.gnu.org/viewvc/gnumed/gnumed/server/locale/dump-missing-db_translations.py?root=gnumed&view=markup" target="_top">script</a> is provided to help with finding and providing missing translations in the database.
<p></p>
<a name="AddTranslation"></a>
<h3><a name="How_to_add_a_translation_target_40language_41_to_the_database"></a> How to add a translation target (language) to the database </h3>
<p></p>
Suppose you want to add a translation named <em>klg_PLUTO</em> to your host <code>farout</code>.
<p></p> <ul>
<li> use <code>select i18n.force_curr_lang('klg_PLUTO');</code> to set the language for the current user to <em>klg_PLUTO</em>
</li> <li> use <code>gnumed/server/locale/dump-missing-db_translations.py</code> to get the missing translations
</li> <li> translate the strings in the SQL script that was generated
</li> <li> run <code>psql -h farout -d gnumed_vXX -U gm-dbo -f the-file.sql</code> (replace XX with the version in question)
</li> <li> contact the developers so they can add your translation to the bootstrapping procedure
</li></ul>
<a name="TopicEnd"></a>
<p></p>
<p></p>
<p></p>
<p></p>
</div>
</body></html>
