Posts categorized “CaseDetective”.

CaseDetective 1.1 Sneak Peek #4: Graphs

So far the sneak peeks for CaseDetective 1.1 haven’t been all that enthralling, even I am able to admit that the features I’ve shown aren’t that big a deal to most users of CaseDetective and FogBugz.

This one may turn a few heads though, CaseDetective for FogBugz 1.1 will include the ability to view some “potted” graphs, just like this one:

SimpleGraphS.jpg

The above graph was grabbed from the new “Overview” pane, which includes the previously sneak peeked Last 50 Events list. To be able to include graphs of statistics from FogBugz in your reports, all you have to do is select an appropriate filter and pick the type of graph you want, then simply copy and paste the graph into your report. It’s as simple as 1-2-3!

SimpleGraphWithPopUpS.jpg

And as you can see from the above screenshot (click it for a better view), when you hover your cursor over a “node” you get a little yellow pop-up with a bit of info about it, such as the number of cases (Y value).

For some people this could be a real time saver, as it’s a very quick way of finding out how many of your FogBugz Cases are in a particular Priority, Status, Release and so on, there are quite a few fields you can graph:

GraphFieldsS.jpg

And of course, you need to be able to see when it was that you had that sudden spike in feature requests opened, resolved, closed or last updated:

DateLineGraphS.jpg

Being able to quickly see patterns in your FogBugz data through graphs and charts in CaseDetective for FogBugz should hopefully help a lot people get through their day that little bit quicker when it comes to getting those reports out.

Anchors Away! (CaseDetective 1.1 Sneak Peek #3: Recent Activity List)

This post was originally a guest post for the Fog Creek Blog, but I’m re-posting it here for completeness with permission from Michael.

There’s a neat little new feature hidden away in FogBugz 5 (currently in beta) that I think a lot of people will find useful (and not just because I asked for it), HTML anchors on displayed bug events.

I guess I should explain why I asked for them and why I think you’ll find them useful…

A few months ago there was a feature request on the FogBugz discussion forum that I thought was pretty good, so I nicked it for CaseDetective (thanks Sam Chrisp)!

The feature request was for a “Recent Activity List”, basically a listing of the last X number of edits and comment events so that the FogBugz user could see what has recently happened, and then click through to any Case that’s of interest. The feature request even included a picture of the list that the user had created for themselves by modifying the FogBugz code.

I liked the idea and felt it would be useful as long as you could click on an event line in CaseDetective and go directly to the FogBugz edit entry in the Case view page without having to scroll down the page to it. There was one snag though, FogBugz 4 didn’t have any HTML anchors on each of the entries in the view page so CaseDetective couldn’t construct a URL to go directly to the entry. Darn!

So I fired off a quick email to Michael (Pryor, I think you all know who he is :-) ) asking whether he’d consider adding anchors to the view pages in FogBugz so that anyone could construct a URL to go directly to the event. Michael said he’d pass it on to the development team for appraisal.

The announced features for FogBugz 5 didn’t mention anchors so I thought I’d missed the boat and would have to wait a little while before implementing my Recent Events list. However, while playing with the FB5 beta late one night (make that early one morning, just couldn’t sleep until I found out everything that was new) I checked the HTML source for the Case View page and there they were, BugEvent anchors. Yeehaa!

Here’s the deal, every bug event that is displayed in a Case has an anchor in it’s header, something like “<a name=”BugEvent.9969″>”.

This means you can send someone directly to a comment or edit with a URL such as:

http://server.example.com/fogbugz/default.php?pg=pgEditBug&command=view&ixBug=2469#BugEvent.9969

or the even simpler:

http://server.example.com/fogbugz/?2469#BugEvent.9969

As long as the URL ends with a “#” followed by the anchor’s name, you should be good to go.

This kind of thing is very useful when you want to point someone to a particular comment or edit, instead of having to say:

