donderdag 17 december 2009

Proper handling of linked resources

In the Eclipse workbench you can import resources, meaning copying them into the workspace, or you can set up a folder to link to them, and they files stay where they were. For large datasets, linking has obvious advantages. There is even a Decorator to show linked resources.

When programming in Eclipse you sometimes must use plain Java based libraries. These libraries handle files using java.io.File. When you work in Eclipse you reference files in the workspace using org.eclipse.core.resources.IFile. So in this case I needed to convert between the two formats.

First attempt, WRONG!

My first attempt was to use f.getFullPath().toFile() and that worked! Because I tested all my code with small data sets in the workspace, it wasn't until later that I found out, this does not work with linked resources:
java.io.FileNotFoundException: /Remote-Data/eulumdat/data.file (No such file or directory)
at java.io.FileInputStream.open(Native Method)
at java.io.FileInputStream.(FileInputStream.java:106)
at java.io.FileReader.(FileReader.java:55)
at xxxx

Second attempt: the proper way

My went back to the offending code and used CTRL+SPACE again. Now I selected f.getLocation().toFile() and that did it!

Conclusion

In order to get your code working in all circumstances, you need to test with both resources in the workspace and outside.
I'm i good company though, as there are 64 open bugs in the Eclipse bugzilla about linked resources.
Also I suspect code that doesn't work with linked resources, will not work with the remote system explorer, like this one.

donderdag 10 december 2009

Dutch Eclipse Democamp 2009 at TSI International Nieuwegein

Yesterday TSI International hosted the Eclipse Democamp for about 40 people in Nieuwegein.

Photographic impression


Wim Jongman preparing for the kick off on Eclipse 4.0
Advanced networking facilities
All the chairs in the building set up
Making clear who paid for food and drink
and quite crowd came to listen and watch
Jeroen van Grondelle and Marcel Offermans about OSGI service patterns. (Only implemented in full in Apache Felix), Jeroen stressed that we have to rethink our application for OSGi to fully take advantage of its facilities and not continue in our old Eclipse habits. Think service, not listener.




Jim van Dijk on Presentation Modelling Framework. We have to learn to think about UI in a whole different way again, not in terms of implementation (widgets, controls, HTML, ...) but in their abstractions: conversations, dialogs, compound dialogs, etc.

Break for food and drink, THANK YOU Wim Jongman!
That went down just as well as the presentations
Roel Spilker and Reinier Zwitserloot talked about Project Lombok and showed the Lombox Eclipse plugin to the world for the first time!
Wim Jongman and Marcel Offermans talked about and demonstrated OSGi in the cloud using Eclipse and Apache ACE.
Finally we heard and saw Jelle Herold about Verostko graphics toolkit & Statebox process engine.

Conclusion

A well hosted gathering with excellent presentations for a growing number of Eclipse enthusiasts.

Decorating your Jobs

Reinforcing your brand with every UI contact of your plugin is important in this competitive world. Many plugins launch Jobs when when Eclipse is starting up, to refresh their data, check the license, or whatever. Some Jobs display an icon to reveal the identity and reinforce the brand. Below is an example, showing two jobs that use text only and one with an icon.So how is this achieved?

step 1: register with Workbench ProgressService

An Eclipse Job can belong to a family, where a family can be any java Object. The ProgressService maintains a table of icons associated with each family. So step one is to register your icon and family in your Activator's start() method.
@Override
public void start(final BundleContext context) throws Exception {
super.start(context);
getWorkbench().getProgressService().
registerIconForFamily(
getImageDescriptor(ICONPATH),
MyTools.PLUGIN_ID);
[...]
Rebuilder rebuilder = new Rebuilder("Initializing My Tools");
rebuilder.schedule(20L);
}

step 2: override belongsTo() in your Job subclass

Next you override the method belongsTo() in your subclass with some simpel logic, calling super.belongsTo()when not equal top allow for Job class hierarchies.
public class Rebuilder extends Job {
[...]
@Override
public boolean belongsTo(final Object family) {
if (family.equals(MyTools.PLUGIN_ID)) {
return true;
}
return super.belongsTo(family);
}
[...]
}

Conclusion

As always in Eclipse programming this solution took a long time to find, but can be implemented in a few lines of code once you know how.

Caveat

