Add support for from subqueries, quoted identifiers, optional as

dbrattli commented 3 years ago

Proposed Changes

Add support for quoted identifiers (https://www.postgresql.org/docs/13/sql-syntax-lexical.html). Also make sure identifiers are not reserved keywords. Currently the parser would except invalid SQL e.g:

SELECT * FROM FROM

With this PR you would need to use a quoted identifier, and quoted identifiers are also parsed correctly (previously they would be ignored):

SELECT * FROM "FROM"

The AS keyword is optional noise. See 7.2.1.2 (https://www.postgresql.org/docs/13/queries-table-expressions.html).
Adds support for subqueries within FROM. See 7.2.1.3 (https://www.postgresql.org/docs/13/queries-table-expressions.html)

Types of changes

What types of changes does your code introduce to BinaryDefense.FSharp.Analyzers? Put an x in the boxes that apply

[ ] Bugfix (non-breaking change which fixes an issue)
[x] New feature (non-breaking change which adds functionality)
[ ] Breaking change (fix or feature that would cause existing functionality to not work as expected)

Checklist

Put an x in the boxes that apply. You can also fill these out after creating the PR. If you're unsure about any of them, don't hesitate to ask. We're here to help! This is simply a reminder of what we are going to look for before merging your code.

[x] Build and tests pass locally
[x] I have added tests that prove my fix is effective or that my feature works (if appropriate)
[ ] I have added necessary documentation (if appropriate)

Further comments

If this is a relatively large or complex change, kick off the discussion by explaining why you chose the solution you did and what alternatives you considered, etc...

Zaid-Ajaj commented 3 years ago

Hi there @dbrattli, these improvements are really nice, thanks a lot! 🙏

I have one question though, do you plan on using those new features in the analyzer?

I am asking this because the parser is only used 10% of the time: the analyzer relies on the database to parse and validate the query and only uses the parsers when more information is required that the database doesn't provide on its own such as when determining the nullability of the parameters or the nullability of casted selected columns.

I see that this PR includes the other two PRs, are those no longer valid?

dbrattli commented 3 years ago

Hi @Zaid-Ajaj. Yes, we're using the parser separately (without the analyzer) i.e only Types.fs and Parser.fs to parse the SQL AST. So there will probably be more PR's if we meet more queries we cannot parse. I must admit that I haven't even look at the analyzer yet, so really not sure what it's doing. I tried making the analyzer work with VS Code, but no luck so far. The CLI tool is working.

Yes, this PR includes the two others. I tried splitting but they depend on each other. So merging this is the same as merging all three. Made 3 PR if you needed to see each feature separately, i.e if you merge the first, the the next will be easier to review etc. But I probably just made things more confusing 😬

Zaid-Ajaj commented 3 years ago

Hi @Zaid-Ajaj. Yes, we're using the parser separately (without the analyzer) i.e only Types.fs and Parser.fs to parse the SQL AST. So there will probably be more PR's if we meet more queries we cannot parse. I must admit that I haven't even look at the analyzer yet, so really not sure what it's doing. I tried making the analyzer work with VS Code, but no luck so far. The CLI tool is working.

I am curious about this, are you using git files dependencies here (via paket)? Because I didn't publish a separate package for the parser 😅 to be honest, I could publish it separately. So far I didn't need to do that because this parser is embedded with the published analyzer

I tried making the analyzer work with VS Code, but no luck so far

Any specific error you got for me to debug?

Yes, this PR includes the two others. I tried splitting but they depend on each other. So merging this is the same as merging all three. Made 3 PR if you needed to see each feature separately, i.e if you merge the first, the the next will be easier to review etc. But I probably just made things more confusing

Please combine the PRs into one, that's easier for me 😄

dbrattli commented 3 years ago

Just using the NpgsqlFSharpAnalyzer nuget and NpgsqlFSharpParser.Parser.parse function to parse SQL into AST. Not sure I have installed it correctly for vscode. I don't see any relevant messages in F# output. Have combined the PR's now so it should be simpler to merge.

Zaid-Ajaj commented 3 years ago

Published as of v3.25 🚀

Zaid-Ajaj / Npgsql.FSharp.Analyzer