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.

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?

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.

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.

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.

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:

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:

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.