What remains is that you can only do this for Jobs that you launch yourself, and not for other background tasks like the Auto Build jobs that call your Builders.
They all share the same icon :-(
I have created a bug for this, please support and vote here.

vrijdag 4 december 2009

WorkbenchMarkerResolution is fantastic!

One of my ongoing projects that is in beta now for a long time is EulumdatTools, a special purpose editor and workbench for managing, verifying and editing EULUMDAT files. EULUMDAT is a European de facto standard for photometric data files. It describes stuff like manufacturer, product name, lamp type, power consumption and luminous flux distribution for lighting products.

Builder, IMarker and IMarkerResolution2


I used Eclipse to create a Builder, IMarkers and IMarkerResolution2 to create a Validator, Problems view entries and Quick Fix solutions under CTRL/CMD+1 to resolve issues where file were not conforming to standard or incomplete (for the curious: some manufacturers do not provide the Direct Flux Factors).
There are 10 of these Direct Flux Factors in every file, so it would be nice to be able to fix these all in one action.

WorkbenchMarkerResolution to the rescue!

After some research I found that that is easier to implement than I first thought! What you must do is make your Quick Fix code extend WorkbenchMarkerReslution instead of implement IMarkerResolution2.
This means you must implement one extra method: IMarker[] findOtherMarkers(IMarker[] markers). You receive ALL of the markers in the problems view and must return an array of those you can handle in this Quick Fix, this is easy to implement with a loop and a isValidOther() method.
public class ReplaceDFF extends WorkbenchMarkerResolution {

private final IMarker originalMarker;

// use constructor to remember original marker
public ReplaceDFF(final IMarker marker) {
super();
originalMarker = marker;
}

@Override
public IMarker[] findOtherMarkers(IMarker[] markers) {
List<IMarker> others = new ArrayList<IMarker>();
for (IMarker marker : markers) {
if (isValidOther(marker)) {
others.add(marker);
}
}
return others.toArray(new IMarker[0]);
}

@Override
public boolean isValidOther(final IMarker marker) {
// is it the originalMarker, we don't want duplicates!
if(markerToCheck.equals(originalMarker)) {
return false;
}
// is it in the same file as original marker?
if(!marker.getResource().equals(originalMarker.getResource())) {
return false;
}
// is it the same validator?
String checkerName = LightOutputRatioChecker.class.getName();
if(!checkerName.equals(getCheckerName(marker))) {
return false;
}
// is it the same error found?
String checkerMessage = getCheckerMessage(marker);
if(!checkerMessage.startsWith(
LightOutputRatioChecker.DIRECT_RATIO_1))
{
return false;
}
return true;
}
[...]
}

The result is improved usability

Running with this small modification gives us this Quick Fix Dialog:
In practice the values are all correct or more than one is wrong, so handling this in one Quick fix will improve the users productivity in a big way. Click once, save the file and all warnings are gone...

Final notes...

I experimented with various filtering strategies before settling on fixing all similar errors in one file. Using the filters you can also opt to:
  • Fix different errors all in the same file, when your run(IMarker) method can handle different errors.
  • Find all errors in the workspace, letting through all markers with this fix. But then each fix required the file to be opened, so that takes long time and this cannot be cancelled.
  • I may still try to check whether the file is already open and only suggest to apply the fix in these.

donderdag 19 november 2009

Eclipse Demo Camp in Netherlands

We have to wait for a few more days for Eclipse Democamp in Netherlands.
Five presentations and thirty attendees already...

zondag 25 oktober 2009

DIY Mylyn Twitter connector

Connecting Mylyn to SQL databases is easy using the Industrial SQL Connector for Mylyn, but you can also connect to something else by implementing or extending the IPersistor interface.
In this blog I describe how to quickly create a basic connector that will allow you follow one or more Twitter feeds right in the Mylyn Task List. Twitter is used to send out notifications by an ever increasing range of hardware or processes:
  • Wim Jongman tweeted about connecting an AS/400 or iSeries to Twitter to notify on jobs>>.
  • Kim Moir added twitter as a way of build notifications for the Equinox team using Hudson twitter plugin >>
  • And there are probably many more...

We are going to create a read-only connector to start with, as these build processes and hardware will not read any responses to their tweets or please their egos by following mentions!

Initial Setup

Install the Industrial SQL Connector for Mylyn from the update site (detailed instructions) or extract the source from SVN.
2011 02 22 Updated links to EclipseLabs update site

Create fragment project

Create a fragment project selecting the org.eclipse.mylyn.industrial.core plugin as host..
Create a lib folder and a run-configs folder. (I need to create a template wizard for this soon!)

Select twitter java library

I looked at the twitter API wiki and chose for Twitter4J as various source considered it most mature. Download it and install it in the fragment's lib folder. There is no need to export any packages!

Create Persistor extension

In the extensions tab of the fragment.xml create an extension to extension point org.eclipse.mylyn.industrial.core.persistor and create a new persistor-config.

Then set the repository attributes to false except for can-create-task-from-key and can-query-repository

Then set all the task attributes to read-only.

Code the Persistor extension using the library

Now we click on the persistor hyperlink to create the Java class, we extend the class PersistorAdapter that basicaly logs all calls and returns sensible defaults.
To query and show tasks we need to implement fetchTask(...), findTasks(...), and to make querying easier also findlegalOwners(...).
package org.eclipse.mylyn.industrial.twitter.persistor;
[...]
public class TwitterPersistor extends PersistorAdapter {

public final static String ID = "org.eclipse.mylyn.industrial.twitter"; //$NON-NLS-1$

private User user;

private Twitter twitter;

public TwitterPersistor() {
}

/**
* @return the twitter, initialize if needed
*/
public Twitter getTwitter(TaskRepository repository) {
if (null == twitter) {
AuthenticationCredentials credentials = repository
.getCredentials(AuthenticationType.REPOSITORY);
String twitterPassword = credentials.getPassword();
String twitterID = credentials.getUserName();
twitter = new Twitter(twitterID, twitterPassword);
}
return twitter;
}

@Override
public IndustrialTask fetchTask(TaskRepository repository, String... taskId)
throws SQLException, CoreException {
Twitter t = getTwitter(repository);

long id = Long.parseLong(taskId[0]);

try {
twitter4j.Status result = t.showStatus(id);
IndustrialTask tweet = new IndustrialTask(repository.getUrl(),
taskId[0], result.getText());

tweet.setOwner(result.getUser().getName());
tweet.setCreationDate(result.getCreatedAt());
tweet.setNotes(result.getText());
return tweet;
} catch (TwitterException e) {
IStatus status = CoreLogger.createStatus(IStatus.ERROR, e);
throw new CoreException(status);
}
}

@Override
public List<String> getLegalOwners(TaskRepository repository)
throws SQLException, CoreException {
Twitter t = getTwitter(repository);

List<String> result = new ArrayList<String>();
List<User> friends;
try {
friends = t.getFriendsStatuses();
for (User friend : friends) {
result.add(friend.getName());
}
return result;
} catch (TwitterException e) {
IStatus status = CoreLogger.createStatus(IStatus.ERROR, e);
throw new CoreException(status);
}
}

@Override
public List<String> findTasks(TaskRepository repository,
IndustrialQueryParams criteria) throws SQLException, CoreException {
Twitter t = getTwitter(repository);

List<String> result = new ArrayList<String>();
try {
for (String user : criteria.getOwner()) {
List<twitter4j.Status> timeline;
timeline = t.getUserTimeline(user);
for (twitter4j.Status s : timeline) {
result.add(Long.toString(s.getId()));
}
}
return result;
} catch (TwitterException e) {
IStatus status = CoreLogger.createStatus(IStatus.ERROR, e);
throw new CoreException(status);
}
}

@Override
public boolean validate(TaskRepository repository) throws SQLException,
CoreException {
return null != getTwitter(repository);
}

@Override
public boolean isAuthenticated(TaskRepository repository)
throws SQLException, CoreException {

try {
user = getTwitter(repository).verifyCredentials();
return true;
} catch (TwitterException e) {
Status status = new Status(IStatus.ERROR, TwitterPersistor.ID,
"Cannot validate Twitter"); //$NON-NLS-1$
throw new CoreException(status);
}
}
}

We add the methods isAuthenticated(...) and validate(...) but both can just default to returning true when just statrting your development.

Now Run and we're Done!


OK the we run the whole project and create a new twitter repository, using your own name and password:

It will even check whether we entered our password correctly when we press the Validate button and call validate(...)

We can now do a simple query on the list of tweeters we are following using a form based query.

And presto, we have the tweeted AS/400 messages in our Mylyn Tasklist!

Conclusion


By parameterizing the creation of Mylyn connectors, the Industrial SQL Connector for Mylyn makes creating a quick and dirty connector to almost anything very, very easy. Creating this connector took me a little more than 2 hours, almost less than creating this blog entry.
Source code can be downloaded here.

vrijdag 16 oktober 2009

Eclipse RCP in a day

Yesterday I spent a very pleasurable afternoon and evening helping out with the Eclipse RCP in a Day course organized by Industrial TSI for members of NL JUG, the Dutch Java Users Group. Topics covered were: OSGi, RCP Application, plugins, Views, Commands, Branding and building applications.

Quite a number of people participated, so maybe we can soon welcome more members to the Eclipse tribe...

The training was given by Wim Jongman, the Dutch Mr Eclipse himself!

Lets hope we see these people back for the Eclipse training series where all will be covered in more depth.

donderdag 1 oktober 2009

Wanted: Eclipse App & Service Store

iTunes app store success with over a billion downloads begs for an Eclipse equivalent for not only providing updates but also discovery of plugins and services and training!

Read tecosystems for an UI outline and > /dev/null for The Aware Workbench.

I can open a bug for this on bugs.eclipse.org but under what part of Eclipse should it be filed: Platform? Incubator? Working Groups?

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.

Industrial SQL Connector for Mylyn - version 0.9.4.v20090917

While using the Industrial SQL Connector to connect Mylyn to an Eventum repository some bugs were uncovered.
These were fixed and a new release 0.9.4.v20090917 posted to the update site. Source is also available from the SVN source repository at svn://bugs.industrial-tsi.com/mylyn_gsc/trunk.

2011 02 22 After moving to EclipseLabs the update site is now here and SVN is here http://svn.codespot.com/a/eclipselabs.org/industrial-mylyn/trunk/


Problems fixed

- can-query-repository always returned true, now listens to setting in repository-attributes
when false, legal property values are no longer queried for, easier when creating a connector, you can go step by step.
- can-synchronize-tasks always returned true, now listens to setting in repository-attributes
- can-delete-tasks (Mylyn since 3.3) was unsupported, now listens to setting in repository-attributes

Enhancements

Added two more query fields in the Fom Based Query wizard:
"Description" and "Comments", so you can now search these as well when you can come up with the right query.

These can be referenced as notes and comments dynamic parameters in the searchForKey query in the TaskMapXXX.xml.

Complete Eventum connector 0.3.0

A zip complete with source of the Eventum connector so far can be downloaded from here.

dinsdag 15 september 2009

Industrial SQL connector and Eventum: adding attachments

Last time we covered Eventum task and comments, this time we'll make the attachments show up in Mylyn.

Eventum attachments


We create an attachment in our Eventum test installation and see what happens. Two tables are involved in the handling of attachments: eventum_issue_attachment containing the meta information like date created and creator, and eventum_issue_attachment_file containing actual data, filename, mime type and filesize.

Modifying the repository-config settings


We must edit fragment.xml and set the can-get-attachments property to true.

Creating the attachment data query


To return the attachment meta-data we must edit CommentsMapEventum.xml and set the getAttachmentForKey query 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">
...
<select id="getAttachmentForKey" resultClass="ibatisAttachment">
<!-- meta data only, return the blob data separately -->
SELECT
usr_email as author,
iaf_filetype as ctype,
iat_created_date as date,
iat_description as description,
iaf_filename as filename,
iaf_id as id,
iaf_filesize as size,
iat_iss_id as taskId,
'' as url
FROM eventum_issue_attachment
INNER JOIN eventum_user ON iat_usr_id = usr_id
INNER JOIN eventum_issue_attachment_file ON iat_id = iaf_iat_id
WHERE iat_iss_id = #value#
</select>
...
</sqlMap>

Creating the attachment BLOB query


To return the attachment binary BLOB we must edit CommentsMapEventum.xml and set the getAttachmentDataForKey query 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">
...
<resultMap id="attachmentDataMap" class="ibatisAttachment">
<result property="blob" column="iaf_file" jdbcType="BLOB" javaType="[B"/>
</resultMap>

<select id="getAttachmentDataForKey" resultMap="attachmentDataMap">
<!-- return the blob data -->
SELECT iaf_file
FROM eventum_issue_attachment_file
WHERE iaf_id = #value#
</select>
...
</sqlMap>

The attachment displays nicely but...


We can see the attachments in the Task Editor, an image uploaded using the web interface, but when we right-click we see several actions that do not work.


Open With Browser will not work, as the Eventum attachments are stored in a database, so they have no web url and cannot be shown in the browser. This is reported under bug 249021. This action should be dimmed when no URL is present.

Open With/Default EditorThis functionality was recently added, but does not take into account the case where no URL is present. This is in the works under bug 220314 and should be dimmed when no URL is present.

Make Local Copy and Open in Browser this is Industrial Connector functionality present to remedy the problems above, but is not a really neat solution.

maandag 7 september 2009

Industrial SQL Connector and Eventum

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 SideEventum Table
Taskseventum_issue
Productseventum_project
Issue Statuseventum_status, eventum_resolution
Issue Priorityeventum_project_priority
Commentseventum_issue_history
Peopleeventum_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.

donderdag 3 september 2009

Industrial SQL Connector versatility

I just created a quick solution for https://bugs.eclipse.org/150174 Eventum Mylyn connector using the Industrial SQL Mylyn connector toolkit in under 60 minutes! Screenshots @ bug, details to follow here.





A ZIP with basic code is available here