Database codegen

What is the problem with the current Delphi database model? You might think I have gone off my trolley completely, but if you are an experienced Delphi programmer you will probably recognize what I’m talking about. Namely that when you  have a huge application with many forms – all populated by query components, you quickly end up with spaghetti code. It depends of course on how you write your queries – but somehow big projects always end up with a fair amount of spaghetti. Especially when you have apps coded over several years, involving some 20+ programmers all with their own ideas (like myself) on how to solve it.

So how do we make database programming on a massive scale “human”? Well, leaving commercial solutions aside, I like my database code wrapped up neatly in classes. When working with database records it is much easier to work with normal objects rather than hundreds of lines of code with fieldByName(“somefield”). So I decided to create a little firebird database table-wrapper generator.

Consider the following task:

• select all items from a table
• loop through each item and sum up a total based on a value from each record

This task is now reduced to:

var
  mItems: TMytableItemList;
  mItem: TMyTableItem;
  mTotal: Int64;

if TMyTableController.getItems(mItems) then
Begin
  try
    for each mItem in mItems do
    inc(mTotal,mItem.value);
  finally
    mItems.free;
  end;
end;

Thats pretty easy to debug and work with. And as you can imagine, the more tables and joins you have – the more complex a normal Delphi TQueryComponent based form gets. But with clear-cut classes neatly wrapped for both read and write — it’s actually fun to write large-scale database applications.

Sanity check

At present my little wrapper spits out some very basic code. Blobs are read verbatim on requesting an object or a list (which is not very friendly) and also you get choices like “should a list be read on demand? or all at once”. Im still busy tinkering with the API (the TDatabaseObj class and firebird database helper methods), but it will soon hit production. The bonus? It’s dead simple to add support for other databases – and the user doesn’t have to care at all about the underlying engine. It doesn’t matter if you use sqlserver, firebird, mysql, or a native delphi database like elevatedb. Just stick to the objects and you are home free.

Other alternatives

If you have the luck to start a project from scratch, then I would probably go for Remobjects Data Abstract product. I have been a fan of Remobjects since day one, and although we have had our differences over the years – i still love their products. They deliver polished, no hazzle and straight to the point solutions. At present my offering is little more than a “proof of concept”, so don’t expect to fork my codebase and re-wamp your databases just yet. But when im done – database programming on source level will be easier and more secure. I have spent the better part of 10 years writing rock solid database solutions for oil companies that demand 24/7 win32 service excellence so – even though some of my solutions might seem strange, rest assured that there are reasons (read: real life experience) for the way the code is generated. A simple exception in a win32 service that handles some 200.000 customers an hour will cost you an arm and a leg.. you dont want to mess about with code formating or excessive use of exception handling at that level.

But word to the wise – you need a good database solution? Remobjects will give you a great solution out of the box. It depends on how close to the code you want to work.