public void Build(Sql.ISqExtensionBuilder builder)
{
var expr = builder.Arguments[0];
var newExpr = expr.Transform(e =>
{
if (e.NodeType == ExpressionType.Convert || e.NodeType == ExpressionType.ConvertChecked)
{
var unary = (UnaryExpression)e;
var method = _method.MakeGenericMethod(unary.Operand.Type, unary.Type);
return(Expression.Call(null, method, unary.Operand));
}
return(e);
});
if (newExpr == expr)
{
builder.ResultExpression = builder.GetExpression(0);
return;
}
var sqlExpr = builder.ConvertExpressionToSql(newExpr);
sqlExpr = ConvertVisitor.Convert(sqlExpr, (v, e) =>
{
if (e is SqlFunction func && func.Name == "$Convert_Remover$")
{
return(func.Parameters[0]);
}
return(e);
});
protected override SqlStatement ProcessQuery(SqlStatement statement)
{
if (statement.IsInsert() && statement.RequireInsertClause().Into !.Name == "Parent")
{
var expr =
new QueryVisitor().Find(statement.RequireInsertClause(), e =>
{
if (e.ElementType == QueryElementType.SetExpression)
{
var se = (SqlSetExpression)e;
return(((SqlField)se.Column).Name == "ParentID");
}
return(false);
}) as SqlSetExpression;
if (expr != null)
{
var value = ConvertTo <int> .From(((IValueContainer)expr.Expression !).Value);
if (value == 555)
{
var tableName = "Parent1";
var dic = new Dictionary <IQueryElement, IQueryElement>();
statement = ConvertVisitor.Convert(statement, (v, e) =>
{
if (e.ElementType == QueryElementType.SqlTable)
{
var oldTable = (SqlTable)e;
if (oldTable.Name == "Parent")
{
var newTable = new SqlTable(oldTable)
{
Name = tableName, PhysicalName = tableName
};
foreach (var field in oldTable.Fields.Values)
{
dic.Add(field, newTable.Fields[field.Name]);
}
return(newTable);
}
}
IQueryElement ex;
return(dic.TryGetValue(e, out ex) ? ex : e);
});
}
}
return(statement);
}
return(statement);
}
/// <summary>
/// Adds an ORDER BY clause to queries using OFFSET/FETCH, if none exists
/// </summary>
protected SqlStatement AddOrderByForSkip(SqlStatement statement)
{
ConvertVisitor.Convert(statement, (visitor, element) => {
if (element is SelectQuery query && query.Select.SkipValue != null && query.OrderBy.IsEmpty)
{
query.OrderBy.ExprAsc(new SqlValue(typeof(int), 1));
}
return(element);
});
private static SqlStatement NormalizeParameters(SqlStatement statement, List <ParameterAccessor> accessors)
{
// remember accessor indexes
new QueryVisitor().VisitAll(statement, e =>
{
if (e.ElementType == QueryElementType.SqlParameter)
{
var parameter = (SqlParameter)e;
if (parameter.IsQueryParameter)
{
var idx = accessors.FindIndex(a => object.ReferenceEquals(a.SqlParameter, parameter));
parameter.AccessorId = idx >= 0 ? (int?)idx : null;
}
}
});
// correct expressions, we have to put expressions in correct order and duplicate them if they are reused
statement = NormalizeExpressions(statement);
var found = new HashSet <ISqlExpression>();
var columnExpressions = new HashSet <ISqlExpression>();
statement = ConvertVisitor.Convert(statement, (visitor, e) =>
{
if (e.ElementType == QueryElementType.SqlParameter)
{
var parameter = (SqlParameter)e;
if (parameter.IsQueryParameter)
{
var parentElement = visitor.ParentElement;
if (parentElement is SqlColumn)
{
columnExpressions.Add(parameter);
}
else if (parentElement !.ElementType == QueryElementType.SetExpression)
{
// consider that expression is already processed by SelectQuery and we do not need duplication.
// It is specific how InsertStatement is built
if (columnExpressions.Contains(parameter))
{
return(parameter);
}
}
if (!found.Add(parameter))
{
var newParameter =
(SqlParameter)parameter.Clone(new Dictionary <ICloneableElement, ICloneableElement>(),
c => true);
return(newParameter);
}
// notify visitor to process this parameter always
visitor.VisitedElements.Add(parameter, null);
}
private static T NormalizeExpressions <T>(T expression)
where T : class, IQueryElement
{
var result = ConvertVisitor.Convert(expression, (visitor, e) =>
{
if (e.ElementType == QueryElementType.SqlExpression)
{
var expr = (SqlExpression)e;
// we interested in modifying only expressions which have parameters
if (HasQueryParameters(expr))
{
if (expr.Expr.IsNullOrEmpty() || expr.Parameters.Length == 0)
{
return(expr);
}
var newExpressions = new List <ISqlExpression>();
var newExpr = QueryHelper.TransformExpressionIndexes(expr.Expr,
idx =>
{
if (idx >= 0 && idx < expr.Parameters.Length)
{
var paramExpr = expr.Parameters[idx];
var normalized = paramExpr;
var newIndex = newExpressions.Count;
if (newExpressions.Contains(normalized) && HasQueryParameters(normalized))
{
normalized = (ISqlExpression)normalized.Clone(
new Dictionary <ICloneableElement, ICloneableElement>(),
c => true);
}
newExpressions.Add(normalized);
return(newIndex);
}
return(idx);
});
// always create copy
var newExpression = new SqlExpression(expr.SystemType, newExpr, expr.Precedence, expr.IsAggregate, expr.IsPure, newExpressions.ToArray());
// force re-entrance
visitor.VisitedElements[expr] = null;
return(newExpression);
}
}
return(e);
});
return(result);
}
/// <summary>
/// Adds an ORDER BY clause to queries using OFFSET/FETCH, if none exists
/// </summary>
protected SqlStatement AddOrderByForSkip(SqlStatement statement)
{
statement = ConvertVisitor.Convert(statement, (visitor, element) =>
{
if (element.ElementType == QueryElementType.OrderByClause)
{
var orderByClause = (SqlOrderByClause)element;
if (orderByClause.OrderBy.IsEmpty && orderByClause.SelectQuery.Select.SkipValue != null)
{
return(new SqlOrderByClause(new[] { new SqlOrderByItem(new SqlValue(typeof(int), 1), false) }));
}
}
return(element);
});
return(statement);
}
/// <summary>
/// We need to use same paremeters as for original query
/// </summary>
/// <param name="original"></param>
SqlStatement Clone(SqlStatement original)
{
var clone = original.Clone();
var pairs = from o in original.Parameters.Distinct()
join n in clone.Parameters.Distinct() on o.Name equals n.Name
select new { Old = o, New = n };
var dic = pairs.ToDictionary(p => p.New, p => p.Old);
clone = ConvertVisitor.Convert(clone, (v, e) =>
e is SqlParameter param && dic.TryGetValue(param, out var newParam) ? newParam : e);
clone.Parameters.Clear();
clone.Parameters.AddRange(original.Parameters);
return(clone);
}
private SqlStatement WrapParameters(SqlStatement statement)
{
// ODBC cannot properly type result column, if it produced by parameter
// To fix it we will wrap all parameters, used as select columns into type-cast
// we use CVar, as other cast functions generate error for null-values parameters and CVar works
// we are interested only in selects, because error generated by data reader, not by database itself
if (statement.QueryType != QueryType.Select)
{
return(statement);
}
statement = ConvertVisitor.Convert(statement, (visitor, e) =>
{
if (e is SqlParameter p && p.IsQueryParameter && visitor.ParentElement is SqlColumn)
{
return(new SqlExpression(p.Type.SystemType, "CVar({0})", Precedence.Primary, p));
}
return(e);
});
static ISqlExpression ConvertParametersToValues(Query query, Expression expr, IDataContext?db, object?[]?ps, ISqlExpression sqlExpr)
{
var parameters = query.Queries[0].Parameters;
var result = ConvertVisitor.Convert(sqlExpr, (v, e) =>
{
if (e.ElementType == QueryElementType.SqlParameter)
{
var parameter = (SqlParameter)e;
var accessor = GetParameterAccessor(parameters, parameter);
if (accessor != null)
{
return(new SqlValue(parameter.Type, accessor.ValueAccessor(expr, db, ps)));
}
}
return(e);
});
return(result);
}
private SqlStatement WrapParameters(SqlStatement statement)
{
// for some reason Firebird doesn't use parameter type information (not supported?) is some places, so
// we need to wrap parameter into CAST() to add type information explicitly
// As it is not clear when type CAST needed, below we should document observations on current behavior.
//
// When CAST is not needed:
// - parameter already in CAST from original query
// - parameter used as direct inserted/updated value in insert/update queries (including merge)
//
// When CAST is needed:
// - in select column expression at any position (except nested subquery): select, subquery, merge source
// - in composite expression in insert or update setter: insert, update, merge (not always, in some cases it works)
statement = ConvertVisitor.Convert(statement, (visitor, e) =>
{
if (e is SqlParameter p && p.IsQueryParameter)
{
// Don't cast in cast
if (visitor.ParentElement is SqlExpression expr && expr.Expr == CASTEXPR)
{
return(e);
}
if (p.Type.SystemType == typeof(bool) && visitor.ParentElement is SqlFunction func && func.Name == "CASE")
{
return(e);
}
var replace = false;
for (var i = visitor.Stack.Count - 1; i >= 0; i--)
{
// went outside of subquery, mission abort
if (visitor.Stack[i] is SelectQuery)
{
return(e);
}
// part of select column
if (visitor.Stack[i] is SqlColumn)
{
replace = true;
break;
}
// insert or update keys used in merge source select query
if (visitor.Stack[i] is SqlSetExpression set &&
i == 2 &&
visitor.Stack[i - 1] is SqlInsertClause &&
visitor.Stack[i - 2] is SqlInsertOrUpdateStatement insertOrUpdate &&
insertOrUpdate.Update.Keys.Any(k => k.Expression == set.Expression))
{
replace = true;
break;
}
// enumerable merge source
if (visitor.Stack[i] is SqlValuesTable)
{
replace = true;
break;
}
// complex insert/update statement, including merge
if (visitor.Stack[i] is SqlSetExpression &&
i >= 2 &&
i < visitor.Stack.Count - 1 && // not just parameter setter
(visitor.Stack[i - 1] is SqlUpdateClause ||
visitor.Stack[i - 1] is SqlInsertClause ||
visitor.Stack[i - 1] is SqlMergeOperationClause))
{
replace = true;
break;
}
}
if (!replace)
{
return(e);
}
return(new SqlExpression(p.Type.SystemType, CASTEXPR, Precedence.Primary, p, new SqlDataType(p.Type)));
}
return(e);
});