]> Git — Sourcephile - gargantext.git/blob - src/Gargantext/Database/Schema/schema.sql
[NgramsTable][WIP] Get / Group / ungroup.
[gargantext.git] / src / Gargantext / Database / Schema / schema.sql
1 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
2 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
3 -- needed for rights management
4 -- CREATE EXTENSION IF NOT EXISTS acl WITH SCHEMA public;
5
6 -- CREATE USER WITH ...
7 -- createdb "gargandb"
8
9 CREATE TABLE public.auth_user (
10 id SERIAL,
11 password character varying(128) NOT NULL,
12 last_login timestamp with time zone,
13 is_superuser boolean NOT NULL,
14 username character varying(150) NOT NULL,
15 first_name character varying(30) NOT NULL,
16 last_name character varying(30) NOT NULL,
17 email character varying(254) NOT NULL,
18 is_staff boolean NOT NULL,
19 is_active boolean NOT NULL,
20 date_joined timestamp with time zone DEFAULT now() NOT NULL,
21 PRIMARY KEY (id)
22 );
23
24 ALTER TABLE public.auth_user OWNER TO gargantua;
25
26 -- TODO add publication_date
27 -- TODO typename -> type_id
28 CREATE TABLE public.nodes (
29 id SERIAL,
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
44 CREATE TABLE public.ngrams (
45 id SERIAL,
46 terms character varying(255),
47 n integer,
48 PRIMARY KEY (id)
49 );
50 ALTER TABLE public.ngrams OWNER TO gargantua;
51
52 -- TODO: delete ID
53 CREATE TABLE public.nodes_ngrams (
54 id SERIAL,
55 node_id integer NOT NULL,
56 ngrams_id integer NOT NULL,
57 parent_id integer REFERENCES public.nodes_ngrams(id) ON DELETE SET NULL,
58 ngrams_type integer,
59 list_type integer,
60 weight double precision,
61 FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
62 FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE,
63 PRIMARY KEY (id)
64 -- PRIMARY KEY (node_id,ngrams_id)
65 );
66 ALTER TABLE public.nodes_ngrams OWNER TO gargantua;
67
68 --
69 -- Name: nodes_ngrams_ngrams; Type: TABLE; Schema: public; Owner: gargantua
70 --
71
72 CREATE TABLE public.nodes_ngrams_ngrams (
73 node_id integer NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
74 ngram1_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
75 ngram2_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
76 weight double precision,
77 PRIMARY KEY (node_id,ngram1_id,ngram2_id)
78 );
79
80 ALTER TABLE public.nodes_ngrams_ngrams OWNER TO gargantua;
81
82
83 CREATE TABLE public.nodes_nodes (
84 node1_id integer NOT NULL,
85 node2_id integer NOT NULL,
86 score real,
87 favorite boolean,
88 delete boolean,
89 PRIMARY KEY (node1_id, node2_id)
90 );
91 ALTER TABLE public.nodes_nodes OWNER TO gargantua;
92
93
94 -- INDEXES
95 CREATE UNIQUE INDEX ON public.auth_user(username);
96
97 CREATE INDEX auth_user_username_like ON public.auth_user USING btree (username varchar_pattern_ops);
98
99 --CREATE INDEX ix_nodes_typename ON public.nodes USING btree (typename);
100 --CREATE INDEX ngrams_n_idx ON public.ngrams USING btree (n);
101 CREATE INDEX nodes_hyperdata_idx ON public.nodes USING gin (hyperdata);
102 CREATE UNIQUE INDEX nodes_expr_idx ON public.nodes USING btree (((hyperdata ->> 'uniqId'::text)));
103
104 CREATE UNIQUE INDEX nodes_expr_idx2 ON public.nodes USING btree (((hyperdata ->> 'uniqIdBdd'::text)));
105 CREATE UNIQUE INDEX nodes_typename_parent_id_expr_idx ON public.nodes USING btree (typename, parent_id, ((hyperdata ->> 'uniqId'::text)));
106 CREATE INDEX nodes_user_id_typename_parent_id_idx ON public.nodes USING btree (user_id, typename, parent_id);
107
108 CREATE UNIQUE INDEX ON public.ngrams(terms);
109 --CREATE UNIQUE INDEX ON public.ngrams(terms,n);
110
111 CREATE UNIQUE INDEX ON public.nodes_ngrams USING btree (node_id,ngrams_id);
112 CREATE INDEX nodes_ngrams_ngrams_id_idx ON public.nodes_ngrams USING btree (ngrams_id);
113 CREATE INDEX nodes_ngrams_ngrams_node_id_idx ON public.nodes_ngrams_ngrams USING btree (node_id);
114 CREATE UNIQUE INDEX ON public.nodes_ngrams USING btree (node_id,ngrams_id,ngrams_type);
115 CREATE INDEX nodes_nodes_delete ON public.nodes_nodes USING btree (node1_id, node2_id, delete);
116 CREATE UNIQUE INDEX nodes_nodes_node1_id_node2_id_idx ON public.nodes_nodes USING btree (node1_id, node2_id);
117
118 -- TRIGGERS
119 -- TODO user haskell-postgresql-simple to create this function
120 -- with rights typename
121 CREATE OR REPLACE FUNCTION public.search_update()
122 RETURNS trigger AS $$
123 begin
124 IF new.typename = 4 AND new.hyperdata @> '{"language_iso2":"EN"}' THEN
125 new.search := to_tsvector( 'english' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
126
127 ELSIF new.typename = 4 AND new.hyperdata @> '{"language_iso2":"FR"}' THEN
128 new.search := to_tsvector( 'french' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
129
130 ELSIF new.typename = 41 THEN
131 new.search := to_tsvector( 'french' , (new.hyperdata ->> 'prenom')
132 || ' ' || (new.hyperdata ->> 'nom')
133 || ' ' || (new.hyperdata ->> 'fonction')
134 );
135 ELSE
136 new.search := to_tsvector( 'english' , new.name);
137 END IF;
138 return new;
139 end
140 $$ LANGUAGE plpgsql;
141
142 ALTER FUNCTION public.search_update() OWNER TO gargantua;
143
144 CREATE TRIGGER search_update_trigger BEFORE INSERT OR UPDATE ON nodes FOR EACH ROW EXECUTE PROCEDURE search_update();
145
146
147 -- Initialize index with already existing data
148 UPDATE nodes SET hyperdata = hyperdata;
149