refactored layout, added hashing triggers, early views
[Evergreen.git] / Open-ILS / src / sql / Pg / stats / circ_stats.sql
1 DROP SCHEMA circ_stats CASCADE;
2
3 BEGIN;
4
5 CREATE SCHEMA circ_stats;
6
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 '?',
15         id                      TEXT    PRIMARY KEY,
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 '?'
20 ) WITHOUT OIDS;
21 CREATE TRIGGER circ_stats_record_dim_id_trigger
22         BEFORE INSERT ON circ_stats.record_dim
23         FOR EACH ROW
24         EXECUTE PROCEDURE dim_row_hash ();
25
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,
31         id                      TEXT    PRIMARY KEY,
32         usr_county              TEXT,
33         usr_city                TEXT,
34         usr_post_code           TEXT    NOT NULL
35 ) WITHOUT OIDS;
36 CREATE TRIGGER circ_stats_usr_dim_id_trigger
37         BEFORE INSERT ON circ_stats.usr_dim
38         FOR EACH ROW
39         EXECUTE PROCEDURE dim_row_hash ();
40
41
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,
52         id                      TEXT    PRIMARY KEY,
53         copy_circ_modifer       TEXT,
54         copy_circ_as_type       TEXT,
55         copy_call_number_label  TEXT    NOT NULL
56 ) WITHOUT OIDS;
57 CREATE TRIGGER circ_stats_copy_dim_id_trigger
58         BEFORE INSERT ON circ_stats.copy_dim
59         FOR EACH ROW
60         EXECUTE PROCEDURE dim_row_hash ();
61
62
63 CREATE TABLE circ_stats.circ_dim (
64         circ_lib                INT                             NOT NULL,
65         circ_staff              INT,
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,
73         id                      TEXT                            PRIMARY KEY,
74         circ_duration_rule      TEXT                            NOT NULL,
75         circ_recuring_fine_rule TEXT                            NOT NULL,
76         circ_max_fine_rule      TEXT                            NOT NULL,
77         circ_stop_fines         TEXT
78 ) WITHOUT OIDS;
79 CREATE TRIGGER circ_stats_circ_dim_id_trigger
80         BEFORE INSERT ON circ_stats.circ_dim
81         FOR EACH ROW
82         EXECUTE PROCEDURE dim_row_hash ();
83
84
85 CREATE TABLE circ_stats.checkout_fact (
86         -- circulation info
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),
90
91         -- patron info
92         usr_dim                 TEXT                            NOT NULL REFERECES circ_stats.usr_dim (id),
93
94         -- copy info
95         copy_dim                TEXT                            NOT NULL REFERECES circ_stats.copy_dim (id),
96
97         -- bib record info
98         bib_dim                 TEXT                            NOT NULL REFERECES circ_stats.record_dim (id)
99 ) WITHOUT OIDS;
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);
105
106 CREATE TABLE circ_stats.renewal_fact (
107         -- circulation info
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),
111
112         -- patron info
113         usr_dim                 TEXT                            NOT NULL REFERECES circ_stats.usr_dim (id),
114
115         -- copy info
116         copy_dim                TEXT                            NOT NULL REFERECES circ_stats.copy_dim (id),
117
118         -- bib record info
119         bib_dim                 TEXT                            NOT NULL REFERECES circ_stats.record_dim (id)
120 ) WITHOUT OIDS;
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);
126
127 CREATE TABLE circ_stats.checkin_fact (
128         -- circulation info
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),
132
133         -- patron info
134         usr_dim                 TEXT                            NOT NULL REFERECES circ_stats.usr_dim (id),
135
136         -- copy info
137         copy_dim                TEXT                            NOT NULL REFERECES circ_stats.copy_dim (id),
138
139         -- bib record info
140         bib_dim                 TEXT                            NOT NULL REFERECES circ_stats.record_dim (id)
141 ) WITHOUT OIDS;
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);
147
148 CREATE OR REPLACE circ_stats.checkout_full_view AS
149         SELECT  circ_id,
150                 circ_timestamp,
151
152                 circ_lib,
153                 circ_staff,
154                 circ_opac_renewal,
155                 circ_self_checkout,
156                 circ_recuring_fine,
157                 circ_max_fine,
158                 circ_fine_interval,
159                 circ_duration,
160                 circ_due_date,
161                 circ_duration_rule,
162                 circ_recuring_fine_rule,
163                 circ_max_fine_rule,
164                 circ_stop_fines,
165
166                 usr_id,
167                 usr_grp,
168                 usr_standing,
169                 usr_home_ou,
170                 usr_county,
171                 usr_city,
172                 usr_post_code,
173
174                 copy_holdable,
175                 copy_ref,
176                 copy_circulate,
177                 copy_opac_visible,
178                 copy_circ_lib,
179                 copy_location,
180                 copy_fine_level,
181                 copy_loan_duration,
182                 copy_location,
183                 copy_circ_modifer,
184                 copy_circ_as_type,
185                 copy_call_number_label,
186
187                 bib_item_type,
188                 bib_item_form,
189                 bib_level,
190                 bib_control_type,
191                 bib_char_encoding,
192                 bib_enc_level,
193                 bib_audience,
194                 bib_cat_form,
195                 bib_pub_status,
196                 bib_pub_date,
197                 bib_item_lang,
198
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);
204
205 CREATE OR REPLACE circ_stats.checkin_full_view AS
206         SELECT  circ_id,
207                 circ_timestamp,
208
209                 circ_lib,
210                 circ_staff,
211                 circ_opac_renewal,
212                 circ_self_checkout,
213                 circ_recuring_fine,
214                 circ_max_fine,
215                 circ_fine_interval,
216                 circ_duration,
217                 circ_due_date,
218                 circ_duration_rule,
219                 circ_recuring_fine_rule,
220                 circ_max_fine_rule,
221                 circ_stop_fines,
222
223                 usr_id,
224                 usr_grp,
225                 usr_standing,
226                 usr_home_ou,
227                 usr_county,
228                 usr_city,
229                 usr_post_code,
230
231                 copy_holdable,
232                 copy_ref,
233                 copy_circulate,
234                 copy_opac_visible,
235                 copy_circ_lib,
236                 copy_location,
237                 copy_fine_level,
238                 copy_loan_duration,
239                 copy_location,
240                 copy_circ_modifer,
241                 copy_circ_as_type,
242                 copy_call_number_label,
243
244                 bib_item_type,
245                 bib_item_form,
246                 bib_level,
247                 bib_control_type,
248                 bib_char_encoding,
249                 bib_enc_level,
250                 bib_audience,
251                 bib_cat_form,
252                 bib_pub_status,
253                 bib_pub_date,
254                 bib_item_lang,
255
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);
261
262 CREATE OR REPLACE circ_stats.renewal_full_view AS
263         SELECT  circ_id,
264                 circ_timestamp,
265
266                 circ_lib,
267                 circ_staff,
268                 circ_opac_renewal,
269                 circ_self_checkout,
270                 circ_recuring_fine,
271                 circ_max_fine,
272                 circ_fine_interval,
273                 circ_duration,
274                 circ_due_date,
275                 circ_duration_rule,
276                 circ_recuring_fine_rule,
277                 circ_max_fine_rule,
278                 circ_stop_fines,
279
280                 usr_id,
281                 usr_grp,
282                 usr_standing,
283                 usr_home_ou,
284                 usr_county,
285                 usr_city,
286                 usr_post_code,
287
288                 copy_holdable,
289                 copy_ref,
290                 copy_circulate,
291                 copy_opac_visible,
292                 copy_circ_lib,
293                 copy_location,
294                 copy_fine_level,
295                 copy_loan_duration,
296                 copy_location,
297                 copy_circ_modifer,
298                 copy_circ_as_type,
299                 copy_call_number_label,
300
301                 bib_item_type,
302                 bib_item_form,
303                 bib_level,
304                 bib_control_type,
305                 bib_char_encoding,
306                 bib_enc_level,
307                 bib_audience,
308                 bib_cat_form,
309                 bib_pub_status,
310                 bib_pub_date,
311                 bib_item_lang,
312
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);
318
319
320 COMMIT;
321