Aarrgghh!!

Squidhead Updates and New Features

It's been a little while since I've talked about new features of Squidhead, so I figured I would take the opportunity to blow my own horn.

New features:

Oracle Support
Linux Support
Linking table support
FKCrazy Application template
Rudimentary ColdSpring Support

Squidhead will run against Oracle 10g. It alters its model a bit to create stored procedures within packages, as this seemed to be in keeping with Oracle Best Practices.

Squidhead will run on Ubuntu. I haven't tried it in other flavors yet, but I can't tell why it wouldn't.

If you follow the convention of naming tables [table1]To[table2] and have the primary keys from table1 and table2 as foreign keys, Squidhead will recognize the table as a linking table. It will create stored procedures that pass queries through this linking table to create relationships between linked tables.

FKCrazy application will use the linking table information to automatically create interface that uses all of the foreign key relationships to add and delete child records. It can do this in a one to many relationship, or in a many to many relationship. Translation: If you've seen my Facebook in 17 minutes presentation - everything I did manually at the end now happens automatically. So if I did it today, it would be more like Facebook in 12 minutes.

Squidhead can create its own ColdSpring configuration files. Not earth shattering but makes integrating a Squidhead application with an application already in ColdSpring much easier.

So there you go, quite the update over the past few weeks.

November 21, 2007 Posted by Terrence Ryan at 12:31 AM

Squidhead Presentation

I've posted my Squidhead Presentation that I gave last night at the Philly CFUG.

November 14, 2007 Posted by Terrence Ryan at 5:27 PM

MSSql vs MySQL vs Oracle, Stored Procedures, and Code Generation

Over the past two weeks I've been struggling with adding support for Oracle in Squidhead. (Expect a release early next week.) Oracle was more different from both Microsoft SQL and MySQL than they were from each other. It got me thinking about the three and their various pros and cons for code generation, and their pros and cons for me doing code generation with stored procedures. I figured it could make a good blog article. (Or someone could see my post and say "Hey you didn't have to do all that because of the blah command!")

Microsoft SQL

This was the first one I started with, so the others are being compared to it. It comes with the built in stored procedure sp_help, which is absolutely brilliant. It gave more information for tables than I ever needed. It also gives most of the information for a stored procedure that I need. Although there are a few things lacking which made me have to resort to using sp_helptext, and then parsing the actual stored procedure. I had to do this to get the number of result sets that a stored procedure returns. (Because if a stored procedure returns more than one record set, Squidhead writes the method to return a structure of queries instead of just a query. ) Also I had to do this to get the metadata of the arguments being passed to a stored procedure.

MySql

Of the three this was the easiest to do. I had a flash one night ("just implement sp_help's queries in MqSQL") and it took me less than 24 hours to add it. Nonetheless I still had to query a lot of system tables directly to get the results I wanted.

There are few other limitations that bug me, but they aren't related to code generation. Things like stored procedures not being able to take default values in their inputs, and views not being able to contain subqueries. I'm sure as those features mature though, we'll see better results there.

Oracle

Finally, we reach Oracle. It took me a week of hair pulling, keyboard slamming and muttered curses to get it right. But finally I did.

What I didn't like:

One has to create a cursor to contain output recordsets for a stored procedure. That's so different than the other systems that I wanted to kill someone. Although, I must confess it makes counting output record sets much easier.

That everything is limited to a name length of 30 characters. So I had to get creative with how to deal with stored procedures with names like usp_entryToComment_list_foreign_key_labels. The solution I came up with was to abbreviate them and rename them higher up in the Squdihead stack so that the functions have the "correct" names.

What I liked:

Packages. Packages are sort of like classes or CFC's for Oracle operations. The encapsulate code into discreet chunks. I basically create a package for every table so my stored procs have names like ENTRYPKG.READ which is better in my mind than usp_entry_read as that's just a way I use to group and sort stored procedures anyway.

The sheer amount of data in the various data dictionaries. I don't have to parse any code strings to get any metadata. That's much easier.

"CREATE OR REPLACE." Truly awesome. Cuts down on the amount of steps I have to do to recreate a stored proc or package. I wish I had known it exists for MySQL.

