devops/postgres/schema.sql

   1 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
   2 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
   3
   4 CREATE EXTENSION IF NOT EXISTS tsm_system_rows;
   5 CREATE EXTENSION pgcrypto;
   6
   7 -----------------------------------------------------------------
   8 CREATE TABLE public.auth_user (
   9     id SERIAL,
  10     password     CHARACTER varying(128) NOT NULL,
  11     last_login   TIMESTAMP with time zone,
  12     is_superuser BOOLEAN NOT NULL,
  13     username     CHARACTER varying(150) NOT NULL,
  14     first_name   CHARACTER varying(30) NOT NULL,
  15     last_name    CHARACTER varying(30) NOT NULL,
  16     email        CHARACTER varying(254) NOT NULL,
  17     is_staff     BOOLEAN NOT NULL,
  18     is_active    BOOLEAN NOT NULL,
  19     date_joined  TIMESTAMP with time zone DEFAULT now() NOT NULL,
  20     PRIMARY KEY (id)
  21 );
  22 ALTER TABLE public.auth_user OWNER TO gargantua;
  23 -----------------------------------------------------------------
  24
  25 -- TODO add publication_date
  26 -- TODO typename -> type_id
  27 CREATE TABLE public.nodes (
  28     id        SERIAL,
  29     hash_id   CHARACTER varying(66) DEFAULT ''::character varying NOT NULL,
  30     typename  INTEGER NOT NULL,
  31     user_id   INTEGER NOT NULL,
  32     parent_id INTEGER REFERENCES public.nodes(id) ON DELETE CASCADE ,
  33     name      CHARACTER varying(255) DEFAULT ''::character varying NOT NULL,
  34     date      TIMESTAMP with time zone DEFAULT now() NOT NULL,
  35     hyperdata jsonb DEFAULT '{}'::jsonb NOT NULL,
  36     search tsvector,
  37     PRIMARY KEY (id),
  38     FOREIGN KEY (user_id)  REFERENCES public.auth_user(id) ON DELETE CASCADE
  39 );
  40 ALTER TABLE public.nodes OWNER TO gargantua;
  41 --------------------------------------------------------------
  42
  43 -- TODO add publication_date
  44 -- TODO typename -> type_id
  45 CREATE TABLE public.contexts (
  46     id        SERIAL,
  47     hash_id   CHARACTER varying(66) DEFAULT ''::character varying NOT NULL,
  48     typename  INTEGER NOT NULL,
  49     user_id   INTEGER NOT NULL,
  50     parent_id INTEGER REFERENCES public.contexts(id) ON DELETE CASCADE ,
  51     name      CHARACTER varying(255) DEFAULT ''::character varying NOT NULL,
  52     date      TIMESTAMP with time zone DEFAULT now() NOT NULL,
  53     hyperdata jsonb DEFAULT '{}'::jsonb NOT NULL,
  54     search tsvector,
  55     PRIMARY KEY (id),
  56     FOREIGN KEY (user_id)  REFERENCES public.auth_user(id) ON DELETE CASCADE
  57 );
  58 ALTER TABLE public.contexts OWNER TO gargantua;
  59
  60 --------------------------------------------------------------
  61 -- | Ngrams
  62 CREATE TABLE public.ngrams (
  63     id SERIAL,
  64     terms CHARACTER varying(255),
  65     n INTEGER,
  66     PRIMARY KEY (id)
  67 );
  68 ALTER TABLE public.ngrams OWNER TO gargantua;
  69
  70 -- | Ngrams PosTag
  71 CREATE TABLE public.ngrams_postag (
  72     id        SERIAL                  ,
  73     lang_id   INTEGER                 ,
  74     algo_id   INTEGER                 ,
  75     postag    CHARACTER varying(5)    ,
  76     ngrams_id INTEGER NOT NULL        ,
  77     lemm_id   INTEGER NOT NULL        ,
  78     score     INTEGER DEFAULT 1 ::integer NOT NULL                        ,
  79     FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE,
  80     FOREIGN KEY (lemm_id)   REFERENCES public.ngrams(id) ON DELETE CASCADE
  81 );
  82 ALTER TABLE public.ngrams_postag OWNER TO gargantua;
  83
  84 --------------------------------------------------------------
  85 -- Node here should have type NodeList
  86 CREATE TABLE public.node_ngrams (
  87     id SERIAL                          ,
  88     node_id        INTEGER NOT NULL    ,
  89     node_subtype   INTEGER             ,
  90     ngrams_id      INTEGER NOT NULL    ,
  91     ngrams_type    INTEGER             , -- change to ngrams_field? (no for pedagogic reason)
  92     ngrams_field   INTEGER             ,
  93     ngrams_tag     INTEGER             ,
  94     ngrams_class   INTEGER             ,
  95     weight double precision            ,
  96     PRIMARY KEY (id)                   ,
  97     FOREIGN KEY (node_id)   REFERENCES public.nodes(id)  ON DELETE CASCADE ,
  98     FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE
  99 );
 100 ALTER TABLE public.node_ngrams OWNER TO gargantua;
 101
 102 --CREATE TABLE public.context_nodengrams_nodengrams (
 103 --    context_id         INTEGER NOT NULL   ,
 104 --    node_ngrams1_id INTEGER NOT NULL   ,
 105 --    node_ngrams2_id INTEGER NOT NULL   ,
 106 --    weight double   precision          ,
 107 --    FOREIGN KEY (node_id) REFERENCES public.contexts(id) ON DELETE CASCADE              ,
 108 --    FOREIGN KEY (node_ngrams1_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
 109 --    FOREIGN KEY (node_ngrams2_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
 110 --    PRIMARY KEY (node_id, node_ngrams1_id, node_ngrams2_id)
 111 --);
 112 --ALTER TABLE public.context_nodengrams_nodengrams OWNER TO gargantua;
 113
 114 --------------------------------------------------------------
 115 --------------------------------------------------------------
 116 --
 117 --CREATE TABLE public.nodes_ngrams_ngrams (
 118 --    node_id   integer NOT NULL REFERENCES public.nodes(id)  ON DELETE CASCADE,
 119 --    ngram1_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
 120 --    ngram2_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
 121 --    weight double precision,
 122 --    PRIMARY KEY (node_id,ngram1_id,ngram2_id)
 123 --);
 124 --
 125 --ALTER TABLE public.nodes_ngrams_ngrams OWNER TO gargantua;
 126 ---------------------------------------------------------------
 127 -- TODO nodes_nodes(node1_id int, node2_id int, edge_type int , weight real)
 128 CREATE TABLE public.nodes_nodes (
 129     node1_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
 130     node2_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
 131     score    REAL    ,
 132     category INTEGER ,
 133     PRIMARY KEY (node1_id, node2_id)
 134 );
 135 ALTER TABLE public.nodes_nodes OWNER TO gargantua;
 136
 137
 138
 139 -- To attach contexts to a Corpus
 140 CREATE TABLE public.nodes_contexts (
 141     id SERIAL                          ,
 142     node_id    INTEGER NOT NULL REFERENCES public.nodes(id)    ON DELETE CASCADE,
 143     context_id INTEGER NOT NULL REFERENCES public.contexts(id) ON DELETE CASCADE,
 144     score    REAL    ,
 145     category INTEGER ,
 146     PRIMARY KEY (id)
 147 );
 148 ALTER TABLE public.nodes_contexts OWNER TO gargantua;
 149
 150 CREATE TABLE public.nodescontexts_nodescontexts (
 151     nodescontexts1 INTEGER NOT NULL REFERENCES public.nodes_contexts(id) ON DELETE CASCADE,
 152     nodescontexts2 INTEGER NOT NULL REFERENCES public.nodes_contexts(id) ON DELETE CASCADE,
 153
 154     PRIMARY KEY (nodescontexts1, nodescontexts2)
 155 );
 156 ALTER TABLE public.nodescontexts_nodescontexts OWNER TO gargantua;
 157
 158
 159 ---------------------------------------------------------------
 160 CREATE TABLE public.context_node_ngrams (
 161     context_id    INTEGER NOT NULL REFERENCES public.contexts (id) ON DELETE CASCADE,
 162     node_id       INTEGER NOT NULL REFERENCES public.nodes    (id) ON DELETE CASCADE,
 163     ngrams_id     INTEGER NOT NULL REFERENCES public.ngrams   (id) ON DELETE CASCADE,
 164     ngrams_type   INTEGER  ,
 165     weight double precision,
 166     PRIMARY KEY (context_id, node_id, ngrams_id, ngrams_type)
 167   );
 168 ALTER TABLE public.context_node_ngrams OWNER TO gargantua;
 169
 170 CREATE TABLE public.context_node_ngrams2 (
 171     context_id      INTEGER NOT NULL REFERENCES public.contexts     (id) ON DELETE CASCADE,
 172     nodengrams_id   INTEGER NOT NULL REFERENCES public.node_ngrams  (id) ON DELETE CASCADE,
 173     weight double   precision,
 174     PRIMARY KEY (context_id, nodengrams_id)
 175 );
 176 ALTER TABLE public.context_node_ngrams2 OWNER TO gargantua;
 177
 178
 179 --------------------------------------------------------------------
 180 CREATE TABLE public.node_node_ngrams (
 181 node1_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 182 node2_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 183 ngrams_id  INTEGER NOT NULL REFERENCES public.ngrams (id) ON DELETE CASCADE,
 184 ngrams_type INTEGER,
 185 weight double precision,
 186 PRIMARY KEY (node1_id, node2_id, ngrams_id, ngrams_type)
 187 );
 188 ALTER TABLE public.node_node_ngrams OWNER TO gargantua;
 189
 190 CREATE TABLE public.node_node_ngrams2 (
 191 node_id         INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 192 nodengrams_id   INTEGER NOT NULL REFERENCES public.node_ngrams  (id) ON DELETE CASCADE,
 193 weight double precision,
 194 PRIMARY KEY (node_id, nodengrams_id)
 195 );
 196 ALTER TABLE public.node_node_ngrams2 OWNER TO gargantua;
 197
 198
 199 --------------------------------------------------------------
 200
 201 --CREATE TABLE public.nodes_ngrams_repo (
 202 --    version integer NOT NULL,
 203 --    patches jsonb DEFAULT '{}'::jsonb NOT NULL,
 204 --    PRIMARY KEY (version)
 205 --);
 206 --ALTER TABLE public.nodes_ngrams_repo OWNER TO gargantua;
 207
 208 ---------------------------------------------------------
 209
 210 -- If needed for rights management at row level
 211 -- CREATE EXTENSION IF NOT EXISTS acl WITH SCHEMA public;
 212 CREATE TABLE public.rights (
 213   user_id INTEGER NOT NULL REFERENCES public.auth_user(id) ON DELETE CASCADE,
 214   node_id INTEGER NOT NULL REFERENCES public.nodes(id)     ON DELETE CASCADE,
 215   rights  INTEGER NOT NULL,
 216   PRIMARY KEY (user_id, node_id)
 217 );
 218 ALTER TABLE public.rights OWNER TO gargantua;
 219
 220 ------------------------------------------------------------
 221 ------------------------------------------------------------
 222 -- INDEXES
 223 CREATE INDEX        ON public.auth_user USING btree (username varchar_pattern_ops);
 224 CREATE UNIQUE INDEX ON public.auth_user USING btree (username);
 225
 226 CREATE INDEX        ON public.rights USING btree (user_id,node_id);
 227
 228 CREATE INDEX        ON public.nodes USING gin (hyperdata);
 229 CREATE INDEX        ON public.nodes USING btree (user_id, typename, parent_id);
 230 CREATE INDEX        ON public.nodes USING btree (id, typename, date ASC);
 231 CREATE INDEX        ON public.nodes USING btree (id, typename, date DESC);
 232 CREATE INDEX        ON public.nodes USING btree (typename, id);
 233 CREATE UNIQUE INDEX ON public.nodes USING btree (hash_id);
 234
 235 CREATE INDEX        ON public.contexts USING gin (hyperdata);
 236 CREATE INDEX        ON public.contexts USING btree (user_id, typename, parent_id);
 237 CREATE INDEX        ON public.contexts USING btree (id, typename, date ASC);
 238 CREATE INDEX        ON public.contexts USING btree (id, typename, date DESC);
 239 CREATE INDEX        ON public.contexts USING btree (typename, id);
 240 CREATE UNIQUE INDEX ON public.contexts USING btree (hash_id);
 241
 242 CREATE INDEX ON public.nodescontexts_nodescontexts USING btree (nodescontexts1, nodescontexts2);
 243 -- CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqId'::text)));
 244 -- CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqIdBdd'::text)));
 245 -- CREATE UNIQUE INDEX ON public.nodes USING btree (typename, parent_id, ((hyperdata ->> 'uniqId'::text)));
 246
 247 CREATE UNIQUE INDEX ON public.ngrams (terms); -- TEST GIN
 248 CREATE        INDEX ON public.ngrams USING btree (id, terms);
 249 CREATE UNIQUE INDEX ON public.ngrams_postag (lang_id,algo_id,postag,ngrams_id,lemm_id);
 250
 251 -- To save the Node Ngrams Repo
 252 CREATE        INDEX ON public.node_ngrams USING btree (node_id,node_subtype);
 253 CREATE UNIQUE INDEX ON public.node_ngrams USING btree (node_id,node_subtype, ngrams_id);
 254
 255
 256
 257 -- To make the links between Nodes in Tree/Forest
 258 CREATE UNIQUE INDEX ON public.nodes_nodes  USING btree (node1_id, node2_id);
 259 CREATE INDEX        ON public.nodes_nodes  USING btree (node1_id, node2_id, category);
 260
 261
 262 -- To make the links between Corpus Node and its contexts
 263 CREATE UNIQUE INDEX ON public.nodes_contexts  USING btree (node_id, context_id);
 264 CREATE INDEX        ON public.nodes_contexts  USING btree (node_id, context_id, category);
 265
 266
 267 ------------------------------------------------------------------------
 268 CREATE UNIQUE INDEX ON public.context_node_ngrams USING btree (context_id, node_id, ngrams_id, ngrams_type);
 269 CREATE        INDEX ON public.context_node_ngrams USING btree (context_id,  node_id);
 270 CREATE        INDEX ON public.context_node_ngrams USING btree (ngrams_id, node_id);
 271 CREATE        INDEX ON public.context_node_ngrams USING btree (ngrams_type);
 272
 273 CREATE INDEX ON public.context_node_ngrams2 USING btree (context_id);
 274 CREATE INDEX ON public.context_node_ngrams2 USING btree (nodengrams_id);
 275 CREATE INDEX ON public.context_node_ngrams2 USING btree (context_id, nodengrams_id);
 276
 277
 278 CREATE UNIQUE INDEX ON public.node_node_ngrams USING btree (node1_id, node2_id, ngrams_id, ngrams_type);
 279 CREATE        INDEX ON public.node_node_ngrams USING btree (node1_id,  node2_id);
 280 CREATE        INDEX ON public.node_node_ngrams USING btree (ngrams_id, node2_id);
 281 CREATE        INDEX ON public.node_node_ngrams USING btree (ngrams_type);
 282 CREATE INDEX ON public.node_node_ngrams2 USING btree (node_id);
 283 CREATE INDEX ON public.node_node_ngrams2 USING btree (nodengrams_id);
 284 CREATE INDEX ON public.node_node_ngrams2 USING btree (node_id, nodengrams_id);
 285
 286 -- CREATE INDEX ON public.context_nodengrams_nodengrams USING btree (context_id, node_ngrams1_id, node_ngrams2_id);
 287 -- CREATE INDEX ON public.context_nodengrams_nodengrams USING btree (node_ngrams1_id);
 288 -- CREATE INDEX ON public.context_nodengrams_nodengrams USING btree (node_ngrams2_id);
 289
 290
 291 ------------------------------------------------------------------------
 292 -- Ngrams Full DB Extraction Optim
 293 -- TODO remove hard parameter and move elsewhere
 294 CREATE OR REPLACE function node_pos(int, int) returns bigint
 295    AS 'SELECT count(id) from nodes
 296       WHERE  id < $1
 297       AND typename = $2
 298       '
 299    LANGUAGE SQL immutable;
 300
 301 --drop index node_by_pos;
 302 --create index node_by_pos on nodes using btree(node_pos(id,typename));
 303