Rapid Application Development toolkit for building Administrative Web Applications

Internationalisation and the Radicore Development Infrastructure (Part 2)

By Tony Marston

1st February 2008

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
Amendment History


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

  1. Are you going to run strings of text through a general-purpose translator, or replace one identifiable string with another?
  2. 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)?
  3. 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?
  4. Are you going to identify each piece of text as a complete string, or give each one a smaller identity code?
  5. Are you going to store the language variations in a database or in text files?
  6. Are you going to put all the language variations into a single file, or have a separate file for each language?
  7. 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:

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

internationalisation2-001 (14K)

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:

This should produce a database structure similar to the following:

Update the Data Dictionary

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

  1. Use the Import Table task to import the names of each new table.
  2. Use the Import Columns task to import the column details for each of these new tables.
  3. Use the Update Column task on the language_id field of each translation table to set the following:
  4. Use the Add Relationship task to identify that the base table (senior) is related to the translation table (junior). Set the following:
  5. 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: This will be rejected unless the following conditions are met:
  6. 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: 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:

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

Figure 2 - Maintain Alternative Languages

internationalisation2-002 (17K)

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

internationalisation2-003 (17K)

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:


© Tony Marston
1st February 2008


Amendment history: