[working/Evergreen.git] / docs / reports / reporter_add_data_source.txt

Adding Data Sources to Reporter\r
-------------------------------\r
\r
indexterm:[reports, adding data sources]\r
\r
You can further customize your Evergreen reporting environment by adding \r
additional data sources.\r
\r
The Evergreen reporter module does not build and execute SQL queries directly, \r
but instead uses a data abstraction layer called *Fieldmapper* to mediate queries \r
on the Evergreen database.Fieldmapper is also used by other core Evergreen DAO \r
services, including cstore and permacrud. The configuration file _fm_IDL.xml_ \r
contains the mapping between _Fieldmapper_ class definitions and the database. \r
The _fm_IDL.xml_ file is located in the _/openils/conf_ directory.\r
\r
indexterm:[fm_IDL.xml]\r
\r
There are 3 basic steps to adding a new data source. Each step will be discussed \r
in more detail in the\r
\r
. Create a PostgreSQL query, view, or table that will provide the data for your \r
data source.\r
. Add a new class to _fm_IDL.xml_ for your data source.\r
. Restart the affected services to see the new data source in Reporter.\r
\r
There are two possbile sources for new data sources:\r
\r
indexterm:[PostgreSQL]\r
\r
indexterm:[SQL]\r
\r
* An SQL query built directly into the class definition in _fm_IDL.xml_. You can \r
use this method if you are only going to access this data source through the \r
Evergreen reporter and/or cstore code that you write.\r
* A new table or view in the Evergreen PostgresSQL database on which a class \r
definition in _fm_IDL.xml_. You can use this method if you want to be able to \r
access this data source through directly through SQL or using other reporting tool.\r
\r
Create a PostgreSQL query, view, or table for your data source\r
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\r
\r
indexterm:[PostgreSQL]\r
\r
You need to decide whether you will create your data source as a query, a view, \r
or a table.\r
\r
. Create a query if you are planning to access this data source only through the \r
Evergreen reporter and/or cstore code that you write. You will use this query to \r
create an IDL only view.\r
. Create a view if you are planning to access this data source through other \r
methods in addition to the Evergreen reporter, or if you may need to do \r
performance tuning to optimize your query.\r
. You may also need to use an additional table as part of your data source if \r
you have additional data that's not included in the base Evergreen, or if you \r
need to use a table to store the results of a query for performance reasons.\r
\r
To develop and test queries, views, and tables, you will need\r
\r
* Access to the Evergree PostgreSQL database at the command line. This is \r
normally the psql application. You \r
can access the Postgres documentation at the \r
http://http://www.postgresql.org/docs/[Official Postgres documentation] for \r
more information about PostgreSQL.\r
* Knowledge of the Evergreen database structure for the data that you want to \r
access. You can find this information by looking at the Evergreen schema\r
http://docs.evergreen-ils.org/2.2/schema/[Evergreen schema] \r
\r
indexterm:[database schema]\r
\r
If the views that you are creating are purely local in usage and are not intended \r
for contribution to the core Evergreen code, create the Views and Tables in the \r
extend_reporter schema. This schema is intended to be used for local \r
customizations and will not be modified during upgrades to the Evergreen system.\r
\r
You should make that you have an appropriate version control pocess for the SQL \r
used to create you data sources.\r
\r
Here's an example of a view created to incorporate some locally defined user \r
statistical categories:\r
\r
.example view for reports\r
----------\r
create view extend_reporter.patronstats as\r
select u.id, \r
grp.name as "ptype",\r
rl.stat_cat_entry as "reg_lib",\r
gr.stat_cat_entry as "gender",\r
ag.stat_cat_entry as "age_group",\r
EXTRACT(YEAR FROM age(u.dob)) as "age",\r
hl.id as "home_lib",\r
u.create_date,\r
u.expire_date,\r
ms_balance_owed\r
from actor.usr u\r
join permission.grp_tree grp \r
        on (u.profile = grp.id and (grp.parent = 2 or grp.name = 'patron')) \r
join actor.org_unit hl on (u.home_ou = hl.id)\r
left join money.open_usr_summary ms \r
        on (ms.usr = u.id) \r
left join actor.stat_cat_entry_usr_map rl \r
        on (u.id = rl.target_usr and rl.stat_cat = 4) \r
left join actor.stat_cat_entry_usr_map bt \r
        on (u.id = bt.target_usr and bt.stat_cat = 3) \r
left join actor.stat_cat_entry_usr_map gr \r
        on (u.id = gr.target_usr and gr.stat_cat = 2) \r
left join actor.stat_cat_entry_usr_map gr \r
        on (u.id = gr.target_usr and gr.stat_cat = 2) \r
left join actor.stat_cat_entry_usr_map ag \r
        on (u.id = ag.target_usr and ag.stat_cat = 1) \r
