Friday, October 30, 2009

Oracle SQL Coding Standards, Or the Dearth of Them

So here I am, laboring furiously to get my refactoring tools polished up for Insight, and the one feature that I would really like to get implemented is a spiffy coding-standards compliance tool for SQL. One would think that it would be a simple matter of digging up the most widely accepted standard, allowing the user to modify that standard to suit their firm’s needs, and then applying it to the script in question. And that isn’t really rocket science. Except for one thing.

There isn’t really one widely-accepted PL-SQL coding standard.

Now, I’m not going to just come out and say that Oracle is horrible at documentation. No, wait, I am going to say that Oracle is horrible at documentation. Aside from the fact that the vast majority of it is nearly incomprehensible, incomplete, and badly formatted, they tend to gloss over things that concern the really important folks: those who have to use the damned thing.

A coding standard is a really important thing. Without it, you end up with ten-year-old databases that have passed through a plethora of hands and no two sets of hands agree about how the code should be laid out, how the variables and objects should be named, how things should be cased, and where commas should be placed. Sounds trivial, I know. But when you have to come in ten years later and try to reconcile all those competing non-standards into a single, cohesive “product” that makes sense so that you can maintain it, it’s a nightmare.

We need a driving vision for a SQL coding standard. And we need it from a single source, with some authority behind it. But it can’t just establish a standard that comes down with some heavy-handed practice that isn’t thought out. As much as I hate to admit it, Oracle has been doing this a long time, and they’re the logical choice to start this movement, with lots of input from the community.

On the other hand, this could be an entirely community-driven thing, a grass-roots effort to establish a common SQL coding standard. On this blog, Lewis Cunningham discusses such a project, but it doesn’t appear to have taken off. It’s a pity, because I think it’d be well worth the effort.

Who knows, maybe I’ll slide on over to Google Apps and see what I can do to start such a grassroots effort myself. I’m tired of sitting around waiting for a consensus to just pop up out of thin air. You’ve got to start somewhere, right?

Wednesday, October 21, 2009

Smug Bastards, Toy Databases, and Ease of Use

Alarmingly, I cannot find anything in this post that I disagree with. I don't know whether that makes me proud, smug, or ashamed. Either way, I laughed my ass off.

Wednesday, October 14, 2009

The Cost of a Quick Second

So, here I am, cleaning up a rather large code base, and I find myself in the throes of retrofitting properties with attributes. Now, when I wrote these properties, the prevalent thought at the time was that I just had a crap-load of properties to write, and tons of business code that needed them. So, in a bug hurry, I slapped together some C# code that spit them out. You know, kind of like this:

public string Owner { get; protected set; }

Now, at the time, that’s all well and good, because at some point, the work just has to get done. But then, I realized that the software I’ve been working on is actually very useful and that I want to polish it up and get it ready to release it to the masses. But I discover, to my utter horror, that the source code is an absolute disaster because, well, I didn’t write it with the intention that anyone else was going to actually look at the source code.


Sure, the thing looks beautiful from the outside. And it does what it’s supposed to do (by and large). And it’s fast. Very fast. But please, please do not look at the plumbing. Or the wiring. The vinyl siding is Fabulous. But don’t look underneath it.


Hence, the retrofitting.


I’m sure you’ve been there. Don’t look at me like that. I know we’ve all written crap code slapped together in a hurry because we needed something just this second and it will only take a second to do.


Which leads me to my point: A quick second now will cost you hours later.


This particular project involves two separate assemblies. A Class Library DLL, which houses all the logic, and a Windows Forms Application which references the Class Library. The problem is that the class library is missing the bulk of its XML documentation comments, and the properties aren’t properly marked with attributes. Silly me.


So now, I have to go back through the code, one file at a time, and mark up my source code. Trust me, this is no picnic. Fortunately, I believe in good, clean names, so the code is largely self-documenting. But there have been a few surprises here and there. But going back through any source file and adding the comments after-the-fact is slow and tedious work. You shouldn’t put yourself in a position to do it in the first place. Comment up front when the purpose of the property or method is fresh in your mind.


When it comes to property attributes, you’d be amazed at how many there are:



  • DefaultProperty. This attribute doesn’t go on a property, but marks up the class to indicate the name of the property that should be treated as the default.
  • ReadOnly. Use this attribute to specify that a property is read-only in the property grid.
  • Description. Use this attribute to specify the description that appears in the property grid, or in the tooltip that appears when the user hovers over the property in the editor.
  • Category. Use this attribute to specify the category that the property is grouped under when the property appears in the property grid.

These are the few that I’m primarily interested in. So, for each property, I have to move along as follows:

/// <sumary>
/// Gets the owner of the view.
/// </sumary>

[ReadOnly(true), Description("Gets the owner of the view."), Category("Schema")]
string Owner { get; protected set; }

This gets a lot more complicated if I’m documenting a method, as you can imagine. Then we’re dealing with parameters, return values, and all that other hooplah because the consumer isn’t familiar with the code like I am.


Refactoring tools can simplify some of this stuff. Some software tools boast the ability to generate documentation comments for you, but my experience with them have been less than rosy.


So take some advice. Spare yourself a little effort down the road. Document and mark up your code as you go along. It’s worth the time to do it up front. I have to go through this code base and get this stuff cleaned up now. The only good thing I can say about it is that it’s giving me a really good chance to go through the code with a fine-toothed comb and clean it up, remove dead code (oh, for an open-source tool for that!), and eliminate some redundancies. Aside from that, I’ll be kicking myself in the future for taking hasty shortcuts like these.


You should be too.