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 CREATE TABLE public.node_ngrams (
  54     id SERIAL,
  55     node_id integer NOT NULL,
  56     ngrams_id integer NOT NULL,
  57     list_type integer,
  58     ngrams_type integer, -- change to ngrams_field? (no for pedagogic reason)
  59     ngrams_field integer,
  60     ngrams_tag integer,
  61     ngrams_class integer,
  62     weight double precision,
  63     FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
  64     FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE,
  65     PRIMARY KEY (id)
  66 );
  67 ALTER TABLE public.node_ngrams OWNER TO gargantua;
  68
  69
  70 CREATE TABLE public.node_node_ngrams_ngrams (
  71     node_id integer NOT NULL,
  72     node_ngrams1_id integer NOT NULL,
  73     node_ngrams2_id integer NOT NULL,
  74     FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
  75     FOREIGN KEY (node_ngrams1_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
  76     FOREIGN KEY (node_ngrams2_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
  77     PRIMARY KEY (node_id, node_ngrams1_id, node_ngrams2_id)
  78 );
  79 ALTER TABLE public.node_node_ngrams_ngrams OWNER TO gargantua;
  80
  81 --------------------------------------------------------------
  82 --------------------------------------------------------------
  83 --
  84 --
  85 --CREATE TABLE public.nodes_ngrams_ngrams (
  86 --    node_id   integer NOT NULL REFERENCES public.nodes(id)  ON DELETE CASCADE,
  87 --    ngram1_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
  88 --    ngram2_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
  89 --    weight double precision,
  90 --    PRIMARY KEY (node_id,ngram1_id,ngram2_id)
  91 --);
  92 --
  93 --ALTER TABLE public.nodes_ngrams_ngrams OWNER TO gargantua;
  94 ---------------------------------------------------------------
  95 -- TODO nodes_nodes(node1_id int, node2_id int, edge_type int , weight real)
  96 CREATE TABLE public.nodes_nodes (
  97     node1_id integer NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
  98     node2_id integer NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
  99     score real,
 100     category integer,
 101     PRIMARY KEY (node1_id,node2_id)
 102 );
 103 ALTER TABLE public.nodes_nodes OWNER TO gargantua;
 104
 105 ---------------------------------------------------------------
 106 -- TODO should reference "id" of nodes_nodes (instead of node1_id, node2_id)
 107 CREATE TABLE public.node_node_ngrams (
 108 node1_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 109 -- here id to node_ngrams
 110 node2_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 111 ngrams_id  INTEGER NOT NULL REFERENCES public.ngrams (id) ON DELETE CASCADE,
 112 ngrams_type INTEGER,
 113 --ngrams_tag INTEGER,
 114 --ngrams_class INTEGER,
 115 weight double precision,
 116 PRIMARY KEY (node1_id, node2_id, ngrams_id, ngrams_type)
 117 );
 118 ALTER TABLE public.node_node_ngrams OWNER TO gargantua;
 119 --------------------------------------------------------------
 120
 121 --CREATE TABLE public.nodes_ngrams_repo (
 122 --    version integer NOT NULL,
 123 --    patches jsonb DEFAULT '{}'::jsonb NOT NULL,
 124 --    PRIMARY KEY (version)
 125 --);
 126 --ALTER TABLE public.nodes_ngrams_repo OWNER TO gargantua;
 127
 128 ---------------------------------------------------------
 129
 130 -- If needed for rights management at row level
 131 -- CREATE EXTENSION IF NOT EXISTS acl WITH SCHEMA public;
 132
 133 CREATE TABLE public.rights (
 134   user_id INTEGER NOT NULL REFERENCES public.auth_user(id) ON DELETE CASCADE,
 135   node_id INTEGER NOT NULL REFERENCES public.nodes(id)     ON DELETE CASCADE,
 136   rights  INTEGER NOT NULL,
 137   PRIMARY KEY (user_id, node_id)
 138 );
 139 ALTER TABLE public.rights OWNER TO gargantua;
 140
 141 ------------------------------------------------------------
 142 -- INDEXES
 143
 144 CREATE INDEX        ON public.auth_user USING btree (username varchar_pattern_ops);
 145 CREATE UNIQUE INDEX ON public.auth_user USING btree (username);
 146
 147 CREATE INDEX        ON public.rights USING btree (user_id,node_id);
 148
 149 CREATE INDEX        ON public.nodes USING gin (hyperdata);
 150 CREATE INDEX        ON public.nodes USING btree (user_id, typename, parent_id);
 151 CREATE INDEX        ON public.nodes USING btree (typename, id);
 152 CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqId'::text)));
 153 CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqIdBdd'::text)));
 154 CREATE UNIQUE INDEX ON public.nodes USING btree (typename, parent_id, ((hyperdata ->> 'uniqId'::text)));
 155
 156 CREATE UNIQUE INDEX ON public.ngrams (terms); -- TEST GIN
 157 CREATE        INDEX ON public.ngrams USING btree (id, terms);
 158
 159 CREATE INDEX        ON public.nodes_nodes  USING btree (node1_id, node2_id, category);
 160 CREATE UNIQUE INDEX ON public.nodes_nodes  USING btree (node1_id, node2_id);
 161
 162 CREATE UNIQUE INDEX ON public.node_node_ngrams USING btree (node1_id, node2_id, ngrams_id, ngrams_type);
 163 CREATE        INDEX ON public.node_node_ngrams USING btree (node1_id,  node2_id);
 164 CREATE        INDEX ON public.node_node_ngrams USING btree (ngrams_id, node2_id);
 165
 166
 167 ------------------------------------------------------------------------
 168 -- Ngrams Full DB Extraction Optim
 169 -- TODO remove hard parameter and move elsewhere
 170 CREATE OR REPLACE function node_pos(int, int) returns bigint
 171    AS 'SELECT count(id) from nodes
 172       WHERE  id < $1
 173       AND typename = $2
 174       '
 175    LANGUAGE SQL immutable;
 176
 177 --drop index node_by_pos;
 178 create index node_by_pos on nodes using btree(node_pos(id,typename));
 179