7439bb27223e77e02a036be3b6147f06cba1f3ff
[working/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_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,
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_lib                INT                             NOT NULL,
89         circ_staff              INT,
90         circ_timestamp          TIMESTAMP WITH TIME ZONE        NOT NULL,
91         circ_dim                TEXT                            NOT NULL REFERECES circ_stats.circ_dim (id),
92
93         -- patron info
94         usr_dim                 TEXT                            NOT NULL REFERECES circ_stats.usr_dim (id),
95
96         -- copy info
97         copy_dim                TEXT                            NOT NULL REFERECES circ_stats.copy_dim (id),
98
99         -- bib record info
100         bib_dim                 TEXT                            NOT NULL REFERECES circ_stats.record_dim (id)
101 ) WITHOUT OIDS;
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);
107
108 CREATE TABLE circ_stats.renewal_fact (
109         -- circulation info
110         circ_id                 BIGINT                          PRIMARY KEY,
111         circ_lib                INT                             NOT NULL,
112         circ_staff              INT,
113         circ_timestamp          TIMESTAMP WITH TIME ZONE        NOT NULL,
114         circ_dim                TEXT                            NOT NULL REFERECES circ_stats.circ_dim (id),
115
116         -- patron info
117         usr_dim                 TEXT                            NOT NULL REFERECES circ_stats.usr_dim (id),
118
119         -- copy info
120         copy_dim                TEXT                            NOT NULL REFERECES circ_stats.copy_dim (id),
121
122         -- bib record info
123         bib_dim                 TEXT                            NOT NULL REFERECES circ_stats.record_dim (id)
124 ) WITHOUT OIDS;
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);
130
131 CREATE TABLE circ_stats.checkin_fact (
132         -- circulation info
133         circ_id                 BIGINT                          PRIMARY KEY,
134         circ_lib                INT                             NOT NULL,
135         circ_staff              INT,
136         circ_timestamp          TIMESTAMP WITH TIME ZONE        NOT NULL,
137         circ_dim                TEXT                            NOT NULL REFERECES circ_stats.circ_dim (id),
138
139         -- patron info
140         usr_dim                 TEXT                            NOT NULL REFERECES circ_stats.usr_dim (id),
141
142         -- copy info
143         copy_dim                TEXT                            NOT NULL REFERECES circ_stats.copy_dim (id),
144
145         -- bib record info
146         bib_dim                 TEXT                            NOT NULL REFERECES circ_stats.record_dim (id)
147 ) WITHOUT OIDS;
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);
153
154 CREATE OR REPLACE circ_stats.checkout_full_view AS
155         SELECT  circ_id,
156                 circ_timestamp,
157
158                 circ_lib,
159                 circ_staff,
160                 circ_opac_renewal,
161                 circ_self_checkout,
162                 circ_recuring_fine,
163                 circ_max_fine,
164                 circ_fine_interval,
165                 circ_duration,
166                 circ_due_date,
167                 circ_duration_rule,
168                 circ_recuring_fine_rule,
169                 circ_max_fine_rule,
170                 circ_stop_fines,
171
172                 usr_id,
173                 usr_grp,
174                 usr_standing,
175                 usr_home_ou,
176                 usr_county,
177                 usr_city,
178                 usr_post_code,
179
180                 copy_holdable,
181                 copy_ref,
182                 copy_circulate,
183                 copy_opac_visible,
184                 copy_circ_lib,
185                 copy_location,
186                 copy_fine_level,
187                 copy_loan_duration,
188                 copy_location,
189                 copy_circ_modifer,
190                 copy_circ_as_type,
191                 copy_call_number_label,
192
193                 bib_item_type,
194                 bib_item_form,
195                 bib_level,
196                 bib_control_type,
197                 bib_char_encoding,
198                 bib_enc_level,
199                 bib_audience,
200                 bib_cat_form,
201                 bib_pub_status,
202                 bib_pub_date,
203                 bib_item_lang,
204
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);
210
211 CREATE OR REPLACE circ_stats.checkin_full_view AS
212         SELECT  circ_id,
213                 circ_timestamp,
214
215                 circ_lib,
216                 circ_staff,
217                 circ_opac_renewal,
218                 circ_self_checkout,
219                 circ_recuring_fine,
220                 circ_max_fine,
221                 circ_fine_interval,
222                 circ_duration,
223                 circ_due_date,
224                 circ_duration_rule,
225                 circ_recuring_fine_rule,
226                 circ_max_fine_rule,
227                 circ_stop_fines,
228
229                 usr_id,
230                 usr_grp,
231                 usr_standing,
232                 usr_home_ou,
233                 usr_county,
234                 usr_city,
235                 usr_post_code,
236
237                 copy_holdable,
238                 copy_ref,
239                 copy_circulate,
240                 copy_opac_visible,
241                 copy_circ_lib,
242                 copy_location,
243                 copy_fine_level,
244                 copy_loan_duration,
245                 copy_location,
246                 copy_circ_modifer,
247                 copy_circ_as_type,
248                 copy_call_number_label,
249
250                 bib_item_type,
251                 bib_item_form,
252                 bib_level,
253                 bib_control_type,
254                 bib_char_encoding,
255                 bib_enc_level,
256                 bib_audience,
257                 bib_cat_form,
258                 bib_pub_status,
259                 bib_pub_date,
260                 bib_item_lang,
261
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);
267
268 CREATE OR REPLACE circ_stats.renewal_full_view AS
269         SELECT  circ_id,
270                 circ_timestamp,
271
272                 circ_lib,
273                 circ_staff,
274                 circ_opac_renewal,
275                 circ_self_checkout,
276                 circ_recuring_fine,
277                 circ_max_fine,
278                 circ_fine_interval,
279                 circ_duration,
280                 circ_due_date,
281                 circ_duration_rule,
282                 circ_recuring_fine_rule,
283                 circ_max_fine_rule,
284                 circ_stop_fines,
285
286                 usr_id,
287                 usr_grp,
288                 usr_standing,
289                 usr_home_ou,
290                 usr_county,
291                 usr_city,
292                 usr_post_code,
293
294                 copy_holdable,
295                 copy_ref,
296                 copy_circulate,
297                 copy_opac_visible,
298                 copy_circ_lib,
299                 copy_location,
300                 copy_fine_level,
301                 copy_loan_duration,
302                 copy_location,
303                 copy_circ_modifer,
304                 copy_circ_as_type,
305                 copy_call_number_label,
306
307                 bib_item_type,
308                 bib_item_form,
309                 bib_level,
310                 bib_control_type,
311                 bib_char_encoding,
312                 bib_enc_level,
313                 bib_audience,
314                 bib_cat_form,
315                 bib_pub_status,
316                 bib_pub_date,
317                 bib_item_lang,
318
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);
324
325
326 COMMIT;
327