devops/postgres/schema.sql

   1 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
   2 CREATE EXTENSION IF NOT EXISTS tsm_system_rows;
   3
   4 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
   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
  27 -- TODO add publication_date
  28 -- TODO typename -> type_id
  29 CREATE TABLE public.nodes (
  30     id        SERIAL,
  31     typename  integer NOT NULL,
  32     user_id   integer NOT NULL,
  33     parent_id integer REFERENCES public.nodes(id) ON DELETE CASCADE ,
  34     name      character varying(255) DEFAULT ''::character varying NOT NULL,
  35     date      timestamp with time zone DEFAULT now() NOT NULL,
  36     hyperdata jsonb DEFAULT '{}'::jsonb NOT NULL,
  37     search tsvector,
  38     PRIMARY KEY (id),
  39     FOREIGN KEY (user_id)  REFERENCES public.auth_user(id) ON DELETE CASCADE
  40 );
  41 ALTER TABLE public.nodes OWNER TO gargantua;
  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 --------------------------------------------------------------
  53 -- TODO: delete delete this table
  54 --CREATE TABLE public.nodes_ngrams (
  55 --    id SERIAL,
  56 --    node_id integer NOT NULL,
  57 --    ngrams_id integer NOT NULL,
  58 --    parent_id integer REFERENCES public.nodes_ngrams(id) ON DELETE SET NULL,
  59 --    ngrams_type integer,
  60 --    list_type integer,
  61 --    weight double precision,
  62 --    FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
  63 --    FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE,
  64 --    PRIMARY KEY (id)
  65 --);
  66 --ALTER TABLE public.nodes_ngrams OWNER TO gargantua;
  67 --------------------------------------------------------------
  68
  69 --------------------------------------------------------------
  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 REFERENCES public.nodes(id) ON DELETE CASCADE,
  85     node2_id integer NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
  86     score real,
  87     category integer,
  88     PRIMARY KEY (node1_id,node2_id)
  89 );
  90 ALTER TABLE public.nodes_nodes OWNER TO gargantua;
  91
  92 ---------------------------------------------------------------
  93 -- TODO should reference "id" of nodes_nodes (instead of node1_id, node2_id)
  94 CREATE TABLE public.node_node_ngrams (
  95 id SERIAL,
  96 node1_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
  97 node2_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
  98 ngrams_id  INTEGER NOT NULL REFERENCES public.ngrams (id) ON DELETE CASCADE,
  99 ngrams_type INTEGER,
 100 weight double precision,
 101 PRIMARY KEY (id)
 102 );
 103 ALTER TABLE public.node_node_ngrams OWNER TO gargantua;
 104 --------------------------------------------------------------
 105
 106 --CREATE TABLE public.nodes_ngrams_repo (
 107 --    version integer NOT NULL,
 108 --    patches jsonb DEFAULT '{}'::jsonb NOT NULL,
 109 --    PRIMARY KEY (version)
 110 --);
 111 --ALTER TABLE public.nodes_ngrams_repo OWNER TO gargantua;
 112
 113 ---------------------------------------------------------
 114
 115 -- If needed for rights management at row level
 116 -- CREATE EXTENSION IF NOT EXISTS acl WITH SCHEMA public;
 117
 118 CREATE TABLE public.rights (
 119   user_id INTEGER NOT NULL REFERENCES public.auth_user(id) ON DELETE CASCADE,
 120   node_id INTEGER NOT NULL REFERENCES public.nodes(id)     ON DELETE CASCADE,
 121   rights  INTEGER NOT NULL,
 122   PRIMARY KEY (user_id, node_id)
 123 );
 124 ALTER TABLE public.rights OWNER TO gargantua;
 125
 126
 127 ------------------------------------------------------------
 128 -- INDEXES
 129
 130 CREATE INDEX        ON public.auth_user USING btree (username varchar_pattern_ops);
 131 CREATE UNIQUE INDEX ON public.auth_user USING btree (username);
 132
 133 CREATE INDEX        ON public.rights USING btree (user_id,node_id);
 134
 135 CREATE INDEX        ON public.nodes USING gin (hyperdata);
 136 CREATE INDEX        ON public.nodes USING btree (user_id, typename, parent_id);
 137 CREATE INDEX        ON public.nodes USING btree (typename, id);
 138 CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqId'::text)));
 139 CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqIdBdd'::text)));
 140 CREATE UNIQUE INDEX ON public.nodes USING btree (typename, parent_id, ((hyperdata ->> 'uniqId'::text)));
 141
 142 CREATE UNIQUE INDEX ON public.ngrams (terms); -- TEST GIN
 143 CREATE        INDEX ON public.ngrams USING btree (id, terms);
 144
 145 CREATE INDEX        ON public.nodes_nodes  USING btree (node1_id, node2_id, category);
 146 CREATE UNIQUE INDEX ON public.nodes_nodes  USING btree (node1_id, node2_id);
 147
 148 CREATE UNIQUE INDEX ON public.node_node_ngrams USING btree (node1_id, node2_id, ngrams_id, ngrams_type);
 149 CREATE        INDEX ON public.node_node_ngrams USING btree (node1_id,  node2_id);
 150 CREATE        INDEX ON public.node_node_ngrams USING btree (ngrams_id, node2_id);
 151
 152 -- TRIGGERS
 153 -- TODO user haskell-postgresql-simple to create this function
 154 -- with rights typename
 155 CREATE OR REPLACE FUNCTION public.search_update()
 156 RETURNS trigger AS $$
 157 begin
 158   IF new.typename = 4 AND new.hyperdata @> '{"language_iso2":"EN"}' THEN
 159     new.search := to_tsvector( 'english' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
 160
 161   ELSIF new.typename = 4 AND new.hyperdata @> '{"language_iso2":"FR"}' THEN
 162     new.search := to_tsvector( 'french' , (new.hyperdata ->> 'title') || ' ' || (new.hyperdata ->> 'abstract'));
 163
 164   ELSIF new.typename = 41 THEN
 165     new.search := to_tsvector( 'french' , (new.hyperdata ->> 'prenom')
 166                                  || ' ' || (new.hyperdata ->> 'nom')
 167                                  || ' ' || (new.hyperdata ->> 'fonction')
 168                              );
 169   ELSE
 170     new.search := to_tsvector( 'english' , new.name);
 171   END IF;
 172   return new;
 173 end
 174 $$ LANGUAGE plpgsql;
 175
 176 ALTER FUNCTION public.search_update() OWNER TO gargantua;
 177
 178 CREATE TRIGGER search_update_trigger BEFORE INSERT OR UPDATE ON nodes FOR EACH ROW EXECUTE PROCEDURE search_update();
 179
 180 -- Ngrams Full DB Extraction Optim
 181 -- TODO remove hard parameter
 182 CREATE OR REPLACE function node_pos(int, int) returns bigint
 183    AS 'SELECT count(id) from nodes
 184       WHERE  id < $1
 185       AND typename = $2
 186       '
 187    LANGUAGE SQL immutable;
 188
 189 --drop index node_by_pos;
 190 create index node_by_pos on nodes using btree(node_pos(id,typename));
 191
 192 -- Initialize index with already existing data
 193 UPDATE nodes SET hyperdata = hyperdata;
 194
 195
 196