|
|
Line 1: |
Line 1: |
− | go back to [[/Mylyn/Incubator/Generic_SQL_Connector]]
| |
| | | |
− | == Create db.properties ==
| |
− |
| |
− | THis file contains some basic propertties
| |
− |
| |
− | <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>
| |