Rapid Application Development toolkit for building Administrative Web Applications

Internationalisation and the Radicore Development Infrastructure (Part 2)

By Tony Marston

1st February 2008

Introduction
Possible Methods
Design Decisions
My Implementation
Identify which languages are to be supported
Create separate tables for translated text
Update the Data Dictionary
Update the class file for each translation table
Populate the translation table with data
Retrieving translated text at runtime
References

Introduction

Internationalisation in a software application covers the ability to communicate with a user in his/her own language. It can be said to exist at the following levels:

Level 0: No internationalisation - the software cannot function in any language other than the one with which it was developed.
Level 1: Uni-lingual - the software can work in a single language, but each installation can use a different language. The contents of the application database is uni-lingual and does not have any facilities to provide translations in other languages.
Level 2: Multi-lingual - the software can work in several languages at the same time, and the application database contains translations of relevant text in all the supported languages. The relevant translation is retrieved as required.

Level 1 is supported in the Radicore framework by having the text for such things as screen titles, field labels, button labels and error messages contained in text files which are separate from the program code. Each set of files contains text in a single language and is held in a subdirectory whose name identifies that language. Each supported language therefore has a copy of these files in its own subdirectory. The framework will detect the user's preferred language, and will access the text files in the appropriate subdirectory. Please refer to Internationalisation and the Radicore Development Infrastructure (Part 1) for full details.

Level 2 is supported in the Radicore framework by maintaining translated text in separate tables within the application database. The framework will detect the user's preferred language, and will retrieve either the native text or the translated text as appropriate. The details are explained in the following sections of this document.

Possible Methods

There are several ways in which text in language 'A' can be replaced with text language 'B'. Before a solution can be designed it is necessary to examine the range of possibilities and weigh up the pros and cons of each one.

Are you going to run strings of text through a general-purpose translator, or replace one identifiable string with another?
Are you going to perform the translation/substitution as early as possible (i.e. as soon as you know what text needs to be output), or as late as possible (i.e. just before it is presented to the user)?
Are you going to put text into the output area and then translate it, or translate it first and then put it into the output area?
Are you going to identify each piece of text as a complete string, or give each one a smaller identity code?
Are you going to store the language variations in a database or in text files?
Are you going to put all the language variations into a single file, or have a separate file for each language?
If you use XML and XSL to produce all HTML output (as I do in my development infrastructure) could you perform all the translation during the XSL transformation?

Design Decisions

While reviewing the possible options I made the following decisions:

The idea of running text, whether whole pages or small fragments, through a general-purpose translator was rejected as I have never seen a translation service that produces perfect results. I will therefore stick to the replacement of one string with another as it uses techniques that are tried and tested as well as being fast and accurate.
As this document is about providing translations of application data, it makes reasonable sense to store those translations in the same database but in separate tables.
By storing the translated text in the database it becomes easier to retrieve the correct translation within a single sql SELECT statement. By using a non-database file this would necessitate a completely separate I/O operation to fetch the translated text.

My Implementation

Identify which languages are to be supported

It is likely that an application will not offer support for all possible languages, therefore it will be necessary to identify the subset of languages that will be supported. This can be done via the List Supported Languages task as shown in Figure 1. Note that one of these languages should also be identified on the Menu Control Data as the default language.

Figure 1 - List Supported Languages

The default language will be used for the text on the "base" table, while the others will identify the language codes that are expected to exist on the various translation tables.

Create separate tables for translated text

It is important to note that not every text field in every database table requires multiple translations. The first step is therefore to identify those tables which contain text fields which DO require multiple translations. For each of these tables (known as "base" tables) it is then necessary to create an additional database table to hold the translations. Each of these alternative language tables should have the following characteristics:

The new table name is not important as the framework will be notified of its existence via an addition to the Data Dictionary.
The primary key should be identical to that of the "base" table, but with the addition of a column called language_id with a type of varchar(5).
The table should contain only those fields which need to be translated, using exactly the same column names as on the "base" table. It is possible to include columns to hold the date and user id for new and updated records, but anything else will be a waste of time.

This should produce a database structure similar to the following:

Base table (one entry in the default language):

CREATE TABLE IF NOT EXISTS `x_option` (
  `option_id` varchar(8) NOT NULL default '',
  `option_desc` varchar(40) NOT NULL default '',
  `created_date` datetime NOT NULL default '2000-01-01 00:00:00',
  `created_user` varchar(16) NOT NULL default 'UNKNOWN',
  `revised_date` datetime default NULL,
  `revised_user` varchar(16) default NULL,
  PRIMARY KEY  (`option_id`)
) TYPE=MyISAM;

Translation table (one entry for each additional language):

CREATE TABLE IF NOT EXISTS `x_option_alt` (
  `option_id` varchar(8) NOT NULL default '',
  `language_id` varchar(5) NOT NULL default '',
  `option_desc` varchar(40) NOT NULL default '',
  `created_date` datetime NOT NULL default '2000-01-01 00:00:00',
  `created_user` varchar(16) NOT NULL default 'UNKNOWN',
  `revised_date` datetime default NULL,
  `revised_user` varchar(16) default NULL,
  PRIMARY KEY  (`option_id`,`language_id`)
) TYPE=MyISAM;

Update the Data Dictionary

Using the tasks which are supplied as part of the Data Dictionary you must perform the following:

