]> Git — Sourcephile - gargantext.git/blob - devops/postgres/schema.sql
changement du seuil
[gargantext.git] / 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 typename INTEGER NOT NULL,
27 user_id INTEGER NOT NULL,
28 parent_id INTEGER REFERENCES public.nodes(id) ON DELETE CASCADE ,
29 name CHARACTER varying(255) DEFAULT ''::character varying NOT NULL,
30 date TIMESTAMP with time zone DEFAULT now() NOT NULL,
31 hyperdata jsonb DEFAULT '{}'::jsonb NOT NULL,
32 search tsvector,
33 PRIMARY KEY (id),
34 FOREIGN KEY (user_id) REFERENCES public.auth_user(id) ON DELETE CASCADE
35 );
36 ALTER TABLE public.nodes OWNER TO gargantua;
37
38 CREATE TABLE public.ngrams (
39 id SERIAL,
40 terms CHARACTER varying(255),
41 n INTEGER,
42 PRIMARY KEY (id)
43 );
44 ALTER TABLE public.ngrams OWNER TO gargantua;
45
46 --------------------------------------------------------------
47 CREATE TABLE public.node_ngrams (
48 id SERIAL,
49 node_id INTEGER NOT NULL,
50 node_subtype INTEGER,
51 ngrams_id INTEGER NOT NULL,
52 ngrams_type INTEGER, -- change to ngrams_field? (no for pedagogic reason)
53 ngrams_field INTEGER,
54 ngrams_tag INTEGER,
55 ngrams_class INTEGER,
56 weight double precision,
57 PRIMARY KEY (id),
58 FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
59 FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE
60 );
61 ALTER TABLE public.node_ngrams OWNER TO gargantua;
62
63 CREATE TABLE public.node_nodengrams_nodengrams (
64 node_id INTEGER NOT NULL,
65 node_ngrams1_id INTEGER NOT NULL,
66 node_ngrams2_id INTEGER NOT NULL,
67 weight double precision,
68 FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
69 FOREIGN KEY (node_ngrams1_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
70 FOREIGN KEY (node_ngrams2_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
71 PRIMARY KEY (node_id, node_ngrams1_id, node_ngrams2_id)
72 );
73 ALTER TABLE public.node_nodengrams_nodengrams OWNER TO gargantua;
74
75 --------------------------------------------------------------
76 --------------------------------------------------------------
77 --
78 --
79 --CREATE TABLE public.nodes_ngrams_ngrams (
80 -- node_id integer NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
81 -- ngram1_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
82 -- ngram2_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
83 -- weight double precision,
84 -- PRIMARY KEY (node_id,ngram1_id,ngram2_id)
85 --);
86 --
87 --ALTER TABLE public.nodes_ngrams_ngrams OWNER TO gargantua;
88 ---------------------------------------------------------------
89 -- TODO nodes_nodes(node1_id int, node2_id int, edge_type int , weight real)
90 CREATE TABLE public.nodes_nodes (
91 node1_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
92 node2_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
93 score REAL,
94 category INTEGER,
95 PRIMARY KEY (node1_id,node2_id)
96 );
97 ALTER TABLE public.nodes_nodes OWNER TO gargantua;
98
99 ---------------------------------------------------------------
100 CREATE TABLE public.node_node_ngrams (
101 node1_id INTEGER NOT NULL REFERENCES public.nodes (id) ON DELETE CASCADE,
102 node2_id INTEGER NOT NULL REFERENCES public.nodes (id) ON DELETE CASCADE,
103 ngrams_id INTEGER NOT NULL REFERENCES public.ngrams (id) ON DELETE CASCADE,
104 ngrams_type INTEGER,
105 weight double precision,
106 PRIMARY KEY (node1_id, node2_id, ngrams_id, ngrams_type)
107 );
108 ALTER TABLE public.node_node_ngrams OWNER TO gargantua;
109
110
111 CREATE TABLE public.node_node_ngrams2 (
112 node_id INTEGER NOT NULL REFERENCES public.nodes (id) ON DELETE CASCADE,
113 nodengrams_id INTEGER NOT NULL REFERENCES public.node_ngrams (id) ON DELETE CASCADE,
114 weight double precision,
115 PRIMARY KEY (node_id, nodengrams_id)
116 );
117 ALTER TABLE public.node_node_ngrams2 OWNER TO gargantua;
118
119 --------------------------------------------------------------
120
121 --CREATE TABLE public.nodes_ngrams_repo (
122 -- version integer NOT NULL,
123 -- patches jsonb DEFAULT '{}'::jsonb NOT NULL,
124 -- PRIMARY KEY (version)
125 --);
126 --ALTER TABLE public.nodes_ngrams_repo OWNER TO gargantua;
127
128 ---------------------------------------------------------
129
130 -- If needed for rights management at row level
131 -- CREATE EXTENSION IF NOT EXISTS acl WITH SCHEMA public;
132
133 CREATE TABLE public.rights (
134 user_id INTEGER NOT NULL REFERENCES public.auth_user(id) ON DELETE CASCADE,
135 node_id INTEGER NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
136 rights INTEGER NOT NULL,
137 PRIMARY KEY (user_id, node_id)
138 );
139 ALTER TABLE public.rights OWNER TO gargantua;
140
141 ------------------------------------------------------------
142 ------------------------------------------------------------
143 -- INDEXES
144 CREATE INDEX ON public.auth_user USING btree (username varchar_pattern_ops);
145 CREATE UNIQUE INDEX ON public.auth_user USING btree (username);
146
147 CREATE INDEX ON public.rights USING btree (user_id,node_id);
148
149 CREATE INDEX ON public.nodes USING gin (hyperdata);
150 CREATE INDEX ON public.nodes USING btree (user_id, typename, parent_id);
151 CREATE INDEX ON public.nodes USING btree (id, typename, date ASC);
152 CREATE INDEX ON public.nodes USING btree (id, typename, date DESC);
153 CREATE INDEX ON public.nodes USING btree (typename, id);
154 CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqId'::text)));
155 CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqIdBdd'::text)));
156 CREATE UNIQUE INDEX ON public.nodes USING btree (typename, parent_id, ((hyperdata ->> 'uniqId'::text)));
157
158 CREATE UNIQUE INDEX ON public.ngrams (terms); -- TEST GIN
159 CREATE INDEX ON public.ngrams USING btree (id, terms);
160 CREATE INDEX ON public.node_ngrams USING btree (node_id,node_subtype);
161 CREATE UNIQUE INDEX ON public.node_ngrams USING btree (node_id,node_subtype, ngrams_id);
162
163 CREATE UNIQUE INDEX ON public.nodes_nodes USING btree (node1_id, node2_id);
164 CREATE INDEX ON public.nodes_nodes USING btree (node1_id, node2_id, category);
165
166 CREATE UNIQUE INDEX ON public.node_node_ngrams USING btree (node1_id, node2_id, ngrams_id, ngrams_type);
167 CREATE INDEX ON public.node_node_ngrams USING btree (node1_id, node2_id);
168 CREATE INDEX ON public.node_node_ngrams USING btree (ngrams_id, node2_id);
169 CREATE INDEX ON public.node_node_ngrams USING btree (ngrams_type);
170 CREATE INDEX ON public.node_nodengrams_nodengrams USING btree (node_id, node_ngrams1_id, node_ngrams2_id);
171 CREATE INDEX ON public.node_nodengrams_nodengrams USING btree (node_ngrams1_id);
172 CREATE INDEX ON public.node_nodengrams_nodengrams USING btree (node_ngrams2_id);
173 CREATE INDEX ON public.node_node_ngrams2 USING btree (node_id);
174 CREATE INDEX ON public.node_node_ngrams2 USING btree (nodengrams_id);
175 CREATE INDEX ON public.node_node_ngrams2 USING btree (node_id, nodengrams_id);
176 ------------------------------------------------------------
177 ------------------------------------------------------------------------
178 -- Ngrams Full DB Extraction Optim
179 -- TODO remove hard parameter and move elsewhere
180 CREATE OR REPLACE function node_pos(int, int) returns bigint
181 AS 'SELECT count(id) from nodes
182 WHERE id < $1
183 AND typename = $2
184 '
185 LANGUAGE SQL immutable;
186
187 --drop index node_by_pos;
188 create index node_by_pos on nodes using btree(node_pos(id,typename));
189