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.

The following table compares different localization methods.

Feature	Row	Field	Table	Cloning
Works with any database	yes	yes	yes	-
Can be localized without changing the database structure	-	-	-	yes
New languages can be added without changing the database structure	yes	-	-	yes
Supports extended data	-	yes	yes	yes

In addition of localization method there are other database localization issues:

• Data type
• Extended data
• Samples

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,

Id	Name	Population
0	United States	297
1	Germany	82
2	Japan	127

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

Id	Language	Name	Population
0	en	United States	297
0	de	Vereinigte Staaten	297
0	ja	アメリカ合衆国	297
1	en	Germany	82
1	de	Deutschland	82
1	ja	ドイツ	82
2	en	Japan	127
2	de	Japan	127
2	ja	日本	127

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 integer 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,

Id	Name	Population
0	United States	297
1	Germany	82
2	Japan	127

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

RowId	ResourceId	Language	Name	Population
0	0	en	United States	297
1	1	en	Germany	82
2	2	en	Japan	127
3	0	de	Vereinigte Staaten	297
4	1	de	Deutschland	82
5	2	de	Japan	127
6	0	ja	アメリカ合衆国	297
7	1	ja	ドイツ	82
8	2	ja	日本	127

The Resource table will look like this.

Id	...
0	...
1	...
2	...

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 Name field would be Name_de or NameDe). Instead of language code you can also use Windows language or locale number (e.g. German field name of Name would be Name_7 or Name7). 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:

Id	Name	Name_de	Name_ja	Population
0	United States	Vereinigte Staaten	アメリカ合衆国	297
1	Germany	Deutschland	ドイツ	82
2	Japan	Japan	日本	127

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:

Id	Name
0	Vereinigte Staaten
1	Deutschland
2	Japan

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

Id	Name
0	アメリカ合衆国
1	ドイツ
2	日本

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 for local file based databases such as Access, SQL Server Compact, and SQLite.

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:

Id	Name	Population
0	Vereinigte Staaten	297
1	Deutschland	82
2	Japan	127

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

Id	Name	Population
0	アメリカ合衆国	297
1	ドイツ	82
2	日本	127

Data type

In most cases your database contains plain text in string fields and you do not have any other data type to be translated. In this case you do not have to do anything but select the localization method your want to use and then select the fields you want to localize. Sisulizer can also localize none-string data. For example if you database has binary fields (BLOB) that contain image data Sisulizer can localize these as well. Like with plain string data you do not have to do anything but select the field.

Some string or binary fields may contain structured data (e.g. HTML, XML, binary data). In that case Sisulizer tried to detect the format. In most cases it succeed to detect it. However some data formats can not be detected (e.g. combined strings). In that case you have to specify the format. After initial scan select the node of the database field in the project tree, right click it and choose Properties. A Database Field dialog appears. Uncheck Automatic format check box and specify the format.

Extended data

Sometimes your data is so long that it does not fit into single field. In that case you have to break a single data into multiple block and store them into multiple table, fields or rows. Sisulizer supports case where you have the first data block in the actual field but all other data block in another table. This table is called extended table and it contains the data block that does not fit to the actual field(s).

Let's have an example. Country table contains

Id	Name	Description
0	United States	The United States of America is a federal republic of 50 states, located primary on central North America.
1	Germany	Germany or the Federal Republic of Germany is one of the world's leading industrialized countries, located in the heart of Europe.
2	Japan	Japan is a country on the western edge of the Pacific Ocean.

The extended table contains extended data blocks. The following sample contains two blocks for United States and one block for Japan. There is no extended block for Germany.

Id	Block	Description
0	1	This is the first extension for US description.
0	2	This is the second extension for US description.
2	1	This is the extension for Japan description.

Block field specifies what is the order of the block in the data sequence. The first extended data block has block number 0 or 1 depending on the logic of your application. Each bock after that increase the block number by one. For example if the maximum field size is 64K and your data is 200 kbytes then the main field contains first 64 kbytes, the first exteneded block next 64 kbytes, the second block next 64 kbytes and third block the last 2 kbytes.

When Sisulizer scans the Country table it combined that actual field data with the extended blocks.

Id	Name	Description
0	United States	The United States of America is a federal republic of 50 states, located primary on central North America. This is the first extension for US description. This is the second extension for US description.
1	Germany	Germany or the Federal Republic of Germany is one of the world's leading industrialized countries, located in the heart of Europe.
2	Japan	Japan is a country on the western edge of the Pacific Ocean. This is the extension for Japan description.

Normally you should not use extended fields because it make maintaining and using database much more difficult. However some databases have 32 kbytes or 64 kbytes limit for a string field. If you have longer strings (e.g. HTML or XML) you do not have any other choice but split the data. If you use the above method Sisulizer can combine data from multiple blocks and localize strings as single entity when localizing the data.

Samples

Sisulizer's Database directory contains database samples.