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 CREATE EXTENSION IF NOT EXISTS acl WITH SCHEMA public;
   4
   5 -- CREATE USER WITH ...
   6 -- createdb "gargandb"
   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
  23 ALTER TABLE public.auth_user OWNER TO gargantua;
  24
  25 -- TODO add publication_date
  26 -- TODO typename -> type_id
  27 CREATE TABLE public.nodes (
  28     id        SERIAL,
  29     typename  integer NOT NULL,
  30     user_id   integer NOT NULL,
  31     parent_id integer REFERENCES public.nodes(id) ON DELETE CASCADE ,
  32     name      character varying(255) DEFAULT ''::character varying NOT NULL,
  33     date      timestamp with time zone DEFAULT now() NOT NULL,
  34     hyperdata jsonb DEFAULT '{}'::jsonb NOT NULL,
  35     search tsvector,
  36     PRIMARY KEY (id),
  37     FOREIGN KEY (user_id)  REFERENCES public.auth_user(id) ON DELETE CASCADE
  38 );
  39 ALTER TABLE public.nodes OWNER TO gargantua;
  40
  41
  42
  43 CREATE TABLE public.ngrams (
  44     id SERIAL,
  45     terms character varying(255),
  46     n integer,
  47     PRIMARY KEY (id)
  48 );
  49 ALTER TABLE public.ngrams OWNER TO gargantua;
  50
  51 -- TODO: delete ID
  52 CREATE TABLE public.nodes_ngrams (
  53     id SERIAL,
  54     node_id integer NOT NULL,
  55     ngram_id integer NOT NULL,
  56     weight double precision,
  57     ngrams_type integer,
  58     FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
  59     FOREIGN KEY (ngram_id) REFERENCES public.ngrams(id) ON DELETE CASCADE,
  60     PRIMARY KEY (node_id,ngram_id)
  61 );
  62 ALTER TABLE public.nodes_ngrams OWNER TO gargantua;
  63
  64 --
  65 -- Name: nodes_ngrams_ngrams; Type: TABLE; Schema: public; Owner: gargantua
  66 --
  67
  68 CREATE TABLE public.nodes_ngrams_ngrams (
  69     node_id integer NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
  70     ngram1_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
  71     ngram2_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
  72     weight double precision,
  73     PRIMARY KEY (node_id,ngram1_id,ngram2_id)
  74 );
  75
  76 ALTER TABLE public.nodes_ngrams_ngrams OWNER TO gargantua;
  77
  78
  79 CREATE TABLE public.nodes_nodes (
  80     node1_id integer NOT NULL,
  81     node2_id integer NOT NULL,
  82     score real,
  83     favorite boolean,
  84     delete boolean,
  85     PRIMARY KEY (node1_id, node2_id)
  86 );
  87 ALTER TABLE public.nodes_nodes OWNER TO gargantua;
  88
  89
  90
  91 -- INDEXES
  92 CREATE UNIQUE INDEX ON public.auth_user(username);
  93
  94 CREATE INDEX auth_user_username_like ON public.auth_user USING btree (username varchar_pattern_ops);
  95
  96 --CREATE INDEX ix_nodes_typename ON public.nodes USING btree (typename);
  97 --CREATE INDEX ngrams_n_idx    ON public.ngrams USING btree (n);
  98 CREATE INDEX nodes_hyperdata_idx ON public.nodes USING gin (hyperdata);
  99 CREATE UNIQUE INDEX nodes_expr_idx ON public.nodes USING btree (((hyperdata ->> 'uniqId'::text)));
 100
 101 CREATE UNIQUE INDEX nodes_expr_idx2 ON public.nodes USING btree (((hyperdata ->> 'uniqIdBdd'::text)));
 102 CREATE UNIQUE INDEX nodes_typename_parent_id_expr_idx ON public.nodes USING btree (typename, parent_id, ((hyperdata ->> 'uniqId'::text)));
 103 CREATE INDEX nodes_user_id_typename_parent_id_idx ON public.nodes USING btree (user_id, typename, parent_id);
 104
 105
 106 CREATE UNIQUE INDEX ON public.ngrams(terms);
 107 --CREATE UNIQUE INDEX ON public.ngrams(terms,n);
 108
 109 CREATE INDEX nodes_ngrams_ngram_id_idx ON public.nodes_ngrams USING btree (ngram_id);
 110 CREATE INDEX nodes_ngrams_ngrams_node_id_idx ON public.nodes_ngrams_ngrams USING btree (node_id);
 111 CREATE UNIQUE INDEX ON public.nodes_ngrams USING btree (node_id,ngram_id);
 112 CREATE INDEX nodes_nodes_delete ON public.nodes_nodes USING btree (node1_id, node2_id, delete);
 113 CREATE UNIQUE INDEX nodes_nodes_node1_id_node2_id_idx ON public.nodes_nodes USING btree (node1_id, node2_id);
 114
 115 -- TRIGGERS
 116 -- TODO user haskell-postgresql-simple to create this function
 117 -- with rights typename
 118 CREATE OR REPLACE FUNCTION public.search_update()
 119 RETURNS trigger AS $$
 120 begin
 121   IF new.typename = 4 AND new.hyperdata @> '{"language_iso2":"EN"}' THEN
 122     new.search := to_tsvector( 'english' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
 123
 124   ELSIF new.typename = 4 AND new.hyperdata @> '{"language_iso2":"FR"}' THEN
 125     new.search := to_tsvector( 'french' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
 126
 127   ELSIF new.typename = 41 THEN
 128     new.search := to_tsvector( 'french' , (new.hyperdata ->> 'prenom')
 129                                  || ' ' || (new.hyperdata ->> 'nom')
 130                                  || ' ' || (new.hyperdata ->> 'fonction')
 131                              );
 132   ELSE
 133     new.search := to_tsvector( 'english' , new.name);
 134   END IF;
 135   return new;
 136 end
 137 $$ LANGUAGE plpgsql;
 138
 139 ALTER FUNCTION public.search_update() OWNER TO gargantua;
 140
 141 CREATE TRIGGER search_update_trigger BEFORE INSERT OR UPDATE ON nodes FOR EACH ROW EXECUTE PROCEDURE search_update();
 142
 143
 144 -- Initialize index with already existing data
 145 UPDATE nodes SET hyperdata = hyperdata;
 146