Open-ILS/src/sql/Pg/upgrade/0099.schema.expression.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0099'); -- Scott McKellar
   4
   5 CREATE TABLE query.expression (
   6         id            SERIAL        PRIMARY KEY,
   7         type          TEXT          NOT NULL CONSTRAINT predicate_type CHECK
   8                                     ( type IN (
   9                                         'xbet',    -- between
  10                                                                         'xbool',   -- boolean
  11                                         'xcase',   -- case
  12                                                                         'xcast',   -- cast
  13                                                                         'xcol',    -- column
  14                                                                         'xex',     -- exists
  15                                                                         'xfld',    -- field
  16                                                                         'xfunc',   -- function
  17                                                                         'xin',     -- in
  18                                                                         'xnbet',   -- not between
  19                                         'xnex',    -- not exists
  20                                                                         'xnin',    -- not in
  21                                         'xnull',   -- null
  22                                                                         'xnum',    -- number
  23                                                                         'xop',     -- operator
  24                                                                         'xstr',    -- string
  25                                                 'xsubq'    -- subquery
  26                                                                 ) ),
  27         parenthesize  BOOL          NOT NULL DEFAULT FALSE,
  28         parent_expr   INT           REFERENCES query.expression
  29                                     ON DELETE CASCADE
  30                                     DEFERRABLE INITIALLY DEFERRED,
  31         seq_no        INT           NOT NULL DEFAULT 1,
  32         literal       TEXT,
  33         table_alias   TEXT,
  34         column_name   TEXT,
  35         left_operand  INT           REFERENCES query.expression
  36                                     DEFERRABLE INITIALLY DEFERRED,
  37         operator      TEXT,
  38         right_operand INT           REFERENCES query.expression
  39                                     DEFERRABLE INITIALLY DEFERRED,
  40         function_id   INT           REFERENCES query.function_sig
  41                                     DEFERRABLE INITIALLY DEFERRED,
  42         subquery      INT           REFERENCES query.stored_query
  43                                     DEFERRABLE INITIALLY DEFERRED,
  44         cast_type     INT           REFERENCES query.datatype
  45                                     DEFERRABLE INITIALLY DEFERRED
  46 );
  47
  48 CREATE UNIQUE INDEX query_expr_parent_seq
  49         ON query.expression( parent_expr, seq_no )
  50         WHERE parent_expr IS NOT NULL;
  51
  52 -- Due to some circular references, the following foreign key definitions
  53 -- had to be deferred until query.expression existed:
  54
  55 ALTER TABLE query.stored_query
  56         ADD FOREIGN KEY ( where_clause )
  57         REFERENCES query.expression( id )
  58         DEFERRABLE INITIALLY DEFERRED;
  59
  60 ALTER TABLE query.stored_query
  61         ADD FOREIGN KEY ( having_clause )
  62         REFERENCES query.expression( id )
  63         DEFERRABLE INITIALLY DEFERRED;
  64
  65 COMMIT;