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 OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
95 IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
96 IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
97 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
99 IF NEW.stop_fines = 'LOST' THEN
100 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
105 $$ LANGUAGE 'plpgsql';
106 CREATE TRIGGER action_circulation_stop_fines_tgr
107 BEFORE UPDATE ON action.circulation
109 EXECUTE PROCEDURE action.circulation_claims_returned ();
112 CREATE TABLE action.hold_request (
113 id SERIAL PRIMARY KEY,
114 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
115 capture_time TIMESTAMP WITH TIME ZONE,
116 fulfillment_time TIMESTAMP WITH TIME ZONE,
117 checkin_time TIMESTAMP WITH TIME ZONE,
118 return_time TIMESTAMP WITH TIME ZONE,
119 prev_check_time TIMESTAMP WITH TIME ZONE,
120 expire_time TIMESTAMP WITH TIME ZONE,
121 target BIGINT NOT NULL, -- see hold_type
122 current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL,
123 fulfillment_staff INT REFERENCES actor.usr (id),
124 fulfillment_lib INT REFERENCES actor.org_unit (id),
125 request_lib INT NOT NULL REFERENCES actor.org_unit (id),
126 requestor INT NOT NULL REFERENCES actor.usr (id),
127 usr INT NOT NULL REFERENCES actor.usr (id),
128 selection_depth INT NOT NULL DEFAULT 0,
129 pickup_lib INT NOT NULL REFERENCES actor.org_unit,
130 hold_type "char" NOT NULL CHECK (hold_type IN ('M','T','V','C')),
131 holdable_formats TEXT,
137 CREATE TABLE action.hold_notification (
138 id SERIAL PRIMARY KEY,
139 hold INT NOT NULL REFERENCES action.hold_request (id),
140 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
141 method TEXT NOT NULL, -- eh...
145 CREATE TABLE action.hold_copy_map (
146 id SERIAL PRIMARY KEY,
147 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE,
148 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
149 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
152 CREATE TABLE action.transit_copy (
153 id SERIAL PRIMARY KEY,
154 source_send_time TIMESTAMP WITH TIME ZONE,
155 dest_recv_time TIMESTAMP WITH TIME ZONE,
156 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
157 source INT NOT NULL REFERENCES actor.org_unit (id),
158 dest INT NOT NULL REFERENCES actor.org_unit (id),
159 prev_hop INT REFERENCES action.transit_copy (id),
160 copy_status INT NOT NULL REFERENCES config.copy_status (id),
161 persistant_transfer BOOL NOT NULL DEFAULT FALSE
164 CREATE TABLE action.hold_transit_copy (
165 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
166 ) INHERITS (action.transit_copy);
168 CREATE TABLE action.unfulfilled_hold_list (
169 id BIGSERIAL PRIMARY KEY,
170 current_copy BIGINT NOT NULL,
172 circ_lib INT NOT NULL,
173 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()