Mylyn to MySQL: Setting up advanced queries

Last time we connected Mylyn to a MySQL database of a custom application and had results in the Mylyn Task List.

But I used a very simple query to connect and it would be pointless to have to rewrite a query inside the connector every time we want to make a selection. There is a way to make allowed values appear in the Industrial Connector Form Based Query dialog.

We see various fields that can all be queried. The fields Summary, Products, Owners, Status, and Priority can all be mapped to a certain database column or combination of columns.
Creation Date, Completion Date, and Due Date create dates to be matched agains database DATETIME or TIMESTAMP columns.

Extracting legal query values

We can easily extract values that exist in the database using a SELECT DISTINCT query. So as exampel I'm going to fill the Products field with all different lighting applications selected in the database by editing TaskRepositoryFOLD.xml.

<select id="legalProducts" resultClass="string">
    SELECT 'empty'
</select>

I change that to the following:

<select id="legalProducts" resultClass="string">
    SELECT DISTINCT q_application FROM lqueries
</select>

We run again and we can see the different values to select in the Form Based Quer dialog.

We can do the same for owners, issueStatus and Priority.

Using the legal query values in Dynamic Queries

The Industrial SQL Connector uses Apache Ibatis as one of the connection layers.
Ibatis allows you make fully dynamic queries using a set of XML statements. Documentation can be downloaded here.

We revise the query searchForKey to include dynamic statements as follows:

<select id="searchForKey" parameterClass="ibatisCriteria" resultClass="string">
    SELECT DISTINCT q_ID as taskId FROM lighting.lqueries
    <dynamic prepend="WHERE">
        <isNotEmpty property="owner">
           <iterate property="owner" conjunction="OR" open="(" close=")"
                prepend="AND" removeFirstPrepend="true">
                q_remote_addr = #owner[]# 
        </isNotEmpty>
        <isNotEmpty property="product">
            <iterate property="product" conjunction="OR" open="(" close=")"
                prepend="AND" removeFirstPrepend="true">
                q_application = #product[]# 
        </isNotEmpty>
        <isNotEmpty property="summary" prepend="AND"
            removeFirstPrepend="true"> q_projecttext LIKE '%$summary$%'
        </isNotEmpty>
        <isNotEmpty property="creationDateBefore" prepend="AND"
            removeFirstPrepend="true"> q_datetime &lt;= #creationDateBefore#
        </isNotEmpty>
        <isNotEmpty property="creationDateAfter" prepend="AND"
            removeFirstPrepend="true"> q_datetime &gt;= #creationDateBefore#
        </isNotEmpty>
        <isEmpty property="priority" prepend="AND">
            q_nresults &lt; 50
        </isEmpty>
        <isNotEmpty property="priority" prepend="AND">
            q_nresults &lt; 50
        </isNotEmpty>
    </dynamic>
</select>

Run again and trying to create a second Form Based Query query as follows:

This will give the these results:

Summary

The Industrial SQL Connector makes it easy to connect any sort of Problem, Incident, Task like data in Mylyn by specifying a few queries. And because of that it makes available the tremendous productivity gains offered by Mylyn to a wide audience.

Surely allowing direct access to database over the internet can be seen as a security risk, but this can be covered at a lower level using SSH2 tunnels with certifcates. There may also be workflow related issues that are not coded in this connector, but even by creating a read-only connector, you will get productivity gains because the local context is stored and time can be tracked.

Mylyn to MySQL: writing the queries

In the previous post I showed you how to easily create a connector for Mylyn to any MySQL database.
This time we cover how writing a few simple queries will display items from the database as Tasks in the Mylyn Task List View.

In order to do the first iteration of retrieving "task like" records from the database, I write 2 queries in TaskMapFOLD.xml:

Create searchForKey to select the desired records.

Then create getForKey to retrieve basic data to show in the task editor.

First version of searchForKey

I begin simply by selecting where the number of results is less than 50 as follows using LIMIT 10 initially:

< select id="searchForKey" parameterclass="ibatisCriteria" resultclass="string">
    SELECT DISTINCT q_ID as taskId FROM lighting.lqueries WHERE 50 > q_nresults LIMIT 10
</select>

For you connecting to another database it will be a different, but this query returns the keys for the items that you want to show.

First version of getForKey

To show items in the task list you must also retrieve some data, minimum is the notes field, like the task ID.

<select id="getForKey" resultClass="ibatisTask">
    SELECT q_ID as taskId, 'test' as notes  FROM lighting.lqueries WHERE q_ID LIKE #value#
</select>

Then we run again, get another eclipse and add a query to the repository we created last time, use the first method Using Form.


Add name for the query to enable the finish method.


And presto, we have items in the Task List!

Refining getForKey

I want to display more items in the task editor so I expand the query using MySQL functions where needed:

<select id="getForKey" resultClass="ibatisTask">
    SELECT
        q_ID as taskId,
        'P3' AS priority,
        q_remote_addr AS owner,
        q_projecttext AS summary,
        q_application AS issueStatus,
        q_merken AS product,
        concat(q_w, 'x', q_l, 'x(', q_ph, '-', q_wh,')') as notes,
        q_datetime AS creationDate
    FROM lighting.lqueries WHERE q_ID LIKE #value#
</select>

I run again and do a refresh on the almost empy editor and presto:

The newly added aliased fields in the query show up in the editor.

Summary

Once we have the basic MySQL Connector setup, we can quickly get items form a database in the Mylyn Task List and Task Editor by editing the queries.

Next time we cover making the query form more dynamic, so we can actually do a dynamic query using the form.