“Please have a look at the comment dated 03/04/2006 10:20:30 made by John Doe in case 2469″

you can say:

“Please look at this comment: <link>” and past in a direct link to the comment.

Of course, it’s helped me a lot as it meant CaseDetective could acquire the Recent Events list I wanted to implement. Double click an event…

CaseDetective-1_1-RecentEvents-SneakPeek.jpg

… and you’ll be taken directly to it in your default browser:

FogBugz5-Anchors.jpg

This event list will make up the bottom half of CaseDetective 1.1s new “Overview” view, which I’ll discuss a little more on my blog soon.

CaseDetective 1.1 is still in development, but we’re working hard on getting it into your hands as soon as possible. Stay tuned for more updates as we get closer to release.

CaseDetective 1.1 Sneak Peek #2: Open cases by Area

In FogBugz there is this concept of a Project Area. Areas are usually something like “Code”, “Documentation”, “User Interface” and so on, a way of categorizing a Bug, Feature or Inquiry so that the correct person can pick it up. They are very useful, a great way compartmentalizing you Cases for better resource and time management.

The problem is, these Areas are children of a Project, so it’s impossible to create a filter in FogBugz that captures all cases that are in an Area regardless of the Project they are in, for example; all Cases related to “Code”. This is a real shame, as many people use Areas for all kinds of things, and would love to see all cases that have the same Area. Some people think of Areas as a kind of tag.

In CaseDetective 1.1 for FogBugz we’ve opened up Areas a little by giving you a “Open cases by Area” summary filter, allowing you to see all open Cases in each of your Areas, regardless of the Project they’re in.

CaseDetective-1_1-OpenCasesByArea-SneakPeek1.jpg

But, if you then need to drill down to find out which Projects those Cases are in, you can.

CaseDetective-1_1-OpenCasesByArea-SneakPeek2.jpg

Some way of seeing all cases in an Area name without having to select a Project has been one of the most requested features I’ve seen, and something I’ve long wanted myself.

CaseDetective 1.1 is still in development, but we’re working hard on getting it into your hands as soon as possible. Stay tuned for more updates as we get closer to release.

The Marco Bambini Blog: Too perfectionist? / CaseDetective 1.1 Sneak Peek #1

Marco Bambini has just posted a

little update on where he is with SQLiteManager 2.0.

Marco’s included a couple of screenshots, one of the about box that shows off the new icon he’s commissioned and the other of the main window.

The new icon is great, looks very slick and professional. I liked the current icon for it’s simplicity and style, but now that I’ve seen the new one the old one looks kind of drab in comparrison. I think he’s made a good decision in getting a new icon created.

The screenshot of the new look interface shows a huge improvement over the current release, it looks much more user friendly and accessible, and I’m very glad the tabs have been replaced by a toolbar. There’re no details in his post on what’s new functionality wise, but a few of the toolbar icons hint at some new features, Analyze, Verify, Optimize etc.

Marco’s worried that he shouldn’t have invested so much time and money into the new look and upgraded functionality, it’s taken longer than he hoped and he’s still not finished yet. Obviously not having got my hands on the new version yet I can’t say for sure whether he’s done the right thing by doing so much and making his current and potential users wait a little longer, but from just those screen shots I expect he’ll not suffer for it.

I’m in a similar position with the next version of CaseDetective for FogBugz, although I’ll admit I run the risk of having even less to show for my efforts than Marco.

The next version of CaseDetective has taken longer than I hoped, but there’s good reason for it. The next release of CaseDetective, while retaining all the extract functionality of the previous version has changed a huge amount “under the hood” to make it more scalable and able to cope with FogBugz databases with large volumes of cases.

And, funnily enough, a lot of the functionality that has enabled me to make this version of CaseDetective more scalable comes from Marco’s original work on the SQLite plugin for REALbasic that was bought by REAL Software and integrated into REALbasic 2005 and later (I’m currently using REALbasic 2006r1).

