Localization using SQLite and TMap

This code has been declared by its author to be Public Domain code.

Localization using SQLite and TMap by Krischan

2016

This is a very simple and easy to use localization using a SQlite database and Brucey's DBSQlite module. If you want to have different languages in your app/game just store the text in a SQlite database! SQlite databases have the advantage that they are easy to manage/edit and can be expanded to additional languages very easy, too. The language can be switched even in runtime if you want.

How it works: the language specified in the language field is loaded into a TMap and a (unique) key can be retrieved just using the LOCALE.Get("key") command. The database example uses a main category and a key category definition to group the keys. The retrievable key is always CATEGORY + "_" + KEY, the code includes an example how to use it.

For example LOCALE.Get("SOLAR_WHITEDWARF") retrieves "White Dwarf" in category "SOLAR" and key "WHITEDWARF" when LOCALE.language is set to "EN" while it retrieves the german equivalent "Weißer Zwerg" when it is set to "DE".

Test database structure screenshot in DB Browser for SQLite:

SuperStrict

Import brl.Map
Import bah.DBSQLite

Global LOCALE:TLocale = New TLocale

' create test database
LOCALE.Create

' english
LOCALE.language = "EN"
LOCALE.Load
Print "English:"
Print LOCALE.Get("GAME_TITLE")
Print LOCALE.Get("SOLAR_MAINSEQUENCE")
Print LOCALE.Get("SOLAR_GIANT")
Print LOCALE.Get("SOLAR_WHITEDWARF")
Print LOCALE.Get("SOLAR_YEARS")

Print

' german
LOCALE.language = "DE"
LOCALE.Load
Print "German:"
Print LOCALE.Get("GAME_TITLE")
Print LOCALE.Get("SOLAR_MAINSEQUENCE")
Print LOCALE.Get("SOLAR_GIANT")
Print LOCALE.Get("SOLAR_WHITEDWARF")
Print LOCALE.Get("SOLAR_YEARS")

Type TLocale

	Global Locale:TMap = CreateMap()			' Global TMap with all text
	Global db:TDBConnection						' database connection
	Global q:TDatabaseQuery						' database query
	
	Field language:String = "EN"				' the current locale Flag
	Field database:String = "locale"			' the database file name
	
	Method Create()

		DeleteFile database + ".db"
		
		db = LoadDatabase("SQLITE", database + ".db")
		
		db.executeQuery("CREATE TABLE locale (id integer primary key AUTOINCREMENT, category varchar(32), key varchar(32), EN text, DE text)")
		db.executeQuery("INSERT INTO locale Values (NULL,'GAME','TITLE','Extrasolar Origin','Extrasolar Origin')")
		db.executeQuery("INSERT INTO locale Values (NULL,'SOLAR','MAINSEQUENCE','Main Sequence','Hauptreihe')")
		db.executeQuery("INSERT INTO locale Values (NULL,'SOLAR','GIANT','Giant','Riese')")
		db.executeQuery("INSERT INTO locale Values (NULL,'SOLAR','WHITEDWARF','White Dwarf','Weißer Zwerg')")
		db.executeQuery("INSERT INTO locale Values (NULL,'SOLAR','YEARS','Years','Jahre')")
								
		db.close()
	
	End Method

	Method Load()
		
		db = LoadDatabase("SQLITE", database + ".db")
		If Not db Then db.close()
		
		q = db.executeQuery("SELECT * from locale")
	
		While q.nextRow()

			' get record
			Local r:TQueryRecord = q.rowRecord()
			Local cat:String = r.getStringByName("category")
			Local Key:String = r.getStringByName("key")
			
			MapInsert(LOCALE, cat + "_" + Key, r.getStringByName(language))
									
		Wend
		
		db.close()
				
	End Method

	' ------------------------------------------------------------------------------------------------
	' Returns Locale Object
	' ------------------------------------------------------------------------------------------------
	Method Get:String(Key:String)
	
		Return String(MapValueForKey(LOCALE, Key))
			
	End Method

End Type

Comments

Derron

2016

You should reorganize your SQL structure. Keyword of the week is "normalization".

Once you did it, it should be no hassle to have "unlimited" languages within the DB instead of an ever-growing table.

In short:
TBL_KEYS
ID | GROUP | KEY
1 | "SOLAR" | "MAINSEQUENCE"

TBL_LOCALIZATION
ID | KEY_ID | LANGUAGE_CODE | VALUE
1 | 1 | "en" | "Main Sequence"
2 | 1 | "de" | "Hauptreihe"

As you do the loading only "once" in your app, it is not that much overhead to now have to use a "join" in your SQL statements.

