Open-ILS/src/sql/Pg/095.schema.booking.sql

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