devops/postgres/upgrade/0.0.5/schema.sql

   1
   2 -- TODO typename -> type_id
   3 CREATE TABLE public.contexts (
   4     id        SERIAL,
   5     hash_id   CHARACTER varying(66) DEFAULT ''::character varying NOT NULL,
   6     typename  INTEGER NOT NULL,
   7     user_id   INTEGER NOT NULL,
   8     parent_id INTEGER REFERENCES public.contexts(id) ON DELETE CASCADE ,
   9     name      CHARACTER varying(255) DEFAULT ''::character varying NOT NULL,
  10     date      TIMESTAMP with time zone DEFAULT now() NOT NULL,
  11     hyperdata jsonb DEFAULT '{}'::jsonb NOT NULL,
  12     search tsvector,
  13     PRIMARY KEY (id),
  14     FOREIGN KEY (user_id)  REFERENCES public.auth_user(id) ON DELETE CASCADE
  15 );
  16 ALTER TABLE public.contexts OWNER TO gargantua;
  17
  18
  19
  20 -- To attach contexts to a Corpus
  21 CREATE TABLE public.nodes_contexts (
  22     node_id    INTEGER NOT NULL REFERENCES public.nodes(id)    ON DELETE CASCADE,
  23     context_id INTEGER NOT NULL REFERENCES public.contexts(id) ON DELETE CASCADE,
  24     score    REAL    ,
  25     category INTEGER ,
  26     PRIMARY KEY (node_id, context_id)
  27 );
  28 ALTER TABLE public.nodes_contexts OWNER TO gargantua;
  29
  30  ---------------------------------------------------------------
  31 CREATE TABLE public.context_node_ngrams (
  32     context_id    INTEGER NOT NULL REFERENCES public.contexts (id) ON DELETE CASCADE,
  33     node_id       INTEGER NOT NULL REFERENCES public.nodes    (id) ON DELETE CASCADE,
  34     ngrams_id     INTEGER NOT NULL REFERENCES public.ngrams   (id) ON DELETE CASCADE,
  35     ngrams_type   INTEGER  ,
  36     weight double precision,
  37     PRIMARY KEY (context_id, node_id, ngrams_id, ngrams_type)
  38   );
  39
  40
  41 ALTER TABLE public.context_node_ngrams OWNER TO gargantua;
  42
  43 CREATE TABLE public.context_node_ngrams2 (
  44     context_id      INTEGER NOT NULL REFERENCES public.contexts  (id)       ON DELETE CASCADE,
  45     nodengrams_id   INTEGER NOT NULL REFERENCES public.node_ngrams  (id) ON DELETE CASCADE,
  46     weight double   precision,
  47     PRIMARY KEY (context_id, nodengrams_id)
  48 );
  49 ALTER TABLE public.context_node_ngrams2 OWNER TO gargantua;
  50
  51
  52
  53 CREATE INDEX        ON public.contexts USING gin (hyperdata);
  54 CREATE INDEX        ON public.contexts USING btree (user_id, typename, parent_id);
  55 CREATE INDEX        ON public.contexts USING btree (id, typename, date ASC);
  56 CREATE INDEX        ON public.contexts USING btree (id, typename, date DESC);
  57 CREATE INDEX        ON public.contexts USING btree (typename, id);
  58 CREATE UNIQUE INDEX ON public.contexts USING btree (hash_id);
  59
  60
  61 -- To make the links between Corpus Node and its contexts
  62 CREATE UNIQUE INDEX ON public.nodes_contexts  USING btree (node_id, context_id);
  63 CREATE INDEX        ON public.nodes_contexts  USING btree (node_id, context_id, category);
  64
  65
  66 ------------------------------------------------------------------------
  67 CREATE UNIQUE INDEX ON public.context_node_ngrams USING btree (context_id, node_id, ngrams_id, ngrams_type);
  68 CREATE        INDEX ON public.context_node_ngrams USING btree (context_id,  node_id);
  69 CREATE        INDEX ON public.context_node_ngrams USING btree (ngrams_id, node_id);
  70 CREATE        INDEX ON public.context_node_ngrams USING btree (ngrams_type);
  71
  72 CREATE INDEX ON public.context_node_ngrams2 USING btree (context_id);
  73 CREATE INDEX ON public.context_node_ngrams2 USING btree (nodengrams_id);
  74 CREATE INDEX ON public.context_node_ngrams2 USING btree (context_id, nodengrams_id);
  75
  76 DROP TABLE if EXISTS public.node_nodengrams_nodengrams;
  77
  78 DROP TRIGGER if EXISTS trigger_count_delete2    ON nodes_nodes;
  79 DROP TRIGGER if EXISTS trigger_count_update_add ON nodes_nodes;
  80 DROP TRIGGER if EXISTS trigger_delete_count     ON nodes_nodes;
  81 DROP TRIGGER if EXISTS trigger_insert_count     ON nodes_nodes;
  82