mercredi 12 juillet 2017

Fluent SQL builder for dapper

I need to create fluent sql builder. I decided to create many interfaces for each part of SQL query (FROM, WHERE, JOIN etc.) and interfaces for each type of query (SELECT, INSERT, UPDATE etc.). My code:

// BUILDERS

public interface IBuilder
{

}

public interface IConditionBuilder<out TThis> : IBuilder
    where TThis : IQuery
{
    TThis Or();
    TThis Not();
}

public interface IFromBuilder<out TThis> : IBuilder
    where TThis : IQuery
{
    TThis From(string table);
    TThis FromRaw(string expression);
}

public interface IGroupBuilder<out TThis> : IBuilder
    where TThis: IQuery
{
    TThis GroupBy(params string[] fields);
    TThis GroupByRaw(string expression);
}

public interface IHavingBuilder<out TThis> : IConditionBuilder<TThis>
    where TThis : IQuery
{
    TThis Having<T>(string field, string operation, T value);
    TThis HavingRaw(string expression);
}

public interface IJoinBuilder<TThis> : IBuilder
    where TThis : IQuery
{
    TThis Join(string table, string first, string second);
    TThis Join(string table, string first, string second, string op);
    TThis Join(string table, string first, string second, string op, string type);
    TThis Join(string table, Func<IOnBuilder<TThis>, IOnBuilder<TThis>> on); // PROBLEM HERE
    TThis Join(string table, Func<IOnBuilder<TThis>, IOnBuilder<TThis>> on, string type); // PROBLEM HERE
}

public interface IJoinBuilder<TThis, in TQuery> : IJoinBuilder<TThis>
    where TThis : IQuery
    where TQuery : ISelectQuery
{
    TThis Join(TQuery query, string first, string second);
    TThis Join(TQuery query, string first, string second, string op);
    TThis Join(TQuery query, string first, string second, string op, string type);
    TThis Join(TQuery query, Func<IOnBuilder<TThis>, IOnBuilder<TThis>> on); // PROBLEM HERE
    TThis Join(TQuery query, Func<IOnBuilder<TThis>, IOnBuilder<TThis>> on, string type); // PROBLEM HERE
}

public interface IOnBuilder<out TThis> : IConditionBuilder<TThis>
    where TThis : IQuery
{
    TThis On(string first, string second);
    TThis On(string first, string second, string op);
    TThis OnRaw(string expression);
}

public interface IOrderBuilder<out TThis> : IBuilder
    where TThis : IQuery
{
    TThis OrderBy(params string[] fields);
    TThis OrderByDesc(params string[] fields);
    TThis OrderByRaw(string expression);
}

public interface IReturningBuilder<out TThis> : IBuilder
    where TThis : IQuery
{
    TThis Returning(params string[] fields);
}

public interface ISelectBuilder<out TThis> : IBuilder
{
    TThis Select(params string[] columns);
    TThis Select(params object[] columns);
    TThis SelectRaw(string expression);
}

public interface ISelectBuilder<out TThis, TQuery> : ISelectBuilder<TThis>
    where TThis: IQuery
    where TQuery: ISelectQuery
{
    TThis Select(TQuery query, string alias);
    TThis Select(Func<TQuery, TQuery> query, string alias);
}

public interface IWhereBuilder<out TThis> : IConditionBuilder<TThis>
    where TThis : IQuery
{
    TThis Where(string field, object value);
    TThis Where(string field, string op, object value);
    TThis WhereBetween<T>(string field, T lower, T higher);
    TThis WhereNull(string field);
    TThis WhereIn<T>(string field, IEnumerable<T> values);
    TThis WhereColumns(string fieldOne, string op, string fieldTwo);
    TThis WhereContains(string field, string value);
    TThis WhereContains(string field, string value, bool isCaseSensetive);
    TThis WhereStarts(string field, string value);
    TThis WhereStarts(string field, string value, bool isCaseSensetive);
    TThis WhereEnds(string field, string value);
    TThis WhereEnds(string field, string value, bool isCaseSensetive);
    TThis WhereRaw(string expression);
}

public interface IWhereBuilder<out TThis, TQuery> : IWhereBuilder<TThis>
    where TThis : IQuery
    where TQuery : ISelectQuery
{
    TThis Where(IWhereBuilder<out TThis, TQuery> where);
    TThis Where(Func<TQuery, TQuery> where); // PROBLEM HERE
    TThis WhereIn(string field, TQuery query);
    TThis WhereIn(string field, Func<TQuery, TQuery> query);
    TThis WhereExists(TQuery query);
    TThis WhereExists(Func<TQuery, TQuery> query);
}

// QUERIES

public interface IQuery
{
    string ToSql();
}

public interface ISelectQuery : IQuery, 
    ISelectBuilder<ISelectQuery>,
    IFromBuilder<ISelectQuery, ISelectQuery>,
    IWhereBuilder<ISelectQuery, ISelectQuery>,
    IJoinBuilder<ISelectQuery>
{
    ISelectQuery Distinct();
    ISelectQuery Take(long limit);
    ISelectQuery Skip(long offset);
    ISelectQuery Union(ISelectQuery query);
    ISelectQuery Union(Func<ISelectQuery, ISelectQuery> query);
}

I have problems with conditions. Now I can write something like this:

query.Where("Id", 10).Or().WhereBetween("Id", 20, 25);

I want to have an ability to write something like this:

query.Where(w=>w.Where("id", 11).Or().Where("id", 15))
// OR
query.Join("table", on => on.On("Id","=","IdTwo").Or().On("Id", "=", "IdThree");

I have no idea how to do it.

Aucun commentaire:

Enregistrer un commentaire