I hadn’t planned on taking so long with CaseDetective 1.1, I expected it to be a small update with a few features that had been asked for, the most important of which being support for MS Access databases. But once I had that feature pretty much in place I found it too irresistible to extend the mechanics of the solution towards some longer term feature ideas and scalability requirements.

CaseDetective 1.1 will now cache all the case, event, attachment and reference data from the FogBugz server’s database on your desktop machine in a REAL SQL Database (SQLite3). Once the first full sync is complete only data from changed cases and the reference data will be synchronized when asked for.

CaseDetective-1_1-Refresh-SneakPeek_1.jpg

There are huge benefits in doing this:

  1. Network and server load is greatly reduced. CaseDetective used to hit the network and database server pretty hard if the filter pulled back a lot of cases, now it’s a local lookup to a very fast database with no network traffic. After the initial sync, only reference data and data from cases that have changed since the last sync are retrieved.
  2. Switching from one filter to another takes a fraction of the time in certain cases. Because there’s only local querying of a local database where CaseDetective controls the indexes needed, those queries can be much faster to execute and the data is available with very little latency.
  3. CaseDetective 1.1 can now support FogBugz servers using Microsoft Access databases as their back end. This is only possible because the queries to sync the data are pretty simple in comparison with the complex filter queries which MS Access couldn’t support in an ANSI SQL standard compliant way.
  4. Abstracting the FogBugz database opens up previously difficult to implement future features. And no, I’m not telling what they might be just yet! :-)

After achieving the MS Access compatibility goal, my next biggest goal in this area is support of large FogBugz databases. My ultimate goal is to get to the point where CaseDetective works well with Fog Creek’s own FogBugz database, which at last count had over 250,000 cases in it, and by now surely is exceeding 300,000. This is going to take some work, file sizes for the cache are going to get rather large with that many cases and associated event and attachment records, I can see file size limits being exceeded unless some partitioning of the data happens. But that’s a future goal, I doubt there are many installations anywhere near that size just now, so I’ll address that in a future release.

The observant among you will have noticed the funny little “Switch View” button in the toolbar of the above screenshot, I’ll explain all about it in a later “Sneak Peek”.

For now, that’s enough details on what’s in the pipe line for CaseDetective, I’d better go get on with finishing it, there’s still some way to go just yet.

UNIX Crypt For RealBasic

A little while ago I mentioned that I had initially built the user authentication in CaseDetective so that it used the user’s FogBugz website to do an automated physical login, and then parsed the resulting page for specific variable names that can only be there if the user had logged in successfully. This was a bad idea, and not even my preferred method, but I’d done this way to avoid asking Fog Creek about the encryption they use for user passwords stored in the database. Anyway, once I wised up and asked Michael Pryor if he’d let me in on the secret, he of course told me that it was the UNIX Crypt algorithm (funnily enough, I’ve recently found a discussion forum post where he had already mentioned it, but it was after I released Beta 1).

I was over the moon when Michael told me it was UNIX Crypt because I figured there’d be thousands of ready made modules that I could choose from that would give me instant access to the one-way crypt I needed, it’s so old, so ingrained in the UNIX world that it was surely going to be a breeze.

So I checked out my Einhugur plugins only to find no simple “UNIX Crypt” functions. Not worry, there are a tonne of different algorithms in the e-CryptIt module, it’s probably just down to me not knowing what the underlying cryptographic algorithm is, so I search the net…

I spent a few hours searching the net, eventually finding out that UNIX Crypt is a derivative of the Data Encryption Standard (DES). I learnt a lot about encryption in these few hours, including that UNIX Crypt is pretty weak, but also that it’s pretty easy to password check with because the “salt” is embedded in the encrypted string as the first two characters. I also leant that in a lot of systems the DES algorithm has been superseded by using Blowfish or Twofish.

