CREATE TABLE IF NOT EXISTS wcc_contentful_schema_version (

version integer PRIMARY KEY,
updated_at timestamp DEFAULT now()

);

START TRANSACTION;

CREATE TABLE IF NOT EXISTS contentful_raw (

-- The Contentful 'sys'->'id'
id varchar PRIMARY KEY,
-- The contentful entry
data jsonb,
-- Every ID that this entry links to in 'fields'->*->[each locale]->'sys'->'id'
links text[]

); CREATE INDEX IF NOT EXISTS contentful_raw_value_type ON contentful_raw ((data->'sys'->>'type')); CREATE INDEX IF NOT EXISTS contentful_raw_value_content_type ON contentful_raw ((data->'sys'->'contentType'->'sys'->>'id'));

– Insert or update a Contentful entry by it's ID CREATE or replace FUNCTION “fn_contentful_upsert_entry”(_id varchar, _data jsonb, _links text[]) RETURNS jsonb AS $$ DECLARE

prev jsonb;

BEGIN

SELECT data, links FROM contentful_raw WHERE id = _id INTO prev;
INSERT INTO contentful_raw (id, data, links) values (_id, _data, _links)
  ON CONFLICT (id) DO
    UPDATE
    SET data = _data,
      links = _links;
RETURN prev;

END; $$ LANGUAGE 'plpgsql';

– Joins the entries table to itself by all the linked entries down to depth 5. – Each entry has a row for each downstream entry in it's tree. – Example: – | id | included_id | depth | – | page1 | page2 | 1 | – | page1 | subpage2 | 2 | – through page2 – | page1 | asset1 | 1 | – | page2 | subpage2 | 1 | – … CREATE MATERIALIZED VIEW IF NOT EXISTS contentful_raw_includes_ids_jointable AS

WITH RECURSIVE includes (root_id, depth) AS (
  SELECT t.id as root_id, 0, t.id, t.links FROM contentful_raw t
  UNION ALL
    SELECT l.root_id, l.depth + 1, r.id, r.links
    FROM includes l, contentful_raw r
    WHERE r.id = ANY(l.links) AND l.depth < 5
)
SELECT root_id as id, id as included_id, min(depth)
  FROM includes
  GROUP BY root_id, id;

CREATE INDEX IF NOT EXISTS contentful_raw_includes_ids_jointable_id ON contentful_raw_includes_ids_jointable (id); CREATE UNIQUE INDEX IF NOT EXISTS contentful_raw_includes_ids_jointable_id_included_id ON contentful_raw_includes_ids_jointable (id, included_id);

– Uses the contentful_raw_includes_ids_jointable to join the entries table to itself, – aggregating the included entries into an array. – Example: – | id | data | includes | – | page1 | jsonb | {page2 jsonb, subpage2 jsonb, asset1 jsonb} | CREATE OR REPLACE VIEW contentful_raw_includes AS

SELECT t.id, t.data, array_remove(array_agg(r_incl.data), NULL) as includes
  FROM contentful_raw t
  LEFT JOIN contentful_raw_includes_ids_jointable incl ON t.id = incl.id
  LEFT JOIN contentful_raw r_incl ON r_incl.id = incl.included_id
  GROUP BY t.id, t.data;

INSERT INTO wcc_contentful_schema_version

VALUES (1);

COMMIT;