Jump to: navigation, search

Mylyn/Incubator/Generic Industrial Connector

< Mylyn‎ | Incubator
Revision as of 18:25, 9 February 2009 by Mm105.xs4all.nl (Talk | contribs) (Create SQL statements for Repository conaining legal values)

This work is funded by Remain Software and Industrial-TSI.

This is a temp doc space for the generic SQL connector for mylyn.

Update Site

We have created an update site to try this connector for 3.0.x and 2.3.x.

Create the update site reference as follows, using the above URL:

Industrial url.png

Then select that site:

Industrial update site.png

And make select the latest version plus the Ibatis SqlMap feature:

Industrial selection.png

Temporary SVN

The port of the generic SQL connector to Mylyn 3.0 will be at:

- svn://bugs.industrial-tsi.com/mylyn_gsc/trunk

The generic SQL connector for Mylyn 2.3.x will be at:

- svn://bugs.industrial-tsi.com/mylyn_gsc/branches/mylyn23x


Maarten Meijer
Wim Jongman
Ahmed Aadel


Initial Request: bug 184532
Discussion: bug 223048

Current work is done using Apache Ibatis so that all configuration can be stored in the ibatis xml configuration files.

Legal parameter sets for various fields can then be stored as pseudo queries.

News from the dev team

* 06/21 Restructured the SVN to start porting to Mylyn 3.0
* 06/13 Now ready to create tasks as well, using a property in the db.properties to allow this. Only how to set the properties in the new task editor?
* 06/10 Well I have refactored the code and project structure, so now basically settings for every database are in a Fragment. 

This increases modularity and offers the possibility of adding code fragments for specific databases, adds a speciic home for JDBC drivers, etc.

* 09/08 Port to 3.0 is underway, new preliminary package names: org.eclipse.mylyn.sql.*
* 09/26 New version 0.7.0.v20080926 with extended queryEditor and general UI improvements, download site available

Generic Task Structure to map to any SQL DB

These are mainly the fields from AbstractTask

- repositoryUrl
- taskId
- owner
- summary
- priority (as P1, P2, P3, P4 or P5)
- completed
- completionDate (null is not completed)
- creationDate
- dueDate
- notes

Further more a generic Task can hold:

- a list of TaskComments and
- a list of TaskAttachments.

But how?

Task Context

Since the users of this connector are owners of the database (otherwise they would not be able to access it) it makes sense to create a new table that is keyed by task id and that holds a blob for the task context. In this way the task context can conveniently be dropped in this table. This could also be a setting in the connector.

Generic Query Structure to map to any SQL DB

There will be a Query window allowing (full text) selection on:

- taskId : match a specific ID
- owner : match a known owner from a list
- summary : match a string in a description
- priority (as P1, P2, P3, P4 or P5) : match one or more priorities
- completed : match true or false
- completionDate (null is not completed)  : match before, after and null
- creationDate  : match before, after and null
- dueDate  : match before, after and null
- notes : match a string in a description

and possibly:

- in the list of TaskComments and
- in the list of TaskAttachments.

Configuring the SQL Connector using Ibatis

Create db.properties

then create a file called db.properties with the folowing conent:

# properties for working with iBatis SqlMaps to access the `derby local demo` database
# compulsory
driver=org.apache.derby.jdbc.ClientDriver # class anme of your driver code
# optional, also in repository settings
#url=jdbc:derby:C:\DerbyDatabases\MyDB;create=true # optional url of your DB
user=APP # user name
password=secret # password also in dialog

Create an SqlMapConfig.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE sqlMapConfig
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
    <!-- the proeprties for driver and DB -->
    <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"/>
    <!-- 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="derby_local_demo/TaskMapDerby.xml"/>
    <sqlMap resource="derby_local_demo/RepositoryMapDerby.xml"/>
    <sqlMap resource="derby_local_demo/CommentsMapDerby.xml"/>

Create SQL statements for Repository conaining legal values

Create SQL statements to return legal Owners, legal Products, legal Issue States, legal Priority values for the query lists and combos.

Also add code to validate and initialize the database when relevant. Default is not to allow initialization.

<?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" >
<sqlMap namespace="Repository">
    <sql id="repository">issues</sql>
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 bug_owner FROM <include refid="repository"/> ORDER BY bug_owner</select>
    <!-- return a list of legal products. -->
    <select id="legalProducts" resultClass="string">SELECT DISTINCT product FROM products ORDER BY product</select>
    <!-- return a list of legal issue status values. -->
    <select id="legalIssueStatus" resultClass="string">SELECT DISTINCT status FROM status ORDER BY sort</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 priority FROM priority ORDER BY priority</select>
    <!-- this query will be executed when pressing the Validate Connection in
        the Repository Settings dialog -->
    <statement id="validate" resultClass="integer">
    <!-- 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">
        SELECT count(*) FROM <include refid="repository"/>;
=== Create SQL statements for handling basic Tasks ===
=== Create SQL statements for handling Comments and Attachments ===
<gallery widths="300px">
Image:queryEditor2.png|New Query Edotor with dates
Image:Mylyn_sql_1.png|Full Workbench showing all relevant structures
Image:Dbconfig.png|New Repository Wizard
Image:Taskeditor.png|Rich SQL Task Editor