Open-ILS/src/sql/Pg/upgrade/0098.schema.sql-stored-query.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0098'); -- Scott McKellar
   4
   5 CREATE TABLE  query.stored_query (
   6         id            SERIAL         PRIMARY KEY,
   7         type          TEXT           NOT NULL CONSTRAINT query_type CHECK
   8                                      ( type IN ( 'SELECT', 'UNION', 'INTERSECT', 'EXCEPT' ) ),
   9         use_all       BOOLEAN        NOT NULL DEFAULT FALSE,
  10         use_distinct  BOOLEAN        NOT NULL DEFAULT FALSE,
  11         from_clause   INT            NOT NULL , --REFERENCES query.from_clause
  12         where_clause  INT            , --REFERENCES query.expression
  13                                      --DEFERRABLE INITIALLY DEFERRED,
  14         having_clause INT            --REFERENCES query.expression
  15                                      --DEFERRABLE INITIALLY DEFERRED
  16 );
  17
  18 -- (Foreign keys to be defined later after other tables are created)
  19
  20 CREATE TABLE query.query_sequence (
  21         id              SERIAL            PRIMARY KEY,
  22         parent_query    INT               NOT NULL
  23                                           REFERENCES query.stored_query
  24                                                                           ON DELETE CASCADE
  25                                                                           DEFERRABLE INITIALLY DEFERRED,
  26         seq_no          INT               NOT NULL,
  27         child_query     INT               NOT NULL
  28                                           REFERENCES query.stored_query
  29                                                                           ON DELETE CASCADE
  30                                                                           DEFERRABLE INITIALLY DEFERRED,
  31         CONSTRAINT query_query_seq UNIQUE( parent_query, seq_no )
  32 );
  33
  34 COMMIT;