DougHughes.net

Recently there’s been a lot of interest in code generators which create database abstraction layers. All of the code generators I’ve seen require that you run a separate program to generate DAOs, Gateways, TOs and beans. I’m currently finishing up a project where we used one.

Before I get into my experiences with database abstraction generators, let’s do a quick review of how they work. Simply put, databases contain metadata which describe their structure. By reading this metadata, you can gain enough information to generate database abstractions.

The typical code generator will be an executable which you point at a table or a database and run. The generator then creates all your needed Data Access objects, Gateways, Transfer Objects and sometimes even Beans.

I’ve been using a code generator on my most recent project. There are some things I love about it and some things I don’t like so much. But, in the end, after two months of using it, I can’t imagine life without it.

The project I’ve been working on has 38 tables. This is not a large number of tables. However, if we had manually written each of the four data-related objects we would have written 152 distinct objects, all of which are almost identical.

Furthermore, with this three person team we had less than eight weeks to build the application. That mean that between all of us, working 60 hours weeks we would have a total of 1440 programming hours. However, if you consider the amount of time spent in meetings, debugging, QAing, fixing machine crashes, taking care of sick babies and wives, etc, you realize that you’re lucky if programmers program 50% of the time. At that rate you have a measly 720 hours in which all of your development must be done. This means that, if each of those 152 database abstraction files took an average of one hour to write, you would be spending more than 20% of your entire development time writing your database abstraction.

Let me put it another way, the code generator we used was probably the most significant reason we’re going to be hitting our deadline. (Actually, we hit it a week early.)

How it works is that you open up the code generator in a web browser and select the types of objects you wanted to generate for a particular table (or all tables) in your database. You provide the path to the directory where generated files should be written and click the generate button. The generator grinds away for a few seconds. When done, you receive a report as to what it did.

There is, of course a problem with this. First off, what happens if you need to customize the objects? There’s no way (that I know of) that a code generator could generate all the queries you might need. So, if you customize the objects you would run the risk of loosing your customizations the next time you regenerate your code.

We solved this problem by customizing the code generator to generate objects into a “base” directory and to also (if they don’t already exist) generate empty shells of “custom” objects which extend objects in the base directory. This allows us to safely edit the custom shells without worry that they might be overwritten.

This particular code generator has a few short comings: It was brought into this project by one of the developers we contracted. They own the IP related to it. Also, it’s closed source and is not available to the public. Another thing that I don’t like is that it creates difficult to read code.

But still, the code generator is terrific, but why do I need to run it each time the database changes?

Why can’t I just have a simple API which does all the work behind the scenes?

If I know what I want and where it is, and it (the database) knows how to describe itself, why can’t an API just generate objects on the fly as needed?

I suppose there’s no reason it can’t. Hence: Reaction For ColdFusion – An API for Inline Dynamic Database Abstraction.

Watch my blog for more on this in a little bit.