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