1 DROP SCHEMA circ_stats CASCADE;
5 CREATE SCHEMA circ_stats;
7 CREATE TABLE circ_stats.record_dim (
8 bib_item_type "char" NOT NULL DEFAULT '?',
9 bib_item_form "char" NOT NULL DEFAULT '?',
10 bib_level "char" NOT NULL DEFAULT '?',
11 bib_control_type "char" NOT NULL DEFAULT '?',
12 bib_char_encoding "char" NOT NULL DEFAULT '?',
13 bib_enc_level "char" NOT NULL DEFAULT '?',
14 bib_audience "char" NOT NULL DEFAULT '?',
16 bib_cat_form TEXT NOT NULL DEFAULT '?',
17 bib_pub_status TEXT NOT NULL DEFAULT '?',
18 bib_pub_date TEXT NOT NULL DEFAULT '?',
19 bib_item_lang TEXT NOT NULL DEFAULT '?'
21 CREATE TRIGGER circ_stats_record_dim_id_trigger
22 BEFORE INSERT ON circ_stats.record_dim
24 EXECUTE PROCEDURE dim_row_hash ();
26 CREATE TABLE circ_stats.usr_dim (
27 usr_id INT NOT NULL DEFAULT 0,
28 usr_grp INT NOT NULL, -- aka profile
29 usr_standing INT NOT NULL,
30 usr_home_ou INT NOT NULL,
34 usr_post_code TEXT NOT NULL
36 CREATE TRIGGER circ_stats_usr_dim_id_trigger
37 BEFORE INSERT ON circ_stats.usr_dim
39 EXECUTE PROCEDURE dim_row_hash ();
42 CREATE TABLE circ_stats.copy_dim (
43 copy_holdable BOOL NOT NULL,
44 copy_ref BOOL NOT NULL,
45 copy_circulate BOOL NOT NULL,
46 copy_opac_visible BOOL NOT NULL,
47 copy_circ_lib INT NOT NULL,
48 copy_location INT NOT NULL,
49 copy_fine_level INT NOT NULL,
50 copy_loan_duration INT NOT NULL,
51 copy_location INT NOT NULL,
53 copy_circ_modifer TEXT,
54 copy_circ_as_type TEXT,
55 copy_call_number_label TEXT NOT NULL
57 CREATE TRIGGER circ_stats_copy_dim_id_trigger
58 BEFORE INSERT ON circ_stats.copy_dim
60 EXECUTE PROCEDURE dim_row_hash ();
63 CREATE TABLE circ_stats.circ_dim (
64 circ_lib INT NOT NULL,
66 circ_opac_renewal BOOL,
67 circ_self_checkout BOOL,
68 circ_recuring_fine NUMERIC(6,2) NOT NULL,
69 circ_max_fine NUMERIC(6,2) NOT NULL,
70 circ_fine_interval INTERVAL NOT NULL,
71 circ_duration INTERVAL NOT NULL,
72 circ_due_date TIMESTAMP WITH TIME ZONE NOT NULL,
74 circ_duration_rule TEXT NOT NULL,
75 circ_recuring_fine_rule TEXT NOT NULL,
76 circ_max_fine_rule TEXT NOT NULL,
79 CREATE TRIGGER circ_stats_circ_dim_id_trigger
80 BEFORE INSERT ON circ_stats.circ_dim
82 EXECUTE PROCEDURE dim_row_hash ();
85 CREATE TABLE circ_stats.checkout_fact (
87 circ_id BIGINT PRIMARY KEY,
88 circ_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
89 circ_dim TEXT NOT NULL REFERECES circ_stats.circ_dim (id),
92 usr_dim TEXT NOT NULL REFERECES circ_stats.usr_dim (id),
95 copy_dim TEXT NOT NULL REFERECES circ_stats.copy_dim (id),
98 bib_dim TEXT NOT NULL REFERECES circ_stats.record_dim (id)
100 CREATE INDEX circ_stats_checkout_fact_time_idx ON circ_stats.checkout_fact (circ_time);
101 CREATE INDEX circ_stats_checkout_fact_circ_dim_idx ON circ_stats.checkout_fact (circ_dim);
102 CREATE INDEX circ_stats_checkout_fact_usr_dim_idx ON circ_stats.checkout_fact (usr_dim);
103 CREATE INDEX circ_stats_checkout_fact_copy_dim_idx ON circ_stats.checkout_fact (copy_dim);
104 CREATE INDEX circ_stats_checkout_fact_bib_dim_idx ON circ_stats.checkout_fact (bib_dim);
106 CREATE TABLE circ_stats.renewal_fact (
108 circ_id BIGINT PRIMARY KEY,
109 circ_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
110 circ_dim TEXT NOT NULL REFERECES circ_stats.circ_dim (id),
113 usr_dim TEXT NOT NULL REFERECES circ_stats.usr_dim (id),
116 copy_dim TEXT NOT NULL REFERECES circ_stats.copy_dim (id),
119 bib_dim TEXT NOT NULL REFERECES circ_stats.record_dim (id)
121 CREATE INDEX circ_stats_renewal_fact_time_idx ON circ_stats.renewal_fact (circ_time);
122 CREATE INDEX circ_stats_renewal_fact_circ_dim_idx ON circ_stats.renewal_fact (circ_dim);
123 CREATE INDEX circ_stats_renewal_fact_usr_dim_idx ON circ_stats.renewal_fact (usr_dim);
124 CREATE INDEX circ_stats_renewal_fact_copy_dim_idx ON circ_stats.renewal_fact (copy_dim);
125 CREATE INDEX circ_stats_renewal_fact_bib_dim_idx ON circ_stats.renewal_fact (bib_dim);
127 CREATE TABLE circ_stats.checkin_fact (
129 circ_id BIGINT PRIMARY KEY,
130 circ_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
131 circ_dim TEXT NOT NULL REFERECES circ_stats.circ_dim (id),
134 usr_dim TEXT NOT NULL REFERECES circ_stats.usr_dim (id),
137 copy_dim TEXT NOT NULL REFERECES circ_stats.copy_dim (id),
140 bib_dim TEXT NOT NULL REFERECES circ_stats.record_dim (id)
142 CREATE INDEX circ_stats_checkin_fact_time_idx ON circ_stats.checkin_fact (circ_time);
143 CREATE INDEX circ_stats_checkin_fact_circ_dim_idx ON circ_stats.checkin_fact (circ_dim);
144 CREATE INDEX circ_stats_checkin_fact_usr_dim_idx ON circ_stats.checkin_fact (usr_dim);
145 CREATE INDEX circ_stats_checkin_fact_copy_dim_idx ON circ_stats.checkin_fact (copy_dim);
146 CREATE INDEX circ_stats_checkin_fact_bib_dim_idx ON circ_stats.checkin_fact (bib_dim);
148 CREATE OR REPLACE circ_stats.checkout_full_view AS
162 circ_recuring_fine_rule,
185 copy_call_number_label,
199 FROM circ_stats.checkout_fact f
200 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
201 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
202 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
203 JOIN circ_stats.bib_dim bd ON (f.bib_dim = bd.id);
205 CREATE OR REPLACE circ_stats.checkin_full_view AS
219 circ_recuring_fine_rule,
242 copy_call_number_label,
256 FROM circ_stats.checkin_fact f
257 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
258 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
259 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
260 JOIN circ_stats.bib_dim bd ON (f.bib_dim = bd.id);
262 CREATE OR REPLACE circ_stats.renewal_full_view AS
276 circ_recuring_fine_rule,
299 copy_call_number_label,
313 FROM circ_stats.renewal_fact f
314 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
315 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
316 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
317 JOIN circ_stats.bib_dim bd ON (f.bib_dim = bd.id);