Possible to get Slightly Better Formatting?

[lilasquared]

Hello! First off great library, just started using it and it is super cool so far. I am interested in having slightly better formatting for the query output though. For complex queries I know formatting is a touchy subject so is it possible to open up some of the functionality so it can be overridden? For example CompileColumns is virtual on the Compiler base class but not on the SqlServerCompiler class. One simple formatting that would help me would be to just include \n\t in the var sql = string.Join(", ", aggregateColumns); on line 322 and 339.

If there is another way I can achieve this I am open to suggestions. Thank you!

[ahmad-moussawi]

Hi thanks, as you've said the formatting is a bit complicated and in the current phase it needs a major rewrite in some of the base classes, if you would to contribute I can give you more insight about it.

For the SqlServerCompiler I think all methods should be virtual, so this is not intended

[lilasquared]

@ahmad-moussawi i'm interested in contributing for sure! I have some other ideas as well that you might be interested it. I really like the syntax that NUnit has for its constraints, so I was thinking it would be possible to implement some of the SQL methods in this way. Below is a simple example for CAST

public static class Cast
{
    public static CastExpression Null => new NullCastExpression();

    public static CastExpression String(String value)
    {
        return new StringCastExpression(value);
    }

    public static CastExpression Literal(Object value)
    {
        return new LiteralCastExpression(value.ToString());
    }
}

public abstract class CastExpression
{
    protected String Expression { get; }

    protected CastExpression(String expression)
    {
        Expression = expression;
    }

    public String AsInt()
    {
        return Cast(Expression, "INT");
    }

    public String AsBit()
    {
        return Cast(Expression, "BIT");
    }

    public String AsVarChar(Int32 size)
    {
        return Cast(Expression, $"VARCHAR({size})");
    }

    public String AsChar(Int32 size)
    {
        return Cast(Expression, $"CHAR({size})");
    }

    public String AsVarBinary(Int32 size)
    {
        return Cast(Expression, $"VARBINARY({size})");
    }

    public String AsBinary(Int32 size)
    {
        return Cast(Expression, $"BINARY({size})");
    }

    private static String Cast(String expr, String type)
    {
        return $"CAST({expr} AS {type})";
    }
}

I am using this in conjunction with SelectRaw but it would be possible to integrate for Select too.

 var query = new SqlKata.Query()
    .SelectRaw(Cast.String("whatever").AsChar(30))
    .From("table");

would generate

SELECT CAST('whatever' AS CHAR(30)) FROM [table]

[ahmad-moussawi]

Happy to hear this, for the indentation issue, we should refactor each CompileMethod to take another optional argument indentation, for instance this:

public virtual string CompileColumn(SqlResult ctx, AbstractColumn column, int indentation = 0) {}

instead of:

public virtual string CompileColumn(SqlResult ctx, AbstractColumn column) {}

this will inform the current compiler method what is the previous indentation level, and the method's responsibility to respect it or not.

Offcourse all these method should check for the boolean global variable Compiler.Format in order to let the developer decide if he want to format the query or not.

[ahmad-moussawi]

for the casting proposition it's nice but currently the abstraction you provided doesn't fit well to be projected in all compilers, and I think in the current state, it will add more complexity for the usage of the query builder, using Raw methods like WhereRaw is more appropriate at the time being.
I don't mind if you open another issue for it, so we can discuss in deep later, thanks