|
|
Line 95: |
Line 95: |
| For detailed instructions on configuring a Industrial Generic SQL Conencter see [[Configuring Industrial Connector using Ibatis]] | | For detailed instructions on configuring a Industrial Generic SQL Conencter see [[Configuring Industrial Connector using Ibatis]] |
| | | |
− | === Create db.properties ===
| |
− |
| |
− | then create a file called db.properties with the folowing content:
| |
− | <source lang="text">
| |
− | # 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
| |
− | </source>
| |
− |
| |
− | === Create an SqlMapConfig.xml ===
| |
− |
| |
− | <source lang="xml">
| |
− | <?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>
| |
− | <!-- 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"/>
| |
− | </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="derby_local_demo/TaskMapDerby.xml"/>
| |
− | <sqlMap resource="derby_local_demo/RepositoryMapDerby.xml"/>
| |
− | <sqlMap resource="derby_local_demo/CommentsMapDerby.xml"/>
| |
− | </sqlMapConfig>
| |
− | </source>
| |
− |
| |
− | === 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.
| |
− |
| |
− | <source lang="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" >
| |
− | <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">
| |
− | SELECT COUNT(*) - 3 FROM SYS.SYSTABLES WHERE (TABLENAME = 'ISSUES' OR TABLENAME = 'COMMENTS' OR TABLENAME= 'ATTACHMENTS')
| |
− | </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">
| |
− | SELECT count(*) FROM <include refid="repository"/>;
| |
− | </statement>
| |
− | </sqlMap>
| |
− |
| |
− | </source>
| |
− |
| |
− | === Create SQL statements for handling basic Tasks ===
| |
− |
| |
− | Create similar queries for fetching tasks meeting criteria and individual tasks
| |
− |
| |
− | <source lang="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" >
| |
− | <sqlMap namespace="Tasks">
| |
− | <!-- please not that completed must be set FIRST as it resets the completion date to null -->
| |
− |
| |
− | <select id="getForKey" resultClass="ibatisTask">
| |
− | SELECT
| |
− | bug_ID as taskId,
| |
− | bug_owner AS owner,
| |
− | bug_summary AS summary,
| |
− | bug_priority AS priority,
| |
− | bug_status AS issueStatus,
| |
− | bug_product AS product,
| |
− | bug_notes as notes,
| |
− | bug_created AS creationDate,
| |
− | bug_scheduled AS scheduledForDate,
| |
− | bug_closed AS completionDate,
| |
− | bug_due AS dueDate,
| |
− | bug_time_estimated / 60 as estimatedTimeHours
| |
− | FROM issues WHERE bug_ID=#value#
| |
− | </select>
| |
− |
| |
− | <select id="searchForKey" parameterClass="ibatisCriteria" resultClass="string">
| |
− | SELECT DISTINCT bug_ID as taskId FROM issues
| |
− | <dynamic prepend="WHERE">
| |
− | <isNotEmpty property="owner">
| |
− | <iterate property="owner" conjunction="OR" open="(" close=")" prepend="AND" removeFirstPrepend="true">
| |
− | bug_owner = #owner[]# </iterate>
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="product">
| |
− | <iterate property="product" conjunction="OR" open="(" close=")"
| |
− | prepend="AND" removeFirstPrepend="true">
| |
− | bug_product = #product[]# </iterate>
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="issueStatus">
| |
− | <iterate property="issueStatus" conjunction="OR" open="(" close=")"
| |
− | prepend="AND" removeFirstPrepend="true">
| |
− | bug_status = #issueStatus[]# </iterate>
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="priority">
| |
− | <iterate property="priority" conjunction="OR" open="(" close=")"
| |
− | prepend="AND" removeFirstPrepend="true">
| |
− | bug_priority = #priority[]#</iterate>
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="summary" prepend="AND"
| |
− | removeFirstPrepend="true"> bug_summary LIKE '%$summary$%'
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="creationDateBefore" prepend="AND"
| |
− | removeFirstPrepend="true"> bug_created <= #creationDateBefore#
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="creationDateAfter" prepend="AND"
| |
− | removeFirstPrepend="true"> bug_created >= #creationDateBefore#
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="dueDateBefore" prepend="AND"
| |
− | removeFirstPrepend="true"> bug_due <= #dueDateBefore#
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="dueDateAfter" prepend="AND"
| |
− | removeFirstPrepend="true"> bug_due >= #dueDateBefore#
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="completionDateBefore" prepend="AND"
| |
− | removeFirstPrepend="true"> bug_closed <= #completionDateBefore#
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="completionDateAfter" prepend="AND"
| |
− | removeFirstPrepend="true"> bug_closed >= #completionDateBefore#
| |
− | </isNotEmpty>
| |
− | </dynamic>
| |
− | </select>
| |
− |
| |
− | <select id="additionalForKey" resultClass="java.util.HashMap">
| |
− | SELECT
| |
− | bug_owner AS task_common_user_reporter_name
| |
− | FROM issues
| |
− | WHERE
| |
− | bug_id = #value#
| |
− | </select>
| |
− |
| |
− | <insert id="newTask" parameterClass="ibatisTask">
| |
− | INSERT INTO issues
| |
− | (
| |
− | bug_owner,
| |
− | bug_summary,
| |
− | bug_priority,
| |
− | bug_product,
| |
− | bug_notes,
| |
− | bug_time_estimated
| |
− | ) VALUES (
| |
− | #owner#,
| |
− | #summary#,
| |
− | #priority#,
| |
− | #product#,
| |
− | #notes#,
| |
− | #estimatedTimeHours# * 60
| |
− | )
| |
− | <selectKey
| |
− | resultClass="int">SELECT max(bug_ID) FROM issues
| |
− | </selectKey>
| |
− | </insert>
| |
− |
| |
− | <update id="updateForkey" parameterClass="ibatisTask">
| |
− | UPDATE issues SET
| |
− | <isNotEmpty property="owner">
| |
− | bug_owner = #owner#,
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="summary">
| |
− | bug_summary = #summary#,
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="priority">
| |
− | bug_priority = #priority#,
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="product">
| |
− | bug_product = #product#,
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="issueStatus">
| |
− | bug_status = #issueStatus#,
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="owner">
| |
− | bug_notes = #notes#,
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="creationDate">
| |
− | bug_created = #creationDate#,
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="scheduledForDate">
| |
− | bug_scheduled = #scheduledForDate#,
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="completionDate">
| |
− | bug_closed = #completionDate#,
| |
− | </isNotEmpty>
| |
− | <isNotEmpty property="dueDate">
| |
− | bug_due = #dueDate#,
| |
− | </isNotEmpty>
| |
− | bug_time_estimated = 60 * #estimatedTimeHours#
| |
− | WHERE bug_ID=#taskId#
| |
− | </update>
| |
− | </sqlMap>
| |
− | </source>
| |
− |
| |
− | === Create SQL statements for handling Comments and Attachments ===
| |
− |
| |
− | When starting all of these can be empty statements, ensure you get the tasks listed properly first.
| |
− |
| |
− | <source lang="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" >
| |
− | <sqlMap namespace="Comments">
| |
− | <!--
| |
− | Keys for TaskComment attributes used by Mylyn in the RepositoryTaskData structure
| |
− |
| |
− | COMMENT_NEW = "task.common.comment.new";
| |
− | COMMENT_TEXT = "task.common.comment.text";
| |
− | COMMENT_DATE = "task.common.comment.date";
| |
− | COMMENT_AUTHOR = "task.common.comment.author";
| |
− | COMMENT_AUTHOR_NAME = "task.common.comment.author.name";
| |
− |
| |
− | DESCRIPTION = "task.common.description";
| |
− |
| |
− | TASK_KEY = "task.common.key";
| |
− |
| |
− | Keys for RepositoryAttachment attributes used by Mylyn in the RepositoryTaskData structure
| |
− |
| |
− | ATTACHMENT_ID = "task.common.attachment.id";
| |
− | ATTACHMENT_TYPE = "task.common.attachment.type";
| |
− | ATTACHMENT_CTYPE = "task.common.attachment.ctype";
| |
− | ATTACHMENT_DATE = "task.common.attachment.date";
| |
− | ATTACHMENT_URL = "task.common.attachment.url";
| |
− | ATTACHMENT_FILENAME = "filename";
| |
− | ATTACHMENT_SIZE = "task.common.attachment.size";
| |
− | -->
| |
− | <!--
| |
− | 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
| |
− | cmt_date as groupKey,
| |
− | cmt_bug_id as taskId,
| |
− | cmt_text as text,
| |
− | cmt_author as author,
| |
− | cmt_author_name as author_name,
| |
− | cmt_date as date
| |
− | FROM comments WHERE cmt_bug_id = #value#
| |
− |
| |
− | </select>
| |
− |
| |
− | <insert id="addComment" parameterClass="ibatisComment">
| |
− | INSERT INTO comments (
| |
− | cmt_bug_id,
| |
− | cmt_text,
| |
− | cmt_author,
| |
− | cmt_author_name,
| |
− | cmt_desc
| |
− | )
| |
− | VALUES (
| |
− | #taskId#,
| |
− | #text#,
| |
− | #author#,
| |
− | #authorName#,
| |
− | #description#
| |
− | )
| |
− | </insert>
| |
− |
| |
− | <select id="getAttachmentForKey" resultClass="ibatisAttachment">
| |
− | <!-- meta data only, return the blob data separately -->
| |
− | SELECT
| |
− | att_id as id,
| |
− | att_desc as description,
| |
− | att_ctype as ctype,
| |
− | att_date as date,
| |
− | att_url as url,
| |
− | att_filename as filename,
| |
− | att_size as size,
| |
− | att_task as taskId,
| |
− | att_name as author
| |
− | FROM attachments WHERE att_task = #value#
| |
− | </select>
| |
− |
| |
− |
| |
− | <resultMap id="attachmentDataMap" class="ibatisAttachment">
| |
− | <result property="blob" column="att_blob" jdbcType="BLOB" javaType="[B"/>
| |
− | </resultMap>
| |
− |
| |
− | <select id="getAttachmentDataForKey" resultMap="attachmentDataMap">
| |
− | <!-- return the blob data -->
| |
− | SELECT
| |
− | att_blob
| |
− | FROM attachments WHERE att_id = #value:INTEGER#
| |
− | </select>
| |
− |
| |
− | <insert id="addAttachment" parameterClass="ibatisAttachment">
| |
− | INSERT INTO attachments (
| |
− | att_desc,
| |
− | att_ctype,
| |
− | att_date,
| |
− | att_url,
| |
− | att_filename,
| |
− | att_size,
| |
− | att_task,
| |
− | att_blob,
| |
− | att_name
| |
− | ) VALUES (
| |
− | #description#,
| |
− | #ctype#,
| |
− | #date:DATETIME#,
| |
− | #url#,
| |
− | #filename#,
| |
− | #size:INTEGER#,
| |
− | #taskId#,
| |
− | #blob:BLOB#,
| |
− | #author#
| |
− | )
| |
− | <selectKey>
| |
− | SELECT max(att_id) FROM attachments
| |
− | </selectKey>
| |
− | </insert>
| |
− |
| |
− | </sqlMap></source>
| |
| | | |
| ==Screenshots== | | ==Screenshots== |
This is a temp doc space for the generic SQL connector for mylyn.
Legal parameter sets for various fields can then be stored as pseudo queries.
This increases modularity and offers the possibility of adding code fragments for specific databases, adds a speciic home for JDBC drivers, etc.
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.