0

Im looking for advise because my experience is not really good in SQLite.

My table look like this:

CREATE TABLE IF NOT EXISTS Route (
  id integer PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL,
  name text NOT NULL,
  localizedName blob NOT NULL
);

Im storing the localized name of the route as a binary blob in the localizedName property(it can hold multiple strings like English, French etc...). then to retrieve it I use a special SQL function

SELECT * FROM Route WHERE tkLocalizedString('en', localizedName) LIKE ?1;

tkLocalizedString is a function written in C programming language and is added to SQLite at runtime and it accept 2 parameters, the first is the language code (ISO 639-2), the second is the localized string blob

localizedName is a binary string table. The key is the language id and the value is the string.

Is this a good practice? Is there a better approach?

1 Answer 1

0

I ended up using a separate table to hold translation because maintaining the custom functions and translation blob is a non necessary complexity and is not a portable solution.

CREATE TABLE IF NOT EXISTS Route (
  id integer PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL,
  name text NOT NULL,
  nameLocalizationId INTEGER REFERENCES Localization(id)
);

The localization table have 3 columns:

CREATE TABLE IF NOT EXISTS Localization (
    id INTEGER NOT NULL,
    language TEXT NOT NULL,
    text TEXT NOT NULL
);
  • id is the translations id (its possible to have multiple rows with same id),
  • language is the BCP 47 language tag,
  • text is the localized string.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.