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.checkout (
   8         -- circulation info
   9         circ_id                 BIGINT                          PRIMARY KEY,
  10         circ_checkout_time      TIMESTAMP WITH TIME ZONE        NOT NULL,
  11         circ_lib                INT                             NOT NULL,
  12         circ_self               BOOL                            NOT NULL,
  13         circ_staff              INT                             NOT NULL,
  14         circ_duration           INTERVAL                        NOT NULL,
  15         circ_duration_rule      TEXT                            NOT NULL,
  16         circ_recuring_fine      INTERVAL                        NOT NULL,
  17         circ_recuring_fine_rule TEXT                            NOT NULL,
  18         circ_max_fine           INTERVAL                        NOT NULL,
  19         circ_max_fine_rule      TEXT                            NOT NULL,
  20         circ_fine_interval      INTERVAL                        NOT NULL,
  21
  22         -- patron info
  23         -- usr_id               INT                             NOT NULL,
  24         usr_grp                 INT                             NOT NULL, -- aka profile
  25         usr_county              TEXT                            NOT NULL,
  26         usr_city                TEXT                            NOT NULL,
  27         usr_post_code           TEXT                            NOT NULL,
  28         usr_standing            INT                             NOT NULL,
  29         usr_home_ou             INT                             NOT NULL,
  30
  31         -- copy info
  32         cp_circ_lib             INT                             NOT NULL,
  33         cp_barcode              TEXT                            NOT NULL,
  34         cp_holdable             BOOL                            NOT NULL,
  35         cp_ref                  BOOL                            NOT NULL,
  36         cp_circulate            BOOL                            NOT NULL,
  37         cp_opac_visible         BOOL                            NOT NULL,
  38         cp_circ_modifer         TEXT                            NOT NULL,
  39         cp_circ_as_type         TEXT                            NOT NULL,
  40         cp_location             INT                             NOT NULL,
  41         cp_fine_level           INT                             NOT NULL,
  42         cp_load_duration        INT                             NOT NULL,
  43         cp_location             INT                             NOT NULL,
  44
  45         -- call number info
  46         cn_owning_lib           INT                             NOT NULL,
  47         cn_label                TEXT                            NOT NULL,
  48
  49         -- bib record info
  50         bib_id                  BIGINT                          NOT NULL,
  51         bib_item_type           "char"                          NOT NULL,
  52         bib_item_form           "char"                          NOT NULL,
  53         bib_level               "char"                          NOT NULL,
  54         bib_control_type        "char"                          NOT NULL,
  55         bib_char_encoding       "char"                          NOT NULL,
  56         bib_enc_level           "char"                          NOT NULL,
  57         bib_audience            "char"                          NOT NULL,
  58         bib_cat_form            TEXT                            NOT NULL,
  59         bib_pub_status          TEXT                            NOT NULL,
  60         bib_item_lang           TEXT                            NOT NULL
  61 ) WITHOUT OIDS;
  62
  63 CREATE TABLE circ_stats.renewal (
  64         -- circulation info
  65         circ_id                 BIGINT                          PRIMARY KEY,
  66         circ_renewal_time       TIMESTAMP WITH TIME ZONE        NOT NULL,
  67         circ_lib                INT                             NOT NULL,
  68         circ_opac               BOOL                            NOT NULL,
  69         circ_staff              INT                             NOT NULL,
  70         circ_duration           INTERVAL                        NOT NULL,
  71         circ_duration_rule      TEXT                            NOT NULL,
  72         circ_recuring_fine      INTERVAL                        NOT NULL,
  73         circ_recuring_fine_rule TEXT                            NOT NULL,
  74         circ_max_fine           INTERVAL                        NOT NULL,
  75         circ_max_fine_rule      TEXT                            NOT NULL,
  76         circ_fine_interval      INTERVAL                        NOT NULL,
  77
  78         -- patron info
  79         -- usr_id               INT                             NOT NULL,
  80         usr_grp                 INT                             NOT NULL, -- aka profile
  81         usr_county              TEXT                            NOT NULL,
  82         usr_city                TEXT                            NOT NULL,
  83         usr_post_code           TEXT                            NOT NULL,
  84         usr_standing            INT                             NOT NULL,
  85         usr_home_ou             INT                             NOT NULL,
  86
  87         -- copy info
  88         cp_circ_lib             INT                             NOT NULL,
  89         cp_barcode              TEXT                            NOT NULL,
  90         cp_holdable             BOOL                            NOT NULL,
  91         cp_ref                  BOOL                            NOT NULL,
  92         cp_circulate            BOOL                            NOT NULL,
  93         cp_opac_visible         BOOL                            NOT NULL,
  94         cp_circ_modifer         TEXT                            NOT NULL,
  95         cp_circ_as_type         TEXT                            NOT NULL,
  96         cp_location             INT                             NOT NULL,
  97         cp_fine_level           INT                             NOT NULL,
  98         cp_load_duration        INT                             NOT NULL,
  99         cp_location             INT                             NOT NULL,
 100
 101         -- call number info
 102         cn_owning_lib           INT                             NOT NULL,
 103         cn_label                TEXT                            NOT NULL,
 104
 105         -- bib record info
 106         bib_id                  BIGINT                          NOT NULL,
 107         bib_item_type           "char"                          NOT NULL,
 108         bib_item_form           "char"                          NOT NULL,
 109         bib_level               "char"                          NOT NULL,
 110         bib_control_type        "char"                          NOT NULL,
 111         bib_char_encoding       "char"                          NOT NULL,
 112         bib_enc_level           "char"                          NOT NULL,
 113         bib_audience            "char"                          NOT NULL,
 114         bib_cat_form            TEXT                            NOT NULL,
 115         bib_pub_status          TEXT                            NOT NULL,
 116         bib_item_lang           TEXT                            NOT NULL
 117 ) WITHOUT OIDS;
 118
 119 CREATE TABLE circ_stats.checkin (
 120         -- circulation info
 121         circ_id                 BIGINT                          PRIMARY KEY,
 122         circ_checkin_time       TIMESTAMP WITH TIME ZONE        NOT NULL,
 123         circ_checkout_lib       INT                             NOT NULL,
 124         circ_checkin_lib        INT                             NOT NULL,
 125         circ_staff              INT                             NOT NULL,
 126         circ_duration           INTERVAL                        NOT NULL,
 127         circ_duration_rule      TEXT                            NOT NULL,
 128         circ_recuring_fine      INTERVAL                        NOT NULL,
 129         circ_recuring_fine_rule TEXT                            NOT NULL,
 130         circ_max_fine           INTERVAL                        NOT NULL,
 131         circ_max_fine_rule      TEXT                            NOT NULL,
 132         circ_fine_interval      INTERVAL                        NOT NULL,
 133
 134         -- patron info
 135         -- usr_id               INT                             NOT NULL,
 136         usr_grp                 INT                             NOT NULL, -- aka profile
 137         usr_county              TEXT                            NOT NULL,
 138         usr_city                TEXT                            NOT NULL,
 139         usr_post_code           TEXT                            NOT NULL,
 140         usr_standing            INT                             NOT NULL,
 141         usr_home_ou             INT                             NOT NULL,
 142
 143         -- copy info
 144         cp_circ_lib             INT                             NOT NULL,
 145         cp_barcode              TEXT                            NOT NULL,
 146         cp_holdable             BOOL                            NOT NULL,
 147         cp_ref                  BOOL                            NOT NULL,
 148         cp_circulate            BOOL                            NOT NULL,
 149         cp_opac_visible         BOOL                            NOT NULL,
 150         cp_circ_modifer         TEXT                            NOT NULL,
 151         cp_circ_as_type         TEXT                            NOT NULL,
 152         cp_location             INT                             NOT NULL,
 153         cp_fine_level           INT                             NOT NULL,
 154         cp_load_duration        INT                             NOT NULL,
 155         cp_location             INT                             NOT NULL,
 156
 157         -- call number info
 158         cn_owning_lib           INT                             NOT NULL,
 159         cn_label                TEXT                            NOT NULL,
 160
 161         -- bib record info
 162         bib_id                  BIGINT                          NOT NULL,
 163         bib_item_type           "char"                          NOT NULL,
 164         bib_item_form           "char"                          NOT NULL,
 165         bib_level               "char"                          NOT NULL,
 166         bib_control_type        "char"                          NOT NULL,
 167         bib_char_encoding       "char"                          NOT NULL,
 168         bib_enc_level           "char"                          NOT NULL,
 169         bib_audience            "char"                          NOT NULL,
 170         bib_cat_form            TEXT                            NOT NULL,
 171         bib_pub_status          TEXT                            NOT NULL,
 172         bib_item_lang           TEXT                            NOT NULL
 173 ) WITHOUT OIDS;
 174