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.

donderdag 27 augustus 2009

Connecting Mylyn to a MySQL database

I have this website/product that proposes lighting products and plans based on user input of room dimensions, usage hours and lighting task. For a demo see Fast Online Lighting Design.
Basically you enter room dimension, lighting task and your economic criteria, then you get 50 different proposals ordered on you economic criteria (3 year lowest cost, 5 year lowest cost, etc). So it is not an issue tracker, but a particular set of input criteria that results in zero or less than 50 proposals is a potential problem. I do the development for this in Eclipse using PHP so if I can quickly locate these problems inside Eclipse that would be very useful!


For this I will use the Mylyn SQL connector that can be downloaded from the update site at http://svn.codespot.com/a/eclipselabs.org/industrial-mylyn/trunk/com.industrialtsi.mylyn.site/.

First I will create a new fragment project called FOLD Log Connector as follows:


It will host in that will host in org.eclipse.mylyn.industrial.core and I set the version requirements a bit wider as we are not in full release yet.


Next I copy and rename some files from the Demo Derby project and replace all queries in the xxx.map files with SELECT NULL.



Then I edit the SqlMapConfix.xml to read as follows:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>

<!--
<properties resource="derby_local_demo/db.properties"/>
-->
<!-- These settings control SqlMap configuration details, primarily to do with transaction
management. They are all optional (see the Developer Guide for more). -->
<settings cacheModelsEnabled="true" enhancementEnabled="false"
lazyLoadingEnabled="false" maxRequests="32" maxSessions="10"
maxTransactions="5" useStatementNamespaces="true"/>
<!-- Type aliases allow you to use a shorter name for long fully qualified class names. -->

<typeAlias alias="ibatisTask" type="org.eclipse.mylyn.industrial.core.dto.IndustrialTask"/>
<typeAlias alias="ibatisCriteria" type="org.eclipse.mylyn.industrial.core.dto.IndustrialQueryParams"/>
<typeAlias alias="ibatisComment" type="org.eclipse.mylyn.industrial.core.dto.IndustrialComment"/>
<typeAlias alias="ibatisAttachment" type="org.eclipse.mylyn.industrial.core.dto.IndustrialAttachment"/>
<!-- Configure a datasource to use with this SQL Map using SimpleDataSource.
Notice the use of the properties from the above resource -->

<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="${driver}"/>
<property name="JDBC.ConnectionURL" value="${url}"/>
<property name="JDBC.Username" value="${user}"/>
<property name="JDBC.Password" value="${password}"/>
<property value="15" name="Pool.MaximumActiveConnections"/>
<property value="15" name="Pool.MaximumIdleConnections"/>
<property value="1000" name="Pool.MaximumWait"/>
</dataSource>
</transactionManager>
<!-- use the url syntax of the task maps to locate the sql Map absolutely. -->
<!-- use the resource syntax to locate in the class tree using class loader -->
<sqlMap resource="FOLD-log-connector/TaskMapFOLD.xml"/>
<sqlMap resource="FOLD-log-connector/RepositoryMapFOLD.xml"/>
<sqlMap resource="FOLD-log-connector/CommentsMapFOLD.xml"/>
</sqlMapConfig>



Next I create an extension org.eclipse.mylyn.industrial.persistor


I make the persistor an IbatisPersistsor


I also set most of the other settings to false or read-only.




Next I must add a specific ibatis-config, because we will use a persistor based on Ibatis SQLMaps.


The ibatis-config contains information on where to find the maps containing SQL statements and what driver to use.
I must also first import and include the MySQL driver and add it to the project classpath (for the extension pont wizard to find it)


Then I can select it in the extension tab.


Next I select the maps location as follows:


I export the driver classes and add the driver in lib and the maps directories to the classpath in the Runtime tab of the MANIFEST.MF.


Then everything is saved an we can run it. I get an Eclipse instance with Mylyn installed and can do an Add Repository. I fill in the fields and can do a Validate Settings



We can start to create the queries in the next session, because we connected.



Please note that everything we have done so far is generic for any MySQL database, so as a convenience to the reader I have created a zipped version of this project.
2011 02 22 After move to EclipseLabs this no longer available, sorry


Next time, I will cover getting the queries to work and provide real data from the database, using the Developer Tools along the way.

maandag 17 augustus 2009

Creating a Mylyn connector - looking inside

Who uses Eclipse but isn't using Mylyn? Still a substantial number of people gathering from the 46 bug reports with request for connectors to various well known task and issue management systems.

In addition to the listed systems many SMB's in the IT industry will be using a home-grown system built many years ago. That system is based on central database and custom client or web interface. The shared corporate knowledge and history built up in this database is so big that changing to another commercial or OSS system is not an option because of cost of migration. So also in these companies many Eclipse users will not be using Mylyn to its full potential.

Number 47 on the list of connectors is the Industrial SQL connector on which I'm working.

In a series of blogs I will try and explain how to quickly connect to any issue tracker using an accessible SQL Database.
Before we can start we must first install Mylyn Developer Tools and the Industrial SQL connector sources.

The Industrial SQL connector can be installed from the update site following the instuctions provide here.

Mylyn Developer Tools

Mylyn Developer Tools is a set of actions and property source providers that allow you to see into the Mylyn internals.

You get the source when you retrieve all of the Mylyn sources from CVS as described here. But as a convenience I have included a ready built version on the Industrial SQL Connector update site.

With this plugin you can inspect properties for Repositories, Tasks, Queries and Categories. All of these descend from an abstract AttributeContainer allowing easy key-value pair storage by the connector.


Task properties show up as items in the task editor.


Query Properties are simple: a URL describes the selection criteria.



Repository properties describe for example legal values for various fields.

The major part of creating a connector is to get your task repository items in the right Mylyn properties with the right marking for read only etc. So this tool is fairly essential!