[OPTIM] concurrent threads (fix mem leaks)
[gargantext.git] / devops / postgres / schema.sql
index 48bf2a0355669f8f48b75d54c4e1eee76757a733..a616cece3de55b0c8eec9a5e7b67acd5d67a2547 100644 (file)
@@ -4,16 +4,16 @@ COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
 
 CREATE TABLE public.auth_user (
     id SERIAL,
-    password     character varying(128) NOT NULL,
-    last_login   timestamp with time zone,
-    is_superuser boolean NOT NULL,
-    username     character varying(150) NOT NULL,
-    first_name   character varying(30) NOT NULL,
-    last_name    character varying(30) NOT NULL,
-    email        character varying(254) NOT NULL,
-    is_staff     boolean NOT NULL,
-    is_active    boolean NOT NULL,
-    date_joined timestamp with time zone DEFAULT now() NOT NULL,
+    password     CHARACTER varying(128) NOT NULL,
+    last_login   TIMESTAMP with time zone,
+    is_superuser BOOLEAN NOT NULL,
+    username     CHARACTER varying(150) NOT NULL,
+    first_name   CHARACTER varying(30) NOT NULL,
+    last_name    CHARACTER varying(30) NOT NULL,
+    email        CHARACTER varying(254) NOT NULL,
+    is_staff     BOOLEAN NOT NULL,
+    is_active    BOOLEAN NOT NULL,
+    date_joined  TIMESTAMP with time zone DEFAULT now() NOT NULL,
     PRIMARY KEY (id)
 );
 
