Creating Readable SQL Statements In AIR (Adobe Integrated Runtime)
Last night, Jason Dean was awesome enough to hold a Connect session with Andy Matthews, Todd Rafferty, and myself in which he demonstrated how to build an HTML and jQuery powered AIR application. It was an awesome presentation and really opened my eyes. Jason has gotten me totally excited about playing with AIR. One thing that I mentioned during the presentation that I figured I would blog about quickly is a technique that I picked up for formatting structure string data.
For those of you who have followed my blog for any amount of time, you've probably come to realize that I love white space in my code (not my rendered output, my code). Too much of anything is a bad thing, but I find that the right amount of white space leads to superior scanning and readability. This is one of the main reasons that the CFQuery tags rocks the party that rocks the body. When moving to a script-based language, like Javascript or Action Script, often times this kind of readability is hard to keep when dealing with structured string data like a SQL statement.
Luckily, in script-based languages, often times you can use the back slash, "\", to escape special characters. When dealing with string data where the structure is complicated (think SQL statements, think building HTML strings), I like to leverage this feature to escape line breaks within string data:
| |
|
|
||
| |
 |
|
||
| |
|
|
As you can see, by placing a back slash as the very last character in a piece of string data, it escapes the line break, allowing the string value to continue on the next line without throwing any unterminated string errors. In doing so, it frees us up to format the string data in a much more human-readable way. Having to tab out the slashes can be annoying; but, the benefit to readability and maintenance is well worth the minor inconvenience.
Reader Comments
I don't understand why people don't use whitespace. It makes it so much more eye friendly. I think that my eyes need a break after looking over hundreds of thousands of lines of code, and using whitespace gives it a little bit of a rest instead of it all being clobbered together.
Hey Ben
Have you ever thought about creating a community CMS like joomla? I'm currently working on the Framework for something similar.
@Ben, that seems just .. ew.
Any time you want to modify your statement in one small place (another join and another selected field?), you have to go through and re-align the whitespace every single line of that statement.
And ... while I'm agnostic on the lovely tabs-vs-spaces holy war ... I find that I only like tabs when they are used to align the *beginnings* of lines, and very much dislike them when they are used to align characters within lines. Tab has no definite width whatsoever, and changes from editor to editor. Some editors show it as two, some as three, some as four, some as eight spaces. Unless someone else is using a compatible editor, the alignment will be very off for him. And then if he is also working on the project, sooner or later you both will end up with horribly misaligned blocks of code all over the place.
So, I don't think I could use that style.
In further news, I tend to prefer using a function to generate SQL statements in all the trivial cases. Merging multiple languages into the same code at the same time (a single page with all CF, SQL, HTML, CSS, JS) seems to me to be a very bad habit with adverse outcomes in all but the simplest cases: it's ok to have a 1000 line application with mixed languages in fifteen files, but it's not a good idea to have a 1,000,000 line application with mixed languages all across 5,000 files.
The function to generate a SQL statement for an insert is usually rather trivial. Plus, if you implement a provider model, you could easily write some simple providers for different SQL dialects (Oracle, MySQL, PostgreSQL, SQL Server, Informix, DB2, SQLite, Firebird, Apache Derby, etc.). The provider interface would be the same across all providers, but each provider would emit slightly different SQL depending on the database (e.g. how to quote names, how to quote values, any differences in statement syntax, etc.). You could select which provider to use once in your application, write all of your trivial SQL queries and statements against that provider model, and only use native SQL when you need to because you can't come up with a good way to implement what you need in a generic way. You could then develop most of your application against an SQLite database, while using an expensive Oracle cluster in test and production, using exactly the same function calls in most places to generate the SQL that you need.
@Jody,
Regarding the community CMS, have you looked at Mura CMS? It's all on top of ColdFusion: http://www.getmura.com/
@Jody,
I have not thought about this, at least not much. But @Steve is right, Mura CMS looks really awesome; they came to our CFUG this past meeting to present and it looks like a really powerful app.
@Justice,
You make a good point about the spacing of tabs across various editors. I suppose in that case, you could simply put the back slash after the last character on the line (after a space) rather than tabbing it out. It might be a bit more distracting that way, but the updates would be much easier.
As far as taking this one tiny concept and raising the question of a whole SQL creation framework, I like your big thinking :) However, I think we can all agree that creating abstract factories to create our SQL statements is probably fodder for another blog post (probably even a book) unto itself.
Spotted the 'Mura' mention and wanted to give it a quick thumbs up. I'm more designer than programmer but have really come to enjoy the power and ease of use of this CMS. They've added an architecture for plugins and I am looking forward to seeing what evolves.
The only language that i have ever seen that gets the treatment of SQL right is CFML! Long live CFQUERY!
@Patrick,
Ha ha, word up - ColdFusion CFQuery tag is just awesome.