Back to my plugins … nope, nothing about DES, not a sausage. Bugger! So I sent an email off to the Einhugur list asking if I was right, maybe I’d missed something, but alas, DES and UNIX Crypt are not supported by e-CryptIt, and no-one knew of another plugin that would help. Double bugger! At least e-CryptIt has Blowfish and Twofish, but that doesn’t help me just now as I need the DES version.

There are very few modules out there (”out there” being the internet) that do UNIX Crypt, and most are for Windows and would require that I bundle another dll with my application, which I really didn’t want to do, I already bundle the eSellerate dll and didn’t want to have to bundle any more.

Even if I did buy a dll (most of these dll files require payment) and distribute it for Windows, it would still have left me with having to find a solution on Mac OS X.

I played around with RealBasic Declares on Mac OS X as it comes with a “unistd” library that I found I could potentially call to do a crypt. But I had very sporadic results, and many crashes, and relying on the placement of a library outside of my control gave me the hebees (you’re supposed to be able to just use the sort name for the library without it’s full path, but that never worked for me, I had to use the full path to get anywhere at all).

What next, write my own UNIX Crypt module from scratch, in RealBasic? Surely not, that’s got to be painful!

Well, I had no choice, I either wrote a module to do UNIX Crypt from within my RealBasic application where I had full control and no cross-platform woes, compile up some freely available C code for both Windows and Mac OS X, keep searching fruitlessly for an existing robust cross-platform solution (that isn’t there), or forget the whole thing and go back to looking at building Integrated Windows authentication into my FogBugz web logon code (shivers with dread).

During my research on the web I found various bits of C code that did DES and UNIX Crypt that had BSD or other unencumbered licenses, so I had at least some code I could look at for inspiration. Now, I’m no C coder, I can read it a bit as long as it doesn’t get too hairy, and I’ve even written a few modules and applications for clients where we needed to wrap third-party libraries with C code, and then in some cases wrapped this with Informix 4GL code. I’ve had some great success with that kind of thing. But I’m telling you now, people that devise encryption algorithms are seriously deranged, how on Earth they come up with this stuff beats me, and how some of these C developers got to the place where they do certain things they do I don’t know. It’s just plain scary looking at encryption code.

I’d found via Wikipedia.org a great DES Algorithm Illustrated page (WARNING: if you go to that site, don’t bother looking at other parts unless you’re in the privacy of your own home as your boss may not appreciate the scantily clad ladies) that explained bit by bit (literally) how the DES algorithm worked with a known input and output. Without that site I’d have been right royally stumped, it allowed me to build a stand alone project in which I could test my UNIXCrypt module with a known input and output not only to the entire algorithm, but to each little segment of the calculations.

It took a few days, and a lot of hair loss, but eventually I had straight DES working fine, I could perfectly replicate the inputs and outputs from DES Algorithm Illustrated. Woohoo!

All I had to do then was implement UNIX Crypt. Sounds easy don’t it? I thought so too. The problem is that UNIX Crypt doesn’t use straight DES, oh no, that would be too easy, it has to be mucked about a bit first! ;-)

UNIX Crypt sets the DES key in just the same manner as normal DES does using the string (password) passed in. So far so good. Then it runs DES encrypt 25 times using that key, but starting off with a message containing just zeros. This also sounds pretty straight forward doesn’t it? Well, on each of those 25 runs of DES crypt, at each point that the “E-Box” is expanded (you’ll have to go look at that DES Algorithm Illustrated site to see what that means) the resulting bits (which by the way have already been mucked about enough thank you very much) are mucked about again depending on some XOr criteria derived from the two “salt” characters.

Luckily I had various bits of C code to check out, but they don’t really explain anything, and try as I might I just couldn’t get the bloomin’ thing working.

I tried for two weeks to get UNIX Crypt working, it was so frustrating. I had DES working, I knew it, so why could I not get these seemingly small changes to adhere to the UNIX Crypt algorithm working?! I think I must have lost at least half my hair over those two weeks (that’s my story and I’m sticking to it, I haven’t been losing my hair at all before that, not one ;-) ).

