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 renewal_remaining INT NOT NULL, -- derived from "circ duration" rule
61 renewal BOOL NOT NULL DEFAULT FALSE,
62 due_date TIMESTAMP WITH TIME ZONE NOT NULL,
63 duration INTERVAL NOT NULL, -- derived from "circ duration" rule
64 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
65 recuring_fine NUMERIC(6,2) NOT NULL, -- derived from "circ fine" rule
66 max_fine NUMERIC(6,2) NOT NULL, -- derived from "max fine" rule
67 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
68 recuring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
69 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
70 stop_fines TEXT CHECK (stop_fines IN ('CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW','LONGOVERDUE'))
71 ) INHERITS (money.billable_xact);
72 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
74 CREATE VIEW action.open_circulation AS
76 FROM action.circulation
77 WHERE xact_finish IS NULL
78 AND ( stop_fines IS NULL OR
79 stop_fines IN ('CLAIMSRETURNED','MAXFINES','LONGOVERDUE')
84 CREATE OR REPLACE VIEW action.open_cirulations AS
86 FROM action.circulation
87 WHERE xact_finish IS NULL;
89 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
91 IF NEW.stop_fines <> OLD.stop_fines AND NEW.stop_fines = 'CLAIMSRETURNED' THEN
92 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
94 IF NEW.stop_fines <> OLD.stop_fines AND NEW.stop_fines = 'LOST' THEN
95 UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
97 IF NEW.stop_fines <> OLD.stop_fines AND NEW.stop_fines = 'MISSING' THEN
98 UPDATE asset.copy SET status = 4 WHERE id = NEW.target_copy;
102 $$ LANGUAGE 'plpgsql';
103 CREATE TRIGGER action_circulation_claims_returned
104 BEFORE UPDATE ON action.circulation
106 EXECUTE PROCEDURE action.circulation_claims_returned ();
109 CREATE TABLE action.hold_request (
110 id SERIAL PRIMARY KEY,
111 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
112 capture_time TIMESTAMP WITH TIME ZONE,
113 fulfillment_time TIMESTAMP WITH TIME ZONE,
114 checkin_time TIMESTAMP WITH TIME ZONE,
115 return_time TIMESTAMP WITH TIME ZONE,
116 prev_check_time TIMESTAMP WITH TIME ZONE,
117 expire_time TIMESTAMP WITH TIME ZONE,
118 hold_type "char" NOT NULL CHECK (hold_type IN ('M','T','V','C')),
119 target BIGINT NOT NULL, -- see hold_type
120 current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL,
121 requestor INT NOT NULL REFERENCES actor.usr (id),
122 usr INT NOT NULL REFERENCES actor.usr (id),
123 selection_depth INT NOT NULL DEFAULT 0,
124 pickup_lib INT NOT NULL REFERENCES actor.org_unit,
125 holdable_formats TEXT,
131 CREATE TABLE action.hold_notification (
132 id SERIAL PRIMARY KEY,
133 hold INT NOT NULL REFERENCES action.hold_request (id),
134 method TEXT NOT NULL, -- eh...
135 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
139 CREATE TABLE action.hold_copy_map (
140 id SERIAL PRIMARY KEY,
141 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE,
142 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
143 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
146 CREATE TABLE action.transit_copy (
147 id SERIAL PRIMARY KEY,
148 source_send_time TIMESTAMP WITH TIME ZONE,
149 dest_recv_time TIMESTAMP WITH TIME ZONE,
150 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
151 source INT NOT NULL REFERENCES actor.org_unit (id),
152 dest INT NOT NULL REFERENCES actor.org_unit (id),
153 prev_hop INT REFERENCES action.transit_copy (id),
154 persistant_transfer BOOL NOT NULL DEFAULT FALSE
157 CREATE TABLE action.hold_transit_copy (
158 hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
159 ) INHERITS (action.transit_copy);
161 CREATE TABLE action.unfulfilled_hold_list (
162 id BIGSERIAL PRIMARY KEY,
163 current_copy BIGINT NOT NULL,
165 circ_lib INT NOT NULL,
166 fail_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()