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)
);
-- 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),
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;
--------------------------------------------------------------
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,
);
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;
--------------------------------------------------------------
--------------------------------------------------------------
---------------------------------------------------------------
-- 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 (
);
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);
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)));
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