public override object Convert(object value, ConvertType convertType)
{
if (value == null)
{
throw new ArgumentNullException("value");
}
switch (convertType)
{
case ConvertType.ExceptionToErrorNumber:
if (value is MySqlException)
{
return(((MySqlException)value).Number);
}
break;
case ConvertType.ExceptionToErrorMessage:
if (value is MySqlException)
{
return(((MySqlException)value).Message);
}
break;
}
return(SqlProvider.Convert(value, convertType));
}
public void SimpleMath()
{
const string input = "([age] >= 16 and [country] == [ru]) or ([age] >= 13 and [country] === [ja])";
const string expectedSql = "where age >= @3 and country = @2 or age >= @1 and country = @0";
var expectedParameters = new object[]
{
"ja", 13, "ru", 16,
};
var tokens = TokenizationService.Tokenizate(input);
var tree = AstService.Convert(tokens);
var(actualSql, actualParameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty()
.From("age")
.ToSql("age"),
SqlProvider.DefineProperty()
.From("country")
.ToSql("country"));
Assert.AreEqual(expectedSql, actualSql.ToString());
Assert.AreEqual(expectedParameters.Length, actualParameters.Length);
for (var i = 0; i < actualParameters.Length; i++)
{
var expectedParameter = expectedParameters[i];
var actualParameter = actualParameters[i];
Assert.AreEqual(expectedParameter, actualParameter);
}
}
public void StringLike()
{
const string input = "[name] contains [and] or ([name] startsWith [Alex] and [name]endsWith[ndr])";
const string expectedSql = "where name like @2 or name like @1 and name like @0";
var expectedParameters = new object[]
{
"%ndr", "Alex%", "%and%",
};
var tokens = TokenizationService.Tokenizate(input);
var tree = AstService.Convert(tokens);
var(actualSql, actualParameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty()
.From("name")
.ToSql("name"));
Assert.AreEqual(expectedSql, actualSql.ToString());
Assert.AreEqual(expectedParameters.Length, actualParameters.Length);
for (var i = 0; i < actualParameters.Length; i++)
{
var expectedParameter = expectedParameters[i];
var actualParameter = actualParameters[i];
Assert.AreEqual(expectedParameter, actualParameter);
}
}
public void Escape()
{
const string allowedProperty = "name";
var equations = (from internalOperator in InternalGrammarService.Operators
from internalOperatorDenotation in internalOperator.Denotations
join sqlOperator in SqlGrammarService.Operators
on internalOperator.OperatorType equals sqlOperator.OperatorType
where internalOperator.Arity == Arity.Binary && sqlOperator.Arity == Arity.Binary
select new
{
Input = $"[{allowedProperty}] {internalOperatorDenotation} [{allowedProperty}]",
Sql = $"where {allowedProperty} {sqlOperator.MainDenotation} @0",
})
.ToArray();
foreach (var equation in equations)
{
var inputEquation = equation.Input;
var expectedSql = equation.Sql;
var tokens = TokenizationService.Tokenizate(inputEquation);
var tree = AstService.Convert(tokens);
var(actualSql, actualParameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty()
.From(allowedProperty)
.ToSql(allowedProperty));
Assert.AreEqual(expectedSql, actualSql.ToString());
Assert.AreEqual(1, actualParameters.Length);
Assert.AreEqual(allowedProperty, actualParameters[0]);
}
}
public void RangeMath()
{
const string input = "[location] in ([Moscow], [St. Petersburg]) and [country] in ([ru], [us])";
const string expectedSql = "where location in ( @3 , @2 ) and country in ( @1 , @0 )";
var expectedParameters = new object[]
{
"us", "ru", "St. Petersburg", "Moscow",
};
var tokens = TokenizationService.Tokenizate(input);
var tree = AstService.Convert(tokens);
var(actualSql, actualParameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty()
.From("location")
.ToSql("location"),
SqlProvider.DefineProperty()
.From("country")
.ToSql("country"));
Assert.AreEqual(expectedSql, actualSql.ToString());
Assert.AreEqual(expectedParameters.Length, actualParameters.Length);
for (var i = 0; i < actualParameters.Length; i++)
{
var expectedParameter = expectedParameters[i];
var actualParameter = actualParameters[i];
Assert.AreEqual(expectedParameter, actualParameter);
}
}
public override object Convert(object value, ConvertType convertType)
{
if (Name == ProviderFullName.Oracle)
{
switch (convertType)
{
case ConvertType.NameToQueryParameter:
var qname = (string)value;
//
// Avoid "ORA-00972: identifier is too long" error
// Cause error : You tried to reference a table, cluster, view, index, synonym, tablespace, or username with a value that was longer than 30 characters.
// Resolution : Names for tables, clusters, views, indexes, synonyms, tablespaces, and usernames must be 30 characters or less.
// You must shorten the name to no more than 30 characters for these objects.
//
if (qname.Length > 30)
{
qname = qname.Substring(0, 30);
return(SqlProvider.Convert(qname, convertType));
}
return(SqlProvider.Convert(value, convertType));
}
}
return(base.Convert(value, convertType));
}
public override object Convert(object value, ConvertType convertType)
{
switch (convertType)
{
case ConvertType.ExceptionToErrorNumber:
if (value is SqlCeException)
{
return(((SqlCeException)value).NativeError);
}
break;
}
return(SqlProvider.Convert(value, convertType));
}
public override object Convert(object value, ConvertType convertType)
{
switch (convertType)
{
case ConvertType.ExceptionToErrorNumber:
{
if (value is SQLiteException)
{
return(((SQLiteException)value).ErrorCode);
}
break;
}
}
return(SqlProvider.Convert(value, convertType));
}
public void PageCount()
{
const string equation = "left + right";
const string expectedSql = "where left + right limit 2 offset 2";
var tokens = TokenizationService.Tokenizate(equation);
var tree = AstService.Convert(tokens);
var(where, parameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty().From("left").ToSql("left"),
SqlProvider.DefineProperty().From("right").ToSql("right"));
where.AddPage(1).AddCount(2);
Assert.AreEqual(expectedSql, where.ToString());
Assert.AreEqual(0, parameters.Length);
}
public void Grammar(string equation,
string[] allowedProperties,
string expectedSql,
object[] expectedParameters)
{
var tokens = TokenizationService.Tokenizate(equation);
Tree <AbstractToken> tree;
try
{
tree = AstService.Convert(tokens);
}
catch (Exception e)
{
throw new Exception("Ast fails", e);
}
SqlFilter sql;
object[] parameters;
try
{
var propertyDefinitions = allowedProperties.Select(x => new PropertyDef(x, x)).ToArray();
(sql, parameters) = SqlProvider.Convert(tree, propertyDefinitions);
}
catch (Exception e)
{
throw new Exception("Sql fails", e);
}
Assert.AreEqual(expectedSql, sql.ToString());
Assert.AreEqual(expectedParameters.Length, parameters.Length);
for (var i = 0; i < parameters.Length; i++)
{
var expectedParameter = expectedParameters[i];
var parameter = parameters[i];
Assert.AreEqual(expectedParameter, parameter);
}
}
public override object Convert(object value, ConvertType convertType)
{
switch (convertType)
{
case ConvertType.ExceptionToErrorNumber:
if (value is OleDbException)
{
var ex = (OleDbException)value;
if (ex.Errors.Count > 0)
{
return(ex.Errors[0].NativeError);
}
}
break;
}
return(SqlProvider.Convert(value, convertType));
}
public override object Convert(object value, ConvertType convertType)
{
switch (convertType)
{
case ConvertType.ExceptionToErrorNumber:
if (value is FbException)
{
var ex = (FbException)value;
if (ex.Errors.Count > 0)
{
foreach (FbError error in ex.Errors)
{
return(error.Number);
}
}
}
break;
}
return(SqlProvider.Convert(value, convertType));
}
public override object Convert(object value, ConvertType convertType)
{
switch (convertType)
{
case ConvertType.ExceptionToErrorNumber:
if (value is IfxException)
{
var ex = (IfxException)value;
foreach (IfxError error in ex.Errors)
{
return(error.NativeError);
}
return(0);
}
break;
}
return(SqlProvider.Convert(value, convertType));
}
public void Smoke(string equation,
string[] allowedProperties,
string expectedSql,
object[] expectedParameters)
{
var tokens = TokenizationService.Tokenizate(equation);
var tree = AstService.Convert(tokens);
var propertyDefinitions = allowedProperties.Select(x => new PropertyDef(x, x)).ToArray();
var(sql, parameters) = SqlProvider.Convert(tree, propertyDefinitions);
Assert.AreEqual(expectedSql, sql.ToString());
Assert.AreEqual(expectedParameters.Length, parameters.Length);
for (var i = 0; i < parameters.Length; i++)
{
var expectedParameter = expectedParameters[i];
var parameter = parameters[i];
Assert.AreEqual(expectedParameter, parameter);
}
}
public override object Convert(object value, ConvertType convertType)
{
switch (convertType)
{
case ConvertType.NameToCommandParameter:
case ConvertType.NameToSprocParameter:
return(ParameterPrefix == null? value: ParameterPrefix + value);
case ConvertType.SprocParameterToName:
var name = (string)value;
if (name.Length > 0)
{
if (name[0] == ':')
{
return(name.Substring(1));
}
if (ParameterPrefix != null &&
name.ToUpper(CultureInfo.InvariantCulture).StartsWith(ParameterPrefix))
{
return(name.Substring(ParameterPrefix.Length));
}
}
break;
case ConvertType.ExceptionToErrorNumber:
if (value is OracleException)
{
return(((OracleException)value).Code);
}
break;
}
return(SqlProvider.Convert(value, convertType));
}
public void Mess()
{
const string input =
"[id] === [1690ffef-7249-4384-8cba-58842e8d48df] and (([length] + 1) * 2 <= 14 or [email] = [email])";
const string expectedSql = "where id = @4 and ( ( table.length + @3 ) * @2 <= @1 or table2.email = @0 )";
var expectedParameters = new object[]
{
"email", 14, 2, 1, Guid.Parse("1690ffef-7249-4384-8cba-58842e8d48df"),
};
var tokens = TokenizationService.Tokenizate(input);
var tree = AstService.Convert(tokens);
var(actualSql, actualParameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty().From("id").ToSql("id"),
SqlProvider.DefineProperty()
.From("length")
.ToSql("table.length"),
SqlProvider.DefineProperty()
.From("email")
.ToSql("table2.email"));
Assert.AreEqual(expectedSql, actualSql.ToString());
Assert.AreEqual(expectedParameters.Length, actualParameters.Length);
for (var i = 0; i < actualParameters.Length; i++)
{
var expectedParameter = expectedParameters[i];
var actualParameter = actualParameters[i];
Assert.AreEqual(expectedParameter, actualParameter);
}
}
public void PropertyRemapping()
{
const string input = "[storesCount] > 3 or [salaryCostsPerMonth] > 10000 * [dollarCourse]";
const string expectedSql = "where storesCount > @1 or salarySlice.perMonth > @0 * bank.currentDollarCourse";
var expectedParameters = new object[]
{
10000, 3,
};
var tokens = TokenizationService.Tokenizate(input);
var tree = AstService.Convert(tokens);
var(actualSql, actualParameters) = SqlProvider.Convert(tree,
SqlProvider.DefineProperty()
.From("storesCount")
.ToSql("storesCount"),
SqlProvider.DefineProperty()
.From("salaryCostsPerMonth")
.ToSql("salarySlice.perMonth"),
SqlProvider.DefineProperty()
.From("dollarCourse")
.ToSql("bank.currentDollarCourse"));
Assert.AreEqual(expectedSql, actualSql.ToString());
Assert.AreEqual(expectedParameters.Length, actualParameters.Length);
for (var i = 0; i < actualParameters.Length; i++)
{
var expectedParameter = expectedParameters[i];
var actualParameter = actualParameters[i];
Assert.AreEqual(expectedParameter, actualParameter);
}
}
public virtual object Convert(object value, ConvertType convertType)
{
return(SqlProvider.Convert(value, convertType));
}
public override object Convert(object value, ConvertType convertType)
{
switch (convertType)
{
case ConvertType.ExceptionToErrorNumber:
if (value is AseException)
{
var ex = (AseException)value;
foreach (AseError error in ex.Errors)
{
if (error.IsError)
{
return(error.MessageNumber);
}
}
foreach (AseError error in ex.Errors)
{
if (error.MessageNumber != 0)
{
return(error.MessageNumber);
}
}
return(0);
}
break;
case ConvertType.ExceptionToErrorMessage:
if (value is AseException)
{
try
{
var ex = (AseException)value;
var sb = new StringBuilder();
foreach (AseError error in ex.Errors)
{
if (error.IsError)
{
sb.AppendFormat("{0} Ln: {1}{2}",
error.Message.TrimEnd('\n', '\r'), error.LineNum, Environment.NewLine);
}
}
foreach (AseError error in ex.Errors)
{
if (!error.IsError)
{
sb.AppendFormat("* {0}{1}", error.Message, Environment.NewLine);
}
}
return(sb.Length == 0 ? ex.Message : sb.ToString());
}
catch
{
}
}
break;
}
return(SqlProvider.Convert(value, convertType));
}