Re: Internationalisation (i18n) with Postgres as backend - Mailing list pgsql-general

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
ultimatelybe easier/cleaner to achieve with individual rows (e.g. using tsrange & gist exclude).  But willing to be
provenwrong. 

Laura

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Tuesday, 1 June 2021 22:10, Steve Baldwin <steve.baldwin@gmail.com> wrote:

> Hi Laura,
>
> Did you consider using hstore to store language and data as a kvp? For example:
>
> b2bc_owner@b2bcreditonline=# create table langtest(pageid text, objectid text, objectdata hstore, constraint
langtest_pkprimary key (pageid, objectid)); 
> CREATE TABLE
> b2bc_owner@b2bcreditonline=# insert into langtest values ('zz', 'abc', '"en"=>"en for abc","de"=>"de for abc"');
> INSERT 0 1
> b2bc_owner@b2bcreditonline=# insert into langtest values ('zz', 'def', '"en"=>"en for def"');
> INSERT 0 1
> b2bc_owner@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.datais not null limit 1 $$; 
> b2bc_owner@b2bcreditonline=# select langtestfunc('zz', 'abc', array['de', 'en']);
>  langtestfunc
> --------------
>  de for abc
> (1 row)
> b2bc_owner@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@protonmail.ch> wrote:
>
> > Hi,
> >
> > I'm creating a Postgres backend for an internal tool which is essentially a very simple implementation of
multi-lingualCMS. 
> >
> > So far my thoughts are along the lines of the below, but I would appreciate a second (or more !) pair of eyes from
somePostgresql 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
mindthe 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
havecontent 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
ofelegible languages as input) 
> >
> > Thanks !
> >
> > Laura