Open-ILS/src/sql/Pg/upgrade/0288.schema.serial-overhaul.sql

   1 -- The following DROP statements are outside of the transaction.
   2 -- That way if one of the tables doesn't exist, the DROP will
   3 -- fail but the rest of the script can still run.
   4
   5 DROP TABLE serial.bib_summary CASCADE;
   6
   7 DROP TABLE serial.index_summary CASCADE;
   8
   9 DROP TABLE serial.sup_summary CASCADE;
  10
  11 DROP TABLE serial.issuance CASCADE;
  12
  13 DROP TABLE serial.binding_unit CASCADE;
  14
  15 DROP TABLE serial.subscription CASCADE;
  16
  17 BEGIN;
  18
  19 INSERT INTO config.upgrade_log (version) VALUES ('0288'); -- Scott McKellar
  20
  21 CREATE TABLE asset.copy_template (
  22         id             SERIAL   PRIMARY KEY,
  23         owning_lib     INT      NOT NULL
  24                                 REFERENCES actor.org_unit (id)
  25                                 DEFERRABLE INITIALLY DEFERRED,
  26         creator        BIGINT   NOT NULL
  27                                 REFERENCES actor.usr (id)
  28                                 DEFERRABLE INITIALLY DEFERRED,
  29         editor         BIGINT   NOT NULL
  30                                 REFERENCES actor.usr (id)
  31                                 DEFERRABLE INITIALLY DEFERRED,
  32         create_date    TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
  33         edit_date      TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
  34         name           TEXT     NOT NULL,
  35         -- columns above this point are attributes of the template itself
  36         -- columns after this point are attributes of the copy this template modifies/creates
  37         circ_lib       INT      REFERENCES actor.org_unit (id)
  38                                 DEFERRABLE INITIALLY DEFERRED,
  39         status         INT      REFERENCES config.copy_status (id)
  40                                 DEFERRABLE INITIALLY DEFERRED,
  41         location       INT      REFERENCES asset.copy_location (id)
  42                                 DEFERRABLE INITIALLY DEFERRED,
  43         loan_duration  INT      CONSTRAINT valid_loan_duration CHECK (
  44                                     loan_duration IS NULL OR loan_duration IN (1,2,3)),
  45         fine_level     INT      CONSTRAINT valid_fine_level CHECK (
  46                                     fine_level IS NULL OR loan_duration IN (1,2,3)),
  47         age_protect    INT,
  48         circulate      BOOL,
  49         deposit        BOOL,
  50         ref            BOOL,
  51         holdable       BOOL,
  52         deposit_amount NUMERIC(6,2),
  53         price          NUMERIC(8,2),
  54         circ_modifier  TEXT,
  55         circ_as_type   TEXT,
  56         alert_message  TEXT,
  57         opac_visible   BOOL,
  58         floating       BOOL,
  59         mint_condition BOOL
  60 );
  61
  62 CREATE TABLE serial.subscription (
  63         id                     SERIAL       PRIMARY KEY,
  64         start_date             TIMESTAMP WITH TIME ZONE     NOT NULL,
  65         end_date               TIMESTAMP WITH TIME ZONE,    -- interpret NULL as current subscription
  66         record_entry           BIGINT       REFERENCES serial.record_entry (id)
  67                                             ON DELETE SET NULL
  68                                             DEFERRABLE INITIALLY DEFERRED,
  69         expected_date_offset   INTERVAL
  70         -- acquisitions/business-side tables link to here
  71 );
  72
  73 --at least one distribution per org_unit holding issues
  74 CREATE TABLE serial.distribution (
  75         id                    SERIAL  PRIMARY KEY,
  76         subscription          INT     NOT NULL
  77                                       REFERENCES serial.subscription (id)
  78                                                                   ON DELETE CASCADE
  79                                                                   DEFERRABLE INITIALLY DEFERRED,
  80         holding_lib           INT     NOT NULL
  81                                       REFERENCES actor.org_unit (id)
  82                                                                   DEFERRABLE INITIALLY DEFERRED,
  83         label                 TEXT    NOT NULL,
  84         receive_call_number   BIGINT  REFERENCES asset.call_number (id)
  85                                       DEFERRABLE INITIALLY DEFERRED,
  86         receive_unit_template INT     REFERENCES asset.copy_template (id)
  87                                       DEFERRABLE INITIALLY DEFERRED,
  88         bind_call_number      BIGINT  REFERENCES asset.call_number (id)
  89                                       DEFERRABLE INITIALLY DEFERRED,
  90         bind_unit_template    INT     REFERENCES asset.copy_template (id)
  91                                       DEFERRABLE INITIALLY DEFERRED,
  92         unit_label_base       TEXT,
  93         unit_label_suffix     TEXT
  94 );
  95
  96 CREATE TABLE serial.stream (
  97         id              SERIAL  PRIMARY KEY,
  98         distribution    INT     NOT NULL
  99                                 REFERENCES serial.distribution (id)
 100                                 ON DELETE CASCADE
 101                                 DEFERRABLE INITIALLY DEFERRED,
 102         routing_label   TEXT
 103 );
 104
 105 CREATE UNIQUE INDEX label_once_per_dist
 106         ON serial.stream (distribution, routing_label)
 107         WHERE routing_label IS NOT NULL;
 108
 109 CREATE TABLE serial.routing_list_user (
 110         id             SERIAL       PRIMARY KEY,
 111         stream         INT          NOT NULL
 112                                     REFERENCES serial.stream
 113                                     ON DELETE CASCADE
 114                                     DEFERRABLE INITIALLY DEFERRED,
 115         pos            INT          NOT NULL DEFAULT 1,
 116         reader         INT          REFERENCES actor.usr
 117                                     ON DELETE CASCADE
 118                                     DEFERRABLE INITIALLY DEFERRED,
 119         department     TEXT,
 120         note           TEXT,
 121         CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
 122         CONSTRAINT reader_or_dept CHECK
 123         (
 124             -- Recipient is a person or a department, but not both
 125                 (reader IS NOT NULL AND department IS NULL) OR
 126                 (reader IS NULL AND department IS NOT NULL)
 127         )
 128 );
 129
 130 CREATE TABLE serial.issuance (
 131         id              SERIAL    PRIMARY KEY,
 132         creator         INT       NOT NULL
 133                                   REFERENCES actor.usr (id)
 134                                                           DEFERRABLE INITIALLY DEFERRED,
 135         editor          INT       NOT NULL
 136                                   REFERENCES actor.usr (id)
 137                                   DEFERRABLE INITIALLY DEFERRED,
 138         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
 139         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
 140         subscription    INT       NOT NULL
 141                                   REFERENCES serial.subscription (id)
 142                                   ON DELETE CASCADE
 143                                   DEFERRABLE INITIALLY DEFERRED,
 144         label           TEXT,
 145         date_published  TIMESTAMP WITH TIME ZONE,
 146         holding_code    TEXT,
 147         holding_type    TEXT      CONSTRAINT valid_holding_type CHECK
 148                                   (
 149                                       holding_type IS NULL
 150                                       OR holding_type IN ('basic','supplement','index')
 151                                   ),
 152         holding_link_id INT
 153         -- TODO: add columns for separate enumeration/chronology values
 154 );
 155
 156 CREATE TABLE serial.unit (
 157         label           TEXT,
 158         label_sort_key  TEXT,
 159         contents        TEXT    NOT NULL
 160 ) INHERITS (asset.copy);
 161
 162 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
 163
 164 CREATE TABLE serial.item (
 165         id              SERIAL  PRIMARY KEY,
 166         creator         INT     NOT NULL
 167                                 REFERENCES actor.usr (id)
 168                                 DEFERRABLE INITIALLY DEFERRED,
 169         editor          INT     NOT NULL
 170                                 REFERENCES actor.usr (id)
 171                                 DEFERRABLE INITIALLY DEFERRED,
 172         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
 173         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
 174         issuance        INT     NOT NULL
 175                                 REFERENCES serial.issuance (id)
 176                                 ON DELETE CASCADE
 177                                 DEFERRABLE INITIALLY DEFERRED,
 178         stream          INT     NOT NULL
 179                                 REFERENCES serial.stream (id)
 180                                 ON DELETE CASCADE
 181                                 DEFERRABLE INITIALLY DEFERRED,
 182         unit            INT     REFERENCES serial.unit (id)
 183                                 ON DELETE SET NULL
 184                                 DEFERRABLE INITIALLY DEFERRED,
 185         uri             INT     REFERENCES asset.uri (id)
 186                                 ON DELETE SET NULL
 187                                 DEFERRABLE INITIALLY DEFERRED,
 188         date_expected   TIMESTAMP WITH TIME ZONE,
 189         date_received   TIMESTAMP WITH TIME ZONE
 190 );
 191
 192 CREATE TABLE serial.item_note (
 193         id          SERIAL  PRIMARY KEY,
 194         item        INT     NOT NULL
 195                             REFERENCES serial.item (id)
 196                             ON DELETE CASCADE
 197                             DEFERRABLE INITIALLY DEFERRED,
 198         creator     INT     NOT NULL
 199                             REFERENCES actor.usr (id)
 200                             DEFERRABLE INITIALLY DEFERRED,
 201         create_date TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
 202         pub         BOOL    NOT NULL    DEFAULT FALSE,
 203         title       TEXT    NOT NULL,
 204         value       TEXT    NOT NULL
 205 );
 206
 207 CREATE TABLE serial.bib_summary (
 208         id                  SERIAL  PRIMARY KEY,
 209         distribution        INT     NOT NULL
 210                                     REFERENCES serial.distribution (id)
 211                                     ON DELETE CASCADE
 212                                     DEFERRABLE INITIALLY DEFERRED,
 213         generated_coverage  TEXT    NOT NULL,
 214         textual_holdings    TEXT
 215 );
 216
 217 CREATE TABLE serial.sup_summary (
 218         id                  SERIAL  PRIMARY KEY,
 219         distribution        INT     NOT NULL
 220                                     REFERENCES serial.distribution (id)
 221                                     ON DELETE CASCADE
 222                                     DEFERRABLE INITIALLY DEFERRED,
 223         generated_coverage  TEXT    NOT NULL,
 224         textual_holdings    TEXT
 225 );
 226
 227 CREATE TABLE serial.index_summary (
 228         id                  SERIAL  PRIMARY KEY,
 229         distribution        INT     NOT NULL
 230                                     REFERENCES serial.distribution (id)
 231                                     ON DELETE CASCADE
 232                                     DEFERRABLE INITIALLY DEFERRED,
 233         generated_coverage  TEXT    NOT NULL,
 234         textual_holdings    TEXT
 235 );
 236
 237 COMMIT;