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