1 DROP SCHEMA action CASCADE;
7 CREATE TABLE action.in_house_use (
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 use_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
15 CREATE TABLE action.non_cataloged_circulation (
16 id SERIAL PRIMARY KEY,
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 circ_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
24 CREATE TABLE action.non_cat_in_house_use (
25 id SERIAL PRIMARY KEY,
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 use_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
32 CREATE TABLE action.survey (
33 id SERIAL PRIMARY KEY,
34 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
35 start_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
36 end_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL,
37 usr_summary BOOL NOT NULL DEFAULT FALSE,
38 opac BOOL NOT NULL DEFAULT FALSE,
39 poll BOOL NOT NULL DEFAULT FALSE,
40 required BOOL NOT NULL DEFAULT FALSE,
42 description TEXT NOT NULL
44 CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name);
46 CREATE TABLE action.survey_question (
47 id SERIAL PRIMARY KEY,
48 survey INT NOT NULL REFERENCES action.survey DEFERRABLE INITIALLY DEFERRED,
49 question TEXT NOT NULL
52 CREATE TABLE action.survey_answer (
53 id SERIAL PRIMARY KEY,
54 question INT NOT NULL REFERENCES action.survey_question DEFERRABLE INITIALLY DEFERRED,
58 CREATE SEQUENCE action.survey_response_group_id_seq;
60 CREATE TABLE action.survey_response (
61 id BIGSERIAL PRIMARY KEY,
62 response_group_id INT,
63 usr INT, -- REFERENCES actor.usr
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 answer_date TIMESTAMP WITH TIME ZONE,
68 effective_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
70 CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS '
72 NEW.answer_date := NOW();
76 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
77 BEFORE INSERT ON action.survey_response
79 EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();
82 CREATE TABLE action.circulation (
83 target_copy BIGINT NOT NULL, -- asset.copy.id
84 circ_lib INT NOT NULL, -- actor.org_unit.id
85 circ_staff INT NOT NULL, -- actor.usr.id
86 checkin_staff INT, -- actor.usr.id
87 checkin_lib INT, -- actor.org_unit.id
88 renewal_remaining INT NOT NULL, -- derived from "circ duration" rule
89 due_date TIMESTAMP WITH TIME ZONE,
90 stop_fines_time TIMESTAMP WITH TIME ZONE,
91 checkin_time TIMESTAMP WITH TIME ZONE,
92 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
93 duration INTERVAL, -- derived from "circ duration" rule
94 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
95 recuring_fine NUMERIC(6,2), -- derived from "circ fine" rule
96 max_fine NUMERIC(6,2), -- derived from "max fine" rule
97 phone_renewal BOOL NOT NULL DEFAULT FALSE,
98 desk_renewal BOOL NOT NULL DEFAULT FALSE,
99 opac_renewal BOOL NOT NULL DEFAULT FALSE,
100 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
101 recuring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
102 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
103 stop_fines TEXT CHECK (stop_fines IN ('CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE'))
104 ) INHERITS (money.billable_xact);
105 ALTER TABLE action.circulation ADD PRIMARY KEY (id);
106 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
107 CREATE INDEX circ_outstanding_idx ON action.circulation (usr) WHERE checkin_time IS NULL;
108 CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE checkin_time IS NOT NULL;
109 CREATE INDEX circ_circ_lib_idx ON "action".circulation (circ_lib);
110 CREATE INDEX circ_open_date_idx ON "action".circulation (xact_start) WHERE xact_finish IS NULL;
113 CREATE OR REPLACE VIEW action.open_circulation AS
115 FROM action.circulation
116 WHERE checkin_time IS NULL
120 CREATE OR REPLACE VIEW action.billable_cirulations AS
122 FROM action.circulation
123 WHERE xact_finish IS NULL;
125 CREATE VIEW stats.fleshed_circulation AS
127 CAST(c.xact_start AS DATE) AS start_date_day,
128 CAST(c.xact_finish AS DATE) AS finish_date_day,
129 DATE_TRUNC('hour', c.xact_start) AS start_date_hour,
130 DATE_TRUNC('hour', c.xact_finish) AS finish_date_hour,
131 cp.call_number_label,
136 FROM "action".circulation c
137 JOIN stats.fleshed_copy cp ON (cp.id = c.target_copy);
140 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
142 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
143 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
144 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
146 IF NEW.stop_fines = 'LOST' THEN
147 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
152 $$ LANGUAGE 'plpgsql';
153 CREATE TRIGGER action_circulation_stop_fines_tgr
154 BEFORE UPDATE ON action.circulation
156 EXECUTE PROCEDURE action.circulation_claims_returned ();
159 CREATE TABLE action.hold_request (
160 id SERIAL PRIMARY KEY,
161 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
162 capture_time TIMESTAMP WITH TIME ZONE,
163 fulfillment_time TIMESTAMP WITH TIME ZONE,
164 checkin_time TIMESTAMP WITH TIME ZONE,
165 return_time TIMESTAMP WITH TIME ZONE,
166 prev_check_time TIMESTAMP WITH TIME ZONE,
167 expire_time TIMESTAMP WITH TIME ZONE,
168 cancel_time TIMESTAMP WITH TIME ZONE,
169 target BIGINT NOT NULL, -- see hold_type
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 selection_ou INT NOT NULL,
177 selection_depth INT NOT NULL DEFAULT 0,
178 pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED,
179 hold_type TEXT NOT NULL CHECK (hold_type IN ('M','T','V','C')),
180 holdable_formats TEXT,
182 email_notify BOOL NOT NULL DEFAULT TRUE,
183 frozen BOOL NOT NULL DEFAULT FALSE,
184 thaw_date TIMESTAMP WITH TIME ZONE
187 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
188 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
189 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
190 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
191 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
194 CREATE TABLE action.hold_notification (
195 id SERIAL PRIMARY KEY,
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 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
199 method TEXT NOT NULL, -- email address or phone number
202 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
204 CREATE TABLE action.hold_copy_map (
205 id SERIAL PRIMARY KEY,
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 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
210 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
211 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
213 CREATE TABLE action.transit_copy (
214 id SERIAL PRIMARY KEY,
215 source_send_time TIMESTAMP WITH TIME ZONE,
216 dest_recv_time TIMESTAMP WITH TIME ZONE,
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 persistant_transfer BOOL NOT NULL DEFAULT FALSE
224 CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest);
225 CREATE INDEX active_transit_source_idx ON "action".transit_copy (source);
226 CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy);
229 CREATE TABLE action.hold_transit_copy (
230 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
231 ) INHERITS (action.transit_copy);
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 DEFERRABLE INITIALLY DEFERRED;
234 CREATE INDEX active_hold_transit_dest_idx ON "action".hold_transit_copy (dest);
235 CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (source);
236 CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy);
239 CREATE TABLE action.unfulfilled_hold_list (
240 id BIGSERIAL PRIMARY KEY,
241 current_copy BIGINT NOT NULL,
243 circ_lib INT NOT NULL,
244 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()