So maybe databases aren't half bad afterall

So I've been working on a budgeting program for the last few years (none of the available ones had the features that I needed/wanted) and when I first started I put all of the data in XML. It was easy/quick to get it up and running and handled the hierarchical nature of the data very well, but as is the case with XML is was very space heavy and took a while to save, load, and query the information (I later changed the program to just load the XML to mirrored data structures in memory to speed things up, but querying transactions by date or bill assignment still meant that I had to touch every single transaction in memory).

So I eventually made the switch to SQLite and since the hierarchy was fixed and I didn't have any trees in my data structures, the weaknesses of hierarchical data in databases weren't a concern. Currently there's only 2 levels in my bills structure (categories and then bills), but a few weeks ago I decided that I wanted to get rid of categories and change the way I did bills to allow infinite depth/sub-bills to my bill organization.

This then had me wondering if I should go back to a more hierarchical friendly data format. After the compactness and speed of SQLite, I didn't want to go back to XML and a little searching turned up Protocol Buffers. They are actually really cool and I was amazed by them that I was about to redo my whole program to make use of them (I'm a sucker for strict typing), but then I started thinking about the whole querying thing again. Right now, I can query in so many ways in a very easy and efficient manner that it's almost impossible to match with my own data structures. I thought about reorganizing the data so that querying was more efficient, but then I was just recreating a database by hand in my code and I just couldn't justify all of the programming time that that would require just to get some potential speed improvements (and then all of the data would have to be resident in memory all the time (or I'd have to write my own read/write/cache system) and the cons definitely started outweighing the potential pros).

So I was then left with getting the hierarchical data working in SQLite. The standard parent-child tree structure was the simple approach that popped into my head first and apparently some new SQL to handle such a thing in some DBs (but not SQLite). I figured that someone smarter than myself must have already solved this problem, and some digging revealed that I was right about that. I stumbled on the nested set model, and it's one of those beautifully elegant solutions that I love so much about programming (but that's another post). It's pretty cool, because you actually encode all of the links in a separate table (or you can do the same table if you like) and you do it by doing a pre-order tree traversal and numbering as you go. So insert, moving, and deleting nodes is just a matter of updating the numbering in linkage. The one disadvantage is that when you add/delete/change a node in the tree, you are going to have to touch half the entries in the table on average, but in my program adding/moving bills is infrequent and there are FAR fewer bills than transactions, so paying that small hit in performance on the bills is more than worth it for the big gains with transactions.

Foreign Keys in SQLite

As a followup to my previous post on foreign keys in SQLite, I just wanted to point out that code to generate the triggers to emulate foreign keys has been official added to SQLite in version 3.6.4.

sqlite-manager

Maybe everyone already know about this, but I just stumbled across sqlite-manager. It's a Firefox add-on that can also be run with XULRunner which allows you admin an SQL database using a GUI. It's still somewhat early on in development, but it's functional and VERY useful. So go check it out for yourself.

Memory Leak Detection with Visual Studio 2008

Brad Fish told me that Visual Studio 2008 was definitely worth the upgrade (especially on Vista), so I took the plunge. For a C++ programmer, I don't think it was as cool of a jump as 2003 to 2005 and not even close to the HUGE jump from 6 to 2003 (I skipped 2002), but it's still been a very nice upgrade.

But anyway, I have been working on making my own little SQLite3 C++ wrapper, because I just didn't like the ones that I could find out there. While doing that I also decided that it would be easiest to use a smart pointer to manage all of the pointers to SQLite stuff. I had played around with the Boost shared_ptr, but once again I just didn't like some of the syntax (mostly the custom deleter being in the constructor rather than a template parameter), so I just dusted off some old shared pointer code of my own and added a custom deleter to it.

It all seemed to be working just fine, but I just wanted to make sure that I was cleaning everything up properly and then I remembered that the current versions of Visual Studio don't dump out the memory leaks like Visual Studio 6 used to do by default. So I started some Googling and found this page. It says that it doesn't work with Express Edition, but it must be a typo or something, because it works (just not quite as described). Basically, here's what I was able to figure out from playing around with it. All you need to do is:
1) Add the header crtdbg.h
2) Call _CrtSetDbgFlag ( _CRTDBG_ALLOC_MEM_DF | _CRTDBG_LEAK_CHECK_DF );

You can find out more about _CrtSetDbgFlag() here, but calling it this way this will turn on the printing out of the memory leaks when your program exits (only when _DEBUG is defined).

After creating some artifical memory leaks, I noticed that it didn't dump the line that the memory was allocated on like it used to. So I did a little scavenging through the crtdbg.h header file and I found a conditional compile that would enable the printing of the allocation line. I added the two conditions to my code:
#define _CRTDBG_MAP_ALLOC
#define _CRTDBG_MAP_ALLOC_NEW
But unfortunately, this is just a mechanism to allow old code to still compile, because now it just prints the line in the crtdbg.h file. This is obviously useless (something that's I just noticed is also pointed out in the comments of the crtdbg.h file), so I guess this is the first thing I've found in the newer versions of Visual Studio that just aren't up to par with Visual Studio 6 (I know I didn't think I'd ever say/hear/read that either).

I guess that I should just be happy that I was able to get my memory leak dump again, but I'm still wondering why that's not on by default.

Integrity is a Virtue

I've finally accepted that I'm kind of obsessive/anal when it comes to programming and that's probably why I'm so fascinated by C++ instead of all of the other crazy languages out there. But I've started playing around with SQLite and it really bothered me that it didn't enforce foreign key constraints. So I did some digging and found this. It's a description of how you can use triggers to enforce foreign keys. It's actually a pretty slick idea and allows people that want/need them to turn them on without the unnecessary overhead for those that don't care (but it would still be nice if this was just added as a code thing that could be taken out with conditional compilation). But the little online tool is pretty handy and I'd definitely recommend it to anyone that's using SQLite.