You could even extend it to "TBL_KEYS" contain the default english value and only override it by some localization (but this isnt "normalization" as described in the books).

bye
Ron

Krischan

2016

Ah I see. My intention was to have an Excel-Style table I could edit very easy. There are different approaches for localization and I found this simple way good enough for my game. It's not efficient but clearly arranged.

Brucey

2016

Mostly like what Derron says, since every time you want to add a new language you'd need to change your database structure (and presumably your code), to include a new column.

Although I disagree with

You could even extend it to "TBL_KEYS" contain the default english value

If you have a table for texts, there's no need to represent the default language in a further different table.

I'd even go so far as to create a table which lists available languages, which you could use in your program for menu selection or such.
This way your app never actually needs to know what languages are available until you execute it - you could add a new language/translation without recompiling.

Derron

2016

I wanted to avoid this depth of normalization:

tbl_localization: ID | Value
-> 1 | "Guten Tag"
-> 2 | "Bonjour"
-> 3 | "Gute Nacht"
-> 4 | "Bonne nuit!"

tbl_language: ID | value
-> 1 | "german"
-> 2 | "french"

tbl_language_has_localization: ID | languageID | localizationID
-> 1 | 1 | 1
-> 2 | 1 | 3
-> 3 | 2 | 2
-> 4 | 2 | 4

The third table is the one "connecting" everything.

But if you do not care about temporary tables (am not sure but I assume it creates one) you could even store the "language" as a kind of "number, value" pair and therefore would have just
tbl_language: ID | localizationID
-> 1 | 1
-> 2 | 3

tbl_localization: ID | Value
-> 1 | "german"
-> 2 | "deutsch"
-> 3 | "french"
-> 4 | "franzoesisch"
-> 5 | "Guten Tag"
-> 6 | "Bonjour"
-> 7 | "Gute Nacht"
-> 8 | "Bonne nuit!"

I would prefer the first one - is it more "common sense" regarding SQL and normalization.

BTW: you want to localize "language" too so you get the language choice in _your_ language, not the mother language:

German | English | Czech
compared to
Deutsch | English | Ceskı
(the C is wrong but forum does not allow this sign)

You could just add the languages to "tbl_localization" and reuse them correctly

@ tbl_keys and default value
This is used when trying to minimize "joins" or "queries" as the default value is available without much hassle (else you need to join the localization from right and this is surely more to do for the DB than just returning the default-value-string together with a potentially not existing one). Of course it redirects the logic to the app (or another portion in the query) but I saw that in some applications trying to be "lightwheight".

bye
Ron

Blitzplotter

2016

This thread is great, I'm going to use it to consolidate (read blow the cobwebs off) the database normalization I did at college.... 9 years ago. Use it or lose it ;)

Krischan

2016

> Mostly like what Derron says, since every time you want to add a new language you'd need to change your database structure (and presumably your code), to include a new column.

Hmm why? I only add another language column in the database and the Get() Method retrieves the phrase by the fixed key as long as the set language matches the language field. Only a single file is updated - the SQlite database. So I don't see any need for a recompilation in this case. Like I said, perhaps it is not the most elegant or effective method but it's very simple and it works for me (KISS principle).

And I don't need unlimited languages, I'm happy if I have at least two or three (English, German and perhaps French/Spanish later if I find somebody who translates it for me).

How many games support dozens or hundreds of languages? ;-)

Derron

2016

>How many games support dozens or hundreds of languages? ;-)

In my game you drop in the new language-file in res/langs/... and it is there.
Optionally you add a "flag-graphic" to one of the ressource-xmls (so it looks good in the language drop down) and voila.

@ KISS
Of course you could do that, but for a SQL-"user" you cannot just "guess" if a column exists (there are commands to get the structure .. for a reason).

One thing with your approach is "data efficiency" - once you got eg. 10k localization entries (this is _not_ as much as it sounds) your DB will reserve 10k * fieldCount cells.

The normalization approach just stores what is "filled", empty cells wont be existing. In your case for all 10k entries a new cell is created as soon as _one_ word in a new language is localized.

Of course your code will do and keeps things simpler - but then I would prefer loading the whole thing as CSV and parse that directly into some arrays. So you could do your localization in Excel/Calc. Ok, you need to make sure about "separator escape" but this is recognized automatically by Excel/Calc.
This allows for whatever editor you want (even notepad) as long as you keep the "escaping" intact.

The more versatile approach is, what we described above. And it becomes even more necessary, if third party tools get involved (eg. an online translator for your project).

bye
Ron

Code Archives Forum