time series reporting and links for said on the wide-copy view
authormiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Thu, 1 Dec 2005 22:54:22 +0000 (22:54 +0000)
committermiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Thu, 1 Dec 2005 22:54:22 +0000 (22:54 +0000)
git-svn-id: svn://svn.open-ils.org/ILS/trunk@2153 dcc99617-32d9-48b4-a31d-7c20da2025e4

Open-ILS/src/reporter/config.sql
Open-ILS/src/reporter/tables.example.xml
Open-ILS/src/sql/Pg/040.schema.asset.sql

index a8237e8..373cb8f 100644 (file)
@@ -4,6 +4,20 @@ CREATE SCHEMA reporter;
 
 BEGIN;
 
+CREATE OR REPLACE VIEW reporter.date_series AS
+       SELECT  CAST('1900/01/01' AS DATE) + x AS date,
+               CAST('1900/01/01' AS DATE) + x AS date_label
+         FROM  GENERATE_SERIES(
+                       0,
+                       CAST( EXTRACT( 'days' FROM CAST( NOW() - CAST( '1900/01/01' AS DATE ) AS INTERVAL ) ) AS INT )
+               ) AS g(x);
+
+CREATE OR REPLACE VIEW reporter.date_hour_series AS
+       SELECT  CAST(date + CAST(h || ' hours' AS INTERVAL) AS TIMESTAMP WITH TIME ZONE) AS date_hour,
+               CAST(date + CAST(h || ' hours' AS INTERVAL) AS TIMESTAMP WITH TIME ZONE) AS date_hour_label
+         FROM  reporter.date_series,
+               GENERATE_SERIES(0,23) g(h);
+
 CREATE TABLE reporter.stage2 (
        id              serial                          primary key,
        stage1          text                            not null, 
index 8023af1..5b13cd4 100644 (file)
                                <description>ID of the Circulating Library</description>
                        </field>
                        <field
+                         name="create_date_day"
+                         datatype="date">
+                               <label>Create Date Joiner</label>
+                               <description>Date of the copy's cataloging</description>
+                       </field>
+                       <field
+                         name="create_date_hour"
+                         datatype="timestamptz">
+                               <label>Create Date and Hour Joiner</label>
+                               <description>Date and Hour of the copy's cataloging</description>
+                       </field>
+                       <field
                          name="create_date"
                          datatype="timestamptz"
                          indexed="true"
                                <description>Timestamp of the copy's cataloging</description>
                        </field>
                        <field
+                         name="edit_date_day"
+                         datatype="date">
+                               <label>Edit Date Joiner</label>
+                               <description>Date of the copy's last edit</description>
+                       </field>
+                       <field
+                         name="edit_date_hour"
+                         datatype="timestamptz">
+                               <label>Edit Date and Hour Joiner</label>
+                               <description>Date and Hour of the copy's last edit</description>
+                       </field>
+                       <field
+                         name="edit_date"
+                         datatype="timestamptz"
+                         indexed="true"
+                         default="now()">
+                               <label>Edit Date</label>
+                               <description>Timestamp of the copy's last edit</description>
+                       </field>
+                       <field
                          name="item_lang"
                          datatype="text">
                                <label>Language</label>
 
                <links>
                        <link
+                         field="create_date_day"
+                         table="generic_day_series"
+                         id="copy_create_day"
+                         key="date"
+                         type="has_a"/>
+                       <link
+                         field="create_date_hour"
+                         table="generic_hour_series"
+                         id="copy_create_hour"
+                         key="date_hour"
+                         type="has_a"/>
+                       <link
                          field="owning_lib"
                          table="org_unit"
                          id="copy_owning_lib"
                </fields>
        </table>
 
+       <table id="generic_day_series" partition="false" fact-table="false">
+               <label>Constant day series</label>
+               <description>View providing a day series starting at 1900 and ending at 'now'</description>
+               <tablename>reporter.date_series</tablename>
+
+               <fields>
+                       <field
+                         name="date"
+                         primary='true'
+                         datatype="text">
+                               <label>Date joiner</label>
+                               <description>Date joiner</description>
+                       </field>
+                       <field
+                         name="date_label"
+                         datatype="date">
+                               <label>Date</label>
+                               <description>Date</description>
+                       </field>
+               </fields>
+       </table>
+
+       <table id="generic_hour_series" partition="false" fact-table="false">
+               <label>Constant hour series</label>
+               <description>View providing a hour series starting at 1900 and ending at 'now'</description>
+               <tablename>reporter.date_hour_series</tablename>
+
+               <fields>
+                       <field
+                         name="date_hour"
+                         primary='true'
+                         datatype="text">
+                               <label>time joiner</label>
+                               <description>time joiner</description>
+                       </field>
+                       <field
+                         name="date_hour_label"
+                         datatype="timestamptz">
+                               <label>Date and Hour</label>
+                               <description>Date and Hour</description>
+                       </field>
+               </fields>
+       </table>
+
        <table id="marc_form_map" partition="false" fact-table="false">
                <label>MARC Item Form Codes</label>
                <description>Table mapping MARC three character codes to Item Form names</description>
index 38c4733..ab372b5 100644 (file)
@@ -135,7 +135,11 @@ CREATE TABLE asset.call_number_note (
 
 CREATE VIEW stats.fleshed_copy AS 
         SELECT  cp.*,
-                cn.label as call_number_label,
+               CAST(cp.create_date AS DATE) AS create_date_day,
+               CAST(cp.edit_date AS DATE) AS edit_date_day,
+               DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
+               DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
+                cn.label AS call_number_label,
                 cn.owning_lib,
                 rd.item_lang,
                 rd.item_type,
@@ -146,7 +150,11 @@ CREATE VIEW stats.fleshed_copy AS
 
 CREATE VIEW stats.fleshed_call_number AS 
         SELECT  cn.*,
-                rd.item_lang,
+                       CAST(cn.create_date AS DATE) AS create_date_day,
+               CAST(cn.edit_date AS DATE) AS edit_date_day,
+               DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
+               DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
+               rd.item_lang,
                 rd.item_type,
                 rd.item_form
         FROM    asset.call_number cn