Conclusion

It's kinda funny, and it may be cognitive dissonance talking, but of the three, I really like Oracle the best for code creation and metadata. Overall, I still lean towards Microsoft Sql as it is the one I know the best. Also whatever your thoughts on Microsoft Sql Server Management Studio, it's so much better than SQL developer for Oracle. But that's a completely different topic.

November 3, 2007 Posted by Terrence Ryan at 7:10 PM

Comments (6) | TrackBack (0)

Presenting at the Philadelphia CFUG 11/13

I'll be doing a presentation and little demo of Squidhead at the Philadelphia Area CFUG meeting on Tuesday November 13^th.

I'll be going over the reason behind the application and what it can do. As for the demo, have you seen my Facebook in 17 minutes demo? Well, I'll be doing that, in about half the time. (I've made some improvements to Squidhead in the interim.)

November 1, 2007 Posted by Terrence Ryan at 9:54 PM

Squidhead ColdFusion Code Generator

Squidhead Mailing List

Inspired by Sean's example, I asked myself, "Why doesn't Squidhead have a mailing list?"

"I don't know," I replied.

So now I have one. Feel to join and pester myself and the 2 other people that will join.

October 24, 2007 Posted by Terrence Ryan at 5:53 PM

Why Use Squidhead?

Squidhead has gone through a little resurgence since I got back from MAX. It's happened for two main reasons.

I'm using to it to build a more complex application
I talked to a few people about it, and they actually liked it

But it occurs to me that I could/should do a better job explaining why you might want to give it a longer look.

Squidhead might be for you if:

You or your organization requires a generator that uses stored procedures instead of inline SQL calls
You or your organization prefers using foreign keys and wants a generator that can use them to discover business logic
You want to fool around with code generation but don't the like MVC code that many of the others use.

Squidhead might give you an excuse to:

Work with Ant, because of the build.xml files it creates for itself.
Experiment with Unit Testing, because it builds its own CFUnit Tests and runs yours too

Squidhead isn't for you if:

You find the name "Squidhead" ridiculous.
You're already using a more established generated or framework

If you're interested, here are some resources for you:

October 17, 2007 Posted by Terrence Ryan at 9:56 PM

Comments (4) | TrackBack (0)

Facebook Demo Part 2

Um, hey, did some of you try and download the Flash version of the video? Um, yeah, and did it crash your browser, and possibly crash your computer?

Sorry about that.

I've fixed the Flash version and it shouldn't do that anymore. I should have gone with Flash Video encoder in Flash CS3 from the get go.

Flash Version

October 8, 2007 Posted by Terrence Ryan at 9:10 PM

Facebook in 17 Minutes

I've long thought about doing a blog in 15 minutes demo of Squidhead. But come on, everyone's done one by now, right?

Then I thought about ColdFusion 8 and its image capabilities, and the image CRUD that I've added to Squidhead and thought maybe a facebook type application would be more compelling. So I did it, and put it up on Google Video. (cause I couldn't get it under 10 minutes.) Then I saw it on Google video and was repelled by the quality of the video. So I have few options to watch it below.

I show off three features of Squidhead in the video:

Image CRUD interface
Foreign Key usage
Scaffolding your stored procedures.

If anyone watches it, please let me know what you think.

UPDATE: It appears that the Flash Version will cause your computer to crash. Sorry about that. I will have a replacement up at some point in the very near future.

UPDATE:Flash Video version works now.

Google Video Version (pretty blurry)
Flash Version (Takes a long time to download)
Windows Media Version (download, still pretty long to download)

October 7, 2007 Posted by Terrence Ryan at 6:13 PM

Comments (3) | TrackBack (0)

Squidhead 2.0

I've been noodling here and there and have finally put the finishing touches on Squidhead 2.0.

Before I started rewriting, I spent some time developing more applications with Squidhead. I figured out a couple areas of inefficiency and tried to fix them when I could. Creating configuration files took longer than it should and was a bit confusing. I found myself writing Ant scripts for all of my applications. I kept forgetting to test my applications.

