zaterdag 29 augustus 2009

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.

1 opmerking:

  1. Hi, I have used your example to successfully connect to our postgresql GForge database. My problem is that my open tasks display as closed items and that I cannot edit any of the tasks.

    Closed items show the date they were closed, but it seems when completionDate is null it shows them as completed, but when I introspect a task with the developer tools there is no completion date attribute in the properties view. My query is as follows:

    <select id="getForKey" resultClass="ibatisTask">
    SELECT
    (TIMESTAMP WITH TIME ZONE 'epoch' + close_date * INTERVAL '1 second') AS "completionDate",
    a.artifact_id as taskId,
    user_name AS owner,
    summary AS summary,
    priority AS priority,
    status_name AS issueStatus,
    field_data AS product,
    details as notes,
    (TIMESTAMP WITH TIME ZONE 'epoch' + open_date * INTERVAL '1 second') AS creationDate,
    null AS scheduledForDate,
    0 as estimatedTimeHours
    FROM artifact a, users b, artifact_status c, artifact_extra_field_data d WHERE a.assigned_to = b.user_id and a.artifact_id = d.artifact_id and extra_field_id = 121 and a.status_id = c.id and a.artifact_id=#value#
    </select>

    The creationDate works fine, but completionDate does not, even if I change the query like to make the completionDate equal to the creationDate (which works). All tasks seem to stay completed, and I cannot edit the task to make it not so.

    Can you give me some hints as to how to go about finding the problem?

    Zach

    BeantwoordenVerwijderen