Project: Squidhead

I’ve been reading Peter Bell and drinking the application generation Kool-Aid. To that end, I’ve been working on what I consider a development tool, but others I have talked to have called a baby framework. My gut feeling is that it might be a little bit too specialized for mass consumption, but I figured I would see if anyone else could use something like this.

For this to make sense I need to tell you a bit about my workplace environment:

  • We run ColdFusion 6 and 7, against large installations of MS SQL.
  • The Admin team (of which I am a member) mandated that developers cannot use direct SQL calls in ColdFusion, instead that we have to use Stored Procedures. I’m not going to get in the particulars of whether this is absolutely right or wrong, but it has done good things specifically for us.
  • Traditionally we have been anti-framework. A few of us have been moving towards it for the past few years, but there is still a lot of mistrust of frameworks, or patterns for that matter.
  • Traditionally we have been slow to adopt object-oriented ColdFusion. Likewise, I, and a few others came around a few years back, but others have not.
  • CFC’s took a long time to get adopted, and they are still not fully in use.

With all of that in mind, I took at look at maybe developing something that would get rid of our most repetitive tasks. So I built a stored procedure creator that I extended, and extended until now, it actually does a whole CRUD application albeit a crude one.

In a nutshell here’s what it does:

  • It inspects a Database
  • It analyzes the tables and views
  • It creates INSERT, SELECT, UPDATE, DELETE and LIST stored procedures for every TABLE.
  • It creates LIST stored procedure for every VIEW.
  • It adds them to the database.
  • It assigns the proper GRANT permissions

Then it does a second pass.

  • It inspects the database
  • It analyses all of the stored procedures not just the ones the process creates
  • It creates the <cfstoredProcedure> code for each stored procedure
  • It creates the <cffunction> to wrap the storedprocedure in.
  • It creates two sets of CFC’s: GATEWAY and DAO
  • It creates a DAO for each TABLE and VIEW.
  • It adds each <cffunction> <cfstoredproc> combo within the appropriate CFC per table.
  • It places any operation with SELECT, DELETE, UPDATE, AND INSERT in the title within the DAO CFC’S.
  • It places all other actions to the GATEWAY CFC’s.
  • It writes the CFC’s to disk.

Finally:

  • It writes Custom Tag components to produce nuggets of Editing and Listing widgets.
  • It strings all of these together to produce a simplistic CRUD application.

There are a few restrictions on its use:

  • Only works on MS SQL
  • Requires account with ability to create and drop objects
  • All tables must have identity.
  • Preferably the id should be [table name]Id (Meaning I haven’t found all the bugs)
  • Fields named “createdOn”, “updatedOn” will be automatically set
  • Field named “createdBy” will only be set on create method
  • All stored procedures should be named “usp_[tableName]_[action]”
  • Table names need to be one word, Camel case is okay
  • Action can contain dashes.

I’ve found that this sort of thing is much better received with a demonstration. Please check out this Demonstration of what it can do.

Also I have a sample of what I think its killer feature is: it analyzes all stored procedures in the database, not just the ones it wrote. So you can write your own stored procedures, press a button, and have the code generator frame out the CFC calls for it. Here is a Demonstration of this in action.

It’s worth it to say that this borrows a lot of successful patterns from Reactor for ColdFusion and ModelGlue. However, it is not as complex, flexible or powerful as either of these two. Let me make that very clear, so I don’t get any flame here. This is far inferior in terms of feature set and flexibility than either of these solutions. There is a lot this thing doesn’t do. But is also doesn’t require you to come completely over to the frameworks camp. It doesn’t require you to learn MVC. To that end, it fits my environment. I think it could be a good bridge between going it alone, and a full flexible, powerful, framework.

Would this be useful to anyone else, or am I right in thinking it’s too specific to our environment?

9 thoughts on “Project: Squidhead

  1. Awesome, I did something recently similar for Oracle but without the stored procedures. I needed to make a prototype and creating a code generator was quicker than writing the CRUD functions. What language did you write this in? Writing a code generator in CF for CF is confusing.

    Like

  2. I think that it would help a lot of people. There are lots of good reasons to used stored procedures and it would automate things for many with homegrown frameworks.

    Like

  3. Sounds great. Would be very useful for me. I’m interested in code generation so it would be interesting from a “how did he do it” perspective too.

    Like

  4. Thanks for the encouragement, Peter.

    Actually, I made the demos with an Adobe product named Captivate (used to be RoboDemo). It’s really a good product that makes doing that sort of thing, extremely easy.

    Like

  5. This is really great! By the time a programmer starts copying/pasting the same subroutine (sproc) for the 3rd or 4th time, he starts to think about an application generator.
    I encourage you to take a look at Paul Nielsen’s logging stored procedure that he’s posted at http://www.sqlserverbible.com.
    It looks at all the tables and builds triggers to log any changes.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s