devops/postgres/upgrade/0.0.6.0.sql

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