Open-ILS/src/sql/Pg/upgrade/0102.schema.query-col-sel-order.sql

   1 -- Script to create the query schema and the tables therein
   2
   3 BEGIN;
   4
   5 INSERT INTO config.upgrade_log (version) VALUES ('0102'); -- Scott McKellar
   6
   7 CREATE TABLE query.record_column (
   8         id            SERIAL            PRIMARY KEY,
   9         from_relation INT               NOT NULL REFERENCES query.from_relation
  10                                         ON DELETE CASCADE
  11                                         DEFERRABLE INITIALLY DEFERRED,
  12         seq_no        INT               NOT NULL,
  13         column_name   TEXT              NOT NULL,
  14         column_type   INT               NOT NULL REFERENCES query.datatype
  15                                         ON DELETE CASCADE
  16                                                                         DEFERRABLE INITIALLY DEFERRED,
  17         CONSTRAINT column_sequence UNIQUE (from_relation, seq_no)
  18 );
  19
  20 CREATE TABLE query.select_item (
  21         id               SERIAL         PRIMARY KEY,
  22         stored_query     INT            NOT NULL REFERENCES query.stored_query
  23                                         ON DELETE CASCADE
  24                                         DEFERRABLE INITIALLY DEFERRED,
  25         seq_no           INT            NOT NULL,
  26         expression       INT            NOT NULL REFERENCES query.expression
  27                                         DEFERRABLE INITIALLY DEFERRED,
  28         column_alias     TEXT,
  29         grouped_by       BOOL           NOT NULL DEFAULT FALSE,
  30         CONSTRAINT select_sequence UNIQUE( stored_query, seq_no )
  31 );
  32
  33 CREATE TABLE query.order_by_item (
  34         id               SERIAL         PRIMARY KEY,
  35         stored_query     INT            NOT NULL REFERENCES query.stored_query
  36                                         ON DELETE CASCADE
  37                                         DEFERRABLE INITIALLY DEFERRED,
  38         seq_no           INT            NOT NULL,
  39         expression       INT            NOT NULL REFERENCES query.expression
  40                                         ON DELETE CASCADE
  41                                         DEFERRABLE INITIALLY DEFERRED,
  42         CONSTRAINT order_by_sequence UNIQUE( stored_query, seq_no )
  43 );
  44
  45 COMMIT;