More comprehensive parsing of the SQL dataflow graph

[mscolnick]

Description

We fail to parse Tables and Common Table Expression (CTE) in certain nested expressions or joins in SQL.

We would like to be able to cover more edge-case. They can be found in:

• tests/_ast/test_sql_visitor.py > "@pytest.mark.xfail"

Suggested solution

We could continue parsing ourselves using the token/ast that duckdb returns, or use a more comprehensive library like sqlglot

Alternative

No response

Additional context

No response

[Light2Dark]

happy to take this up! I'm keen to introduce sqlglot since it's needed to parse other sql dialects (& is far easier to write the parsing code).

and I'm guessing it would be okay to add sqlglot as another optional dependency?

edit:
Also notice dml statements (update, insert) aren't included in the dataflow graph. Do we want to support this?

[mscolnick]

That would be awesome - @akshayka seemed onboard to add sqlglot as a dep.

The logic should be fairly encapsulated / easy to switch out. I think all the tests that exist for the duckdb dialect should cover the test cases to handle, but maybe Claude can find some missing edge cases.

[akshayka]

Yes -- totally onboard to have sqlgot as a dependency that's optional but required for SQL.

This change would be super impactful. Excited you're interested in taking it on, let us know if we can help.

[snooze92]

Just to mention this would be epic for my use-case, we run our own fork of a database, we have added functions and features... We have a sqlglot dialect for it and it'd be awesome to use fully-powered Marimo SQL cells with that!