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 COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
   4
   5 CREATE TABLE public.auth_user (
   6     id SERIAL,
   7     password     character varying(128) NOT NULL,
   8     last_login   timestamp with time zone,
   9     is_superuser boolean NOT NULL,
  10     username     character varying(150) NOT NULL,
  11     first_name   character varying(30) NOT NULL,
  12     last_name    character varying(30) NOT NULL,
  13     email        character varying(254) NOT NULL,
  14     is_staff     boolean NOT NULL,
  15     is_active    boolean NOT NULL,
  16     date_joined timestamp with time zone DEFAULT now() NOT NULL,
  17     PRIMARY KEY (id)
  18 );
  19
  20 ALTER TABLE public.auth_user OWNER TO gargantua;
  21
  22 -- TODO add publication_date
  23 -- TODO typename -> type_id
  24 CREATE TABLE public.nodes (
  25     id        SERIAL,
  26     typename  integer NOT NULL,
  27     user_id   integer NOT NULL,
  28     parent_id integer REFERENCES public.nodes(id) ON DELETE CASCADE ,
  29     name      character varying(255) DEFAULT ''::character varying NOT NULL,
  30     date      timestamp with time zone DEFAULT now() NOT NULL,
  31     hyperdata jsonb DEFAULT '{}'::jsonb NOT NULL,
  32     search tsvector,
  33     PRIMARY KEY (id),
  34     FOREIGN KEY (user_id)  REFERENCES public.auth_user(id) ON DELETE CASCADE
  35 );
  36 ALTER TABLE public.nodes OWNER TO gargantua;
  37
  38 CREATE TABLE public.ngrams (
  39     id SERIAL,
  40     terms character varying(255),
  41     n integer,
  42     PRIMARY KEY (id)
  43 );
  44 ALTER TABLE public.ngrams OWNER TO gargantua;
  45
  46 --------------------------------------------------------------
  47 CREATE TABLE public.node_ngrams (
  48     id SERIAL,
  49     node_id integer NOT NULL,
  50     node_subtype integer,
  51     ngrams_id integer NOT NULL,
  52     ngrams_type integer, -- change to ngrams_field? (no for pedagogic reason)
  53     ngrams_field integer,
  54     ngrams_tag integer,
  55     ngrams_class integer,
  56     weight double precision,
  57     PRIMARY KEY (id),
  58     FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
  59     FOREIGN KEY (ngrams_id) REFERENCES public.ngrams(id) ON DELETE CASCADE
  60 );
  61 ALTER TABLE public.node_ngrams OWNER TO gargantua;
  62
  63 CREATE TABLE public.node_ngrams_ngrams (
  64     node_id integer NOT NULL,
  65     node_ngrams1_id integer NOT NULL,
  66     node_ngrams2_id integer NOT NULL,
  67     FOREIGN KEY (node_id) REFERENCES public.nodes(id) ON DELETE CASCADE,
  68     FOREIGN KEY (node_ngrams1_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
  69     FOREIGN KEY (node_ngrams2_id) REFERENCES public.node_ngrams(id) ON DELETE CASCADE,
  70     PRIMARY KEY (node_id, node_ngrams1_id, node_ngrams2_id)
  71 );
  72 ALTER TABLE public.node_ngrams_ngrams OWNER TO gargantua;
  73
  74 --------------------------------------------------------------
  75 --------------------------------------------------------------
  76 --
  77 --
  78 --CREATE TABLE public.nodes_ngrams_ngrams (
  79 --    node_id   integer NOT NULL REFERENCES public.nodes(id)  ON DELETE CASCADE,
  80 --    ngram1_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
  81 --    ngram2_id integer NOT NULL REFERENCES public.ngrams(id) ON DELETE CASCADE,
  82 --    weight double precision,
  83 --    PRIMARY KEY (node_id,ngram1_id,ngram2_id)
  84 --);
  85 --
  86 --ALTER TABLE public.nodes_ngrams_ngrams OWNER TO gargantua;
  87 ---------------------------------------------------------------
  88 -- TODO nodes_nodes(node1_id int, node2_id int, edge_type int , weight real)
  89 CREATE TABLE public.nodes_nodes (
  90     node1_id integer NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
  91     node2_id integer NOT NULL REFERENCES public.nodes(id) ON DELETE CASCADE,
  92     score real,
  93     category integer,
  94     PRIMARY KEY (node1_id,node2_id)
  95 );
  96 ALTER TABLE public.nodes_nodes OWNER TO gargantua;
  97
  98 ---------------------------------------------------------------
  99 -- TODO should reference "id" of nodes_nodes (instead of node1_id, node2_id)
 100 CREATE TABLE public.node_node_ngrams (
 101 node1_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 102 -- here id to node_ngrams
 103 node2_id   INTEGER NOT NULL REFERENCES public.nodes  (id) ON DELETE CASCADE,
 104 ngrams_id  INTEGER NOT NULL REFERENCES public.ngrams (id) ON DELETE CASCADE,
 105 ngrams_type INTEGER,
 106 --ngrams_tag INTEGER,
 107 --ngrams_class INTEGER,
 108 weight double precision,
 109 PRIMARY KEY (node1_id, node2_id, ngrams_id, ngrams_type)
 110 );
 111 ALTER TABLE public.node_node_ngrams OWNER TO gargantua;
 112 --------------------------------------------------------------
 113
 114 --CREATE TABLE public.nodes_ngrams_repo (
 115 --    version integer NOT NULL,
 116 --    patches jsonb DEFAULT '{}'::jsonb NOT NULL,
 117 --    PRIMARY KEY (version)
 118 --);
 119 --ALTER TABLE public.nodes_ngrams_repo OWNER TO gargantua;
 120
 121 ---------------------------------------------------------
 122
 123 -- If needed for rights management at row level
 124 -- CREATE EXTENSION IF NOT EXISTS acl WITH SCHEMA public;
 125
 126 CREATE TABLE public.rights (
 127   user_id INTEGER NOT NULL REFERENCES public.auth_user(id) ON DELETE CASCADE,
 128   node_id INTEGER NOT NULL REFERENCES public.nodes(id)     ON DELETE CASCADE,
 129   rights  INTEGER NOT NULL,
 130   PRIMARY KEY (user_id, node_id)
 131 );
 132 ALTER TABLE public.rights OWNER TO gargantua;
 133
 134 ------------------------------------------------------------
 135 -- INDEXES
 136
 137 CREATE INDEX        ON public.auth_user USING btree (username varchar_pattern_ops);
 138 CREATE UNIQUE INDEX ON public.auth_user USING btree (username);
 139
 140 CREATE INDEX        ON public.rights USING btree (user_id,node_id);
 141
 142 CREATE INDEX        ON public.nodes USING gin (hyperdata);
 143 CREATE INDEX        ON public.nodes USING btree (user_id, typename, parent_id);
 144 CREATE INDEX        ON public.nodes USING btree (typename, id);
 145 CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqId'::text)));
 146 CREATE UNIQUE INDEX ON public.nodes USING btree (((hyperdata ->> 'uniqIdBdd'::text)));
 147 CREATE UNIQUE INDEX ON public.nodes USING btree (typename, parent_id, ((hyperdata ->> 'uniqId'::text)));
 148
 149 CREATE UNIQUE INDEX ON public.ngrams (terms); -- TEST GIN
 150 CREATE        INDEX ON public.ngrams USING btree (id, terms);
 151 CREATE UNIQUE INDEX ON public.node_ngrams USING btree (node_id,node_subtype, ngrams_id);
 152
 153 CREATE INDEX        ON public.nodes_nodes  USING btree (node1_id, node2_id, category);
 154 CREATE UNIQUE INDEX ON public.nodes_nodes  USING btree (node1_id, node2_id);
 155
 156 CREATE UNIQUE INDEX ON public.node_node_ngrams USING btree (node1_id, node2_id, ngrams_id, ngrams_type);
 157 CREATE        INDEX ON public.node_node_ngrams USING btree (node1_id,  node2_id);
 158 CREATE        INDEX ON public.node_node_ngrams USING btree (ngrams_id, node2_id);
 159
 160
 161 ------------------------------------------------------------------------
 162 -- Ngrams Full DB Extraction Optim
 163 -- TODO remove hard parameter and move elsewhere
 164 CREATE OR REPLACE function node_pos(int, int) returns bigint
 165    AS 'SELECT count(id) from nodes
 166       WHERE  id < $1
 167       AND typename = $2
 168       '
 169    LANGUAGE SQL immutable;
 170
 171 --drop index node_by_pos;
 172 create index node_by_pos on nodes using btree(node_pos(id,typename));
 173