ianmjones

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.

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.