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   TEXT              NOT NULL,
 233         CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
 234 );
 235
 236 CREATE TABLE query.select_item (
 237         id               SERIAL         PRIMARY KEY,
 238         stored_query     INT            NOT NULL REFERENCES query.stored_query
 239                                         ON DELETE CASCADE
 240                                         DEFERRABLE INITIALLY DEFERRED,
 241         seq_no           INT            NOT NULL,
 242         expression       INT            NOT NULL REFERENCES query.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 query.order_by_item (
 250         id               SERIAL         PRIMARY KEY,
 251         stored_query     INT            NOT NULL REFERENCES query.stored_query
 252                                         ON DELETE CASCADE
 253                                         DEFERRABLE INITIALLY DEFERRED,
 254         seq_no           INT            NOT NULL,
 255         expression       INT            NOT NULL REFERENCES query.expression
 256                                         ON DELETE CASCADE
 257                                         DEFERRABLE INITIALLY DEFERRED,
 258         CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
 259 );
 260
 261 -- Create updatable views
 262
 263 CREATE OR REPLACE VIEW query.expr_string AS
 264     SELECT
 265         id,
 266         parenthesize,
 267         parent_expr,
 268         seq_no,
 269         literal
 270     FROM
 271         query.expression
 272     WHERE
 273         type = 'xstr';
 274
 275 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
 276     ON INSERT TO query.expr_string
 277     DO INSTEAD
 278     INSERT INTO query.expression (
 279         id,
 280         type,
 281         parenthesize,
 282         parent_expr,
 283         seq_no,
 284         literal
 285     ) VALUES (
 286         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 287         'xstr',
 288         COALESCE(NEW.parenthesize, FALSE),
 289         NEW.parent_expr,
 290         COALESCE(NEW.seq_no, 1),
 291         NEW.literal
 292     );
 293
 294 CREATE OR REPLACE RULE query_expr_string_update_rule AS
 295     ON UPDATE TO query.expr_string
 296     DO INSTEAD
 297     UPDATE query.expression SET
 298         id = NEW.id,
 299         parenthesize = NEW.parenthesize,
 300         parent_expr = NEW.parent_expr,
 301         seq_no = NEW.seq_no,
 302         literal = NEW.literal
 303     WHERE
 304         id = OLD.id;
 305
 306 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
 307     ON DELETE TO query.expr_string
 308     DO INSTEAD
 309     DELETE FROM query.expression WHERE id = OLD.id;
 310
 311 COMMIT;