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