1 DROP SCHEMA circ_stats CASCADE;
5 CREATE SCHEMA circ_stats;
7 CREATE TABLE circ_stats.circulation (
8 id BIGSERIAL PRIMARY KEY,
9 target_copy BIGINT NOT NULL,
11 circ_lib INT NOT NULL,
12 renewal_remaining INT NOT NULL,
16 xact_start TIMESTAMP WITH TIME ZONE NOT NULL,
17 xact_finish TIMESTAMP WITH TIME ZONE,
18 due_date TIMESTAMP WITH TIME ZONE NOT NULL,
19 stop_fines_time TIMESTAMP WITH TIME ZONE,
20 checkin_time TIMESTAMP WITH TIME ZONE,
21 duration INTERVAL NOT NULL,
22 fine_interval INTERVAL NOT NULL,
23 recuring_fine NUMERIC(6,2) NOT NULL,
24 max_fine NUMERIC(6,2) NOT NULL,
25 opac_renewal BOOL NOT NULL,
26 phone_renewal BOOL NOT NULL,
27 desk_renewal BOOL NOT NULL,
28 duration_rule TEXT NOT NULL,
29 recuring_fine_rule TEXT NOT NULL,
30 max_fine_rule TEXT NOT NULL,
34 CREATE TABLE circ_stats.circulation_checkin () INHERITS (circ_stats.circulation);
35 CREATE TABLE circ_stats.circulation_claimsreturned () INHERITS (circ_stats.circulation);
36 CREATE TABLE circ_stats.circulation_lost () INHERITS (circ_stats.circulation);
37 CREATE TABLE circ_stats.circulation_maxfines () INHERITS (circ_stats.circulation);
38 CREATE TABLE circ_stats.circulation_renew () INHERITS (circ_stats.circulation);
41 CREATE TABLE circ_stats.record_dim (
42 id BIGSERIAL PRIMARY KEY,
43 bib_item_type TEXT NOT NULL DEFAULT '?',
44 bib_item_form TEXT NOT NULL DEFAULT '?',
45 bib_level TEXT NOT NULL DEFAULT '?',
46 bib_control_type TEXT NOT NULL DEFAULT '?',
47 bib_char_encoding TEXT NOT NULL DEFAULT '?',
48 bib_enc_level TEXT NOT NULL DEFAULT '?',
49 bib_audience TEXT NOT NULL DEFAULT '?',
50 bib_cat_form TEXT NOT NULL DEFAULT '?',
51 bib_pub_status TEXT NOT NULL DEFAULT '?',
52 bib_pub_date TEXT NOT NULL DEFAULT '?',
53 bib_item_lang TEXT NOT NULL DEFAULT '?',
54 bib_title TEXT NOT NULL DEFAULT '?',
55 bib_author TEXT NOT NULL DEFAULT '?'
57 CREATE TRIGGER circ_stats_record_dim_id_trigger
58 BEFORE INSERT ON circ_stats.record_dim
60 EXECUTE PROCEDURE dim_row_hash ();
62 CREATE TABLE circ_stats.usr_dim (
63 id BIGSERIAL PRIMARY KEY,
64 usr_id INT NOT NULL DEFAULT 0,
65 usr_grp INT NOT NULL, -- aka profile
66 usr_standing INT NOT NULL,
67 usr_home_ou INT NOT NULL,
70 usr_post_code TEXT NOT NULL
72 CREATE TRIGGER circ_stats_usr_dim_id_trigger
73 BEFORE INSERT ON circ_stats.usr_dim
75 EXECUTE PROCEDURE dim_row_hash ();
78 CREATE TABLE circ_stats.copy_dim (
79 id BIGSERIAL PRIMARY KEY,
80 copy_holdable BOOL NOT NULL,
81 copy_ref BOOL NOT NULL,
82 copy_circulate BOOL NOT NULL,
83 copy_opac_visible BOOL NOT NULL,
84 copy_circ_lib INT NOT NULL,
85 copy_location INT NOT NULL,
86 copy_fine_level INT NOT NULL,
87 copy_loan_duration INT NOT NULL,
88 copy_circ_modifer TEXT,
89 copy_circ_as_type TEXT,
90 copy_call_number_label TEXT NOT NULL
92 CREATE TRIGGER circ_stats_copy_dim_id_trigger
93 BEFORE INSERT ON circ_stats.copy_dim
95 EXECUTE PROCEDURE dim_row_hash ();
98 CREATE TABLE circ_stats.circ_dim (
99 id BIGSERIAL PRIMARY KEY,
100 circ_opac_renewal BOOL,
101 circ_desk_renewal BOOL,
102 circ_phone_renewal BOOL,
103 circ_self_checkout BOOL,
104 circ_recuring_fine NUMERIC(6,2) NOT NULL,
105 circ_max_fine NUMERIC(6,2) NOT NULL,
106 circ_fine_interval INTERVAL NOT NULL,
107 circ_duration INTERVAL NOT NULL,
108 circ_due_date TIMESTAMP WITH TIME ZONE NOT NULL,
109 circ_duration_rule TEXT NOT NULL,
110 circ_recuring_fine_rule TEXT NOT NULL,
111 circ_max_fine_rule TEXT NOT NULL,
114 CREATE TRIGGER circ_stats_circ_dim_id_trigger
115 BEFORE INSERT ON circ_stats.circ_dim
117 EXECUTE PROCEDURE dim_row_hash ();
120 CREATE TABLE circ_stats.checkout_fact (
122 circ_id BIGINT PRIMARY KEY,
123 circ_lib INT NOT NULL,
125 circ_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
126 circ_dim INT NOT NULL REFERENCES circ_stats.circ_dim (id),
129 usr_dim INT NOT NULL REFERENCES circ_stats.usr_dim (id),
132 copy_dim INT NOT NULL REFERENCES circ_stats.copy_dim (id),
135 bib_dim INT NOT NULL REFERENCES circ_stats.record_dim (id)
137 CREATE INDEX circ_stats_checkout_fact_time_idx ON circ_stats.checkout_fact (circ_timestamp);
138 CREATE INDEX circ_stats_checkout_fact_circ_dim_idx ON circ_stats.checkout_fact (circ_dim);
139 CREATE INDEX circ_stats_checkout_fact_usr_dim_idx ON circ_stats.checkout_fact (usr_dim);
140 CREATE INDEX circ_stats_checkout_fact_copy_dim_idx ON circ_stats.checkout_fact (copy_dim);
141 CREATE INDEX circ_stats_checkout_fact_bib_dim_idx ON circ_stats.checkout_fact (bib_dim);
143 CREATE TABLE circ_stats.renewal_fact (
145 circ_id BIGINT PRIMARY KEY,
146 circ_lib INT NOT NULL,
148 circ_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
149 circ_dim INT NOT NULL REFERENCES circ_stats.circ_dim (id),
152 usr_dim INT NOT NULL REFERENCES circ_stats.usr_dim (id),
155 copy_dim INT NOT NULL REFERENCES circ_stats.copy_dim (id),
158 bib_dim INT NOT NULL REFERENCES circ_stats.record_dim (id)
160 CREATE INDEX circ_stats_renewal_fact_time_idx ON circ_stats.renewal_fact (circ_timestamp);
161 CREATE INDEX circ_stats_renewal_fact_circ_dim_idx ON circ_stats.renewal_fact (circ_dim);
162 CREATE INDEX circ_stats_renewal_fact_usr_dim_idx ON circ_stats.renewal_fact (usr_dim);
163 CREATE INDEX circ_stats_renewal_fact_copy_dim_idx ON circ_stats.renewal_fact (copy_dim);
164 CREATE INDEX circ_stats_renewal_fact_bib_dim_idx ON circ_stats.renewal_fact (bib_dim);
166 CREATE TABLE circ_stats.checkin_fact (
168 circ_id BIGINT PRIMARY KEY,
169 circ_lib INT NOT NULL,
171 circ_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
172 circ_dim INT NOT NULL REFERENCES circ_stats.circ_dim (id),
175 usr_dim INT NOT NULL REFERENCES circ_stats.usr_dim (id),
178 copy_dim INT NOT NULL REFERENCES circ_stats.copy_dim (id),
181 bib_dim INT NOT NULL REFERENCES circ_stats.record_dim (id)
183 CREATE INDEX circ_stats_checkin_fact_time_idx ON circ_stats.checkin_fact (circ_timestamp);
184 CREATE INDEX circ_stats_checkin_fact_circ_dim_idx ON circ_stats.checkin_fact (circ_dim);
185 CREATE INDEX circ_stats_checkin_fact_usr_dim_idx ON circ_stats.checkin_fact (usr_dim);
186 CREATE INDEX circ_stats_checkin_fact_copy_dim_idx ON circ_stats.checkin_fact (copy_dim);
187 CREATE INDEX circ_stats_checkin_fact_bib_dim_idx ON circ_stats.checkin_fact (bib_dim);
189 CREATE OR REPLACE VIEW circ_stats.checkout_full_view AS
203 circ_recuring_fine_rule,
225 copy_call_number_label,
239 FROM circ_stats.checkout_fact f
240 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
241 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
242 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
243 JOIN circ_stats.record_dim bd ON (f.bib_dim = bd.id);
245 CREATE OR REPLACE VIEW circ_stats.checkin_full_view AS
259 circ_recuring_fine_rule,
281 copy_call_number_label,
295 FROM circ_stats.checkin_fact f
296 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
297 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
298 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
299 JOIN circ_stats.record_dim bd ON (f.bib_dim = bd.id);
301 CREATE OR REPLACE VIEW circ_stats.renewal_full_view AS
315 circ_recuring_fine_rule,
337 copy_call_number_label,
351 FROM circ_stats.renewal_fact f
352 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
353 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
354 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
355 JOIN circ_stats.record_dim bd ON (f.bib_dim = bd.id);