Community
Participate
Working Groups
Here is a suggestion for a future Mylar connection. Since most task repositories are in a relational database it does not make sense that we should build each connector from scratch. Instead there should be a a generic connector just as there is a generic web connector. The base task building blocks (tables) are: Tasks, contains master data (id, prio, moddate, plandate, assigned to, etc..) Task Details, contains at least one single block of comments Can be extened by the following tables Attachements, containing (a reference to) attachments People data, contains people info In addition to that we need methods that operate on the data. These methods could either update a single field in the database or implement some clever interface. These map to the actions that can be found in bugzilla (close, etc) I see a wizard that is able to connect to a database, show the table and fields that map to map Mylar fields. The base could be defined in extension points so that zero programming is required to create a new connector.
I like this idea and it would be a nice complement to our generic web-based connector. But notably it could be a full rich connector since it would interact with structured data.
Please note that unlike generic connector the use of this one is only limited to the internal issue trackers and only those that do expose their db to the world. I haven't seen such installs.
I have, exactly the ones you list: internal issue trackers. Agreed that there is no use case for hosted and OSS issue trackers, but I've heard of a few companies using db-based issue trackers. Wim: I assume that this is your experience too?
Yes, behind every issue tracker there is a database. In case the database is exposed, creating a generic SQL connector could yield a Mylar connector in minutes. The exposure does not need to be to the world, only to the ones that are interested. A company could use this to create a connector to a propriarty issue tracker. I see two ways of approaching this. The first one is a generic connector that stores information about the structure of the database and maps to Mylar requests on the fly. The second one is a software factory: A wizard that is told about the structure of the database and the mapping to Mylar and then creates a connector plugin which can be tailored.
Is there a simpler way to do the first pass, e.g. make the generic connector and have it rely on a mapping that's in a configuration file specified per-repository?
When working with _existing_ databases I found the quickest and most flexible way to get from SQL result sets to POJO and vice versa is to use iBatis. See http://ibatis.apache.org/ configuring it would then be a question of writing a dedicated SQLMap for the various resultsets needed. But then: what is the generic structure of an internal issue tracker?
(In reply to comment #6) > But then: what is the generic structure of an internal issue tracker? There really isn't one, so a mapping between fields and our common task schema (encapsulated by the RepositoryTask class and mapped to by AbstractAttributeFactory and related mechanisms) would still need to be provided.
(In reply to comment #4) > Yes, behind every issue tracker there is a database. In case the database is > exposed, creating a generic SQL connector could yield a Mylar connector in > minutes. The exposure does not need to be to the world, only to the ones that > are interested. A company could use this to create a connector to a propriarty > issue tracker. I'm looking at this currently (at the request of Wim) with the configuration to be implemented as an ibatis (http://ibatis.apache.org/ ) config file. This can map result sets from many different databases and containing joins etc in SQL to a generic java object. My current question is one of authorization. With these internal issue trackers, does every individual user have access to the database or would they share the same database username/password? With a web/xml/soap interface in between the user is authenticated and authorized in the front app and and the front app is authorized to talk to the database. Where would/should/does authentication/authorization take place in such a set up?
(In reply to comment #8) > My current question is one of authorization. With these internal issue > trackers, does every individual user have access to the database or would they > share the same database username/password? Hello Maarten, I think this would be handled just as a normal connector, store the name and password in a task repository. Normally each user must have its own name and password.
That sounds right to me. Then those credentials, stored in the same way as they are for the WS-based repositories, can be used for authentication with the straight DB connection. I wonder if it makes sense to consider leveraging something like DTP (http://www.eclipse.org/datatools/ ) for the database connections? Not sure since I haven't looked at that project for a while and since this wouldn't need any real UI beyond the AbstractRepositoryConnectorUi.
Hi Mik, Ibatis can declaratively create Java objects directly from an SQL result set mapping a local issue tracker to a Mylyn task object. For this I need to have a no argument constructor defined for AbstractTask and AbstractTaskContainer because of java inheritance rules. How can I request such an API addition? I think it's best to keep it in my local source and upload it tiogether with the DB connector as a whole. I'm marking it @since 3.0 or are there different tags for this?
Maarten: please do create a bug tagged [api] for Mylyn for this use case, because we need to make sure to consider it for the Mylyn 3.0 APIs changes.
(In reply to comment #10) > That sounds right to me. Then those credentials, stored in the same way as they > are for the WS-based repositories, can be used for authentication with the > straight DB connection. I need to access the credentials (username/password) from my core plugin and I only have the repository url, how do I do that?
The list of repositories is managed in Tasks UI. Usually core methods take a TaskRepository object as a parameter and retrieve credentials from the object. Is it feasible for you to pass that instead of the URL?
I'm tracking progress on this ticket with a view to using it with PVCS Tracker with a SQL Server back-end, having been directed here from #210822. If anyone has any details of the PVCS Tracker schema then it'd be much appreciated, but I'm quite happy to provide whatever details I figure out as I go.
Preliminary docs are being posted on: http://wiki.eclipse.org/Mylyn/Generic_SQL_Connector
Created attachment 92479 [details] change to org.eclipse.mylyn.tasks.core.AbstractTask needed to get this connector to work Ibatis requires setters and getters on all fields that it must CRUD from and to the database. This one is missing.
Created attachment 92480 [details] mylyn/context/zip
Maarten, can you try to create the setter in the concrete class that extends AbstractTask?
(In reply to comment #19) > Maarten, can you try to create the setter in the concrete class that extends > AbstractTask? > No, I tried that, but taskId is private :-(
Opened a separate bug for discussing generic task structure: bug 223048: [discussion] determine (lowest+) common structure of (internal) issue trackers https://bugs.eclipse.org/bugs/show_bug.cgi?id=223048 Please join in that discussion!
MIk, we're making real progress here an intend to contribute this to the base Mylyn install. So how do we do that: licensing, ibatis into Orbit, into mylyn incubation, update sites, documentation, etc. If you want it in base mylyn can you assign to me? Otherwise we need to set up an update site on www.industrialtsi.com an depend on Mylyn.
(In reply to comment #19) > Maarten, can you try to create the setter in the concrete class that extends > AbstractTask? Well I removed the need for the patch for now by making taskID a read only property.
fyi, Mik has been offline all this week Maarten so you can expect a response later next week.
I have just added details to bug #210822 about PVCS Tracker database structure. Note that the user credentials used to sign into PVCS Tracker using the client are separate from the credentials used to connect to the DB, which are provided during server definition.
Please assign to me. We're making real progress! See http://wiki.eclipse.org/Mylyn/Generic_SQL_Connector for details.
Maarten: I've moved the wiki page into the Mylyn/Incubator space: http://wiki.eclipse.org/Mylyn/Incubator/Generic_SQL_Connector We are about to submit the incubator project proposal (bug 214182). Do you (and Wim?) want to be added as interested parties? If so please comment on the bug stating your names and affiliations.
Refactored code so that each DB can have its settings in an Eclipse Fragment project... nice :-) I have also added to possibility of adding new tasks (when allowed by user) And finally created a version using the Derby database engine allowing the store and retrieval of context. Goodbye local task repository with all its limitations... I invite all CC: to try this out and give feedback before I start porting to 3.0
> I invite all CC: to try this out and give feedback before I start porting to > 3.0 I just tested the Derby demo. Nice work! I'm looking for Redmine Mylyn connector. This could be a possibility, although there will be some limitations, because Redmine doesn't store attachments as blob and has a done_ratio field instead of a simple boolean completed field. So I might just wait for the Redmine REST API... When do you think porting to the new Mylyn version will be ready?
I have commenced porting to Mylyn 3.0 API. Code is located at the repository svn://bugs.industrial-tsi.com/mylyn_gsc/trunk I'm working with new package names in view of the anticipated contribution of this connector to the Mylyn codebase under EPL by Industrial TSI. The plugins are now named: - org.eclipse.mylyn.sql.core - org.eclipse.mylyn.sql.test - org.eclipse.mylyn.sql.ui One still needs the ibatis database library in plugin - org.apache.ibatis - org.apache.ibatis.feature There is a demo/test implementation based on Derby in a fragment project - org.eclipse.mylyn.sql.demo.derby This project includes the necessary SQL to setup a local trial Derby DB I will talk to Wim of Industrial TSI to provide facilities for an update site, to make thing easier. I have change the package names of the DTO objects so if you have already configure some SQL using the 2.3 version, you only have to change the class aliases in SqlMapConfig.xml and then the queries should work with the new version as well. Please note that stuff is not final yet, for example Attachments do not yet work, but connection and form based simple query, and rich task editor does.
Created attachment 112510 [details] team project set for generic Sql Conenctor for Mylyn 3.0
Since today, new tasks and attachments are also supported in the new version. Steffen, you are assigned as my contributor mentor, so can you have a look at this and see whether it is about ready for incubator status?
We have created an update site at http://bugs.industrial-tsi.com/mylyndb/ to try this connector for 3.0.x and 2.3.x. Thanks Wim!
Any chance of a 3.4 version?
(In reply to comment #34) > Any chance of a 3.4 version? > I thought it would be clear that the 3.0.x version is for Eclipse 3.4! Steps to retrieve: - add an update site: http://bugs.industrial-tsi.com/mylyndb/ - select two features: -- org.eclipse.mylyn.sql.feature_0.7.1.v20080926 -- org.apache.ibatis.feature_2.3.0 If you have worked with the previous version, you must chage a the Ibatis task aliases and add a few extra queries, look at the derby connector for specifics. You need to create the configuration files for your database yourself, using the derby connector as an example. For information on the configuration files, I must refer you to http://ibatis.apache.org/ I intend to create a set of instructions for configuring on the wiki in the next few weeks.
> I thought it would be clear that the 3.0.x version is for Eclipse 3.4! My apologies, I'm an idiot (I had an unrelated issue with my Mylyn version, which prevented me from installing the connector, and I thought at first that it was a problem with the connector, not my Mylyn).
We have started refactoring the current connector. Basically we are no longer dependent on Ibatis and transformed it into a generic industrial connector. We now have a facade interface to any persistence layer and have created an adapter from the current ibatis connector. The existing stuff will still work meaning we have an ibatised Derby connector but have layered this a bit so that everybody can implement their own data access if they want. The existing parts are tagged as M1 and the trunk contains the new code which is still under active development which we will update daily. The new stuff can be anonymously reached through svn://bugs.industrial-tsi.com/mylyn_gsc/ No update site yet
Hi Steffen, we're getting ready to contribute this connector. How to proceed from here on? I can't submit a patch as there is nothing to patch yet, can you check out from SVN and give it a spin?
Great to know that Maarten. It would be best if you joined a weekly call and put this on the agenda to discuss how to proceed further.
Hi Steffen, we are now doing clean up in preparation for submission - we have Mylyn code formatting rules in place - I have installed Eclipse API tools to check against Mylyn 3.0.0 API as minimum - I have created a shared CheckStyle profile in project org.eclipse.mylyn.industrial to get a code quality baseline - I'm creating unit test for parts to use in combination with Eclemma to get to about 85% coverage from the unit tests I'm moving the Derby implementation from an 'embedded' repository to a shared 'network' one What more do I need to do prior to submission?
That sounds good Maarten. I think it's most important that we discuss the benefit for the Mylyn community provided by the Generic SQL connector and future maintenance requirements before proceeding with a more detailed code review.
(In reply to comment #35) > I intend to create a set of instructions for configuring on the wiki in the > next few weeks. > Someone notified me that this was ready to try -- did these instructions materialise? Cheers, JT
(In reply to comment #42) > Someone notified me that this was ready to try -- did these instructions > materialise? Thanks for reminding me! I made a start at http://wiki.eclipse.org/Mylyn/Incubator/Generic_SQL_Connector/Configuring_Industrial_Connector_using_Ibatis
I have created an example of how you can create a Mylyn connector in 5 minutes. This connector should only use the core and the ui projects but for some reason there is still Ibatis stuff in the core project. Maarten, can you elaborate on this? The documentation of the 5 minute Mylyn connector can be found here: http://wiki.eclipse.org/Mylyn/Incubator/Generic_SQL_Connector/Configuring_Industrial_Connector_using_Nothing There are two other demo projects using IBatis and JPA. Details are here: http://wiki.eclipse.org/Mylyn/Incubator/Generic_SQL_Connector/ We are now working an a USENET connector using this Industrial connector as a base.
Wim: I noticed that the page name is now http://wiki.eclipse.org/Mylyn/Incubator/Generic_Industrial_Connector This name sounds a bit odd to me, because our other connectors, unrelated to this one, can be considered "Industrial". I'd like to make sure that all the connectors have a name that uniquely identifies them. I assume "SQL" was too restrictive which is why you changed it? How about "Generic Database Connector"?
(In reply to comment #45) The name "Generic Industrial Connector" was chosen to be in line with the "Generic Web Connector". Meaning that "if there is no offical connector for xyz, try this one". Only we do not focus on web accessible task repositories but on "legacy" task repositories. Do you think the name Industrial is to strong (as in "Industrial Strength") and might "overclass" the other connectors? For this reason we have added Generic to the name and the name shall never be "Industrial Connector" but always "Generic Industrial Connector". What would also be a name is "Generic Legacy Connector" but with this name we think nobody will look at it, after all, who wants to be legacy. "Generic Database Connector" was also considered but we found that to restrictive too. For example, we are in the process of creating a USENET connector based on this one. We envision a great life for this connector that could give Mylyn another useability boost for the thousands of "industries" that could turn their terminal based legacy task repsitory into a great GUI app in just a rainy afternoon. If this would happen then the name fits perfectly. You can see in the amount of words I have used for this reply that I really want to hang on to this name. I hope you will let us have it but if you think it is not in line with Mylyn's naming conventions than we will change it to something else.
Stupid question: why not just "Generic Repository Connector" (or "Generic Mylyn Repository Connector")? Then name describes exactly what it is, while still allowing things like a Usenet-based system, and the "Generic" part discourages people from using it if they have a non-generic connector that they can use instead. Just a thought.
I agree with all the reasoning above. The problem is that we deliberately moved away from the name "Generic Web Connector" because it was misleading for similar reasons, i.e., it overlapped with existing connectors. We change the name to the "Web Templates" connector. Not great, but better. Wim: What I think would work well is if we could review the role and function of the connector during an upcoming Mylyn call. Is it possible for you to join us one of these Thursdays at 10am Pacific time?
The Ibatis stuff makes it function as a sort of "SQL Templates" Connector and that as I envisioned it at the start: add the specific SQL in the form of Ibatis SqlMaps and a driver and you're in business. The separate Persistor and more abstract interface as suggested and implemented by Wim/Ahmed have given it an even bigger scope, allowing use of JPA or connecting to NNTP or whatever can be accessed by some java code. It is all about filling the fields in a generalized Task object using whatever means needed, to get a connector up and running quickly. This hopefully will effectively lower the barrier to start using Mylyn and reaping it's benefits. I think we are the victims from self-inflicted scope creep here. My name proposal "xxL Template Connector", we need to add a new connector wizard code template then to the extension point...xxL then stands for SQL, XML, anyL...
Well I'm back again. I've had a little time the last couple of days to start looking into using this for PVCS Tracker with Eclipse 3.5. It took me a little while to get my head around things, like how to get it to connect to MS SQL Server 2000, but I've made some progress. I have the following plugins installed: org.eclipse.mylyn.industrial.core org.eclipse.mylyn.industrial.demo.derby org.eclipse.mylyn.industrial.tests org.eclipse.mylyn.industrial.ui These are all version 0.8.1.v20090112. Interestingly I don't seem to have any Industrial features. I haven't been particularly good about documenting all my steps, but I seem to recall that whenI installed features via the update site, something didn't work (I couldn't see the Industrial repository type) and I couldn't open some of the jar files to configure my new connector. So I downloaded the ZIP file and dropped things in the right places (which, having read further I now realise probably wasn't the best idea). I have the following exploded in the dropins directory: org.eclipse.mylyn.industrial.demo.derby_0.8.1.v20090112 I have added a new repository type in connector.xml within that directory and set what seem like reasonable properties to get me started with minimal functionality: <repository name="PVCS Tracker"> <persistor name="PVCS Tracker Persistor"> <class>org.eclipse.mylyn.industrial.core.persistence.IbatisPersistor</class> </persistor> <repository-properties> <property name="can-create-new-task" value="false" /> <property name="can-create-task-from-key" value="false" /> <property name="can-query-repository" value="true" /> <property name="can-synchronize-tasks" value="false" /> <property name="can-get-attachments" value="true" /> <property name="can-post-attachments" value="false" /> </repository-properties> <task> <task-attributes> <!-- You can disable a task attribute shown on the editor by setting its read-only value to true. You may be willing to configure only what you need and let the rest to their default value, which is false --> <attribute id="task.common.product" readonly="true" /> <attribute id="task.common.status" readonly="true" /> <attribute id="task.common.user.assigned" readonly="true" /> <attribute id="task.common.date.due" readonly="true" /> <attribute id="task.common.date.created" readonly="true" /> <attribute id="task.common.date.modified" readonly="true" /> <attribute id="task.common.summary" readonly="true" /> <attribute id="task.common.priority" readonly="true" /> <attribute id="task.common.description" readonly="true" /> </task-attributes> </task> </repository> I then followed the instructions at http://wiki.eclipse.org/Mylyn/Incubator/Generic_Industrial_Connector/Configuring_Industrial_Connector_using_Ibatis with some trial and error, some learning about Eclipse plugins and fragments and classpath handling, and I'm now about to add a PVCS Tracker repository and validate it. Great! I've then come to try and create a new query against this repository and come unstuck. Previously I was getting a NPE, but I'm currently unable to reproduce that. Now I'm seeing a problem converting creationDate to the right value, but I'll keep tweaking the query to resolve that. That's about all I have for now, but I just wanted to report some progress. Once I get it displaying task lists, I'll start looking at comments and attachments and then possibly write access to the repository. I'll try and report back as I move along.
Well that didn't take too long! With tweaks to the date fields in the query and definitions of the queries to retrieve comments, attachment metadata and attachment content I now have a query that returns all tickets in my repository! Things that (at least appear to) work: - Basic fields. Title, Status, Date created, Priority, Product, Description. I don't think I set up Date modified and Date due. - Completed flag. Not quite sure how it determines this. - Attachment list. Filename, Creator (sort of) and Created. I don't think we have anything to map to Description and I haven't figured out how to get the size yet, so they're returning NULL presently. - Comments. User, date and comment text (concatenated from title and main text). Clicking reply quotes the text, but prepends "null" for some reason. Things that don't work yet: - Filtering. I specified a single Product and a few Statuses in the Form Based Query but all records are currently returned. This may be to do with me not having defined a required query somewhere. - Attachment content. I can see the list of attachments, but trying to open one just opens an empty browser tab at about:blank. This may be to do with me returning NULL as the URL and type because I really don't know what to put in there! - Write access. Because I'm not that brave yet!
Great work James!! This sounds good. For the filtering, there is the "searcForKey" query in the TaskMap.xml. I would be carefull to write because that could invalidate the version system unless you know exactly what files to write. It is maybe not very difficult and could only involve writing of the PVCS task file. However, make sure before you try. I am waiting for new updates.
Yeah, I went back and looked at my TaskMapPVCS.xml file and I've commented out the big scary dynamic query - that's why filtering isn't working. I've tried using DATALENGTH(trkfile.fileCont) to get the attachment size, but I'm still seeing file size as zero. fileCont is a SQL Server IMAGE column. I'm also now looking at how to get the file content - I suspect there's going to be some digging around in the Ibatis docs for that. This is all totally tied to our implementation at the moment and in fact I'm not sure if there's a way to untie it due to the way PVCS Tracker is so customisable and the way it achieves that customisability. But it's all looking extremely positive so far. Great work!
(In reply to comment #51) > Things that (at least appear to) work: > - Basic fields. Title, Status, Date created, Priority, Product, Description. > I don't think I set up Date modified and Date due. > - Completed flag. Not quite sure how it determines this. That is Completed Date not being null > - Attachment list. Filename, Creator (sort of) and Created. I don't think > we have anything to map to Description and I haven't figured out how to get the > size yet, so they're returning NULL presently. Size is informational only > - Comments. User, date and comment text (concatenated from title and main > text). Clicking reply quotes the text, but prepends "null" for some reason. > > Things that don't work yet: > > - Filtering. I specified a single Product and a few Statuses in the Form > Based Query but all records are currently returned. This may be to do with me > not having defined a required query somewhere. You need queries to return legal values of these fields SELECT DISTINCT field FROM table; > - Attachment content. I can see the list of attachments, but trying to open > one just opens an empty browser tab at about:blank. This may be to do with me > returning NULL as the URL and type because I really don't know what to put in > there! If there is no legal URL there should be a Make Local Copy and Open in Browser popup menu item that downloads BLOB from DB and open local copy, Igf you can download with a regular URL you should do that instead. > - Write access. Because I'm not that brave yet! Fix everything else before you do write access.
(In reply to comment #54) > > Things that don't work yet: > > > > - Filtering. I specified a single Product and a few Statuses in the Form > > Based Query but all records are currently returned. This may be to do with me > > not having defined a required query somewhere. > You need queries to return legal values of these fields SELECT DISTINCT field > FROM table; Yes, I still need to work on this. I've cut out a huge conditional statement from the SQLMap that I need to work out. > > - Attachment content. I can see the list of attachments, but trying to open > > one just opens an empty browser tab at about:blank. This may be to do with me > > returning NULL as the URL and type because I really don't know what to put in > > there! > If there is no legal URL there should be a Make Local Copy and Open in Browser > popup menu item that downloads BLOB from DB and open local copy, Igf you can > download with a regular URL you should do that instead. I've been trying to use that feature but getting an "Unsupported data conversion" exception. Having a read around, some have suggested that the ResultSet.getBlob() method may not be implemented (and may be returning a misleading error message) in the MS SQL Server JDBC Driver. The page at http://msdn.microsoft.com/en-us/library/ms378813.aspx seems to suggest that the LONGVARBINARY JDBC type might work, although that is for SQL Server 2008 - I haven't yet found an equivalent page for SQL Server 2000. I did, however, try changing the SQLMap and got the same error. com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in pvcs_tracker/CommentsMapPVCS.xml. --- The error occurred while applying a result map. --- Check the Comments.attachmentDataMap. --- Check the result mapping for the 'blob' property. --- Cause: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unsupported data conversion. at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:566) at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:114) at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:91) at org.eclipse.mylyn.industrial.core.persistence.IbatisPersistor.fetchAttachmentBlob(IbatisPersistor.java:116) at org.eclipse.mylyn.industrial.core.internal.IndustrialAttachmentHandler.getContent(IndustrialAttachmentHandler.java:73) at org.eclipse.mylyn.internal.tasks.ui.util.AttachmentUtil.downloadAttachment(AttachmentUtil.java:254) at org.eclipse.mylyn.internal.tasks.ui.util.DownloadAttachmentJob.run(DownloadAttachmentJob.java:50) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55) Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unsupported data conversion. at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source) at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source) at com.microsoft.jdbc.base.BaseData.unsupportedConversion(Unknown Source) at com.microsoft.jdbc.base.BaseData.getBlob(Unknown Source) at com.microsoft.jdbc.base.BaseResultSet.getBlob(Unknown Source) at com.microsoft.jdbc.base.BaseResultSet.getBlob(Unknown Source) at com.ibatis.sqlmap.engine.type.ResultGetterImpl.getBlob(ResultGetterImpl.java:76) at com.ibatis.sqlmap.engine.type.BlobTypeHandlerCallback.getResult(BlobTypeHandlerCallback.java:29) at com.ibatis.sqlmap.engine.type.CustomTypeHandler.getResult(CustomTypeHandler.java:52) at com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getPrimitiveResultMappingValue(BasicResultMap.java:611) at com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getResults(BasicResultMap.java:344) at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.java:381) at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(SqlExecutor.java:301) at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:190) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173) ... 9 more This is what I have at the moment: <resultMap id="attachmentDataMap" class="ibatisAttachment"> <result property="blob" column="fileCont" jdbcType="LONGVARBINARY" javaType="[B"/> </resultMap> <select id="getAttachmentDataForKey" resultMap="attachmentDataMap"> <!-- return the blob data --> SELECT fileCont FROM trkfile WHERE fileId = #value:INTEGER# </select> > > - Write access. Because I'm not that brave yet! > Fix everything else before you do write access. Well, yes. And then I think I still have to figure out a couple of bits and pieces, because I'm fairly sure some counters and references are updated on inserts and updates that I don't currently take any notice of. Thanks for the help and advice so far.
(In reply to comment #55) > > > - Attachment content. I can see the list of attachments, but trying to open > > > one just opens an empty browser tab at about:blank. This may be to do with me > > > returning NULL as the URL and type because I really don't know what to put in > > > there! > > If there is no legal URL there should be a Make Local Copy and Open in Browser > > popup menu item that downloads BLOB from DB and open local copy, Igf you can > > download with a regular URL you should do that instead. > > I've been trying to use that feature but getting an "Unsupported data > conversion" exception. Having a read around, some have suggested that the > ResultSet.getBlob() method may not be implemented (and may be returning a > misleading error message) in the MS SQL Server JDBC Driver. Resolved, by using the jTDS (http://jtds.sourceforge.net/) driver instead of the default Microsoft one - this also reports the correct file size. So all that remains (unless it turns out I can produce a URL for attachments) is to get filtering working and then look at write access.
It looks like I finally have filtering working for all fields we have data for, so the read access to the repository is pretty much complete. I just have a couple of questions at this stage: - Is there a way to completely remove fields from the UI? We don't have a Due Date in our repository, so it'd be nice not to have it presented in the search dialogue and task details. - Is there a way to specify repository access credentials that are separate from the DB credentials? All users use the same DB user to connect to the PVCS Tracker repository, but also have a PVCS Tracker username and password which I'm currently not using in Mylyn. - Is there a way to "clean" the data returned by the DB before attempting to transform it to XML? Some of the notes in the tickets have characters that are not valid in XML and although at the moment I'm explicitly filtering out the known bad characters, I'd prefer a generic solution that strips out (or somehow escapes) anything that's not valid in an XML document. I guess this is a SQL/iBatis thing really. Other than those queries, it all looks very promising. I'm going to use it alongside the standard Tracker interface for a while and see how it goes. I've just about used all the time I can on this for the moment, so I'll look into write access to the repository in the (hopefully not-too-distant) future.
Hi James, Seems like you had fun. We will look into these questions. Is it possible to attach your project so that we can add it to the wiki as an example?
(In reply to comment #58) > Seems like you had fun. We will look into these questions. Is it possible to > attach your project so that we can add it to the wiki as an example? It's been great fun and I really appreciate the work that went into creating the connector. I may be able to attach some details, but I'll have to carefully vet what's in there before I do so. I also might be able to contribute source code patches, but I cannot access external SVN repositories from work because our proxy server doesn't support the required methods. Unfortunately this is beyond my control and unlikely to change. Are there zip files available?
(In reply to comment #57) > - Is there a way to completely remove fields from the UI? We don't have a Due > Date in our repository, so it'd be nice not to have it presented in the search > dialogue and task details. I think this is a nice to have, to be done together with the request for extra fields to show. > - Is there a way to specify repository access credentials that are separate > from the DB credentials? All users use the same DB user to connect to the PVCS > Tracker repository, but also have a PVCS Tracker username and password which I'm > currently not using in Mylyn. My current thoughts on this are to separate the DB user ID into the configuration (and only in the configiration) and have the user name from the UI function as the repository user name. > - Is there a way to "clean" the data returned by the DB before attempting to > transform it to XML? Some of the notes in the tickets have characters that are > not valid in XML and although at the moment I'm explicitly filtering out the > known bad characters, I'd prefer a generic solution that strips out (or somehow > escapes) anything that's not valid in an XML document. I guess this is a > SQL/iBatis thing really. I suggest looking into ibatis TypeHandlerCallBack to clean up database produced content, examples are at: http://ibatis.apache.org/docs/java/user/com/ibatis/sqlmap/client/extensions/TypeHandlerCallback.html
(In reply to comment #59) > It's been great fun and I really appreciate the work that went into creating the > connector. I may be able to attach some details, but I'll have to carefully vet > what's in there before I do so. Great to hear. Kudos Maarten and Wim.
(In reply to comment #60) >> - Is there a way to specify repository access credentials that are separate >> from the DB credentials? All users use the same DB user to connect to the PVCS >> Tracker repository, but also have a PVCS Tracker username and password which I'm >> currently not using in Mylyn. > My current thoughts on this are to separate the DB user ID into the > configuration (and only in the configiration) and have the user name from the UI > function as the repository user name. In testing this with Eventum, bug#150174 I also found this necessary. So I'm preparing for Release 1.0.0: The Repository Settings Dialog should contain only the User credentials that will be used for Access Control, Creating new Bugs, Adding Comments, Adding Repositories. In order to implement Access Control for users, would it be sufficient to add a method to the IPersistor interface/PersistorAdapter class like boolean isAuthenticated(TaskRespository repository)? The code can retrieve the user credentials from the repository. Also the user and password can be referenced in the SqlMaps files as ${user} and ${password} properties, as these are passed into Ibatis in TasksSqlMapConfig.java so the can be used directly in a query. The Database Access credentials shall be moved into the the extension point and fixed, allowing easy distribution internally in companies for access to DB and will not be User editable. Am I missing anything here?
Reviewed code and found that some srepository setting were unsupported: - can-query-repository always returned true, now listens to setting when false, legal property values are no longer queried for, easier when starting up - can-synchronize-tasks always returned true, now listens to setting - can-delete-tasks (Mylyn since 3.3) was unsupported, now listens to setting I also added two more query fileds in the Fom Based Query wizard: "Description" and "Comments", so you can now search these as well when you can come up with the right query. All of this is updated, committed to SVN and a new version put on the update site 0.9.4.v20090917
> The Database Access credentials shall be moved into the the extension point and > fixed, allowing easy distribution internally in companies for access to DB and > will not be User editable. > > Am I missing anything here? Do you mean adding name/pw into the extension point XML? That is not a good solution. Everybody has access this information and what happens if the credentials change. How about providing a callback class on this spot instead of the raw credentials. For 1.0 we previously decided to remove the IBATIS dependency out of the core. Is this still the plan?
(In reply to comment #64) > Do you mean adding name/pw into the extension point XML? That is not a good > solution. Everybody has access this information and what happens if the > credentials change. How about providing a callback class on this spot instead > of the raw credentials. Good point, will think of alternative solution. Most connectors will start out as read-only connectors, so an extra DB username/password can be created with RO access rights. For write access however, the DB username/password MUST be present in the client connector at some point. Maybe store in Secure Storage instead of plain text in repository properties. > For 1.0 we previously decided to remove the IBATIS dependency out of the core. > Is this still the plan? Ibatis dependency is already marked optional at runtime, so if not used by fragment, the Ibatis plug-in is not required or loaded. Ibatis needs Eclipse-Buddy access to classes in core and fragments as well to get the JDBC driver and sqlMaps, so removal is either impossible or non-trivial. Postpone for 2.0
Hey. Great Work. My aim is to set up a task management solution for a small developer team. Based on the derby demo I implemented a mapping to a MS-SQL server database. I also created a db schema like the one suggested in the demo. I get everything to work with eclipse, but I discovered some drawbacks and I don't know if it is my fault or just due to the beta state of the connector. Here are my problems: - bug_due is not set, when a new task is created - it seems that the content assist for the user assignment does not get its values from the shared DB - when replying to a comment the string "null" is added in front of the quoted text - org.eclipse.mylyn.industrial.core.dto.IndustrialTask.modificationDate isn't used at all (Is there a reason why?) Here is the version information of the used software: Eclipse 3.5.0 Mylyn 3.2.0 Industrial Connector 0.9.4 MS-SQL Server 2005 If it is needed, I can also post the db setup script and the configuration files for the mapping.
(In reply to comment #66) > Hey. Great Work. Thanks! Have you read the eventum example at http://eclipsophy.blogspot.com/ ? > My aim is to set up a task management solution for a small developer team. > Based on the derby demo I implemented a mapping to a MS-SQL server database. I > also created a db schema like the one suggested in the demo. I get everything > to work with eclipse, but I discovered some drawbacks and I don't know if it is > my fault or just due to the beta state of the connector. > > Here are my problems: > > - bug_due is not set, when a new task is created > - it seems that the content assist for the user assignment does not get its > values from the shared DB > - when replying to a comment the string "null" is added in front of the quoted > text > - org.eclipse.mylyn.industrial.core.dto.IndustrialTask.modificationDate isn't > used at all (Is there a reason why?) > > Here is the version information of the used software: > Eclipse 3.5.0 > Mylyn 3.2.0 > Industrial Connector 0.9.4 > MS-SQL Server 2005 > > If it is needed, I can also post the db setup script and the configuration > files for the mapping. Please open a new bug with this request, a [sql] prefix before the summary and the map files/db script attached. I will then accept the bug and help you to get stuff working a.s.a.p.
release 0.9.5 - fixed the reporter name appearing in demo derby - solved comment prefix == null problem, this is framework returning null instead of empty string :-( - added support for delete task operations using query Tasks.deleteForkey - removed some obsolete methods - code cleanup & preparing for i18n
The Mylyn project has grown tremendously and taken on new directions since this bug was opened in 2007 with the foundation of Tasktop and the release of many commercial connectors. As Mik has pointed out elsewhere, the Mylyn dev team does not have the resources to maintain the many contributions offered, one of which was this SQL connector. This is the only way for the Mylyn project to stay in innovation mode, stay out of maintenance mode! We understand and respect that decision as we too will benefit from that innovation. The Industrial SQL Connector for Mylyn does have an active though not very visible user community as it is the basis of the mylyn connector of the commercial software change management product of remain software: http://remainsoftware.com/solutions/software-change-management As can be seen from the entries above also some people have used the Industrial SQL Connector to create connections to their in-house SQL based systems. For this reason we have decided to take the opportunity to join the newly formed Eclipselabs with code hosting at google code, and no longer pursue becoming part of mylyn core.. There we will have our own dedicated Wiki, issue tracker, SVN repository and update site, so we no longer have to free ride on the eclipse.org infrastucture. As a consequence the projects and packages will be renamed to get rid of the org.eclipse.mylyn prefix and revert back to com.industrialtsi.mylyn, but will keep the EPL. The new location for the project and source code is here: http://code.google.com/a/eclipselabs.org/p/industrial-mylyn/ We will be updating the Wiki and blog in the days to come,a and provide an update site for the revised code.
Unable to install "Industrial Connector for MyLyn": I was unsuccessful installing the connector on a Kepler Eclipse: - Downloaded the Kepler for Java EE Developers eclipse-jee-kepler-R-win32-x86_64.zip - Started with a fresh workspace - Selected the Industrial SQL Connector for Mylyn and clicked on Install, leaving all 4 components checked - 30 seconds later I get the message that the connect itself won't be installed because of: Cannot complete the install because one or more required items could not be found. Software being installed: Industrial Connector for Mylyn 0.9.9 (com.industrialtsi.mylyn.feature.feature.group 0.9.9) Missing requirement: Industrial Connector for Mylyn 0.9.9 (com.industrialtsi.mylyn.feature.feature.group 0.9.9) requires 'org.eclipse.mylyn.tasks.ui [3.8.0,3.9.0)' but it could not be found Any advice would be highly welcome.
(In reply to comment #70) Did you install Mylyn?
(In reply to comment #71) > (In reply to comment #70) > > Did you install Mylyn? Scratch that. I think the repo that you point to needs to be updated. Can't you check out the sources and tweak it a little bit? https://code.google.com/a/eclipselabs.org/p/industrial-mylyn/source/browse
Hi Wim, Thanks for your answer. I'd be very happy to work with the source. Could you please point me to a document that explains how to run and debug the code as part of Eclipse? I used to work as a Java Architect in 1998, but I'm quite a newbee on Eclipse plug-in development now... Cheers! Frank
(In reply to comment #70) > Cannot complete the install because one or more required items could not > be found. > Software being installed: Industrial Connector for Mylyn 0.9.9 > (com.industrialtsi.mylyn.feature.feature.group 0.9.9) > Missing requirement: Industrial Connector for Mylyn 0.9.9 > (com.industrialtsi.mylyn.feature.feature.group 0.9.9) requires > 'org.eclipse.mylyn.tasks.ui [3.8.0,3.9.0)' but it could not be found It's OSGI versioning at work, we need to update the connector MANIFEST.MF to work with Mylyn 3.9.0 and up. Will do that when I have time later this week, so we go to version 0.9.10.
Hi Wim, Frank, I updated the source at https://code.google.com/a/eclipselabs.org/p/industrial-mylyn/source/browse/ with following changes: + made compatible with Mylyn 3.9.0 + completely buildable with Maven/Tycho + bumper version up to 0.9.10 + removed remain software reference from pom file + added dependency on EclipseLink 2.4.2 You should be able to checkout, run the pom.xml in /com.industrialtsi.mylyn.maven to an install, and the install from an update sit in /com.industrialtsi.mylyn.site In order to create your own connector and DB definition you shoulkd create your own fragment where can follow three routes: - use JPA to define the queries for the connector - use iBatis XML files to create definitions for the queries - use Java code to handle the connecting by implementing a persistor A slightly outdated set of examples for the iBatis route is shown here http://www.eclipsophy.com/2009/09/industrial-sql-connector-and-eventum.html Closing as RESOLVED FIXED