Open-ILS/src/sql/Pg/008.schema.sql.sql

   1 -- Script to create the sql schema and the tables therein
   2
   3 BEGIN;
   4
   5 DROP SCHEMA IF EXISTS sql CASCADE;
   6 CREATE SCHEMA sql;
   7 COMMENT ON SCHEMA sql is $$
   8 /*
   9  * Copyright (C) 2009  Equinox Software, Inc. / Georgia Public Library Service
  10  * Scott McKellar <scott@esilibrary.com>
  11  *
  12  * Schema: sql
  13  *
  14  * Contains tables designed to represent SQL queries for use in
  15  * reports and the like.
  16  *
  17  * ****
  18  *
  19  * This program is free software; you can redistribute it and/or
  20  * modify it under the terms of the GNU General Public License
  21  * as published by the Free Software Foundation; either version 2
  22  * of the License, or (at your option) any later version.
  23  *
  24  * This program is distributed in the hope that it will be useful,
  25  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  26  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  27  * GNU General Public License for more details.
  28  */
  29 $$;
  30
  31 CREATE TABLE  sql.stored_query (
  32         id            SERIAL         PRIMARY KEY,
  33         type          TEXT           NOT NULL CONSTRAINT query_type CHECK
  34                                      ( type IN ( 'SELECT', 'UNION', 'INTERSECT', 'EXCEPT' ) ),
  35         use_all       BOOLEAN        NOT NULL DEFAULT FALSE,
  36         use_distinct  BOOLEAN        NOT NULL DEFAULT FALSE,
  37         from_clause   INT            NOT NULL , --REFERENCES sql.from_clause
  38         where_clause  INT            , --REFERENCES sql.expression
  39                                      --DEFERRABLE INITIALLY DEFERRED,
  40         having_clause INT            --REFERENCES sql.expression
  41                                      --DEFERRABLE INITIALLY DEFERRED
  42 );
  43
  44 -- (Foreign keys to be defined later after other tables are created)
  45
  46 CREATE TABLE sql.query_sequence (
  47         id              SERIAL            PRIMARY KEY,
  48         parent_query    INT               NOT NULL
  49                                           REFERENCES sql.stored_query
  50                                                                           ON DELETE CASCADE
  51                                                                           DEFERRABLE INITIALLY DEFERRED,
  52         seq_no          INT               NOT NULL,
  53         child_query     INT               NOT NULL
  54                                           REFERENCES sql.stored_query
  55                                                                           ON DELETE CASCADE
  56                                                                           DEFERRABLE INITIALLY DEFERRED,
  57         CONSTRAINT sql_query_seq UNIQUE( parent_query, seq_no )
  58 );
  59
  60 CREATE TABLE sql.datatype (
  61         id              SERIAL            PRIMARY KEY,
  62         datatype_name   TEXT              NOT NULL UNIQUE,
  63         is_numeric      BOOL              NOT NULL DEFAULT FALSE,
  64         is_composite    BOOL              NOT NULL DEFAULT FALSE,
  65         CONSTRAINT qdt_comp_not_num CHECK
  66         ( is_numeric IS FALSE OR is_composite IS FALSE )
  67 );
  68
  69 CREATE TABLE sql.subfield (
  70         id              SERIAL            PRIMARY KEY,
  71         composite_type  INT               NOT NULL
  72                                           REFERENCES sql.datatype(id)
  73                                           ON DELETE CASCADE
  74                                           DEFERRABLE INITIALLY DEFERRED,
  75         seq_no          INT               NOT NULL
  76                                           CONSTRAINT qsf_pos_seq_no
  77                                           CHECK( seq_no > 0 ),
  78         subfield_type   INT               NOT NULL
  79                                           REFERENCES sql.datatype(id)
  80                                           DEFERRABLE INITIALLY DEFERRED,
  81         CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
  82 );
  83
  84 CREATE TABLE sql.function_sig (
  85         id              SERIAL            PRIMARY KEY,
  86         function_name   TEXT              NOT NULL,
  87         return_type     INT               REFERENCES sql.datatype(id)
  88                                           DEFERRABLE INITIALLY DEFERRED,
  89         is_aggregate    BOOL              NOT NULL DEFAULT FALSE,
  90         CONSTRAINT qfd_rtn_or_aggr CHECK
  91         ( return_type IS NULL OR is_aggregate = FALSE )
  92 );
  93
  94 CREATE INDEX sql_function_sig_name_idx
  95         ON sql.function_sig (function_name);
  96
  97 CREATE TABLE sql.function_param_def (
  98         id              SERIAL            PRIMARY KEY,
  99         function_id     INT               NOT NULL
 100                                           REFERENCES sql.function_sig( id )
 101                                           ON DELETE CASCADE
 102                                           DEFERRABLE INITIALLY DEFERRED,
 103         seq_no          INT               NOT NULL
 104                                           CONSTRAINT qfpd_pos_seq_no CHECK
 105                                           ( seq_no > 0 ),
 106         datatype        INT               NOT NULL
 107                                           REFERENCES sql.datatype( id )
 108                                           DEFERRABLE INITIALLY DEFERRED,
 109         CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
 110 );
 111
 112 CREATE TABLE sql.expression (
 113         id            SERIAL        PRIMARY KEY,
 114         type          TEXT          NOT NULL CONSTRAINT predicate_type CHECK
 115                                     ( type IN (
 116                                         'xbet',    -- between
 117                                                                         'xbool',   -- boolean
 118                                         'xcase',   -- case
 119                                                                         'xcast',   -- cast
 120                                                                         'xcol',    -- column
 121                                                                         'xex',     -- exists
 122                                                                         'xfld',    -- field
 123                                                                         'xfunc',   -- function
 124                                                                         'xin',     -- in
 125                                                                         'xnbet',   -- not between
 126                                         'xnex',    -- not exists
 127                                                                         'xnin',    -- not in
 128                                         'xnull',   -- null
 129                                                                         'xnum',    -- number
 130                                                                         'xop',     -- operator
 131                                                                         'xstr',    -- string
 132                                                 'xsubq'    -- subquery
 133                                                                 ) ),
 134         parenthesize  BOOL          NOT NULL DEFAULT FALSE,
 135         parent_expr   INT           REFERENCES sql.expression
 136                                     ON DELETE CASCADE
 137                                     DEFERRABLE INITIALLY DEFERRED,
 138         seq_no        INT           NOT NULL DEFAULT 1,
 139         literal       TEXT,
 140         table_alias   TEXT,
 141         column_name   TEXT,
 142         left_operand  INT           REFERENCES sql.expression
 143                                     DEFERRABLE INITIALLY DEFERRED,
 144         operator      TEXT,
 145         right_operand INT           REFERENCES sql.expression
 146                                     DEFERRABLE INITIALLY DEFERRED,
 147         function_id   INT           REFERENCES sql.function_sig
 148                                     DEFERRABLE INITIALLY DEFERRED,
 149         subquery      INT           REFERENCES sql.stored_query
 150                                     DEFERRABLE INITIALLY DEFERRED,
 151         cast_type     INT           REFERENCES sql.datatype
 152                                     DEFERRABLE INITIALLY DEFERRED
 153 );
 154
 155 CREATE UNIQUE INDEX sql_expr_parent_seq
 156         ON sql.expression( parent_expr, seq_no )
 157         WHERE parent_expr IS NOT NULL;
 158
 159 -- Due to some circular references, the following foreign key definitions
 160 -- had to be deferred until sql.expression existed:
 161
 162 ALTER TABLE sql.stored_query
 163         ADD FOREIGN KEY ( where_clause )
 164         REFERENCES sql.expression( id )
 165         DEFERRABLE INITIALLY DEFERRED;
 166
 167 ALTER TABLE sql.stored_query
 168         ADD FOREIGN KEY ( having_clause )
 169         REFERENCES sql.expression( id )
 170         DEFERRABLE INITIALLY DEFERRED;
 171
 172 CREATE TABLE sql.case_branch (
 173         id            SERIAL        PRIMARY KEY,
 174         parent_expr   INT           NOT NULL REFERENCES sql.expression
 175                                     ON DELETE CASCADE
 176                                     DEFERRABLE INITIALLY DEFERRED,
 177         seq_no        INT           NOT NULL,
 178         condition     INT           REFERENCES sql.expression
 179                                     DEFERRABLE INITIALLY DEFERRED,
 180         result        INT           NOT NULL REFERENCES sql.expression
 181                                     DEFERRABLE INITIALLY DEFERRED,
 182         CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
 183 );
 184
 185 CREATE TABLE sql.from_relation (
 186         id               SERIAL        PRIMARY KEY,
 187         type             TEXT          NOT NULL CONSTRAINT relation_type CHECK (
 188                                            type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
 189         table_name       TEXT,
 190         class_name       TEXT,
 191         subquery         INT           REFERENCES sql.stored_query,
 192         function_call    INT           REFERENCES sql.expression,
 193         table_alias      TEXT          NOT NULL,
 194         parent_relation  INT           REFERENCES sql.from_relation
 195                                        ON DELETE CASCADE
 196                                        DEFERRABLE INITIALLY DEFERRED,
 197         seq_no           INT           NOT NULL DEFAULT 1,
 198         join_type        TEXT          CONSTRAINT good_join_type CHECK (
 199                                            join_type IS NULL OR join_type IN
 200                                            ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
 201                                        ),
 202         on_clause        INT           REFERENCES sql.expression
 203                                        DEFERRABLE INITIALLY DEFERRED,
 204         CONSTRAINT join_or_core CHECK (
 205             ( parent_relation IS NULL AND join_type IS NULL
 206               AND on_clause IS NULL and table_alias IS NULL )
 207             OR
 208             ( parent_relation IS NOT NULL AND join_type IS NOT NULL
 209               AND on_clause IS NOT NULL )
 210         )
 211 );
 212
 213 CREATE UNIQUE INDEX from_parent_seq
 214         ON sql.from_relation( parent_relation, seq_no )
 215         WHERE parent_relation IS NOT NULL;
 216
 217 -- The following foreign key had to be deferred until
 218 -- sql.from_relation existed
 219
 220 ALTER TABLE sql.stored_query
 221         ADD FOREIGN KEY (from_clause)
 222         REFERENCES sql.from_relation
 223         DEFERRABLE INITIALLY DEFERRED;
 224
 225 CREATE TABLE sql.record_column (
 226         id            SERIAL            PRIMARY KEY,
 227         from_relation INT               NOT NULL REFERENCES sql.from_relation
 228                                         ON DELETE CASCADE
 229                                         DEFERRABLE INITIALLY DEFERRED,
 230         seq_no        INT               NOT NULL,
 231         column_name   TEXT              NOT NULL,
 232         column_type   TEXT              NOT NULL,
 233         CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
 234 );
 235
 236 CREATE TABLE sql.select_item (
 237         id               SERIAL         PRIMARY KEY,
 238         stored_query     INT            NOT NULL REFERENCES sql.stored_query
 239                                         ON DELETE CASCADE
 240                                         DEFERRABLE INITIALLY DEFERRED,
 241         seq_no           INT            NOT NULL,
 242         expression       INT            NOT NULL REFERENCES sql.expression
 243                                         DEFERRABLE INITIALLY DEFERRED,
 244         column_alias     TEXT,
 245         grouped_by       BOOL           NOT NULL DEFAULT FALSE,
 246         CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
 247 );
 248
 249 CREATE TABLE sql.order_by_item (
 250         id               SERIAL         PRIMARY KEY,
 251         stored_query     INT            NOT NULL REFERENCES sql.stored_query
 252                                         ON DELETE CASCADE
 253                                         DEFERRABLE INITIALLY DEFERRED,
 254         seq_no           INT            NOT NULL,
 255         expression       INT            NOT NULL REFERENCES sql.expression
 256                                         ON DELETE CASCADE
 257                                         DEFERRABLE INITIALLY DEFERRED,
 258         CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
 259 );
 260
 261 COMMIT;