Tag: Web Development

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.

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

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.

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.)

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.

Squidhead ColdFusion Code Generator

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

1. I’m using to it to build a more complex application
2. 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:

• Squidhead at RIAForge
• Squidhead documentation online