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.