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.