devops/postgres/schema.sql

   1 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
   2 CREATE EXTENSION IF NOT EXISTS tsm_system_rows;
   3 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
   4
   5 CREATE TABLE public.auth_user (
   6     id SERIAL,
   7     password     CHARACTER varying(128) NOT NULL,
   8     last_login   TIMESTAMP with time zone,
   9     is_superuser BOOLEAN NOT NULL,
  10     username     CHARACTER varying(150) NOT NULL,
  11     first_name   CHARACTER varying(30) NOT NULL,
  12     last_name    CHARACTER varying(30) NOT NULL,
  13     email        CHARACTER varying(254) NOT NULL,
  14     is_staff     BOOLEAN NOT NULL,
  15     is_active    BOOLEAN NOT NULL,
  16     date_joined  TIMESTAMP with time zone DEFAULT now() NOT NULL,
  17     PRIMARY KEY (id)
  18 );
  19
  20 ALTER TABLE public.auth_user OWNER TO gargantua;
  21
  22 -- TODO add publication_date
  23 -- TODO typename -> type_id
  24 CREATE TABLE public.nodes (
  25     id        SERIAL,
  26     hash_id   CHARACTER varying(66) DEFAULT ''::character varying NOT NULL,
  27     typename  INTEGER NOT NULL,
  28     user_id   INTEGER NOT NULL,
  29     parent_id INTEGER REFERENCES public.nodes(id) ON DELETE CASCADE ,
  30     name      CHARACTER varying(255) DEFAULT ''::character varying NOT NULL,
  31     date      TIMESTAMP with time zone DEFAULT now() NOT NULL,
  32     hyperdata jsonb DEFAULT '{}'::jsonb NOT NULL,
  33     search tsvector,
  34     PRIMARY KEY (id),
  35     FOREIGN KEY (user_id)  REFERENCES public.auth_user(id) ON DELETE CASCADE
  36 );
  37 ALTER TABLE public.nodes OWNER TO gargantua;
  38
  39 CREATE TABLE public.ngrams (
  40     id SERIAL,
  41     terms CHARACTER varying(255),
  42     n INTEGER,
  43     PRIMARY KEY (id)
  44 );
  45 ALTER TABLE public.ngrams OWNER TO gargantua;
  46
  47 --------------------------------------------------------------
  48 CREATE TABLE public.node_ngrams (
  49     id SERIAL,
  50     node_id INTEGER NOT NULL,
  51     node_subtype INTEGER,
  52     ngrams_id INTEGER NOT NULL,
  53     ngrams_type INTEGER, -- change to ngrams_field? (no for pedagogic reason)
  54     ngrams_field INTEGER,
  55     ngrams_tag INTEGER,
  56     ngrams_class INTEGER,
  57     weight double precision,
  58     PRIMARY KEY (id),
  59     FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
  60     FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE
  61 );
  62 ALTER TABLE public.node_ngrams OWNER TO gargantua;
  63
  64 CREATE TABLE public.node_nodengrams_nodengrams (
  65     node_id INTEGER NOT NULL,
  66     node_ngrams1_id INTEGER NOT NULL,
  67     node_ngrams2_id INTEGER NOT NULL,
  68     weight double precision,
  69     FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
  70     FOREIGN KEY (node_ngrams1_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
  71     FOREIGN KEY (node_ngrams2_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
  72     PRIMARY KEY (node_id, node_ngrams1_id, node_ngrams2_id)
  73 );
  74 ALTER TABLE public.node_nodengrams_nodengrams OWNER TO gargantua;
  75
  76 --------------------------------------------------------------
  77 --------------------------------------------------------------
  78 --
  79 --
  80 --CREATE TABLE public.nodes_ngrams_ngrams (
  81 --    node_id   integer NOT NULL REFERENCES public.nodes(id)  ON DELETE CASCADE,
  82 --    ngram1_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
  83 --    ngram2_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
  84 --    weight double precision,
  85 --    PRIMARY KEY (node_id,ngram1_id,ngram2_id)
  86 --);
  87 --
  88 --ALTER TABLE public.nodes_ngrams_ngrams OWNER TO gargantua;
  89 ---------------------------------------------------------------
  90 -- TODO nodes_nodes(node1_id int, node2_id int, edge_type int , weight real)
  91 CREATE TABLE public.nodes_nodes (
  92     node1_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
  93     node2_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
  94     score REAL,
  95     category INTEGER,
  96     PRIMARY KEY (node1_id, node2_id)
  97 );
  98 ALTER TABLE public.nodes_nodes OWNER TO gargantua;
  99
 100
 101 ---------------------------------------------------------------
 102 CREATE TABLE public.node_node_ngrams (
 103 node1_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 104 node2_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 105 ngrams_id  INTEGER NOT NULL REFERENCES public.ngrams (id) ON DELETE CASCADE,
 106 ngrams_type INTEGER,
 107 weight double precision,
 108 PRIMARY KEY (node1_id, node2_id, ngrams_id, ngrams_type)
 109 );
 110 ALTER TABLE public.node_node_ngrams OWNER TO gargantua;
 111
 112 CREATE TABLE public.node_node_ngrams2 (
 113 node_id         INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 114 nodengrams_id   INTEGER NOT NULL REFERENCES public.node_ngrams  (id) ON DELETE CASCADE,
 115 weight double precision,
 116 PRIMARY KEY (node_id, nodengrams_id)
 117 );
 118 ALTER TABLE public.node_node_ngrams2 OWNER TO gargantua;
 119
 120 --------------------------------------------------------------
 121
 122 --CREATE TABLE public.nodes_ngrams_repo (
 123 --    version integer NOT NULL,
 124 --    patches jsonb DEFAULT '{}'::jsonb NOT NULL,
 125 --    PRIMARY KEY (version)
 126 --);
 127 --ALTER TABLE public.nodes_ngrams_repo OWNER TO gargantua;
 128
 129 ---------------------------------------------------------
 130
 131 -- If needed for rights management at row level
 132 -- CREATE EXTENSION IF NOT EXISTS acl WITH SCHEMA public;
 133
 134 CREATE TABLE public.rights (
 135   user_id INTEGER NOT NULL REFERENCES public.auth_user(id) ON DELETE CASCADE,
 136   node_id INTEGER NOT NULL REFERENCES public.nodes(id)     ON DELETE CASCADE,
 137   rights  INTEGER NOT NULL,
 138   PRIMARY KEY (user_id, node_id)
 139 );
 140 ALTER TABLE public.rights OWNER TO gargantua;
 141
 142 ------------------------------------------------------------
 143 ------------------------------------------------------------
 144 -- INDEXES
 145 CREATE INDEX        ON public.auth_user USING btree (username varchar_pattern_ops);
 146 CREATE UNIQUE INDEX ON public.auth_user USING btree (username);
 147
 148 CREATE INDEX        ON public.rights USING btree (user_id,node_id);
 149
 150 CREATE INDEX        ON public.nodes USING gin (hyperdata);
 151 CREATE INDEX        ON public.nodes USING btree (user_id, typename, parent_id);
 152 CREATE INDEX        ON public.nodes USING btree (id, typename, date ASC);
 153 CREATE INDEX        ON public.nodes USING btree (id, typename, date DESC);
 154 CREATE INDEX        ON public.nodes USING btree (typename, id);
 155 CREATE UNIQUE INDEX ON public.nodes USING btree (hash_id);
 156 -- CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqId'::text)));
 157 -- CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqIdBdd'::text)));
 158 -- CREATE UNIQUE INDEX ON public.nodes USING btree (typename, parent_id, ((hyperdata ->> 'uniqId'::text)));
 159
 160 CREATE UNIQUE INDEX ON public.ngrams (terms); -- TEST GIN
 161 CREATE        INDEX ON public.ngrams USING btree (id, terms);
 162 CREATE        INDEX ON public.node_ngrams USING btree (node_id,node_subtype);
 163 CREATE UNIQUE INDEX ON public.node_ngrams USING btree (node_id,node_subtype, ngrams_id);
 164
 165 CREATE UNIQUE INDEX ON public.nodes_nodes  USING btree (node1_id, node2_id);
 166 CREATE INDEX        ON public.nodes_nodes  USING btree (node1_id, node2_id, category);
 167
 168 CREATE UNIQUE INDEX ON public.node_node_ngrams USING btree (node1_id, node2_id, ngrams_id, ngrams_type);
 169 CREATE        INDEX ON public.node_node_ngrams USING btree (node1_id,  node2_id);
 170 CREATE        INDEX ON public.node_node_ngrams USING btree (ngrams_id, node2_id);
 171 CREATE        INDEX ON public.node_node_ngrams USING btree (ngrams_type);
 172 CREATE INDEX ON public.node_nodengrams_nodengrams USING btree (node_id, node_ngrams1_id, node_ngrams2_id);
 173 CREATE INDEX ON public.node_nodengrams_nodengrams USING btree (node_ngrams1_id);
 174 CREATE INDEX ON public.node_nodengrams_nodengrams USING btree (node_ngrams2_id);
 175 CREATE INDEX ON public.node_node_ngrams2 USING btree (node_id);
 176 CREATE INDEX ON public.node_node_ngrams2 USING btree (nodengrams_id);
 177 CREATE INDEX ON public.node_node_ngrams2 USING btree (node_id, nodengrams_id);
 178 ------------------------------------------------------------
 179 ------------------------------------------------------------------------
 180 -- Ngrams Full DB Extraction Optim
 181 -- TODO remove hard parameter and move elsewhere
 182 CREATE OR REPLACE function node_pos(int, int) returns bigint
 183    AS 'SELECT count(id) from nodes
 184       WHERE  id < $1
 185       AND typename = $2
 186       '
 187    LANGUAGE SQL immutable;
 188
 189 --drop index node_by_pos;
 190 create index node_by_pos on nodes using btree(node_pos(id,typename));
 191
 192
 193
 194 -- Trigger to update hash of nodes
 195 CREATE EXTENSION pgcrypto;
 196 CREATE OR REPLACE FUNCTION hash_update_nodes()
 197 RETURNS trigger AS $$
 198 BEGIN
 199     IF tg_op = 'INSERT' OR tg_op = 'UPDATE' THEN
 200         IF NEW.hash_id = ''
 201           THEN NEW.hash_id = digest(CONCAT(NEW.id, NEW.hyperdata), 'sha256');
 202         END IF;
 203         RETURN NEW;
 204     END IF;
 205 END
 206 $$ LANGUAGE plpgsql;
 207
 208 CREATE TRIGGER some_table_hash_update
 209 BEFORE INSERT OR UPDATE ON nodes FOR EACH ROW EXECUTE PROCEDURE hash_update_nodes();