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         id                      TEXT    PRIMARY KEY,
  52         copy_circ_modifer       TEXT,
  53         copy_circ_as_type       TEXT,
  54         copy_call_number_label  TEXT    NOT NULL
  55 ) WITHOUT OIDS;
  56 CREATE TRIGGER circ_stats_copy_dim_id_trigger
  57         BEFORE INSERT ON circ_stats.copy_dim
  58         FOR EACH ROW
  59         EXECUTE PROCEDURE dim_row_hash ();
  60
  61
  62 CREATE TABLE circ_stats.circ_dim (
  63         circ_opac_renewal       BOOL,
  64         circ_desk_renewal       BOOL,
  65         circ_phone_renewal      BOOL,
  66         circ_self_checkout      BOOL,
  67         circ_recuring_fine      NUMERIC(6,2)                    NOT NULL,
  68         circ_max_fine           NUMERIC(6,2)                    NOT NULL,
  69         circ_fine_interval      INTERVAL                        NOT NULL,
  70         circ_duration           INTERVAL                        NOT NULL,
  71         circ_due_date           TIMESTAMP WITH TIME ZONE        NOT NULL,
  72         id                      TEXT                            PRIMARY KEY,
  73         circ_duration_rule      TEXT                            NOT NULL,
  74         circ_recuring_fine_rule TEXT                            NOT NULL,
  75         circ_max_fine_rule      TEXT                            NOT NULL,
  76         circ_stop_fines         TEXT
  77 ) WITHOUT OIDS;
  78 CREATE TRIGGER circ_stats_circ_dim_id_trigger
  79         BEFORE INSERT ON circ_stats.circ_dim
  80         FOR EACH ROW
  81         EXECUTE PROCEDURE dim_row_hash ();
  82
  83
  84 CREATE TABLE circ_stats.checkout_fact (
  85         -- circulation info
  86         circ_id                 BIGINT                          PRIMARY KEY,
  87         circ_lib                INT                             NOT NULL,
  88         circ_staff              INT,
  89         circ_timestamp          TIMESTAMP WITH TIME ZONE        NOT NULL,
  90         circ_dim                TEXT                            NOT NULL REFERENCES circ_stats.circ_dim (id),
  91
  92         -- patron info
  93         usr_dim                 TEXT                            NOT NULL REFERENCES circ_stats.usr_dim (id),
  94
  95         -- copy info
  96         copy_dim                TEXT                            NOT NULL REFERENCES circ_stats.copy_dim (id),
  97
  98         -- bib record info
  99         bib_dim                 TEXT                            NOT NULL REFERENCES circ_stats.record_dim (id)
 100 ) WITHOUT OIDS;
 101 CREATE INDEX circ_stats_checkout_fact_time_idx          ON circ_stats.checkout_fact (circ_timestamp);
 102 CREATE INDEX circ_stats_checkout_fact_circ_dim_idx      ON circ_stats.checkout_fact (circ_dim);
 103 CREATE INDEX circ_stats_checkout_fact_usr_dim_idx       ON circ_stats.checkout_fact (usr_dim);
 104 CREATE INDEX circ_stats_checkout_fact_copy_dim_idx      ON circ_stats.checkout_fact (copy_dim);
 105 CREATE INDEX circ_stats_checkout_fact_bib_dim_idx       ON circ_stats.checkout_fact (bib_dim);
 106
 107 CREATE TABLE circ_stats.renewal_fact (
 108         -- circulation info
 109         circ_id                 BIGINT                          PRIMARY KEY,
 110         circ_lib                INT                             NOT NULL,
 111         circ_staff              INT,
 112         circ_timestamp          TIMESTAMP WITH TIME ZONE        NOT NULL,
 113         circ_dim                TEXT                            NOT NULL REFERENCES circ_stats.circ_dim (id),
 114
 115         -- patron info
 116         usr_dim                 TEXT                            NOT NULL REFERENCES circ_stats.usr_dim (id),
 117
 118         -- copy info
 119         copy_dim                TEXT                            NOT NULL REFERENCES circ_stats.copy_dim (id),
 120
 121         -- bib record info
 122         bib_dim                 TEXT                            NOT NULL REFERENCES circ_stats.record_dim (id)
 123 ) WITHOUT OIDS;
 124 CREATE INDEX circ_stats_renewal_fact_time_idx           ON circ_stats.renewal_fact (circ_timestamp);
 125 CREATE INDEX circ_stats_renewal_fact_circ_dim_idx       ON circ_stats.renewal_fact (circ_dim);
 126 CREATE INDEX circ_stats_renewal_fact_usr_dim_idx        ON circ_stats.renewal_fact (usr_dim);
 127 CREATE INDEX circ_stats_renewal_fact_copy_dim_idx       ON circ_stats.renewal_fact (copy_dim);
 128 CREATE INDEX circ_stats_renewal_fact_bib_dim_idx        ON circ_stats.renewal_fact (bib_dim);
 129
 130 CREATE TABLE circ_stats.checkin_fact (
 131         -- circulation info
 132         circ_id                 BIGINT                          PRIMARY KEY,
 133         circ_lib                INT                             NOT NULL,
 134         circ_staff              INT,
 135         circ_timestamp          TIMESTAMP WITH TIME ZONE        NOT NULL,
 136         circ_dim                TEXT                            NOT NULL REFERENCES circ_stats.circ_dim (id),
 137
 138         -- patron info
 139         usr_dim                 TEXT                            NOT NULL REFERENCES circ_stats.usr_dim (id),
 140
 141         -- copy info
 142         copy_dim                TEXT                            NOT NULL REFERENCES circ_stats.copy_dim (id),
 143
 144         -- bib record info
 145         bib_dim                 TEXT                            NOT NULL REFERENCES circ_stats.record_dim (id)
 146 ) WITHOUT OIDS;
 147 CREATE INDEX circ_stats_checkin_fact_time_idx           ON circ_stats.checkin_fact (circ_timestamp);
 148 CREATE INDEX circ_stats_checkin_fact_circ_dim_idx       ON circ_stats.checkin_fact (circ_dim);
 149 CREATE INDEX circ_stats_checkin_fact_usr_dim_idx        ON circ_stats.checkin_fact (usr_dim);
 150 CREATE INDEX circ_stats_checkin_fact_copy_dim_idx       ON circ_stats.checkin_fact (copy_dim);
 151 CREATE INDEX circ_stats_checkin_fact_bib_dim_idx        ON circ_stats.checkin_fact (bib_dim);
 152
 153 CREATE OR REPLACE VIEW circ_stats.checkout_full_view AS
 154         SELECT  circ_id,
 155                 circ_timestamp,
 156
 157                 circ_lib,
 158                 circ_staff,
 159                 circ_opac_renewal,
 160                 circ_self_checkout,
 161                 circ_recuring_fine,
 162                 circ_max_fine,
 163                 circ_fine_interval,
 164                 circ_duration,
 165                 circ_due_date,
 166                 circ_duration_rule,
 167                 circ_recuring_fine_rule,
 168                 circ_max_fine_rule,
 169                 circ_stop_fines,
 170
 171                 usr_id,
 172                 usr_grp,
 173                 usr_standing,
 174                 usr_home_ou,
 175                 usr_county,
 176                 usr_city,
 177                 usr_post_code,
 178
 179                 copy_holdable,
 180                 copy_ref,
 181                 copy_circulate,
 182                 copy_opac_visible,
 183                 copy_circ_lib,
 184                 copy_fine_level,
 185                 copy_loan_duration,
 186                 copy_location,
 187                 copy_circ_modifer,
 188                 copy_circ_as_type,
 189                 copy_call_number_label,
 190
 191                 bib_item_type,
 192                 bib_item_form,
 193                 bib_level,
 194                 bib_control_type,
 195                 bib_char_encoding,
 196                 bib_enc_level,
 197                 bib_audience,
 198                 bib_cat_form,
 199                 bib_pub_status,
 200                 bib_pub_date,
 201                 bib_item_lang
 202
 203           FROM  circ_stats.checkout_fact f
 204                 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
 205                 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
 206                 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
 207                 JOIN circ_stats.record_dim bd ON (f.bib_dim = bd.id);
 208
 209 CREATE OR REPLACE VIEW circ_stats.checkin_full_view AS
 210         SELECT  circ_id,
 211                 circ_timestamp,
 212
 213                 circ_lib,
 214                 circ_staff,
 215                 circ_opac_renewal,
 216                 circ_self_checkout,
 217                 circ_recuring_fine,
 218                 circ_max_fine,
 219                 circ_fine_interval,
 220                 circ_duration,
 221                 circ_due_date,
 222                 circ_duration_rule,
 223                 circ_recuring_fine_rule,
 224                 circ_max_fine_rule,
 225                 circ_stop_fines,
 226
 227                 usr_id,
 228                 usr_grp,
 229                 usr_standing,
 230                 usr_home_ou,
 231                 usr_county,
 232                 usr_city,
 233                 usr_post_code,
 234
 235                 copy_holdable,
 236                 copy_ref,
 237                 copy_circulate,
 238                 copy_opac_visible,
 239                 copy_circ_lib,
 240                 copy_fine_level,
 241                 copy_loan_duration,
 242                 copy_location,
 243                 copy_circ_modifer,
 244                 copy_circ_as_type,
 245                 copy_call_number_label,
 246
 247                 bib_item_type,
 248                 bib_item_form,
 249                 bib_level,
 250                 bib_control_type,
 251                 bib_char_encoding,
 252                 bib_enc_level,
 253                 bib_audience,
 254                 bib_cat_form,
 255                 bib_pub_status,
 256                 bib_pub_date,
 257                 bib_item_lang
 258
 259           FROM  circ_stats.checkin_fact f
 260                 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
 261                 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
 262                 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
 263                 JOIN circ_stats.record_dim bd ON (f.bib_dim = bd.id);
 264
 265 CREATE OR REPLACE VIEW circ_stats.renewal_full_view AS
 266         SELECT  circ_id,
 267                 circ_timestamp,
 268
 269                 circ_lib,
 270                 circ_staff,
 271                 circ_opac_renewal,
 272                 circ_self_checkout,
 273                 circ_recuring_fine,
 274                 circ_max_fine,
 275                 circ_fine_interval,
 276                 circ_duration,
 277                 circ_due_date,
 278                 circ_duration_rule,
 279                 circ_recuring_fine_rule,
 280                 circ_max_fine_rule,
 281                 circ_stop_fines,
 282
 283                 usr_id,
 284                 usr_grp,
 285                 usr_standing,
 286                 usr_home_ou,
 287                 usr_county,
 288                 usr_city,
 289                 usr_post_code,
 290
 291                 copy_holdable,
 292                 copy_ref,
 293                 copy_circulate,
 294                 copy_opac_visible,
 295                 copy_circ_lib,
 296                 copy_fine_level,
 297                 copy_loan_duration,
 298                 copy_location,
 299                 copy_circ_modifer,
 300                 copy_circ_as_type,
 301                 copy_call_number_label,
 302
 303                 bib_item_type,
 304                 bib_item_form,
 305                 bib_level,
 306                 bib_control_type,
 307                 bib_char_encoding,
 308                 bib_enc_level,
 309                 bib_audience,
 310                 bib_cat_form,
 311                 bib_pub_status,
 312                 bib_pub_date,
 313                 bib_item_lang
 314
 315           FROM  circ_stats.renewal_fact f
 316                 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
 317                 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
 318                 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
 319                 JOIN circ_stats.record_dim bd ON (f.bib_dim = bd.id);
 320
 321
 322 COMMIT;
 323