devops/postgres/upgrade/0.0.6.1.sql

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