Compliment and Suggestion for Enhancements

[rferraton]

I recently came across your SQL parser on GitHub and wanted to extend my sincere compliments for the fantastic work you've done. The repository is impressive, and I am confident that it will be instrumental in helping me achieve my objectives.

However, I find myself a bit at a loss due to the limited number of examples provided in the documentation. I believe that including more detailed examples could significantly enhance the user experience and potentially increase the adoption of your packages.

One specific feature I'm interested in is the manipulation of the WHERE clause in a parsed SQL query. A straightforward example could demonstrate how to add a WHERE clause to a query that lacks one. Additionally, if a WHERE clause already exists, it would be beneficial to show how to append another predicate using an AND operator.

Incorporating such examples would not only assist users like myself in better understanding and utilizing your parser but also showcase the versatility and practical applications of your work.

Thank you once again for your contribution to the community. I look forward to any future updates and enhancements.

[rferraton]

I tryed this

var query = new Parser().ParseSql(sql).Last().AsQuery();                            
var select = query.Body.AsSelectExpression();

//parsing the select statement to extract the different clauses
var distinct = select.Select.Distinct;
var columns = select.Select.Projection;
var from = select.Select.From;
var where = select.Select.Selection;                
var groupby = select.Select.GroupBy;
var having = select.Select.Having;
var orderby = select.Select.SortBy;
var limit = select.Select.Top;  

var addenum = new Between(
                new Identifier("Ctid"),
                false,
                new LiteralValue(new Value.SingleQuotedString("(0,0)")),
                new LiteralValue(new Value.SingleQuotedString("(1024,1024)"))
                );

var newwhere = new BinaryOp(
    where,
    BinaryOperator.And,
    addenum
    );

// create a new query with a new where clause and all other clauses

query = query with
{
    Body = select with
    {
        Select = new Select(columns)
        {
            From = from,
            GroupBy = groupby,
            Having = having,
            SortBy = orderby,
            Top = limit,
            Distinct = distinct,
            Selection = newwhere
        }
    }
};

[TylerBrinks]

Hi there,

I agree, and documentation is forthcoming. The Rust project this code is based on follows typical Rust paradigms, and as such the query instances generated by the parser are immutable. That may feel foreign, in a way, to .net developers given how many libraries tent to default to mutable properties.

The issue with allowing public access to properties is that one would be able to modify a query and wind up in a state that is not valid. For example, it would be possible to query aggregates and then remove a group by clause. For that reason, and quite a few others, it makes sense to favor immutability.

That said, custom dialects, the inbuilt visitor pattern, and general post-parsing object manipulation more than cover every case one would need to parse and modify the AST.

Your example above looks quite verbose. If you're simply trying to change a where clause, this approach allows creation of a custom property:

var query = new Parser().ParseSql("select * from location where a > 1 group by z").First().AsQuery();
var select = query.Body.AsSelectExpression();
var from = select.Select.From;
var groupBy = select.Select.GroupBy;

query = query with
{
    Body = select with
    {
        Select = new Select([new SelectItem.UnnamedExpression(new Expression.LiteralValue(new Value.SingleQuotedString("abc")))])
        {
            From = from,
            GroupBy = groupBy
        }
    }
};

[rferraton]

I understand the "immutable" point of view but , as you said as a C# dev, i found mutable more flexible If someone change a property that will break something the target database will be the last defense against bad query.

May be a isvalid() function could be added ?

[TylerBrinks]

Not all queries are targeted at a database, or at least not a RDBMS server. Other projects using this library are aimed at building queries that extract data from any number of unknown providers, which is what makes dialects are foundational. For example, my (personal) query engine can query CSV, JSON etc... which each have a unique set of rules that are not found in any SQL grammar. In other words, validating a query depends on the parse-time features of the underlying dialect.

I like to think of it like a language compiler. If you have a syntax error in your C# , the compiler fails and gives you details on where/why compilation could not complete. Compilation rules change based on language version (think .net pre-lambdas; arrow functions would not compile). Similarly, the SQL parser verifies SQL statements as they are parsed based on a number of dialect-specific syntax rules. For example, Postgres allows operators (e.g. for jsonb) like @> which is not valid in most other dialects. Validating a syntax tree after creation would have to re-traverse the entire tree and validate the rules that were already passing when the AST was created in the first place.

I would suggest looking at how visitors work. They're incredibly powerful and can hook into the pipeline and alter the tree at given levels. Otherwise, custom dialects or dialect subclasses give 100% control over how the AST is created.

[rferraton]

Thank you @TylerBrinks to take time for this project and for answer me. I agree with you : the sql parser is really great. I use dialect because my project use several database sources.

As you suggest , I will look at visitors. I may ask more exemples, it help me a lot. :)

[TylerBrinks]

Awesome. I'm glad it's helpful.