How to manage multilanguage

[a-gonzalo]

Hello everyone,

I was thinking about how SQLPage can handle multilingual support, in the sense that each user can choose the language offered by the application.

I'm somewhat used to working with Java environments, and I know how Bundles work, but... In the case of having to set up SQL queries for the display, I don't see how to do it in a way that you don't have to repeat 20 queries for each tag of the application.

My idea was to make a language table, with a column for each language mapped to the tags column (like a bundle, so to speak). How would you do it?

[lovasoa]

Hello!

There is currently no solution for this that isn't a little verbose.

you can use a single language table like this:

CREATE TABLE language (
    translation_key VARCHAR(255) PRIMARY KEY,
    language_code VARCHAR(5),
    translation_value VARCHAR(255)
);

Here's an example of querying the translation for "homepage.hello_world" based on the user's language:

SELECT l.translation_value
FROM language l
JOIN user u ON l.language_code = u.language_code
WHERE l.translation_key = 'homepage.hello_world' AND u.session_id = sqlpage.cookie('session');

to make this a little less verbose, you can define a function if your database supports stored procedures:

-- assuming postgres syntax
CREATE OR REPLACE FUNCTION t(user_id INT, key VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    translation_value VARCHAR;
BEGIN
    SELECT l.translation_value INTO translation_value
    FROM language l
    JOIN user u ON l.language_code = u.language_code
    WHERE l.translation_key = key AND u.user_id = user_id;

    RETURN translation_value;
END;
$$ LANGUAGE plpgsql;

Then use

SELECT t($uid, 'homepage.hello_world');

[a-gonzalo]

So, If I want to create a login page for example, I'll probably need something like:

SELECT 
    'form' AS component,
    (select t($language, 'login.validate') as validate; -- Assuming the Language could be a parameter

SELECT
   (select t($language, 'login.user') as label,
   'User' as name;
SELECT
    (select t($language, 'login.password') as label,
   'Password' as name;

I mean, It could work, but creating an entire application like this would be a pain.

I don't know if programmaticaly combining with SQLPage could be done with something more maintaineable.

The other option I was thinking is about creating different .sql per language, and load them with a box or something on the page, but maintaining that could be something like hell too xD

[lovasoa]

Yes, your example is exactly what I meant.

But indeed, if your requirements start getting more complicated, then you'll probably be better off with something a little bit heavier than sqlpage, where i18n is baked in.

[lovasoa]

Actually you don't need the extra select :

SELECT
   t($language, 'login.password') as label,
   'Password' as name;

[anayrat]

An idea could be a lookup table and sqlpage function to lookup the table ?

CREATE TABLE language (
    translation_key VARCHAR(255) PRIMARY KEY,
    language_code VARCHAR(5),
    translation_value VARCHAR(255)
);

With :

• Record 1:
  • translation_key = tl42
  • language_code = fr
  • translation_value = 'Bonjour'

SELECT  sqlpage.translate('tl42','en','Hello world')
-- 'Hello world is the default string'. If we have a global settings like 'language', sqlpage could replace the string according to the lookup table.  

[lovasoa]

CREATE OR REPLACE FUNCTION translate(language VARCHAR(5), key VARCHAR(255))
RETURNS VARCHAR(255) AS $$
DECLARE
    result VARCHAR(255);
BEGIN
    SELECT translation_value INTO result
    FROM language
    WHERE language_code = language AND translation_key = key;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

[lovasoa]

or in MySQL

DELIMITER //

CREATE FUNCTION translate(language VARCHAR(5), key VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
    DECLARE result VARCHAR(255);
    SELECT translation_value INTO result
    FROM language
    WHERE language_code = language AND translation_key = key;
    RETURN result;
END //

DELIMITER ;

[anayrat]

Why would you need a SQLPage function? This can be a normal sql stored procedure, or just a sql subquery.

Indeed, the idea is it could be integrated in sqlpage :)

[lovasoa]

Currently, sqlpage never runs any SQL "behind your back". It runs only the code you write. I like this property of the tool.

[anayrat]

I understand :)

[sqlpagetom]

Hello everyone, and thank you for the amazing work on SQLPage!
I’d like to add multilingual support and wanted to share the approach I’m considering. What do you think?

create table if not exists lang2 AS 
    select 'fr' AS l, 
    'Bonjour' as __hello, 
    'Saisir la tâche' as __task,
    'Oui' as __yes,
    'Non' as __no
    union all
    select 'en' , 
    'Hello' , 
    'Type task',
    'Yes',
    'No'
;


select
    'text' as component, 
    __hello as title,
    'Change language : [FR](?l=fr) - [EN](?l=en)
 test : 
 ' || __hello ||' - ' || __task
as contents_md

from lang2 where l=IIF(NOT EXISTS (SELECT 1 FROM lang2 where l = $l) ,'fr',$l)
;





SELECT 'form' AS component, 'multipart/form-data' AS enctype;
SELECT 
    CASE 
        WHEN $edit IS NOT NULL THEN 
            (SELECT task FROM todos WHERE id = $edit) 
        ELSE '' 
    END                          AS value,
    'task'                       AS name,
    __task           AS label,
    'text'                       AS type, 
    __hello    AS placeholder,
TRUE                            AS autofocus
from lang2 where l=IIF(NOT EXISTS (SELECT 1 FROM lang2 where l = $l) ,'fr',$l)
;

[lovasoa]

This works but you would need to constantly change your database schema at you add new strings

[sqlpagetom]

Thank for your feedback!