PostgreSQL and the SQL standards process

Posted Sep 21, 2011 22:03 UTC (Wed) by raven667 (subscriber, #5198)
In reply to: PostgreSQL and the SQL standards process by robert_s
Parent article: PostgreSQL and the SQL standards process

I'm not sure how that's going to work out, unless you restrict queries to just getting and setting individual values you are going to need some way to express a complex filtering expression that can be executed by the database and not your application. The job of communicating intentions in complicated enough that it will de-facto be be its own language. You could end up with different query languages for each programming language, for each database server and being restricted on which language you can implement a program on based on how complex the queries are allowed to be, not on the database but on your interface to the database.

I guess that's already the case for a lot of the newer databases like Hadoop where each has its own custom API and special storage/transaction/replication semantics

PostgreSQL and the SQL standards process

Posted Sep 21, 2011 23:56 UTC (Wed) by zlynx (guest, #2285) [Link] (2 responses)

C# has LINQ which is really cool.

The backend code to implement it is awful, but the user side of it is great.

PostgreSQL and the SQL standards process

Posted Sep 22, 2011 0:58 UTC (Thu) by raven667 (subscriber, #5198) [Link] (1 responses)

That's cool but its not standard and AFAIK not implemented anywhere else. Is it even in Mono? That's what I was saying about each environment getting its own better or worse API implementation. At the end of the day, right now, whatever queries you can create with LINQ, which must output SQL at the end, can be done in any language or by hand. What if that was tied to the combination of the implementation language and backend db, if LINQ was only available for C# talking to MS SQL, with features not exposed or usable by other languages or against other DBs.

I suppose that's already the case somewhat with proprietary extensions and incompatibilities between different SQL databases but I worry that it would be harder to handle and workaround if there were less compatibility between applications and databases as provided by the SQL standard.

PostgreSQL and the SQL standards process

Posted Sep 22, 2011 1:24 UTC (Thu) by Cyberax (✭ supporter ✭, #52523) [Link]

Yes, you can use LINQ in Mono. And there are alternative LINQ providers, like http://bltoolkit.net/Doc.Linq.ashx for various databases.

LINQ is just a mechanism which can be used to create DSLs to query data.

PostgreSQL and the SQL standards process

Posted Sep 22, 2011 5:55 UTC (Thu) by smurf (subscriber, #17840) [Link]

You already have the standard operations anyway. They're called "functions" and "operators" and such. And you do need a common representation for that between client and server.

Right now, that common representation is text and SQL, plus (sometimes) a server-specific method to prepare statements to prevent having to transcode and quote data.

Replace that with XML or whatever all you want. Doesn't buy you anything. You still have to assemble a textual string on one end and analyze it on the other side.

Three choices here:

* stay with SQL. It mostly works, people are used to it, you can do interesting stuff with it in any programming language whatsoever. Downside: you still need server-specific code for more complicated queries and for schema.

* agree on a common high-level API for queries, and possibly for schema queries/changes. Downside: who'd do the work and who'd agree on the result? The commercial vendors? Hardly.

* agree on a common binary data stream between client and server. Same downside, plus doesn't work for embedded servers.

PostgreSQL and the SQL standards process

Posted Sep 25, 2011 23:06 UTC (Sun) by jberkus (guest, #55561) [Link] (1 responses)

"You could end up with different query languages for each programming language, for each database server and being restricted on which language you can implement a program on based on how complex the queries are allowed to be, not on the database but on your interface to the database."

The thing is, we *already* have that. 80% of applications out there which interface with a SQL database use an ORM or similar high-level interface, and few ORMs support more than a couple of DBMSes. Developers are not using SQL to push data today.

Frankly, if database geeks took control of the ORMs and gave developers a user-friendly interface in their own language, it would be both better for developers and better for the databases. We could make sure that these interfaces *do the right thing* as far as database interfacing is concerned. The whole path of Call ORM --> Generate SQL String --> Parse SQL String --> Communicate Binary Protocol has at least one too many steps in it.

Couch and Mongo have gone partway there with JSON, but they're still essentially using intermediate query languages. And worse, BSON isn't even a standard anywhere, so it's completely non-portable.

Of course, we want to still support SQL as a direct interface for database geeks to do "advanced" work. But that's only about 10% of the database interaction out there. And I say this as a SQL expert.

PostgreSQL and the SQL standards process

Posted Sep 26, 2011 2:48 UTC (Mon) by raven667 (subscriber, #5198) [Link]

I think we largely agree on facts and are saying some of the same things from different perspectives. Right now ORMs and applications can support multiple databases but if the db/orm/language become more tightly coupled then I could see that no longer being the case. Applications built against newer databases like Couch and Mongo seem to be much less portable than traditional SQL applications.

As you say, if each database engine wrote their own custom ORM then developers could write better queries and get better results more safely but it would increase lock-in which is not an unmitigated positive.