]> Git — Sourcephile - gargantext.git/blob - devops/postgres/upgrade/0.0.5/schema.sql
[hal] use conduit for hal parsing
[gargantext.git] / devops / postgres / upgrade / 0.0.5 / schema.sql
1
2 -- TODO typename -> type_id
3 CREATE TABLE public.contexts (
4 id SERIAL,
5 hash_id CHARACTER varying(66) DEFAULT ''::character varying NOT NULL,
6 typename INTEGER NOT NULL,
7 user_id INTEGER NOT NULL,
8 parent_id INTEGER REFERENCES public.contexts(id) ON DELETE CASCADE ,
9 name CHARACTER varying(255) DEFAULT ''::character varying NOT NULL,
10 date TIMESTAMP with time zone DEFAULT now() NOT NULL,
11 hyperdata jsonb DEFAULT '{}'::jsonb NOT NULL,
12 search tsvector,
13 PRIMARY KEY (id),
14 FOREIGN KEY (user_id) REFERENCES public.auth_user(id) ON DELETE CASCADE
15 );
16 ALTER TABLE public.contexts OWNER TO gargantua;
17
18
19
20 -- To attach contexts to a Corpus
21 CREATE TABLE public.nodes_contexts (
22 node_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
23 context_id INTEGER NOT NULL REFERENCES public.contexts(id) ON DELETE CASCADE,
24 score REAL ,
25 category INTEGER ,
26 PRIMARY KEY (node_id, context_id)
27 );
28 ALTER TABLE public.nodes_contexts OWNER TO gargantua;
29
30 ---------------------------------------------------------------
31 CREATE TABLE public.context_node_ngrams (
32 context_id INTEGER NOT NULL REFERENCES public.contexts (id) ON DELETE CASCADE,
33 node_id INTEGER NOT NULL REFERENCES public.nodes (id) ON DELETE CASCADE,
34 ngrams_id INTEGER NOT NULL REFERENCES public.ngrams (id) ON DELETE CASCADE,
35 ngrams_type INTEGER ,
36 weight double precision,
37 PRIMARY KEY (context_id, node_id, ngrams_id, ngrams_type)
38 );
39
40
41 ALTER TABLE public.context_node_ngrams OWNER TO gargantua;
42
43 CREATE TABLE public.context_node_ngrams2 (
44 context_id INTEGER NOT NULL REFERENCES public.contexts (id) ON DELETE CASCADE,
45 nodengrams_id INTEGER NOT NULL REFERENCES public.node_ngrams (id) ON DELETE CASCADE,
46 weight double precision,
47 PRIMARY KEY (context_id, nodengrams_id)
48 );
49 ALTER TABLE public.context_node_ngrams2 OWNER TO gargantua;
50
51
52
53 CREATE INDEX ON public.contexts USING gin (hyperdata);
54 CREATE INDEX ON public.contexts USING btree (user_id, typename, parent_id);
55 CREATE INDEX ON public.contexts USING btree (id, typename, date ASC);
56 CREATE INDEX ON public.contexts USING btree (id, typename, date DESC);
57 CREATE INDEX ON public.contexts USING btree (typename, id);
58 CREATE UNIQUE INDEX ON public.contexts USING btree (hash_id);
59
60
61 -- To make the links between Corpus Node and its contexts
62 CREATE UNIQUE INDEX ON public.nodes_contexts USING btree (node_id, context_id);
63 CREATE INDEX ON public.nodes_contexts USING btree (node_id, context_id, category);
64
65
66 ------------------------------------------------------------------------
67 CREATE UNIQUE INDEX ON public.context_node_ngrams USING btree (context_id, node_id, ngrams_id, ngrams_type);
68 CREATE INDEX ON public.context_node_ngrams USING btree (context_id, node_id);
69 CREATE INDEX ON public.context_node_ngrams USING btree (ngrams_id, node_id);
70 CREATE INDEX ON public.context_node_ngrams USING btree (ngrams_type);
71
72 CREATE INDEX ON public.context_node_ngrams2 USING btree (context_id);
73 CREATE INDEX ON public.context_node_ngrams2 USING btree (nodengrams_id);
74 CREATE INDEX ON public.context_node_ngrams2 USING btree (context_id, nodengrams_id);
75
76 DROP TABLE if EXISTS public.node_nodengrams_nodengrams;
77
78 DROP TRIGGER if EXISTS trigger_count_delete2 ON nodes_nodes;
79 DROP TRIGGER if EXISTS trigger_count_update_add ON nodes_nodes;
80 DROP TRIGGER if EXISTS trigger_delete_count ON nodes_nodes;
81 DROP TRIGGER if EXISTS trigger_insert_count ON nodes_nodes;
82