Open-ILS/src/sql/Pg/upgrade/0097.schema.sql-datatype.sql

   1 -- Script to create the query schema and the tables therein
   2
   3 BEGIN;
   4
   5 INSERT INTO config.upgrade_log (version) VALUES ('0097'); -- Scott McKellar
   6
   7 DROP SCHEMA IF EXISTS sql CASCADE;
   8 DROP SCHEMA IF EXISTS query CASCADE;
   9
  10 CREATE SCHEMA query;
  11
  12 CREATE TABLE query.datatype (
  13         id              SERIAL            PRIMARY KEY,
  14         datatype_name   TEXT              NOT NULL UNIQUE,
  15         is_numeric      BOOL              NOT NULL DEFAULT FALSE,
  16         is_composite    BOOL              NOT NULL DEFAULT FALSE,
  17         CONSTRAINT qdt_comp_not_num CHECK
  18         ( is_numeric IS FALSE OR is_composite IS FALSE )
  19 );
  20
  21 CREATE TABLE query.subfield (
  22         id              SERIAL            PRIMARY KEY,
  23         composite_type  INT               NOT NULL
  24                                           REFERENCES query.datatype(id)
  25                                           ON DELETE CASCADE
  26                                           DEFERRABLE INITIALLY DEFERRED,
  27         seq_no          INT               NOT NULL
  28                                           CONSTRAINT qsf_pos_seq_no
  29                                           CHECK( seq_no > 0 ),
  30         subfield_type   INT               NOT NULL
  31                                           REFERENCES query.datatype(id)
  32                                           DEFERRABLE INITIALLY DEFERRED,
  33         CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
  34 );
  35
  36 CREATE TABLE query.function_sig (
  37         id              SERIAL            PRIMARY KEY,
  38         function_name   TEXT              NOT NULL,
  39         return_type     INT               REFERENCES query.datatype(id)
  40                                           DEFERRABLE INITIALLY DEFERRED,
  41         is_aggregate    BOOL              NOT NULL DEFAULT FALSE,
  42         CONSTRAINT qfd_rtn_or_aggr CHECK
  43         ( return_type IS NULL OR is_aggregate = FALSE )
  44 );
  45
  46 CREATE INDEX query_function_sig_name_idx
  47         ON query.function_sig (function_name);
  48
  49 CREATE TABLE query.function_param_def (
  50         id              SERIAL            PRIMARY KEY,
  51         function_id     INT               NOT NULL
  52                                           REFERENCES query.function_sig( id )
  53                                           ON DELETE CASCADE
  54                                           DEFERRABLE INITIALLY DEFERRED,
  55         seq_no          INT               NOT NULL
  56                                           CONSTRAINT qfpd_pos_seq_no CHECK
  57                                           ( seq_no > 0 ),
  58         datatype        INT               NOT NULL
  59                                           REFERENCES query.datatype( id )
  60                                           DEFERRABLE INITIALLY DEFERRED,
  61         CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
  62 );
  63
  64 COMMIT;