Re: Databases as objects

Sampo Syreeni wrote:
> On 2006-12-22, Thomas Gagne wrote:
>
>> I could write an entire program with inlined C code and not use
>> macros or functions. But functions improve modularity, readability,
>> reliability, and reusability. Why wouldn't SQL benefit from the same
>> organization?
>
> Because relational algebra is rather different from your typical
> procedural host language: it is a high level, declarative language, it
> deals with entire sets of things at a time, and it's highly productive
> (whatever you get back from a query can be used freely in formulating
> new queries). When you wrap it up in a number of procedures -- in most
> cases a much lower level abstraction -- you usually end up losing most
> of the expressivity and compositionality that made the relational
> model attractive for data management in the first place.
But relational algebra, or practically speaking--SQL, doesn't exist for its own sake any more than pseudo-relational databases (PRDBs) exist for their own sake. They profitably exist to solve problems and automate. A brilliantly designed PRDB may do a splendid and gratifying job maintaining the state of a system, but in practice it is sometimes necessary (less frequently than most think) to compromise the design (denormalize) to improve performance. SQL may be a grand tool to manipulate data, but wholly inappropriate for end-users to order books and DVDs from Amazon's website.

At some point, all that expression and composition has an ingress and egress, and what lies between them is a reusable, repeatable code. No one really wants to write all that SQL by hand every time so it's put inside a macro. To increase the macro's value parameters are applied to make it a function. Now we're cooking with fire.

But now I'm confused why the value of evolving loose SQL to functions seems to escape c.d.t. Perhaps we're talking passed each other?

>
> For example, you might package the lookup of a single customer's
> details as a procedure, but if you then wanted to do something to all
> customers, the best that a procedural host language is usually able to
> offer you is a cursor loop.
Why wouldn't I use another procedure that either uses the results of the first, or includes the salient SQL of the first so it may perform a subsequent relational operation?
> Using something like that is obviously a bad idea, because this sort
> of thing is much more naturally and efficiently implemented as a set
> update, optimized by the DBMS.
Agreed.
> The problem is that after you've expressed that set update as a low
> level construct like an explicit loop, the compiler cannot be expected
> to be intelligent enough to figure out that you actually wanted to
> quantify over a set; going from high level abstractions to low level
> details is much simpler than the converse.
Agreed again. That's why I wouldn't do it that way. Besides, involving the application in that kind of looping seems to violate the cohesiveness of the DB--it can do it much more efficiently. One reason I can think NOT to do it inside the database is if there was another parallel operation that must be performed with each update. For instance, our commercial finance system doesn't update anything without leaving an elaborate audit trail behind so we can produce the equivalent of your bank statement. The business reasons for when, what, and how don't exist inside the DB. Triggers, though useful for single table updates, are all thumbs for multi-table updates.
> Of course you could then tell to the compiler what it doesn't
> understand: just implement a new method which encapsulates the set
> update. But how is it cleaner or more productive to have umpteen
> methods to do various specialized things to your database, than to
> have the small set of closed, general, high level, declarative
> primitives that the relational algebra represents?
Except for, well, exceptions to things which are at best awkwardly implemented with set operations, I think we agree.

-- 
Visit <http://blogs.instreamfinancial.com/anything.php> 
to read my rants on technology and the finance industry.