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