From cf7dfb688427e43fb6bd3c9da7855abe75c92c8f Mon Sep 17 00:00:00 2001 From: scottmk Date: Mon, 16 Nov 2009 17:59:10 +0000 Subject: [PATCH] Create booking schema and the tables therein. A Open-ILS/src/sql/Pg/095.schema.booking.sql M Open-ILS/src/sql/Pg/002.schema.config.sql A Open-ILS/src/sql/Pg/upgrade/0086.schema.booking-tables.sql M Open-ILS/examples/fm_IDL.xml git-svn-id: svn://svn.open-ils.org/ILS/trunk@14921 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 186 +++++++++++++++++++++ Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/095.schema.booking.sql | 131 +++++++++++++++ .../sql/Pg/upgrade/0086.schema.booking-tables.sql | 135 +++++++++++++++ 4 files changed, 453 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/095.schema.booking.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/0086.schema.booking-tables.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 4c2ef6854e..59c7e639d0 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -1707,6 +1707,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -1737,6 +1738,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -2330,6 +2332,177 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -2852,6 +3025,13 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + @@ -2873,6 +3053,12 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 22099db0c1..27d1205e0e 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0085'); -- dbs +INSERT INTO config.upgrade_log (version) VALUES ('0086'); -- Scott McKellar CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/095.schema.booking.sql b/Open-ILS/src/sql/Pg/095.schema.booking.sql new file mode 100644 index 0000000000..4d0f2c547b --- /dev/null +++ b/Open-ILS/src/sql/Pg/095.schema.booking.sql @@ -0,0 +1,131 @@ +BEGIN; + +CREATE SCHEMA booking; + +CREATE TABLE booking.resource_type ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL, + fine_interval INTERVAL, + fine_amount DECIMAL(8,2) NOT NULL DEFAULT 0, + owner INT NOT NULL + REFERENCES actor.org_unit( id ) + DEFERRABLE INITIALLY DEFERRED, + catalog_item BOOLEAN NOT NULL DEFAULT FALSE, + transferable BOOLEAN NOT NULL DEFAULT FALSE, + CONSTRAINT brt_name_once_per_owner UNIQUE(owner, name) +); + +CREATE TABLE booking.resource ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL + REFERENCES actor.org_unit(id) + DEFERRABLE INITIALLY DEFERRED, + type INT NOT NULL + REFERENCES booking.resource_type(id) + DEFERRABLE INITIALLY DEFERRED, + overbook BOOLEAN NOT NULL DEFAULT FALSE, + barcode TEXT NOT NULL, + deposit BOOLEAN NOT NULL DEFAULT FALSE, + deposit_amount DECIMAL(8,2) NOT NULL DEFAULT 0.00, + user_fee DECIMAL(8,2) NOT NULL DEFAULT 0.00, + CONSTRAINT br_unique UNIQUE(owner, type, barcode) +); + +-- For non-catalog items: hijack barcode for name/description + +CREATE TABLE booking.resource_attr ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL + REFERENCES actor.org_unit(id) + DEFERRABLE INITIALLY DEFERRED, + name TEXT NOT NULL, + resource_type INT NOT NULL + REFERENCES booking.resource_type(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + required BOOLEAN NOT NULL DEFAULT FALSE, + CONSTRAINT bra_name_once_per_type UNIQUE(resource_type, name) +); + +CREATE TABLE booking.resource_attr_value ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL + REFERENCES actor.org_unit(id) + DEFERRABLE INITIALLY DEFERRED, + attr INT NOT NULL + REFERENCES booking.resource_attr(id) + DEFERRABLE INITIALLY DEFERRED, + valid_value TEXT NOT NULL, + CONSTRAINT brav_logical_key UNIQUE(owner, attr, valid_value) +); + +-- Do we still need a name column? + + +CREATE TABLE booking.resource_attr_map ( + id SERIAL PRIMARY KEY, + resource INT NOT NULL + REFERENCES booking.resource(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + resource_attr INT NOT NULL + REFERENCES booking.resource_attr(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + value INT NOT NULL + REFERENCES booking.resource_attr_value(id) + DEFERRABLE INITIALLY DEFERRED, + CONSTRAINT bram_one_value_per_attr UNIQUE(resource, resource_attr) +); + +CREATE TABLE booking.reservation ( + request_time TIMESTAMPTZ NOT NULL DEFAULT now(), + start_time TIMESTAMPTZ, + end_time TIMESTAMPTZ, + capture_time TIMESTAMPTZ, + cancel_time TIMESTAMPTZ, + pickup_time TIMESTAMPTZ, + return_time TIMESTAMPTZ, + booking_interval INTERVAL, + fine_interval INTERVAL, + fine_amount DECIMAL(8,2), + target_resource_type INT NOT NULL + REFERENCES booking.resource_type(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + target_resource INT REFERENCES booking.resource(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + current_resource INT REFERENCES booking.resource(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + request_lib INT NOT NULL + REFERENCES actor.org_unit(id) + DEFERRABLE INITIALLY DEFERRED, + pickup_lib INT REFERENCES actor.org_unit(id) + DEFERRABLE INITIALLY DEFERRED, + capture_staff INT REFERENCES actor.usr(id) + DEFERRABLE INITIALLY DEFERRED +) INHERITS (money.billable_xact); + +ALTER TABLE booking.reservation ADD PRIMARY KEY (id); + +ALTER TABLE booking.reservation + ADD CONSTRAINT booking_reservation_usr_fkey + FOREIGN KEY (usr) REFERENCES actor.usr (id) + DEFERRABLE INITIALLY DEFERRED; + +CREATE TABLE booking.reservation_attr_value_map ( + id SERIAL PRIMARY KEY, + reservation INT NOT NULL + REFERENCES booking.reservation(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + attr_value INT NOT NULL + REFERENCES booking.resource_attr_value(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + CONSTRAINT bravm_logical_key UNIQUE(reservation, attr_value) +); + +COMMIT; \ No newline at end of file diff --git a/Open-ILS/src/sql/Pg/upgrade/0086.schema.booking-tables.sql b/Open-ILS/src/sql/Pg/upgrade/0086.schema.booking-tables.sql new file mode 100644 index 0000000000..112cd71415 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0086.schema.booking-tables.sql @@ -0,0 +1,135 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0086'); + +DROP SCHEMA IF EXISTS booking CASCADE; + +CREATE SCHEMA booking; + +CREATE TABLE booking.resource_type ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL, + fine_interval INTERVAL, + fine_amount DECIMAL(8,2) NOT NULL DEFAULT 0, + owner INT NOT NULL + REFERENCES actor.org_unit( id ) + DEFERRABLE INITIALLY DEFERRED, + catalog_item BOOLEAN NOT NULL DEFAULT FALSE, + transferable BOOLEAN NOT NULL DEFAULT FALSE, + CONSTRAINT brt_name_once_per_owner UNIQUE(owner, name) +); + +CREATE TABLE booking.resource ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL + REFERENCES actor.org_unit(id) + DEFERRABLE INITIALLY DEFERRED, + type INT NOT NULL + REFERENCES booking.resource_type(id) + DEFERRABLE INITIALLY DEFERRED, + overbook BOOLEAN NOT NULL DEFAULT FALSE, + barcode TEXT NOT NULL, + deposit BOOLEAN NOT NULL DEFAULT FALSE, + deposit_amount DECIMAL(8,2) NOT NULL DEFAULT 0.00, + user_fee DECIMAL(8,2) NOT NULL DEFAULT 0.00, + CONSTRAINT br_unique UNIQUE(owner, type, barcode) +); + +-- For non-catalog items: hijack barcode for name/description + +CREATE TABLE booking.resource_attr ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL + REFERENCES actor.org_unit(id) + DEFERRABLE INITIALLY DEFERRED, + name TEXT NOT NULL, + resource_type INT NOT NULL + REFERENCES booking.resource_type(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + required BOOLEAN NOT NULL DEFAULT FALSE, + CONSTRAINT bra_name_once_per_type UNIQUE(resource_type, name) +); + +CREATE TABLE booking.resource_attr_value ( + id SERIAL PRIMARY KEY, + owner INT NOT NULL + REFERENCES actor.org_unit(id) + DEFERRABLE INITIALLY DEFERRED, + attr INT NOT NULL + REFERENCES booking.resource_attr(id) + DEFERRABLE INITIALLY DEFERRED, + valid_value TEXT NOT NULL, + CONSTRAINT brav_logical_key UNIQUE(owner, attr, valid_value) +); + +-- Do we still need a name column? + + +CREATE TABLE booking.resource_attr_map ( + id SERIAL PRIMARY KEY, + resource INT NOT NULL + REFERENCES booking.resource(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + resource_attr INT NOT NULL + REFERENCES booking.resource_attr(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + value INT NOT NULL + REFERENCES booking.resource_attr_value(id) + DEFERRABLE INITIALLY DEFERRED, + CONSTRAINT bram_one_value_per_attr UNIQUE(resource, resource_attr) +); + +CREATE TABLE booking.reservation ( + request_time TIMESTAMPTZ NOT NULL DEFAULT now(), + start_time TIMESTAMPTZ, + end_time TIMESTAMPTZ, + capture_time TIMESTAMPTZ, + cancel_time TIMESTAMPTZ, + pickup_time TIMESTAMPTZ, + return_time TIMESTAMPTZ, + booking_interval INTERVAL, + fine_interval INTERVAL, + fine_amount DECIMAL(8,2), + target_resource_type INT NOT NULL + REFERENCES booking.resource_type(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + target_resource INT REFERENCES booking.resource(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + current_resource INT REFERENCES booking.resource(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + request_lib INT NOT NULL + REFERENCES actor.org_unit(id) + DEFERRABLE INITIALLY DEFERRED, + pickup_lib INT REFERENCES actor.org_unit(id) + DEFERRABLE INITIALLY DEFERRED, + capture_staff INT REFERENCES actor.usr(id) + DEFERRABLE INITIALLY DEFERRED +) INHERITS (money.billable_xact); + +ALTER TABLE booking.reservation ADD PRIMARY KEY (id); + +ALTER TABLE booking.reservation + ADD CONSTRAINT booking_reservation_usr_fkey + FOREIGN KEY (usr) REFERENCES actor.usr (id) + DEFERRABLE INITIALLY DEFERRED; + +CREATE TABLE booking.reservation_attr_value_map ( + id SERIAL PRIMARY KEY, + reservation INT NOT NULL + REFERENCES booking.reservation(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + attr_value INT NOT NULL + REFERENCES booking.resource_attr_value(id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + CONSTRAINT bravm_logical_key UNIQUE(reservation, attr_value) +); + +COMMIT; -- 2.11.0