When looking at the
Mylyn connector
Most Wanted List you'll see many issue tracker that do not have a dedicated connector to the Mylyn Eclipse Task based UI.
Many of these are backed by databases to store all the complex relations and workflow. In the next several blogs I'm going to demonstrate how easy it is to connect Mylyn to any database backed issue tracker using the
Industrial SQL Connector.
For the example I will be using
Eventum, created and used by MySQL, number 4 on the list. It is also OSS, so we can easily get it to play around with.
This will not be a full fledged connector, but a quick start to get the task change notification directly from email into Eclipse as first step in making you
more productive.
Install Industrial SQL Connector
This connector can be installed from
http://svn.codespot.com/a/eclipselabs.org/industrial-mylyn/trunk/com.industrialtsi.mylyn.site/
Note Images display Pre EclipseLabs update site
Download empty MySQL connector Fragment
You can download a ready made empty MySQL connector Fragment as described
here and unzip it and rename some files, so we get this structure:
I want to make this easier but need code from PDE, please vote for
bug 288547: Add a NewFragmentFromTemplateWizard with API to make Fragments from templates
Install Eventum 2.2
Install this on you local machine following instructions provided
here, so you can inspect the database tables, create and test the queries.
Then when you are
completely satisfied that it works, do you change and connect to the production database!
Once the default install is completed use a database inspection tool like phpMyAdmin or soemthing else to familiarize yourself with the database structure. Luckily the Eventum designers used a very consistent naming scheme for TABLES and COLUMNS!
Once completed you can log into the system and start creating some initial users, projects and issues.
While creating a user, a project and a test issue in the web interface, make notes of what backend database tables change and get filled, because the Industrial SQL Connector is all about mapping database tables to task fields. Databases are usually constructed with record ID's for the machine and record names or titles for us, the users. As Mylyn is about making us more productive, we will as a matter of policy retrieve the human-readable formats from the database and map those back into records using
JOIN statements. The preliminary mapping is as follows:
| Task Side | Eventum Table |
|---|
| Tasks | eventum_issue |
| Products | eventum_project |
| Issue Status | eventum_status, eventum_resolution |
| Issue Priority | eventum_project_priority |
| Comments | eventum_issue_history |
| People | eventum_user, eventum_issue_user |
Creating the Connector: step 1 creating extension
Open the fragment.xml and create extension
org.eclipse.mylyn.industrial.core.persistor and create a
persistor-config, add an
ibatis-config. Fill in the fields as below:
Creating the Connector: step 2 retrieving repository elements
Edit the file
maps/eventum-direct-db/RepositoryMapEventum.xml as follows.
<?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="Repository">
<!--
Legal issue owners and other legal values for fields are taken here
from DISTINCT values in the database. An alternative would be a
SELECT from any table containing legal users.
-->
<!-- return a list of legal issue owners. -->
<select id="legalOwners" resultClass="string">
SELECT DISTINCT usr_full_name FROM eventum_user
</select>
<!-- return a list of legal products. -->
<select id="legalProducts" resultClass="string">
SELECT DISTINCT prj_title FROM eventum_project
</select>
<!-- return a list of legal issue status values. -->
<select id="legalIssueStatus" resultClass="string">
SELECT DISTINCT sta_title FROM eventum_status
</select>
<!-- return a legal list of priority values.
Note that in Mylyn these all need to be mapped to one of
"P1", "P2", "P3", "P4" or "P5". -->
<select id="legalPriority" resultClass="string">
SELECT DISTINCT concat('P',pri_rank) FROM eventum_project_priority
</select>
<!-- this query will be executed when pressing the Validate Connection in
the Repository Settings dialog -->
<statement id="validate" resultClass="boolean">SELECT TRUE </statement>
<!-- this query will be executed when pressing the Validate Connection in
the Repository Settings dialog -->
<statement id="authenticate" resultClass="boolean">
SELECT TRUE
</statement>
<!-- This query will be executed when validation fails and the repository
can be initialized or updated based on version (like local Derby) -->
<statement id="initialize" resultClass="string" parameterClass="string">
$value$
</statement>
</sqlMap>
This will give us a nice prefilled dialog in of the
Form Based Query:
Creating the Connector: step 3 mapping the task
Open the file
maps/eventum-direct-db/TaskMapEventum.xml and locate the query
getForKey. This query retrieves all mappable issue attributes give an issue ID to use as key.
<?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="Tasks">
<select id="getForKey" resultClass="ibatisTask">
SELECT
iss_id as taskId,
concat('P',pri_rank) AS priority,
usr_full_name AS owner,
iss_summary AS summary,
iss_description as notes,
prj_title AS product,
sta_title AS issueStatus,
iss_created_date AS creationDate,
iss_closed_date AS completionDateDate,
iss_expected_resolution_date AS dueDateDate
FROM eventum_issue
INNER JOIN eventum_issue_user ON isu_iss_id = iss_id
INNER JOIN eventum_user ON isu_usr_id = usr_id
INNER JOIN eventum_project ON iss_prj_id = prj_id
INNER JOIN eventum_status ON iss_sta_id = sta_id
INNER JOIN eventum_project_priority ON iss_pri_id = pri_id
WHERE iss_id LIKE #value#
</select>
[...]
</sqlMap>
Creating the Connector: step 4 adding dynamic search
We also need to edit the statement in
searchForKey for the Form Based Query to make its selection:
<?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="Tasks">
[...]
<select id="searchForKey" parameterClass="ibatisCriteria" resultClass="string">
SELECT DISTINCT iss_id as taskId FROM eventum_issue
<dynamic>
<isNotEmpty property="product">
INNER JOIN eventum_project ON iss_prj_id = prj_id
</isNotEmpty>
<isNotEmpty property="priority">
INNER JOIN eventum_project_priority ON iss_pri_id = pri_id
</isNotEmpty>
<isNotEmpty property="issueStatus">
INNER JOIN eventum_status ON iss_sta_id = sta_id
</isNotEmpty>
</dynamic>
<dynamic prepend="WHERE">
<isNotEmpty property="summary" prepend="AND"
removeFirstPrepend="true"> iss_summary LIKE '%$summary$%'
</isNotEmpty>
<isNotEmpty property="creationDateBefore" prepend="AND"
removeFirstPrepend="true"> iss_created_date <= #creationDateBefore#
</isNotEmpty>
<isNotEmpty property="creationDateAfter" prepend="AND"
removeFirstPrepend="true"> iss_created_date gt;= #creationDateBefore#
</isNotEmpty>
<isNotEmpty property="product">
<iterate property="product" conjunction="OR" open="(" close=")"
prepend="AND" removeFirstPrepend="true">
prj_title = #product[]#</iterate>
</isNotEmpty>
<isNotEmpty property="priority">
<iterate property="priority" conjunction="OR" open="(" close=")"
prepend="AND" removeFirstPrepend="true">
pri_rank = right(#priority[]#,1)</iterate>
</isNotEmpty>
<isNotEmpty property="issueStatus">
<iterate property="issueStatus" conjunction="OR" open="(" close=")"
prepend="AND" removeFirstPrepend="true">
sta_title = #issueStatus[]# </iterate>
</isNotEmpty>
</dynamic>
</select>
[...]
</sqlMap>
Using the
<dynamic> tag in the SqlMaps query language, you can specify fully dynamic queries, that will do this:
Creating the Connector: step 5 adding comments
When we have the basic task and attributes, we will not notice all activity that happens around a task. A Task or Issue usually has some sort of history associated with it, and this can be done by editing the file
maps/eventum-direct-db/CommentsMapEventum.xml as follows:
<?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">
[...]
<!--
groupKey use : when you want Mylyn to concatenate strings in different
records into one comment you can force that to happen by giving these
strings the same groupKey.
Leaving the the groupKey set to null, will concatenate all records into
one Mylyn comment. This is counterintuitive!!
-->
<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
</select>
[...]
</sqlMap>
Note the use of
groupKey in the statement. This construct will force all comments from the same date to be listed into one comment. Eventum issue edits result in multiple items being entered into the history using the same timestamp. Using
groupKey allows you to define or alter the grouping of comments.
Creating the Connector: step 6 adding additional attributes
Sometimes we have additional attributes that can be mapped to the Mylyn common attributes listed in
TaskAttributeMapper.java. Open the file
maps/eventum-direct-db/TaskMapEventum.xml and go to query
additionalForKey.
<?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="Tasks">
[...]
<!--
Keys for TaskData attributes used by Mylyn in the RepositoryTaskData structure
listed in file /org.eclipse.mylyn.tasks.core/src/org/eclipse/mylyn/tasks/core/data/TaskAttributeMapper.java
There is a problem in ibatis where dot notation fields returned in a Map are parsed as bean properties.
-->
<select id="additionalForKey" resultClass="java.util.HashMap">
SELECT
res_title as task_common_resolution,
usr_full_name as task_common_user_reporter
FROM eventum_issue
INNER JOIN eventum_resolution ON res_id = iss_res_id
INNER JOIN eventum_user ON usr_id = iss_usr_id
WHERE iss_id LIKE #value#
</select>
[...]
</sqlMap>
Conclusion
By following the simple steps outlined above, it is very easy to create a read-only connector to any database backed issue tracker. The Tasks in Mylyn are filled using a direct mapping of database columns to task elements and is not very complicated. Not a single line of Java code was needed! There is a usable UI to queries, and the results are displayed in the Task Editor, allowing a local context to be attached to it.
Connecting to database is easy when it is on the same LAN, slightly more involved when it is remote. My experience using a SSH tunnels using Putty (under Windows) or similar is that it can make a relatively secure connection easy to setup.
The connector fragment including source project can be downloaded in a ZIP from
here.
