devops/postgres/upgrade/0.0.6.1.sql

   1 -- we will migrate data here
   2 -- rename old table and create a new one
   3
   4 ALTER TABLE public.node_stories RENAME TO node_stories_old;
   5
   6 CREATE TABLE public.node_stories (
   7   id SERIAL,
   8   node_id INTEGER NOT NULL,
   9   version INTEGER NOT NULL,
  10   ngrams_type_id INTEGER NOT NULL,
  11   ngrams_id INTEGER NOT NULL,
  12   --children TEXT[],
  13   ngrams_repo_element jsonb DEFAULT '{}'::jsonb NOT NULL,
  14   PRIMARY KEY (id),
  15   FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
  16   FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE
  17 );
  18 ALTER TABLE public.node_stories OWNER TO gargantua;
  19
  20 CREATE UNIQUE INDEX ON public.node_stories USING btree (node_id, ngrams_type_id, ngrams_id);
  21
  22 -- Authors (ngrams_type_id = 1), see G.D.S.Ngrams.hs -> ngramsTypeId
  23 INSERT INTO public.node_stories
  24   (node_id, version, ngrams_type_id, ngrams_id, ngrams_repo_element)
  25   SELECT node_id, (archive->'version')::int, 1, ngrams.id, j.value
  26     FROM node_stories_old
  27     CROSS JOIN jsonb_each(archive->'state'->'Authors') AS j
  28     JOIN ngrams ON terms = j.key;
  29 -- we will leave children for later, small steps
  30 -- INSERT INTO public.node_stories
  31 --   (node_id, version, ngrams_type_id, ngrams_id, children, ngrams_repo_element)
  32 --   SELECT node_id, (archive->'version')::int, 1, ngrams.id, c.children, (j.value - 'children')
  33 --     FROM node_stories_old
  34 --     CROSS JOIN jsonb_each(archive->'state'->'Authors') AS j
  35 --     CROSS JOIN LATERAL (SELECT array_agg(d.elem) AS children FROM jsonb_array_elements_text(j.value->'children') AS d(elem)) AS c
  36 --     JOIN ngrams ON terms = j.key;
  37
  38 -- Institutes (ngrams_type_id = 2)
  39 INSERT INTO public.node_stories
  40   (node_id, version, ngrams_type_id, ngrams_id, ngrams_repo_element)
  41   SELECT node_id, (archive->'version')::int, 2, ngrams.id, j.value
  42     FROM node_stories_old
  43     CROSS JOIN jsonb_each(archive->'state'->'Institutes') AS j
  44   JOIN ngrams ON terms = j.key;
  45 -- Sources (ngrams_type_id = 3)
  46 INSERT INTO public.node_stories
  47   (node_id, version, ngrams_type_id, ngrams_id, ngrams_repo_element)
  48   SELECT node_id, (archive->'version')::int, 3, ngrams.id, j.value
  49     FROM node_stories_old
  50     CROSS JOIN jsonb_each(archive->'state'->'Sources') AS j
  51     JOIN ngrams ON terms = j.key;
  52 -- NgramsTerms (ngrams_type_id = 4)
  53 INSERT INTO public.node_stories
  54   (node_id, version, ngrams_type_id, ngrams_id, ngrams_repo_element)
  55   SELECT node_id, (archive->'version')::int, 4, ngrams.id, j.value
  56     FROM node_stories_old
  57     CROSS JOIN jsonb_each(archive->'state'->'NgramsTerms') AS j
  58     JOIN ngrams ON terms = j.key;