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

61–70 of 103 posts

Re: Open Source SQL Parsers

#61

Post by zasdffaa »

ErwinSmout wrote: post #58
zasdffaa wrote: post #53

Earlier quoted context omitted.

> So, I am looking for a customizable query parser, planner, and execution engine That looks like an entire DB engine, isn't it? > map the public schema to the private schema sounds like view. It seems like a pretty major project as you describe it.

And if you're set on building an "entire DB engine" then you can just as well go the full mile and go for relational. As opposed to SQL.

I know about TTM etc, but what business value would that bring to be fully relational? Specifically, thanks.

Re: Open Source SQL Parsers

#62

Post by vkazanov »

rzmmm wrote: post #51
vkazanov wrote: post #42

Earlier quoted context omitted.

...and there's more to sql trouble than just troublesome syntax, typical for languages of its generation! 1. Sql is non-modular 2. Non-orthogonal 3. Its parroting of the (beautiful) relational data model is... opinionated at best. The standard is unreadable and not really implementable, one always has to resort to implementation docs. Realistically there are no alternatives, but... sql is no good.

What do you think about using SQL views and functions to make composable, modular SQL code?

1. Views are fine. They somewhat help with abstracting things away here and there, yes. But these are just macros, right?

2. What SQL functions are you talking about? There are builtins, e.g. avg, sum, and then there are User Defined Functions, which have nothing to do with SQL, mostly just a random external language or one of the non-portable PL/SQL flavours.

Practically speaking, one cannot really write portable SQL outside of a very limited intersection of various dialects. And within this limit users just cannot build a reasonably portable code library.

There is a reason why analysts working with SQL just copy massive query templates again and again and again...

Re: Open Source SQL Parsers

#63

Post by tehlike »

cryptonector wrote: post #60
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...

I don't mind SQL, really, but I would prefer a query construction API that has the full power of SQL (and more even). At the very least such a thing would not have a SQL injection problem, but also could be used to generate ASTs. Query q = db.from("foo").join("bar").using("id") .select("id","foothing","barthing"); However, the moment you want any non-trivial SQL expression, such an API becomes unwieldy. And yet the n…

Linq in c# fixes that.

Re: Open Source SQL Parsers

#64

Post by tehlike »

ako wrote: post #47
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...

Nice example of: always begin with the end in mind - first specify what data you want to see and then specify where it should come from. I think it helps you focus on the outcome required, and makes you more efficient deciding how to implement it. I use the same approach for methods in normal code: first decide on the signature of the method, and potentially a unit test to test it, and then think about the implementa…

I like to think of data transformation a chaining problem. Linq lambda syntax is kind of like that.

db.Table.where(x=> x.name == 'abc').skip(10).take(20).groupBy()

This is really hard in sql especially with temporary tables and nesting you need to do as soon as you need to do things a little out of order...

Etc

Re: Open Source SQL Parsers

#65

Post by tehlike »

technion wrote: post #34
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...

This feels a lot like why Kusto was invented. It's very close to SQL, but written the other way around with the table name first, then the 'where', and finally the columns. https://docs.microsoft.com/en-us/azure/data-explorer/kusto/q...

Correct looks like it's trying to be the linq of sql. Looks good. MSFT really knows how to make programming tools...

Re: Open Source SQL Parsers

#66

Post by tehlike »

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...

Some discussion on prql https://news.ycombinator.com/item?id=30060784#30062329

Re: Open Source SQL Parsers

#67

Post by ErwinSmout »

cryptonector wrote: post #60
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...

I don't mind SQL, really, but I would prefer a query construction API that has the full power of SQL (and more even). At the very least such a thing would not have a SQL injection problem, but also could be used to generate ASTs. Query q = db.from("foo").join("bar").using("id") .select("id","foothing","barthing"); However, the moment you want any non-trivial SQL expression, such an API becomes unwieldy. And yet the n…

That "the API becomes unwieldy" is in fact inevitable. In order to support .where() as well as .select(, ascolname) you need a way to pass a parameter of type something-like-expression to the .where() and .select() methods, and lambdas or at least something like them appear to be the right way to do that (e.g. .where((foothing) -> (foothingPlus (assume my .where() example was Java), the host language compiler is never going to find a way to cope with the declaration of the 'input' of the 'lambda', *precisely* because [the definitions of] those things are outside the scope of what the host language compiler knows about. So you need a way to do something like "import my-db-definition;" And then you need to put machinery in place to verify at runtime that the definitions as they are for the db, are still the same (or compatible) with the definitions that the program was compiled with. Etc. etc. etc.

Re: Open Source SQL Parsers

#68

Post by ErwinSmout »

tehlike wrote: post #64
ako wrote: post #47

Earlier quoted context omitted.

Nice example of: always begin with the end in mind - first specify what data you want to see and then specify where it should come from. I think it helps you focus on the outcome required, and makes you more efficient deciding how to implement it. I use the same approach for methods in normal code: first decide on the signature of the method, and potentially a unit test to test it, and then think about the implementa…

I like to think of data transformation a chaining problem. Linq lambda syntax is kind of like that. db.Table.where(x=> x.name == 'abc').skip(10).take(20).groupBy() This is really hard in sql especially with temporary tables and nesting you need to do as soon as you need to do things a little out of order... Etc

That's because the true foundation is that relational algebra is (a) an algebra and (b) closed over relations. (a) means you should have an expressions system that allows arbitrary nesting and (b) means you should be able to arbitrarily nest relational expressions in particular. SQL fails heavily on (b) (it fails less so than it used to do with the SQL:1992 standard but even so, it's still far from where we should have been and SQL itself is still an abomination).

Moreover, your very examples illustrate how "thinking SQL" is *NOT* the path to finding proper solutions. .skip() and .take() clearly originate from the world of ranking problems and it drips off of every word that you were thinking of finding a "modern" way to specify [things like] "TOP 10", but those solutions are themselves a fundamentally crippled way to attack the problem.

Re: Open Source SQL Parsers

#69

Post by ErwinSmout »

7952 wrote: post #52
zmxz wrote: post #41

Earlier quoted context omitted.

In my case it's the opposite - SQL is perfectly readable and fits my mental model while the project you linked feels awkward and hard to read. However, it's good to have options, I wonder if prql will get traction. It would be great if it did.

A lot of the time people's mental model will be derived from the language though.

And in the case of SQL, that is precisely the problem.

Re: Open Source SQL Parsers

#70

Post by ErwinSmout »

tehlike wrote: post #63
cryptonector wrote: post #60

Earlier quoted context omitted.

I don't mind SQL, really, but I would prefer a query construction API that has the full power of SQL (and more even). At the very least such a thing would not have a SQL injection problem, but also could be used to generate ASTs. Query q = db.from("foo").join("bar").using("id") .select("id","foothing","barthing"); However, the moment you want any non-trivial SQL expression, such an API becomes unwieldy. And yet the n…

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.
Post reply on HN