Finally I sent out a plea to the RealBasic Network Users Group (NUG) for anyone who knows any way of getting UNIX Crypt working reliably cross platform from RealBasic. I was prepared to throw all my work away if anyone came up with anything good. No one had suggestion that would work for me, it seems no one on the group had needed to do it before cross platform, although there was a Mac OS X solution for encrypting passwords.

Eventually a guy called Mark Nutter found some Java code I hadn’t seen before that did UNIX Crypt, I looked at it and found that it very closely matched the C code I’d used as a template for my own development. I followed it through and compared to my own RealBasic code, just like I had the C code, but alas could not find anything that looked wrong.

When I reported back to the NUG that I still couldn’t find the problem Mark then offered to take a look at my code to see if anything jumped out, so I took him up on his offer and sent him my test project. Later that day he wrote back, unfortunately not having been able to find the problem. Here’s a quote from his email:

… I’m checking your RB code against that, and
everything seems to check out, except that the results
aren’t matching. But encryption algorithms are so
twisted! My brain hurts after only a couple hours, I
can imagine you’ve been going bonkers.

At this point I’m fairly stumped also. :(

I wrote back thanking him for trying and telling him not to try looking at it any more, I didn’t want to be held responsible for his decline into madness! I’d just have to try and step through the code bit by bit and see if I can find the place where it went astray.

When Mark wrote back to say good luck he casually mentioned that I should try and compile up the C code I’d used as a template and output to a buffer every now and then and compare with my RealBasic code. Doh! Why the hell didn’t think of that sooner, I had code that I knew should work, tried and tested, but just in another language.

So I jumped into XCode created a new project and copied in the C code I’d been using and set about creating a command line app that would simply print out the bit strings at various points. I started off by making it simply do the UNIX crypt to make sure it was doing the right thing, outputting the input and output strings. It worked.

Then I decided I’d start off slowly, I just added one intermediate output of a “bit string”, the bits as accepted by the setkey function which is called right at the beginning before doing the real DES stuff. Then did the same in my RealBasic code. Ran the two of them, and guess what, I already had a miss-match! Bugger, bugger bugger! If I had a problem this early on in the process, it was going to take me bloody ages to find and fix all the other errors.

When I looked closely at the two bit strings, that should have been the same, but weren’t, I noticed that my code was creating a string very similar, in fact it was almost identical except for an extra “0″ at the beginning of the string, and then again before the next recognizable pattern of “1″s and “0″s. Damn, my code was passing in 8bits per character rather than the 7bits per character for the key that the C code was using. Checked my code, sure enough, there it was, an extra “0″ in my bit mask when converting the ascii characters to binary!

Oh well, delete one “O”, recompile and run again to find the next error. Except there wasn’t an error, my test project only bloody went and spat out the correct encrypted string! Woohoo! Do a little jigg!

It only took me a couple of minute to import my UNIXCrypt module into my CaseDetective project and hook it up to get it working, finally I could authenticate a user’s FogBugz login id and password against the database, cross platform and without any dependance on external modules or applications. Phew, I can’t tell you how much of a relief that was, I was so happy.

So, after all that hard work, fretting and nearly going insane, I’m going to do one last insane thing, I’m releasing my UNIXCrypt RealBasic module as a free download. You can grab it from my IMiJ Software website, it’s in the “Free Stuff” section.

You can download or check out the UNIXCrypt REALbasic module and test project that I created from the rbunixcrypt Google Code project. It’s under a MIT license, so you can do pretty much anything you like with it.

You can use the UNIXCrypt RealBasic module to simulate UNIX Crypt by simply calling “UNIXCrypt” with a string and two character salt.

Thank you to everyone who helped or tried to help me get this done, hope someone finds it useful.