@@ -23,11 +23,11 @@ ALTER TABLE public.auth_user OWNER TO gargantua;
 -- TODO typename -> type_id
 CREATE TABLE public.nodes (
     id        SERIAL,
-    typename  integer NOT NULL,
-    user_id   integer NOT NULL,
-    parent_id integer REFERENCES public.nodes(id) ON DELETE CASCADE ,
-    name      character varying(255) DEFAULT ''::character varying NOT NULL,
-    date      timestamp with time zone DEFAULT now() NOT NULL,
+    typename  INTEGER NOT NULL,
+    user_id   INTEGER NOT NULL,
+    parent_id INTEGER REFERENCES public.nodes(id) ON DELETE CASCADE ,
+    name      CHARACTER varying(255) DEFAULT ''::character varying NOT NULL,
+    date      TIMESTAMP with time zone DEFAULT now() NOT NULL,
     hyperdata jsonb DEFAULT '{}'::jsonb NOT NULL,
     search tsvector,
     PRIMARY KEY (id),
@@ -37,8 +37,8 @@ ALTER TABLE public.nodes OWNER TO gargantua;
 
 CREATE TABLE public.ngrams (
     id SERIAL,
-    terms character varying(255),
-    n integer,
+    terms CHARACTER varying(255),
+    n INTEGER,
     PRIMARY KEY (id)
 );
 ALTER TABLE public.ngrams OWNER TO gargantua;
@@ -46,13 +46,13 @@ ALTER TABLE public.ngrams OWNER TO gargantua;
 --------------------------------------------------------------
 CREATE TABLE public.node_ngrams (
     id SERIAL,
-    node_id integer NOT NULL,
-    node_subtype integer,
-    ngrams_id integer NOT NULL,
-    ngrams_type integer, -- change to ngrams_field? (no for pedagogic reason)
-    ngrams_field integer,
-    ngrams_tag integer,
-    ngrams_class integer,
+    node_id INTEGER NOT NULL,
+    node_subtype INTEGER,
+    ngrams_id INTEGER NOT NULL,
+    ngrams_type INTEGER, -- change to ngrams_field? (no for pedagogic reason)
+    ngrams_field INTEGER,
+    ngrams_tag INTEGER,
+    ngrams_class INTEGER,
     weight double precision,
     PRIMARY KEY (id),
     FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
@@ -60,16 +60,17 @@ CREATE TABLE public.node_ngrams (
 );
 ALTER TABLE public.node_ngrams OWNER TO gargantua;
 
-CREATE TABLE public.node_node_ngrams_ngrams (
-    node_id integer NOT NULL,
-    node_ngrams1_id integer NOT NULL,
-    node_ngrams2_id integer NOT NULL,
+CREATE TABLE public.node_nodengrams_nodengrams (
+    node_id INTEGER NOT NULL,
+    node_ngrams1_id INTEGER NOT NULL,
+    node_ngrams2_id INTEGER NOT NULL,
+    weight double precision,
     FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
     FOREIGN KEY (node_ngrams1_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
     FOREIGN KEY (node_ngrams2_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
     PRIMARY KEY (node_id, node_ngrams1_id, node_ngrams2_id)
 );
-ALTER TABLE public.node_node_ngrams_ngrams OWNER TO gargantua;
+ALTER TABLE public.node_nodengrams_nodengrams OWNER TO gargantua;
 
 --------------------------------------------------------------
 --------------------------------------------------------------
@@ -87,28 +88,34 @@ ALTER TABLE public.node_node_ngrams_ngrams OWNER TO gargantua;
 ---------------------------------------------------------------
 -- TODO nodes_nodes(node1_id int, node2_id int, edge_type int , weight real)
 CREATE TABLE public.nodes_nodes (
-    node1_id integer NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
-    node2_id integer NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
-    score real,
-    category integer,
+    node1_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
+    node2_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
+    score REAL,
+    category INTEGER,
     PRIMARY KEY (node1_id,node2_id)
 );
 ALTER TABLE public.nodes_nodes OWNER TO gargantua;
 
 ---------------------------------------------------------------
--- TODO should reference "id" of nodes_nodes (instead of node1_id, node2_id)
 CREATE TABLE public.node_node_ngrams (
 node1_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
--- here id to node_ngrams
 node2_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 ngrams_id  INTEGER NOT NULL REFERENCES public.ngrams (id) ON DELETE CASCADE,
 ngrams_type INTEGER,
---ngrams_tag INTEGER,
---ngrams_class INTEGER,
 weight double precision,
 PRIMARY KEY (node1_id, node2_id, ngrams_id, ngrams_type)
 );
 ALTER TABLE public.node_node_ngrams OWNER TO gargantua;
+
+
+CREATE TABLE public.node_node_ngrams2 (
+node_id         INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
+nodengrams_id   INTEGER NOT NULL REFERENCES public.node_ngrams  (id) ON DELETE CASCADE,
+weight double precision,
+PRIMARY KEY (node_id, nodengrams_id)
+);
+ALTER TABLE public.node_node_ngrams2 OWNER TO gargantua;
+
 --------------------------------------------------------------
 
 --CREATE TABLE public.nodes_ngrams_repo (
@@ -131,9 +138,9 @@ CREATE TABLE public.rights (
 );
 ALTER TABLE public.rights OWNER TO gargantua;
 
+------------------------------------------------------------
 ------------------------------------------------------------
 -- INDEXES
-
 CREATE INDEX        ON public.auth_user USING btree (username varchar_pattern_ops);
 CREATE UNIQUE INDEX ON public.auth_user USING btree (username);
 
@@ -141,6 +148,8 @@ CREATE INDEX        ON public.rights USING btree (user_id,node_id);
 
 CREATE INDEX        ON public.nodes USING gin (hyperdata);
 CREATE INDEX        ON public.nodes USING btree (user_id, typename, parent_id);
+CREATE INDEX        ON public.nodes USING btree (id, typename, date ASC);
+CREATE INDEX        ON public.nodes USING btree (id, typename, date DESC);
 CREATE INDEX        ON public.nodes USING btree (typename, id);
 CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqId'::text)));
 CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqIdBdd'::text)));
@@ -148,16 +157,23 @@ CREATE UNIQUE INDEX ON public.nodes USING btree (typename, parent_id, ((hyperdat
 
 CREATE UNIQUE INDEX ON public.ngrams (terms); -- TEST GIN
 CREATE        INDEX ON public.ngrams USING btree (id, terms);
+CREATE        INDEX ON public.node_ngrams USING btree (node_id,node_subtype);
 CREATE UNIQUE INDEX ON public.node_ngrams USING btree (node_id,node_subtype, ngrams_id);
 
-CREATE INDEX        ON public.nodes_nodes  USING btree (node1_id, node2_id, category);
 CREATE UNIQUE INDEX ON public.nodes_nodes  USING btree (node1_id, node2_id);
+CREATE INDEX        ON public.nodes_nodes  USING btree (node1_id, node2_id, category);
 
 CREATE UNIQUE INDEX ON public.node_node_ngrams USING btree (node1_id, node2_id, ngrams_id, ngrams_type);
 CREATE        INDEX ON public.node_node_ngrams USING btree (node1_id,  node2_id);
 CREATE        INDEX ON public.node_node_ngrams USING btree (ngrams_id, node2_id);
-
-
+CREATE        INDEX ON public.node_node_ngrams USING btree (ngrams_type);
+CREATE INDEX ON public.node_nodengrams_nodengrams USING btree (node_id, node_ngrams1_id, node_ngrams2_id);
+CREATE INDEX ON public.node_nodengrams_nodengrams USING btree (node_ngrams1_id);
+CREATE INDEX ON public.node_nodengrams_nodengrams USING btree (node_ngrams2_id);
+CREATE INDEX ON public.node_node_ngrams2 USING btree (node_id);
+CREATE INDEX ON public.node_node_ngrams2 USING btree (nodengrams_id);
+CREATE INDEX ON public.node_node_ngrams2 USING btree (node_id, nodengrams_id);
+------------------------------------------------------------
 ------------------------------------------------------------------------
 -- Ngrams Full DB Extraction Optim
 -- TODO remove hard parameter and move elsewhere