Why are String Formatted Queries Considered So Magical?
in [Python]
|
From: Owen Jacobson on 27 Jun 2010 23:18 On 2010-06-27 22:51:59 -0400, Carl Banks said: > On Jun 27, 3:20�pm, Roy Smith <r...(a)panix.com> wrote: >> In article >> <14e44c9c-04d9-452d-b544-498adfaf7...(a)d8g2000yqf.googlegroups.com>, >> �Carl Banks <pavlovevide...(a)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
From: Owen Jacobson on 27 Jun 2010 23:19 On 2010-06-27 22:51:59 -0400, Carl Banks said: > On Jun 27, 3:20�pm, Roy Smith <r...(a)panix.com> wrote: >> In article >> <14e44c9c-04d9-452d-b544-498adfaf7...(a)d8g2000yqf.googlegroups.com>, >> �Carl Banks <pavlovevide...(a)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
From: Ben Finney on 27 Jun 2010 23:33 Carl Banks <pavlovevidence(a)gmail.com> writes: > On Jun 27, 4:35 pm, Ben Finney <ben+pyt...(a)benfinney.id.au> wrote: > > Carl Banks <pavlovevide...(a)gmail.com> writes: > > > Seriously, almost every other kind of library uses a binary API. > > > > Except for the huge number that deal with text protocols or languages. > > No, not really. Almost all types of libraries have binary APIs, > including those that deal with text protocols or language. Any > control with string commands is something that's built on top of the > binary API. I don't know what you mean by this. Are you referring to the operating system's function call API? It's trivially true that the OS function call API is “binary”, but that doesn't seem useful for distinguishing; by that definiition, SQL isn't a “library API” at all. So I assumed you didn't mean that. Rather, I was taking you to mean the network API used for communicating with the server; and it's in that context that I'm saying there are a huge number of text-based network APIs. If that's not what you mean either, then I need you to explain. > I'm disappointed, usually when you sit on your reinforced soapbox and > pretense the air of infinite expertise you at least use reasonable > logic. Kindly stop inventing straw men to attack; I deny the position you're painting for me. > Also, I was asking about databases. "SQL is a text language" is not > the answer to the question "Why do RDBs use string commands instead of > binary APIs"? To that question, I'd say that SQL isn't a library API, but rather a network API and a command API, and is thus well implemented with textual commands. -- \ “[W]e are still the first generation of users, and for all that | `\ we may have invented the net, we still don't really get it.” | _o__) —Douglas Adams | Ben Finney
From: Carl Banks on 27 Jun 2010 23:48 On Jun 27, 8:19 pm, Owen Jacobson <angrybald...(a)gmail.com> wrote: > On 2010-06-27 22:51:59 -0400, Carl Banks said: > > On Jun 27, 3:20 pm, Roy Smith <r...(a)panix.com> wrote: > >> In article > >> <14e44c9c-04d9-452d-b544-498adfaf7...(a)d8g2000yqf.googlegroups.com>, > >> Carl Banks <pavlovevide...(a)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
From: Stephen Hansen on 27 Jun 2010 23:52
On 6/27/10 7:51 PM, Carl Banks wrote: > 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. See, there's really two kinds of SQL out there. There's the layman's SQL which is pretty straight-forward. Sure, it can start looking a little complicated if you get multiple clauses in the WHERE line (and maybe you're ambitious and do a simple inner join), but its probably still not bad. That can get translated into an API pretty easily. Then there's the type of SQL that results in DBA's having jobs-- and deservedly so. Its *really* a very flexible and powerful language capable of doing quite a lot to bend, flex, twist, and interleave that data in the server while building up a result set for you. I'm honestly only really in the former camp with a toe into the latter (I use aggregation and windowing functions over some interesting joins on occasion, but it takes effort). So I can't give a lot of serious examples to *prove* I'm right. So I just have to say: based on my experience and admittedly limited imagination, converting the full expressive power of SQL into a regular sort of API would be a very, very, very hairy sort of mess. SQLAlchemy can do the layman's SQL, and can *kind of* do a *little bit* of the advanced stuff-- but usually, it does the advanced stuff by just making it very easy for you to shove it out of the way and do SQL directly. But still: that's the structured part of SQL which belongs in a string. The data does not. It should be obvious that when a database provides you a mechanism to pass data in such that it doesn't need sanitization* at all, that's preferable to actually doing sanitization, even if you're divinely capable of perfect sanitization and even if sanitization is a trivial task that a monkey should be able to handle. -- ... Stephen Hansen ... Also: Ixokai ... Mail: me+list/python (AT) ixokai (DOT) io ... Blog: http://meh.ixokai.io/ P.S. *My computer /swears/ sanitization is spelled wrong. Either I'm high or it's high. Stupid old school mac mini. |