where u.active = 't' and u.deleted <> 't';\r
-----------\r
\r
Add a new class to fm_IDL.xml for your data source\r
--------------------------------------------------\r
\r
Once you have your data source, the next step is to add that data source as a \r
new class in _fm_IDL.xml_.\r
\r
indexterm:[fm_IDL.xml]\r
\r
You will need to add the following attributes for the class definition\r
\r
* *id*. You should follow a consistent naming convention for your class names \r
that won't create conflicts in the future with any standard classes added in \r
future upgrades. Evergreen normally names each class with the first letter of \r
each word in the schema and table names. You may want to add a local prefix or \r
suffix to your local class names.\r
* *controller=”open-ils.cstore”*\r
* *oils_obj:fieldmapper=”extend_reporter::long_name_of_view”*\r
* *oils_persist.readonly=”true”*\r
* *reporter:core=”true”* (if you want this to show up as a “core” reporting source)\r
* *reporter:label*. This is the name that will appear on the data source list in \r
the Evergreen reporter.\r
* *oils_persist:source_definition*. If this is an IDL-only view, add the SQL query \r
here. You don't need this attribute if your class is based on a PostgreSQL view \r
or table.\r
* *oils_persist:tablename="schemaname.viewname or tablename"* If this class is \r
based on a PostgreSQL view or table, add the table name here. You don't need \r
this attribute is your class is an IDL-only view.\r
\r
For each column in the view or query output, add field element and set the \r
following attributes. The fields should be wrapped with _<field> </field>_\r
\r
* *reporter:label*. This is the name that appears in the Evergreen reporter.\r
* *name*. This should match the column name in the view or query output.\r
* *reporter:datatype* (which can be id, bool, money, org_unit, int, number, \r
interval, float, text, timestamp, or link)\r
\r
For each linking field, add a link element with the following attributes. The \r
elements should be wrapped with _<link> </link>_\r
* *field* (should match field.name)\r
* *reltype* (“has_a”, “might_have”, or “has_many”)\r
* *map* (“”)\r
* *key* (name of the linking field in the foreign table)\r
* *class* (ID of the IDL class of the table that is to be linked to)\r
\r
The following example is a class definition for the example view that was created \r
in the previous section.\r
\r
.example class definition for reports\r
----------\r
<class id="erpstats" controller="open-ils.reporter-store" \r
oils_obj:fieldmapper="extend_reporter::patronstats" \r
oils_persist:tablename="extend_reporter.patronstats" oils_persist:readonly="true" \r
reporter:label="Patron Statistics" reporter:core="true">\r
  <fields oils_persist:primary="id">\r
  <field reporter:label="Patron ID" name="id" reporter:datatype="link" />\r
  <field reporter:label="Patron Type" name="ptype" reporter:datatype="text" />\r
  <field reporter:label="Reg Lib" name="reg_lib" reporter:datatype="text" />\r
  <field reporter:label="Boro/Twp" name="boro_twp" reporter:datatype="text" />\r
  <field reporter:label="Gender" name="gender" reporter:datatype="text" />\r
  <field reporter:label="Age Group" name="age_group" reporter:datatype="text" />\r
  <field reporter:label="Age" name="age" reporter:datatype="int" />\r
  <field reporter:label="Home Lib ID" name="home_lib_id" \r
        reporter:datatype="link" />\r
  <field reporter:label="Home Lib Code" name="home_lib_code" \r
        reporter:datatype="text" />\r
  <field reporter:label="Home Lib" name="home_lib" reporter:datatype="text" />\r
  <field reporter:label="Create Date" name="create_date" \r
        reporter:datatype="timestamp" />\r
  <field reporter:label="Expire Date" name="expire_date" \r
        reporter:datatype="timestamp" />\r
  <field reporter:label="Balance Owed" name="balance_owed" \r
        reporter:datatype="money" />\r
</fields>\r
<links>\r
  <link field="id" reltype="has_a" key="id" map="" class="au"/>\r
  <link field="home_lib_id" reltype="has_a" key="id" map="" class="aou"/>\r
</links>\r
</class>\r
---------\r
\r
NOTE: _fm_IDL.xml_ is used by other core Evergreen DAO services, including cstore \r
and permacrud. So changes to this file can affect the entire Evergreen \r
application, not just reporter. After making changes fm_IDL.xml, it is a good \r
idea to ensure that it is valid XML by using a utility such as *xmllint* – a \r
syntax error can render much of Evergreen nonfunctional. Set up a good change \r
control system for any changes to fm_IDL.xml. You will need to keep a separate \r
copy of you local class definitions so that you can reapply the changes to \r
_fm_IDL.xml_ after Evergreen upgrades.\r
\r
Restart the affected services to see the new data source in the reporter\r
------------------------------------------------------------------------\r
\r
The following steps are needed to for Evergreen to recognize the changes to \r
_fm_IDL.xml_\r
\r
. Copy the updated _fm_IDL.xml_ Update _/openils/conf/fm_IDL.xml_ to \r
_/openils/var/web/reports/fm_IDL.xml_\r
+\r
-------------\r
cp _/openils/conf/fm_IDL.xml /openils/var/web/reports/fm_IDL.xml\r
-------------\r
+\r
. Run Autogen to to update the Javascript versions of the fieldmapper definitions.\r
+\r
-------------\r
/openils/bin/autogen.sh\r
-------------\r
+    \r
. Restart C services\r
+\r
-------------\r
osrf_ctl.sh -l -a restart_c\r
-------------\r
+\r
. Restart the Evergreen reporter. You may need to modify this command depending \r
on your system configuration and pid path\r
+\r
------------\r
opensrf-perl.pl -l -action restart -service open-ils.reporter \\r
-config /openils/conf/opensrf_core.xml -pid-dir /openils/var/run\r
------------\r
+\r
. Restart the Evergreen application or _use Admin --> For Developers --> Clear \r
Cache_\r
\r
\r
\r