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