devops/postgres/upgrade/0.0.5/nodes2contexts.sql

   1 -- to delete
   2 -- DELETE FROM contexts;
   3
   4 -- WITH docs (id,hash_id,typename,user_id,parent_id,name,date,hyperdata, search)
   5 WITH docs AS (SELECT * from nodes  WHERE nodes.typename IN (4,41)),
   6
   7 inserted (id, hash_id) AS (
   8   INSERT INTO contexts (hash_id,typename,user_id,parent_id,name,date,hyperdata, search)
   9     SELECT d.hash_id,d.typename,d.user_id,NULL,d.name,d.date,d.hyperdata,search FROM docs AS d
  10     RETURNING contexts.id, contexts.hash_id
  11  ),
  12
  13 indexed (node_id, context_id) AS (
  14   SELECT docs.id, inserted.id from inserted
  15   JOIN docs on docs.hash_id = inserted.hash_id
  16 ),
  17
  18 -- nodes_nodes -> nodes_contexts
  19 nodes_contexts_query AS (
  20 INSERT INTO nodes_contexts (node_id, context_id,score, category)
  21 SELECT nn.node1_id,i.context_id,nn.score,nn.category FROM nodes_nodes nn
  22 JOIN indexed i ON i.node_id = nn.node2_id
  23 ),
  24
  25 -- nodes_nodes_ngrams -> contexts_nodes_ngrams
  26 contexts_nodes_ngrams_query AS (
  27 INSERT INTO context_node_ngrams
  28 SELECT i.context_id, nnn.node1_id, nnn.ngrams_id, nnn.ngrams_type, nnn.weight FROM node_node_ngrams nnn
  29 JOIN indexed i ON i.node_id = nnn.node2_id
  30 ),
  31
  32 ---- nodes_nodes_ngrams2 -> contexts_nodes_ngrams2
  33 context_node_ngrams2_query AS (
  34 INSERT INTO context_node_ngrams2
  35 SELECT i.context_id, nnn2.nodengrams_id, nnn2.weight FROM node_node_ngrams2 nnn2
  36 JOIN indexed i ON i.node_id = nnn2.node_id
  37 )
  38
  39 -- WITH CASCADE it should update others tables
  40 DELETE FROM nodes n
  41 USING indexed i WHERE i.node_id = n.id
  42 ;
  43
  44 UPDATE contexts SET parent_id = id;
  45
  46