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