Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Open Source SQL Parsers

tokern.io

Post reply on HN
Print view View original

81–90 of 103 posts

Re: Open Source SQL Parsers

#81

Post by cryptonector »

ErwinSmout wrote: post #80
cryptonector wrote: post #73

Earlier quoted context omitted.

> lambdas Lambdas in the host language won't play well with remote RDBMSes -- you'd have to be able to serialize the lambda and make the serialized form reasonably efficient. I'm skeptical of lambdas for expressions in DB query APIs. An expression like `foo + bar` has to become `.expr(plus("foo", "bar"))`, except, if you take this to its limit you'll want to use non-string objects to identify column names and other s…

"And yet an API is kinda desirable." Yeah. however I may not live to see the day when that desire, which I agree is felt by 99.99% of the developer community (hell, even by 99.99% of the end user community because don't come and tell me that what that community is feeling isn't some sort of sense that "the developers just can't offer us any answers"), actually gets to be fulfilled. "compiling to a standard AST" I thi…

> I think that what you might be failing to appreciate is that achieving that requires a "standard algebra", and that no such thing exists at this present day.

Oh I appreciate that. What I've in mind is something like the SQL standard (which isn't exactly universally adhered to) of QL ASTs.

Re: Open Source SQL Parsers

#82

Post by zasdffaa »

ErwinSmout wrote: post #78
zasdffaa wrote: post #77

Earlier quoted context omitted.

Am aware of sira-prise but not very well. Thanks, will read up (any disclaimer needed here; has sira-prise any link to you?) I'd appreciate you omitting the codeshitter ad-homs, it undermines your case. Pretty sure no declarative statement can be made efficient automatically so that remains a dream (though one I will need to look at) so it will kill performance. I too hate procedural enforcements but there seems to b…

Link between me and SIRA_PRISE : I am the author. codeshitter ad-homs : yeah well I know they are. The fact of the matter is the history between SIRA_PRISE and me (and why I did it in the first place) is now almost 20 yrs old, and I know how it's been received, and that's primarily due to (a) how the codeshitters (and the way how they are subject to the Dunning-Kruger effect) have come to dominate the entire industry…

Jesus. I feel there may be a touch of dunning-kruger about you. From that book (page xvi)

" Standard SQL is relationally complete in its support for declarative constraints by permitting the inclusion of query expressions in the CHECK clause of a constraint declaration.

We do not yet know—and it is an important and interesting research topic—how to do the kind of optimization that would be needed for the DBMS to work out efficient evaluation strategies along the lines of the authors’ custom-written solutions. "

So it can't. But then you say.

> But the reasons are ENTIRELY related to SQL's depending on 3VL.

Easy to fix. Simply require the assertion to be defined on tables (ok, ok, relvars) with 'not null' on every column, also require a PK to ensure uniqueness, and you're away. Except you aren't.

I'm not going to argue with you. Your knowledge is clearly considerable but it comes with something extra I don't need.

Re: Open Source SQL Parsers

#83

Post by zasdffaa »

i_s wrote: post #71
zasdffaa wrote: post #44

Earlier quoted context omitted.

Hmm? This doesn't make much sense. MSSQL does these already I think (except the first) . - Inlining scalar function calls (less impactful now with Sql Server 2019) write them as table valued funcs and it'll inline them for you (ugly but it works and is easy) . - Removing joins from a query when we know it won't impact the number of records That's just tree pruning. It does that . - Deepening where conditions against…

> This doesn't make much sense. MSSQL does these already I think (except the first) We tested all of these before taking the time to do the rewriting, and no, either they don't, or they way they did it isn't good enough. You can say I'm lying if you want, I'm not going to spend time arguing about it.

You could post some working examples and show them failing.

Re: Open Source SQL Parsers

#84

Post by ErwinSmout »

userbinator wrote: post #16
tehlike wrote: post #5

Not related to parsers, but i find sql syntax so backwards. Listing columns first Then table Then join Then filter Then group bys Then limit The order of operations are out of whack and makes pipeline ing a little hard. I found this to be closer to LINQ way https://github.com/prql/prql I hope in near future databases will come with better query languages...

It was probably influenced by COBOL, i.e. designed to read like English.

It was probably also influenced by Codd's own preference for the data language in calculus-based style (Codd's roots as a mathematician are to "blame" here), as opposed to algebra-based (the style typically preferred by developers).

Re: Open Source SQL Parsers

#85

Post by ErwinSmout »

zasdffaa wrote: post #82
ErwinSmout wrote: post #78

Earlier quoted context omitted.

Link between me and SIRA_PRISE : I am the author. codeshitter ad-homs : yeah well I know they are. The fact of the matter is the history between SIRA_PRISE and me (and why I did it in the first place) is now almost 20 yrs old, and I know how it's been received, and that's primarily due to (a) how the codeshitters (and the way how they are subject to the Dunning-Kruger effect) have come to dominate the entire industry…

