The amazing adventures of Doug Hughes

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.

Comments on: "Database Abstraction Code Generators" (9)

  1. Steve Bryant said:


    I have been working on something (perhaps) similar and I would love hear your thoughts on it. It doesn’t alter tables (yet), but will soon.




  2. Doug,

    You might also want to check out the discussion going in the CFCDev list. There’s a push for an ORM like Ruby on Rails’ Activerecord except done in CF. ActiveRecord sound very similar to what you’re proposing.


  3. I abstract my database using SQL-92 which means my SQL code may look a bit simplistic so it can run on any Database platform that has an ODBC driver however this also means my code an run on literally any database platform on the planet. There is just one SQL function that is unique to each platform and this can be eliminated by using the Db Schema that is encapsulated by the JDBC Objects ColdFusion uses when running SQL Statements using ODBC. I believe this was the purpose behind the creation of ODBC to begin with. Additionally I maintain a single set of <CFSCRIPT> functions that I use for all my apps to ease the usage of <CFQUERY> in a robust manner. I don’t waste any time coding any DAO’s (Data Access Objects) using ColdFusion and I doubt seriously the apps I have worked on or will work on would receive any benefit if I were to dedicate time and effort to code CFC’s into a set of DAO’s so I simply cut to the chase and get the work done as quickly as possible using fairly vanilla yet robust <CFSCRIPT> code along with SQL-92. Eh, it gets the work done for me.


  4. Qasim Rasheed said:


    Would you like to mention which tool you were using to generate DAO, Gateways etc.



  5. Jeff Houser said:

    Although not exactly the same thing, I did some searching for UML modeling programs that could generate CFCs. I only found two that would do it:

    Enterprise Architect: Very customizable and works pretty well, but I wasn’t a fan of the program.

    Poseidon: The CFC Generator wouldn’t work in the current version, but I loved the interface.

    Does anyone know of any other programs?


  6. Doug Hughes said:

    Jeff, I havn’t done any formal research into UML to CFC generators. However, from what I’ve seen of UML software, most of the top-end software implement an extenstion mechanism which you might be able to use to create your own CFC generator.

    Many of the UML tools can save the diagrams into a generic XML format. I can’t recall the name at the moment, but I considered writing a program to tanslate that XML into a set of CFC shells.

    The problem I have with that is that 99% of the time, what I design in UML has very little relation to what I actually end up with in code once I get working.


  7. I’ve just been getting up to speed on Reactor, and I wsa reading through your old posts. You seem to give credit for the inspiration for Reactor to the person who originally brought their code generator into your project, but you never say who it is.

    Don’t you think they deserve credit for inspiring your work? Especially given that Reactor is a GPL project, giving credit to people’s work is essentially everything. Don’t you agree?


  8. Doug Hughes said:

    Rob – The guy’s name was Paul Mone. No slight was intended.


  9. Nice Post, thanks.
    I have read and learned a lot about IP, software and computer programming through your blog.
    I’m subscribing
    Thanks again and keep the good Information comming.

    Craigslist IP Software


Comments are closed.

Tag Cloud

%d bloggers like this: