On Jun 27, 8:19 pm, Owen Jacobson <angrybald...@gmail.com> wrote: > On 2010-06-27 22:51:59 -0400, Carl Banks said: > > On Jun 27, 3:20 pm, Roy Smith <r...@panix.com> wrote: > >> In article > >> <14e44c9c-04d9-452d-b544-498adfaf7...@d8g2000yqf.googlegroups.com>, > >> Carl Banks <pavlovevide...@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 -- http://mail.python.org/mailman/listinfo/python-list
