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.
- If you don’t have Subversion proper (not just Tortoise) installed on your machine, download the real SVN binaries as a zip file.
- Extract it inside a folder of Tortoise, or OnTime or anywhere you want. Remember the path to the /bin directory.
- Rt-click the specific project in the tree, choose Manage Source Control.
- Enter the details as seen here:
- 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.
Using TortoiseSVN, add the following properties to the root folder in your application. Don’t put the double quotes in for the values:
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.
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.
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:
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>"
if(urlid > 10000) then
response.redirect("/web/Defects/ViewDefect.aspx?DefectId=" + urlid)
response.redirect("/web/Features/ViewFeature.aspx?FeatureId=" + urlid)
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:
call set dirRepo=%dirRepo:/=\%
cd /d %dirRepo%
cd /d hooks
svnlook changed -r %2 %1 > %temp%\%LogSvnChangeFile%
svnlook log -r %2 %1 | %ScriptCommand% "%LogBugDataScript%" %2 %temp%\%LogSvnChangeFile% %~n1
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.
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.
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.