Open-ILS/src/sql/Pg/upgrade/0086.schema.booking-tables.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0086');
   4
   5 DROP SCHEMA IF EXISTS booking CASCADE;
   6
   7 CREATE SCHEMA booking;
   8
   9 CREATE TABLE booking.resource_type (
  10         id             SERIAL          PRIMARY KEY,
  11         name           TEXT            NOT NULL,
  12         fine_interval  INTERVAL,
  13         fine_amount    DECIMAL(8,2)    NOT NULL DEFAULT 0,
  14         owner          INT             NOT NULL
  15                                        REFERENCES actor.org_unit( id )
  16                                        DEFERRABLE INITIALLY DEFERRED,
  17         catalog_item   BOOLEAN         NOT NULL DEFAULT FALSE,
  18         transferable   BOOLEAN         NOT NULL DEFAULT FALSE,
  19         CONSTRAINT brt_name_once_per_owner UNIQUE(owner, name)
  20 );
  21
  22 CREATE TABLE booking.resource (
  23         id             SERIAL           PRIMARY KEY,
  24         owner          INT              NOT NULL
  25                                         REFERENCES actor.org_unit(id)
  26                                         DEFERRABLE INITIALLY DEFERRED,
  27         type           INT              NOT NULL
  28                                         REFERENCES booking.resource_type(id)
  29                                         DEFERRABLE INITIALLY DEFERRED,
  30         overbook       BOOLEAN          NOT NULL DEFAULT FALSE,
  31         barcode        TEXT             NOT NULL,
  32         deposit        BOOLEAN          NOT NULL DEFAULT FALSE,
  33         deposit_amount DECIMAL(8,2)     NOT NULL DEFAULT 0.00,
  34         user_fee       DECIMAL(8,2)     NOT NULL DEFAULT 0.00,
  35         CONSTRAINT br_unique UNIQUE(owner, type, barcode)
  36 );
  37
  38 -- For non-catalog items: hijack barcode for name/description
  39
  40 CREATE TABLE booking.resource_attr (
  41         id              SERIAL          PRIMARY KEY,
  42         owner           INT             NOT NULL
  43                                         REFERENCES actor.org_unit(id)
  44                                         DEFERRABLE INITIALLY DEFERRED,
  45         name            TEXT            NOT NULL,
  46         resource_type   INT             NOT NULL
  47                                         REFERENCES booking.resource_type(id)
  48                                         ON DELETE CASCADE
  49                                         DEFERRABLE INITIALLY DEFERRED,
  50         required        BOOLEAN         NOT NULL DEFAULT FALSE,
  51         CONSTRAINT bra_name_once_per_type UNIQUE(resource_type, name)
  52 );
  53
  54 CREATE TABLE booking.resource_attr_value (
  55         id               SERIAL         PRIMARY KEY,
  56         owner            INT            NOT NULL
  57                                         REFERENCES actor.org_unit(id)
  58                                         DEFERRABLE INITIALLY DEFERRED,
  59         attr             INT            NOT NULL
  60                                         REFERENCES booking.resource_attr(id)
  61                                         DEFERRABLE INITIALLY DEFERRED,
  62         valid_value      TEXT           NOT NULL,
  63         CONSTRAINT brav_logical_key UNIQUE(owner, attr, valid_value)
  64 );
  65
  66 -- Do we still need a name column?
  67
  68
  69 CREATE TABLE booking.resource_attr_map (
  70         id               SERIAL         PRIMARY KEY,
  71         resource         INT            NOT NULL
  72                                         REFERENCES booking.resource(id)
  73                                         ON DELETE CASCADE
  74                                         DEFERRABLE INITIALLY DEFERRED,
  75         resource_attr    INT            NOT NULL
  76                                         REFERENCES booking.resource_attr(id)
  77                                         ON DELETE CASCADE
  78                                         DEFERRABLE INITIALLY DEFERRED,
  79         value            INT            NOT NULL
  80                                         REFERENCES booking.resource_attr_value(id)
  81                                         DEFERRABLE INITIALLY DEFERRED,
  82         CONSTRAINT bram_one_value_per_attr UNIQUE(resource, resource_attr)
  83 );
  84
  85 CREATE TABLE booking.reservation (
  86         request_time     TIMESTAMPTZ   NOT NULL DEFAULT now(),
  87         start_time       TIMESTAMPTZ,
  88         end_time         TIMESTAMPTZ,
  89         capture_time     TIMESTAMPTZ,
  90         cancel_time      TIMESTAMPTZ,
  91         pickup_time      TIMESTAMPTZ,
  92         return_time      TIMESTAMPTZ,
  93         booking_interval INTERVAL,
  94         fine_interval    INTERVAL,
  95         fine_amount      DECIMAL(8,2),
  96         target_resource_type  INT       NOT NULL
  97                                         REFERENCES booking.resource_type(id)
  98                                         ON DELETE CASCADE
  99                                         DEFERRABLE INITIALLY DEFERRED,
 100         target_resource  INT            REFERENCES booking.resource(id)
 101                                         ON DELETE CASCADE
 102                                         DEFERRABLE INITIALLY DEFERRED,
 103         current_resource INT            REFERENCES booking.resource(id)
 104                                         ON DELETE CASCADE
 105                                         DEFERRABLE INITIALLY DEFERRED,
 106         request_lib      INT            NOT NULL
 107                                         REFERENCES actor.org_unit(id)
 108                                         DEFERRABLE INITIALLY DEFERRED,
 109         pickup_lib       INT            REFERENCES actor.org_unit(id)
 110                                         DEFERRABLE INITIALLY DEFERRED,
 111         capture_staff    INT            REFERENCES actor.usr(id)
 112                                         DEFERRABLE INITIALLY DEFERRED
 113 ) INHERITS (money.billable_xact);
 114
 115 ALTER TABLE booking.reservation ADD PRIMARY KEY (id);
 116
 117 ALTER TABLE booking.reservation
 118         ADD CONSTRAINT booking_reservation_usr_fkey
 119         FOREIGN KEY (usr) REFERENCES actor.usr (id)
 120         DEFERRABLE INITIALLY DEFERRED;
 121
 122 CREATE TABLE booking.reservation_attr_value_map (
 123         id               SERIAL         PRIMARY KEY,
 124         reservation      INT            NOT NULL
 125                                         REFERENCES booking.reservation(id)
 126                                         ON DELETE CASCADE
 127                                         DEFERRABLE INITIALLY DEFERRED,
 128         attr_value       INT            NOT NULL
 129                                         REFERENCES booking.resource_attr_value(id)
 130                                         ON DELETE CASCADE
 131                                         DEFERRABLE INITIALLY DEFERRED,
 132         CONSTRAINT bravm_logical_key UNIQUE(reservation, attr_value)
 133 );
 134
 135 COMMIT;