Jesus. I feel there may be a touch of dunning-kruger about you. From that book (page xvi) " Standard SQL is relationally complete in its support for declarative constraints by permitting the inclusion of query expressions in the CHECK clause of a constraint declaration. We do not yet know—and it is an important and interesting research topic—how to do the kind of optimization that would be needed for the DBMS to work…

The basic text of the book was written before Lex died (must have been +- 2006), my work in which I cracked that problem saw the light of day after that time. So "we do not yet know ..." was technically correct at the time they wrote it, it no longer would be now.

That other snippet "Standard SQL is relationally complete ... CHECK clause ..." is technically correct, but the standard allows subqueries referencing other tables than the one the CHECK clause is on, but as far as I'm aware no product supports that (and the ones that do leave the user exposed to risk of faulty behaviour). Sadly, such a feature is necessary if we'd want to write, say, an FK constraint in the form of CHECK clause on the referencing table : CHECK (EXISTS (SELECT 1 FROM PARENT WHERE ) ).

Re: Open Source SQL Parsers

#86

Post by antonvs »

ErwinSmout wrote: post #70
tehlike wrote: post #63

Earlier quoted context omitted.

Linq in c# fixes that.

I doubt that it does. There is way more to "integrating [host] language and queries" than the MIN() of what Micro$oft engineers are (a) capable of understanding and (b) allowed by their own management to put in the products they come up with.

Your comment may be true of many things that Microsoft does, but not LINQ.

The creator of LINQ was Erik Meijer (https://en.wikipedia.org/wiki/Erik_Meijer_(computer_scientis...), who was a leading researcher in programming languages and functional programming before shifting into industry. He's well known in the Haskell world for e.g. his work on bananas and lenses, and many other things.

LINQ (https://en.wikipedia.org/wiki/Language_Integrated_Query) is basically an embedding of a general monadic framework, augmented with a set of query-specific operators. It can process arbitrary data sources - quoting from the link: "arrays, enumerable classes, XML documents, relational databases, and third-party data sources."

In addition, because it's fundamentally monadic, it can be used to express all sorts of computations, by: "utilizing query expressions as a general framework for readably composing arbitrary computations, include the construction of event handlers or monadic parsers."

LINQ has been ported to PHP, JS & TS, and even Actionscript (although the ports aren't necessarily as capable as the version on .NET, which has language-level support for the features.)

If you're interested in this general subject, I guarantee you you have a lot to learn from LINQ and the research work behind it.

Re: Open Source SQL Parsers

#87

Post by tehlike »

ErwinSmout wrote: post #79
tehlike wrote: post #76

Earlier quoted context omitted.

Microsoft is surprisingly good for creating good developer tools and infrastructure.

In the perception of the Micro$oft users who are brainwashed with the idea that what Micro$oft does is good for the developers. (In fact, it might even be outright true. But that's not a guarantee that what the Micro$oft users do with the Micro$oft tools is necessarily also the * BEST* thing for the user of that software product that the Micro$oft users produce.)

You are being unnecessarily cynical. I left microsoft ecosystem about 10 years ago, but still miss the developer environment they had in 2012 today. Nothing still comes close.

Re: Open Source SQL Parsers

#88

Post by anitil »

pbowyer wrote: post #49
anitil wrote: post #6

I'd be interested to hear people's use cases for parsing SQL. The link talks about exploring sql history, but has anyone else got some interesting uses? A couple of times where I've needed to parse SQL I would typically write a module for sqlite3 and get it to do the parsing for me. But annoyingly I can't remember _why_ I did this or what I was trying to achieve.

A couple of years ago I inherited an old, badly written web app doing up to 800 queries per page. It was slow and hammering the server. Being read-heavy the app was an ideal candidate for output caching. But - there were no hooks in the admin code to add cache invalidation. And I wasn't going to crawl through 10s of thousands of badly written lines and add cache invalidation calls manually, because I would miss some.…

That is a fantastic use case! I'd have never have thought of doing this

Re: Open Source SQL Parsers

#89

Post by anitil »

eatonphil wrote: post #8
anitil wrote: post #6

I'd be interested to hear people's use cases for parsing SQL. The link talks about exploring sql history, but has anyone else got some interesting uses? A couple of times where I've needed to parse SQL I would typically write a module for sqlite3 and get it to do the parsing for me. But annoyingly I can't remember _why_ I did this or what I was trying to achieve.

Building a SQL IDE and want to do introspection on queries. Building a SQL frontend for some API or CLI. Building a SQL-aware proxy. Just a few ideas. :)

Interesting. Out of interest, what would an SQL-aware proxy give you? On-the-fly rewrites? Automatic caching?

Re: Open Source SQL Parsers

#90

Post by anitil »

cgio wrote: post #11
anitil wrote: post #6

I'd be interested to hear people's use cases for parsing SQL. The link talks about exploring sql history, but has anyone else got some interesting uses? A couple of times where I've needed to parse SQL I would typically write a module for sqlite3 and get it to do the parsing for me. But annoyingly I can't remember _why_ I did this or what I was trying to achieve.

Harvesting lineage information could be a good use case.

This would be so useful to me right now. So often I'm flying blind making edits and just praying I don't ruin someone else's day
Post reply on HN