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.