A report from OpenSQLCamp
Posted Nov 3, 2010 1:09 UTC (Wed) by ringerc
Parent article: A report from OpenSQLCamp
Standard machine-friendly representation of SQL
I'm an application developer who likes SQL - for reporting and and complex queries. It's a powerful language with great features, and I'd like to use it more in my code. In reality, I still use a query builder library or even an ORM much of the time, because what I hate and fear is assembling SQL from horrid little snippets for any non-trivial dynamic query. I'd love to see a standardized machine-friendly form of SQL that's:
- Designed for fast, simple and unambigious parsing/processing;
- Structured and friendly for machine processing. Use XML, use JSON, use whatever, but use something that's not free-form text;
- Capable of being easily restructured to add and remove clauses, embed another query as a subquery, etc; and
- Semantically identical to SQL, and translatable to text SQL queries for databases that don't accept it as an an alternate wire representation
Half the reason people "hate and fear" SQL is because it's a free-form, pseudo-natural-language query system that's an absolute bastard to manipulate in code. People are driven to add-on query builder APIs or ORMs to get library interfaces that make producing dynamic queries less excruciating than battling SQL snippets.
ORM extensions to the query language and result representation
The other thing I'd really love to see in SQL would be extensions to support ORMs. They're not going away, and the pain of getting from objects to/from relational data is greatly increased by the SQL interface that must be used. SQL is great for reporting and for complex queries, but ORMs are very useful for working with the data during small, highly dynamic transaction processing.
A set of ORM support extensions to SQL that defines a heirachical or graph representation for the returned data rather than a "flat" tabular/relational representation would massively reduce the pain involved in using ORMs with SQL databases, letting you stick to full SQL where its power is desirable and use a simplified "entity selector" when you just want to grab a bunch of records from a bunch of different relations according to some relationship. This would help avoid all that horrid left-outer-join abuse and many small selects that ORMs often do, by permitting them to request (say) "customer 1 and all customer 1's invoices and bookings" in a single query that'd return a single structured representation of the result data. For simple cases multiple tabular result sets from a query would be good enough (ie: a 1-record result for the customer, followed by an n-record result for invoices and an m-record result for bookings). Ideally you'd want to be able to return what's essentially a graph representation from the database, so you could slurp in database-produced JSON/XML/whatever and easily turn it into native language objects. Or, for that matter, return serialized forms of the language's native objects directly from the database (python pickle, Java serialize, etc) using a language runtime embedded in the database engine.
Imagine how much nicer it'd be to use ORMs if the database still stored data in relational form (so you get all those nice constraints, foreign key relationships, and the power of SQL to query it all) - but knew how to backward-traverse foreign key relationships and produce object graphs when you want an object representation of the data not a tabular representation. Oh, bliss.
to post comments)