donderdag 17 september 2009

Industrial SQL Connector and Eventum: UNION queries

After playing around with Eventum some more as a result of looking into the Mylyn [connector] Eventum request I noticed that Eventum supports a very elaborate system of comments: Internal Notes, Phone calls, Drafts, emails and attachments in addition to the issue status history we included earlier.
Our question for today is whether we can support this diversity with the simplified task model provided with the Industrial SQL Connector?

MySQL UNION queries

MySQL provides the UNION statement to merge the results of two or more SELECT queries. So we try the following edit of CommentMapEventum.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<!--
licensed under ECL, to connect Mylyn to SQL databases
Copyright 2009 Maarten Meijer, all right reserved.
http://eclipsophy.blogger.com
-->
<sqlMap namespace="Comments">
...
<select id="getForKey" resultClass="ibatisComment">
(SELECT
his_created_date as groupKey,
usr_email as author,
usr_full_name as authorName,
his_created_date as date,
his_summary as text
FROM eventum_issue_history
INNER JOIN eventum_user ON his_usr_id = usr_id
WHERE his_iss_id = #value#
ORDER BY his_id)
UNION
(SELECT
not_created_date as groupKey,
usr_email as author,
usr_full_name as authorName,
not_created_date as date,
not_note as text
FROM eventum_note
INNER JOIN eventum_user ON not_usr_id = usr_id
WHERE not_iss_id = #value#
ORDER BY not_id)
</select>
...
</sqlMap>

Results from modified query

We run again and look at an issue with an internal note attached:

We see the internal notes text merged with the history. When we use a CONCAT() statement we can even prefix different parts with different makeup or create more elaborate text bits.

Conclusion


If you know how to write SQL queries you can do fairly complex stuff with the Industrial SQL Connector.
So go ahead and give it a try yourself! Installation instructions are provided here.