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

   1 /*
   2  * Copyright (C) 2009  Equinox Software, Inc. / Georgia Public Library Service
   3  * Scott McKellar <scott@esilibrary.com>
   4  *
   5  * This program is free software; you can redistribute it and/or
   6  * modify it under the terms of the GNU General Public License
   7  * as published by the Free Software Foundation; either version 2
   8  * of the License, or (at your option) any later version.
   9  *
  10  * This program is distributed in the hope that it will be useful,
  11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13  * GNU General Public License for more details.
  14  */
  15
  16 BEGIN;
  17
  18 DROP SCHEMA IF EXISTS query CASCADE;
  19 CREATE SCHEMA query;
  20 COMMENT ON SCHEMA query IS $$
  21 Contains tables designed to represent user-defined queries for
  22 reports and the like.
  23 $$;
  24
  25 CREATE TABLE  query.stored_query (
  26         id            SERIAL         PRIMARY KEY,
  27         type          TEXT           NOT NULL CONSTRAINT query_type CHECK
  28                                      ( type IN ( 'SELECT', 'UNION', 'INTERSECT', 'EXCEPT' ) ),
  29         use_all       BOOLEAN        NOT NULL DEFAULT FALSE,
  30         use_distinct  BOOLEAN        NOT NULL DEFAULT FALSE,
  31         from_clause   INT,           --REFERENCES query.from_clause
  32                                      --DEFERRABLE INITIALLY DEFERRED,
  33         where_clause  INT,           --REFERENCES query.expression
  34                                      --DEFERRABLE INITIALLY DEFERRED,
  35         having_clause INT,           --REFERENCES query.expression
  36                                      --DEFERRABLE INITIALLY DEFERRED,
  37         limit_count   INT,           --REFERENCES query.expression
  38                                      --DEFERRABLE INITIALLY DEFERRED,
  39         offset_count  INT            --REFERENCES query.expression
  40                                      --DEFERRABLE INITIALLY DEFERRED
  41 );
  42
  43 -- (Foreign keys to be defined later after other tables are created)
  44
  45 CREATE TABLE query.query_sequence (
  46         id              SERIAL            PRIMARY KEY,
  47         parent_query    INT               NOT NULL
  48                                           REFERENCES query.stored_query
  49                                                                           ON DELETE CASCADE
  50                                                                           DEFERRABLE INITIALLY DEFERRED,
  51         seq_no          INT               NOT NULL,
  52         child_query     INT               NOT NULL
  53                                           REFERENCES query.stored_query
  54                                                                           ON DELETE CASCADE
  55                                                                           DEFERRABLE INITIALLY DEFERRED,
  56         CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no )
  57 );
  58
  59 CREATE TABLE query.datatype (
  60         id              SERIAL            PRIMARY KEY,
  61         datatype_name   TEXT              NOT NULL UNIQUE,
  62         is_numeric      BOOL              NOT NULL DEFAULT FALSE,
  63         is_composite    BOOL              NOT NULL DEFAULT FALSE,
  64         CONSTRAINT qdt_comp_not_num CHECK
  65         ( is_numeric IS FALSE OR is_composite IS FALSE )
  66 );
  67
  68 -- Leave room to seed with stock datatypes
  69 -- before adding customized ones
  70 SELECT setval( 'query.datatype_id_seq', 1000 );
  71
  72 CREATE TABLE query.subfield (
  73         id              SERIAL            PRIMARY KEY,
  74         composite_type  INT               NOT NULL
  75                                           REFERENCES query.datatype(id)
  76                                           ON DELETE CASCADE
  77                                           DEFERRABLE INITIALLY DEFERRED,
  78         seq_no          INT               NOT NULL
  79                                           CONSTRAINT qsf_pos_seq_no
  80                                           CHECK( seq_no > 0 ),
  81         subfield_type   INT               NOT NULL
  82                                           REFERENCES query.datatype(id)
  83                                           DEFERRABLE INITIALLY DEFERRED,
  84         CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
  85 );
  86
  87 CREATE TABLE query.function_sig (
  88         id              SERIAL            PRIMARY KEY,
  89         function_name   TEXT              NOT NULL,
  90         return_type     INT               REFERENCES query.datatype(id)
  91                                           DEFERRABLE INITIALLY DEFERRED,
  92         is_aggregate    BOOL              NOT NULL DEFAULT FALSE,
  93         CONSTRAINT qfd_rtn_or_aggr CHECK
  94         ( return_type IS NULL OR is_aggregate = FALSE )
  95 );
  96
  97 CREATE INDEX query_function_sig_name_idx
  98         ON query.function_sig (function_name);
  99
 100 CREATE TABLE query.function_param_def (
 101         id              SERIAL            PRIMARY KEY,
 102         function_id     INT               NOT NULL
 103                                           REFERENCES query.function_sig( id )
 104                                           ON DELETE CASCADE
 105                                           DEFERRABLE INITIALLY DEFERRED,
 106         seq_no          INT               NOT NULL
 107                                           CONSTRAINT qfpd_pos_seq_no CHECK
 108                                           ( seq_no > 0 ),
 109         datatype        INT               NOT NULL
 110                                           REFERENCES query.datatype( id )
 111                                           DEFERRABLE INITIALLY DEFERRED,
 112         CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
 113 );
 114
 115 CREATE TABLE query.bind_variable (
 116         name          TEXT             PRIMARY KEY,
 117         type          TEXT             NOT NULL
 118                                            CONSTRAINT bind_variable_type CHECK
 119                                            ( type in ( 'string', 'number', 'string_list', 'number_list' )),
 120         description   TEXT             NOT NULL,
 121         default_value TEXT,            -- to be encoded in JSON
 122         label         TEXT             NOT NULL
 123 );
 124
 125 CREATE TABLE query.expression (
 126         id            SERIAL        PRIMARY KEY,
 127         type          TEXT          NOT NULL CONSTRAINT expression_type CHECK
 128                                     ( type IN (
 129                                         'xbet',    -- between
 130                                                                         'xbind',   -- bind variable
 131                                                                         'xbool',   -- boolean
 132                                         'xcase',   -- case
 133                                                                         'xcast',   -- cast
 134                                                                         'xcol',    -- column
 135                                                                         'xex',     -- exists
 136                                                                         'xfunc',   -- function
 137                                                                         'xin',     -- in
 138                                                                         'xisnull', -- is null
 139                                         'xnull',   -- null
 140                                                                         'xnum',    -- number
 141                                                                         'xop',     -- operator
 142                                                                         'xser',    -- series
 143                                                                         'xstr',    -- string
 144                                                 'xsubq'    -- subquery
 145                                                                 ) ),
 146         parenthesize  BOOL          NOT NULL DEFAULT FALSE,
 147         parent_expr   INT           REFERENCES query.expression
 148                                     ON DELETE CASCADE
 149                                     DEFERRABLE INITIALLY DEFERRED,
 150         seq_no        INT           NOT NULL DEFAULT 1,
 151         literal       TEXT,
 152         table_alias   TEXT,
 153         column_name   TEXT,
 154         left_operand  INT           REFERENCES query.expression
 155                                     DEFERRABLE INITIALLY DEFERRED,
 156         operator      TEXT,
 157         right_operand INT           REFERENCES query.expression
 158                                     DEFERRABLE INITIALLY DEFERRED,
 159         function_id   INT           REFERENCES query.function_sig
 160                                     DEFERRABLE INITIALLY DEFERRED,
 161         subquery      INT           REFERENCES query.stored_query
 162                                     DEFERRABLE INITIALLY DEFERRED,
 163         cast_type     INT           REFERENCES query.datatype
 164                                     DEFERRABLE INITIALLY DEFERRED,
 165         negate        BOOL          NOT NULL DEFAULT FALSE,
 166         bind_variable TEXT          REFERENCES query.bind_variable
 167                                     DEFERRABLE INITIALLY DEFERRED
 168 );
 169
 170 CREATE UNIQUE INDEX query_expr_parent_seq
 171         ON query.expression( parent_expr, seq_no )
 172         WHERE parent_expr IS NOT NULL;
 173
 174 -- Due to some circular references, the following foreign key definitions
 175 -- had to be deferred until query.expression existed:
 176
 177 ALTER TABLE query.stored_query
 178         ADD FOREIGN KEY ( where_clause )
 179         REFERENCES query.expression( id )
 180         DEFERRABLE INITIALLY DEFERRED;
 181
 182 ALTER TABLE query.stored_query
 183         ADD FOREIGN KEY ( having_clause )
 184         REFERENCES query.expression( id )
 185         DEFERRABLE INITIALLY DEFERRED;
 186
 187 ALTER TABLE query.stored_query
 188         ADD FOREIGN KEY ( limit_count )
 189         REFERENCES query.expression( id )
 190         DEFERRABLE INITIALLY DEFERRED;
 191
 192 ALTER TABLE query.stored_query
 193         ADD FOREIGN KEY ( offset_count )
 194         REFERENCES query.expression( id )
 195         DEFERRABLE INITIALLY DEFERRED;
 196
 197 CREATE TABLE query.case_branch (
 198         id            SERIAL        PRIMARY KEY,
 199         parent_expr   INT           NOT NULL REFERENCES query.expression
 200                                     ON DELETE CASCADE
 201                                     DEFERRABLE INITIALLY DEFERRED,
 202         seq_no        INT           NOT NULL,
 203         condition     INT           REFERENCES query.expression
 204                                     DEFERRABLE INITIALLY DEFERRED,
 205         result        INT           NOT NULL REFERENCES query.expression
 206                                     DEFERRABLE INITIALLY DEFERRED,
 207         CONSTRAINT case_branch_parent_seq UNIQUE (parent_expr, seq_no)
 208 );
 209
 210 CREATE TABLE query.from_relation (
 211         id               SERIAL        PRIMARY KEY,
 212         type             TEXT          NOT NULL CONSTRAINT relation_type CHECK (
 213                                            type IN ( 'RELATION', 'SUBQUERY', 'FUNCTION' ) ),
 214         table_name       TEXT,
 215         class_name       TEXT,
 216         subquery         INT           REFERENCES query.stored_query,
 217         function_call    INT           REFERENCES query.expression,
 218         table_alias      TEXT,
 219         parent_relation  INT           REFERENCES query.from_relation
 220                                        ON DELETE CASCADE
 221                                        DEFERRABLE INITIALLY DEFERRED,
 222         seq_no           INT           NOT NULL DEFAULT 1,
 223         join_type        TEXT          CONSTRAINT good_join_type CHECK (
 224                                            join_type IS NULL OR join_type IN
 225                                            ( 'INNER', 'LEFT', 'RIGHT', 'FULL' )
 226                                        ),
 227         on_clause        INT           REFERENCES query.expression
 228                                        DEFERRABLE INITIALLY DEFERRED,
 229         CONSTRAINT join_or_core CHECK (
 230             ( parent_relation IS NULL AND join_type IS NULL
 231               AND on_clause IS NULL )
 232             OR
 233             ( parent_relation IS NOT NULL AND join_type IS NOT NULL
 234               AND on_clause IS NOT NULL )
 235         )
 236 );
 237
 238 CREATE UNIQUE INDEX from_parent_seq
 239         ON query.from_relation( parent_relation, seq_no )
 240         WHERE parent_relation IS NOT NULL;
 241
 242 -- The following foreign key had to be deferred until
 243 -- query.from_relation existed
 244
 245 ALTER TABLE query.stored_query
 246         ADD FOREIGN KEY (from_clause)
 247         REFERENCES query.from_relation
 248         DEFERRABLE INITIALLY DEFERRED;
 249
 250 CREATE TABLE query.record_column (
 251         id            SERIAL            PRIMARY KEY,
 252         from_relation INT               NOT NULL REFERENCES query.from_relation
 253                                         ON DELETE CASCADE
 254                                         DEFERRABLE INITIALLY DEFERRED,
 255         seq_no        INT               NOT NULL,
 256         column_name   TEXT              NOT NULL,
 257     column_type   INT               NOT NULL REFERENCES query.datatype
 258                                     ON DELETE CASCADE
 259                                     DEFERRABLE INITIALLY DEFERRED,
 260         CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
 261 );
 262
 263 CREATE TABLE query.select_item (
 264         id               SERIAL         PRIMARY KEY,
 265         stored_query     INT            NOT NULL REFERENCES query.stored_query
 266                                         ON DELETE CASCADE
 267                                         DEFERRABLE INITIALLY DEFERRED,
 268         seq_no           INT            NOT NULL,
 269         expression       INT            NOT NULL REFERENCES query.expression
 270                                         DEFERRABLE INITIALLY DEFERRED,
 271         column_alias     TEXT,
 272         grouped_by       BOOL           NOT NULL DEFAULT FALSE,
 273         CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
 274 );
 275
 276 CREATE TABLE query.order_by_item (
 277         id               SERIAL         PRIMARY KEY,
 278         stored_query     INT            NOT NULL REFERENCES query.stored_query
 279                                         ON DELETE CASCADE
 280                                         DEFERRABLE INITIALLY DEFERRED,
 281         seq_no           INT            NOT NULL,
 282         expression       INT            NOT NULL REFERENCES query.expression
 283                                         ON DELETE CASCADE
 284                                         DEFERRABLE INITIALLY DEFERRED,
 285         CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
 286 );
 287
 288 -- Create updatable views -------------------------------------------
 289
 290 -- Create updatable view for BETWEEN expressions
 291
 292 CREATE OR REPLACE VIEW query.expr_xbet AS
 293     SELECT
 294                 id,
 295                 parenthesize,
 296                 parent_expr,
 297                 seq_no,
 298                 left_operand,
 299                 negate
 300     FROM
 301         query.expression
 302     WHERE
 303         type = 'xbet';
 304
 305 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
 306     ON INSERT TO query.expr_xbet
 307     DO INSTEAD
 308     INSERT INTO query.expression (
 309                 id,
 310                 type,
 311                 parenthesize,
 312                 parent_expr,
 313                 seq_no,
 314                 left_operand,
 315                 negate
 316     ) VALUES (
 317         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 318         'xbet',
 319         COALESCE(NEW.parenthesize, FALSE),
 320         NEW.parent_expr,
 321         COALESCE(NEW.seq_no, 1),
 322                 NEW.left_operand,
 323                 COALESCE(NEW.negate, false)
 324     );
 325
 326 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
 327     ON UPDATE TO query.expr_xbet
 328     DO INSTEAD
 329     UPDATE query.expression SET
 330         id = NEW.id,
 331         parenthesize = NEW.parenthesize,
 332         parent_expr = NEW.parent_expr,
 333         seq_no = NEW.seq_no,
 334                 left_operand = NEW.left_operand,
 335                 negate = NEW.negate
 336     WHERE
 337         id = OLD.id;
 338
 339 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
 340     ON DELETE TO query.expr_xbet
 341     DO INSTEAD
 342     DELETE FROM query.expression WHERE id = OLD.id;
 343
 344 -- Create updatable view for bind variable expressions
 345
 346 CREATE OR REPLACE VIEW query.expr_xbind AS
 347     SELECT
 348                 id,
 349                 parenthesize,
 350                 parent_expr,
 351                 seq_no,
 352                 bind_variable
 353     FROM
 354         query.expression
 355     WHERE
 356         type = 'xbind';
 357
 358 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
 359     ON INSERT TO query.expr_xbind
 360     DO INSTEAD
 361     INSERT INTO query.expression (
 362                 id,
 363                 type,
 364                 parenthesize,
 365                 parent_expr,
 366                 seq_no,
 367                 bind_variable
 368     ) VALUES (
 369         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 370         'xbind',
 371         COALESCE(NEW.parenthesize, FALSE),
 372         NEW.parent_expr,
 373         COALESCE(NEW.seq_no, 1),
 374                 NEW.bind_variable
 375     );
 376
 377 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
 378     ON UPDATE TO query.expr_xbind
 379     DO INSTEAD
 380     UPDATE query.expression SET
 381         id = NEW.id,
 382         parenthesize = NEW.parenthesize,
 383         parent_expr = NEW.parent_expr,
 384         seq_no = NEW.seq_no,
 385                 bind_variable = NEW.bind_variable
 386     WHERE
 387         id = OLD.id;
 388
 389 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
 390     ON DELETE TO query.expr_xbind
 391     DO INSTEAD
 392     DELETE FROM query.expression WHERE id = OLD.id;
 393
 394 -- Create updatable view for boolean expressions
 395
 396 CREATE OR REPLACE VIEW query.expr_xbool AS
 397     SELECT
 398                 id,
 399                 parenthesize,
 400                 parent_expr,
 401                 seq_no,
 402                 literal,
 403                 negate
 404     FROM
 405         query.expression
 406     WHERE
 407         type = 'xbool';
 408
 409 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
 410     ON INSERT TO query.expr_xbool
 411     DO INSTEAD
 412     INSERT INTO query.expression (
 413                 id,
 414                 type,
 415                 parenthesize,
 416                 parent_expr,
 417                 seq_no,
 418                 literal,
 419                 negate
 420     ) VALUES (
 421         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 422         'xbool',
 423         COALESCE(NEW.parenthesize, FALSE),
 424         NEW.parent_expr,
 425         COALESCE(NEW.seq_no, 1),
 426         NEW.literal,
 427                 COALESCE(NEW.negate, false)
 428     );
 429
 430 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
 431     ON UPDATE TO query.expr_xbool
 432     DO INSTEAD
 433     UPDATE query.expression SET
 434         id = NEW.id,
 435         parenthesize = NEW.parenthesize,
 436         parent_expr = NEW.parent_expr,
 437         seq_no = NEW.seq_no,
 438         literal = NEW.literal,
 439                 negate = NEW.negate
 440     WHERE
 441         id = OLD.id;
 442
 443 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
 444     ON DELETE TO query.expr_xbool
 445     DO INSTEAD
 446     DELETE FROM query.expression WHERE id = OLD.id;
 447
 448 -- Create updatable view for CASE expressions
 449
 450 CREATE OR REPLACE VIEW query.expr_xcase AS
 451     SELECT
 452                 id,
 453                 parenthesize,
 454                 parent_expr,
 455                 seq_no,
 456                 left_operand,
 457                 negate
 458     FROM
 459         query.expression
 460     WHERE
 461         type = 'xcase';
 462
 463 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
 464     ON INSERT TO query.expr_xcase
 465     DO INSTEAD
 466     INSERT INTO query.expression (
 467                 id,
 468                 type,
 469                 parenthesize,
 470                 parent_expr,
 471                 seq_no,
 472                 left_operand,
 473                 negate
 474     ) VALUES (
 475         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 476         'xcase',
 477         COALESCE(NEW.parenthesize, FALSE),
 478         NEW.parent_expr,
 479         COALESCE(NEW.seq_no, 1),
 480                 NEW.left_operand,
 481                 COALESCE(NEW.negate, false)
 482     );
 483
 484 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
 485     ON UPDATE TO query.expr_xcase
 486     DO INSTEAD
 487     UPDATE query.expression SET
 488         id = NEW.id,
 489         parenthesize = NEW.parenthesize,
 490         parent_expr = NEW.parent_expr,
 491         seq_no = NEW.seq_no,
 492                 left_operand = NEW.left_operand,
 493                 negate = NEW.negate
 494     WHERE
 495         id = OLD.id;
 496
 497 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
 498     ON DELETE TO query.expr_xcase
 499     DO INSTEAD
 500     DELETE FROM query.expression WHERE id = OLD.id;
 501
 502 -- Create updatable view for cast expressions
 503
 504 CREATE OR REPLACE VIEW query.expr_xcast AS
 505     SELECT
 506                 id,
 507                 parenthesize,
 508                 parent_expr,
 509                 seq_no,
 510                 left_operand,
 511                 cast_type,
 512                 negate
 513     FROM
 514         query.expression
 515     WHERE
 516         type = 'xcast';
 517
 518 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
 519     ON INSERT TO query.expr_xcast
 520     DO INSTEAD
 521     INSERT INTO query.expression (
 522                 id,
 523                 type,
 524                 parenthesize,
 525                 parent_expr,
 526                 seq_no,
 527                 left_operand,
 528                 cast_type,
 529                 negate
 530     ) VALUES (
 531         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 532         'xcast',
 533         COALESCE(NEW.parenthesize, FALSE),
 534         NEW.parent_expr,
 535         COALESCE(NEW.seq_no, 1),
 536                 NEW.left_operand,
 537                 NEW.cast_type,
 538                 COALESCE(NEW.negate, false)
 539     );
 540
 541 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
 542     ON UPDATE TO query.expr_xcast
 543     DO INSTEAD
 544     UPDATE query.expression SET
 545         id = NEW.id,
 546         parenthesize = NEW.parenthesize,
 547         parent_expr = NEW.parent_expr,
 548         seq_no = NEW.seq_no,
 549                 left_operand = NEW.left_operand,
 550                 cast_type = NEW.cast_type,
 551                 negate = NEW.negate
 552     WHERE
 553         id = OLD.id;
 554
 555 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
 556     ON DELETE TO query.expr_xcast
 557     DO INSTEAD
 558     DELETE FROM query.expression WHERE id = OLD.id;
 559
 560 -- Create updatable view for column expressions
 561
 562 CREATE OR REPLACE VIEW query.expr_xcol AS
 563     SELECT
 564                 id,
 565                 parenthesize,
 566                 parent_expr,
 567                 seq_no,
 568                 table_alias,
 569                 column_name,
 570                 negate
 571     FROM
 572         query.expression
 573     WHERE
 574         type = 'xcol';
 575
 576 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
 577     ON INSERT TO query.expr_xcol
 578     DO INSTEAD
 579     INSERT INTO query.expression (
 580                 id,
 581                 type,
 582                 parenthesize,
 583                 parent_expr,
 584                 seq_no,
 585                 table_alias,
 586                 column_name,
 587                 negate
 588     ) VALUES (
 589         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 590         'xcol',
 591         COALESCE(NEW.parenthesize, FALSE),
 592         NEW.parent_expr,
 593         COALESCE(NEW.seq_no, 1),
 594                 NEW.table_alias,
 595                 NEW.column_name,
 596                 COALESCE(NEW.negate, false)
 597     );
 598
 599 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
 600     ON UPDATE TO query.expr_xcol
 601     DO INSTEAD
 602     UPDATE query.expression SET
 603         id = NEW.id,
 604         parenthesize = NEW.parenthesize,
 605         parent_expr = NEW.parent_expr,
 606         seq_no = NEW.seq_no,
 607                 table_alias = NEW.table_alias,
 608                 column_name = NEW.column_name,
 609                 negate = NEW.negate
 610     WHERE
 611         id = OLD.id;
 612
 613 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
 614     ON DELETE TO query.expr_xcol
 615     DO INSTEAD
 616     DELETE FROM query.expression WHERE id = OLD.id;
 617
 618 -- Create updatable view for EXISTS expressions
 619
 620 CREATE OR REPLACE VIEW query.expr_xex AS
 621     SELECT
 622                 id,
 623                 parenthesize,
 624                 parent_expr,
 625                 seq_no,
 626                 subquery,
 627                 negate
 628     FROM
 629         query.expression
 630     WHERE
 631         type = 'xex';
 632
 633 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
 634     ON INSERT TO query.expr_xex
 635     DO INSTEAD
 636     INSERT INTO query.expression (
 637                 id,
 638                 type,
 639                 parenthesize,
 640                 parent_expr,
 641                 seq_no,
 642                 subquery,
 643                 negate
 644     ) VALUES (
 645         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 646         'xex',
 647         COALESCE(NEW.parenthesize, FALSE),
 648         NEW.parent_expr,
 649         COALESCE(NEW.seq_no, 1),
 650                 NEW.subquery,
 651                 COALESCE(NEW.negate, false)
 652     );
 653
 654 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
 655     ON UPDATE TO query.expr_xex
 656     DO INSTEAD
 657     UPDATE query.expression SET
 658         id = NEW.id,
 659         parenthesize = NEW.parenthesize,
 660         parent_expr = NEW.parent_expr,
 661         seq_no = NEW.seq_no,
 662                 subquery = NEW.subquery,
 663                 negate = NEW.negate
 664     WHERE
 665         id = OLD.id;
 666
 667 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
 668     ON DELETE TO query.expr_xex
 669     DO INSTEAD
 670     DELETE FROM query.expression WHERE id = OLD.id;
 671
 672 -- Create updatable view for function call expressions
 673
 674 CREATE OR REPLACE VIEW query.expr_xfunc AS
 675     SELECT
 676                 id,
 677                 parenthesize,
 678                 parent_expr,
 679                 seq_no,
 680                 column_name,
 681                 function_id,
 682                 negate
 683     FROM
 684         query.expression
 685     WHERE
 686         type = 'xfunc';
 687
 688 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
 689     ON INSERT TO query.expr_xfunc
 690     DO INSTEAD
 691     INSERT INTO query.expression (
 692                 id,
 693                 type,
 694                 parenthesize,
 695                 parent_expr,
 696                 seq_no,
 697                 column_name,
 698                 function_id,
 699                 negate
 700     ) VALUES (
 701         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 702         'xfunc',
 703         COALESCE(NEW.parenthesize, FALSE),
 704         NEW.parent_expr,
 705         COALESCE(NEW.seq_no, 1),
 706                 NEW.column_name,
 707                 NEW.function_id,
 708                 COALESCE(NEW.negate, false)
 709     );
 710
 711 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
 712     ON UPDATE TO query.expr_xfunc
 713     DO INSTEAD
 714     UPDATE query.expression SET
 715         id = NEW.id,
 716         parenthesize = NEW.parenthesize,
 717         parent_expr = NEW.parent_expr,
 718         seq_no = NEW.seq_no,
 719                 column_name = NEW.column_name,
 720                 function_id = NEW.function_id,
 721                 negate = NEW.negate
 722     WHERE
 723         id = OLD.id;
 724
 725 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
 726     ON DELETE TO query.expr_xfunc
 727     DO INSTEAD
 728     DELETE FROM query.expression WHERE id = OLD.id;
 729
 730 -- Create updatable view for IN expressions
 731
 732 CREATE OR REPLACE VIEW query.expr_xin AS
 733     SELECT
 734                 id,
 735                 parenthesize,
 736                 parent_expr,
 737                 seq_no,
 738                 left_operand,
 739                 subquery,
 740                 negate
 741     FROM
 742         query.expression
 743     WHERE
 744         type = 'xin';
 745
 746 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
 747     ON INSERT TO query.expr_xin
 748     DO INSTEAD
 749     INSERT INTO query.expression (
 750                 id,
 751                 type,
 752                 parenthesize,
 753                 parent_expr,
 754                 seq_no,
 755                 left_operand,
 756                 subquery,
 757                 negate
 758     ) VALUES (
 759         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 760         'xin',
 761         COALESCE(NEW.parenthesize, FALSE),
 762         NEW.parent_expr,
 763         COALESCE(NEW.seq_no, 1),
 764                 NEW.left_operand,
 765                 NEW.subquery,
 766                 COALESCE(NEW.negate, false)
 767     );
 768
 769 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
 770     ON UPDATE TO query.expr_xin
 771     DO INSTEAD
 772     UPDATE query.expression SET
 773         id = NEW.id,
 774         parenthesize = NEW.parenthesize,
 775         parent_expr = NEW.parent_expr,
 776         seq_no = NEW.seq_no,
 777                 left_operand = NEW.left_operand,
 778                 subquery = NEW.subquery,
 779                 negate = NEW.negate
 780     WHERE
 781         id = OLD.id;
 782
 783 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
 784     ON DELETE TO query.expr_xin
 785     DO INSTEAD
 786     DELETE FROM query.expression WHERE id = OLD.id;
 787
 788 -- Create updatable view for IS NULL expressions
 789
 790 CREATE OR REPLACE VIEW query.expr_xisnull AS
 791     SELECT
 792                 id,
 793                 parenthesize,
 794                 parent_expr,
 795                 seq_no,
 796                 left_operand,
 797                 negate
 798     FROM
 799         query.expression
 800     WHERE
 801         type = 'xisnull';
 802
 803 CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
 804     ON INSERT TO query.expr_xisnull
 805     DO INSTEAD
 806     INSERT INTO query.expression (
 807                 id,
 808                 type,
 809                 parenthesize,
 810                 parent_expr,
 811                 seq_no,
 812                 left_operand,
 813                 negate
 814     ) VALUES (
 815         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 816         'xisnull',
 817         COALESCE(NEW.parenthesize, FALSE),
 818         NEW.parent_expr,
 819         COALESCE(NEW.seq_no, 1),
 820                 NEW.left_operand,
 821                 COALESCE(NEW.negate, false)
 822     );
 823
 824 CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
 825     ON UPDATE TO query.expr_xisnull
 826     DO INSTEAD
 827     UPDATE query.expression SET
 828         id = NEW.id,
 829         parenthesize = NEW.parenthesize,
 830         parent_expr = NEW.parent_expr,
 831         seq_no = NEW.seq_no,
 832                 left_operand = NEW.left_operand,
 833                 negate = NEW.negate
 834     WHERE
 835         id = OLD.id;
 836
 837 CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS
 838     ON DELETE TO query.expr_xisnull
 839     DO INSTEAD
 840     DELETE FROM query.expression WHERE id = OLD.id;
 841
 842 -- Create updatable view for NULL expressions
 843
 844 CREATE OR REPLACE VIEW query.expr_xnull AS
 845     SELECT
 846                 id,
 847                 parenthesize,
 848                 parent_expr,
 849                 seq_no,
 850                 negate
 851     FROM
 852         query.expression
 853     WHERE
 854         type = 'xnull';
 855
 856 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
 857     ON INSERT TO query.expr_xnull
 858     DO INSTEAD
 859     INSERT INTO query.expression (
 860                 id,
 861                 type,
 862                 parenthesize,
 863                 parent_expr,
 864                 seq_no,
 865                 negate
 866     ) VALUES (
 867         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 868         'xnull',
 869         COALESCE(NEW.parenthesize, FALSE),
 870         NEW.parent_expr,
 871         COALESCE(NEW.seq_no, 1),
 872                 COALESCE(NEW.negate, false)
 873     );
 874
 875 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
 876     ON UPDATE TO query.expr_xnull
 877     DO INSTEAD
 878     UPDATE query.expression SET
 879         id = NEW.id,
 880         parenthesize = NEW.parenthesize,
 881         parent_expr = NEW.parent_expr,
 882         seq_no = NEW.seq_no,
 883                 negate = NEW.negate
 884     WHERE
 885         id = OLD.id;
 886
 887 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
 888     ON DELETE TO query.expr_xnull
 889     DO INSTEAD
 890     DELETE FROM query.expression WHERE id = OLD.id;
 891
 892 -- Create updatable view for numeric literal expressions
 893
 894 CREATE OR REPLACE VIEW query.expr_xnum AS
 895     SELECT
 896                 id,
 897                 parenthesize,
 898                 parent_expr,
 899                 seq_no,
 900                 literal
 901     FROM
 902         query.expression
 903     WHERE
 904         type = 'xnum';
 905
 906 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
 907     ON INSERT TO query.expr_xnum
 908     DO INSTEAD
 909     INSERT INTO query.expression (
 910                 id,
 911                 type,
 912                 parenthesize,
 913                 parent_expr,
 914                 seq_no,
 915                 literal
 916     ) VALUES (
 917         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 918         'xnum',
 919         COALESCE(NEW.parenthesize, FALSE),
 920         NEW.parent_expr,
 921         COALESCE(NEW.seq_no, 1),
 922         NEW.literal
 923     );
 924
 925 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
 926     ON UPDATE TO query.expr_xnum
 927     DO INSTEAD
 928     UPDATE query.expression SET
 929         id = NEW.id,
 930         parenthesize = NEW.parenthesize,
 931         parent_expr = NEW.parent_expr,
 932         seq_no = NEW.seq_no,
 933         literal = NEW.literal
 934     WHERE
 935         id = OLD.id;
 936
 937 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
 938     ON DELETE TO query.expr_xnum
 939     DO INSTEAD
 940     DELETE FROM query.expression WHERE id = OLD.id;
 941
 942 -- Create updatable view for operator expressions
 943
 944 CREATE OR REPLACE VIEW query.expr_xop AS
 945     SELECT
 946                 id,
 947                 parenthesize,
 948                 parent_expr,
 949                 seq_no,
 950                 left_operand,
 951                 operator,
 952                 right_operand,
 953                 negate
 954     FROM
 955         query.expression
 956     WHERE
 957         type = 'xop';
 958
 959 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
 960     ON INSERT TO query.expr_xop
 961     DO INSTEAD
 962     INSERT INTO query.expression (
 963                 id,
 964                 type,
 965                 parenthesize,
 966                 parent_expr,
 967                 seq_no,
 968                 left_operand,
 969                 operator,
 970                 right_operand,
 971                 negate
 972     ) VALUES (
 973         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 974         'xop',
 975         COALESCE(NEW.parenthesize, FALSE),
 976         NEW.parent_expr,
 977         COALESCE(NEW.seq_no, 1),
 978                 NEW.left_operand,
 979                 NEW.operator,
 980                 NEW.right_operand,
 981                 COALESCE(NEW.negate, false)
 982     );
 983
 984 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
 985     ON UPDATE TO query.expr_xop
 986     DO INSTEAD
 987     UPDATE query.expression SET
 988         id = NEW.id,
 989         parenthesize = NEW.parenthesize,
 990         parent_expr = NEW.parent_expr,
 991         seq_no = NEW.seq_no,
 992                 left_operand = NEW.left_operand,
 993                 operator = NEW.operator,
 994                 right_operand = NEW.right_operand,
 995                 negate = NEW.negate
 996     WHERE
 997         id = OLD.id;
 998
 999 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
