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
  23 ALTER TABLE public.auth_user OWNER TO gargantua;
  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 ALTER TABLE nodes ADD COLUMN IF NOT EXISTS search_title tsvector;
  42 UPDATE nodes SET search_title = to_tsvector('english', coalesce("hyperdata"->>'title', '') || ' ' || coalesce("hyperdata"->>'abstract', ''));
  43 CREATE INDEX IF NOT EXISTS search_title_idx ON nodes USING GIN (search_title);
  44
  45 --------------------------------------------------------------
  46 -- | Ngrams
  47 CREATE TABLE public.ngrams (
  48     id SERIAL,
  49     terms CHARACTER varying(255),
  50     n INTEGER,
  51     PRIMARY KEY (id)
  52 );
  53 ALTER TABLE public.ngrams OWNER TO gargantua;
  54
  55 -- | Ngrams PosTag
  56 CREATE TABLE public.ngrams_postag (
  57     id SERIAL,
  58     lang_id INTEGER,
  59     algo_id INTEGER,
  60     postag CHARACTER varying(5),
  61     ngrams_id INTEGER NOT NULL,
  62     lemm_id   INTEGER NOT NULL,
  63     score     INTEGER DEFAULT 1 ::integer NOT NULL,
  64     FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE,
  65     FOREIGN KEY (lemm_id)   REFERENCES public.ngrams(id) ON DELETE CASCADE
  66 );
  67 ALTER TABLE public.ngrams_postag OWNER TO gargantua;
  68
  69 --------------------------------------------------------------
  70 CREATE TABLE public.node_ngrams (
  71     id SERIAL,
  72     node_id INTEGER NOT NULL,
  73     node_subtype INTEGER,
  74     ngrams_id INTEGER NOT NULL,
  75     ngrams_type INTEGER, -- change to ngrams_field? (no for pedagogic reason)
  76     ngrams_field INTEGER,
  77     ngrams_tag INTEGER,
  78     ngrams_class INTEGER,
  79     weight double precision,
  80     PRIMARY KEY (id),
  81     FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
  82     FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE
  83 );
  84 ALTER TABLE public.node_ngrams OWNER TO gargantua;
  85
  86 CREATE TABLE public.node_nodengrams_nodengrams (
  87     node_id INTEGER NOT NULL,
  88     node_ngrams1_id INTEGER NOT NULL,
  89     node_ngrams2_id INTEGER NOT NULL,
  90     weight double precision,
  91     FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
  92     FOREIGN KEY (node_ngrams1_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
  93     FOREIGN KEY (node_ngrams2_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
  94     PRIMARY KEY (node_id, node_ngrams1_id, node_ngrams2_id)
  95 );
  96 ALTER TABLE public.node_nodengrams_nodengrams OWNER TO gargantua;
  97
  98 --------------------------------------------------------------
  99 --------------------------------------------------------------
 100 --
 101 --
 102 --CREATE TABLE public.nodes_ngrams_ngrams (
 103 --    node_id   integer NOT NULL REFERENCES public.nodes(id)  ON DELETE CASCADE,
 104 --    ngram1_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
 105 --    ngram2_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
 106 --    weight double precision,
 107 --    PRIMARY KEY (node_id,ngram1_id,ngram2_id)
 108 --);
 109 --
 110 --ALTER TABLE public.nodes_ngrams_ngrams OWNER TO gargantua;
 111 ---------------------------------------------------------------
 112 -- TODO nodes_nodes(node1_id int, node2_id int, edge_type int , weight real)
 113 CREATE TABLE public.nodes_nodes (
 114     node1_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
 115     node2_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
 116     score REAL,
 117     category INTEGER,
 118     PRIMARY KEY (node1_id, node2_id)
 119 );
 120 ALTER TABLE public.nodes_nodes OWNER TO gargantua;
 121
 122
 123 ---------------------------------------------------------------
 124 CREATE TABLE public.node_node_ngrams (
 125 node1_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 126 node2_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 127 ngrams_id  INTEGER NOT NULL REFERENCES public.ngrams (id) ON DELETE CASCADE,
 128 ngrams_type INTEGER,
 129 weight double precision,
 130 PRIMARY KEY (node1_id, node2_id, ngrams_id, ngrams_type)
 131 );
 132 ALTER TABLE public.node_node_ngrams OWNER TO gargantua;
 133
 134 CREATE TABLE public.node_node_ngrams2 (
 135 node_id         INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 136 nodengrams_id   INTEGER NOT NULL REFERENCES public.node_ngrams  (id) ON DELETE CASCADE,
 137 weight double precision,
 138 PRIMARY KEY (node_id, nodengrams_id)
 139 );
 140 ALTER TABLE public.node_node_ngrams2 OWNER TO gargantua;
 141
 142 --------------------------------------------------------------
 143
 144 --CREATE TABLE public.nodes_ngrams_repo (
 145 --    version integer NOT NULL,
 146 --    patches jsonb DEFAULT '{}'::jsonb NOT NULL,
 147 --    PRIMARY KEY (version)
 148 --);
 149 --ALTER TABLE public.nodes_ngrams_repo OWNER TO gargantua;
 150
 151 ---------------------------------------------------------
 152
 153 -- If needed for rights management at row level
 154 -- CREATE EXTENSION IF NOT EXISTS acl WITH SCHEMA public;
 155
 156 CREATE TABLE public.rights (
 157   user_id INTEGER NOT NULL REFERENCES public.auth_user(id) ON DELETE CASCADE,
 158   node_id INTEGER NOT NULL REFERENCES public.nodes(id)     ON DELETE CASCADE,
 159   rights  INTEGER NOT NULL,
 160   PRIMARY KEY (user_id, node_id)
 161 );
 162 ALTER TABLE public.rights OWNER TO gargantua;
 163
 164 ------------------------------------------------------------
 165 ------------------------------------------------------------
 166 -- INDEXES
 167 CREATE INDEX        ON public.auth_user USING btree (username varchar_pattern_ops);
 168 CREATE UNIQUE INDEX ON public.auth_user USING btree (username);
 169
 170 CREATE INDEX        ON public.rights USING btree (user_id,node_id);
 171
 172 CREATE INDEX        ON public.nodes USING gin (hyperdata);
 173 CREATE INDEX        ON public.nodes USING btree (user_id, typename, parent_id);
 174 CREATE INDEX        ON public.nodes USING btree (id, typename, date ASC);
 175 CREATE INDEX        ON public.nodes USING btree (id, typename, date DESC);
 176 CREATE INDEX        ON public.nodes USING btree (typename, id);
 177 CREATE UNIQUE INDEX ON public.nodes USING btree (hash_id);
 178 -- CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqId'::text)));
 179 -- CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqIdBdd'::text)));
 180 -- CREATE UNIQUE INDEX ON public.nodes USING btree (typename, parent_id, ((hyperdata ->> 'uniqId'::text)));
 181
 182 CREATE UNIQUE INDEX ON public.ngrams (terms); -- TEST GIN
 183 CREATE        INDEX ON public.ngrams USING btree (id, terms);
 184 CREATE UNIQUE INDEX ON public.ngrams_postag (lang_id,algo_id,postag,ngrams_id,lemm_id);
 185 CREATE        INDEX ON public.node_ngrams USING btree (node_id,node_subtype);
 186 CREATE UNIQUE INDEX ON public.node_ngrams USING btree (node_id,node_subtype, ngrams_id);
 187
 188 CREATE UNIQUE INDEX ON public.nodes_nodes  USING btree (node1_id, node2_id);
 189 CREATE INDEX        ON public.nodes_nodes  USING btree (node1_id, node2_id, category);
 190
 191 CREATE UNIQUE INDEX ON public.node_node_ngrams USING btree (node1_id, node2_id, ngrams_id, ngrams_type);
 192 CREATE        INDEX ON public.node_node_ngrams USING btree (node1_id,  node2_id);
 193 CREATE        INDEX ON public.node_node_ngrams USING btree (ngrams_id, node2_id);
 194 CREATE        INDEX ON public.node_node_ngrams USING btree (ngrams_type);
 195 CREATE INDEX ON public.node_nodengrams_nodengrams USING btree (node_id, node_ngrams1_id, node_ngrams2_id);
 196 CREATE INDEX ON public.node_nodengrams_nodengrams USING btree (node_ngrams1_id);
 197 CREATE INDEX ON public.node_nodengrams_nodengrams USING btree (node_ngrams2_id);
 198 CREATE INDEX ON public.node_node_ngrams2 USING btree (node_id);
 199 CREATE INDEX ON public.node_node_ngrams2 USING btree (nodengrams_id);
 200 CREATE INDEX ON public.node_node_ngrams2 USING btree (node_id, nodengrams_id);
 201 ------------------------------------------------------------
 202 ------------------------------------------------------------------------
 203 -- Ngrams Full DB Extraction Optim
 204 -- TODO remove hard parameter and move elsewhere
 205 CREATE OR REPLACE function node_pos(int, int) returns bigint
 206    AS 'SELECT count(id) from nodes
 207       WHERE  id < $1
 208       AND typename = $2
 209       '
 210    LANGUAGE SQL immutable;
 211
 212 --drop index node_by_pos;
 213 --create index node_by_pos on nodes using btree(node_pos(id,typename));
 214