static string AddUnaryCondition(ISqlSyntax sqlFragmentBuilder, string input, UnarySqlOperator condition, bool hasOneItemOnly)
{
switch (condition)
{
case UnarySqlOperator.AverageFunction:
return(Func(sqlFragmentBuilder.AverageFunctionName));
case UnarySqlOperator.CountFunction:
return(Func(sqlFragmentBuilder.CountFunctionName));
case UnarySqlOperator.MaxFunction:
return(Func(sqlFragmentBuilder.MaxFunctionName));
case UnarySqlOperator.MinFunction:
return(Func(sqlFragmentBuilder.MinFunctionName));
case UnarySqlOperator.SumFunction:
return(Func(sqlFragmentBuilder.SumFunctionName));
default:
throw new InvalidOperationException($"Cannot build sql expression for expression type {condition}.");
}
string Func(string functionName) => $"{functionName}({input})";
}
public override string BuildFromString(BuildMapState state, ISqlSyntax sqlFragmentBuilder, string wrappedQueryAlias = null)
{
var tableIsFirstParamPart = wrappedQueryAlias == null;
var table1 = (First.Param ?? "").StartsWith("@") ? null : wrappedQueryAlias;
return(Next.Aggregate(
BuildColumn(table1, First),
Aggregate));
string Aggregate(string x, (StringBasedElement param, BinarySqlOperator type) y)
{
var table = (y.param.Param ?? "").StartsWith("@") ? null : wrappedQueryAlias;
var yValue = BuildColumn(table, y.param);
return(ISqlExpressionUtils.Combine(sqlFragmentBuilder, x, yValue, y.type));
}
string BuildColumn(string tab, StringBasedElement el)
{
var column = el.AddRoot(state).param;
if (tableIsFirstParamPart)
{
var p = column.Split('.');
if (p.Length > 1)
{
tab = p.Take(p.Length - 1).JoinString(".");
column = p[p.Length - 1];
}
}
return(sqlFragmentBuilder.BuildSelectColumn(tab, column));
}
}
/// <summary>
/// Build the string for a SELECT column
/// </summary>
public static string BuildSelectColumn(this ISqlSyntax builder, string tableName, string columnName)
{
columnName = (columnName ?? "").StartsWith("@") ? columnName : builder.WrapColumn(columnName);
return(tableName == null ?
$"{columnName}" :
$"{builder.WrapTable(tableName)}.{columnName}");
}
public string BuildFromString(BuildMapState state, ISqlSyntax sqlFragmentBuilder, string wrappedQueryAlias = null)
{
var first = First.BuildFromString(state, sqlFragmentBuilder, wrappedQueryAlias);
return(AddUnaryCondition(
sqlFragmentBuilder,
first,
Operator,
First.HasOneItemOnly));
}
/// <summary>
/// Add an alias to a SELECT column
/// </summary>
public static string AddAliasColumn(this ISqlSyntax builder, string sqlCode, string alias)
{
if (string.IsNullOrEmpty(alias) ||
alias.StartsWith($"{SqlStatementConstants.RootObjectAlias}."))
{
return(sqlCode);
}
return(builder.BuildAlias(sqlCode, builder.WrapAlias(alias)));
}
public Dependencies(
ISqlSyntax sqlSyntax,
DataTypes dataTypes,
Action <Settings> cleanupOldDatabase,
Func <Settings, IConnection> createConnection)
{
DataTypes = dataTypes;
CleanupOldDatabase = cleanupOldDatabase;
CreateConnection = createConnection;
SqlSyntax = sqlSyntax;
}
/// <summary>
/// Concat 2 query parts in a defined way (e.g. l + r)
/// </summary>
public static string Concat(this ISqlSyntax builder, string l, string r, BinarySqlOperator concatType)
{
switch (concatType)
{
case BinarySqlOperator.And:
return(builder.BuildAndCondition(l, r));
case BinarySqlOperator.Or:
return(builder.BuildOrCondition(l, r));
case BinarySqlOperator.Add:
return(builder.BuildAddCondition(l, r));
case BinarySqlOperator.Subtract:
return(builder.BuildSubtractCondition(l, r));
case BinarySqlOperator.Multiply:
return(builder.BuildMultiplyCondition(l, r));
case BinarySqlOperator.Divide:
return(builder.BuildDivideCondition(l, r));
case BinarySqlOperator.In:
return(builder.BuildInCondition(l, r));
case BinarySqlOperator.Comma:
return(builder.BuildCommaCondition(l, r));
case BinarySqlOperator.Equal:
return(builder.BuildEqualityCondition(l, r));
case BinarySqlOperator.NotEqual:
return(builder.BuildNonEqualityCondition(l, r));
case BinarySqlOperator.GreaterThan:
return(builder.BuildGreaterThanCondition(l, r));
case BinarySqlOperator.GreaterThanOrEqual:
return(builder.BuildGreaterThanEqualToCondition(l, r));
case BinarySqlOperator.LessThan:
return(builder.BuildLessThanCondition(l, r));
case BinarySqlOperator.LessThanOrEqual:
return(builder.BuildLessThanEqualToCondition(l, r));
default:
throw new NotSupportedException($"Cannot combine query parts using concatType: {concatType}.");
}
}
public CompiledQuery(
QueryParts sql,
object[] parameters,
string[] selectColumns,
RootObjectPropertyGraph propertyGraph,
ISqlSyntax sqlFragmentBuilder,
bool requiresSimpleValueUnwrap)
{
SqlParts = sql;
Parameters = RewriteParameters(sql.Assemble().sql, parameters);
SelectColumns = selectColumns;
PropertyGraph = propertyGraph;
SqlFragmentBuilder = sqlFragmentBuilder;
RequiresSimpleValueUnwrap = requiresSimpleValueUnwrap;
}
public MappedSqlStatementBuilder(
BuildMapState state,
IEnumerable <QueryElementBasedMappedProperty> selectProperties,
ISqlSelectStatement statement,
ISqlString innerSqlString,
ISqlSyntax sqlSyntax)
{
State = state ?? throw new ArgumentNullException(nameof(state));
SelectProperties = selectProperties?.ToDictionary(x => x.To) ?? throw new ArgumentNullException(nameof(selectProperties));
Statement = statement ?? throw new ArgumentNullException(nameof(statement));
InnerSqlString = innerSqlString ?? throw new ArgumentNullException(nameof(innerSqlString));
SqlSyntax = sqlSyntax ?? throw new ArgumentNullException(nameof(innerSqlString));
// this paradigm can be extended if there are more than 1
// alias needed per query
InnerQueryAlias = SqlStatementConstants.InnerQueryAlias;
}
/// <summary>
/// Compile a sqlBuilder into a query which can be executed multiple times
/// </summary>
/// <param name="sqlBuilder">The builder with all properties populated</param>
/// <param name="parameters">Any constant parameters in the statement</param>
/// <param name="queryParseType">Define the way results are to be parsed</param>
public static CompiledQuery <TArgs, TResult> Compile <TArgs, TResult> (
this ISqlString sqlBuilder,
ISqlSelectStatement statement,
IEnumerable <object> parameters,
ISqlSyntax sqlSyntax,
QueryParseType queryParseType,
bool requiresSimpleValueUnwrap)
{
var sql = ToSql(sqlBuilder);
var selectColumns = statement.SelectColumns.Select(Alias).ToArray();
var resultType = requiresSimpleValueUnwrap
? ReflectionUtils.CreatePropMapValue(typeof(TResult))
: typeof(TResult);
var propertyGraph = statement.BuildObjetPropertyGraph(resultType, queryParseType);
return(new CompiledQuery <TArgs, TResult>(sql, parameters.ToArray(), selectColumns, propertyGraph, sqlSyntax, requiresSimpleValueUnwrap));
}
public override string BuildFromString(BuildMapState state, ISqlSyntax sqlFragmentBuilder, string wrappedQueryAlias = null)
{
return(Next.Aggregate(
BuildColumn(First),
Aggregate));
string Aggregate(string x, (SelectColumnBasedElement param, BinarySqlOperator type) y)
{
var yValue = BuildColumn(y.param);
return(ISqlExpressionUtils.Combine(sqlFragmentBuilder, x, yValue, y.type));
}
string BuildColumn(SelectColumnBasedElement el)
{
return(sqlFragmentBuilder.BuildSelectColumn(
el.IsParameter ? null : wrappedQueryAlias,
el.IsParameter ? el.ParameterName : el.Column.Alias));
}
}
/// <summary>
/// Compile the query into something which can be executed multiple times
/// </summary>
public static ICompiledQuery <TArgs, TMapped> Compile <TArgs, TResult, TMapped>(
ISqlSyntax sqlSyntax,
SqlExecutor <TArgs, TResult> query,
LambdaExpression mapper,
bool requiresPropertyUnwrap,
ILogger logger)
{
var(wrappedBuilder, parameters) = query.ToSqlStatement();
var wrappedStatement = new SqlStatement(wrappedBuilder);
var argsParam = mapper.Parameters.Count > 1 ? mapper.Parameters[1] : null;
var state = new BuildMapState(query.PrimaryTableMember.memberName, parameters, mapper.Parameters[0], argsParam, wrappedStatement, query.SqlSyntax, true, MappingPurpose.Mapping);
var(properties, tables) = MapBuilder.BuildMapFromRoot(state, mapper.Body);
properties = properties.Enumerate();
var statement = new MappedSelectStatement(properties, tables, wrappedStatement.Tables.First().PrimaryKey);
var builder = new MappedSqlStatementBuilder(state, properties, statement, wrappedBuilder, sqlSyntax);
return(builder.Compile <TArgs, TMapped>(statement, parameters.Parameters, sqlSyntax, QueryParseType.ORM, requiresPropertyUnwrap));
}
public Query(ISqlSyntax sqlSyntax)
: base(sqlSyntax)
{
}
public ResultMapper(ISqlSyntax sqlSyntax)
: base(sqlSyntax)
{
}
/// <param name="strictJoins">If set to true, every join added to the SqlDsl query will also be added to the Sql query.
/// If false, joins which are not used in a mapping, WHERE clause, ON clause etc... will be automatically removed</param>
public SqlSelect(ISqlSyntax syntax, bool strictJoins)
: base(syntax, strictJoins)
{
}
public static string EqualTo(ISqlSyntax first, ISqlSyntax second) => Paren(first, S.EqualTo, second);
public static string LessThanEqualTo(ISqlSyntax first, ISqlSyntax second) => Paren(first, S.LessThanEqualTo, second);
public static string GreaterThanEqualTo(ISqlSyntax first, ISqlSyntax second) => Paren(first, S.GreaterThanEqualTo, second);
private static string Paren(ISqlSyntax first, string oper, ISqlSyntax second) => $"({first.ToSql()} {oper} {second.ToSql()})";