1000     ON DELETE TO query.expr_xop
1001     DO INSTEAD
1002     DELETE FROM query.expression WHERE id = OLD.id;
1003
1004 -- Create updatable view for series expressions,
1005 -- i.e. series of expressions separated by operators
1006
1007 CREATE OR REPLACE VIEW query.expr_xser AS
1008     SELECT
1009                 id,
1010                 parenthesize,
1011                 parent_expr,
1012                 seq_no,
1013                 operator,
1014                 negate
1015     FROM
1016         query.expression
1017     WHERE
1018         type = 'xser';
1019
1020 CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
1021     ON INSERT TO query.expr_xser
1022     DO INSTEAD
1023     INSERT INTO query.expression (
1024                 id,
1025                 type,
1026                 parenthesize,
1027                 parent_expr,
1028                 seq_no,
1029                 operator,
1030                 negate
1031     ) VALUES (
1032         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1033         'xser',
1034         COALESCE(NEW.parenthesize, FALSE),
1035         NEW.parent_expr,
1036         COALESCE(NEW.seq_no, 1),
1037                 NEW.operator,
1038                 COALESCE(NEW.negate, false)
1039     );
1040
1041 CREATE OR REPLACE RULE query_expr_xser_update_rule AS
1042     ON UPDATE TO query.expr_xser
1043     DO INSTEAD
1044     UPDATE query.expression SET
1045         id = NEW.id,
1046         parenthesize = NEW.parenthesize,
1047         parent_expr = NEW.parent_expr,
1048         seq_no = NEW.seq_no,
1049                 operator = NEW.operator,
1050                 negate = NEW.negate
1051     WHERE
1052         id = OLD.id;
1053
1054 CREATE OR REPLACE RULE query_expr_xser_delete_rule AS
1055     ON DELETE TO query.expr_xser
1056     DO INSTEAD
1057     DELETE FROM query.expression WHERE id = OLD.id;
1058
1059 -- Create updatable view for string literal expressions
1060
1061 CREATE OR REPLACE VIEW query.expr_xstr AS
1062     SELECT
1063         id,
1064         parenthesize,
1065         parent_expr,
1066         seq_no,
1067         literal
1068     FROM
1069         query.expression
1070     WHERE
1071         type = 'xstr';
1072
1073 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
1074     ON INSERT TO query.expr_xstr
1075     DO INSTEAD
1076     INSERT INTO query.expression (
1077         id,
1078         type,
1079         parenthesize,
1080         parent_expr,
1081         seq_no,
1082         literal
1083     ) VALUES (
1084         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1085         'xstr',
1086         COALESCE(NEW.parenthesize, FALSE),
1087         NEW.parent_expr,
1088         COALESCE(NEW.seq_no, 1),
1089         NEW.literal
1090     );
1091
1092 CREATE OR REPLACE RULE query_expr_string_update_rule AS
1093     ON UPDATE TO query.expr_xstr
1094     DO INSTEAD
1095     UPDATE query.expression SET
1096         id = NEW.id,
1097         parenthesize = NEW.parenthesize,
1098         parent_expr = NEW.parent_expr,
1099         seq_no = NEW.seq_no,
1100         literal = NEW.literal
1101     WHERE
1102         id = OLD.id;
1103
1104 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
1105     ON DELETE TO query.expr_xstr
1106     DO INSTEAD
1107     DELETE FROM query.expression WHERE id = OLD.id;
1108
1109 -- Create updatable view for subquery expressions
1110
1111 CREATE OR REPLACE VIEW query.expr_xsubq AS
1112     SELECT
1113                 id,
1114                 parenthesize,
1115                 parent_expr,
1116                 seq_no,
1117                 subquery,
1118                 negate
1119     FROM
1120         query.expression
1121     WHERE
1122         type = 'xsubq';
1123
1124 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
1125     ON INSERT TO query.expr_xsubq
1126     DO INSTEAD
1127     INSERT INTO query.expression (
1128                 id,
1129                 type,
1130                 parenthesize,
1131                 parent_expr,
1132                 seq_no,
1133                 subquery,
1134                 negate
1135     ) VALUES (
1136         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1137         'xsubq',
1138         COALESCE(NEW.parenthesize, FALSE),
1139         NEW.parent_expr,
1140         COALESCE(NEW.seq_no, 1),
1141                 NEW.subquery,
1142                 COALESCE(NEW.negate, false)
1143     );
1144
1145 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1146     ON UPDATE TO query.expr_xsubq
1147     DO INSTEAD
1148     UPDATE query.expression SET
1149         id = NEW.id,
1150         parenthesize = NEW.parenthesize,
1151         parent_expr = NEW.parent_expr,
1152         seq_no = NEW.seq_no,
1153                 subquery = NEW.subquery,
1154                 negate = NEW.negate
1155     WHERE
1156         id = OLD.id;
1157
1158 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1159     ON DELETE TO query.expr_xsubq
1160     DO INSTEAD
1161     DELETE FROM query.expression WHERE id = OLD.id;
1162
1163 COMMIT;