Use the Import Table task to import the names of each new table.
Use the Import Columns task to import the column details for each of these new tables.
Use the Update Column task on the language_id field of each translation table to set the following:
- Control = Dropdown list
- Option List = language_id
Use the Add Relationship task to identify that the base table (senior) is related to the translation table (junior). Set the following:
- Relationship Type = CASCADE.
- Order By = blank.
- Parent Field = blank.
Use the Update Table task on the "base" table to identify to the framework that a table of alternative language translations exists. Set the following:
- Alternative Language Table = the identity of the associated language table.
This will be rejected unless the following conditions are met:
- The primary key of the alternative language table must be identical to the "base" table, but with the addition of a column called language_id.
- The alternative language table must contain at least one text field which exists on the "base" table. The qualifying fields will appear under the heading "Alternative Language Columns".

Use the Export Table task to export details from the Data Dictionary to the application. This must be done on both the "base" table and the translation table, as well as any other table which is related to the "base" table. This will export the following details to he relevant <table>.dict.inc files:

For the "base" table it will identify that a translation table exists, as follows:

// alternative language options 
$this->alt_language_table       = 'x_pers_type_alt';
$this->alt_language_cols        = 'pers_type_desc';

For each table which has the "base" table as its parent in a relationship it will identify that the parent table has a translation table, as follows:

// parent relationship details 
$this->parent_relations[]       = array('parent' => 'x_pers_type',
                                        'parent_field' => 'pers_type_desc',
                                        'alt_language_table' => 'x_pers_type_alt',
                                        'alt_language_cols' => 'pers_type_desc',
                                        'fields' => array('pers_type_id' => 'pers_type_id'));

This information can then be used by the framework to alter the sql SELECT statement which is generated at runtime.

Update the class file for each translation table

The <tablename>.class.inc file for each translation table should be amended to include the following custom method:

    function _cm_getExtraData ($where, $fieldarray)
    // Perform custom processing for the getExtraData method.
    // $where = a string in SQL 'where' format.
    // $fieldarray = the contents of $where as an array.
    {
        if (!array_key_exists('language_id', $this->lookup_data)) {
            // get options for language_code
            $array = getLanguageArray('languages');
            $this->lookup_data['language_id'] = $array;
        } // if

        return $fieldarray;

    } // _cm_getExtraData

This will load the contents of the dropdown list for the language_id field.

Populate the translation table with data

In order to achieve this a new task must be created. Using the Generate PHP scripts procedure take the following steps:

Select the translation table.
Select the LIST2 pattern.
For the parent table select the "base" table (i.e. the table containing the untranslated text).
Use the Maintain Navigation Buttons screen to add the newly-created LIST2 task to the navigation buttons of the task which lists the "base" table.

When this new task is run it should look something like that shown in Figure 2:

Figure 2 - Maintain Alternative Languages

When this task is run it will ensure that an entry exists on the translation table for each of the supported languages (except for the default language which already exists on the "base" table). If any entry is missing it will automatically be created with each column being filled in from the contents of the "base" table, but with a prefix which identifies the language code. This will help identify those entries which have yet to be translated.

Note that no custom code needs to be built into the LIST2 task to achieve this as the std.list2.inc file already contains the following standard code:

if (!empty($outer_data)) {
    $inner_data = $dbinner->getData($where);

    if ($dbinner->tablename == $dbouter->alt_language_table) {
        // ensure that default entries exist for all supported languages
        $inner_data = $dbinner->getLanguageEntries($inner_data, $outer_data, $dbouter->alt_language_cols);
    } // if

} // if

In order to modify any of the text translations simply select them and press the "Update" button in the navigation bar. This will activate a task similar to the one shown in Figure 3:

Figure 3 - Update Alternative Language

Retrieving translated text at runtime

Having created text in alternative languages for certain columns in certain tables, what code does the developer need to insert so that the correct translation is retrieved at runtime to match the user's preferred language? The answer is - none. Provided that $this->sql_from is empty when leaving the _cm_pre_getData() method then the _sqlForeignJoin() method will be called to construct the default values for $this->sql_select and $this->sql_from which will include JOINS to all the parent tables specified in the $parent_relations array. If it is required to add manual extensions to the $this->sql_select and $this->sql_from strings please refer to FAQ 84.

The _sqlForeignJoin() method will look for non-empty values for $alt_language strings for the current table ("base" table) as well as any parent tables and, if found, will alter the contents of the $this->sql_select and $this->sql_from strings as follows:

For each field name in $alt_language_cols it will append the following to $this->sql_select:
```
COALESCE((SELECT fieldname 
          FROM alt_language_table 
          WHERE alt_language_table.foreign_key=base_table.primary_key 
            AND alt_language_table.language_id='??')
         , base_table.fieldname) AS fieldname
```
where language_id is taken either from the language_code field in the USER record, or from the language setting in the user's browser (whichever comes first).
Note that COALESCE(option1, option2) AS fieldname is used so that if an entry with that language_id does not exist then the text from base_table.fieldname will be used to supply the text in the default language.
If a previous reference to fieldname already exists in $this->sql_select then it will be removed otherwise it will cause an ORA-00918: column ambiguously defined error in Oracle.
If fieldname exists in $this->sql_orderby and is qualified with a table name, then that table name must be removed. Sorting by base_table.fieldname will not be appropriate if the value is actually obtained from alt_language_table.fieldname.
If fieldname exists in $this->sql_search then it will be moved to $this->sql_having.

References

counter