Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / SQL
Tip/Trick

Logtext with Parameters using sqlite3

Rate me:
Please Sign up or sign in to vote.
4.67/5 (3 votes)
15 Aug 2016CPOL1 min read 5.6K   4  
Using sqlite3 for a multi language log where logdata can contain parameters.

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:

SQL
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:

SQL
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

  • 15th August, 2016: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Netherlands Netherlands
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --