Re: Internationalisation (i18n) with Postgres as backend

Hi Laura,

Did you consider using hstore to store language and data as a kvp? For
example:

b2bc_owner(at)b2bcreditonline=# create table langtest(pageid text, objectid
text, objectdata hstore, constraint langtest_pk primary key (pageid,
objectid));
CREATE TABLE
b2bc_owner(at)b2bcreditonline=# insert into langtest values ('zz', 'abc',
'"en"=>"en for abc","de"=>"de for abc"');
INSERT 0 1
b2bc_owner(at)b2bcreditonline=# insert into langtest values ('zz', 'def',
'"en"=>"en for def"');
INSERT 0 1
b2bc_owner(at)b2bcreditonline=# create or replace function langtestfunc(text,
text, text[]) returns text language sql as $$ select a.data from langtest
as t, unnest(t.objectdata->$3) as a(data) where t.pageid = $1 and
t.objectid = $2 and a.data is not null limit 1 $$;
b2bc_owner(at)b2bcreditonline=# select langtestfunc('zz', 'abc', array['de',
'en']);
langtestfunc
--------------
de for abc
(1 row)
b2bc_owner(at)b2bcreditonline=# select langtestfunc('zz', 'def', array['de',
'en']);
langtestfunc
--------------
en for def
(1 row)

Just a thought.

Cheers,

Steve

On Wed, Jun 2, 2021 at 6:09 AM Laura Smith <
n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch> wrote:

> Hi,
>
> I'm creating a Postgres backend for an internal tool which is essentially
> a very simple implementation of multi-lingual CMS.
>
> So far my thoughts are along the lines of the below, but I would
> appreciate a second (or more !) pair of eyes from some Postgresql gurus. I
> am especially interested in feedback and suggestions in relation to the
> following questions:
>
> (a) Is this going to work as expected (i.e. have I missed some obvious
> foot-guns ?)
>
> (b) Is this manner of doing things reasonably efficient or are there
> better ways I should be thinking of ? (bear in mind the schema is not set
> in stone, so completely out of the box suggestions welcome !).
>
> The basic design concept (oversimplified) is: For each page, you have one
> or more objects and those objects may have content in one or more languages.
>
> create table langtest(
> pageid text not null,
> objectid text not null ,
> objectlang text not null,
> objectdata text not null);
>
> create unique index on (pageid,objectid,objectlang);
>
> insert into langTest(pageID,objectID,objectLang,objectData) values
> ('zzz','abc','en','Lorem ipsum dolor sit amet');
> insert into langTest(pageID,objectID,objectLang,objectData) values
> ('zzz','abc','de','Amet sit dolor ipsum lorem');
> insert into langTest(pageID,objectID,objectLang,objectData) values
> ('zzz','def','en','Dolor ipsum amet sit lorem');
>
> select distinct on(objectid)objectid,objectlang,pageid,objectdata from
> langTest where pageid='zzz' and objectLang = any('{de,en}'::text[]) order
> by objectid,array_position('{de,en}'::text[],objectLang);
>
> (The idea being that the select query will be wrapped into a function
> which the frontend will call, passing a list of elegible languages as input)
>
> Thanks !
>
> Laura
>
>
>