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                 negate
 288     FROM
 289         query.expression
 290     WHERE
 291         type = 'xbet';
 292
 293 CREATE OR REPLACE RULE query_expr_xbet_insert_rule AS
 294     ON INSERT TO query.expr_xbet
 295     DO INSTEAD
 296     INSERT INTO query.expression (
 297                 id,
 298                 type,
 299                 parenthesize,
 300                 parent_expr,
 301                 seq_no,
 302                 negate
 303     ) VALUES (
 304         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 305         'xbet',
 306         COALESCE(NEW.parenthesize, FALSE),
 307         NEW.parent_expr,
 308         COALESCE(NEW.seq_no, 1),
 309                 COALESCE(NEW.negate, false)
 310     );
 311
 312 CREATE OR REPLACE RULE query_expr_xbet_update_rule AS
 313     ON UPDATE TO query.expr_xbet
 314     DO INSTEAD
 315     UPDATE query.expression SET
 316         id = NEW.id,
 317         parenthesize = NEW.parenthesize,
 318         parent_expr = NEW.parent_expr,
 319         seq_no = NEW.seq_no,
 320                 negate = NEW.negate
 321     WHERE
 322         id = OLD.id;
 323
 324 CREATE OR REPLACE RULE query_expr_xbet_delete_rule AS
 325     ON DELETE TO query.expr_xbet
 326     DO INSTEAD
 327     DELETE FROM query.expression WHERE id = OLD.id;
 328
 329 -- Create updatable view for bind variable expressions
 330
 331 CREATE OR REPLACE VIEW query.expr_xbind AS
 332     SELECT
 333                 id,
 334                 parenthesize,
 335                 parent_expr,
 336                 seq_no,
 337                 bind_variable
 338     FROM
 339         query.expression
 340     WHERE
 341         type = 'xbind';
 342
 343 CREATE OR REPLACE RULE query_expr_xbind_insert_rule AS
 344     ON INSERT TO query.expr_xbind
 345     DO INSTEAD
 346     INSERT INTO query.expression (
 347                 id,
 348                 type,
 349                 parenthesize,
 350                 parent_expr,
 351                 seq_no,
 352                 bind_variable
 353     ) VALUES (
 354         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 355         'xbind',
 356         COALESCE(NEW.parenthesize, FALSE),
 357         NEW.parent_expr,
 358         COALESCE(NEW.seq_no, 1),
 359                 NEW.bind_variable
 360     );
 361
 362 CREATE OR REPLACE RULE query_expr_xbind_update_rule AS
 363     ON UPDATE TO query.expr_xbind
 364     DO INSTEAD
 365     UPDATE query.expression SET
 366         id = NEW.id,
 367         parenthesize = NEW.parenthesize,
 368         parent_expr = NEW.parent_expr,
 369         seq_no = NEW.seq_no,
 370                 bind_variable = NEW.bind_variable
 371     WHERE
 372         id = OLD.id;
 373
 374 CREATE OR REPLACE RULE query_expr_xbind_delete_rule AS
 375     ON DELETE TO query.expr_xbind
 376     DO INSTEAD
 377     DELETE FROM query.expression WHERE id = OLD.id;
 378
 379 -- Create updatable view for boolean expressions
 380
 381 CREATE OR REPLACE VIEW query.expr_xbool AS
 382     SELECT
 383                 id,
 384                 parenthesize,
 385                 parent_expr,
 386                 seq_no,
 387                 literal,
 388                 negate
 389     FROM
 390         query.expression
 391     WHERE
 392         type = 'xbool';
 393
 394 CREATE OR REPLACE RULE query_expr_xbool_insert_rule AS
 395     ON INSERT TO query.expr_xbool
 396     DO INSTEAD
 397     INSERT INTO query.expression (
 398                 id,
 399                 type,
 400                 parenthesize,
 401                 parent_expr,
 402                 seq_no,
 403                 literal,
 404                 negate
 405     ) VALUES (
 406         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 407         'xbool',
 408         COALESCE(NEW.parenthesize, FALSE),
 409         NEW.parent_expr,
 410         COALESCE(NEW.seq_no, 1),
 411         NEW.literal,
 412                 COALESCE(NEW.negate, false)
 413     );
 414
 415 CREATE OR REPLACE RULE query_expr_xbool_update_rule AS
 416     ON UPDATE TO query.expr_xbool
 417     DO INSTEAD
 418     UPDATE query.expression SET
 419         id = NEW.id,
 420         parenthesize = NEW.parenthesize,
 421         parent_expr = NEW.parent_expr,
 422         seq_no = NEW.seq_no,
 423         literal = NEW.literal,
 424                 negate = NEW.negate
 425     WHERE
 426         id = OLD.id;
 427
 428 CREATE OR REPLACE RULE query_expr_xbool_delete_rule AS
 429     ON DELETE TO query.expr_xbool
 430     DO INSTEAD
 431     DELETE FROM query.expression WHERE id = OLD.id;
 432
 433 -- Create updatable view for CASE expressions
 434
 435 CREATE OR REPLACE VIEW query.expr_xcase AS
 436     SELECT
 437                 id,
 438                 parenthesize,
 439                 parent_expr,
 440                 seq_no,
 441                 negate
 442     FROM
 443         query.expression
 444     WHERE
 445         type = 'xcase';
 446
 447 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
 448     ON INSERT TO query.expr_xcase
 449     DO INSTEAD
 450     INSERT INTO query.expression (
 451                 id,
 452                 type,
 453                 parenthesize,
 454                 parent_expr,
 455                 seq_no,
 456                 negate
 457     ) VALUES (
 458         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 459         'xcase',
 460         COALESCE(NEW.parenthesize, FALSE),
 461         NEW.parent_expr,
 462         COALESCE(NEW.seq_no, 1),
 463                 COALESCE(NEW.negate, false)
 464     );
 465
 466 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
 467     ON UPDATE TO query.expr_xcase
 468     DO INSTEAD
 469     UPDATE query.expression SET
 470         id = NEW.id,
 471         parenthesize = NEW.parenthesize,
 472         parent_expr = NEW.parent_expr,
 473         seq_no = NEW.seq_no,
 474                 negate = NEW.negate
 475     WHERE
 476         id = OLD.id;
 477
 478 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
 479     ON DELETE TO query.expr_xcase
 480     DO INSTEAD
 481     DELETE FROM query.expression WHERE id = OLD.id;
 482
 483 -- Create updatable view for cast expressions
 484
 485 CREATE OR REPLACE VIEW query.expr_xcast AS
 486     SELECT
 487                 id,
 488                 parenthesize,
 489                 parent_expr,
 490                 seq_no,
 491                 left_operand,
 492                 cast_type,
 493                 negate
 494     FROM
 495         query.expression
 496     WHERE
 497         type = 'xcast';
 498
 499 CREATE OR REPLACE RULE query_expr_xcast_insert_rule AS
 500     ON INSERT TO query.expr_xcast
 501     DO INSTEAD
 502     INSERT INTO query.expression (
 503                 id,
 504                 type,
 505                 parenthesize,
 506                 parent_expr,
 507                 seq_no,
 508                 left_operand,
 509                 cast_type,
 510                 negate
 511     ) VALUES (
 512         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 513         'xcast',
 514         COALESCE(NEW.parenthesize, FALSE),
 515         NEW.parent_expr,
 516         COALESCE(NEW.seq_no, 1),
 517                 NEW.left_operand,
 518                 NEW.cast_type,
 519                 COALESCE(NEW.negate, false)
 520     );
 521
 522 CREATE OR REPLACE RULE query_expr_xcast_update_rule AS
 523     ON UPDATE TO query.expr_xcast
 524     DO INSTEAD
 525     UPDATE query.expression SET
 526         id = NEW.id,
 527         parenthesize = NEW.parenthesize,
 528         parent_expr = NEW.parent_expr,
 529         seq_no = NEW.seq_no,
 530                 left_operand = NEW.left_operand,
 531                 cast_type = NEW.cast_type,
 532                 negate = NEW.negate
 533     WHERE
 534         id = OLD.id;
 535
 536 CREATE OR REPLACE RULE query_expr_xcast_delete_rule AS
 537     ON DELETE TO query.expr_xcast
 538     DO INSTEAD
 539     DELETE FROM query.expression WHERE id = OLD.id;
 540
 541 -- Create updatable view for column expressions
 542
 543 CREATE OR REPLACE VIEW query.expr_xcol AS
 544     SELECT
 545                 id,
 546                 parenthesize,
 547                 parent_expr,
 548                 seq_no,
 549                 table_alias,
 550                 column_name,
 551                 negate
 552     FROM
 553         query.expression
 554     WHERE
 555         type = 'xcol';
 556
 557 CREATE OR REPLACE RULE query_expr_xcol_insert_rule AS
 558     ON INSERT TO query.expr_xcol
 559     DO INSTEAD
 560     INSERT INTO query.expression (
 561                 id,
 562                 type,
 563                 parenthesize,
 564                 parent_expr,
 565                 seq_no,
 566                 table_alias,
 567                 column_name,
 568                 negate
 569     ) VALUES (
 570         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 571         'xcol',
 572         COALESCE(NEW.parenthesize, FALSE),
 573         NEW.parent_expr,
 574         COALESCE(NEW.seq_no, 1),
 575                 NEW.table_alias,
 576                 NEW.column_name,
 577                 COALESCE(NEW.negate, false)
 578     );
 579
 580 CREATE OR REPLACE RULE query_expr_xcol_update_rule AS
 581     ON UPDATE TO query.expr_xcol
 582     DO INSTEAD
 583     UPDATE query.expression SET
 584         id = NEW.id,
 585         parenthesize = NEW.parenthesize,
 586         parent_expr = NEW.parent_expr,
 587         seq_no = NEW.seq_no,
 588                 table_alias = NEW.table_alias,
 589                 column_name = NEW.column_name,
 590                 negate = NEW.negate
 591     WHERE
 592         id = OLD.id;
 593
 594 CREATE OR REPLACE RULE query_expr_xcol_delete_rule AS
 595     ON DELETE TO query.expr_xcol
 596     DO INSTEAD
 597     DELETE FROM query.expression WHERE id = OLD.id;
 598
 599 -- Create updatable view for EXISTS expressions
 600
 601 CREATE OR REPLACE VIEW query.expr_xex AS
 602     SELECT
 603                 id,
 604                 parenthesize,
 605                 parent_expr,
 606                 seq_no,
 607                 subquery,
 608                 negate
 609     FROM
 610         query.expression
 611     WHERE
 612         type = 'xex';
 613
 614 CREATE OR REPLACE RULE query_expr_xex_insert_rule AS
 615     ON INSERT TO query.expr_xex
 616     DO INSTEAD
 617     INSERT INTO query.expression (
 618                 id,
 619                 type,
 620                 parenthesize,
 621                 parent_expr,
 622                 seq_no,
 623                 subquery,
 624                 negate
 625     ) VALUES (
 626         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 627         'xex',
 628         COALESCE(NEW.parenthesize, FALSE),
 629         NEW.parent_expr,
 630         COALESCE(NEW.seq_no, 1),
 631                 NEW.subquery,
 632                 COALESCE(NEW.negate, false)
 633     );
 634
 635 CREATE OR REPLACE RULE query_expr_xex_update_rule AS
 636     ON UPDATE TO query.expr_xex
 637     DO INSTEAD
 638     UPDATE query.expression SET
 639         id = NEW.id,
 640         parenthesize = NEW.parenthesize,
 641         parent_expr = NEW.parent_expr,
 642         seq_no = NEW.seq_no,
 643                 subquery = NEW.subquery,
 644                 negate = NEW.negate
 645     WHERE
 646         id = OLD.id;
 647
 648 CREATE OR REPLACE RULE query_expr_xex_delete_rule AS
 649     ON DELETE TO query.expr_xex
 650     DO INSTEAD
 651     DELETE FROM query.expression WHERE id = OLD.id;
 652
 653 -- Create updatable view for field expressions
 654
 655 CREATE OR REPLACE VIEW query.expr_xfld AS
 656     SELECT
 657                 id,
 658                 parenthesize,
 659                 parent_expr,
 660                 seq_no,
 661                 column_name,
 662                 left_operand,
 663                 negate
 664     FROM
 665         query.expression
 666     WHERE
 667         type = 'xfld';
 668
 669 CREATE OR REPLACE RULE query_expr_xfld_insert_rule AS
 670     ON INSERT TO query.expr_xfld
 671     DO INSTEAD
 672     INSERT INTO query.expression (
 673                 id,
 674                 type,
 675                 parenthesize,
 676                 parent_expr,
 677                 seq_no,
 678                 column_name,
 679                 left_operand,
 680                 negate
 681     ) VALUES (
 682         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 683         'xfld',
 684         COALESCE(NEW.parenthesize, FALSE),
 685         NEW.parent_expr,
 686         COALESCE(NEW.seq_no, 1),
 687                 NEW.column_name,
 688                 NEW.left_operand,
 689                 COALESCE(NEW.negate, false)
 690     );
 691
 692 CREATE OR REPLACE RULE query_expr_xfld_update_rule AS
 693     ON UPDATE TO query.expr_xfld
 694     DO INSTEAD
 695     UPDATE query.expression SET
 696         id = NEW.id,
 697         parenthesize = NEW.parenthesize,
 698         parent_expr = NEW.parent_expr,
 699         seq_no = NEW.seq_no,
 700                 column_name = NEW.column_name,
 701                 left_operand = NEW.left_operand,
 702                 negate = NEW.negate
 703     WHERE
 704         id = OLD.id;
 705
 706 CREATE OR REPLACE RULE query_expr_xfld_delete_rule AS
 707     ON DELETE TO query.expr_xfld
 708     DO INSTEAD
 709     DELETE FROM query.expression WHERE id = OLD.id;
 710
 711 -- Create updatable view for function call expressions
 712
 713 CREATE OR REPLACE VIEW query.expr_xfunc AS
 714     SELECT
 715                 id,
 716                 parenthesize,
 717                 parent_expr,
 718                 seq_no,
 719                 function_id,
 720                 negate
 721     FROM
 722         query.expression
 723     WHERE
 724         type = 'xfunc';
 725
 726 CREATE OR REPLACE RULE query_expr_xfunc_insert_rule AS
 727     ON INSERT TO query.expr_xfunc
 728     DO INSTEAD
 729     INSERT INTO query.expression (
 730                 id,
 731                 type,
 732                 parenthesize,
 733                 parent_expr,
 734                 seq_no,
 735                 function_id,
 736                 negate
 737     ) VALUES (
 738         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 739         'xfunc',
 740         COALESCE(NEW.parenthesize, FALSE),
 741         NEW.parent_expr,
 742         COALESCE(NEW.seq_no, 1),
 743                 NEW.function_id,
 744                 COALESCE(NEW.negate, false)
 745     );
 746
 747 CREATE OR REPLACE RULE query_expr_xfunc_update_rule AS
 748     ON UPDATE TO query.expr_xfunc
 749     DO INSTEAD
 750     UPDATE query.expression SET
 751         id = NEW.id,
 752         parenthesize = NEW.parenthesize,
 753         parent_expr = NEW.parent_expr,
 754         seq_no = NEW.seq_no,
 755                 function_id = NEW.function_id,
 756                 negate = NEW.negate
 757     WHERE
 758         id = OLD.id;
 759
 760 CREATE OR REPLACE RULE query_expr_xfunc_delete_rule AS
 761     ON DELETE TO query.expr_xfunc
 762     DO INSTEAD
 763     DELETE FROM query.expression WHERE id = OLD.id;
 764
 765 -- Create updatable view for IN expressions
 766
 767 CREATE OR REPLACE VIEW query.expr_xin AS
 768     SELECT
 769                 id,
 770                 parenthesize,
 771                 parent_expr,
 772                 seq_no,
 773                 left_operand,
 774                 subquery,
 775                 negate
 776     FROM
 777         query.expression
 778     WHERE
 779         type = 'xin';
 780
 781 CREATE OR REPLACE RULE query_expr_xin_insert_rule AS
 782     ON INSERT TO query.expr_xin
 783     DO INSTEAD
 784     INSERT INTO query.expression (
 785                 id,
 786                 type,
 787                 parenthesize,
 788                 parent_expr,
 789                 seq_no,
 790                 left_operand,
 791                 subquery,
 792                 negate
 793     ) VALUES (
 794         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 795         'xin',
 796         COALESCE(NEW.parenthesize, FALSE),
 797         NEW.parent_expr,
 798         COALESCE(NEW.seq_no, 1),
 799                 NEW.left_operand,
 800                 NEW.subquery,
 801                 COALESCE(NEW.negate, false)
 802     );
 803
 804 CREATE OR REPLACE RULE query_expr_xin_update_rule AS
 805     ON UPDATE TO query.expr_xin
 806     DO INSTEAD
 807     UPDATE query.expression SET
 808         id = NEW.id,
 809         parenthesize = NEW.parenthesize,
 810         parent_expr = NEW.parent_expr,
 811         seq_no = NEW.seq_no,
 812                 left_operand = NEW.left_operand,
 813                 subquery = NEW.subquery,
 814                 negate = NEW.negate
 815     WHERE
 816         id = OLD.id;
 817
 818 CREATE OR REPLACE RULE query_expr_xin_delete_rule AS
 819     ON DELETE TO query.expr_xin
 820     DO INSTEAD
 821     DELETE FROM query.expression WHERE id = OLD.id;
 822
 823 -- Create updatable view for IS NULL expressions
 824
 825 CREATE OR REPLACE VIEW query.expr_xisnull AS
 826     SELECT
 827                 id,
 828                 parenthesize,
 829                 parent_expr,
 830                 seq_no,
 831                 left_operand,
 832                 negate
 833     FROM
 834         query.expression
 835     WHERE
 836         type = 'xisnull';
 837
 838 CREATE OR REPLACE RULE query_expr_xisnull_insert_rule AS
 839     ON INSERT TO query.expr_xisnull
 840     DO INSTEAD
 841     INSERT INTO query.expression (
 842                 id,
 843                 type,
 844                 parenthesize,
 845                 parent_expr,
 846                 seq_no,
 847                 left_operand,
 848                 negate
 849     ) VALUES (
 850         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 851         'xisnull',
 852         COALESCE(NEW.parenthesize, FALSE),
 853         NEW.parent_expr,
 854         COALESCE(NEW.seq_no, 1),
 855                 NEW.left_operand,
 856                 COALESCE(NEW.negate, false)
 857     );
 858
 859 CREATE OR REPLACE RULE query_expr_xisnull_update_rule AS
 860     ON UPDATE TO query.expr_xisnull
 861     DO INSTEAD
 862     UPDATE query.expression SET
 863         id = NEW.id,
 864         parenthesize = NEW.parenthesize,
 865         parent_expr = NEW.parent_expr,
 866         seq_no = NEW.seq_no,
 867                 left_operand = NEW.left_operand,
 868                 negate = NEW.negate
 869     WHERE
 870         id = OLD.id;
 871
 872 CREATE OR REPLACE RULE query_expr_xisnull_delete_rule AS
 873     ON DELETE TO query.expr_xisnull
 874     DO INSTEAD
 875     DELETE FROM query.expression WHERE id = OLD.id;
 876
 877 -- Create updatable view for NULL expressions
 878
 879 CREATE OR REPLACE VIEW query.expr_xnull AS
 880     SELECT
 881                 id,
 882                 parenthesize,
 883                 parent_expr,
 884                 seq_no,
 885                 negate
 886     FROM
 887         query.expression
 888     WHERE
 889         type = 'xnull';
 890
 891 CREATE OR REPLACE RULE query_expr_xnull_insert_rule AS
 892     ON INSERT TO query.expr_xnull
 893     DO INSTEAD
 894     INSERT INTO query.expression (
 895                 id,
 896                 type,
 897                 parenthesize,
 898                 parent_expr,
 899                 seq_no,
 900                 negate
 901     ) VALUES (
 902         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 903         'xnull',
 904         COALESCE(NEW.parenthesize, FALSE),
 905         NEW.parent_expr,
 906         COALESCE(NEW.seq_no, 1),
 907                 COALESCE(NEW.negate, false)
 908     );
 909
 910 CREATE OR REPLACE RULE query_expr_xnull_update_rule AS
 911     ON UPDATE TO query.expr_xnull
 912     DO INSTEAD
 913     UPDATE query.expression SET
 914         id = NEW.id,
 915         parenthesize = NEW.parenthesize,
 916         parent_expr = NEW.parent_expr,
 917         seq_no = NEW.seq_no,
 918                 negate = NEW.negate
 919     WHERE
 920         id = OLD.id;
 921
 922 CREATE OR REPLACE RULE query_expr_xnull_delete_rule AS
 923     ON DELETE TO query.expr_xnull
 924     DO INSTEAD
 925     DELETE FROM query.expression WHERE id = OLD.id;
 926
 927 -- Create updatable view for numeric literal expressions
 928
 929 CREATE OR REPLACE VIEW query.expr_xnum AS
 930     SELECT
 931                 id,
 932                 parenthesize,
 933                 parent_expr,
 934                 seq_no,
 935                 literal
 936     FROM
 937         query.expression
 938     WHERE
 939         type = 'xnum';
 940
 941 CREATE OR REPLACE RULE query_expr_xnum_insert_rule AS
 942     ON INSERT TO query.expr_xnum
 943     DO INSTEAD
 944     INSERT INTO query.expression (
 945                 id,
 946                 type,
 947                 parenthesize,
 948                 parent_expr,
 949                 seq_no,
 950                 literal
 951     ) VALUES (
 952         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
 953         'xnum',
 954         COALESCE(NEW.parenthesize, FALSE),
 955         NEW.parent_expr,
 956         COALESCE(NEW.seq_no, 1),
 957         NEW.literal
 958     );
 959
 960 CREATE OR REPLACE RULE query_expr_xnum_update_rule AS
 961     ON UPDATE TO query.expr_xnum
 962     DO INSTEAD
 963     UPDATE query.expression SET
 964         id = NEW.id,
 965         parenthesize = NEW.parenthesize,
 966         parent_expr = NEW.parent_expr,
 967         seq_no = NEW.seq_no,
 968         literal = NEW.literal
 969     WHERE
 970         id = OLD.id;
 971
 972 CREATE OR REPLACE RULE query_expr_xnum_delete_rule AS
 973     ON DELETE TO query.expr_xnum
 974     DO INSTEAD
 975     DELETE FROM query.expression WHERE id = OLD.id;
 976
 977 -- Create updatable view for operator expressions
 978
 979 CREATE OR REPLACE VIEW query.expr_xop AS
 980     SELECT
 981                 id,
 982                 parenthesize,
 983                 parent_expr,
 984                 seq_no,
 985                 left_operand,
 986                 operator,
 987                 right_operand,
 988                 negate
 989     FROM
 990         query.expression
 991     WHERE
 992         type = 'xop';
 993
 994 CREATE OR REPLACE RULE query_expr_xop_insert_rule AS
 995     ON INSERT TO query.expr_xop
 996     DO INSTEAD
 997     INSERT INTO query.expression (
 998                 id,
 999                 type,
1000                 parenthesize,
1001                 parent_expr,
1002                 seq_no,
1003                 left_operand,
1004                 operator,
1005                 right_operand,
1006                 negate
1007     ) VALUES (
1008         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1009         'xop',
1010         COALESCE(NEW.parenthesize, FALSE),
1011         NEW.parent_expr,
1012         COALESCE(NEW.seq_no, 1),
1013                 NEW.left_operand,
1014                 NEW.operator,
1015                 NEW.right_operand,
1016                 COALESCE(NEW.negate, false)
1017     );
1018
1019 CREATE OR REPLACE RULE query_expr_xop_update_rule AS
1020     ON UPDATE TO query.expr_xop
1021     DO INSTEAD
1022     UPDATE query.expression SET
1023         id = NEW.id,
1024         parenthesize = NEW.parenthesize,
1025         parent_expr = NEW.parent_expr,
1026         seq_no = NEW.seq_no,
1027                 left_operand = NEW.left_operand,
1028                 operator = NEW.operator,
1029                 right_operand = NEW.right_operand,
1030                 negate = NEW.negate
1031     WHERE
1032         id = OLD.id;
1033
1034 CREATE OR REPLACE RULE query_expr_xop_delete_rule AS
1035     ON DELETE TO query.expr_xop
1036     DO INSTEAD
1037     DELETE FROM query.expression WHERE id = OLD.id;
1038
1039 -- Create updatable view for series expressions,
1040 -- i.e. series of expressions separated by operators
1041
1042 CREATE OR REPLACE VIEW query.expr_xser AS
1043     SELECT
1044                 id,
1045                 parenthesize,
1046                 parent_expr,
1047                 seq_no,
1048                 operator,
1049                 negate
1050     FROM
1051         query.expression
1052     WHERE
1053         type = 'xser';
1054
1055 CREATE OR REPLACE RULE query_expr_xser_insert_rule AS
1056     ON INSERT TO query.expr_xser
1057     DO INSTEAD
1058     INSERT INTO query.expression (
1059                 id,
1060                 type,
1061                 parenthesize,
1062                 parent_expr,
1063                 seq_no,
1064                 operator,
1065                 negate
1066     ) VALUES (
1067         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1068         'xser',
1069         COALESCE(NEW.parenthesize, FALSE),
1070         NEW.parent_expr,
1071         COALESCE(NEW.seq_no, 1),
1072                 NEW.operator,
1073                 COALESCE(NEW.negate, false)
1074     );
1075
1076 CREATE OR REPLACE RULE query_expr_xser_update_rule AS
1077     ON UPDATE TO query.expr_xser
1078     DO INSTEAD
1079     UPDATE query.expression SET
1080         id = NEW.id,
1081         parenthesize = NEW.parenthesize,
1082         parent_expr = NEW.parent_expr,
1083         seq_no = NEW.seq_no,
1084                 operator = NEW.operator,
1085                 negate = NEW.negate
1086     WHERE
1087         id = OLD.id;
1088
1089 CREATE OR REPLACE RULE query_expr_xser_delete_rule AS
1090     ON DELETE TO query.expr_xser
1091     DO INSTEAD
1092     DELETE FROM query.expression WHERE id = OLD.id;
1093
1094 -- Create updatable view for string literal expressions
1095
1096 CREATE OR REPLACE VIEW query.expr_xstr AS
1097     SELECT
1098         id,
1099         parenthesize,
1100         parent_expr,
1101         seq_no,
1102         literal
1103     FROM
1104         query.expression
1105     WHERE
1106         type = 'xstr';
1107
1108 CREATE OR REPLACE RULE query_expr_string_insert_rule AS
1109     ON INSERT TO query.expr_xstr
1110     DO INSTEAD
1111     INSERT INTO query.expression (
1112         id,
1113         type,
1114         parenthesize,
1115         parent_expr,
1116         seq_no,
1117         literal
1118     ) VALUES (
1119         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1120         'xstr',
1121         COALESCE(NEW.parenthesize, FALSE),
1122         NEW.parent_expr,
1123         COALESCE(NEW.seq_no, 1),
1124         NEW.literal
1125     );
1126
1127 CREATE OR REPLACE RULE query_expr_string_update_rule AS
1128     ON UPDATE TO query.expr_xstr
1129     DO INSTEAD
1130     UPDATE query.expression SET
1131         id = NEW.id,
1132         parenthesize = NEW.parenthesize,
1133         parent_expr = NEW.parent_expr,
1134         seq_no = NEW.seq_no,
1135         literal = NEW.literal
1136     WHERE
1137         id = OLD.id;
1138
1139 CREATE OR REPLACE RULE query_expr_string_delete_rule AS
1140     ON DELETE TO query.expr_xstr
1141     DO INSTEAD
1142     DELETE FROM query.expression WHERE id = OLD.id;
1143
1144 -- Create updatable view for subquery expressions
1145
1146 CREATE OR REPLACE VIEW query.expr_xsubq AS
1147     SELECT
1148                 id,
1149                 parenthesize,
1150                 parent_expr,
1151                 seq_no,
1152                 subquery,
1153                 negate
1154     FROM
1155         query.expression
1156     WHERE
1157         type = 'xsubq';
1158
1159 CREATE OR REPLACE RULE query_expr_xsubq_insert_rule AS
1160     ON INSERT TO query.expr_xsubq
1161     DO INSTEAD
1162     INSERT INTO query.expression (
1163                 id,
1164                 type,
1165                 parenthesize,
1166                 parent_expr,
1167                 seq_no,
1168                 subquery,
1169                 negate
1170     ) VALUES (
1171         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
1172         'xsubq',
1173         COALESCE(NEW.parenthesize, FALSE),
1174         NEW.parent_expr,
1175         COALESCE(NEW.seq_no, 1),
1176                 NEW.subquery,
1177                 COALESCE(NEW.negate, false)
1178     );
1179
1180 CREATE OR REPLACE RULE query_expr_xsubq_update_rule AS
1181     ON UPDATE TO query.expr_xsubq
1182     DO INSTEAD
1183     UPDATE query.expression SET
1184         id = NEW.id,
1185         parenthesize = NEW.parenthesize,
1186         parent_expr = NEW.parent_expr,
1187         seq_no = NEW.seq_no,
1188                 subquery = NEW.subquery,
1189                 negate = NEW.negate
1190     WHERE
1191         id = OLD.id;
1192
1193 CREATE OR REPLACE RULE query_expr_xsubq_delete_rule AS
1194     ON DELETE TO query.expr_xsubq
1195     DO INSTEAD
1196     DELETE FROM query.expression WHERE id = OLD.id;
1197
1198 COMMIT;