Tag: Squidhead

I’ve done three updates to Squidhead since the last time I blogged about it. Two of them were pretty major so I figured I would squawk some more about it.

First, I haven’t forgotten about adding support for other DBMS’s other than Microsoft SQL. I haven’t done it, but I have re-architected Squidhead slightly to make it a bit more possible to do so down the road. There’s one more step I need to take to open that up, which is focusing on primary key’s rather than identity. I know it should work, but I’m just not ready to pull the trigger on it.

Second, I’ve added quite a few features around the main unique feature of Squidhead, stored procedures. The whole focus of Squidhead is pulling in user created stored procedures and creating the ColdFusion code to support them. The recent changes I made allow Squidhead to properly handle store procedures that return multiple result sets. Alternately it can handle multiple output parameters, from the stored procedures.

Third, I’ve got a Build a Blog in 15 minutes presentation in the works. It’s a respectful homage, to Joe Rinehart’s
YouTube presentation on ModelGlue. I figure it’s effective in getting the feature set across, and appropriate since Joe’s presentation got me into code generation and frameworks in the first place.

Finally, I have a pretty good idea of what’s coming down the pike for Squidhead. Before I add support for other DBMS, I’m going to try and flush out the feature set, so as to avoid writing multiple versions in tandem. I prefer to port over the full version to other databases. My current list of updates includes support for foreign and primary keys. I’m also looking to add some form validation. That being said, if anyone has any thoughts, I’m very open to them.

I finally got around to listening to the ColdFusion Weekly Podcast on February 12^th. Squidhead was mentioned – WoooHooo! Wait a minute from the description it seems like Squidhead just creates CRUD + list stored procedures. The key to my disappointment is the word “just.” Squidhead doesn’t just generate stored procedures. It also reads in stored procedures that you create. So sure, it scaffolds the basic CRUD and list actions building stored procedures and CFC’s but also builds CFML from user defined stored procedures.

So for example, you have a table, person, with columns personid, username, password, first and last name. You run Squidhead once, you get the 4 CRUD procs, plus a DAO CFC to call them, then you get a list proc, plus a gateway CFC to call them. But then you go back and write a procedure “usp_person_select_by_username” because you would rather do that then select by identity. You run Squidhead again; it adds “select_by_username” function to the person DAO CFC.

But let’s take it one step further, suppose you write a stored procedure named “usp_person_auth” that takes the username and password and checks them as an authentication routine. Run Squidhead again. You’ll now have an “auth” function in your person gateway CFC.

The idea here is to do the database work in the database, and not have to do the manual work of writing the cfml calling code. Sure, it creates stored procedures, and that is a leg up, but it also lets those of us that like starting in the database an even higher leg up… (not sure if that’s the right modification of that metaphor.)

Anyway, don’t take this as a swipe at Matt and Peter. I’m glad to have some exposure in the ColdFusion Weekly Podcast. It’s my bad that I haven’t been publicizing Squidhead better.

I’m not talking about being unemotional about code, which would be “objectively”. No, “objectly,” I’m talking about code generation. When I first started writing Squidhead, I was writing procedural code to generate my code. After all, it’s tempting to think of it procedurally, as code tends to read from top to bottom. One of the breakthroughs for me was figuring out that most of the coding structures I was using can be expressed as objects. I mean the actual code text itself can be expressed as objects. In ColdFusion, this means a CFC.

Let me show you an example of what I’m talking about:

This is a basic insert function. We’ve all written this type of function as part of a CFC.

Let’s break this down into its properties:

Once you’ve established the structure of the CFC, you make these properties into variables. Anything that could be a list is actually an array. So arguments and local variables are actually an array of <cfargument> declarations, as is local variables. You write getter and setters. Actually I tend to write adders instead of setters as I’m just adding content to properties that grow. The getter that I write just concatenates all of the code and outputs it.

Here’s the code:

Function.cfc

You can even break it down even further. If you wanted, each <cfargument> could be an object with each attribute being a property. I didn’t go that far in Squidhead, but I did break the <cfstoredproc> declaration into an CFC.

This works very well for highly structured code, like <cfcomponents>’s , <cffunction>’s and <cfstoredproc>’s but it can be extended into less obviously structured code like custom tags. I was able to convert most of my procedural code creators to object oriented code creators.

So for whatever that’s worth, maybe this can help someone else out there.

One of the requests I’ve gotten for Squidhead is a MySQL version. Being the responsible Open Source author (or wanting to suck up to the people that are actually interested in it,) I tried to make it work with MySQL 5.0.

It did not go well. Specifically, I cannot call MySQL stored procedures from ColdFusion using <cfstoredproc>. (I get a null error from indexOfIgnoreCaseRespectQuotes in the MySQL driver.) It isn’t an absolute deal breaker, but makes my job much, much harder. I have to branch off more than just the database interaction. I would also have to branch off the CFC code too. (Which is much more complicated than the database stuff.)

So now I’m stuck at a quandary:

• Do I do the work to get it to interact with MySQL at Squidhead’s present level of features? OR
• Do I start adding features to Squidhead, along just the MSSQL path, and go back later and add MySQL support for the whole thing?

Part of my original logic behind doing it in MS SQL only, was that the problem I was trying to solve with Squidhead was DBA mandates of “You must use stored procedures!” My gut feeling was that this type of mandate was more likely to found in a shop with a more established database. I’m not knocking MySQL here, but stored procedures were just introduced in version 5.0, and a lot of shops don’t run it yet. Therefore less shops would mandate stored procedures with MySQL. So it would be smarter to go after an Oracle version than a MySQL version.

Then there are the features that I would like to add.

• I would like to remove the requirement identities. (I would do CRUD against Primary Keys.)
• I would like to add support for configuration metadata for things like “ORDER BY.”
• I would like to decouple the custom tags from the data retrieval cfc methods.

Does anyone out there with either an interest in Squidhead, or experience with an open source product have any advice?

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?