bin/gargantext-upgrade/Main.hs

   1 {-|
   2 Module      : Main.hs
   3 Description : Gargantext Import Corpus
   4 Copyright   : (c) CNRS, 2017-Present
   5 License     : AGPL + CECILL v3
   6 Maintainer  : team@gargantext.org
   7 Stability   : experimental
   8 Portability : POSIX
   9
  10 Import a corpus binary.
  11
  12  -}
  13
  14 {-# LANGUAGE Strict            #-}
  15 {-# LANGUAGE QuasiQuotes       #-}
  16
  17 module Main where
  18
  19
  20 import Data.Either (Either(..))
  21 import Database.PostgreSQL.Simple.SqlQQ (sql)
  22 import GHC.IO.Exception (IOException)
  23 import Gargantext.API.Admin.EnvTypes (DevEnv)
  24 import Gargantext.API.Dev (withDevEnv, runCmdDev)
  25 import Gargantext.API.Node () -- instances only
  26 import Gargantext.API.Prelude (GargError)
  27 import Gargantext.Core (HasDBid(toDBid))
  28 import Gargantext.Core.Types.Individu (User(..))
  29 import Gargantext.Database.Action.Flow (getOrMk_RootWithCorpus)
  30 import Gargantext.Database.Admin.Config (userMaster, corpusMasterName)
  31 import Gargantext.Database.Admin.Trigger.Init
  32 import Gargantext.Database.Admin.Types.Hyperdata (HyperdataCorpus)
  33 import Gargantext.Database.Admin.Types.Node (NodeType(NodeDocument, NodeContact))
  34 import Gargantext.Database.Prelude (Cmd'', Cmd, execPGSQuery)
  35 import Gargantext.Database.Query.Table.Node (getOrMkList)
  36 import Gargantext.Prelude
  37 import Gargantext.Prelude.Config (GargConfig(..), readConfig)
  38 import Prelude (getLine)
  39 import System.Environment (getArgs)
  40 import qualified Data.List as List (cycle, concat, take, unlines)
  41
  42 main :: IO ()
  43 main = do
  44
  45   let ___ = putStrLn $ List.concat $ List.take 72 $ List.cycle ["_"]
  46
  47   ___
  48   putStrLn "GarganText upgrade to version 0.0.5"
  49   ___
  50
  51   params@[iniPath] <- getArgs
  52   _ <- if length params /= 1
  53          then panic "Usage: ./gargantext-upgrade gargantext.ini"
  54          else pure ()
  55
  56   putStrLn $ List.unlines
  57            [ "Your Database defined in gargantext.ini will be upgraded."
  58            , "We stronlgy recommend you to make a backup using pg_dump."
  59            , ""
  60            , "If you encounter issues, please report your bugs here:"
  61            , "https://gitlab.iscpif.fr/gargantext/haskell-gargantext/issues/101"
  62            , ""
  63            , "Press ENTER if you want to continue, CTRL+C if you want to stop."
  64            ]
  65
  66   _ok  <- getLine
  67
  68   cfg       <- readConfig         iniPath
  69   let secret = _gc_secretkey cfg
  70
  71   let
  72     contextsTriggers :: Cmd GargError ()
  73     contextsTriggers = do
  74       (masterUserId, _masterRootId, masterCorpusId)
  75                   <- getOrMk_RootWithCorpus (UserName userMaster)
  76                                             (Left corpusMasterName)
  77                                             (Nothing :: Maybe HyperdataCorpus)
  78       masterListId <- getOrMkList masterCorpusId masterUserId
  79       _triggers    <- initLastTriggers masterListId
  80       pure ()
  81
  82
  83   withDevEnv iniPath $ \env -> do
  84     -- First upgrade the Database Schema
  85     _ <- runCmdDev env sqlSchema
  86
  87     -- Then upgrade the triggers
  88     _ <- runCmdDev env (initFirstTriggers secret :: Cmd GargError [Int64])
  89     _ <- runCmdDev env (contextsTriggers         :: Cmd GargError ())
  90
  91    -- Move nodes to contexts table
  92     _ <- runCmdDev env sqlNodes2Contexts
  93
  94    -- Update the hashes
  95     _ <- runCmdDev env sqlUpdateTriggerHash
  96
  97     ___
  98     putStrLn "Uprade done with success !"
  99     ___
 100     pure ()
 101
 102
 103
 104 sqlUpdateTriggerHash :: Cmd'' DevEnv IOException Int64
 105 sqlUpdateTriggerHash = do
 106   execPGSQuery query ()
 107     where
 108       query = [sql|
 109       UPDATE nodes    SET typename = typename;
 110       UPDATE contexts SET typename = typename;
 111      |]
 112
 113
 114 sqlNodes2Contexts :: Cmd'' DevEnv IOException Int64
 115 sqlNodes2Contexts = do
 116   execPGSQuery query (toDBid NodeDocument,toDBid NodeContact)
 117     where
 118       query = [sql|
 119         -- WITH docs (id,hash_id,typename,user_id,parent_id,name,date,hyperdata, search)
 120         WITH docs AS (SELECT * from nodes  WHERE nodes.typename IN (?,?)),
 121
 122         inserted (id, hash_id) AS (
 123           INSERT INTO contexts (hash_id,typename,user_id,parent_id,name,date,hyperdata, search)
 124             SELECT d.hash_id,d.typename,d.user_id,NULL,d.name,d.date,d.hyperdata,search FROM docs AS d
 125             RETURNING contexts.id, contexts.hash_id
 126          ),
 127
 128         indexed (node_id, context_id) AS (
 129           SELECT docs.id, inserted.id from inserted
 130           JOIN docs on docs.hash_id = inserted.hash_id
 131         ),
 132
 133         -- nodes_nodes -> nodes_contexts
 134         nodes_contexts_query AS (
 135         INSERT INTO nodes_contexts (node_id, context_id,score, category)
 136         SELECT nn.node1_id,i.context_id,nn.score,nn.category FROM nodes_nodes nn
 137         JOIN indexed i ON i.node_id = nn.node2_id
 138         ),
 139
 140         -- nodes_nodes_ngrams -> contexts_nodes_ngrams
 141         contexts_nodes_ngrams_query AS (
 142         INSERT INTO context_node_ngrams
 143         SELECT i.context_id, nnn.node1_id, nnn.ngrams_id, nnn.ngrams_type, nnn.weight FROM node_node_ngrams nnn
 144         JOIN indexed i ON i.node_id = nnn.node2_id
 145         ),
 146
 147         ---- nodes_nodes_ngrams2 -> contexts_nodes_ngrams2
 148         context_node_ngrams2_query AS (
 149         INSERT INTO context_node_ngrams2
 150         SELECT i.context_id, nnn2.nodengrams_id, nnn2.weight FROM node_node_ngrams2 nnn2
 151         JOIN indexed i ON i.node_id = nnn2.node_id
 152         )
 153
 154         -- WITH CASCADE it should update others tables
 155         DELETE FROM nodes n
 156         USING indexed i WHERE i.node_id = n.id
 157         ;
 158
 159         UPDATE contexts SET parent_id = id;
 160
 161
 162   |]
 163
 164
 165
 166
 167
 168 sqlSchema :: Cmd'' DevEnv IOException Int64
 169 sqlSchema = do
 170   execPGSQuery query ()
 171     where
 172       query = [sql|
 173         -- TODO typename -> type_id
 174         CREATE TABLE public.contexts (
 175             id        SERIAL,
 176             hash_id   CHARACTER varying(66) DEFAULT ''::character varying NOT NULL,
 177             typename  INTEGER NOT NULL,
 178             user_id   INTEGER NOT NULL,
 179             parent_id INTEGER REFERENCES public.contexts(id) ON DELETE CASCADE ,
 180             name      CHARACTER varying(255) DEFAULT ''::character varying NOT NULL,
 181             date      TIMESTAMP with time zone DEFAULT now() NOT NULL,
 182             hyperdata jsonb DEFAULT '{}'::jsonb NOT NULL,
 183             search tsvector,
 184             PRIMARY KEY (id),
 185             FOREIGN KEY (user_id)  REFERENCES public.auth_user(id) ON DELETE CASCADE
 186         );
 187         ALTER TABLE public.contexts OWNER TO gargantua;
 188
 189
 190
 191         -- To attach contexts to a Corpus
 192         CREATE TABLE public.nodes_contexts (
 193             node_id    INTEGER NOT NULL REFERENCES public.nodes(id)    ON DELETE CASCADE,
 194             context_id INTEGER NOT NULL REFERENCES public.contexts(id) ON DELETE CASCADE,
 195             score    REAL    ,
 196             category INTEGER ,
 197             PRIMARY KEY (node_id, context_id)
 198         );
 199         ALTER TABLE public.nodes_contexts OWNER TO gargantua;
 200
 201          ---------------------------------------------------------------
 202         CREATE TABLE public.context_node_ngrams (
 203             context_id    INTEGER NOT NULL REFERENCES public.contexts (id) ON DELETE CASCADE,
 204             node_id       INTEGER NOT NULL REFERENCES public.nodes    (id) ON DELETE CASCADE,
 205             ngrams_id     INTEGER NOT NULL REFERENCES public.ngrams   (id) ON DELETE CASCADE,
 206             ngrams_type   INTEGER  ,
 207             weight double precision,
 208             PRIMARY KEY (context_id, node_id, ngrams_id, ngrams_type)
 209           );
 210
 211
 212         ALTER TABLE public.context_node_ngrams OWNER TO gargantua;
 213
 214         CREATE TABLE public.context_node_ngrams2 (
 215             context_id      INTEGER NOT NULL REFERENCES public.contexts  (id)       ON DELETE CASCADE,
 216             nodengrams_id   INTEGER NOT NULL REFERENCES public.node_ngrams  (id) ON DELETE CASCADE,
 217             weight double   precision,
 218             PRIMARY KEY (context_id, nodengrams_id)
 219         );
 220         ALTER TABLE public.context_node_ngrams2 OWNER TO gargantua;
 221
 222
 223
 224         CREATE INDEX        ON public.contexts USING gin (hyperdata);
 225         CREATE INDEX        ON public.contexts USING btree (user_id, typename, parent_id);
 226         CREATE INDEX        ON public.contexts USING btree (id, typename, date ASC);
 227         CREATE INDEX        ON public.contexts USING btree (id, typename, date DESC);
 228         CREATE INDEX        ON public.contexts USING btree (typename, id);
 229         CREATE UNIQUE INDEX ON public.contexts USING btree (hash_id);
 230
 231
 232         -- To make the links between Corpus Node and its contexts
 233         CREATE UNIQUE INDEX ON public.nodes_contexts  USING btree (node_id, context_id);
 234         CREATE INDEX        ON public.nodes_contexts  USING btree (node_id, context_id, category);
 235
 236
 237         ------------------------------------------------------------------------
 238         CREATE UNIQUE INDEX ON public.context_node_ngrams USING btree (context_id, node_id, ngrams_id, ngrams_type);
 239         CREATE        INDEX ON public.context_node_ngrams USING btree (context_id,  node_id);
 240         CREATE        INDEX ON public.context_node_ngrams USING btree (ngrams_id, node_id);
 241         CREATE        INDEX ON public.context_node_ngrams USING btree (ngrams_type);
 242
 243         CREATE INDEX ON public.context_node_ngrams2 USING btree (context_id);
 244         CREATE INDEX ON public.context_node_ngrams2 USING btree (nodengrams_id);
 245         CREATE INDEX ON public.context_node_ngrams2 USING btree (context_id, nodengrams_id);
 246
 247         DROP TABLE if EXISTS public.node_nodengrams_nodengrams;
 248
 249         DROP TRIGGER if EXISTS trigger_count_delete2    ON nodes_nodes;
 250         DROP TRIGGER if EXISTS trigger_count_update_add ON nodes_nodes;
 251         DROP TRIGGER if EXISTS trigger_delete_count     ON nodes_nodes;
 252         DROP TRIGGER if EXISTS trigger_insert_count     ON nodes_nodes;
 253
 254
 255         -- Indexes needed to speed up the deletes
 256         -- Trigger for constraint node_ngrams_node_id_fkey
 257         CREATE INDEX IF NOT EXISTS node_ngrams_node_id_idx       ON public.node_ngrams USING btree (node_id);
 258
 259         -- Trigger for constraint node_node_ngrams2_node_id_fkey
 260         CREATE INDEX IF NOT EXISTS node_node_ngrams2_node_id_idx ON public.node_node_ngrams2 USING btree (node_id);
 261
 262         -- Trigger for constraint node_node_ngrams_node1_id_fkey
 263         CREATE INDEX IF NOT EXISTS node_node_ngrams_node1_id_idx  ON public.node_node_ngrams USING btree (node1_id);
 264
 265         -- Trigger for constraint node_node_ngrams_node2_id_fkey
 266         CREATE INDEX IF NOT EXISTS node_node_ngrams_node2_id_idx  ON public.node_node_ngrams USING btree (node2_id);
 267
 268         -- Trigger for constraint nodes_nodes_node1_id_fkey
 269         CREATE INDEX IF NOT EXISTS nodes_nodes_node1_id_idx ON public.nodes_nodes USING btree (node1_id);
 270         -- Trigger for constraint nodes_nodes_node2_id_fkey
 271         CREATE INDEX IF NOT EXISTS nodes_nodes_node2_id_idx ON public.nodes_nodes USING btree (node2_id);
 272
 273         -- Trigger for constraint nodes_parent_id_fkey
 274         CREATE INDEX IF NOT EXISTS nodes_parent_id_idx ON public.nodes USING btree (parent_id);
 275
 276         -- Trigger for constraint rights_node_id_fkey
 277         CREATE INDEX IF NOT EXISTS rights_node_id_idx ON public.rights USING btree (node_id);
 278
 279         -- Trigger for constraint nodes_contexts_node_id_fkey
 280         CREATE INDEX IF NOT EXISTS nodes_contexts_node_id_idx ON public.nodes_contexts USING btree (node_id);
 281
 282         -- Trigger for constraint context_node_ngrams_node_id_fkey
 283         CREATE INDEX IF NOT EXISTS context_node_node_id_idx ON public.context_node_ngrams USING btree (node_id);
 284
 285   |]
 286
 287
 288