- Timestamp:
- 05/09/08 12:34:30 (5 months ago)
- Files:
-
- 1 modified
Legend:
- Unmodified
- Added
- Removed
-
branches/rel_1_2/Open-ILS/src/sql/Pg/090.schema.action.sql
r8908 r9543 7 7 CREATE TABLE action.in_house_use ( 8 8 id SERIAL PRIMARY KEY, 9 item BIGINT NOT NULL REFERENCES asset.copy (id) ,10 staff INT NOT NULL REFERENCES actor.usr (id) ,11 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ,9 item BIGINT NOT NULL REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED, 10 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 11 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 12 12 use_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() 13 13 ); … … 15 15 CREATE TABLE action.non_cataloged_circulation ( 16 16 id SERIAL PRIMARY KEY, 17 patron INT NOT NULL REFERENCES actor.usr (id) ,18 staff INT NOT NULL REFERENCES actor.usr (id) ,19 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) ,20 item_type INT NOT NULL REFERENCES config.non_cataloged_type (id) ,17 patron INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 18 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 19 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 20 item_type INT NOT NULL REFERENCES config.non_cataloged_type (id) DEFERRABLE INITIALLY DEFERRED, 21 21 circ_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() 22 22 ); … … 24 24 CREATE TABLE action.non_cat_in_house_use ( 25 25 id SERIAL PRIMARY KEY, 26 item_type BIGINT NOT NULL REFERENCES config.non_cataloged_type(id) ,27 staff INT NOT NULL REFERENCES actor.usr (id) ,28 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ,26 item_type BIGINT NOT NULL REFERENCES config.non_cataloged_type(id) DEFERRABLE INITIALLY DEFERRED, 27 staff INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 28 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 29 29 use_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() 30 30 ); … … 32 32 CREATE TABLE action.survey ( 33 33 id SERIAL PRIMARY KEY, 34 owner INT NOT NULL REFERENCES actor.org_unit (id) ,34 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 35 35 start_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), 36 36 end_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL, … … 46 46 CREATE TABLE action.survey_question ( 47 47 id SERIAL PRIMARY KEY, 48 survey INT NOT NULL REFERENCES action.survey ,48 survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED, 49 49 question TEXT NOT NULL 50 50 ); … … 52 52 CREATE TABLE action.survey_answer ( 53 53 id SERIAL PRIMARY KEY, 54 question INT NOT NULL REFERENCES action.survey_question ,54 question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED, 55 55 answer TEXT NOT NULL 56 56 ); … … 62 62 response_group_id INT, 63 63 usr INT, -- REFERENCES actor.usr 64 survey INT NOT NULL REFERENCES action.survey ,65 question INT NOT NULL REFERENCES action.survey_question ,66 answer INT NOT NULL REFERENCES action.survey_answer ,64 survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED, 65 question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED, 66 answer INT NOT NULL REFERENCES action.survey_answer DEFERRABLE INITIALLY DEFERRED, 67 67 answer_date TIMESTAMP WITH TIME ZONE, 68 68 effective_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() … … 176 176 cancel_time TIMESTAMP WITH TIME ZONE, 177 177 target BIGINT NOT NULL, -- see hold_type 178 current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL ,179 fulfillment_staff INT REFERENCES actor.usr (id) ,180 fulfillment_lib INT REFERENCES actor.org_unit (id) ,181 request_lib INT NOT NULL REFERENCES actor.org_unit (id) ,182 requestor INT NOT NULL REFERENCES actor.usr (id) ,183 usr INT NOT NULL REFERENCES actor.usr (id) ,178 current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, 179 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 180 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 181 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 182 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 183 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 184 184 selection_ou INT NOT NULL, 185 185 selection_depth INT NOT NULL DEFAULT 0, 186 pickup_lib INT NOT NULL REFERENCES actor.org_unit ,186 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED, 187 187 hold_type TEXT NOT NULL CHECK (hold_type IN ('M','T','V','C')), 188 188 holdable_formats TEXT, … … 202 202 CREATE TABLE action.hold_notification ( 203 203 id SERIAL PRIMARY KEY, 204 hold INT NOT NULL REFERENCES action.hold_request (id) ,205 notify_staff INT REFERENCES actor.usr (id) ,204 hold INT NOT NULL REFERENCES action.hold_request (id) DEFERRABLE INITIALLY DEFERRED, 205 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 206 206 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), 207 207 method TEXT NOT NULL, -- email address or phone number … … 212 212 CREATE TABLE action.hold_copy_map ( 213 213 id SERIAL PRIMARY KEY, 214 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE ,215 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE ,214 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, 215 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, 216 216 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy) 217 217 ); … … 223 223 source_send_time TIMESTAMP WITH TIME ZONE, 224 224 dest_recv_time TIMESTAMP WITH TIME ZONE, 225 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE ,226 source INT NOT NULL REFERENCES actor.org_unit (id) ,227 dest INT NOT NULL REFERENCES actor.org_unit (id) ,228 prev_hop INT REFERENCES action.transit_copy (id) ,229 copy_status INT NOT NULL REFERENCES config.copy_status (id) ,225 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, 226 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 227 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 228 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED, 229 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED, 230 230 persistant_transfer BOOL NOT NULL DEFAULT FALSE 231 231 ); … … 239 239 ) INHERITS (action.transit_copy); 240 240 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id); 241 ALTER TABLE action.hold_transit_copy ADD CONSTRAINT ahtc_tc_fkey FOREIGN KEY (target_copy) REFERENCES asset.copy (id) ON DELETE CASCADE ;241 ALTER TABLE action.hold_transit_copy ADD CONSTRAINT ahtc_tc_fkey FOREIGN KEY (target_copy) REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; 242 242 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest); 243 243 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
