Re: Internationalisation (i18n) with Postgres as backend

Hi Steve,

I didn't consider hstore, I did consider jsonb though.

The thing that made me lean towards individual rows rather than consolidated was that I thought versioning would ultimately be easier/cleaner to achieve with individual rows (e.g. using tsrange & gist exclude).  But willing to be proven wrong.

Laura

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Tuesday, 1 June 2021 22:10, Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com> wrote:

> 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