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.circulation (
   8     id                  BIGSERIAL                       PRIMARY KEY,
   9     target_copy         BIGINT                          NOT NULL,
  10     usr                 INT                             NOT NULL,
  11     circ_lib            INT                             NOT NULL,
  12     renewal_remaining   INT                             NOT NULL,
  13     circ_staff          INT,
  14     checkin_staff       INT,
  15     checkin_lib         INT,
  16     xact_start          TIMESTAMP WITH TIME ZONE        NOT NULL,
  17     xact_finish         TIMESTAMP WITH TIME ZONE,
  18     due_date            TIMESTAMP WITH TIME ZONE        NOT NULL,
  19     stop_fines_time     TIMESTAMP WITH TIME ZONE,
  20     checkin_time        TIMESTAMP WITH TIME ZONE,
  21     duration            INTERVAL                        NOT NULL,
  22     fine_interval       INTERVAL                        NOT NULL,
  23     recurring_fine      NUMERIC(6,2)                    NOT NULL,
  24     max_fine            NUMERIC(6,2)                    NOT NULL,
  25     opac_renewal        BOOL                            NOT NULL,
  26     phone_renewal       BOOL                            NOT NULL,
  27     desk_renewal        BOOL                            NOT NULL,
  28     duration_rule       TEXT                            NOT NULL,
  29     recurring_fine_rule TEXT                            NOT NULL,
  30     max_fine_rule       TEXT                            NOT NULL,
  31     stop_fines          TEXT
  32 ) WITHOUT OIDS;
  33
  34 CREATE TABLE circ_stats.circulation_checkin () INHERITS (circ_stats.circulation);
  35 CREATE TABLE circ_stats.circulation_claimsreturned () INHERITS (circ_stats.circulation);
  36 CREATE TABLE circ_stats.circulation_lost () INHERITS (circ_stats.circulation);
  37 CREATE TABLE circ_stats.circulation_maxfines () INHERITS (circ_stats.circulation);
  38 CREATE TABLE circ_stats.circulation_renew () INHERITS (circ_stats.circulation);
  39
  40
  41 CREATE TABLE circ_stats.record_dim (
  42         id                      BIGSERIAL       PRIMARY KEY,
  43         bib_item_type           TEXT    NOT NULL DEFAULT '?',
  44         bib_item_form           TEXT    NOT NULL DEFAULT '?',
  45         bib_level               TEXT    NOT NULL DEFAULT '?',
  46         bib_control_type        TEXT    NOT NULL DEFAULT '?',
  47         bib_char_encoding       TEXT    NOT NULL DEFAULT '?',
  48         bib_enc_level           TEXT    NOT NULL DEFAULT '?',
  49         bib_audience            TEXT    NOT NULL DEFAULT '?',
  50         bib_cat_form            TEXT    NOT NULL DEFAULT '?',
  51         bib_pub_status          TEXT    NOT NULL DEFAULT '?',
  52         bib_pub_date            TEXT    NOT NULL DEFAULT '?',
  53         bib_item_lang           TEXT    NOT NULL DEFAULT '?',
  54         bib_title               TEXT    NOT NULL DEFAULT '?',
  55         bib_author              TEXT    NOT NULL DEFAULT '?'
  56 ) WITHOUT OIDS;
  57 CREATE TRIGGER circ_stats_record_dim_id_trigger
  58         BEFORE INSERT ON circ_stats.record_dim
  59         FOR EACH ROW
  60         EXECUTE PROCEDURE dim_row_hash ();
  61
  62 CREATE TABLE circ_stats.usr_dim (
  63         id                      BIGSERIAL       PRIMARY KEY,
  64         usr_id                  INT     NOT NULL DEFAULT 0,
  65         usr_grp                 INT     NOT NULL, -- aka profile
  66         usr_standing            INT     NOT NULL,
  67         usr_home_ou             INT     NOT NULL,
  68         usr_county              TEXT,
  69         usr_city                TEXT,
  70         usr_post_code           TEXT    NOT NULL
  71 ) WITHOUT OIDS;
  72 CREATE TRIGGER circ_stats_usr_dim_id_trigger
  73         BEFORE INSERT ON circ_stats.usr_dim
  74         FOR EACH ROW
  75         EXECUTE PROCEDURE dim_row_hash ();
  76
  77
  78 CREATE TABLE circ_stats.copy_dim (
  79         id                      BIGSERIAL       PRIMARY KEY,
  80         copy_holdable           BOOL    NOT NULL,
  81         copy_ref                BOOL    NOT NULL,
  82         copy_circulate          BOOL    NOT NULL,
  83         copy_opac_visible       BOOL    NOT NULL,
  84         copy_circ_lib           INT     NOT NULL,
  85         copy_location           INT     NOT NULL,
  86         copy_fine_level         INT     NOT NULL,
  87         copy_loan_duration      INT     NOT NULL,
  88         copy_circ_modifer       TEXT,
  89         copy_circ_as_type       TEXT,
  90         copy_call_number_label  TEXT    NOT NULL
  91 ) WITHOUT OIDS;
  92 CREATE TRIGGER circ_stats_copy_dim_id_trigger
  93         BEFORE INSERT ON circ_stats.copy_dim
  94         FOR EACH ROW
  95         EXECUTE PROCEDURE dim_row_hash ();
  96
  97
  98 CREATE TABLE circ_stats.circ_dim (
  99         id                      BIGSERIAL                               PRIMARY KEY,
 100         circ_opac_renewal       BOOL,
 101         circ_desk_renewal       BOOL,
 102         circ_phone_renewal      BOOL,
 103         circ_self_checkout      BOOL,
 104         circ_recurring_fine     NUMERIC(6,2)                    NOT NULL,
 105         circ_max_fine           NUMERIC(6,2)                    NOT NULL,
 106         circ_fine_interval      INTERVAL                        NOT NULL,
 107         circ_duration           INTERVAL                        NOT NULL,
 108         circ_due_date           TIMESTAMP WITH TIME ZONE        NOT NULL,
 109         circ_duration_rule      TEXT                            NOT NULL,
 110         circ_recurring_fine_rule        TEXT                            NOT NULL,
 111         circ_max_fine_rule      TEXT                            NOT NULL,
 112         circ_stop_fines         TEXT
 113 ) WITHOUT OIDS;
 114 CREATE TRIGGER circ_stats_circ_dim_id_trigger
 115         BEFORE INSERT ON circ_stats.circ_dim
 116         FOR EACH ROW
 117         EXECUTE PROCEDURE dim_row_hash ();
 118
 119
 120 CREATE TABLE circ_stats.checkout_fact (
 121         -- circulation info
 122         circ_id                 BIGINT                          PRIMARY KEY,
 123         circ_lib                INT                             NOT NULL,
 124         circ_staff              INT,
 125         circ_timestamp          TIMESTAMP WITH TIME ZONE        NOT NULL,
 126         circ_dim                INT                             NOT NULL REFERENCES circ_stats.circ_dim (id),
 127
 128         -- patron info
 129         usr_dim                 INT                             NOT NULL REFERENCES circ_stats.usr_dim (id),
 130
 131         -- copy info
 132         copy_dim                INT                             NOT NULL REFERENCES circ_stats.copy_dim (id),
 133
 134         -- bib record info
 135         bib_dim                 INT                             NOT NULL REFERENCES circ_stats.record_dim (id)
 136 ) WITHOUT OIDS;
 137 CREATE INDEX circ_stats_checkout_fact_time_idx          ON circ_stats.checkout_fact (circ_timestamp);
 138 CREATE INDEX circ_stats_checkout_fact_circ_dim_idx      ON circ_stats.checkout_fact (circ_dim);
 139 CREATE INDEX circ_stats_checkout_fact_usr_dim_idx       ON circ_stats.checkout_fact (usr_dim);
 140 CREATE INDEX circ_stats_checkout_fact_copy_dim_idx      ON circ_stats.checkout_fact (copy_dim);
 141 CREATE INDEX circ_stats_checkout_fact_bib_dim_idx       ON circ_stats.checkout_fact (bib_dim);
 142
 143 CREATE TABLE circ_stats.renewal_fact (
 144         -- circulation info
 145         circ_id                 BIGINT                          PRIMARY KEY,
 146         circ_lib                INT                             NOT NULL,
 147         circ_staff              INT,
 148         circ_timestamp          TIMESTAMP WITH TIME ZONE        NOT NULL,
 149         circ_dim                INT                             NOT NULL REFERENCES circ_stats.circ_dim (id),
 150
 151         -- patron info
 152         usr_dim                 INT                             NOT NULL REFERENCES circ_stats.usr_dim (id),
 153
 154         -- copy info
 155         copy_dim                INT                             NOT NULL REFERENCES circ_stats.copy_dim (id),
 156
 157         -- bib record info
 158         bib_dim                 INT                             NOT NULL REFERENCES circ_stats.record_dim (id)
 159 ) WITHOUT OIDS;
 160 CREATE INDEX circ_stats_renewal_fact_time_idx           ON circ_stats.renewal_fact (circ_timestamp);
 161 CREATE INDEX circ_stats_renewal_fact_circ_dim_idx       ON circ_stats.renewal_fact (circ_dim);
 162 CREATE INDEX circ_stats_renewal_fact_usr_dim_idx        ON circ_stats.renewal_fact (usr_dim);
 163 CREATE INDEX circ_stats_renewal_fact_copy_dim_idx       ON circ_stats.renewal_fact (copy_dim);
 164 CREATE INDEX circ_stats_renewal_fact_bib_dim_idx        ON circ_stats.renewal_fact (bib_dim);
 165
 166 CREATE TABLE circ_stats.checkin_fact (
 167         -- circulation info
 168         circ_id                 BIGINT                          PRIMARY KEY,
 169         circ_lib                INT                             NOT NULL,
 170         circ_staff              INT,
 171         circ_timestamp          TIMESTAMP WITH TIME ZONE        NOT NULL,
 172         circ_dim                INT                             NOT NULL REFERENCES circ_stats.circ_dim (id),
 173
 174         -- patron info
 175         usr_dim                 INT                             NOT NULL REFERENCES circ_stats.usr_dim (id),
 176
 177         -- copy info
 178         copy_dim                INT                             NOT NULL REFERENCES circ_stats.copy_dim (id),
 179
 180         -- bib record info
 181         bib_dim                 INT                             NOT NULL REFERENCES circ_stats.record_dim (id)
 182 ) WITHOUT OIDS;
 183 CREATE INDEX circ_stats_checkin_fact_time_idx           ON circ_stats.checkin_fact (circ_timestamp);
 184 CREATE INDEX circ_stats_checkin_fact_circ_dim_idx       ON circ_stats.checkin_fact (circ_dim);
 185 CREATE INDEX circ_stats_checkin_fact_usr_dim_idx        ON circ_stats.checkin_fact (usr_dim);
 186 CREATE INDEX circ_stats_checkin_fact_copy_dim_idx       ON circ_stats.checkin_fact (copy_dim);
 187 CREATE INDEX circ_stats_checkin_fact_bib_dim_idx        ON circ_stats.checkin_fact (bib_dim);
 188
 189 CREATE OR REPLACE VIEW circ_stats.checkout_full_view AS
 190         SELECT  circ_id,
 191                 circ_timestamp,
 192
 193                 circ_lib,
 194                 circ_staff,
 195                 circ_opac_renewal,
 196                 circ_self_checkout,
 197                 circ_recurring_fine,
 198                 circ_max_fine,
 199                 circ_fine_interval,
 200                 circ_duration,
 201                 circ_due_date,
 202                 circ_duration_rule,
 203                 circ_recurring_fine_rule,
 204                 circ_max_fine_rule,
 205                 circ_stop_fines,
 206
 207                 usr_id,
 208                 usr_grp,
 209                 usr_standing,
 210                 usr_home_ou,
 211                 usr_county,
 212                 usr_city,
 213                 usr_post_code,
 214
 215                 copy_holdable,
 216                 copy_ref,
 217                 copy_circulate,
 218                 copy_opac_visible,
 219                 copy_circ_lib,
 220                 copy_fine_level,
 221                 copy_loan_duration,
 222                 copy_location,
 223                 copy_circ_modifer,
 224                 copy_circ_as_type,
 225                 copy_call_number_label,
 226
 227                 bib_item_type,
 228                 bib_item_form,
 229                 bib_level,
 230                 bib_control_type,
 231                 bib_char_encoding,
 232                 bib_enc_level,
 233                 bib_audience,
 234                 bib_cat_form,
 235                 bib_pub_status,
 236                 bib_pub_date,
 237                 bib_item_lang
 238
 239           FROM  circ_stats.checkout_fact f
 240                 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
 241                 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
 242                 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
 243                 JOIN circ_stats.record_dim bd ON (f.bib_dim = bd.id);
 244
 245 CREATE OR REPLACE VIEW circ_stats.checkin_full_view AS
 246         SELECT  circ_id,
 247                 circ_timestamp,
 248
 249                 circ_lib,
 250                 circ_staff,
 251                 circ_opac_renewal,
 252                 circ_self_checkout,
 253                 circ_recurring_fine,
 254                 circ_max_fine,
 255                 circ_fine_interval,
 256                 circ_duration,
 257                 circ_due_date,
 258                 circ_duration_rule,
 259                 circ_recurring_fine_rule,
 260                 circ_max_fine_rule,
 261                 circ_stop_fines,
 262
 263                 usr_id,
 264                 usr_grp,
 265                 usr_standing,
 266                 usr_home_ou,
 267                 usr_county,
 268                 usr_city,
 269                 usr_post_code,
 270
 271                 copy_holdable,
 272                 copy_ref,
 273                 copy_circulate,
 274                 copy_opac_visible,
 275                 copy_circ_lib,
 276                 copy_fine_level,
 277                 copy_loan_duration,
 278                 copy_location,
 279                 copy_circ_modifer,
 280                 copy_circ_as_type,
 281                 copy_call_number_label,
 282
 283                 bib_item_type,
 284                 bib_item_form,
 285                 bib_level,
 286                 bib_control_type,
 287                 bib_char_encoding,
 288                 bib_enc_level,
 289                 bib_audience,
 290                 bib_cat_form,
 291                 bib_pub_status,
 292                 bib_pub_date,
 293                 bib_item_lang
 294
 295           FROM  circ_stats.checkin_fact f
 296                 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
 297                 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
 298                 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
 299                 JOIN circ_stats.record_dim bd ON (f.bib_dim = bd.id);
 300
 301 CREATE OR REPLACE VIEW circ_stats.renewal_full_view AS
 302         SELECT  circ_id,
 303                 circ_timestamp,
 304
 305                 circ_lib,
 306                 circ_staff,
 307                 circ_opac_renewal,
 308                 circ_self_checkout,
 309                 circ_recurring_fine,
 310                 circ_max_fine,
 311                 circ_fine_interval,
 312                 circ_duration,
 313                 circ_due_date,
 314                 circ_duration_rule,
 315                 circ_recurring_fine_rule,
 316                 circ_max_fine_rule,
 317                 circ_stop_fines,
 318
 319                 usr_id,
 320                 usr_grp,
 321                 usr_standing,
 322                 usr_home_ou,
 323                 usr_county,
 324                 usr_city,
 325                 usr_post_code,
 326
 327                 copy_holdable,
 328                 copy_ref,
 329                 copy_circulate,
 330                 copy_opac_visible,
 331                 copy_circ_lib,
 332                 copy_fine_level,
 333                 copy_loan_duration,
 334                 copy_location,
 335                 copy_circ_modifer,
 336                 copy_circ_as_type,
 337                 copy_call_number_label,
 338
 339                 bib_item_type,
 340                 bib_item_form,
 341                 bib_level,
 342                 bib_control_type,
 343                 bib_char_encoding,
 344                 bib_enc_level,
 345                 bib_audience,
 346                 bib_cat_form,
 347                 bib_pub_status,
 348                 bib_pub_date,
 349                 bib_item_lang
 350
 351           FROM  circ_stats.renewal_fact f
 352                 JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
 353                 JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
 354                 JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
 355                 JOIN circ_stats.record_dim bd ON (f.bib_dim = bd.id);
 356
 357
 358 COMMIT;
 359
 360