public override ISqlPredicate ConvertSearchStringPredicate(MappingSchema mappingSchema, SqlPredicate.SearchString predicate,
ConvertVisitor visitor,
OptimizationContext optimizationContext)
{
if (!predicate.IgnoreCase)
{
return(ConvertSearchStringPredicateViaLike(mappingSchema, predicate, visitor, optimizationContext));
}
ISqlExpression expr;
switch (predicate.Kind)
{
case SqlPredicate.SearchString.SearchKind.EndsWith:
{
predicate = new SqlPredicate.SearchString(
new SqlFunction(typeof(string), "$ToLower$", predicate.Expr1),
predicate.IsNot,
new SqlFunction(typeof(string), "$ToLower$", predicate.Expr2), predicate.Kind,
predicate.IgnoreCase);
return(ConvertSearchStringPredicateViaLike(mappingSchema, predicate, visitor, optimizationContext));
}
case SqlPredicate.SearchString.SearchKind.StartsWith:
{
expr = new SqlExpression(typeof(bool),
predicate.IsNot ? "{0} NOT STARTING WITH {1}" : "{0} STARTING WITH {1}",
Precedence.Comparison,
TryConvertToValue(predicate.Expr1, optimizationContext.Context), TryConvertToValue(predicate.Expr2, optimizationContext.Context))
{
CanBeNull = false
};
break;
}
case SqlPredicate.SearchString.SearchKind.Contains:
expr = new SqlExpression(typeof(bool),
predicate.IsNot ? "{0} NOT CONTAINING {1}" : "{0} CONTAINING {1}",
Precedence.Comparison,
TryConvertToValue(predicate.Expr1, optimizationContext.Context), TryConvertToValue(predicate.Expr2, optimizationContext.Context))
{
CanBeNull = false
};
break;
default:
throw new InvalidOperationException($"Unexpected predicate: {predicate.Kind}");
}
return(new SqlSearchCondition(new SqlCondition(false, new SqlPredicate.Expr(expr))));
}
public override ISqlPredicate ConvertSearchStringPredicate(SqlPredicate.SearchString predicate, ConvertVisitor <RunOptimizationContext> visitor)
{
ISqlExpression expr;
var caseSensitive = predicate.CaseSensitive.EvaluateBoolExpression(visitor.Context.OptimizationContext.Context);
// for explicit case-sensitive search we apply "CAST({0} AS BLOB)" to searched string as COLLATE's collation is character set-dependent
switch (predicate.Kind)
{
case SqlPredicate.SearchString.SearchKind.EndsWith:
{
if (caseSensitive == false)
{
predicate = new SqlPredicate.SearchString(
new SqlFunction(typeof(string), "$ToLower$", predicate.Expr1),
predicate.IsNot,
new SqlFunction(typeof(string), "$ToLower$", predicate.Expr2), predicate.Kind,
predicate.CaseSensitive);
}
else if (caseSensitive == true)
{
predicate = new SqlPredicate.SearchString(
new SqlExpression(typeof(string), "CAST({0} AS BLOB)", Precedence.Primary, predicate.Expr1),
predicate.IsNot,
predicate.Expr2,
predicate.Kind,
predicate.CaseSensitive);
}
return(ConvertSearchStringPredicateViaLike(predicate, visitor));
}
case SqlPredicate.SearchString.SearchKind.StartsWith:
{
expr = new SqlExpression(typeof(bool),
predicate.IsNot ? "{0} NOT STARTING WITH {1}" : "{0} STARTING WITH {1}",
Precedence.Comparison,
TryConvertToValue(
caseSensitive == false
? new SqlFunction(typeof(string), "$ToLower$", predicate.Expr1)
: caseSensitive == true
? new SqlExpression(typeof(string), "CAST({0} AS BLOB)", Precedence.Primary, predicate.Expr1)
: predicate.Expr1,
visitor.Context.OptimizationContext.Context),
TryConvertToValue(
caseSensitive == false
? new SqlFunction(typeof(string), "$ToLower$", predicate.Expr2)
: predicate.Expr2, visitor.Context.OptimizationContext.Context))
{
CanBeNull = false
};
break;
}
case SqlPredicate.SearchString.SearchKind.Contains:
{
if (caseSensitive == false)
{
expr = new SqlExpression(typeof(bool),
predicate.IsNot ? "{0} NOT CONTAINING {1}" : "{0} CONTAINING {1}",
Precedence.Comparison,
TryConvertToValue(predicate.Expr1, visitor.Context.OptimizationContext.Context),
TryConvertToValue(predicate.Expr2, visitor.Context.OptimizationContext.Context))
{
CanBeNull = false
};
}
else
{
if (caseSensitive == true)
{
predicate = new SqlPredicate.SearchString(
new SqlExpression(typeof(string), "CAST({0} AS BLOB)", Precedence.Primary, predicate.Expr1),
predicate.IsNot,
predicate.Expr2,
predicate.Kind,
new SqlValue(false));
}
return(ConvertSearchStringPredicateViaLike(predicate, visitor));
}
break;
}
default:
throw new InvalidOperationException($"Unexpected predicate: {predicate.Kind}");
}
return(new SqlSearchCondition(new SqlCondition(false, new SqlPredicate.Expr(expr))));
}
public override ISqlPredicate ConvertSearchStringPredicate <TContext>(MappingSchema mappingSchema, SqlPredicate.SearchString predicate, ConvertVisitor <RunOptimizationContext <TContext> > visitor,
OptimizationContext optimizationContext)
{
var like = ConvertSearchStringPredicateViaLike(mappingSchema, predicate, visitor,
optimizationContext);
if (predicate.CaseSensitive.EvaluateBoolExpression(optimizationContext.Context) == true)
{
SqlPredicate.ExprExpr?subStrPredicate = null;
switch (predicate.Kind)
{
case SqlPredicate.SearchString.SearchKind.StartsWith:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(string), "Substr", predicate.Expr1, new SqlValue(1),
new SqlFunction(typeof(int), "Length", predicate.Expr2)),
SqlPredicate.Operator.Equal,
predicate.Expr2, null);
break;
}
case SqlPredicate.SearchString.SearchKind.EndsWith:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(string), "Substr", predicate.Expr1,
new SqlBinaryExpression(typeof(int),
new SqlFunction(typeof(int), "Length", predicate.Expr2), "*", new SqlValue(-1),
Precedence.Multiplicative)
),
SqlPredicate.Operator.Equal,
predicate.Expr2, null);
break;
}
case SqlPredicate.SearchString.SearchKind.Contains:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(int), "InStr", predicate.Expr1, predicate.Expr2),
SqlPredicate.Operator.Greater,
new SqlValue(0), null);
break;
}
}
if (subStrPredicate != null)
{
var result = new SqlSearchCondition(
new SqlCondition(false, like, predicate.IsNot),
new SqlCondition(predicate.IsNot, subStrPredicate));
return(result);
}
}
return(like);
}
public override ISqlPredicate ConvertSearchStringPredicate <TContext>(MappingSchema mappingSchema, SqlPredicate.SearchString predicate, ConvertVisitor <RunOptimizationContext <TContext> > visitor,
OptimizationContext optimizationContext)
{
var like = ConvertSearchStringPredicateViaLike(mappingSchema, predicate, visitor,
optimizationContext);
if (predicate.CaseSensitive.EvaluateBoolExpression(optimizationContext.Context) == true)
{
SqlPredicate.ExprExpr?subStrPredicate = null;
switch (predicate.Kind)
{
case SqlPredicate.SearchString.SearchKind.StartsWith:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary,
new SqlFunction(typeof(string), "SUBSTRING",
predicate.Expr1,
new SqlValue(1),
new SqlFunction(typeof(int), "Length", predicate.Expr2))),
SqlPredicate.Operator.Equal,
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2),
null
);
break;
}
case SqlPredicate.SearchString.SearchKind.EndsWith:
{
var indexExpression = new SqlBinaryExpression(typeof(int),
new SqlBinaryExpression(typeof(int),
new SqlFunction(typeof(int), "Length", predicate.Expr1),
"-",
new SqlFunction(typeof(int), "Length", predicate.Expr2)),
"+",
new SqlValue(1));
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary,
new SqlFunction(typeof(string), "SUBSTRING",
predicate.Expr1,
indexExpression,
new SqlFunction(typeof(int), "Length", predicate.Expr2))),
SqlPredicate.Operator.Equal,
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2),
null
);
break;
}
case SqlPredicate.SearchString.SearchKind.Contains:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(int), "CHARINDEX",
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary,
predicate.Expr2),
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary,
predicate.Expr1)),
SqlPredicate.Operator.Greater,
new SqlValue(0), null);
break;
}
}
if (subStrPredicate != null)
{
var result = new SqlSearchCondition(
new SqlCondition(false, like, predicate.IsNot),
new SqlCondition(predicate.IsNot, subStrPredicate));
return(result);
}
}
return(like);
}
public override ISqlPredicate ConvertSearchStringPredicate(SqlPredicate.SearchString predicate, ConvertVisitor <RunOptimizationContext> visitor)
{
var like = base.ConvertSearchStringPredicate(predicate, visitor);
if (predicate.CaseSensitive.EvaluateBoolExpression(visitor.Context.OptimizationContext.Context) == true)
{
SqlPredicate.ExprExpr?subStrPredicate = null;
switch (predicate.Kind)
{
case SqlPredicate.SearchString.SearchKind.StartsWith:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, new SqlFunction(
typeof(string), "LEFT", predicate.Expr1,
new SqlFunction(typeof(int), "Length", predicate.Expr2))),
SqlPredicate.Operator.Equal,
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2),
null
);
break;
}
case SqlPredicate.SearchString.SearchKind.EndsWith:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, new SqlFunction(
typeof(string), "RIGHT", predicate.Expr1,
new SqlFunction(typeof(int), "Length", predicate.Expr2))),
SqlPredicate.Operator.Equal,
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2),
null
);
break;
}
case SqlPredicate.SearchString.SearchKind.Contains:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(int), "CHARINDEX",
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary,
predicate.Expr2),
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary,
predicate.Expr1)),
SqlPredicate.Operator.Greater,
new SqlValue(0), null);
break;
}
}
if (subStrPredicate != null)
{
var result = new SqlSearchCondition(
new SqlCondition(false, like, predicate.IsNot),
new SqlCondition(predicate.IsNot, subStrPredicate));
return(result);
}
}
return(like);
}
