Adobe Solution Partner

March 26, 2009

Axosoft OnTime to Subversion (SVN) hooks via TortoiseSVN

Filed under: General Development — Tags: , , — Nat Papovich @ 5:52 pm

A few years ago, we tried out Axosoft’s OnTime ticket managment/bug tracker. At the time, we were impressed. You can read the full details of my review here. After using it for a couple weeks, I hooked up the “BUGTRAQ” keyword in TortoiseSVN to enable us to have bi-directional links between SVN and OnTime. I posted this to the Axosoft community forums, but never got around to making the scripts available to do the hookup. Here are the instructions.

The first thing you need to do is hook up OnTime and Subversion “regularly”, i.e. how Axosoft says you can hook it up.

Subversion Setup

  1. If you don’t have Subversion proper (not just Tortoise) installed on your machine, download the real SVN binaries as a zip file.
  2. Extract it inside a folder of Tortoise, or OnTime or anywhere you want. Remember the path to the /bin directory.
  3. Rt-click the specific project in the tree, choose Manage Source Control.
  4. Enter the details as seen here:ontime-svn
  5. Make sure you enter the right project/repo path in the Server URL, not just svn.site.com. You may need to add a /trunk depending on your setup.

Now make sure this all works between OnTime and SVN. If it doesn’t, you have no hope of progressing. The next step is setting up TortoiseSVN to allow a Bug ID to be added to commits.

SVN Properties

Using TortoiseSVN, add the following properties to the root folder in your application. Don’t put the double quotes in for the values:

Name / Value
bugtraq:label / "OnTime ID"
bugtraq:message / "BUGID: %BUGID%"
bugtraq:url / "http://ontime.yoursite.com/TortoiseLinker.asp?id=%BUGID%

Do a commit on that root folder and tell everyone to do an update on the root folder so they have the same properties.

Now you should notice that the path I used above is “ontime.yoursite.com”. Naturally, you should point that to your site, that is, your own copy of OnTime. This won’t work on the hosted accounts. The next thing we need to do is prepare OnTime for your new TortoiseLinker.asp file, but before that, we need to hack the OnTime database.

By the way, I think this will also work with the uber-cool Versions app for SVN on Mac. Not sure though.

Hacking OnTime Primary Keys

“Hacking” is such an overused word, but I’m using it not like, “I hacked the FBI mainframe,” but like “This code was written by a hack.” Which is what my opinion of OnTime is, BTW. It’s important to note that what you are about to do will probably void any warranties or support contracts you may have with Axosoft. How badly did you say you wanted this integration?

Open up your SQL Enterprise Manager, or SQL Server Management Studio, and browse the OnTime database. Go to the Design view of the Defects table. Find the Column Properties for the Defect ID column, and then find the “Identity Seed” sub-property of the “Identity Specification” property. Change this from “1″ to “10000″, or a million, or a zillion, or whatever is the greatest number of Features you think you’ll ever ever ever have, across all your projects. Yes, I said Features, not Defects. You change the Defects one, not the Features one.

hacking-ontime

Yes, this is a flaw in the system, but it gets around a stupid design failure in OnTime. Note that this will not screw up any of your existing tickets, but you just won’t be able to use this whole Tortoise-SVN-OnTime integration for any tickets created before you made this adjustment.

Save the table and get out of there.

Making TortoiseLinker.asp

Now go over to your ontime.yoursite.com server, and make a script called “TortoiseLinker.asp” and put it in the path that you specified above in the bugtraq:url SVN property. Here is the script:

<%
Dim urlid
urlid = request("id")

if(Trim(urlid) = "" OR urlid = null) then
    response.write "Unable to find 'id' on the URL string.<br><br><a href='http://ontime.yoursite.com/web'>Click here to go to OnTime.</a>"
    response.end
end if

if(urlid > 10000) then
    response.redirect("/web/Defects/ViewDefect.aspx?DefectId=" + urlid)
else
    response.redirect("/web/Features/ViewFeature.aspx?FeatureId=" + urlid)
end if
response.end
%>

I’m not an ASP programmer, so I’m not going to bother walking you through this code line by line. I will tell you that the job of this file is to handle a link in an SVN commit message. That’s the point of the bugtraq:url property. “When you click a link in an SVN message, go to this URL.” This little script just pulls the “id” field off the incoming request, parses it, and sends you to the right OnTime screen. Now you see why we adjusted the identity seed for Defects. Without this adjustement, you would have no idea whether an incoming id was supposed to reference a Feature or a Defect. And that is Axosoft’s design flaw. There is no way that you can identify the end-point with just a numeric integer. You also need to know the type, like Defect, Features, Note, etc. (BTW, we never used anything except Defects and Features, so you might be screwed if you rely heavily on other “types”.)

Now test this dude out. Commit a change to the repo, put a BUGID in the field in Tortoise, and then you can click the link in the message, which gets handled by TortoiseLinker.asp, which then sends you to the right OnTime screen. But we also want SVN commits to automatically update tickets in OnTime. And that’s in the next step.

Making Post-Commit Hooks

For this piece of the puzzle, I blatantly ripped off FogBugz, but I guess it all comes around because we pay for FogBugz now.

Make a file called post-commit.bat and put it in the “hooks” folder of your project. This was lifted directly from the FogBugz help site:

setlocal
set dirRepo=%1
call set dirRepo=%dirRepo:/=\%
cd /d %dirRepo%
cd /d hooks
set ChangeFileSuffix=%2
set LogSvnChangeFile=svn%ChangeFileSuffix:~-2,2%.txt
set LogBugDataScript=%~dp0logBugDataSVN.vbs
set ScriptCommand=cscript
svnlook changed -r %2 %1 > %temp%\%LogSvnChangeFile%
svnlook log -r %2 %1 | %ScriptCommand% "%LogBugDataScript%" %2 %temp%\%LogSvnChangeFile% %~n1
del %temp%\%LogSvnChangeFile%
endlocal

