1 DROP SCHEMA action CASCADE;
7 CREATE TABLE action.survey (
9 name TEXT NOT NULL UNIQUE,
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
16 CREATE TABLE action.survey_question (
17 id SERIAL PRIMARY KEY,
18 survey INT NOT NULL REFERENCES action.survey,
19 question TEXT NOT NULL UNIQUE
22 CREATE TABLE action.survey_answer (
23 id SERIAL PRIMARY KEY,
24 question INT NOT NULL REFERENCES action.survey_question,
25 answer TEXT NOT NULL UNIQUE
28 CREATE TABLE action.survey_response (
29 id BIGSERIAL PRIMARY KEY,
30 usr BIGINT NOT NULL, -- REFERENCES actor.usr
31 survey INT NOT NULL REFERENCES action.survey,
32 question INT NOT NULL REFERENCES action.survey_question,
33 answer INT NOT NULL REFERENCES action.survey_answer,
35 effective_date DATE NOT NULL DEFAULT NOW()::DATE
37 CREATE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS $$
39 NEW.anser_date := NOW()::DATE;
42 $$ LANGUAGE 'plpgsql';
43 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
44 BEFORE INSERT ON action.survey_response
46 EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();
48 CREATE TABLE action.circulation (
49 target_copy BIGINT NOT NULL, -- asset.copy.id
50 circ_lib INT NOT NULL, -- actor.org_unit.id
51 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
52 duration INTERVAL NOT NULL, -- derived from "circ duration" rule
53 renewal_remaining INT NOT NULL, -- derived from "circ duration" rule
54 recuring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
55 recuring_fine NUMERIC(6,2) NOT NULL, -- derived from "circ fine" rule
56 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
57 max_fine NUMERIC(6,2) NOT NULL, -- derived from "max fine" rule
58 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
59 stop_fines TEXT CHECK (finish_reason IN ('CHECKIN','CLAIMSRETURNED','LOST','MAXFINES'))
60 ) INHERITS (money.billable_xact);
61 CREATE INDEX circ_xact_idx ON action.circulation (xact);
62 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;