just the start ...

git-svn-id: svn://svn.open-ils.org/ILS/trunk@1712 dcc99617-32d9-48b4-a31d-7c20da2025e4

diff --git a/Open-ILS/src/sql/Pg/stats/asset_stats.sql b/Open-ILS/src/sql/Pg/stats/asset_stats.sql
new file mode 100644 (file)
index 0000000..4033f87
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/stats/asset_stats.sql
@@ -0,0 +1,18 @@
+DROP SCHEMA asset_hist CASCADE;
+
+BEGIN
+
+CREATE SCHEMA asset_hist;
+
+CREATE TABLE asset_hist.copy_immutables (
+       id              BIGINT                          PRIMARY KEY,
+       owning_lib      BIGINT                          NOT NULL,
+       creator         BIGINT                          NOT NULL,
+       create_date     TIMESTAMP WITH TIME ZONE        NOT NULL,
+       deleter         BIGINT,
+       delete_date     TIMESTAMP WITH TIME ZONE,
+       barcode         TEXT                            NOT NULL,
+       copy_number     INT                             NOT NULL,
+       price           NUMERIC(8,2)                    NOT NULL
+);
+

diff --git a/Open-ILS/src/sql/Pg/stats/circ_stats.sql b/Open-ILS/src/sql/Pg/stats/circ_stats.sql
new file mode 100644 (file)
index 0000000..45ff054
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/stats/circ_stats.sql
@@ -0,0 +1,174 @@
+DROP SCHEMA circ_stats CASCADE;
+
+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,
+       circ_lib                INT                             NOT NULL,
+       circ_self               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,
+
+       -- 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,
+
+       -- 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,
+
+       -- 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
+) WITHOUT OIDS;
+
+CREATE TABLE circ_stats.renewal (
+       -- 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,
+
+       -- 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,
+
+       -- 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,
+
+       -- 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
+) WITHOUT OIDS;
+
+CREATE TABLE circ_stats.checkin (
+       -- 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,
+
+       -- 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,
+
+       -- 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,
+
+       -- 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
+) WITHOUT OIDS;
+