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

   1 -- Script to create the sql 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
   9 CREATE SCHEMA sql;
  10
  11 CREATE TABLE sql.datatype (
  12         id              SERIAL            PRIMARY KEY,
  13         datatype_name   TEXT              NOT NULL UNIQUE,
  14         is_numeric      BOOL              NOT NULL DEFAULT FALSE,
  15         is_composite    BOOL              NOT NULL DEFAULT FALSE,
  16         CONSTRAINT qdt_comp_not_num CHECK
  17         ( is_numeric IS FALSE OR is_composite IS FALSE )
  18 );
  19
  20 CREATE TABLE sql.subfield (
  21         id              SERIAL            PRIMARY KEY,
  22         composite_type  INT               NOT NULL
  23                                           REFERENCES sql.datatype(id)
  24                                           ON DELETE CASCADE
  25                                           DEFERRABLE INITIALLY DEFERRED,
  26         seq_no          INT               NOT NULL
  27                                           CONSTRAINT qsf_pos_seq_no
  28                                           CHECK( seq_no > 0 ),
  29         subfield_type   INT               NOT NULL
  30                                           REFERENCES sql.datatype(id)
  31                                           DEFERRABLE INITIALLY DEFERRED,
  32         CONSTRAINT qsf_datatype_seq_no UNIQUE (composite_type, seq_no)
  33 );
  34
  35 CREATE TABLE sql.function_sig (
  36         id              SERIAL            PRIMARY KEY,
  37         function_name   TEXT              NOT NULL,
  38         return_type     INT               REFERENCES sql.datatype(id)
  39                                           DEFERRABLE INITIALLY DEFERRED,
  40         is_aggregate    BOOL              NOT NULL DEFAULT FALSE,
  41         CONSTRAINT qfd_rtn_or_aggr CHECK
  42         ( return_type IS NULL OR is_aggregate = FALSE )
  43 );
  44
  45 CREATE INDEX sql_function_sig_name_idx
  46         ON sql.function_sig (function_name);
  47
  48 CREATE TABLE sql.function_param_def (
  49         id              SERIAL            PRIMARY KEY,
  50         function_id     INT               NOT NULL
  51                                           REFERENCES sql.function_sig( id )
  52                                           ON DELETE CASCADE
  53                                           DEFERRABLE INITIALLY DEFERRED,
  54         seq_no          INT               NOT NULL
  55                                           CONSTRAINT qfpd_pos_seq_no CHECK
  56                                           ( seq_no > 0 ),
  57         datatype        INT               NOT NULL
  58                                           REFERENCES sql.datatype( id )
  59                                           DEFERRABLE INITIALLY DEFERRED,
  60         CONSTRAINT qfpd_function_param_seq UNIQUE (function_id, seq_no)
  61 );
  62
  63 COMMIT;