1 DROP SCHEMA action CASCADE;
7 CREATE TABLE action.survey (
9 owner INT NOT NULL REFERENCES actor.org_unit (id),
10 start_date DATE NOT NULL DEFAULT NOW(),
11 end_date DATE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL,
12 usr_summary BOOL NOT NULL DEFAULT FALSE,
13 opac BOOL NOT NULL DEFAULT FALSE,
14 poll BOOL NOT NULL DEFAULT FALSE,
15 required BOOL NOT NULL DEFAULT FALSE,
17 description TEXT NOT NULL
19 CREATE UNIQUE INDEX asv_once_per_owner_idx ON action.survey (owner,name);
21 CREATE TABLE action.survey_question (
22 id SERIAL PRIMARY KEY,
23 survey INT NOT NULL REFERENCES action.survey,
24 question TEXT NOT NULL
27 CREATE TABLE action.survey_answer (
28 id SERIAL PRIMARY KEY,
29 question INT NOT NULL REFERENCES action.survey_question,
33 CREATE SEQUENCE action.survey_response_group_id_seq;
35 CREATE TABLE action.survey_response (
36 id BIGSERIAL PRIMARY KEY,
37 response_group_id INT,
38 usr INT, -- REFERENCES actor.usr
39 survey INT NOT NULL REFERENCES action.survey,
40 question INT NOT NULL REFERENCES action.survey_question,
41 answer INT NOT NULL REFERENCES action.survey_answer,
42 answer_date TIMESTAMP WITH TIME ZONE,
43 effective_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
45 CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS '
47 NEW.answer_date := NOW();
51 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
52 BEFORE INSERT ON action.survey_response
54 EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();
57 CREATE TABLE action.circulation (
58 target_copy BIGINT NOT NULL, -- asset.copy.id
59 circ_lib INT NOT NULL, -- actor.org_unit.id
60 circ_staff INT NOT NULL, -- actor.usr.id
61 checkin_staff INT, -- actor.usr.id
62 checkin_lib INT, -- actor.org_unit.id
63 renewal_remaining INT NOT NULL, -- derived from "circ duration" rule
64 due_date TIMESTAMP WITH TIME ZONE NOT NULL,
65 stop_fines_time TIMESTAMP WITH TIME ZONE,
66 checkin_time TIMESTAMP WITH TIME ZONE,
67 duration INTERVAL NOT NULL, -- derived from "circ duration" rule
68 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
69 recuring_fine NUMERIC(6,2) NOT NULL, -- derived from "circ fine" rule
70 max_fine NUMERIC(6,2) NOT NULL, -- derived from "max fine" rule
71 phone_renewal BOOL NOT NULL DEFAULT FALSE,
72 desk_renewal BOOL NOT NULL DEFAULT FALSE,
73 opac_renewal BOOL NOT NULL DEFAULT FALSE,
74 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
75 recuring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
76 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
77 stop_fines TEXT CHECK (stop_fines IN ('CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE'))
78 ) INHERITS (money.billable_xact);
79 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
81 CREATE OR REPLACE VIEW action.open_circulation AS
83 FROM action.circulation
84 WHERE checkin_time IS NULL
88 CREATE OR REPLACE VIEW action.billable_cirulations AS
90 FROM action.circulation
91 WHERE xact_finish IS NULL;
93 CREATE VIEW stats.fleshed_circulation AS
95 CAST(c.xact_start AS DATE) AS start_date_day,
96 CAST(c.xact_finish AS DATE) AS finish_date_day,
97 DATE_TRUNC('hour', c.xact_start) AS start_date_hour,
98 DATE_TRUNC('hour', c.xact_finish) AS finish_date_hour,
104 FROM "action".circulation c
105 JOIN stats.fleshed_copy cp ON (cp.id = c.target_copy);
108 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
110 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
111 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
112 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
114 IF NEW.stop_fines = 'LOST' THEN
115 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
120 $$ LANGUAGE 'plpgsql';
121 CREATE TRIGGER action_circulation_stop_fines_tgr
122 BEFORE UPDATE ON action.circulation
124 EXECUTE PROCEDURE action.circulation_claims_returned ();
127 CREATE TABLE action.hold_request (
128 id SERIAL PRIMARY KEY,
129 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
130 capture_time TIMESTAMP WITH TIME ZONE,
131 fulfillment_time TIMESTAMP WITH TIME ZONE,
132 checkin_time TIMESTAMP WITH TIME ZONE,
133 return_time TIMESTAMP WITH TIME ZONE,
134 prev_check_time TIMESTAMP WITH TIME ZONE,
135 expire_time TIMESTAMP WITH TIME ZONE,
136 target BIGINT NOT NULL, -- see hold_type
137 current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL,
138 fulfillment_staff INT REFERENCES actor.usr (id),
139 fulfillment_lib INT REFERENCES actor.org_unit (id),
140 request_lib INT NOT NULL REFERENCES actor.org_unit (id),
141 requestor INT NOT NULL REFERENCES actor.usr (id),
142 usr INT NOT NULL REFERENCES actor.usr (id),
143 selection_depth INT NOT NULL DEFAULT 0,
144 pickup_lib INT NOT NULL REFERENCES actor.org_unit,
145 hold_type "char" NOT NULL CHECK (hold_type IN ('M','T','V','C')),
146 holdable_formats TEXT,
152 CREATE TABLE action.hold_notification (
153 id SERIAL PRIMARY KEY,
154 hold INT NOT NULL REFERENCES action.hold_request (id),
155 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
156 method TEXT NOT NULL, -- eh...
160 CREATE TABLE action.hold_copy_map (
161 id SERIAL PRIMARY KEY,
162 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE,
163 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
164 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
167 CREATE TABLE action.transit_copy (
168 id SERIAL PRIMARY KEY,
169 source_send_time TIMESTAMP WITH TIME ZONE,
170 dest_recv_time TIMESTAMP WITH TIME ZONE,
171 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
172 source INT NOT NULL REFERENCES actor.org_unit (id),
173 dest INT NOT NULL REFERENCES actor.org_unit (id),
174 prev_hop INT REFERENCES action.transit_copy (id),
175 copy_status INT NOT NULL REFERENCES config.copy_status (id),
176 persistant_transfer BOOL NOT NULL DEFAULT FALSE
179 CREATE TABLE action.hold_transit_copy (
180 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
181 ) INHERITS (action.transit_copy);
183 CREATE TABLE action.unfulfilled_hold_list (
184 id BIGSERIAL PRIMARY KEY,
185 current_copy BIGINT NOT NULL,
187 circ_lib INT NOT NULL,
188 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()