Changeset 9542 for trunk/Open-ILS/src/sql/Pg/090.schema.action.sql
- Timestamp:
- 05/09/08 12:31:09 (2 months ago)
- Files:
-
- 1 modified
-
trunk/Open-ILS/src/sql/Pg/090.schema.action.sql (modified) (12 diffs)
Legend:
- Unmodified
- Added
- Removed
-
trunk/Open-ILS/src/sql/Pg/090.schema.action.sql
r8506 r9542 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() … … 168 168 cancel_time TIMESTAMP WITH TIME ZONE, 169 169 target BIGINT NOT NULL, -- see hold_type 170 current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL ,171 fulfillment_staff INT REFERENCES actor.usr (id) ,172 fulfillment_lib INT REFERENCES actor.org_unit (id) ,173 request_lib INT NOT NULL REFERENCES actor.org_unit (id) ,174 requestor INT NOT NULL REFERENCES actor.usr (id) ,175 usr INT NOT NULL REFERENCES actor.usr (id) ,170 current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, 171 fulfillment_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 172 fulfillment_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 173 request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 174 requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 175 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 176 176 selection_ou INT NOT NULL, 177 177 selection_depth INT NOT NULL DEFAULT 0, 178 pickup_lib INT NOT NULL REFERENCES actor.org_unit ,178 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED, 179 179 hold_type TEXT NOT NULL CHECK (hold_type IN ('M','T','V','C')), 180 180 holdable_formats TEXT, … … 194 194 CREATE TABLE action.hold_notification ( 195 195 id SERIAL PRIMARY KEY, 196 hold INT NOT NULL REFERENCES action.hold_request (id) ,197 notify_staff INT REFERENCES actor.usr (id) ,196 hold INT NOT NULL REFERENCES action.hold_request (id) DEFERRABLE INITIALLY DEFERRED, 197 notify_staff INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 198 198 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), 199 199 method TEXT NOT NULL, -- email address or phone number … … 204 204 CREATE TABLE action.hold_copy_map ( 205 205 id SERIAL PRIMARY KEY, 206 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE ,207 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE ,206 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, 207 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, 208 208 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy) 209 209 ); … … 215 215 source_send_time TIMESTAMP WITH TIME ZONE, 216 216 dest_recv_time TIMESTAMP WITH TIME ZONE, 217 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE ,218 source INT NOT NULL REFERENCES actor.org_unit (id) ,219 dest INT NOT NULL REFERENCES actor.org_unit (id) ,220 prev_hop INT REFERENCES action.transit_copy (id) ,221 copy_status INT NOT NULL REFERENCES config.copy_status (id) ,217 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, 218 source INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 219 dest INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 220 prev_hop INT REFERENCES action.transit_copy (id) DEFERRABLE INITIALLY DEFERRED, 221 copy_status INT NOT NULL REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED, 222 222 persistant_transfer BOOL NOT NULL DEFAULT FALSE 223 223 ); … … 231 231 ) INHERITS (action.transit_copy); 232 232 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id); 233 ALTER TABLE action.hold_transit_copy ADD CONSTRAINT ahtc_tc_fkey FOREIGN KEY (target_copy) REFERENCES asset.copy (id) ON DELETE CASCADE ;233 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; 234 234 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest); 235 235 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
