devops/postgres/upgrade/0.0.5.9.1.sql

   1 create table public.node_story_archive_history (
   2   id SERIAL,
   3   node_id INTEGER NOT NULL,
   4   ngrams_type_id INTEGER NOT NULL,
   5   ngrams_id INTEGER NOT NULL,
   6   patch jsonb DEFAULT '{}'::jsonb NOT NULL,
   7   PRIMARY KEY (id),
   8   FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
   9   FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE
  10 );
  11 ALTER TABLE public.node_story_archive_history OWNER TO gargantua;
  12
  13
  14 -- INSERT INTO node_story_archive_history (node_id, ngrams_type_id, patch) SELECT t.node_id, t.ngrams_type_id, t.patch FROM
  15 -- (
  16 -- WITH q AS (SELECT node_id, history.*, row_number() over (ORDER BY node_id) AS sid
  17 -- FROM node_stories,
  18 -- jsonb_to_recordset(archive->'history') AS history("Authors" jsonb, "Institutes" jsonb, "NgramsTerms" jsonb, "Sources" jsonb))
  19
  20 -- (SELECT node_id, sid, 1 AS ngrams_type_id, "Authors" AS patch FROM q WHERE "Authors" IS NOT NULL)
  21 -- UNION (SELECT node_id, sid, 2 AS ngrams_type_id, "Institutes" AS patch FROM q WHERE "Institutes" IS NOT NULL)
  22 -- UNION (SELECT node_id, sid, 4 AS ngrams_type_id, "NgramsTerms" AS patch FROM q WHERE "NgramsTerms" IS NOT NULL)
  23 -- UNION (SELECT node_id, sid, 3 AS ngrams_type_id, "Sources" AS patch FROM q WHERE "Sources" IS NOT NULL)
  24 -- ORDER BY node_id, ngrams_type_id, sid
  25 -- ) AS t;