One guy's take on the web, programming, cigars, politics, Philadelphia, and whatever else comes to mind.
I got to welcome my first child into the world yesterday. John Patrick Ryan. He was 8 lbs 5oz, and 21 inches long. Mom's pretty beat-up, but Jack is doing just fine. I may be biased, but he is perhaps the most perfect child ever born. That's all; I've got to get back to the hospital.
Picture should be visible if you're on my site.
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:
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 made a little update to the CFAir compiler package. I tweaked the generated application to be a little more complicated, and CFAir still works on it. There's a screenshot to the right (If you're own my site.)
I've posted my Squidhead Presentation that I gave last night at the Philly CFUG.
I made some changes to the CFAIR compiler project.
Additionally I added a screenshot of the image to the RIAForge site.
So check out CF Air Compiler, and please let me know what you think of it. Is this worth developing further? Or should I just wait for Centaur? Anybody have any suggestions or ideas on how to proceed?
I was inspired by Ben Forta's post on creating AIR application from ColdFusion. I felt like there had to be a better way than just copying the source of ColdFusion generated HTML files. So I started working with it and came up with a proof of concept project on RIAForge: CF Air Compiler.
I wrote a simple application called "testapp" that just binds a CFGrid to the results of a CFC call that returns a CFFeed of coldfusionbloggers. This is the application that will be packaged into an Air application.
I used CFHttp to grab the ColdFusion page outputted as HTML and move them to a repository. Then I used ReMatch to find all of the used CFIDE javascript files, css files, and images, and copy them to the repository. Then I used ReMatch to grab all of the cfc calls and convert them to fully qualified url calls.
The second part was a little easier. All it required was wrapping the adt.bat in a CFC wrapper. It just has two methods: certificate and package. They correspond to the options for the adt.bat file. I used that to compile the application into AIR.
There were a few challenges with it. I haven't figured out a way to get to the images used in CSS. I could just grab them indiscriminately, but I want to do a little better, expect an update after I think about it for a bit. Also the CF JavaScript evidently tries to do some things that violate the security model of the Air Runtime. It required wrapping the ColdFusion generated html in an iframe. To read more about the issue, you can check out the Air Wiki.
It's not perfect code, but I figured I would at least get it out there and maybe someone smarter could take it a little further. Check it out, please feel free to offer suggestions and criticism.
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 13th.
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.)
| Su | Mo | Tu | We | Th | Fr | Sa |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | ||||
| 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| 11 | 12 | 13 | 14 | 15 | 16 | 17 |
| 18 | 19 | 20 | 21 | 22 | 23 | 24 |
| 25 | 26 | 27 | 28 | 29 | 30 | 31 |
adobemax06 adobemax07 air Air Basecamp CFC Cigar Reviews Cigars ColdFusion Entertainment Film Flex Internet Meme Meta Blogging Movable Type Personal Philly Philly Mayor 2007 Places to Smoke Politics Random Religion Running a ColdFusion Shop Squidhead svnAuth.cfc TV Web Development Who Needs A Cockpunchin' ? Writing
Squidhead creates scaffolds for MS SQL based applications using stored procedures.
Copyright 2003-2008 Terrence Ryan.
This is a personal blog. The opinions expressed here are mine and not those of my employer.