SQL as metaprogramming
I'll start by quoting Glyph Lefkowitz from a recent article (okay, recent for my techblog writing):
Metaprogramming is hard, and dress it up however you like, that's what using SQL is. Your code is generating other code, and evaluating its results.
Because metaprogramming is so hard, it is almost exclusively the province of frameworks, environments and operating systems. For good reason, too. Whenever code generates other code, there are potentially very serious mistakes that can get made. [...]
Read the whole thing; it's worth it, and he elaborates on the idea.
SQL is an especially dangerous form of metaprogramming because it isn't just generating code on the fly; it's generating text on the fly and evaluating it as code. Pretty much everything that does that has all sorts of interesting issues, as Glyph points out indirectly.
Metaprogramming is hard in part because it's an extra level of indirection. Because SQL is such a simple 'programming language', it's easy to write it by hand; this tempts people into thinking that it should be equally easy to dynamically generate it in programs. Text-based metaprogramming adds a second level of indirection; you're not creating code, you're creating text that will create code. Overlook something in the translation process and you have an explosion or a security hole.
The general wisdom I've absorbed for dealing with SQL in programs is to lock the all SQL up in a low-level module that handles the jobs needed by the rest of your program. This is essentially a mini framework, and the more you make things OO the more it turns into a general object-relational mapper.
(Mind you, I'm not sure that I like the level of magic required by ORMs, generic SQL frameworks, and so on. Mini-frameworks have the advantage of being small and limited, with few moving parts to worry about.)
|
|