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

   1 -- Script to create the query schema and the tables therein
   2
   3 BEGIN;
   4
   5 DROP SCHEMA IF EXISTS query CASCADE;
   6 CREATE SCHEMA query;
   7 COMMENT ON SCHEMA actor IS $$
   8 /*
   9  * Copyright (C) 2009  Equinox Software, Inc. / Georgia Public Library Service
  10  * Scott McKellar <scott@esilibrary.com>
  11  *
  12  * Schema: query
  13  *
  14  * Contains tables designed to represent user-defined queries for
  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  query.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 query.from_clause
  38         where_clause  INT            , --REFERENCES query.expression
  39                                      --DEFERRABLE INITIALLY DEFERRED,
  40         having_clause INT            --REFERENCES query.expression
  41                                      --DEFERRABLE INITIALLY DEFERRED
  42 );
  43
  44 -- (Foreign keys to be defined later after other tables are created)
  45
  46 CREATE TABLE query.query_sequence (
  47         id              SERIAL            PRIMARY KEY,
  48         parent_query    INT               NOT NULL
  49                                           REFERENCES query.stored_query
  50                                                                           ON DELETE CASCADE
  51                                                                           DEFERRABLE INITIALLY DEFERRED,
  52         seq_no          INT               NOT NULL,
  53         child_query     INT               NOT NULL
  54                                           REFERENCES query.stored_query
  55                                                                           ON DELETE CASCADE
  56                                                                           DEFERRABLE INITIALLY DEFERRED,
  57         CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no )
  58 );
  59
  60 CREATE TABLE query.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 query.subfield (
  70         id              SERIAL            PRIMARY KEY,
  71         composite_type  INT               NOT NULL
  72                                           REFERENCES query.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 query.datatype(id)
  80                                           DEFERRABLE INITIALLY DEFERRED,
  81         CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
  82 );
  83
  84 CREATE TABLE query.function_sig (
  85         id              SERIAL            PRIMARY KEY,
  86         function_name   TEXT              NOT NULL,
  87         return_type     INT               REFERENCES query.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 query_function_sig_name_idx
  95         ON query.function_sig (function_name);
  96
  97 CREATE TABLE query.function_param_def (
  98         id              SERIAL            PRIMARY KEY,
  99         function_id     INT               NOT NULL
 100                                           REFERENCES query.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 query.datatype( id )
 108                                           DEFERRABLE INITIALLY DEFERRED,
 109         CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
 110 );
 111
 112 CREATE TABLE query.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 query.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 query.expression
 143                                     DEFERRABLE INITIALLY DEFERRED,
 144         operator      TEXT,
 145         right_operand INT           REFERENCES query.expression
 146                                     DEFERRABLE INITIALLY DEFERRED,
 147         function_id   INT           REFERENCES query.function_sig
 148                                     DEFERRABLE INITIALLY DEFERRED,
 149         subquery      INT           REFERENCES query.stored_query
 150                                     DEFERRABLE INITIALLY DEFERRED,
 151         cast_type     INT           REFERENCES query.datatype
 152                                     DEFERRABLE INITIALLY DEFERRED
 153 );
 154
 155 CREATE UNIQUE INDEX query_expr_parent_seq
 156         ON query.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 query.expression existed:
 161
 162 ALTER TABLE query.stored_query
 163         ADD FOREIGN KEY ( where_clause )
 164         REFERENCES query.expression( id )
 165         DEFERRABLE INITIALLY DEFERRED;
 166
 167 ALTER TABLE query.stored_query
 168         ADD FOREIGN KEY ( having_clause )
 169         REFERENCES query.expression( id )
 170         DEFERRABLE INITIALLY DEFERRED;
 171
 172 CREATE TABLE query.case_branch (
 173         id            SERIAL        PRIMARY KEY,
 174         parent_expr   INT           NOT NULL REFERENCES query.expression
 175                                     ON DELETE CASCADE
 176                                     DEFERRABLE INITIALLY DEFERRED,
 177         seq_no        INT           NOT NULL,
 178         condition     INT           REFERENCES query.expression
 179                                     DEFERRABLE INITIALLY DEFERRED,
 180         result        INT           NOT NULL REFERENCES query.expression
 181                                     DEFERRABLE INITIALLY DEFERRED,
 182         CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
 183 );
 184
 185 CREATE TABLE query.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 query.stored_query,
 192         function_call    INT           REFERENCES query.expression,
 193         table_alias      TEXT          NOT NULL,
 194         parent_relation  INT           REFERENCES query.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 query.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 query.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 -- query.from_relation existed
 219
 220 ALTER TABLE query.stored_query
 221         ADD FOREIGN KEY (from_clause)
 222         REFERENCES query.from_relation
 223         DEFERRABLE INITIALLY DEFERRED;
 224
 225 CREATE TABLE query.record_column (
 226         id            SERIAL            PRIMARY KEY,
 227         from_relation INT               NOT NULL REFERENCES query.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   INT               NOT NULL REFERENCES query.datatype
 233                                     ON DELETE CASCADE
 234                                     DEFERRABLE INITIALLY DEFERRED,
 235         CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
 236 );
 237
 238 CREATE TABLE query.select_item (
 239         id               SERIAL         PRIMARY KEY,
 240         stored_query     INT            NOT NULL REFERENCES query.stored_query
 241                                         ON DELETE CASCADE
 242                                         DEFERRABLE INITIALLY DEFERRED,
 243         seq_no           INT            NOT NULL,
 244         expression       INT            NOT NULL REFERENCES query.expression
 245                                         DEFERRABLE INITIALLY DEFERRED,
 246         column_alias     TEXT,
 247         grouped_by       BOOL           NOT NULL DEFAULT FALSE,
 248         CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
 249 );
 250
 251 CREATE TABLE query.order_by_item (
 252         id               SERIAL         PRIMARY KEY,
 253         stored_query     INT            NOT NULL REFERENCES query.stored_query
 254                                         ON DELETE CASCADE
 255                                         DEFERRABLE INITIALLY DEFERRED,
 256         seq_no           INT            NOT NULL,
 257         expression       INT            NOT NULL REFERENCES query.expression
 258                                         ON DELETE CASCADE
 259                                         DEFERRABLE INITIALLY DEFERRED,
 260         CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
 261 );
 262
 263 -- Create updatable views
 264
 265 CREATE OR REPLACE VIEW query.expr_string AS
 266     SELECT
 267         id,
 268         parenthesize,
 269         parent_expr,
 270         seq_no,
 271         literal
 272     FROM
 273         query.expression
 274     WHERE
 275         type = 'xstr';
 276
 277 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
 278     ON INSERT TO query.expr_string
 279     DO INSTEAD
 280     INSERT INTO query.expression (
 281         id,
 282         type,
 283         parenthesize,
 284         parent_expr,
 285         seq_no,
 286         literal
 287     ) VALUES (
 288         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 289         'xstr',
 290         COALESCE(NEW.parenthesize, FALSE),
 291         NEW.parent_expr,
 292         COALESCE(NEW.seq_no, 1),
 293         NEW.literal
 294     );
 295
 296 CREATE OR REPLACE RULE query_expr_string_update_rule AS
 297     ON UPDATE TO query.expr_string
 298     DO INSTEAD
 299     UPDATE query.expression SET
 300         id = NEW.id,
 301         parenthesize = NEW.parenthesize,
 302         parent_expr = NEW.parent_expr,
 303         seq_no = NEW.seq_no,
 304         literal = NEW.literal
 305     WHERE
 306         id = OLD.id;
 307
 308 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
 309     ON DELETE TO query.expr_string
 310     DO INSTEAD
 311     DELETE FROM query.expression WHERE id = OLD.id;
 312
 313 COMMIT;