This file will be automatically called every time a commit occurs, after the commit occurs. Make sure you don’t have any typos in there. Notice that this bat file calls a .vbs script, so that’s next.

This is where it gets really ugly. In short, since we don’t use OnTime any more, I don’t have the exact copy of this file that I used. Therefore, I can only point you to a file, offer some pointers and wish you luck.

Go here: http://www.fogcreek.com/FogBugz/docs/60/topics/sourcecontrol/setup/Subversion.html

That article mentions the vbs file, which you can download right here, sticking it in the same “hooks” folder of your project.

The ugly part is that you need to edit this vbs file to call a file called TortoiseLinker.cfm, located on your own server in a path of your choosing, and appending three parameters to the URL: id, rev and file. Those variables will come across from the post-commit.bat.

Take a look at lines 8 through 22. It should be easy enough to fix those. Now look at lines 118 and 119. Understand how they work so you can change them to make sure they hit TortoiseCommiter.cfm. I sure wish I had this file, all fixed up and pointing to TortoiseCommiter.cfm, but alas.

Making TortoiseCommiter.cfm

As I said before, I’m not an ASP programmer. I had originally intended to make this as an ASP file, but I’m a ColdFusion programmer. Don’t let this little hiccup foil you. If you are a good programmer of any language, you should be able to read this file and write it in your language of choice (and a language which your OnTime server can read/run). We had ColdFusion also running on our OnTime server, so this script is run easily.

Download the TortoiseCommiter.cfm script here, then open it up. If you’re lucky enough to have CF running on your OnTime server, you’ll make zero changes. Just make a ColdFusion DSN called “ontime” and point it to your OnTime database.

If you don’t have ColdFusion on your server, you’ll need to rewrite this file in your programming language of choice. Send me a copy and I’ll include it in this blog post for other people to use. Here’s how the file works.

On line 26, I do some validation of the request parameters. On line 44, I run a query on the OnTime database to get information about the ticket and the user for which the ticket is assigned, again using that big autonumber that we hacked earlier. On line 68, I get the SVN connection information from OnTime which you set up way back in the very first step of this process. Line 78 is a room for improvement – I’d like to only allow ticket updates if the committer was assigned the ticket, but I think you’d have to have a way to tie SVN users and OnTime users together unless you could rely on the fragile usernames.

Now the real action happens. On line 85, I call a built-in OnTime stored procedure called spI_SCMLinks. The “sp” part means it’s a stored proc. The “I” part means this stored proc does and insert. If you check out this stored proc yourself, you can see what it does, but suffice it to say that it makes a link from the ticket to an SVN path.

Then on line 95, I get a specific workflow step, in my case “Being Tested”. This is a fragile step in that if that workflow step changes, or the meaning of that workflow step changes, this script will fail. You have been warned.

Finally, on lines 103 through 123 I update the existing OnTime ticket, setting the StatusTypeId, WorkflowStepId, AssignedToId to values I want, and appending some information to the Description field. All of that is quite brittle in that I’ve hard-coded values which could get my in trouble later. I’m sure you can figure out a cleaner way to do it.

The last little bit, lines 124 to 134 were never turned on, but I think it could be valuable to get working, if you use the Audit Trails in OnTime.

And finally, on line 136, I output the string ‘OK’, which is what the vbs file expects.

Summary

This may seem like a very roundabout way to accomplish such a seemingly simple and incredibly powerful bit of integration, and I do agree. However, this is exactly how it’s accomplished with any other system implementing the bugtraq properties in SVN. Admittedly though, the creator of the ticket system (ahem, Axosoft) should build these scripts and make them available to their users, just like FogCreek does. If that ever happens, you’ll be able to skip all the scripting steps here. I am betting though, that Axosoft won’t help users out with this because of that one terrible design flaw that primary keys are shared across ticket types. Without doing that little autonumber seed hack, none of this will work.

Share and Enjoy:
  • Digg
  • del.icio.us
  • Facebook
  • Google Bookmarks
  • LinkedIn
  • StumbleUpon
  • Technorati
  • TwitThis

3 Comments »

  1. I think you can get around hacking the primary keys by a simple naming convention. Prefix defects with d, features with f, tasks with t etc. Then in the TortoiseLinker.asp file, check the first character to find out where to redirect the user to, instead of basing it on the defect id in the querystring.

    While this feels kind of dirty to me as well, it’s preferrable to modifying the ontime database primary key and potentially voiding my support contract.

    Your post was extremely helpful, Thanks.

    Comment by Brandon Behrens — April 21, 2009 @ 6:05 pm

  2. Out of curiosity, why did you switch from OnTime to FogBugz? FogBugz seems to lack a lot of functionality in comparison to OnTime.

    Comment by Scott Vickery — June 24, 2009 @ 1:31 pm

  3. Scott – yes FogBugz has a much smaller featureset than OnTime. And we actually used many of those features in OnTime like workflow-specific templates, the client portal, and customizable workflows. We switched mainly to give FogBugz a try, and it suits us well enough. Not perfectly, but then neither did OnTime. Ultimately, we’d like to -gasp- build our own, but we haven’t done it yet. For a company like ours, task and ticket management can be a strategic advantage.

    Comment by Nat Papovich — July 10, 2009 @ 11:57 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

 

Server Down?

Maximize Web application uptime by drawing upon Webapper's years of experience tuning and stabilizing many of the world's largest ColdFusion Web applications. Contact us today!