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?