1 DROP SCHEMA action CASCADE;
7 CREATE TABLE action.survey (
9 owner INT NOT NULL REFERENCES actor.org_unit (id),
10 name TEXT NOT NULL UNIQUE,
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 required BOOL NOT NULL DEFAULT FALSE
19 CREATE TABLE action.survey_question (
20 id SERIAL PRIMARY KEY,
21 survey INT NOT NULL REFERENCES action.survey,
22 question TEXT NOT NULL UNIQUE
25 CREATE TABLE action.survey_answer (
26 id SERIAL PRIMARY KEY,
27 question INT NOT NULL REFERENCES action.survey_question,
28 answer TEXT NOT NULL UNIQUE
31 CREATE TABLE action.survey_response (
32 id BIGSERIAL PRIMARY KEY,
33 usr INT NOT NULL, -- REFERENCES actor.usr
34 survey INT NOT NULL REFERENCES action.survey,
35 question INT NOT NULL REFERENCES action.survey_question,
36 answer INT NOT NULL REFERENCES action.survey_answer,
38 effective_date DATE NOT NULL DEFAULT NOW()::DATE
40 CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS '
42 NEW.anser_date := NOW()::DATE;
46 CREATE TRIGGER action_survey_response_answer_date_fixup_tgr
47 BEFORE INSERT ON action.survey_response
49 EXECUTE PROCEDURE action.survey_response_answer_date_fixup ();
51 CREATE TABLE action.circulation (
52 target_copy BIGINT NOT NULL, -- asset.copy.id
53 circ_lib INT NOT NULL, -- actor.org_unit.id
54 duration_rule TEXT NOT NULL, -- name of "circ duration" rule
55 duration INTERVAL NOT NULL, -- derived from "circ duration" rule
56 renewal_remaining INT NOT NULL, -- derived from "circ duration" rule
57 recuring_fine_rule TEXT NOT NULL, -- name of "circ fine" rule
58 recuring_fine NUMERIC(6,2) NOT NULL, -- derived from "circ fine" rule
59 max_fine_rule TEXT NOT NULL, -- name of "max fine" rule
60 max_fine NUMERIC(6,2) NOT NULL, -- derived from "max fine" rule
61 fine_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, -- derived from "circ fine" rule
62 stop_fines TEXT CHECK (stop_fines IN ('CHECKIN','CLAIMSRETURNED','LOST','MAXFINES'))
63 ) INHERITS (money.billable_xact);
64 CREATE INDEX circ_open_xacts_idx ON action.circulation (usr) WHERE xact_finish IS NULL;