1 DROP SCHEMA action CASCADE;
7 CREATE TABLE action.survey (
9 owner INT NOT NULL REFERENCES actor.org_unit (id),
11 description TEXT NOT NULL,
12 start_date DATE NOT NULL DEFAULT NOW(),
13 end_date DATE NOT NULL DEFAULT NOW() + '10 years'::INTERVAL,
14 usr_summary BOOL NOT NULL DEFAULT FALSE,
15 opac BOOL NOT NULL DEFAULT FALSE,
16 poll BOOL NOT NULL DEFAULT FALSE,
17 required BOOL NOT NULL DEFAULT FALSE
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 renewal BOOL NOT NULL DEFAULT FALSE,
60 circ_lib INT NOT NULL, -- actor.org_unit.id
61 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
62 duration INTERVAL NOT NULL, -- derived from "circ duration" rule
63 renewal_remaining INT NOT NULL, -- derived from "circ duration" rule
64 recuring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
65 recuring_fine NUMERIC(6,2) NOT NULL, -- derived from "circ fine" rule
66 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
67 max_fine NUMERIC(6,2) NOT NULL, -- derived from "max fine" rule
68 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
69 due_date TIMESTAMP WITH TIME ZONE NOT NULL,
70 stop_fines TEXT CHECK (stop_fines IN ('CHECKIN','CLAIMSRETURNED','LOST','MAXFINES','RENEW'))
71 ) INHERITS (money.billable_xact);
72 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;
74 CREATE OR REPLACE VIEW action.open_cirulations AS
76 FROM action.circulation
77 WHERE xact_finish IS NULL;
79 CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$
81 IF NEW.stop_fines <> OLD.stop_fines AND NEW.stop_fines = 'CLAIMSRETURNED' THEN
82 UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
86 $$ LANGUAGE 'plpgsql';
87 CREATE TRIGGER action_circulation_claims_returned
88 BEFORE UPDATE ON action.circulation
90 EXECUTE PROCEDURE action.circulation_claims_returned ();
93 CREATE TABLE action.hold_request (
94 id SERIAL PRIMARY KEY,
95 request_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
96 capture_time TIMESTAMP WITH TIME ZONE,
97 fulfillment_time TIMESTAMP WITH TIME ZONE,
98 checkin_time TIMESTAMP WITH TIME ZONE,
99 return_time TIMESTAMP WITH TIME ZONE,
100 prev_check_time TIMESTAMP WITH TIME ZONE,
101 expire_time TIMESTAMP WITH TIME ZONE,
102 requestor INT NOT NULL REFERENCES actor.usr (id),
103 usr INT NOT NULL REFERENCES actor.usr (id),
104 hold_type CHAR NOT NULL CHECK (hold_type IN ('M','T','V','C')),
105 holdable_formats TEXT,
108 target BIGINT NOT NULL, -- see hold_type
109 selection_depth INT NOT NULL DEFAULT 0,
110 pickup_lib INT NOT NULL REFERENCES actor.org_unit,
111 current_copy BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL
115 CREATE TABLE action.hold_notification (
116 id SERIAL PRIMARY KEY,
117 hold INT NOT NULL REFERENCES action.hold_request (id),
118 method TEXT NOT NULL, -- eh...
119 notify_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
123 CREATE TABLE action.hold_copy_map (
124 id SERIAL PRIMARY KEY,
125 hold INT NOT NULL REFERENCES action.hold_request (id) ON DELETE CASCADE,
126 target_copy BIGINT NOT NULL REFERENCES asset.copy (id) ON DELETE CASCADE,
127 CONSTRAINT copy_once_per_hold UNIQUE (hold,target_copy)
130 CREATE TABLE action.transit_copy (
131 id SERIAL PRIMARY KEY,
132 source INT NOT NULL REFERENCES actor.org_unit (id),
133 dest INT NOT NULL REFERENCES actor.org_unit (id),
134 persistant_transfer BOOL NOT NULL DEFAULT FALSE,
135 source_send_time TIMESTAMP WITH TIME ZONE,
136 dest_recv_time TIMESTAMP WITH TIME ZONE,
137 prev_hop INT REFERENCES action.transit_copy (id)
140 CREATE TABLE action.hold_transit_copy (
141 hold INT NOT NULL REFERENCES action.hold_request (id)
142 ) INHERITS (action.transit_copy);