Further when I was maintaining the actual Squidhead codebase, some things were frustrating. Maintaining a separate code base for ColdFusion 8 and ColdFusion 7 was a pain in the ass. Maintaining a simple version and a business version was a pain. Adding new features required a series of cascading tweaks because it was poorly architected.

So here are the solutions to all of those issues and a few more for good measure.

New features:

MySql 5.0 Support
It creates Ant Build files for common tasks
It can run CFUnit tests that it creates during its build
It now will use CF8 rich elements it the generated crud *
It now handles images *
It's now a little easier to extend
- It's easy to add steps.
- It's possible to add application templates
Modified XML configuration allows developer to override certain defaults like form field labels.
New Configuration Builder
New Shiny Web 2.0 looks (Okay, not a feature per see.)
Cheesey Web 2.0 Logos created for every application.

* CF 8 features can be turned off if application will not be running on a CF8 server.

Deprecated Features:

Simple Applications (They are still available, but they won't be updated)

Removed Features:

Threading (Speed Enhancements weren't enough to justify added complexity)

Requirements:

Squidhead: ColdFusion 8
Database: Microsoft SQL 2000 or 2005; MySql 5.0
Generated Application Using MSSQL: ColdFusion 7 or 8
Generated Application Using MySql: ColdFusion 8

Notes:

For the most part, your old config files will still work. However the more changes you made the more likely they are to have problems.

Old versions of Squidhead still available at http://www.numtopia.com/squidhead.

The new version is available at Riaforge.

September 23, 2007 Posted by Terrence Ryan at 2:35 AM

CFUnit and CFAnt

I'm working on some improvements to Squidhead, and a few of them relate to Ant. The new version of Squidhead creates Ant build files. In addition to Ant tasks for rebuilding and refreshing the application, there is also one for running all of the CFUnit tests that Squidhead creates.

Since I'm trying to highlight the ability to add steps to Squidhead applications, I was thinking that it might be cool to not just write the tests as part of the Squidhead build, but also run the tests as part of the Squidhead. Including the ColdFusion autorunner for the reports didn't work. Because both CFunit and Squidhead rely on <cfflush>. So I fired up CFAnt to see if it would run. It didn't. It through an error that stated: BUILD FAILED… askdef class net.sourceforge.cfunit.ant.CFUnit cannot be found.

I copied the version of cfunit-ant-v3-alpha.jar I use with Eclipse to my ColdFusion8/lib directory, restarted the server and voila: BUILD SUCCESSFUL.

Ant in ColdFusion is cool.

Oh and I imagine the same tip would help you to run CFCUnit tests to run from <CFAnt>

September 19, 2007 Posted by Terrence Ryan at 1:49 AM

Comments (1) | TrackBack (0)

Squidhead Future

I'm doing some serious thinking about the future of Squidhead, and I some stuff I wanted to run by the small but plucky group of Squidhead users that are out there.

It's gotten a little unwieldy to do updates for both a ColdFusion 7 and 8 version of the feature set. Additionally, the whole business objects and non-business objects version is also getting hard to support. Finally, there are a whole bunch of features I would like to support but require ColdFusion 8. So here is what I am proposing:

Ceasing development of the current version of Squidhead.
Starting a new development track that required ColdFusion 8 for application generation. (But then generated application could run on ColdFusion 7.)
Dropping the non-business objects version of the code.

Once I did that, I could do the following:

Switch database analysis over to use <cfdbinfo> for most, but not all of what I do.
This would make adding support for other DBMS's easier

I'd be happy to hear any feedback you might have on these proposals. Assuming no one objects, I would publish one last version of the old code as 1.0, and then start pruning and working on what I would call version 2.0. (I would also come up with a more standard versioning system.)

August 26, 2007 Posted by Terrence Ryan at 11:36 PM

Comments (5) | TrackBack (0)

Squidhead and CFUnit

I published a change to Squidhead earlier today that included an option for Squidhead to write its own unit tests today. These unit tests are compatible with and require CFUnit. Here's a little bit about the reasons behind this.

I originally started fooling around with unit testing because after drinking Pragmatic Programmer Kool-Aid, and attending cf.Objective, it was the next thing on my list. I figured I would be pulling them into Squidhead, as it's pretty easy to tell if Squidhead worked (the application is either written or not,) but it's hard to tell if the created application is working. So I started to write unit tests for my DAO Objects figuring it would be an easy place to start. Once I got one working, I realized it was relatively rule based and could stand to be generated itself.

What it does is:

Create a mock struct of the record.
Create a mock struct of the record slightly different from the first.
Create a record from the mock record.
Read it back and compares it to the source.
Update it according to the different mock record.
Read it back and compares it to the source.
Delete the record
Read again to make sure the item is gone.

All that is wrapped in a <cftransaction> making sure the thing never happened. Additionally it reads in and honors foreign key constraints. It's got some weaknesses: it doesn't like binaries or images; its smalldatetime check is only accurate to the hour; and there are probably more bugs lurking.

Now once I got them working, they were in, but what's the point for users of the application? One should expect that the parts work.

So to make this worthwhile I added some logic that will run any test that is in the test directory structure. So if you're using Squidhead and looking to dip your feet in the waters of CFunit or unit testing in general here's your chance.

If you're wondering why CFUnit, and not CFCUnit, it's because as far as I can tell CFCUnit requires Mach II, and I figured one requirement was better than two.

So if anyone has any suggestion on how to test gateways or dao's I'd be curious. Otherwise check it out.

May 24, 2007 Posted by Terrence Ryan at 9:50 PM

Comments (7) | TrackBack (0)

Squidhead - 3 Big Additions

Over the last few days, between the inspiration I got from cf.Objective(), and some positive internal feedback regarding Squidhead, I was inspired to make some cool additions (Or what I consider cool editions.) I figured I would share.

Business Layer

Dave Konopka advocated for a layer between the DAO and UI for awhile, as I explain in my last Squidhead update. Well he finally got it. Basically it's the place where you want to put all of your logic for dealing with the objects as they will be interacted with by the UI.

XML Structure Cache

I wanted to do a few things to overcome the fact that the database is does not contain enough metadata to handle certain things. But I didn't want people to have to start with XML to start working with Squidhead. Solution: an XML version of the variable structure that Squidhead uses to generate all of the procedures, functions, cfc's, custom tags, and cfml pages for an application.

So what happens is that at some point you freeze changes on the database, then you set Squidhead to use the disk cache instead of the database. It will then allow you to change a couple of things in how the application works.

Currently you can only change three things (and have the change do something useful):

DisplayName on database columns that will show as labels on all UI components instead of the name. ("First Name" vs f_name).
OrderBy on tables which will alter the order of the default List elements
ForeignKeyLabel the column which will be substituted for the primary key, in all tables that link to this table via foreign keys.

Which leads me to...

Foreign Key Support

Squidhead now understands foreign keys. Here's how it works:

Form UI - On forms that interact with tables with foreign keys, Squidhead will produce a select box of the aforementioned "foreign key labels."

List and Read UI- These components now call separate stored procedures that properly join the requisite tables to show the appropriate "foreign key label" in lieu of the actual value.

I haven't done anything with children objects or anything yet. Basically the change was to make the UI a little more useful.

My next pass of updates will be documentation related, as I've fallen behind.

Anyway give a try when you get a chance. Get it at RiaForge.

May 11, 2007 Posted by Terrence Ryan at 3:45 PM

Squidhead Update 5/2007

Squidhead is swimming along nicely. I've gotten some good suggestions, over the last few weeks, and figured I would post what's going on.

Dave Konopka, who works with me here at Wharton, has a pretty good complaint about Squidhead. Basically his complaint is that there needs to be another layer between the DAO and UI. We've taken to calling it the Business layer. Basically, many times his basic interaction with the database requires more than just one DAO operation, and often other operations not related to the database (logging, email, etc.) So to achieve perfect encapsulation and reusability through the application, he requires the business layer.

He provided me with a good bit of code for it, which will create Business beans for any of the DAO operations. Additionally it abstracts the whole "Is it an add or is it an update" logic, making the UI have to do less work.

I've added the business CFC creation routine to Squidhead and am working on rewiring the UI to use it. It will be optional, so that existing applications don't break, but it will be set to the default way of doing stuff.

Finally, I did get some requests for Fusebox integration, and MySQL compatibility. I'm working on those, but Dave's in my office, so he gets to sell me this stuff right to my face.

May 2, 2007 Posted by Terrence Ryan at 11:47 AM

Comments (3) | TrackBack (0)

Squidhead Swims On

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.

March 27, 2007 Posted by Terrence Ryan at 8:07 PM

Poor, Slightly Misunderstood, Squidhead

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.

March 5, 2007 Posted by Terrence Ryan at 10:31 PM

Thinking About Code Objectly

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:

</cfstoredproc>

</cffunction>

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:

Documentation	<!---******************************---> <!---create---> <!---This function inserts a single comment record into the database.---> <!---******************************--->
Header	<cffunction access="public" name="create" output="FALSE" returntype="numeric" hint="This function inserts a single comment record into the database." >
Arguments	<cfargument name="body" type="string" required="FALSE" default="" />
	<cfargument name="authorID" type="numeric" required="FALSE" default="0" />
	<cfargument name="createdBy" type="numeric" required="FALSE" default="0" />
	<cfargument name="updatedBy" type="numeric" required="FALSE" default="0" />
Local Variables	<cfset var commentID = "" />
Local Variables	<cfset var results = "" />
Content	<cfstoredproc procedure="usp_comment_insert" datasource="#application.datasource#" username="#application.dbusername#" password="#application.dbpassword#"> <cfprocparam type="OUT" cfsqltype="CF_SQL_INTEGER" variable="commentID" dbvarname="@commentID" maxlength="4" null="NO" /> <cfprocparam type="IN" cfsqltype="CF_SQL_LONGVARCHAR" variable="body" dbvarname="@body" value="#arguments.body#" maxlength="16" null="NO" /> <cfprocparam type="IN" cfsqltype="CF_SQL_INTEGER" variable="authorID" dbvarname="@authorID" value="#arguments.authorID#" maxlength="4" null="NO" /> <cfprocparam type="IN" cfsqltype="CF_SQL_INTEGER" variable="createdBy" dbvarname="@createdBy" value="#arguments.createdBy#" maxlength="4" null="NO" /> <cfprocparam type="IN" cfsqltype="CF_SQL_INTEGER" variable="updatedBy" dbvarname="@updatedBy" value="#arguments.updatedBy#" maxlength="4" null="NO" /> <cfprocresult name="results" /> </cfstoredproc> <cfset results = commentID />
Results	<cfset results = commentID />
Footer	</cffunction>

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.

February 21, 2007 Posted by Terrence Ryan at 12:37 AM

MyWoes with Squidhead

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?

February 15, 2007 Posted by Terrence Ryan at 1:42 AM

Whoa

You want people to find every problem with your code and publicly point them out? I'm not talking about code reviews again; I'm talking about releasing Squidhead as Open Source on RIAForge. I haven't been this panicked about updates for awhile.

Not that I'm complaining. Every comment has been a move towards making it a better product, but I was unprepared for it. I missed a whole bunch of dumb things. (Dumb that I missed them, not that people want them.)

Thanks to everyone that downloaded it, and everyone who commented. I've made pretty much every recommendation, and fixed every bug report to date. With one exception that is. I haven't made any movement on a MySQL version. I want to get it to work right in MS SQL before switching to another database. Good news is that errors are being found at a good clip.

February 8, 2007 Posted by Terrence Ryan at 12:56 AM

ColdFusion, Squidhead,

Squidhead - released

I've published Squidhead up on RIAforge, and it is now available for download. It's my first project I've taken the time to open source, so I am very excited.

If you're wondering what I'm talking about, Project: Squidhead will explain.

Thanks to Ray, and all of the other good people who run RIAforge. I've liked it as a consumer of its content, now as publisher, I love it.

February 5, 2007 Posted by Terrence Ryan at 12:45 PM

ColdFusion, Squidhead,

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?

January 31, 2007 Posted by Terrence Ryan at 3:25 PM

Comments (9) | TrackBack (0)