| |||||||||||||
PostgreSQL and the SQL standards processPostgreSQL and the SQL standards processPosted Sep 21, 2011 21:46 UTC (Wed) by robert_s (subscriber, #42402)In reply to: PostgreSQL and the SQL standards process by Cyberax Parent article: PostgreSQL and the SQL standards process
I couldn't agree more. Except:
"If there was implementation of query language based on http://en.wikipedia.org/wiki/FLWOR on PostgreSQL backend - I'd have switched immediately."
I'm not convinced we need a whole query "language" at all. The huge majority of database use is done on behalf of larger applications written in a different language. Why make developers learn another half-functional language just to be able to express their query intentions? I'd be far more interested in mechanisms to expose the querying possibilities in a developer's "native" language, which is why I found Berkus' sentence
"However, it's more likely that we'll be working more on direct language interfaces in the future instead"
so encouraging.
(Log in to post comments)
PostgreSQL and the SQL standards process Posted Sep 21, 2011 22:03 UTC (Wed) by raven667 (subscriber, #5198) [Link]
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 (subscriber, #2285) [Link]
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]
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 (subscriber, #55561) [Link]
"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.
PostgreSQL and the SQL standards process Posted Sep 21, 2011 23:59 UTC (Wed) by Cyberax (✭ supporter ✭, #52523) [Link]
The problem is, you need DB engine to see the 'whole' query to do some optimizations. For example if you do JOINs in SQL as for-loops over the tables inside your application, then DB engine wouldn't be able to use indexes to optimize them.
That's why a separate fully-analyzable language is needed. FLWOR is a nice example because it's functional and fully reference-transparent. So optimization engine can backtrack through definitions and get the complete picture of what's happening. And then use tried-and-true join optimizations.
PostgreSQL and the SQL standards process Posted Sep 22, 2011 19:43 UTC (Thu) by iabervon (subscriber, #722) [Link]
You do need some representation of the complete query you are performing. The question is actually: is there any benefit to having database operations go through a representation which is a standardized character sequence? Generally, both applications and databases have some sort of data structure representing a query, and the application flattens this into a character sequence which the database has to read in order to construct approximately the same data structure in order to analyze it. If the query API were specified in terms of a data structure instead of a character sequence, it would eliminate an awkward and error-prone step on each side.
PostgreSQL and the SQL standards process Posted Sep 22, 2011 20:08 UTC (Thu) by Cyberax (✭ supporter ✭, #52523) [Link]
Doesn't really matter much for me.
Explicit query-construction API should express roughly the same ideas as the textual query representation.
PostgreSQL and the SQL standards process Posted Sep 22, 2011 20:14 UTC (Thu) by dlang (✭ supporter ✭, #313) [Link]
you would not eliminate the conversion step, you would just replace the conversion between an text string and the internal data structure with the conversion between the binary sequence and the internal data structure.
different languages (and for that matter, different libraries for a single language) will want to represent the data in different ways.
it would be good to have a standard format that could eliminate the SQL injection type of vulnerability, but it's hard to think of anything that can take an arbitrary byte stream and use it in an arbitrary place in the statement, while still allowing multiple statements in one blob that would eliminate the problem
PostgreSQL and the SQL standards process Posted Sep 30, 2011 18:43 UTC (Fri) by Baylink (subscriber, #755) [Link]
You're effectively asking: "should queries be one string of text, instead of a whole sequence of API calls". (Unless I've misunderstood you badly, in which case, I apologize.)
The answer is "you've never been 400ms away from your database engine, have you"?
Yes, the architecture of SQL is the way it is for a reason. You young whippersnappers (:-) have never run a database engine with an API interface across even a LAN, much less a WAN, so you forget both halves of the latency term of the equation, much less the data reduction of having *all* the intermediate work happen inside the server.
|
|||||||||||||