From 54e7e9440e369b42386e15b7779b0485f7971945 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Thu, 23 Jun 2005 19:27:05 +0000
Subject: [PATCH] added holds tables

git-svn-id: svn://svn.open-ils.org/ILS/trunk@914 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/src/sql/Postgres/090.schema.action.sql | 65 ++++++++++++++++++-------
 1 file changed, 47 insertions(+), 18 deletions(-)

diff --git a/Open-ILS/src/sql/Postgres/090.schema.action.sql b/Open-ILS/src/sql/Postgres/090.schema.action.sql
index 9687e2a2b9..d6a3dda629 100644
--- a/Open-ILS/src/sql/Postgres/090.schema.action.sql
+++ b/Open-ILS/src/sql/Postgres/090.schema.action.sql
@@ -33,14 +33,14 @@ CREATE TABLE action.survey_answer (
 CREATE SEQUENCE action.survey_response_group_id_seq;
 
 CREATE TABLE action.survey_response (
-	id		BIGSERIAL	PRIMARY KEY,
+	id			BIGSERIAL			PRIMARY KEY,
 	response_group_id	INT,
-	usr		INT, -- REFERENCES actor.usr
-	survey		INT		NOT NULL REFERENCES action.survey,
-	question	INT		NOT NULL REFERENCES action.survey_question,
-	answer		INT		NOT NULL REFERENCES action.survey_answer,
-	answer_date	TIMESTAMP WITH TIME ZONE,
-	effective_date	TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
+	usr			INT, -- REFERENCES actor.usr
+	survey			INT				NOT NULL REFERENCES action.survey,
+	question		INT				NOT NULL REFERENCES action.survey_question,
+	answer			INT				NOT NULL REFERENCES action.survey_answer,
+	answer_date		TIMESTAMP WITH TIME ZONE,
+	effective_date		TIMESTAMP WITH TIME ZONE	NOT NULL DEFAULT NOW()
 );
 CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS '
 BEGIN
@@ -55,20 +55,49 @@ CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
 
 
 CREATE TABLE action.circulation (
-	target_copy		BIGINT		NOT NULL, -- asset.copy.id
-	circ_lib		INT		NOT NULL, -- actor.org_unit.id
-	duration_rule		TEXT		NOT NULL, -- name of "circ duration" rule
-	duration		INTERVAL	NOT NULL, -- derived from "circ duration" rule
-	renewal_remaining	INT		NOT NULL, -- derived from "circ duration" rule
-	recuring_fine_rule	TEXT		NOT NULL, -- name of "circ fine" rule
-	recuring_fine		NUMERIC(6,2)	NOT NULL, -- derived from "circ fine" rule
-	max_fine_rule		TEXT		NOT NULL, -- name of "max fine" rule
-	max_fine		NUMERIC(6,2)	NOT NULL, -- derived from "max fine" rule
-	fine_interval		INTERVAL	NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
-	stop_fines		TEXT		CHECK (stop_fines IN ('CHECKIN','CLAIMSRETURNED','LOST','MAXFINES'))
+	target_copy		BIGINT				NOT NULL, -- asset.copy.id
+	circ_lib		INT				NOT NULL, -- actor.org_unit.id
+	duration_rule		TEXT				NOT NULL, -- name of "circ duration" rule
+	duration		INTERVAL			NOT NULL, -- derived from "circ duration" rule
+	renewal_remaining	INT				NOT NULL, -- derived from "circ duration" rule
+	recuring_fine_rule	TEXT				NOT NULL, -- name of "circ fine" rule
+	recuring_fine		NUMERIC(6,2)			NOT NULL, -- derived from "circ fine" rule
+	max_fine_rule		TEXT				NOT NULL, -- name of "max fine" rule
+	max_fine		NUMERIC(6,2)			NOT NULL, -- derived from "max fine" rule
+	fine_interval		INTERVAL			NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
+	due_date		TIMESTAMP WITH TIME ZONE	NOT NULL,
+	stop_fines		TEXT				CHECK (stop_fines IN ('CHECKIN','CLAIMSRETURNED','LOST','MAXFINES'))
 ) INHERITS (money.billable_xact);
 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
 
 
+CREATE TABLE action.hold_request (
+	id			SERIAL				PRIMARY KEY,
+	request_time		TIMESTAMP WITH TIME ZONE	NOT NULL DEFAULT NOW(),
+	capture_time		TIMESTAMP WITH TIME ZONE,
+	fulfillment_time	TIMESTAMP WITH TIME ZONE,
+	prev_check_time		TIMESTAMP WITH TIME ZONE,
+	expire_time		TIMESTAMP WITH TIME ZONE,
+	requestor		INT				NOT NULL REFERENCES actor.usr (id),
+	usr			INT				NOT NULL REFERENCES actor.usr (id),
+	hold_type		CHAR				NOT NULL CHECK (hold_type IN ('M','T','V','C')),
+	holdable_formats	TEXT,
+	target			BIGINT				NOT NULL, -- see hold_type
+	selection_depth		INT				NOT NULL DEFAULT 0,
+	pickup_lib		INT				NOT NULL REFERENCES actor.org_unit,
+	current_copy		BIGINT				REFERENCES asset.copy (id) ON DELETE SET NULL
+);
+
+
+CREATE TABLE action.hold_notification (
+	id		SERIAL		PRIMARY KEY,
+	hold		INT		NOT NULL REFERENCES action.hold_request (id),
+	method		TEXT		NOT NULL, -- eh...
+	notify_time	TIMESTAMP	NOT NULL DEFAULT NOW(),
+	note		TEXT
+);
+
+
+
 COMMIT;
 
-- 
2.11.0