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

   1 /*
   2  * Copyright (C) 2009  Equinox Software, Inc.
   3  * Scott McKellar <scott@esilibrary.com>
   4  *
   5  * This program is free software; you can redistribute it and/or
   6  * modify it under the terms of the GNU General Public License
   7  * as published by the Free Software Foundation; either version 2
   8  * of the License, or (at your option) any later version.
   9  *
  10  * This program is distributed in the hope that it will be useful,
  11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13  * GNU General Public License for more details.
  14  *
  15  */
  16
  17 BEGIN;
  18
  19 DROP SCHEMA IF EXISTS booking CASCADE;
  20
  21 CREATE SCHEMA booking;
  22
  23 CREATE TABLE booking.resource_type (
  24         id             SERIAL          PRIMARY KEY,
  25         name           TEXT            NOT NULL,
  26         fine_interval  INTERVAL,
  27         fine_amount    DECIMAL(8,2)    NOT NULL DEFAULT 0,
  28         max_fine       DECIMAL(8,2),
  29         owner          INT             NOT NULL
  30                                        REFERENCES actor.org_unit( id )
  31                                        DEFERRABLE INITIALLY DEFERRED,
  32         catalog_item   BOOLEAN         NOT NULL DEFAULT FALSE,
  33         transferable   BOOLEAN         NOT NULL DEFAULT FALSE,
  34     record         INT             REFERENCES biblio.record_entry (id)
  35                                    DEFERRABLE INITIALLY DEFERRED,
  36         CONSTRAINT brt_name_once_per_owner UNIQUE(owner, name, record)
  37 );
  38
  39 CREATE TABLE booking.resource (
  40         id             SERIAL           PRIMARY KEY,
  41         owner          INT              NOT NULL
  42                                         REFERENCES actor.org_unit(id)
  43                                         DEFERRABLE INITIALLY DEFERRED,
  44         type           INT              NOT NULL
  45                                         REFERENCES booking.resource_type(id)
  46                                         DEFERRABLE INITIALLY DEFERRED,
  47         overbook       BOOLEAN          NOT NULL DEFAULT FALSE,
  48         barcode        TEXT             NOT NULL,
  49         deposit        BOOLEAN          NOT NULL DEFAULT FALSE,
  50         deposit_amount DECIMAL(8,2)     NOT NULL DEFAULT 0.00,
  51         user_fee       DECIMAL(8,2)     NOT NULL DEFAULT 0.00,
  52         CONSTRAINT br_unique UNIQUE(owner, type, barcode)
  53 );
  54
  55 -- For non-catalog items: hijack barcode for name/description
  56
  57 CREATE TABLE booking.resource_attr (
  58         id              SERIAL          PRIMARY KEY,
  59         owner           INT             NOT NULL
  60                                         REFERENCES actor.org_unit(id)
  61                                         DEFERRABLE INITIALLY DEFERRED,
  62         name            TEXT            NOT NULL,
  63         resource_type   INT             NOT NULL
  64                                         REFERENCES booking.resource_type(id)
  65                                         ON DELETE CASCADE
  66                                         DEFERRABLE INITIALLY DEFERRED,
  67         required        BOOLEAN         NOT NULL DEFAULT FALSE,
  68         CONSTRAINT bra_name_once_per_type UNIQUE(resource_type, name)
  69 );
  70
  71 CREATE TABLE booking.resource_attr_value (
  72         id               SERIAL         PRIMARY KEY,
  73         owner            INT            NOT NULL
  74                                         REFERENCES actor.org_unit(id)
  75                                         DEFERRABLE INITIALLY DEFERRED,
  76         attr             INT            NOT NULL
  77                                         REFERENCES booking.resource_attr(id)
  78                                         DEFERRABLE INITIALLY DEFERRED,
  79         valid_value      TEXT           NOT NULL,
  80         CONSTRAINT brav_logical_key UNIQUE(owner, attr, valid_value)
  81 );
  82
  83 -- Do we still need a name column?
  84
  85
  86 CREATE TABLE booking.resource_attr_map (
  87         id               SERIAL         PRIMARY KEY,
  88         resource         INT            NOT NULL
  89                                         REFERENCES booking.resource(id)
  90                                         ON DELETE CASCADE
  91                                         DEFERRABLE INITIALLY DEFERRED,
  92         resource_attr    INT            NOT NULL
  93                                         REFERENCES booking.resource_attr(id)
  94                                         ON DELETE CASCADE
  95                                         DEFERRABLE INITIALLY DEFERRED,
  96         value            INT            NOT NULL
  97                                         REFERENCES booking.resource_attr_value(id)
  98                                         DEFERRABLE INITIALLY DEFERRED,
  99         CONSTRAINT bram_one_value_per_attr UNIQUE(resource, resource_attr)
 100 );
 101
 102 CREATE TABLE booking.reservation (
 103         request_time     TIMESTAMPTZ   NOT NULL DEFAULT now(),
 104         start_time       TIMESTAMPTZ,
 105         end_time         TIMESTAMPTZ,
 106         capture_time     TIMESTAMPTZ,
 107         cancel_time      TIMESTAMPTZ,
 108         pickup_time      TIMESTAMPTZ,
 109         return_time      TIMESTAMPTZ,
 110         booking_interval INTERVAL,
 111         fine_interval    INTERVAL,
 112         fine_amount      DECIMAL(8,2),
 113         max_fine         DECIMAL(8,2),
 114         target_resource_type  INT       NOT NULL
 115                                         REFERENCES booking.resource_type(id)
 116                                         ON DELETE CASCADE
 117                                         DEFERRABLE INITIALLY DEFERRED,
 118         target_resource  INT            REFERENCES booking.resource(id)
 119                                         ON DELETE CASCADE
 120                                         DEFERRABLE INITIALLY DEFERRED,
 121         current_resource INT            REFERENCES booking.resource(id)
 122                                         ON DELETE CASCADE
 123                                         DEFERRABLE INITIALLY DEFERRED,
 124         request_lib      INT            NOT NULL
 125                                         REFERENCES actor.org_unit(id)
 126                                         DEFERRABLE INITIALLY DEFERRED,
 127         pickup_lib       INT            REFERENCES actor.org_unit(id)
 128                                         DEFERRABLE INITIALLY DEFERRED,
 129         capture_staff    INT            REFERENCES actor.usr(id)
 130                                         DEFERRABLE INITIALLY DEFERRED
 131 ) INHERITS (money.billable_xact);
 132
 133 ALTER TABLE booking.reservation ADD PRIMARY KEY (id);
 134
 135 ALTER TABLE booking.reservation
 136         ADD CONSTRAINT booking_reservation_usr_fkey
 137         FOREIGN KEY (usr) REFERENCES actor.usr (id)
 138         DEFERRABLE INITIALLY DEFERRED;
 139
 140 CREATE TABLE booking.reservation_attr_value_map (
 141         id               SERIAL         PRIMARY KEY,
 142         reservation      INT            NOT NULL
 143                                         REFERENCES booking.reservation(id)
 144                                         ON DELETE CASCADE
 145                                         DEFERRABLE INITIALLY DEFERRED,
 146         attr_value       INT            NOT NULL
 147                                         REFERENCES booking.resource_attr_value(id)
 148                                         ON DELETE CASCADE
 149                                         DEFERRABLE INITIALLY DEFERRED,
 150         CONSTRAINT bravm_logical_key UNIQUE(reservation, attr_value)
 151 );
 152
 153 COMMIT;