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.