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
   4 -- CREATE USER WITH ...
   5 -- createdb "gargandb"
   6
   7 CREATE TABLE public.auth_user (
   8     id SERIAL,
   9     password     character varying(128) NOT NULL,
  10     last_login   timestamp with time zone,
  11     is_superuser boolean NOT NULL,
  12     username     character varying(150) NOT NULL,
  13     first_name   character varying(30) NOT NULL,
  14     last_name    character varying(30) NOT NULL,
  15     email        character varying(254) NOT NULL,
  16     is_staff     boolean NOT NULL,
  17     is_active    boolean NOT NULL,
  18     date_joined timestamp with time zone DEFAULT now() NOT NULL,
  19     PRIMARY KEY (id)
  20 );
  21
  22 ALTER TABLE public.auth_user OWNER TO gargantua;
  23
  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 CREATE TABLE public.ngrams (
  43     id SERIAL,
  44     terms character varying(255),
  45     n integer,
  46     PRIMARY KEY (id)
  47 );
  48 ALTER TABLE public.ngrams OWNER TO gargantua;
  49
  50 --------------------------------------------------------------
  51 --------------------------------------------------------------
  52 -- TODO: delete delete this table
  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 CREATE TABLE public.nodes_ngrams_repo (
  70     version integer NOT NULL,
  71     patches jsonb DEFAULT '{}'::jsonb NOT NULL,
  72     PRIMARY KEY (version)
  73 );
  74 ALTER TABLE public.nodes_ngrams_repo OWNER TO gargantua;
  75
  76 --------------------------------------------------------------
  77 --
  78 --
  79 -- TODO: delete delete this table
  80 CREATE TABLE public.nodes_ngrams_ngrams (
  81     node_id   integer NOT NULL REFERENCES public.nodes(id)  ON DELETE CASCADE,
  82     ngram1_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
  83     ngram2_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
  84     weight double precision,
  85     PRIMARY KEY (node_id,ngram1_id,ngram2_id)
  86 );
  87
  88 ALTER TABLE public.nodes_ngrams_ngrams OWNER TO gargantua;
  89
  90 ---------------------------------------------------------
  91 CREATE TABLE public.nodes_nodes (
  92     node1_id integer NOT NULL,
  93     node2_id integer NOT NULL,
  94     score real,
  95     favorite boolean,
  96     delete boolean,
  97     PRIMARY KEY (node1_id, node2_id)
  98 );
  99 ALTER TABLE public.nodes_nodes OWNER TO gargantua;
 100 ---------------------------------------------------------
 101
 102 -- If needed for rights management at row level
 103 -- CREATE EXTENSION IF NOT EXISTS acl WITH SCHEMA public;
 104
 105 CREATE TABLE public.rights (
 106   user_id INTEGER NOT NULL REFERENCES public.auth_user(id) ON DELETE CASCADE,
 107   node_id INTEGER NOT NULL REFERENCES public.nodes(id)     ON DELETE CASCADE,
 108   rights  INTEGER NOT NULL,
 109   PRIMARY KEY (user_id, node_id)
 110 );
 111 ALTER TABLE public.rights OWNER TO gargantua;
 112
 113 CREATE INDEX rights_userId_nodeId ON public.rights USING btree (user_id,node_id);
 114
 115
 116 ------------------------------------------------------------
 117 -- INDEXES
 118 CREATE UNIQUE INDEX ON public.auth_user(username);
 119
 120 CREATE INDEX auth_user_username_like ON public.auth_user USING btree (username varchar_pattern_ops);
 121
 122 --CREATE INDEX ix_nodes_typename ON public.nodes USING btree (typename);
 123 --CREATE INDEX ngrams_n_idx    ON public.ngrams USING btree (n);
 124 CREATE INDEX nodes_hyperdata_idx ON public.nodes USING gin (hyperdata);
 125 CREATE UNIQUE INDEX nodes_expr_idx ON public.nodes USING btree (((hyperdata ->> 'uniqId'::text)));
 126
 127 CREATE UNIQUE INDEX nodes_expr_idx2 ON public.nodes USING btree (((hyperdata ->> 'uniqIdBdd'::text)));
 128 CREATE UNIQUE INDEX nodes_typename_parent_id_expr_idx ON public.nodes USING btree (typename, parent_id, ((hyperdata ->> 'uniqId'::text)));
 129 CREATE INDEX nodes_user_id_typename_parent_id_idx ON public.nodes USING btree (user_id, typename, parent_id);
 130
 131 CREATE UNIQUE INDEX ON public.ngrams(terms);
 132 --CREATE UNIQUE INDEX ON public.ngrams(terms,n);
 133
 134 CREATE UNIQUE INDEX ON public.nodes_ngrams USING btree (node_id,ngrams_id);
 135 CREATE INDEX nodes_ngrams_ngrams_id_idx ON public.nodes_ngrams USING btree (ngrams_id);
 136 CREATE INDEX nodes_ngrams_ngrams_node_id_idx ON public.nodes_ngrams_ngrams USING btree (node_id);
 137 CREATE UNIQUE INDEX ON public.nodes_ngrams USING btree (node_id,ngrams_id,ngrams_type);
 138 CREATE INDEX nodes_nodes_delete ON public.nodes_nodes USING btree (node1_id, node2_id, delete);
 139 CREATE UNIQUE INDEX nodes_nodes_node1_id_node2_id_idx ON public.nodes_nodes USING btree (node1_id, node2_id);
 140
 141 -- TRIGGERS
 142 -- TODO user haskell-postgresql-simple to create this function
 143 -- with rights typename
 144 CREATE OR REPLACE FUNCTION public.search_update()
 145 RETURNS trigger AS $$
 146 begin
 147   IF new.typename = 4 AND new.hyperdata @> '{"language_iso2":"EN"}' THEN
 148     new.search := to_tsvector( 'english' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
 149
 150   ELSIF new.typename = 4 AND new.hyperdata @> '{"language_iso2":"FR"}' THEN
 151     new.search := to_tsvector( 'french' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
 152
 153   ELSIF new.typename = 41 THEN
 154     new.search := to_tsvector( 'french' , (new.hyperdata ->> 'prenom')
 155                                  || ' ' || (new.hyperdata ->> 'nom')
 156                                  || ' ' || (new.hyperdata ->> 'fonction')
 157                              );
 158   ELSE
 159     new.search := to_tsvector( 'english' , new.name);
 160   END IF;
 161   return new;
 162 end
 163 $$ LANGUAGE plpgsql;
 164
 165 ALTER FUNCTION public.search_update() OWNER TO gargantua;
 166
 167 CREATE TRIGGER search_update_trigger BEFORE INSERT OR UPDATE ON nodes FOR EACH ROW EXECUTE PROCEDURE search_update();
 168
 169
 170 -- Initialize index with already existing data
 171 UPDATE nodes SET hyperdata = hyperdata;
 172