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;
6 -- CREATE USER WITH ...
9 CREATE TABLE public.
auth_user (
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,
24 ALTER TABLE public.auth_user
OWNER TO gargantua
;
26 -- TODO add publication_date
27 -- TODO typename -> type_id
28 CREATE TABLE public.
nodes (
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,
38 FOREIGN KEY (user_id
) REFERENCES public.
auth_user(id) ON DELETE CASCADE
40 ALTER TABLE public.nodes
OWNER TO gargantua
;
44 CREATE TABLE public.
ngrams (
46 terms
character varying(255),
50 ALTER TABLE public.ngrams
OWNER TO gargantua
;
53 CREATE TABLE public.
nodes_ngrams (
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,
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,
64 -- PRIMARY KEY (node_id,ngrams_id)
66 ALTER TABLE public.nodes_ngrams
OWNER TO gargantua
;
69 -- Name: nodes_ngrams_ngrams; Type: TABLE; Schema: public; Owner: gargantua
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
)
80 ALTER TABLE public.nodes_ngrams_ngrams
OWNER TO gargantua
;
83 CREATE TABLE public.
nodes_nodes (
84 node1_id
integer NOT NULL,
85 node2_id
integer NOT NULL,
89 PRIMARY KEY (node1_id
, node2_id
)
91 ALTER TABLE public.nodes_nodes
OWNER TO gargantua
;
95 CREATE UNIQUE INDEX ON public.
auth_user(username
);
97 CREATE INDEX auth_user_username_like
ON public.auth_user
USING btree (username varchar_pattern_ops
);
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)));
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
);
108 CREATE UNIQUE INDEX ON public.
ngrams(terms
);
109 --CREATE UNIQUE INDEX ON public.ngrams(terms,n);
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
);
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 $$
124 IF new.typename
= 4 AND new.hyperdata @
> '{"language_iso2":"EN"}' THEN
125 new.
search := to_tsvector( 'english' , (new.hyperdata
->> 'title') ||
' ' ||
(new.hyperdata
->> 'abstract'));
127 ELSIF
new.typename
= 4 AND new.hyperdata @
> '{"language_iso2":"FR"}' THEN
128 new.
search := to_tsvector( 'french' , (new.hyperdata
->> 'title') ||
' ' ||
(new.hyperdata
->> 'abstract'));
130 ELSIF
new.typename
= 41 THEN
131 new.
search := to_tsvector( 'french' , (new.hyperdata
->> 'prenom')
132 ||
' ' ||
(new.hyperdata
->> 'nom')
133 ||
' ' ||
(new.hyperdata
->> 'fonction')
136 new.
search := to_tsvector( 'english' , new.
name);
142 ALTER FUNCTION public.
search_update() OWNER TO gargantua
;
144 CREATE TRIGGER search_update_trigger
BEFORE INSERT OR UPDATE ON nodes
FOR EACH ROW EXECUTE PROCEDURE search_update();
147 -- Initialize index with already existing data
148 UPDATE nodes
SET hyperdata
= hyperdata
;