]> Git — Sourcephile - gargantext.git/blob - devops/postgres/schema.sql
[CLEAN]
[gargantext.git] / 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 ALTER TABLE public.auth_user OWNER TO gargantua;
23 -----------------------------------------------------------------
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 --------------------------------------------------------------
42
43 -- TODO add publication_date
44 -- TODO typename -> type_id
45 CREATE TABLE public.contexts (
46 id SERIAL,
47 hash_id CHARACTER varying(66) DEFAULT ''::character varying NOT NULL,
48 typename INTEGER NOT NULL,
49 user_id INTEGER NOT NULL,
50 parent_id INTEGER REFERENCES public.contexts(id) ON DELETE CASCADE ,
51 name CHARACTER varying(255) DEFAULT ''::character varying NOT NULL,
52 date TIMESTAMP with time zone DEFAULT now() NOT NULL,
53 hyperdata jsonb DEFAULT '{}'::jsonb NOT NULL,
54 search tsvector,
55 PRIMARY KEY (id),
56 FOREIGN KEY (user_id) REFERENCES public.auth_user(id) ON DELETE CASCADE
57 );
58 ALTER TABLE public.contexts OWNER TO gargantua;
59
60 --------------------------------------------------------------
61 -- | Ngrams
62 CREATE TABLE public.ngrams (
63 id SERIAL,
64 terms CHARACTER varying(255),
65 n INTEGER,
66 PRIMARY KEY (id)
67 );
68 ALTER TABLE public.ngrams OWNER TO gargantua;
69
70 -- | Ngrams PosTag
71 CREATE TABLE public.ngrams_postag (
72 id SERIAL ,
73 lang_id INTEGER ,
74 algo_id INTEGER ,
75 postag CHARACTER varying(5) ,
76 ngrams_id INTEGER NOT NULL ,
77 lemm_id INTEGER NOT NULL ,
78 score INTEGER DEFAULT 1 ::integer NOT NULL ,
79 FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE,
80 FOREIGN KEY (lemm_id) REFERENCES public.ngrams(id) ON DELETE CASCADE
81 );
82 ALTER TABLE public.ngrams_postag OWNER TO gargantua;
83
84 --------------------------------------------------------------
85 -- Node here should have type NodeList
86 CREATE TABLE public.node_ngrams (
87 id SERIAL ,
88 node_id INTEGER NOT NULL ,
89 node_subtype INTEGER ,
90 ngrams_id INTEGER NOT NULL ,
91 ngrams_type INTEGER , -- change to ngrams_field? (no for pedagogic reason)
92 ngrams_field INTEGER ,
93 ngrams_tag INTEGER ,
94 ngrams_class INTEGER ,
95 weight double precision ,
96 PRIMARY KEY (id) ,
97 FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE ,
98 FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE
99 );
100 ALTER TABLE public.node_ngrams OWNER TO gargantua;
101
102 --CREATE TABLE public.context_nodengrams_nodengrams (
103 -- context_id INTEGER NOT NULL ,
104 -- node_ngrams1_id INTEGER NOT NULL ,
105 -- node_ngrams2_id INTEGER NOT NULL ,
106 -- weight double precision ,
107 -- FOREIGN KEY (node_id) REFERENCES public.contexts(id) ON DELETE CASCADE ,
108 -- FOREIGN KEY (node_ngrams1_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
109 -- FOREIGN KEY (node_ngrams2_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
110 -- PRIMARY KEY (node_id, node_ngrams1_id, node_ngrams2_id)
111 --);
112 --ALTER TABLE public.context_nodengrams_nodengrams OWNER TO gargantua;
113
114 --------------------------------------------------------------
115 --------------------------------------------------------------
116 --
117 --CREATE TABLE public.nodes_ngrams_ngrams (
118 -- node_id integer NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
119 -- ngram1_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
120 -- ngram2_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
121 -- weight double precision,
122 -- PRIMARY KEY (node_id,ngram1_id,ngram2_id)
123 --);
124 --
125 --ALTER TABLE public.nodes_ngrams_ngrams OWNER TO gargantua;
126 ---------------------------------------------------------------
127 -- TODO nodes_nodes(node1_id int, node2_id int, edge_type int , weight real)
128 CREATE TABLE public.nodes_nodes (
129 node1_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
130 node2_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
131 score REAL ,
132 category INTEGER ,
133 PRIMARY KEY (node1_id, node2_id)
134 );
135 ALTER TABLE public.nodes_nodes OWNER TO gargantua;
136
137
138 -- To attach contexts to a Corpus
139 CREATE TABLE public.nodes_contexts (
140 node_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
141 context_id INTEGER NOT NULL REFERENCES public.contexts(id) ON DELETE CASCADE,
142 score REAL ,
143 category INTEGER ,
144 PRIMARY KEY (node_id, context_id)
145 );
146 ALTER TABLE public.nodes_contexts OWNER TO gargantua;
147
148 ---------------------------------------------------------------
149 CREATE TABLE public.context_node_ngrams (
150 context_id INTEGER NOT NULL REFERENCES public.contexts (id) ON DELETE CASCADE,
151 node_id INTEGER NOT NULL REFERENCES public.nodes (id) ON DELETE CASCADE,
152 ngrams_id INTEGER NOT NULL REFERENCES public.ngrams (id) ON DELETE CASCADE,
153 ngrams_type INTEGER ,
154 weight double precision,
155 PRIMARY KEY (context_id, node_id, ngrams_id, ngrams_type)
156 );
157
158
159 ALTER TABLE public.context_node_ngrams OWNER TO gargantua;
160
161 CREATE TABLE public.context_node_ngrams2 (
162 context_id INTEGER NOT NULL REFERENCES public.contexts (id) ON DELETE CASCADE,
163 nodengrams_id INTEGER NOT NULL REFERENCES public.node_ngrams (id) ON DELETE CASCADE,
164 weight double precision,
165 PRIMARY KEY (context_id, nodengrams_id)
166 );
167 ALTER TABLE public.context_node_ngrams2 OWNER TO gargantua;
168
169
170 --------------------------------------------------------------------
171 CREATE TABLE public.node_node_ngrams (
172 node1_id INTEGER NOT NULL REFERENCES public.nodes (id) ON DELETE CASCADE,
173 node2_id INTEGER NOT NULL REFERENCES public.nodes (id) ON DELETE CASCADE,
174 ngrams_id INTEGER NOT NULL REFERENCES public.ngrams (id) ON DELETE CASCADE,
175 ngrams_type INTEGER,
176 weight double precision,
177 PRIMARY KEY (node1_id, node2_id, ngrams_id, ngrams_type)
178 );
179 ALTER TABLE public.node_node_ngrams OWNER TO gargantua;
180
181 CREATE TABLE public.node_node_ngrams2 (
182 node_id INTEGER NOT NULL REFERENCES public.nodes (id) ON DELETE CASCADE,
183 nodengrams_id INTEGER NOT NULL REFERENCES public.node_ngrams (id) ON DELETE CASCADE,
184 weight double precision,
185 PRIMARY KEY (node_id, nodengrams_id)
186 );
187 ALTER TABLE public.node_node_ngrams2 OWNER TO gargantua;
188
189
190
191
192 --------------------------------------------------------------
193
194 --CREATE TABLE public.nodes_ngrams_repo (
195 -- version integer NOT NULL,
196 -- patches jsonb DEFAULT '{}'::jsonb NOT NULL,
197 -- PRIMARY KEY (version)
198 --);
199 --ALTER TABLE public.nodes_ngrams_repo OWNER TO gargantua;
200
201 ---------------------------------------------------------
202
203 -- If needed for rights management at row level
204 -- CREATE EXTENSION IF NOT EXISTS acl WITH SCHEMA public;
205 CREATE TABLE public.rights (
206 user_id INTEGER NOT NULL REFERENCES public.auth_user(id) ON DELETE CASCADE,
207 node_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
208 rights INTEGER NOT NULL,
209 PRIMARY KEY (user_id, node_id)
210 );
211 ALTER TABLE public.rights OWNER TO gargantua;
212
213 ------------------------------------------------------------
214 ------------------------------------------------------------
215 -- INDEXES
216 CREATE INDEX ON public.auth_user USING btree (username varchar_pattern_ops);
217 CREATE UNIQUE INDEX ON public.auth_user USING btree (username);
218
219 CREATE INDEX ON public.rights USING btree (user_id,node_id);
220
221 CREATE INDEX ON public.nodes USING gin (hyperdata);
222 CREATE INDEX ON public.nodes USING btree (user_id, typename, parent_id);
223 CREATE INDEX ON public.nodes USING btree (id, typename, date ASC);
224 CREATE INDEX ON public.nodes USING btree (id, typename, date DESC);
225 CREATE INDEX ON public.nodes USING btree (typename, id);
226 CREATE UNIQUE INDEX ON public.nodes USING btree (hash_id);
227
228 CREATE INDEX ON public.contexts USING gin (hyperdata);
229 CREATE INDEX ON public.contexts USING btree (user_id, typename, parent_id);
230 CREATE INDEX ON public.contexts USING btree (id, typename, date ASC);
231 CREATE INDEX ON public.contexts USING btree (id, typename, date DESC);
232 CREATE INDEX ON public.contexts USING btree (typename, id);
233 CREATE UNIQUE INDEX ON public.contexts USING btree (hash_id);
234
235
236 -- CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqId'::text)));
237 -- CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqIdBdd'::text)));
238 -- CREATE UNIQUE INDEX ON public.nodes USING btree (typename, parent_id, ((hyperdata ->> 'uniqId'::text)));
239
240 CREATE UNIQUE INDEX ON public.ngrams (terms); -- TEST GIN
241 CREATE INDEX ON public.ngrams USING btree (id, terms);
242 CREATE UNIQUE INDEX ON public.ngrams_postag (lang_id,algo_id,postag,ngrams_id,lemm_id);
243
244 -- To save the Node Ngrams Repo
245 CREATE INDEX ON public.node_ngrams USING btree (node_id,node_subtype);
246 CREATE UNIQUE INDEX ON public.node_ngrams USING btree (node_id,node_subtype, ngrams_id);
247
248
249
250 -- To make the links between Nodes in Tree/Forest
251 CREATE UNIQUE INDEX ON public.nodes_nodes USING btree (node1_id, node2_id);
252 CREATE INDEX ON public.nodes_nodes USING btree (node1_id, node2_id, category);
253
254 -- To make the links between Corpus Node and its contexts
255 CREATE UNIQUE INDEX ON public.nodes_contexts USING btree (node_id, context_id);
256 CREATE INDEX ON public.nodes_contexts USING btree (node_id, context_id, category);
257
258
259 ------------------------------------------------------------------------
260 CREATE UNIQUE INDEX ON public.context_node_ngrams USING btree (context_id, node_id, ngrams_id, ngrams_type);
261 CREATE INDEX ON public.context_node_ngrams USING btree (context_id, node_id);
262 CREATE INDEX ON public.context_node_ngrams USING btree (ngrams_id, node_id);
263 CREATE INDEX ON public.context_node_ngrams USING btree (ngrams_type);
264
265 CREATE INDEX ON public.context_node_ngrams2 USING btree (context_id);
266 CREATE INDEX ON public.context_node_ngrams2 USING btree (nodengrams_id);
267 CREATE INDEX ON public.context_node_ngrams2 USING btree (context_id, nodengrams_id);
268
269
270 CREATE UNIQUE INDEX ON public.node_node_ngrams USING btree (node1_id, node2_id, ngrams_id, ngrams_type);
271 CREATE INDEX ON public.node_node_ngrams USING btree (node1_id, node2_id);
272 CREATE INDEX ON public.node_node_ngrams USING btree (ngrams_id, node2_id);
273 CREATE INDEX ON public.node_node_ngrams USING btree (ngrams_type);
274 CREATE INDEX ON public.node_node_ngrams2 USING btree (node_id);
275 CREATE INDEX ON public.node_node_ngrams2 USING btree (nodengrams_id);
276 CREATE INDEX ON public.node_node_ngrams2 USING btree (node_id, nodengrams_id);
277
278 -- CREATE INDEX ON public.context_nodengrams_nodengrams USING btree (context_id, node_ngrams1_id, node_ngrams2_id);
279 -- CREATE INDEX ON public.context_nodengrams_nodengrams USING btree (node_ngrams1_id);
280 -- CREATE INDEX ON public.context_nodengrams_nodengrams USING btree (node_ngrams2_id);
281
282
283 ------------------------------------------------------------------------
284 -- Ngrams Full DB Extraction Optim
285 -- TODO remove hard parameter and move elsewhere
286 CREATE OR REPLACE function node_pos(int, int) returns bigint
287 AS 'SELECT count(id) from nodes
288 WHERE id < $1
289 AND typename = $2
290 '
291 LANGUAGE SQL immutable;
292
293 --drop index node_by_pos;
294 --create index node_by_pos on nodes using btree(node_pos(id,typename));
295