1 DROP SCHEMA action CASCADE;
7 CREATE TABLE action.in_house_use (
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),
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),
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),
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),
27 staff INT NOT NULL REFERENCES actor.usr (id),
28 org_unit INT NOT NULL REFERENCES actor.org_unit (id),
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),
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,
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,
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,
65 question INT NOT NULL REFERENCES action.survey_question,
66 answer INT NOT NULL REFERENCES action.survey_answer,
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 duration INTERVAL, -- derived from "circ duration" rule
93 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
94 recuring_fine NUMERIC(6,2), -- derived from "circ fine" rule
95 max_fine NUMERIC(6,2), -- derived from "max fine" rule
96 phone_renewal BOOL NOT NULL DEFAULT FALSE,
97 desk_renewal BOOL NOT NULL DEFAULT FALSE,
98 opac_renewal BOOL NOT NULL DEFAULT FALSE,
99 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
100 recuring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
101 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
102 stop_fines TEXT CHECK (stop_fines IN ('CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE'))
103 ) INHERITS (money.billable_xact);
104 ALTER TABLE action.circulation ADD PRIMARY KEY (id);
105 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
106 CREATE INDEX circ_outstanding_idx ON action.circulation (usr) WHERE checkin_time IS NULL;
107 CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE checkin_time IS NOT NULL;
110 CREATE OR REPLACE VIEW action.open_circulation AS
112 FROM action.circulation
113 WHERE checkin_time IS NULL
117 CREATE OR REPLACE VIEW action.billable_cirulations AS
119 FROM action.circulation
120 WHERE xact_finish IS NULL;
122 CREATE VIEW stats.fleshed_circulation AS
124 CAST(c.xact_start AS DATE) AS start_date_day,
125 CAST(c.xact_finish AS DATE) AS finish_date_day,
126 DATE_TRUNC('hour', c.xact_start) AS start_date_hour,
127 DATE_TRUNC('hour', c.xact_finish) AS finish_date_hour,
128 cp.call_number_label,
133 FROM "action".circulation c
134 JOIN stats.fleshed_copy cp ON (cp.id = c.target_copy);
137 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
139 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
140 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
141 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
143 IF NEW.stop_fines = 'LOST' THEN
144 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
149 $$ LANGUAGE 'plpgsql';
150 CREATE TRIGGER action_circulation_stop_fines_tgr
151 BEFORE UPDATE ON action.circulation
153 EXECUTE PROCEDURE action.circulation_claims_returned ();
156 CREATE TABLE action.hold_request (
157 id SERIAL PRIMARY KEY,
158 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
159 capture_time TIMESTAMP WITH TIME ZONE,
160 fulfillment_time TIMESTAMP WITH TIME ZONE,
161 checkin_time TIMESTAMP WITH TIME ZONE,
162 return_time TIMESTAMP WITH TIME ZONE,
163 prev_check_time TIMESTAMP WITH TIME ZONE,
164 expire_time TIMESTAMP WITH TIME ZONE,
165 cancel_time TIMESTAMP WITH TIME ZONE,
166 target BIGINT NOT NULL, -- see hold_type
167 current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL,
168 fulfillment_staff INT REFERENCES actor.usr (id),
169 fulfillment_lib INT REFERENCES actor.org_unit (id),
170 request_lib INT NOT NULL REFERENCES actor.org_unit (id),
171 requestor INT NOT NULL REFERENCES actor.usr (id),
172 usr INT NOT NULL REFERENCES actor.usr (id),
173 selection_ou INT NOT NULL,
174 selection_depth INT NOT NULL DEFAULT 0,
175 pickup_lib INT NOT NULL REFERENCES actor.org_unit,
176 hold_type TEXT NOT NULL CHECK (hold_type IN ('M','T','V','C')),
177 holdable_formats TEXT,
179 email_notify BOOL NOT NULL DEFAULT TRUE
182 CREATE INDEX hold_request_target_idx ON action.hold_request (target);
183 CREATE INDEX hold_request_usr_idx ON action.hold_request (usr);
184 CREATE INDEX hold_request_pickup_lib_idx ON action.hold_request (pickup_lib);
185 CREATE INDEX hold_request_current_copy_idx ON action.hold_request (current_copy);
186 CREATE INDEX hold_request_prev_check_time_idx ON action.hold_request (prev_check_time);
189 CREATE TABLE action.hold_notification (
190 id SERIAL PRIMARY KEY,
191 hold INT NOT NULL REFERENCES action.hold_request (id),
192 notify_staff INT REFERENCES actor.usr (id),
193 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
194 method TEXT NOT NULL, -- email address or phone number
197 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
199 CREATE TABLE action.hold_copy_map (
200 id SERIAL PRIMARY KEY,
201 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE,
202 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
203 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
205 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
206 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
208 CREATE TABLE action.transit_copy (
209 id SERIAL PRIMARY KEY,
210 source_send_time TIMESTAMP WITH TIME ZONE,
211 dest_recv_time TIMESTAMP WITH TIME ZONE,
212 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
213 source INT NOT NULL REFERENCES actor.org_unit (id),
214 dest INT NOT NULL REFERENCES actor.org_unit (id),
215 prev_hop INT REFERENCES action.transit_copy (id),
216 copy_status INT NOT NULL REFERENCES config.copy_status (id),
217 persistant_transfer BOOL NOT NULL DEFAULT FALSE
220 CREATE TABLE action.hold_transit_copy (
221 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
222 ) INHERITS (action.transit_copy);
223 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
225 CREATE TABLE action.unfulfilled_hold_list (
226 id BIGSERIAL PRIMARY KEY,
227 current_copy BIGINT NOT NULL,
229 circ_lib INT NOT NULL,
230 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()