Why are String Formatted Queries Considered So Magical?

[Owen Jacobson]

On 2010-06-27 22:51:59 -0400, Carl Banks said:

> On Jun 27, 3:20 pm, Roy Smith <r... at panix.com> wrote:
>> In article
>> <14e44c9c-04d9-452d-b544-498adfaf7... at d8g2000yqf.googlegroups.com>,
>>  Carl Banks <pavlovevide... at gmail.com> wrote:
>> 
>> 
>> 
>>> Seriously, almost every other kind of library uses a binary API. What
>>> makes databases so special that they need a string-command based API?
>>> How about this instead (where this a direct binary interface to the
>>> library):
>> 
>>> results = rdb_query(table = model,
>>>                     columns = [model.name, model.number])
>> 
>>> results = rdb_inner_join(tables = [records,tags],
>>>                          joins = [(records.id,tags.record_id)]),
>>>                          columns = [record.name, tag.name])
>> 
>>> Well, we know the real reason is that C, Java, and friends lack
>>> expressiveness and so constructing a binary query is an ASCII
>>> nightmare.  Still, it hasn't stopped binary APIs in other kinds of
>>> libraries.
>> 
>> Well, the answer to that one is simple.  SQL, in the hands of somebody
>> like me, can be used to express a few pathetic joins and what I do with
>> it could probably be handled with the kind of API you're describing.  
>> But, the language has far more expressivity than that, and a
>> domain-specific language is really a good fit for what it can do.
> 
> I'm not the biggest expert on SQL ever, but the only thing I can think
> of is expressions.  Statements don't express anything very complex,
> and could straightforwardly be represented by function calls.  But
> it's a fair point.

Off the top of my head, I can think of a few things that would be 
tricky to turn into an API:

 * Aggregation (GROUP BY, aggregate functions over arbitrary 
expressions, HAVING clauses).
 * CASE expressions.
 * Subqueries.
 * Recursive queries (in DBMSes that support them).
 * Window clauses (likewise).
 * Set operations between queries (UNION, DIFFERENCE, INTERSECT).
 * A surprisingly rich set of JOIN clauses beyond the obvious inner 
natural joins.
 * Various DBMS-specific locking hints.
 * Computed inserts and updates.
 * Updates and deletes that include joins.
 * RETURNING lists on modification queries.
 * Explicit (DBMS-side) cursors.

This is by no means an exhaustive list.

Of course, it's possible to represent all of this via an API rather 
than a language, and libraries like SQLAlchemy make a reasonable 
attempt at doing just that. However, not every programming language has 
the kind of structural flexibility to do that well: a library similar 
to SQLalchemy would be incredibly clunky (if it worked at all) in, say, 
Java or C#, and it'd be nearly impossible to pull off in C. Even LDAP, 
which is defined more in terms of APIs than languages, forgoes trying 
to define a predicate API and uses a domain-specific filtering language 
instead.

There's certainly a useful subset of SQL that could be trivially 
replaced with an API. Simple by-the-numbers CRUD queries don't exercise 
much of SQL's power. In fact, we can do that already: any ORM can 
handle that level just fine.

-o