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.survey (
25 id SERIAL PRIMARY KEY,
26 owner INT NOT NULL REFERENCES actor.org_unit (id),
27 start_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
28 end_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL,
29 usr_summary BOOL NOT NULL DEFAULT FALSE,
30 opac BOOL NOT NULL DEFAULT FALSE,
31 poll BOOL NOT NULL DEFAULT FALSE,
32 required BOOL NOT NULL DEFAULT FALSE,
34 description TEXT NOT NULL
36 CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name);
38 CREATE TABLE action.survey_question (
39 id SERIAL PRIMARY KEY,
40 survey INT NOT NULL REFERENCES action.survey,
41 question TEXT NOT NULL
44 CREATE TABLE action.survey_answer (
45 id SERIAL PRIMARY KEY,
46 question INT NOT NULL REFERENCES action.survey_question,
50 CREATE SEQUENCE action.survey_response_group_id_seq;
52 CREATE TABLE action.survey_response (
53 id BIGSERIAL PRIMARY KEY,
54 response_group_id INT,
55 usr INT, -- REFERENCES actor.usr
56 survey INT NOT NULL REFERENCES action.survey,
57 question INT NOT NULL REFERENCES action.survey_question,
58 answer INT NOT NULL REFERENCES action.survey_answer,
59 answer_date TIMESTAMP WITH TIME ZONE,
60 effective_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
62 CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS '
64 NEW.answer_date := NOW();
68 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
69 BEFORE INSERT ON action.survey_response
71 EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();
74 CREATE TABLE action.circulation (
75 target_copy BIGINT NOT NULL, -- asset.copy.id
76 circ_lib INT NOT NULL, -- actor.org_unit.id
77 circ_staff INT NOT NULL, -- actor.usr.id
78 checkin_staff INT, -- actor.usr.id
79 checkin_lib INT, -- actor.org_unit.id
80 renewal_remaining INT NOT NULL, -- derived from "circ duration" rule
81 due_date TIMESTAMP WITH TIME ZONE,
82 stop_fines_time TIMESTAMP WITH TIME ZONE,
83 checkin_time TIMESTAMP WITH TIME ZONE,
84 duration INTERVAL, -- derived from "circ duration" rule
85 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
86 recuring_fine NUMERIC(6,2), -- derived from "circ fine" rule
87 max_fine NUMERIC(6,2), -- derived from "max fine" rule
88 phone_renewal BOOL NOT NULL DEFAULT FALSE,
89 desk_renewal BOOL NOT NULL DEFAULT FALSE,
90 opac_renewal BOOL NOT NULL DEFAULT FALSE,
91 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
92 recuring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
93 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
94 stop_fines TEXT CHECK (stop_fines IN ('CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE'))
95 ) INHERITS (money.billable_xact);
96 ALTER TABLE action.circulation ADD PRIMARY KEY (id);
97 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
98 CREATE INDEX circ_outstanding_idx ON action.circulation (usr) WHERE checkin_time IS NULL;
99 CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE checkin_time IS NOT NULL;
102 CREATE OR REPLACE VIEW action.open_circulation AS
104 FROM action.circulation
105 WHERE checkin_time IS NULL
109 CREATE OR REPLACE VIEW action.billable_cirulations AS
111 FROM action.circulation
112 WHERE xact_finish IS NULL;
114 CREATE VIEW stats.fleshed_circulation AS
116 CAST(c.xact_start AS DATE) AS start_date_day,
117 CAST(c.xact_finish AS DATE) AS finish_date_day,
118 DATE_TRUNC('hour', c.xact_start) AS start_date_hour,
119 DATE_TRUNC('hour', c.xact_finish) AS finish_date_hour,
120 cp.call_number_label,
125 FROM "action".circulation c
126 JOIN stats.fleshed_copy cp ON (cp.id = c.target_copy);
129 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
131 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
132 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
133 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
135 IF NEW.stop_fines = 'LOST' THEN
136 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
141 $$ LANGUAGE 'plpgsql';
142 CREATE TRIGGER action_circulation_stop_fines_tgr
143 BEFORE UPDATE ON action.circulation
145 EXECUTE PROCEDURE action.circulation_claims_returned ();
148 CREATE TABLE action.hold_request (
149 id SERIAL PRIMARY KEY,
150 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
151 capture_time TIMESTAMP WITH TIME ZONE,
152 fulfillment_time TIMESTAMP WITH TIME ZONE,
153 checkin_time TIMESTAMP WITH TIME ZONE,
154 return_time TIMESTAMP WITH TIME ZONE,
155 prev_check_time TIMESTAMP WITH TIME ZONE,
156 expire_time TIMESTAMP WITH TIME ZONE,
157 cancel_time TIMESTAMP WITH TIME ZONE,
158 target BIGINT NOT NULL, -- see hold_type
159 current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL,
160 fulfillment_staff INT REFERENCES actor.usr (id),
161 fulfillment_lib INT REFERENCES actor.org_unit (id),
162 request_lib INT NOT NULL REFERENCES actor.org_unit (id),
163 requestor INT NOT NULL REFERENCES actor.usr (id),
164 usr INT NOT NULL REFERENCES actor.usr (id),
165 selection_ou INT NOT NULL,
166 selection_depth INT NOT NULL DEFAULT 0,
167 pickup_lib INT NOT NULL REFERENCES actor.org_unit,
168 hold_type TEXT NOT NULL CHECK (hold_type IN ('M','T','V','C')),
169 holdable_formats TEXT,
171 email_notify BOOL NOT NULL DEFAULT TRUE
175 CREATE TABLE action.hold_notification (
176 id SERIAL PRIMARY KEY,
177 hold INT NOT NULL REFERENCES action.hold_request (id),
178 notify_staff INT REFERENCES actor.usr (id),
179 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
180 method TEXT NOT NULL, -- email address or phone number
183 CREATE INDEX ahn_hold_idx ON action.hold_notification (hold);
185 CREATE TABLE action.hold_copy_map (
186 id SERIAL PRIMARY KEY,
187 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE,
188 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
189 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
191 -- CREATE INDEX acm_hold_idx ON action.hold_copy_map (hold);
192 CREATE INDEX acm_copy_idx ON action.hold_copy_map (target_copy);
194 CREATE TABLE action.transit_copy (
195 id SERIAL PRIMARY KEY,
196 source_send_time TIMESTAMP WITH TIME ZONE,
197 dest_recv_time TIMESTAMP WITH TIME ZONE,
198 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
199 source INT NOT NULL REFERENCES actor.org_unit (id),
200 dest INT NOT NULL REFERENCES actor.org_unit (id),
201 prev_hop INT REFERENCES action.transit_copy (id),
202 copy_status INT NOT NULL REFERENCES config.copy_status (id),
203 persistant_transfer BOOL NOT NULL DEFAULT FALSE
206 CREATE TABLE action.hold_transit_copy (
207 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
208 ) INHERITS (action.transit_copy);
209 ALTER TABLE action.hold_transit_copy ADD PRIMARY KEY (id);
211 CREATE TABLE action.unfulfilled_hold_list (
212 id BIGSERIAL PRIMARY KEY,
213 current_copy BIGINT NOT NULL,
215 circ_lib INT NOT NULL,
216 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()