From 59bdad2f76cc795bea4632d01f9c6cb236adca01 Mon Sep 17 00:00:00 2001 From: June Rayner <raynerj@einetwork.net> Date: Wed, 31 Aug 2011 14:16:40 -0400 Subject: [PATCH] New document for adding data sources to reporter --- 2.0/reports/report-add-data-source.xml | 322 +++++++++++++++++++++++++++++++++ 1 file changed, 322 insertions(+) create mode 100644 2.0/reports/report-add-data-source.xml diff --git a/2.0/reports/report-add-data-source.xml b/2.0/reports/report-add-data-source.xml new file mode 100644 index 0000000000..88c596d545 --- /dev/null +++ b/2.0/reports/report-add-data-source.xml @@ -0,0 +1,322 @@ +<?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> -- 2.11.0