Database Localization

There are five methods to localize databases. The methods are:

• Row localization where id and language id together make the primary key
• Row localization where id and language id are are not part of primary key
• Field localization
• Table localization
• Database cloning

Each method has it advantages and disadvantages. You must choose the method that best suits your needs. No matter what method you use Sisulizer does never not change the table structure. It only updates the values in the localized fields, tables or rows, or creates and updates the localized database copies. You have to modify the database structure to prepare it for localization.

Comparing different localization methods

The following table compares different localization methods.

Combined strings

Some fields may contains combined strings. You can set a field to contain combined strings by assigning an expression for it. Right click the field name in the database source dialog's Items sheet and choose Expression.

Row localization where id and language id together make the primary key

In this method Sisulizer copies the original row for each language. The copied rows equal to the original one except the Language field value is different (matches the language of the row) and those fields that have been marked to be localized contain localized value.

Prepare table structure for localization

Add language field to those database tables that you want to localize. This requires you to change the table structure. Make sure that the language field is part of primary key.

For example you have the following table structure:

CREATE TABLE Country
(
  Id INTEGER NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Population INTEGER NOT NULL,
  PRIMARY KEY(Id)
);

If you want to localize the above table add the Language field and add that to the primary index.

CREATE TABLE Country
(
  Id INTEGER NOT NULL,
  Language VARCHAR(10) NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Population INTEGER NOT NULL,
  PRIMARY KEY(Id, Language)
);

Now the database is ready for localization.

Add database to the project

Use Project Wizard to create a Sisulizer project containing the database. After you have selected the database on the Database sheet you have to set the field properties. If you use the standard field name convention for language field Sisulizer will automatically detect the field to be a language field. The standard language field name is either "language", "languageid", "lang", "langid", "locale" or "localeid". If you do not use the standard language field name convention you must mark the field to be a language field by right clicking and checking the Language Id Field menu. Finally check (double click) those fields that you want to localize.

Sample table

If the original Country table looks like this,

The localized Country table will look like this after Sisulizer has updated the localized field values:

Row localization where id and language id are not part of primary key

In this method Sisulizer copies the original row for each language. The copied rows equal to the original one except the Language field value is different (matches the language of the row) and those fields that have been marked to be localized contain localized value. Each localized row has a unique primary key value. The row contains an id field that points to the actual resource id, and a language field that contains the language code.

Prepare table structure for localization

Add resource id and language fields to those database tables that you want to localize. This requires you to change the table structure.

For example you have the following table structure:

CREATE TABLE Country
(
  Id INTEGER NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Population INTEGER NOT NULL,
  PRIMARY KEY(Id)
);

If you want to localize the above add a new primary id filed, RowId, and change primary key into that. Add a resource id field, ResourceId, that contains the resource id of the row. Add a language field, Language, that contains the language id.

CREATE TABLE Country
(
  RowId INTEGER NOT NULL,
  ResourceId INTEGER NOT NULL,
  Language VARCHAR(10) NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Population INTEGER NOT NULL,
  PRIMARY KEY(RowId)
);

Resource table contains the unique resource ids. When adding localized strings to the database Sisulizer has to create new rows to the Country table. Each row has to have a unique id. If you want to control the way how Sisulizer creates new id write a SQL function and give that for Sisulizer's database source.

Now the database is ready for localization.

Add database to the project

Use Project Wizard to create a Sisulizer project containing the database. After you have selected the database on the Database sheet you have to set the field properties.Right click the ResourceId and check Id Field menu item to mark that field as id field. If you use the standard field name convention for language field Sisulizer will automatically detect the field to be a language field. The standard language field name is either "language", "languageid", "lang", "langid", "locale" or "localeid". If you do not use the standard language field name convention you must mark the field to be a language field by right clicking and checking the Language Id Field menu. Finally check (double click) those fields that you want to localize.

Sisulizer has to create new row ids when it adds localized rows. By default Sisulizer uses the next available interger number for row ids. If you want to use some other id or your row id is not an integer value you have two choices. Either you enter a SQL statement that returns a new row id or you write a stored procedure that returns a new row id. To configure row id generation right click the table name in the project tree (not Database source dialog) and choose Properties.

Sample table

If the original Country table looks like this,

The localized Country table will look like this after Sisulizer has updated the localized field values:

The Resource table will look like this.

Field localization

In this method Sisulizer updates the values of the localized fields. The localized fields are equal to the original field except they contain data in different language. For example if the original language is English and you want to localize the database to German and Japanese you add German and Japanese fields for those fields that contain strings to be localized.

Prepare table structure for localization

Add localized fields to those databases that you want to localize. This requires you to change the table structure.

If you want to localize the Country table to German and Japanese add localized Name, Capital and Description fields for each language.

CREATE TABLE Country
(
  Id INTEGER NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Name_de VARCHAR(50) NOT NULL,
  Name_ja VARCHAR(50) NOT NULL,
  Population INTEGER NOT NULL,
  PRIMARY KEY(Id)
);

Now the database has placeholders for German and Japanese localization. Unlike in row localization you have to change the table structure every time you add a new language.

Add database to the project

Use Project Wizard to create a Sisulizer project containing the database. After you have selected the database on the Database sheet you have to set the field properties.If you use the standard field name convention for localized fields Sisulizer will automatically detect the fields to be localized ones. The standard localized field name is a combination of the original field name and the language code (e.g. German field name of Description field would be Description_de or DescriptionDe). If you do not use the standard localized field name convention you must drag localized fields as a child fields of the original fields and set the language of the field by right clicking and choosing the language from the Languages menu.

Sample table

The localized Country table will look like this after Sisulizer has updated the localized field values:

Table localization

In this method Sisulizer adds new language tables for each table.

Prepare table structure for localization

Add localized tables for those tables that you want to localize. The localized tables only contains the primary key and those fields that will be localized.

If you want to localize the table to German and Japanese add localized Name, Capital and Description fields for each language.

CREATE TABLE Country_de
(
  Id INTEGER NOT NULL,
  Name VARCHAR(50) NOT NULL,
  PRIMARY KEY(Id)
);

CREATE TABLE Country_ja
(
  Id INTEGER NOT NULL,
  Name VARCHAR(50) NOT NULL,
  PRIMARY KEY(Id)
);

Now the database has placeholders for German and Japanese localization. Unlike in row localization you have to change the table structure every time you add a new language.

Add database to the project

Use Project Wizard to create a Sisulizer project containing the database. After you have selected the database on the Database sheet you have to set the field properties.If you use the standard table name convention for localized tables Sisulizer will automatically detect the tables to be localized ones. The standard localized table name is a combination of the original table name and the language code (e.g. German table name of Description table would be Description_de or DescriptionDe). If you do not use the standard localized table name convention you must drag localized table as a child table of the original table and set the language of the table by right clicking and choosing the language from the Languages menu.

Sample table

The localized German Country table will look like this after Sisulizer has updated the localized field values:

The localized Japanese Country table will look like this after Sisulizer has updated the localized field values:

Database cloning

In this method Sisulizer creates a copy of database for each language. This method is the only localization method that does not require changing of the table structure. Database cloning is available only for those databases that use single database file such as Access and Interbase.

Add database to the project

Use Project Wizard to create a Sisulizer project containing the database. After you have selected the database on the Database sheet you have to set the field properties.Remember to check Clone original database check box in the Database sheet. Check those fields that you want to localize.

Sample table

The Country table in the German database will look like this after Sisulizer has created the database:

The Country table in the Japanese database will look like this after Sisulizer has created the database:

Samples

Sisulizer's Database directory contains database samples.