| |||||||||||||
A report from OpenSQLCampA report from OpenSQLCampPosted Nov 3, 2010 16:46 UTC (Wed) by iabervon (subscriber, #722)In reply to: A report from OpenSQLCamp by ringerc Parent article: A report from OpenSQLCamp
I think one mistake that was made with SQL that you're still making is the idea that queries should normally be constructed as a sequence of characters, like program source. Since applications will almost always have to make queries with systematic structural variations, it isn't possible to put it all in the program source, and there's not really any other particular benefit to the sequence of characters for programmatic use (of course, it can be convenient to interact with the database manually over a text terminal, but that's an operational issue rather than a development issue). What I think would be really nice is something that is to SQL what DOM is to XML: something that can be manipulated at a logical granularity and doesn't need to be unparsed and parsed at all except when it has to go between independent systems (which wouldn't happen at all in most applications). I think it might even be fine if the unparsed form was still SQL, just because it would hardly matter (of course, it would mean having a syntax that isn't entirely predictable from the parsed form's API, which would mean that extensions would have to be defined in both formats if they are to be possible to use over a text terminal).
(Log in to post comments)
A report from OpenSQLCamp Posted Nov 3, 2010 20:00 UTC (Wed) by nix (subscriber, #2304) [Link]
i.e. what we want is Sexpy Query Language? I'd support such an endeavour :)
A report from OpenSQLCamp Posted Nov 10, 2010 17:08 UTC (Wed) by jeremiah (subscriber, #1221) [Link]
My current working solution is to define queries as an XML tree. This tree defines both the query, and what the resulting XML should look like. I'm not interested in storing full XML documents in the DB, but breaking them up into atomic pieces. That a subsequent query, can the reconstruct across documents. I had really considered hacking the Postres source to do this. I've just have a parser that builds sql, and one that processes result sets into XML. Based on a rather large assumption here, I would think it would be possible to create an API that would take XML based queries and parse them into Postgres's internal query representation, and have postgres's normal query optimization apply w/o much work. The difficult part would be processing the result sets in a hierarchical fashion w/o iterating over the redundant outer left join redundancies. There is a lot of wasted time there, but I'd imagine that the internal optimizations that produce results are not designed in a tree friendly way, but in a flat way. So you'd probably be making rather severe architectural changes, to the point of it not being worth it. I guess you could always filter the flat results at the DB into the XML, but that would mean processing the result tree twice. Which most of us do already, but atleast it would already be in mem, and avoid transport time.
</unedited train of thought>
In a nutshell, it seems like everyone wants to be able to send a query or data tree in, and get a result tree out.
A report from OpenSQLCamp Posted Nov 10, 2010 17:33 UTC (Wed) by jeremiah (subscriber, #1221) [Link]
btw. NeoDB does an almost decent job of this, but too much of the processing and map reducing is done in code as opposed to by the DB itself. Although it's tree based, the query structure was not dynamically defined enough for my uses.
A report from OpenSQLCamp Posted Nov 10, 2010 21:59 UTC (Wed) by nix (subscriber, #2304) [Link]
That would be lovely. Downside: XML is horribly verbose and ugly as sin.
(Has anyone written an XML -> sexp bidirectional filter? If not, I should: it wouldn't be hard and would make this stuff *so* much easier for humans to read and write. It's quite an achievement, actually, coming up with something *less* readable than Lisp's sexps... the SGML/XML people should be proud.)
A report from OpenSQLCamp Posted Nov 10, 2010 23:40 UTC (Wed) by jeremiah (subscriber, #1221) [Link]
So I'm trying to visualize this, and failing. I don't suppose you'd toss up an example? I'm curious to see how you'd address a mix of attribute value pairs and element names and content. It's been at least 15 years since I messed with lisp.
I deal with XML every day, and agree with the verbosity and ugliness comment. The only way i can make it palatable is lots of indenting, and syntax highlighting, and even then it's pretty rough some times. I like it's flexibility and ubiquitousness though. I don't hate it nearly as much as I dislike the SQL language.
A report from OpenSQLCamp Posted Nov 11, 2010 0:11 UTC (Thu) by nix (subscriber, #2304) [Link]
There are lots of possible unambiguous mappings. Instead of
<foo bar="baz">quux<wob>foo</wob>blah</foo>
you could say
(foo :bar "baz" (:content "quux" (wob (:content "foo")) (:content "blah")))
perhaps. Or, if you don't like the ":content", you could say
(foo (:attr bar "baz") "quux" (wob "foo") "blah")
(yeah, I think the latter is much easier to read. Attributes are less common than content in decent XML, so attributes should be more verbose in the sexpy syntax.)
Note that both these syntaxes render it impossible to close elements in the wrong order and impossible to do the analogue of missing a / out. Those two things on their own make it worthwhile, I think.
(I am by no means the first person to have thought about this. I'm not even the five thousandth.)
A report from OpenSQLCamp Posted Nov 11, 2010 1:13 UTC (Thu) by jeremiah (subscriber, #1221) [Link]
I most definitely prefer the second, and thanks for the example. I'm going to roll it around in my head for a bit. I am curious to know why you think "Attributes are less common than content in decent XML" though. Most of the XML I deal with is coming out of a DB and generally the table name corresponds to the element name, and the column names to the attribute names etc. This makes for fairly decently readable XML, when a person has to look at it. Sure you run into issues with large content that doesn't really fit with in an attribute, and should be content instead. But for the majority of data that I have to deal with I have very very few content only elements. Do you have any pointers as to why this is a bad thing?
A report from OpenSQLCamp Posted Nov 11, 2010 2:11 UTC (Thu) by jeremiah (subscriber, #1221) [Link]
Never mind. There was some missed subtlety in my reading of your post.
A report from OpenSQLCamp Posted Nov 11, 2010 7:17 UTC (Thu) by nix (subscriber, #2304) [Link]
I think it depends on where your data's coming from. I think for the application you were discussing, you're probably right. I tend to deal with XML as a data-interchange format, and while a few of those are slightly self-describing, with datatypes in an attribute, most of them hand you a (generally very buggy) schema and then have no attributes at all in the content.
Note: a nice thing about using a real programming language rather than XML is that you could go the whole hog if you want and eliminate redundancy, although this looks uglier than it should because all my indentation is being eaten by the comment form and because I've only got one line of XML generated, much too little to be worth using this technique:
(let ((type-string '(:attr type "string"))
is equivalent to
<foo type="string">quux<num-foos type="int">6</num-foos></foo>
If you wanted to have 'type's with parameters, let alone optional ones, you'd have to do something a little different:
(flet ((type-float (precision) `(:attr type "float" `(:attr type ,(concat "float-" ,precision)))))
is equivalent to
<foo type="float-6">14.172</foo>
Note that the latter is horribly hard to typecheck properly in XML, but having something typecheck the Lisp notation is trivial. Also note that your generator and parser don't need to understand Lisp (although it is so easy the parser might as well understand a restricted subset): it just needs to know how to generate stereotyped let and (for parameterized attributes) flet expressions.
|
|||||||||||||