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         elbow_room     INTERVAL,
  27         fine_interval  INTERVAL,
  28         fine_amount    DECIMAL(8,2)    NOT NULL DEFAULT 0,
  29         max_fine       DECIMAL(8,2),
  30         owner          INT             NOT NULL
  31                                        REFERENCES actor.org_unit( id )
  32                                        DEFERRABLE INITIALLY DEFERRED,
  33         catalog_item   BOOLEAN         NOT NULL DEFAULT FALSE,
  34         transferable   BOOLEAN         NOT NULL DEFAULT FALSE,
  35     record         BIGINT          REFERENCES biblio.record_entry (id)
  36                                    DEFERRABLE INITIALLY DEFERRED,
  37         CONSTRAINT brt_name_and_record_once_per_owner UNIQUE(owner, name, record)
  38 );
  39
  40 CREATE TABLE booking.resource (
  41         id             SERIAL           PRIMARY KEY,
  42         owner          INT              NOT NULL
  43                                         REFERENCES actor.org_unit(id)
  44                                         DEFERRABLE INITIALLY DEFERRED,
  45         type           INT              NOT NULL
  46                                         REFERENCES booking.resource_type(id)
  47                                         DEFERRABLE INITIALLY DEFERRED,
  48         overbook       BOOLEAN          NOT NULL DEFAULT FALSE,
  49         barcode        TEXT             NOT NULL,
  50         deposit        BOOLEAN          NOT NULL DEFAULT FALSE,
  51         deposit_amount DECIMAL(8,2)     NOT NULL DEFAULT 0.00,
  52         user_fee       DECIMAL(8,2)     NOT NULL DEFAULT 0.00,
  53         CONSTRAINT br_unique UNIQUE(owner, barcode)
  54 );
  55
  56 -- For non-catalog items: hijack barcode for name/description
  57
  58 CREATE TABLE booking.resource_attr (
  59         id              SERIAL          PRIMARY KEY,
  60         owner           INT             NOT NULL
  61                                         REFERENCES actor.org_unit(id)
  62                                         DEFERRABLE INITIALLY DEFERRED,
  63         name            TEXT            NOT NULL,
  64         resource_type   INT             NOT NULL
  65                                         REFERENCES booking.resource_type(id)
  66                                         ON DELETE CASCADE
  67                                         DEFERRABLE INITIALLY DEFERRED,
  68         required        BOOLEAN         NOT NULL DEFAULT FALSE,
  69         CONSTRAINT bra_name_once_per_type UNIQUE(resource_type, name)
  70 );
  71
  72 CREATE TABLE booking.resource_attr_value (
  73         id               SERIAL         PRIMARY KEY,
  74         owner            INT            NOT NULL
  75                                         REFERENCES actor.org_unit(id)
  76                                         DEFERRABLE INITIALLY DEFERRED,
  77         attr             INT            NOT NULL
  78                                         REFERENCES booking.resource_attr(id)
  79                                         DEFERRABLE INITIALLY DEFERRED,
  80         valid_value      TEXT           NOT NULL,
  81         CONSTRAINT brav_logical_key UNIQUE(owner, attr, valid_value)
  82 );
  83
  84 -- Do we still need a name column?
  85
  86
  87 CREATE TABLE booking.resource_attr_map (
  88         id               SERIAL         PRIMARY KEY,
  89         resource         INT            NOT NULL
  90                                         REFERENCES booking.resource(id)
  91                                         ON DELETE CASCADE
  92                                         DEFERRABLE INITIALLY DEFERRED,
  93         resource_attr    INT            NOT NULL
  94                                         REFERENCES booking.resource_attr(id)
  95                                         ON DELETE CASCADE
  96                                         DEFERRABLE INITIALLY DEFERRED,
  97         value            INT            NOT NULL
  98                                         REFERENCES booking.resource_attr_value(id)
  99                                         DEFERRABLE INITIALLY DEFERRED,
 100         CONSTRAINT bram_one_value_per_attr UNIQUE(resource, resource_attr)
 101 );
 102
 103 CREATE TABLE booking.reservation (
 104         request_time     TIMESTAMPTZ   NOT NULL DEFAULT now(),
 105         start_time       TIMESTAMPTZ,
 106         end_time         TIMESTAMPTZ,
 107         capture_time     TIMESTAMPTZ,
 108         cancel_time      TIMESTAMPTZ,
 109         pickup_time      TIMESTAMPTZ,
 110         return_time      TIMESTAMPTZ,
 111         booking_interval INTERVAL,
 112         fine_interval    INTERVAL,
 113         fine_amount      DECIMAL(8,2),
 114         max_fine         DECIMAL(8,2),
 115         target_resource_type  INT       NOT NULL
 116                                         REFERENCES booking.resource_type(id)
 117                                         ON DELETE CASCADE
 118                                         DEFERRABLE INITIALLY DEFERRED,
 119         target_resource  INT            REFERENCES booking.resource(id)
 120                                         ON DELETE CASCADE
 121                                         DEFERRABLE INITIALLY DEFERRED,
 122         current_resource INT            REFERENCES booking.resource(id)
 123                                         ON DELETE CASCADE
 124                                         DEFERRABLE INITIALLY DEFERRED,
 125         request_lib      INT            NOT NULL
 126                                         REFERENCES actor.org_unit(id)
 127                                         DEFERRABLE INITIALLY DEFERRED,
 128         pickup_lib       INT            REFERENCES actor.org_unit(id)
 129                                         DEFERRABLE INITIALLY DEFERRED,
 130         capture_staff    INT            REFERENCES actor.usr(id)
 131                                         DEFERRABLE INITIALLY DEFERRED,
 132         email_notify     BOOLEAN        NOT NULL DEFAULT FALSE
 133 ) INHERITS (money.billable_xact);
 134
 135 ALTER TABLE booking.reservation ADD PRIMARY KEY (id);
 136
 137 ALTER TABLE booking.reservation
 138         ADD CONSTRAINT booking_reservation_usr_fkey
 139         FOREIGN KEY (usr) REFERENCES actor.usr (id)
 140         DEFERRABLE INITIALLY DEFERRED;
 141
 142 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('reservation');
 143 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
 144 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
 145
 146
 147 CREATE TABLE booking.reservation_attr_value_map (
 148         id               SERIAL         PRIMARY KEY,
 149         reservation      INT            NOT NULL
 150                                         REFERENCES booking.reservation(id)
 151                                         ON DELETE CASCADE
 152                                         DEFERRABLE INITIALLY DEFERRED,
 153         attr_value       INT            NOT NULL
 154                                         REFERENCES booking.resource_attr_value(id)
 155                                         ON DELETE CASCADE
 156                                         DEFERRABLE INITIALLY DEFERRED,
 157         CONSTRAINT bravm_logical_key UNIQUE(reservation, attr_value)
 158 );
 159
 160 CREATE TABLE action.reservation_transit_copy (
 161     reservation    INT REFERENCES booking.reservation (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
 162 ) INHERITS (action.transit_copy);
 163 ALTER TABLE action.reservation_transit_copy ADD PRIMARY KEY (id);
 164 ALTER TABLE action.reservation_transit_copy ADD CONSTRAINT artc_tc_fkey FOREIGN KEY (target_copy) REFERENCES booking.resource (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
 165 CREATE INDEX active_reservation_transit_dest_idx ON "action".reservation_transit_copy (dest);
 166 CREATE INDEX active_reservation_transit_source_idx ON "action".reservation_transit_copy (source);
 167 CREATE INDEX active_reservation_transit_cp_idx ON "action".reservation_transit_copy (target_copy);
 168
 169 COMMIT;