refactored layout, added hashing triggers, early views
authormiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Thu, 25 Aug 2005 18:23:35 +0000 (18:23 +0000)
committermiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Thu, 25 Aug 2005 18:23:35 +0000 (18:23 +0000)
git-svn-id: svn://svn.open-ils.org/ILS/trunk@1734 dcc99617-32d9-48b4-a31d-7c20da2025e4

Open-ILS/src/sql/Pg/stats/circ_stats.sql

index 45ff054..9474548 100644 (file)
@@ -4,171 +4,318 @@ BEGIN;
 
 CREATE SCHEMA circ_stats;
 
-CREATE TABLE circ_stats.checkout (
-       -- circulation info
-       circ_id                 BIGINT                          PRIMARY KEY,
-       circ_checkout_time      TIMESTAMP WITH TIME ZONE        NOT NULL,
+CREATE TABLE circ_stats.record_dim (
+       bib_item_type           "char"  NOT NULL DEFAULT '?',
+       bib_item_form           "char"  NOT NULL DEFAULT '?',
+       bib_level               "char"  NOT NULL DEFAULT '?',
+       bib_control_type        "char"  NOT NULL DEFAULT '?',
+       bib_char_encoding       "char"  NOT NULL DEFAULT '?',
+       bib_enc_level           "char"  NOT NULL DEFAULT '?',
+       bib_audience            "char"  NOT NULL DEFAULT '?',
+       id                      TEXT    PRIMARY KEY,
+       bib_cat_form            TEXT    NOT NULL DEFAULT '?',
+       bib_pub_status          TEXT    NOT NULL DEFAULT '?',
+       bib_pub_date            TEXT    NOT NULL DEFAULT '?',
+       bib_item_lang           TEXT    NOT NULL DEFAULT '?'
+) WITHOUT OIDS;
+CREATE TRIGGER circ_stats_record_dim_id_trigger
+       BEFORE INSERT ON circ_stats.record_dim
+       FOR EACH ROW
+       EXECUTE PROCEDURE dim_row_hash ();
+
+CREATE TABLE circ_stats.usr_dim (
+       usr_id                  INT     NOT NULL DEFAULT 0,
+       usr_grp                 INT     NOT NULL, -- aka profile
+       usr_standing            INT     NOT NULL,
+       usr_home_ou             INT     NOT NULL,
+       id                      TEXT    PRIMARY KEY,
+       usr_county              TEXT,
+       usr_city                TEXT,
+       usr_post_code           TEXT    NOT NULL
+) WITHOUT OIDS;
+CREATE TRIGGER circ_stats_usr_dim_id_trigger
+       BEFORE INSERT ON circ_stats.usr_dim
+       FOR EACH ROW
+       EXECUTE PROCEDURE dim_row_hash ();
+
+
+CREATE TABLE circ_stats.copy_dim (
+       copy_holdable           BOOL    NOT NULL,
+       copy_ref                BOOL    NOT NULL,
+       copy_circulate          BOOL    NOT NULL,
+       copy_opac_visible       BOOL    NOT NULL,
+       copy_circ_lib           INT     NOT NULL,
+       copy_location           INT     NOT NULL,
+       copy_fine_level         INT     NOT NULL,
+       copy_loan_duration      INT     NOT NULL,
+       copy_location           INT     NOT NULL,
+       id                      TEXT    PRIMARY KEY,
+       copy_circ_modifer       TEXT,
+       copy_circ_as_type       TEXT,
+       copy_call_number_label  TEXT    NOT NULL
+) WITHOUT OIDS;
+CREATE TRIGGER circ_stats_copy_dim_id_trigger
+       BEFORE INSERT ON circ_stats.copy_dim
+       FOR EACH ROW
+       EXECUTE PROCEDURE dim_row_hash ();
+
+
+CREATE TABLE circ_stats.circ_dim (
        circ_lib                INT                             NOT NULL,
-       circ_self               BOOL                            NOT NULL,
-       circ_staff              INT                             NOT NULL,
+       circ_staff              INT,
+       circ_opac_renewal       BOOL,
+       circ_self_checkout      BOOL,
+       circ_recuring_fine      NUMERIC(6,2)                    NOT NULL,
+       circ_max_fine           NUMERIC(6,2)                    NOT NULL,
+       circ_fine_interval      INTERVAL                        NOT NULL,
        circ_duration           INTERVAL                        NOT NULL,
+       circ_due_date           TIMESTAMP WITH TIME ZONE        NOT NULL,
+       id                      TEXT                            PRIMARY KEY,
        circ_duration_rule      TEXT                            NOT NULL,
-       circ_recuring_fine      INTERVAL                        NOT NULL,
        circ_recuring_fine_rule TEXT                            NOT NULL,
-       circ_max_fine           INTERVAL                        NOT NULL,
        circ_max_fine_rule      TEXT                            NOT NULL,
-       circ_fine_interval      INTERVAL                        NOT NULL,
+       circ_stop_fines         TEXT
+) WITHOUT OIDS;
+CREATE TRIGGER circ_stats_circ_dim_id_trigger
+       BEFORE INSERT ON circ_stats.circ_dim
+       FOR EACH ROW
+       EXECUTE PROCEDURE dim_row_hash ();
+
+
+CREATE TABLE circ_stats.checkout_fact (
+       -- circulation info
+       circ_id                 BIGINT                          PRIMARY KEY,
+       circ_timestamp          TIMESTAMP WITH TIME ZONE        NOT NULL,
+       circ_dim                TEXT                            NOT NULL REFERECES circ_stats.circ_dim (id),
 
        -- patron info
-       -- usr_id               INT                             NOT NULL,
-       usr_grp                 INT                             NOT NULL, -- aka profile
-       usr_county              TEXT                            NOT NULL,
-       usr_city                TEXT                            NOT NULL,
-       usr_post_code           TEXT                            NOT NULL,
-       usr_standing            INT                             NOT NULL,
-       usr_home_ou             INT                             NOT NULL,
+       usr_dim                 TEXT                            NOT NULL REFERECES circ_stats.usr_dim (id),
 
        -- copy info
-       cp_circ_lib             INT                             NOT NULL,
-       cp_barcode              TEXT                            NOT NULL,
-       cp_holdable             BOOL                            NOT NULL,
-       cp_ref                  BOOL                            NOT NULL,
-       cp_circulate            BOOL                            NOT NULL,
-       cp_opac_visible         BOOL                            NOT NULL,
-       cp_circ_modifer         TEXT                            NOT NULL,
-       cp_circ_as_type         TEXT                            NOT NULL,
-       cp_location             INT                             NOT NULL,
-       cp_fine_level           INT                             NOT NULL,
-       cp_load_duration        INT                             NOT NULL,
-       cp_location             INT                             NOT NULL,
-
-       -- call number info
-       cn_owning_lib           INT                             NOT NULL,
-       cn_label                TEXT                            NOT NULL,
+       copy_dim                TEXT                            NOT NULL REFERECES circ_stats.copy_dim (id),
 
        -- bib record info
-       bib_id                  BIGINT                          NOT NULL,
-       bib_item_type           "char"                          NOT NULL,
-       bib_item_form           "char"                          NOT NULL,
-       bib_level               "char"                          NOT NULL,
-       bib_control_type        "char"                          NOT NULL,
-       bib_char_encoding       "char"                          NOT NULL,
-       bib_enc_level           "char"                          NOT NULL,
-       bib_audience            "char"                          NOT NULL,
-       bib_cat_form            TEXT                            NOT NULL,
-       bib_pub_status          TEXT                            NOT NULL,
-       bib_item_lang           TEXT                            NOT NULL
+       bib_dim                 TEXT                            NOT NULL REFERECES circ_stats.record_dim (id)
 ) WITHOUT OIDS;
+CREATE INDEX circ_stats_checkout_fact_time_idx         ON circ_stats.checkout_fact (circ_time);
+CREATE INDEX circ_stats_checkout_fact_circ_dim_idx     ON circ_stats.checkout_fact (circ_dim);
+CREATE INDEX circ_stats_checkout_fact_usr_dim_idx      ON circ_stats.checkout_fact (usr_dim);
+CREATE INDEX circ_stats_checkout_fact_copy_dim_idx     ON circ_stats.checkout_fact (copy_dim);
+CREATE INDEX circ_stats_checkout_fact_bib_dim_idx      ON circ_stats.checkout_fact (bib_dim);
 
-CREATE TABLE circ_stats.renewal (
+CREATE TABLE circ_stats.renewal_fact (
        -- circulation info
        circ_id                 BIGINT                          PRIMARY KEY,
-       circ_renewal_time       TIMESTAMP WITH TIME ZONE        NOT NULL,
-       circ_lib                INT                             NOT NULL,
-       circ_opac               BOOL                            NOT NULL,
-       circ_staff              INT                             NOT NULL,
-       circ_duration           INTERVAL                        NOT NULL,
-       circ_duration_rule      TEXT                            NOT NULL,
-       circ_recuring_fine      INTERVAL                        NOT NULL,
-       circ_recuring_fine_rule TEXT                            NOT NULL,
-       circ_max_fine           INTERVAL                        NOT NULL,
-       circ_max_fine_rule      TEXT                            NOT NULL,
-       circ_fine_interval      INTERVAL                        NOT NULL,
+       circ_timestamp          TIMESTAMP WITH TIME ZONE        NOT NULL,
+       circ_dim                TEXT                            NOT NULL REFERECES circ_stats.circ_dim (id),
 
        -- patron info
-       -- usr_id               INT                             NOT NULL,
-       usr_grp                 INT                             NOT NULL, -- aka profile
-       usr_county              TEXT                            NOT NULL,
-       usr_city                TEXT                            NOT NULL,
-       usr_post_code           TEXT                            NOT NULL,
-       usr_standing            INT                             NOT NULL,
-       usr_home_ou             INT                             NOT NULL,
+       usr_dim                 TEXT                            NOT NULL REFERECES circ_stats.usr_dim (id),
 
        -- copy info
-       cp_circ_lib             INT                             NOT NULL,
-       cp_barcode              TEXT                            NOT NULL,
-       cp_holdable             BOOL                            NOT NULL,
-       cp_ref                  BOOL                            NOT NULL,
-       cp_circulate            BOOL                            NOT NULL,
-       cp_opac_visible         BOOL                            NOT NULL,
-       cp_circ_modifer         TEXT                            NOT NULL,
-       cp_circ_as_type         TEXT                            NOT NULL,
-       cp_location             INT                             NOT NULL,
-       cp_fine_level           INT                             NOT NULL,
-       cp_load_duration        INT                             NOT NULL,
-       cp_location             INT                             NOT NULL,
-
-       -- call number info
-       cn_owning_lib           INT                             NOT NULL,
-       cn_label                TEXT                            NOT NULL,
+       copy_dim                TEXT                            NOT NULL REFERECES circ_stats.copy_dim (id),
 
        -- bib record info
-       bib_id                  BIGINT                          NOT NULL,
-       bib_item_type           "char"                          NOT NULL,
-       bib_item_form           "char"                          NOT NULL,
-       bib_level               "char"                          NOT NULL,
-       bib_control_type        "char"                          NOT NULL,
-       bib_char_encoding       "char"                          NOT NULL,
-       bib_enc_level           "char"                          NOT NULL,
-       bib_audience            "char"                          NOT NULL,
-       bib_cat_form            TEXT                            NOT NULL,
-       bib_pub_status          TEXT                            NOT NULL,
-       bib_item_lang           TEXT                            NOT NULL
+       bib_dim                 TEXT                            NOT NULL REFERECES circ_stats.record_dim (id)
 ) WITHOUT OIDS;
+CREATE INDEX circ_stats_renewal_fact_time_idx          ON circ_stats.renewal_fact (circ_time);
+CREATE INDEX circ_stats_renewal_fact_circ_dim_idx      ON circ_stats.renewal_fact (circ_dim);
+CREATE INDEX circ_stats_renewal_fact_usr_dim_idx       ON circ_stats.renewal_fact (usr_dim);
+CREATE INDEX circ_stats_renewal_fact_copy_dim_idx      ON circ_stats.renewal_fact (copy_dim);
+CREATE INDEX circ_stats_renewal_fact_bib_dim_idx       ON circ_stats.renewal_fact (bib_dim);
 
-CREATE TABLE circ_stats.checkin (
+CREATE TABLE circ_stats.checkin_fact (
        -- circulation info
        circ_id                 BIGINT                          PRIMARY KEY,
-       circ_checkin_time       TIMESTAMP WITH TIME ZONE        NOT NULL,
-       circ_checkout_lib       INT                             NOT NULL,
-       circ_checkin_lib        INT                             NOT NULL,
-       circ_staff              INT                             NOT NULL,
-       circ_duration           INTERVAL                        NOT NULL,
-       circ_duration_rule      TEXT                            NOT NULL,
-       circ_recuring_fine      INTERVAL                        NOT NULL,
-       circ_recuring_fine_rule TEXT                            NOT NULL,
-       circ_max_fine           INTERVAL                        NOT NULL,
-       circ_max_fine_rule      TEXT                            NOT NULL,
-       circ_fine_interval      INTERVAL                        NOT NULL,
+       circ_timestamp          TIMESTAMP WITH TIME ZONE        NOT NULL,
+       circ_dim                TEXT                            NOT NULL REFERECES circ_stats.circ_dim (id),
 
        -- patron info
-       -- usr_id               INT                             NOT NULL,
-       usr_grp                 INT                             NOT NULL, -- aka profile
-       usr_county              TEXT                            NOT NULL,
-       usr_city                TEXT                            NOT NULL,
-       usr_post_code           TEXT                            NOT NULL,
-       usr_standing            INT                             NOT NULL,
-       usr_home_ou             INT                             NOT NULL,
+       usr_dim                 TEXT                            NOT NULL REFERECES circ_stats.usr_dim (id),
 
        -- copy info
-       cp_circ_lib             INT                             NOT NULL,
-       cp_barcode              TEXT                            NOT NULL,
-       cp_holdable             BOOL                            NOT NULL,
-       cp_ref                  BOOL                            NOT NULL,
-       cp_circulate            BOOL                            NOT NULL,
-       cp_opac_visible         BOOL                            NOT NULL,
-       cp_circ_modifer         TEXT                            NOT NULL,
-       cp_circ_as_type         TEXT                            NOT NULL,
-       cp_location             INT                             NOT NULL,
-       cp_fine_level           INT                             NOT NULL,
-       cp_load_duration        INT                             NOT NULL,
-       cp_location             INT                             NOT NULL,
-
-       -- call number info
-       cn_owning_lib           INT                             NOT NULL,
-       cn_label                TEXT                            NOT NULL,
+       copy_dim                TEXT                            NOT NULL REFERECES circ_stats.copy_dim (id),
 
        -- bib record info
-       bib_id                  BIGINT                          NOT NULL,
-       bib_item_type           "char"                          NOT NULL,
-       bib_item_form           "char"                          NOT NULL,
-       bib_level               "char"                          NOT NULL,
-       bib_control_type        "char"                          NOT NULL,
-       bib_char_encoding       "char"                          NOT NULL,
-       bib_enc_level           "char"                          NOT NULL,
-       bib_audience            "char"                          NOT NULL,
-       bib_cat_form            TEXT                            NOT NULL,
-       bib_pub_status          TEXT                            NOT NULL,
-       bib_item_lang           TEXT                            NOT NULL
+       bib_dim                 TEXT                            NOT NULL REFERECES circ_stats.record_dim (id)
 ) WITHOUT OIDS;
+CREATE INDEX circ_stats_checkin_fact_time_idx          ON circ_stats.checkin_fact (circ_time);
+CREATE INDEX circ_stats_checkin_fact_circ_dim_idx      ON circ_stats.checkin_fact (circ_dim);
+CREATE INDEX circ_stats_checkin_fact_usr_dim_idx       ON circ_stats.checkin_fact (usr_dim);
+CREATE INDEX circ_stats_checkin_fact_copy_dim_idx      ON circ_stats.checkin_fact (copy_dim);
+CREATE INDEX circ_stats_checkin_fact_bib_dim_idx       ON circ_stats.checkin_fact (bib_dim);
+
+CREATE OR REPLACE circ_stats.checkout_full_view AS
+       SELECT  circ_id,
+               circ_timestamp,
+
+               circ_lib,
+               circ_staff,
+               circ_opac_renewal,
+               circ_self_checkout,
+               circ_recuring_fine,
+               circ_max_fine,
+               circ_fine_interval,
+               circ_duration,
+               circ_due_date,
+               circ_duration_rule,
+               circ_recuring_fine_rule,
+               circ_max_fine_rule,
+               circ_stop_fines,
+
+               usr_id,
+               usr_grp,
+               usr_standing,
+               usr_home_ou,
+               usr_county,
+               usr_city,
+               usr_post_code,
+
+               copy_holdable,
+               copy_ref,
+               copy_circulate,
+               copy_opac_visible,
+               copy_circ_lib,
+               copy_location,
+               copy_fine_level,
+               copy_loan_duration,
+               copy_location,
+               copy_circ_modifer,
+               copy_circ_as_type,
+               copy_call_number_label,
+
+               bib_item_type,
+               bib_item_form,
+               bib_level,
+               bib_control_type,
+               bib_char_encoding,
+               bib_enc_level,
+               bib_audience,
+               bib_cat_form,
+               bib_pub_status,
+               bib_pub_date,
+               bib_item_lang,
+
+         FROM  circ_stats.checkout_fact f
+               JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
+               JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
+               JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
+               JOIN circ_stats.bib_dim bd ON (f.bib_dim = bd.id);
+
+CREATE OR REPLACE circ_stats.checkin_full_view AS
+       SELECT  circ_id,
+               circ_timestamp,
+
+               circ_lib,
+               circ_staff,
+               circ_opac_renewal,
+               circ_self_checkout,
+               circ_recuring_fine,
+               circ_max_fine,
+               circ_fine_interval,
+               circ_duration,
+               circ_due_date,
+               circ_duration_rule,
+               circ_recuring_fine_rule,
+               circ_max_fine_rule,
+               circ_stop_fines,
+
+               usr_id,
+               usr_grp,
+               usr_standing,
+               usr_home_ou,
+               usr_county,
+               usr_city,
+               usr_post_code,
+
+               copy_holdable,
+               copy_ref,
+               copy_circulate,
+               copy_opac_visible,
+               copy_circ_lib,
+               copy_location,
+               copy_fine_level,
+               copy_loan_duration,
+               copy_location,
+               copy_circ_modifer,
+               copy_circ_as_type,
+               copy_call_number_label,
+
+               bib_item_type,
+               bib_item_form,
+               bib_level,
+               bib_control_type,
+               bib_char_encoding,
+               bib_enc_level,
+               bib_audience,
+               bib_cat_form,
+               bib_pub_status,
+               bib_pub_date,
+               bib_item_lang,
+
+         FROM  circ_stats.checkin_fact f
+               JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
+               JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
+               JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
+               JOIN circ_stats.bib_dim bd ON (f.bib_dim = bd.id);
+
+CREATE OR REPLACE circ_stats.renewal_full_view AS
+       SELECT  circ_id,
+               circ_timestamp,
+
+               circ_lib,
+               circ_staff,
+               circ_opac_renewal,
+               circ_self_checkout,
+               circ_recuring_fine,
+               circ_max_fine,
+               circ_fine_interval,
+               circ_duration,
+               circ_due_date,
+               circ_duration_rule,
+               circ_recuring_fine_rule,
+               circ_max_fine_rule,
+               circ_stop_fines,
+
+               usr_id,
+               usr_grp,
+               usr_standing,
+               usr_home_ou,
+               usr_county,
+               usr_city,
+               usr_post_code,
+
+               copy_holdable,
+               copy_ref,
+               copy_circulate,
+               copy_opac_visible,
+               copy_circ_lib,
+               copy_location,
+               copy_fine_level,
+               copy_loan_duration,
+               copy_location,
+               copy_circ_modifer,
+               copy_circ_as_type,
+               copy_call_number_label,
+
+               bib_item_type,
+               bib_item_form,
+               bib_level,
+               bib_control_type,
+               bib_char_encoding,
+               bib_enc_level,
+               bib_audience,
+               bib_cat_form,
+               bib_pub_status,
+               bib_pub_date,
+               bib_item_lang,
+
+         FROM  circ_stats.renewal_fact f
+               JOIN circ_stats.circ_dim cd ON (f.circ_dim = cd.id)
+               JOIN circ_stats.usr_dim ud ON (f.usr_dim = ud.id)
+               JOIN circ_stats.copy_dim cpd ON (f.copy_dim = cpd.id)
+               JOIN circ_stats.bib_dim bd ON (f.bib_dim = bd.id);
+
+
+COMMIT;