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