Why are String Formatted Queries Considered So Magical?

[Carl Banks]

On Jun 27, 8:19 pm, Owen Jacobson <angrybald... at gmail.com> wrote:
> 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.

I don't know the exact details of all of these, but I'm going to opine
that at least some of these are easily expressible with a function
call API.  Perhaps more naturally than with string queries.  For
instance, set operations:

query1 = rdb_query(...)
query2 = rdb_query(...)

final_query = rdb_union(query1,query2)

or

final_query = query1 & query2

I'm not sure why GROUP BY couldn't be expressed by a keyword
argument.  The complexity of aggregate functions and computed inserts
comes mainly from expressions (which Roy Smith already mentioned), the
actual statements are simple.


> 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.

Yeah, which was kind of my original theory.


Carl Banks