Skip to content

Create a Translationsystem that deals with delivered items, JSON Structure of ContenItems and performance and as a Bonus works with graphile

A "SQLish" concept - with only 4 languages

Testable via https://repco.arbeit.cba.media/graphiql

Items without Translation become null ( what is maybe smart) Performance is on Dev ( many items - but not so many as Live) ok for single records. But a query over many items takes time

4257 records in 14s SELECT *,uid,t_title,title FROM public."Translation" WHERE "contentUrl"::text Like '%new%' LIMIT 10000

Limit 50: 0:0:0:357 ( fast as content items)

I mean this is not that bad, cause no smart index are added in this concept.

--------- Test Graph --------

query MyQuery {
  translations(condition: {uid: "eayughhwwbcbqrw4cgscagnvqee"}) {
    edges {
      node {
        tTitle
        title
        pubDate
        contentUrl
        contentFormat
        content
        licenseUid
        originalLanguages
        primaryGroupingUid
        publicationServiceUid
        removed
        revisionId
        subtitle
        summary
        tContent
        tSubtitle
        tSummary
        uid
      }
    }
    totalCount
  }
}

--------- First Test --------

create or replace function jsonb_merge_recurse(orig jsonb, delta jsonb)
returns jsonb language sql as $$
    select
        jsonb_object_agg(
            coalesce(keyOrig, keyDelta),
            case
                when valOrig isnull then valDelta
                when valDelta isnull then valOrig
                when (jsonb_typeof(valOrig) <> 'object' or jsonb_typeof(valDelta) <> 'object') then valDelta
                else jsonb_merge_recurse(valOrig, valDelta)
            end
        )
    from jsonb_each(orig) e1(keyOrig, valOrig)
    full join jsonb_each(delta) e2(keyDelta, valDelta) on keyOrig = keyDelta
$$;

CREATE OR REPLACE FUNCTION public.getmlbase(
	lang text)
    RETURNS TABLE(uid text, revisionid text, queryparam text, title text, summary text, content text, originallanguages text, contenturl text, pubdate timestamp without time zone) 
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
    SELECT 
	uid,
	"revisionId",
	$1 as param,
	TRIM(BOTH '"'::text FROM (((title ->> $1::text)::jsonb) -> 'value'::text)::text) AS title,
    TRIM(BOTH '"'::text FROM (((summary ->> $1::text)::jsonb) -> 'value'::text)::text) AS summary,
    TRIM(BOTH '"'::text FROM (((content ->> $1::text)::jsonb) -> 'value'::text)::text) AS content,
    TRIM(BOTH '"'::text FROM ((("originalLanguages" ->> 'language_codes'::text)::jsonb) -> 0)::text) AS "origlang",
	"contentUrl",
	"pubDate"
	FROM
	"ContentItem"
	WHERE 
	removed = false
	AND TRIM(BOTH '"'::text FROM ((("originalLanguages" ->> 'language_codes'::text)::jsonb) -> 0)::text) = $1
	;
$BODY$;

ALTER FUNCTION public.getmlbase(text)
    OWNER TO repco;

