1 <?xml version="1.0" encoding="utf-8"?>
2 <chapter version="5.0" xml:id="add-data-source" xml:lang="EN"
3 xmlns="http://docbook.org/ns/docbook"
4 xmlns:xlink="http://www.w3.org/1999/xlink"
5 xmlns:xi="http://www.w3.org/2001/XInclude"
6 xmlns:ns5="http://www.w3.org/1998/Math/MathML"
7 xmlns:ns4="http://www.w3.org/2000/svg"
8 xmlns:ns3="http://www.w3.org/1999/xhtml"
9 xmlns:ns="http://docbook.org/ns/docbook">
11 <title>Adding Data Sources to Reporter</title>
14 <para>You can further customize your Evergreen reporting environment by
15 adding additional data sources.</para>
17 <para>The Evergreen reporter module does not build and execute SQL queries
18 directly, but instead uses a data abstraction layer called Fieldmapper to
19 mediate queries on the Evergreen database. Fieldmapper is also used by other
20 core Evergreen DAO services, including cstore and permacrud. The
21 configuration file fm_IDL.xml contains the mapping between Fieldmapper class
22 definitions and the database. The fm_IDL.xml file is located in the
23 /openils/conf directory.</para>
25 <para>There are 3 basic steps to adding a new data source. Each step will be
26 discussed in more detail in the</para>
30 <para>Create a PostgreSQL query, view, or table that will provide the
31 data for your data source.</para>
35 <para>Add a new class to fm_IDL.xml for your data source.</para>
39 <para>Restart the affected services to see the new data source in
44 <para>There are two possbile sources for new data sources:</para>
48 <para>An SQL query built directly into the class definition in
49 fm_IDL.xml. You can use this method if you are only going to access this
50 data source through the Evergreen reporter and/or cstore code that you
55 <para>A new table or view in the Evergreen PostgresSQL database on which
56 a class definition in fm_IDL.xml. You can use this method if you want to
57 be able to access this data source through directly through SQL or using
58 other reporting tool.</para>
63 <title>Create a PostgreSQL query, view, or table that will provide the
64 data for your data source</title>
66 <para>You need to decide whether you will create your data source as a
67 query, a view, or a table.</para>
71 <para>Create a <emphasis>query</emphasis> if you are planning to
72 access this data source only through the Evergreen reporter and/or
73 cstore code that you write. You will use this query to create an IDL
78 <para>Create a <emphasis>view</emphasis> if you are planning to access
79 this data source through other methods in addition to the Evergreen
80 reporter, or if you may need to do performance tuning to optimize your
85 <para>You may also need to use an additional
86 <emphasis>table</emphasis> as part of your data source if you have
87 additional data that's not included in the base Evergreen, or if you
88 need to use a table to store the results of a query for performance
93 <para>To develop and test queries, views, and tables, you will need</para>
97 <para>Access to the Evergree PostgreSQL database at the command line.
98 This is normally the psql application. For introductory information,
99 please see <xref linkend="intro_to_sql" />. You can access the
100 Postgres documentation at the <link
101 xlink:href="http://www.postgresql.org/docs/">Official Postgres
102 documentation</link> for more information about PostgreSQL.</para>
106 <para>Knowledge of the Evergreen database structure for the data that
107 you want to access. You can find this information by looking at the
108 Evergreen schema - see <xref linkend="databaseschema" /> and <xref
109 linkend="data_models_and_access" /></para>
113 <para>If the views that you are creating are purely local in usage are are
114 intended for contribution to the core Evergreen code, create the Views and
115 Tables in the extend_reporter schema. This schema is intended to be used
116 for local customizations and will not be modified during upgrades to the
117 Evergreen system.</para>
119 <para>You should make that you have an appropriate version control pocess
120 for the SQL used to create you data sources.</para>
122 <para>Here's an example of a view created to incorporate some locally
123 defined user statistical categories.<programlisting>create view extend_reporter.patronstats as
126 rl.stat_cat_entry as "reg_lib",
127 gr.stat_cat_entry as "gender",
128 ag.stat_cat_entry as "age_group",
129 EXTRACT(YEAR FROM age(u.dob)) as "age",
135 join permission.grp_tree grp on (u.profile = grp.id and (grp.parent = 2 or grp.name = 'patron'))
136 join actor.org_unit hl on (u.home_ou = hl.id)
137 left join money.open_usr_summary ms on (ms.usr = u.id)
138 left join actor.stat_cat_entry_usr_map rl on (u.id = rl.target_usr and rl.stat_cat = 4)
139 left join actor.stat_cat_entry_usr_map bt on (u.id = bt.target_usr and bt.stat_cat = 3)
140 left join actor.stat_cat_entry_usr_map gr on (u.id = gr.target_usr and gr.stat_cat = 2)
141 left join actor.stat_cat_entry_usr_map gr on (u.id = gr.target_usr and gr.stat_cat = 2)
142 left join actor.stat_cat_entry_usr_map ag on (u.id = ag.target_usr and ag.stat_cat = 1)
143 where u.active = 't' and u.deleted <> 't';
144 </programlisting></para>
148 <title>Add a new class to fm_IDL.xml for your data source</title>
150 <para>Once you have your data source, the next step is to add that data
151 source as a new class in fm_IDL.xml. </para>
153 <para>You will need to add the following attributes for the class
158 <para><emphasis>id.</emphasis> You should follow a consistent naming
159 convention for your class names that won't create conflicts in the
160 future with any standard classes added in future upgrades. Evergreen
161 normally names each class with the first letter of each word in the
162 schema and table names. You may want to add a local prefix or suffix
163 to your local class names.</para>
167 <para><emphasis>controller=”open-ils.cstore”</emphasis></para>
171 <para><emphasis>oils_obj:fieldmapper</emphasis>=”extend_reporter::long_name_of_view”</para>
175 <para><emphasis>oils_persist.readonly</emphasis>=”true”</para>
179 <para><emphasis>reporter:core</emphasis>=”true” (if you want this to
180 show up as a “core” reporting source)</para>
184 <para><emphasis>reporter</emphasis>:label. This is the name that will
185 appear on the data source list in the Evergreen reporter.</para>
189 <para><emphasis>oils_persist:source_definition</emphasis>. If this is
190 an IDL-only view, add the SQL query here. You don't need this
191 attribute if your class is based on a PostgreSQL view or table.</para>
195 <para><emphasis>oils_persist:tablename=</emphasis>"schemaname.viewname
196 or tablename" If this class is based on a PostgreSQL view or table,
197 add the table name here. You don't need this attribute is your class
198 is an IDL-only view.</para>
202 <para>For each column in the view or query output, add
203 <emphasis>field</emphasis> element and set the following attributes. The
204 fields should be wrapped with <field> </field> </para>
208 <para>reporter:label. This is the name that appears in the Evergreen
213 <para>name. This should match the column name in the view or query
218 <para>reporter:datatype (which can be id, bool, money, org_unit, int,
219 number, interval, float, text, timestamp, or link)</para>
223 <para>For each linking field, add a <emphasis>link</emphasis> element with
224 the following attributes. The elements should be wrapped with <link>
229 <para>field (should match field.name)</para>
233 <para>reltype (“has_a”, “might_have”, or “has_many”)</para>
237 <para>map (“”)</para>
241 <para>key (name of the linking field in the foreign table)</para>
245 <para>class (ID of the IDL class of the table that is to be linked
250 <para>The following example is a class definition for the example view
251 that was created in the previous section.</para>
253 <para><programlisting><class id="erpstats" controller="open-ils.reporter-store" oils_obj:fieldmapper="extend_reporter::patronstats" oils_persist:tablename="extend_reporter.patronstats" oils_persist:readonly="true" reporter:label="Patron Statistics" reporter:core="true">
254 <fields oils_persist:primary="id">
255 <field reporter:label="Patron ID" name="id" reporter:datatype="link" />
256 <field reporter:label="Patron Type" name="ptype" reporter:datatype="text" />
257 <field reporter:label="Reg Lib" name="reg_lib" reporter:datatype="text" />
258 <field reporter:label="Boro/Twp" name="boro_twp" reporter:datatype="text" />
259 <field reporter:label="Gender" name="gender" reporter:datatype="text" />
260 <field reporter:label="Age Group" name="age_group" reporter:datatype="text" />
261 <field reporter:label="Age" name="age" reporter:datatype="int" />
262 <field reporter:label="Home Lib ID" name="home_lib_id" reporter:datatype="link" />
263 <field reporter:label="Home Lib Code" name="home_lib_code" reporter:datatype="text" />
264 <field reporter:label="Home Lib" name="home_lib" reporter:datatype="text" />
265 <field reporter:label="Create Date" name="create_date" reporter:datatype="timestamp" />
266 <field reporter:label="Expire Date" name="expire_date" reporter:datatype="timestamp" />
267 <field reporter:label="Balance Owed" name="balance_owed" reporter:datatype="money" />
270 <link field="id" reltype="has_a" key="id" map="" class="au"/>
271 <link field="home_lib_id" reltype="has_a" key="id" map="" class="aou"/>
273 </class></programlisting><caution>
274 <para>fm_IDL.xml is used by other core Evergreen DAO services,
275 including cstore and permacrud. So changes to this file can affect the
276 entire Evergreen application, not just reporter. After making changes
277 fm_IDL.xml, it is a good idea to ensure that it is valid XML by using
278 a utility such as xmllint – a syntax error can render much of
279 Evergreen nonfunctional. Set up a good change control system for any
280 changes to fm_IDL.xml. You will need to keep a separate copy of you
281 local class definitions so that you can reapply the changes to
282 fm_IDL.xml after Evergreen upgrades.</para>
287 <title>Restart the affected services to see the new data source in the
290 <para>The following steps are needed to for Evergreen to recognize the
291 changes to fm_IDL.xml</para>
295 <para>Copy the updated fm_IDL.xml Update /openils/conf/fm_IDL.xml to
296 /openils/var/web/reports/fm_IDL.xml<programlisting>cp /openils/conf/fm_IDL.xml /openils/var/web/reports/fm_IDL.xml</programlisting></para>
300 <para>Run Autogen to to update the Javascript versions of the
301 fieldmapper definitions.</para>
303 <programlisting>/openils/bin/autogen.sh</programlisting>
307 <para>Restart C services<programlisting>osrf_ctl.sh -l -a restart_c</programlisting></para>
311 <para>Restart the Evergreen reporter. You may need to modify this
312 command depending on your system configuration and pid
313 path<programlisting>opensrf-perl.pl -l -action restart -service open-ils.reporter -config /openils/conf/opensrf_core.xml -pid-dir /openils/var/run</programlisting></para>
317 <para>Restart the Evergreen application or use Admin, For Developers,