Logtext with Parameters using sqlite3

Introduction

This example shows how to setup a multi language log in sqlite3 and get translated text containing (translated) parameters with just one query.

Background

Many embedded devices use sqlite3 for storage of settings or logdata. This example shows how to setup a logdata table which contains parameters and text in multiple languages. I created a group_replace extension in sqlite3. This way, you can select the data from this table and let sqlite fill in the parameters of your log. Mixing this function with SQL's build in coalesce function, you can present the logdata in the users language with just one query.

Using the Code

Here is the example code:

CREATE TABLE "texts"(
  "text_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "key" TEXT
);
CREATE TABLE "languages"(
  "language_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "name" TEXT,
  "native_name" TEXT,
  "language_code" TEXT
);
CREATE TABLE "logdata"(
  "logdata_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "timestamp" INTEGER,
  "debug" TEXT,
  "log_text_id" INTEGER NOT NULL,
  CONSTRAINT "fk_logdata_texts1"
    FOREIGN KEY("log_text_id")
    REFERENCES "texts"("text_id")
    ON DELETE CASCADE
);
CREATE INDEX "logdata.fk_logdata_texts1_idx" ON "logdata"("log_text_id");
CREATE TABLE "logparameter"(
  "logparameter_id" INTEGER PRIMARY KEY NOT NULL,
  "logdata_id" INTEGER NOT NULL,
  "key" TEXT,
  "text_id" INTEGER,
  "text" TEXT,
  CONSTRAINT "fk_translated_parameter_texts1"
    FOREIGN KEY("text_id")
    REFERENCES "texts"("text_id")
    ON DELETE CASCADE,
  CONSTRAINT "fk_translated_parameter_logdata1"
    FOREIGN KEY("logdata_id")
    REFERENCES "logdata"("logdata_id")
    ON DELETE CASCADE
);

CREATE TABLE "text_translations"(
  "text_translation_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "text_id" INTEGER NOT NULL,
  "language_id" INTEGER NOT NULL,
  "text" TEXT,
  CONSTRAINT "fk_text_translations_texts1"
    FOREIGN KEY("text_id")
    REFERENCES "texts"("text_id")
    ON DELETE CASCADE,
  CONSTRAINT "fk_text_translations_languages1"
    FOREIGN KEY("language_id")
    REFERENCES "languages"("language_id")
    ON DELETE CASCADE
);
CREATE INDEX "text_translations.fk_text_translations_texts1_idx" _
ON "text_translations"("text_id");
CREATE INDEX "text_translations.fk_text_translations_languages1_idx" _
ON "text_translations"("language_id");

INSERT INTO languages (name,native_name, language_code) 
    VALUES ('Dutch', 'Nederlands', 'nl'), 
           ('English', 'English', 'en');
INSERT INTO texts (key) VALUES ('log_text_send'), ('green');
INSERT INTO text_translations (text_id, language_id, text) 
    VALUES (1,1, 'Er is een SMS verstuurd naar %name met telefoon nummer %nr, favoriete kleur %color'),
           (1,2, 'A text message has been send to nr %nr for user %name who likes color %color'),
           (2,1, 'groen'),
           (2,2, 'green');
INSERT INTO logdata (log_text_id) values(1);
INSERT INTO logparameter(logdata_id,key, text_id, text) 
    VALUES (1,'%name', null, 'anthony'),
           (1,'%nr',   null, '+31612341234'),
           (1,'%color',2,    null);

SELECT load_extension('./sqlite3_group_replace_extension.so');

SELECT group_replace(tt.text, lp.key, COALESCE(vtt.text,lp.text)) FROM logdata l
JOIN text_translations tt ON tt.text_id=l.log_text_id AND tt.language_id=1 
LEFT JOIN logparameter lp ON lp.logdata_id=l.logdata_id
LEFT JOIN text_translations vtt ON vtt.text_id=lp.text_id AND vtt.language_id=1 
GROUP BY l.logdata_id;

The above example code results in this translated log text: "Er is een SMS verstuurd naar anthony met telefoon nummer +31612341234, favoriete kleur groen"

If we want the result in English, we just use another language code:

SELECT group_replace(tt.text, lp.key, COALESCE(vtt.text,lp.text)) FROM logdata l 
JOIN text_translations tt ON tt.text_id=l.log_text_id AND tt.language_id=2 
LEFT JOIN logparameter lp ON lp.logdata_id=l.logdata_id 
LEFT JOIN text_translations vtt ON vtt.text_id=lp.text_id AND vtt.language_id=2 
GROUP BY l.logdata_id;

This results in: "A text message has been send to nr +31612341234 for user anthony who likes color green"

This example shows:

• parameters can have a different order in each language
• parameters can be a variable text such as user input, or a translated text string (color in the above example)

See https://github.com/adesys/sqlite3_group_replace_extension for the group_replace extension.

Points of Interest

This is just an example of how to use the group_replace function. For example, you could add a date column (to logparameters) to print a date in format depending on the current user's country.

Other data types, such as integers, can be inserted in the logparameters table by casting it to a text.

History

• 15^th August, 2016: Initial version