CREATE TABLE IF NOT EXISTS public."ContentItemTranslation"
(
    uid text COLLATE pg_catalog."default" NOT NULL,
    "targetLanguage" text COLLATE pg_catalog."default" NOT NULL,
    title text COLLATE pg_catalog."default" NOT NULL,
    subtitle text COLLATE pg_catalog."default" NOT NULL,
    summary text COLLATE pg_catalog."default" NOT NULL,
    "summaryCleaned" text COLLATE pg_catalog."default" NOT NULL,
    content text COLLATE pg_catalog."default" NOT NULL,
    "contentCleaned" text COLLATE pg_catalog."default" NOT NULL,
    metadata text COLLATE pg_catalog."default" NOT NULL,
    engine text COLLATE pg_catalog."default" NOT NULL,
    "createdAt" timestamp(3) without time zone NOT NULL,
    "updatedAt" timestamp(3) without time zone NOT NULL,
    "revisionId" text COLLATE pg_catalog."default" NOT NULL,
    "contentItemUid" text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "ContentItemTranslation_pkey" PRIMARY KEY (uid),
    CONSTRAINT "ContentItemTranslation_contentItemUid_fkey" FOREIGN KEY ("contentItemUid")
        REFERENCES public."ContentItem" (uid) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT "ContentItemTranslation_revisionId_fkey" FOREIGN KEY ("revisionId")
        REFERENCES public."Revision" (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT
)

--- UNique uid deleted not relevant for this tests ---

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."ContentItemTranslation"
    OWNER to repco;


INSERT INTO "ContentItemTranslation"  ( 
uid, "targetLanguage", title, subtitle , summary, "summaryCleaned",
content, "contentCleaned", 
metadata, 
engine, "createdAt", "updatedAt", 
"contentItemUid","revisionId"
) 
SELECT  
CONCAT('trans_',uid),
"getmlbase"."originallanguages",
title, 'notnull' as subtitle,
summary,
summary as "summaryCleaned",
content, content as "contentCleaned",
'Test put' as meta,
'PG' as engine,
'2011-09-02 11:42:18' as "createdAt", '2011-09-02 11:42:18' as "updatedAt",
 uid as "contentItemUid","revisionid" as "revisionId"
FROM getMlBase('en')

INSERT INTO "ContentItemTranslation"  ( 
uid, "targetLanguage", title, subtitle , summary, "summaryCleaned",
content, "contentCleaned", 
metadata, 
engine, "createdAt", "updatedAt", 
"contentItemUid","revisionId"
) 
SELECT  
CONCAT('trans_',uid),
"getmlbase"."originallanguages",
title, 'notnull' as subtitle,
summary,
summary as "summaryCleaned",
content, content as "contentCleaned",
'Test put' as meta,
'PG' as engine,
'2011-09-02 11:42:18' as "createdAt", '2011-09-02 11:42:18' as "updatedAt",
 uid as "contentItemUid","revisionid" as "revisionId"
FROM getMlBase('de')

INSERT INTO "ContentItemTranslation"  ( 
uid, "targetLanguage", title, subtitle , summary, "summaryCleaned",
content, "contentCleaned", 
metadata, 
engine, "createdAt", "updatedAt", 
"contentItemUid","revisionId"
) 
SELECT  
CONCAT('trans_',uid),
"getmlbase"."originallanguages",
title, 'notnull' as subtitle,
summary,
summary as "summaryCleaned",
content, content as "contentCleaned",
'Test put' as meta,
'PG' as engine,
'2011-09-02 11:42:18' as "createdAt", '2011-09-02 11:42:18' as "updatedAt",
 uid as "contentItemUid","revisionid" as "revisionId"
FROM getMlBase('pl')

INSERT INTO "ContentItemTranslation"  ( 
uid, "targetLanguage", title, subtitle , summary, "summaryCleaned",
content, "contentCleaned", 
metadata, 
engine, "createdAt", "updatedAt", 
"contentItemUid","revisionId"
) 
SELECT  
CONCAT('trans_',uid),
"getmlbase"."originallanguages",
title, 'notnull' as subtitle,
summary,
summary as "summaryCleaned",
content, content as "contentCleaned",
'Test put' as meta,
'PG' as engine,
'2011-09-02 11:42:18' as "createdAt", '2011-09-02 11:42:18' as "updatedAt",
 uid as "contentItemUid","revisionid" as "revisionId"
FROM getMlBase('it')


SELECT DISTINCT "targetLanguage" FROM public."ContentItemTranslation"

UPDATE public."ContentItemTranslation"
SET 
content = CONCAT('PL pl pl ',content),
summary = CONCAT('PL pl pl ',summary),
title = CONCAT('PL pl pl ',title),
subtitle = CONCAT('PL pl pl ',subtitle)
WHERE "targetLanguage" = 'pl'

UPDATE public."ContentItemTranslation"
SET 
content = CONCAT('it it it ',content),
summary = CONCAT('it it it ',summary),
title = CONCAT('it it it ',title),
subtitle = CONCAT('it it it ',subtitle)
WHERE "targetLanguage" = 'it'

UPDATE public."ContentItemTranslation"
SET 
content = CONCAT('de de de ',content),
summary = CONCAT('de de de ',summary),
title = CONCAT('de de de ',title),
subtitle = CONCAT('de de de ',subtitle)
WHERE "targetLanguage" = 'de'

UPDATE public."ContentItemTranslation"
SET 
content = CONCAT('en en en ',content),
summary = CONCAT('en en en ',summary),
title = CONCAT('en en en ',title),
subtitle = CONCAT('en en en ',subtitle)
WHERE "targetLanguage" = 'en'




Select 
	jsonb_merge_recurse(
		"ContentItem".title,
		jsonb_build_object(
			'it',jsonb_build_object('value',it.title),
			'de',jsonb_build_object('value',de.title),
			'en',jsonb_build_object('value',en.title),
			'pl',jsonb_build_object('value',pl.title)
		)
	) as t_title,

	jsonb_merge_recurse(
		"ContentItem".subtitle,
		jsonb_build_object(
			'it',jsonb_build_object('value',it.subtitle),
			'de',jsonb_build_object('value',de.subtitle),
			'en',jsonb_build_object('value',en.subtitle),
			'pl',jsonb_build_object('value',pl.subtitle)
		)
	) as t_subtitle,

	jsonb_merge_recurse(
	"ContentItem".summary,
		jsonb_build_object(
			'it',jsonb_build_object('value',it.summary),
			'de',jsonb_build_object('value',de.summary),
			'en',jsonb_build_object('value',en.summary),
			'pl',jsonb_build_object('value',pl.summary)
		)
	) as t_summary,

	jsonb_merge_recurse(
	"ContentItem".content,
		jsonb_build_object(
			'it',jsonb_build_object('value',it.content),
			'de',jsonb_build_object('value',de.content),
			'en',jsonb_build_object('value',en.content),
			'pl',jsonb_build_object('value',pl.content)
		)
	) as t_content
	

FROM 
"ContentItem"
LEFT JOIN "ContentItemTranslation" as en ON (
	"ContentItem".uid = en."contentItemUid"
	AND en."targetLanguage" = 'en'
)
LEFT JOIN "ContentItemTranslation" as pl ON (
	"ContentItem".uid = pl."contentItemUid"
	AND pl."targetLanguage" = 'pl'
)
LEFT JOIN "ContentItemTranslation" as de ON (
	"ContentItem".uid = de."contentItemUid"
	AND de."targetLanguage" = 'de'
)
LEFT JOIN "ContentItemTranslation" as it ON (
	"ContentItem".uid = it."contentItemUid"
	AND it."targetLanguage" = 'it'
)
--WHERE "ContentItem".uid = 'eayupl264kvsaraifppmnptgm6a'
--AND t."targetLanguage" = 'en'










-- View: public.Translations

-- DROP VIEW public."Translations";

CREATE OR REPLACE VIEW public."Translations"
 AS
 SELECT jsonb_merge_recurse("ContentItem".title, jsonb_build_object('en', jsonb_build_object('value', en.title), 'pl', jsonb_build_object('value', pl.title))) AS t_title,
    jsonb_merge_recurse("ContentItem".subtitle, jsonb_build_object('en', jsonb_build_object('value', en.subtitle), 'pl', jsonb_build_object('value', pl.subtitle))) AS t_subtitle,
    jsonb_merge_recurse("ContentItem".summary, jsonb_build_object('en', jsonb_build_object('value', en.summary), 'pl', jsonb_build_object('value', pl.summary))) AS t_summary,
    jsonb_merge_recurse("ContentItem".content, jsonb_build_object('en', jsonb_build_object('value', en.content), 'pl', jsonb_build_object('value', pl.content))) AS t_content,
    "ContentItem".uid,
    "ContentItem"."revisionId",
    "ContentItem"."pubDate",
    "ContentItem"."contentFormat",
    "ContentItem"."primaryGroupingUid",
    "ContentItem"."licenseUid",
    "ContentItem"."publicationServiceUid",
    "ContentItem".title,
    "ContentItem".summary,
    "ContentItem".content,
    "ContentItem"."originalLanguages",
    "ContentItem".removed,
    "ContentItem"."contentUrl",
    "ContentItem".subtitle
   FROM "ContentItem"
     LEFT JOIN "ContentItemTranslation" en ON "ContentItem".uid = en."contentItemUid" AND en."targetLanguage" = 'en'::text
     LEFT JOIN "ContentItemTranslation" pl ON "ContentItem".uid = pl."contentItemUid" AND pl."targetLanguage" = 'pl'::text;

ALTER TABLE public."Translations"
    OWNER TO repco;




--------
Graphen
query MyQuery {
  translations(condition: {uid: "eayughhwwbcbqrw4cgscagnvqee"}) {
    edges {
      node {
        tTitle
        title
        pubDate
        contentUrl
        contentFormat
        content
        licenseUid
        originalLanguages
        primaryGroupingUid
        publicationServiceUid
        removed
        revisionId
        subtitle
        summary
        tContent
        tSubtitle
        tSummary
        uid
      }
    }
    totalCount
  }
}


query MyQuery {
  contentItems(first: 10, condition: {search: "Soviet AND Becoming AND case"}) {
    edges {
      node {
        uid
        pubDate
        title
        contentUrl
        contentItemTranslations {
          edges {
            node {
              engine
              revisionId
              contentItemUid
              subtitle
              summary
              summaryCleaned
              targetLanguage
              content
              contentCleaned
              title
              updatedAt
            }
          }
          totalCount
        }
        revisionId
      }
    }
    totalCount
  }
}
Edited by Urbas Gerald

gitlab project and software management by fairkom.eu - more open source web apps at fairapps.net