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