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_opac_renewal BOOL,
65 circ_desk_renewal BOOL,
66 circ_phone_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_lib INT NOT NULL,
90 circ_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
91 circ_dim TEXT NOT NULL REFERECES circ_stats.circ_dim (id),
94 usr_dim TEXT NOT NULL REFERECES circ_stats.usr_dim (id),
97 copy_dim TEXT NOT NULL REFERECES circ_stats.copy_dim (id),
100 bib_dim TEXT NOT NULL REFERECES circ_stats.record_dim (id)
102 CREATE INDEX circ_stats_checkout_fact_time_idx ON circ_stats.checkout_fact (circ_time);
103 CREATE INDEX circ_stats_checkout_fact_circ_dim_idx ON circ_stats.checkout_fact (circ_dim);
104 CREATE INDEX circ_stats_checkout_fact_usr_dim_idx ON circ_stats.checkout_fact (usr_dim);
105 CREATE INDEX circ_stats_checkout_fact_copy_dim_idx ON circ_stats.checkout_fact (copy_dim);
106 CREATE INDEX circ_stats_checkout_fact_bib_dim_idx ON circ_stats.checkout_fact (bib_dim);
108 CREATE TABLE circ_stats.renewal_fact (
110 circ_id BIGINT PRIMARY KEY,
111 circ_lib INT NOT NULL,
113 circ_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
114 circ_dim TEXT NOT NULL REFERECES circ_stats.circ_dim (id),
117 usr_dim TEXT NOT NULL REFERECES circ_stats.usr_dim (id),
120 copy_dim TEXT NOT NULL REFERECES circ_stats.copy_dim (id),
123 bib_dim TEXT NOT NULL REFERECES circ_stats.record_dim (id)
125 CREATE INDEX circ_stats_renewal_fact_time_idx ON circ_stats.renewal_fact (circ_time);
126 CREATE INDEX circ_stats_renewal_fact_circ_dim_idx ON circ_stats.renewal_fact (circ_dim);
127 CREATE INDEX circ_stats_renewal_fact_usr_dim_idx ON circ_stats.renewal_fact (usr_dim);
128 CREATE INDEX circ_stats_renewal_fact_copy_dim_idx ON circ_stats.renewal_fact (copy_dim);
129 CREATE INDEX circ_stats_renewal_fact_bib_dim_idx ON circ_stats.renewal_fact (bib_dim);
131 CREATE TABLE circ_stats.checkin_fact (
133 circ_id BIGINT PRIMARY KEY,
134 circ_lib INT NOT NULL,
136 circ_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
137 circ_dim TEXT NOT NULL REFERECES circ_stats.circ_dim (id),
140 usr_dim TEXT NOT NULL REFERECES circ_stats.usr_dim (id),
143 copy_dim TEXT NOT NULL REFERECES circ_stats.copy_dim (id),
146 bib_dim TEXT NOT NULL REFERECES circ_stats.record_dim (id)
148 CREATE INDEX circ_stats_checkin_fact_time_idx ON circ_stats.checkin_fact (circ_time);
149 CREATE INDEX circ_stats_checkin_fact_circ_dim_idx ON circ_stats.checkin_fact (circ_dim);
150 CREATE INDEX circ_stats_checkin_fact_usr_dim_idx ON circ_stats.checkin_fact (usr_dim);
151 CREATE INDEX circ_stats_checkin_fact_copy_dim_idx ON circ_stats.checkin_fact (copy_dim);
152 CREATE INDEX circ_stats_checkin_fact_bib_dim_idx ON circ_stats.checkin_fact (bib_dim);
154 CREATE OR REPLACE circ_stats.checkout_full_view AS
168 circ_recuring_fine_rule,
191 copy_call_number_label,
205 FROM circ_stats.checkout_fact f
206 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
207 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
208 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
209 JOIN circ_stats.bib_dim bd ON (f.bib_dim = bd.id);
211 CREATE OR REPLACE circ_stats.checkin_full_view AS
225 circ_recuring_fine_rule,
248 copy_call_number_label,
262 FROM circ_stats.checkin_fact f
263 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
264 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
265 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
266 JOIN circ_stats.bib_dim bd ON (f.bib_dim = bd.id);
268 CREATE OR REPLACE circ_stats.renewal_full_view AS
282 circ_recuring_fine_rule,
305 copy_call_number_label,
319 FROM circ_stats.renewal_fact f
320 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
321 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
322 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
323 JOIN circ_stats.bib_dim bd ON (f.bib_dim = bd.id);