public override void VisitSetVariableStatement(SetVariableStatement setVariableStatement)
{
//Parameters from other sources go before inline parameters
if (!_visitorMetadata.Parameters.Contains(setVariableStatement.VariableReference.Name))
{
if (setVariableStatement.ScalarExpression is StringLiteral stringLiteral)
{
_visitorMetadata.Parameters.Add(SqlParameter.Create(setVariableStatement.VariableReference.Name, stringLiteral.Value));
}
else if (setVariableStatement.ScalarExpression is IntegerLiteral integerLiteral)
{
_visitorMetadata.Parameters.Add(SqlParameter.Create(setVariableStatement.VariableReference.Name, integerLiteral.Value));
}
else if (setVariableStatement.ScalarExpression is NumericLiteral numericLiteral)
{
_visitorMetadata.Parameters.Add(SqlParameter.Create(setVariableStatement.VariableReference.Name, numericLiteral.Value));
}
else if (setVariableStatement.ScalarExpression is Base64Literal base64Literal)
{
var decodedString = Encoding.UTF8.GetString(Convert.FromBase64String(base64Literal.Value));
_visitorMetadata.Parameters.Add(SqlParameter.Create(setVariableStatement.VariableReference.Name, decodedString));
}
else if (setVariableStatement.ScalarExpression is BooleanLiteral booleanLiteral)
{
_visitorMetadata.Parameters.Add(SqlParameter.Create(setVariableStatement.VariableReference.Name, booleanLiteral.Value));
}
else
{
throw new NotImplementedException($"The parameter type: {setVariableStatement.ScalarExpression.GetType().Name} is not implemented");
}
}
}
public override SqlObject VisitLimit_count([NotNull] sqlParser.Limit_countContext context)
{
Contract.Requires(context != null);
SqlLimitSpec sqlLimitSpec;
if (context.NUMERIC_LITERAL() != null)
{
sqlLimitSpec = SqlLimitSpec.Create(
SqlNumberLiteral.Create(
CstToAstVisitor.GetNumber64ValueFromNode(
context.NUMERIC_LITERAL())));
}
else if (context.PARAMETER() != null)
{
sqlLimitSpec = SqlLimitSpec.Create(
SqlParameter.Create(
context.PARAMETER().GetText()));
}
else
{
throw new NotImplementedException();
}
return(sqlLimitSpec);
}
public override SqlObject Visit(SqlParameter sqlParameter)
{
return(SqlParameter.Create(
this.GetObfuscatedString(
sqlParameter.Name,
"param",
ref this.paramaterSequenceNumber)));
}
public void TestSearchFunctionWithParameter()
{
Assert.That(async() =>
{
SqlParameters sqlParameters = new SqlParameters()
.Add(SqlParameter.Create("P0", "test"));
await SqlExecutor.Execute("SELECT Orderkey, Orderpriority FROM \"order\" WHERE CONTAINS(*, @P0)", sqlParameters);
}, Throws.InstanceOf <SqlErrorException>().With.Message.EqualTo("Search is not implemented for this table"));
}
public void TestInPredicateCantConvertTypeWithParameters()
{
Assert.ThrowsAsync <SqlErrorException>(async() =>
{
var parameters = new SqlParameters().Add(SqlParameter.Create("P0", "test"));
await SqlExecutor.Execute("SELECT * FROM \"enumtable\" WHERE enum in (@P0)", parameters);
},
"Value 'test' could not be converted to type: 'Koralium.SqlToExpression.Tests.Models.Enum'"
);
}
public async Task TestStringLikeContainsParameter()
{
var parameters = new SqlParameters()
.Add(SqlParameter.Create("Parameter", "L"));
var result = await SqlExecutor.Execute("SELECT Orderkey, Orderpriority FROM \"order\" WHERE Orderpriority like '%' + @Parameter + '%'", parameters);
var expected = TpchData.Orders
.Where(x => x.Orderpriority.Contains("L"))
.Select(x => new { x.Orderkey, x.Orderpriority })
.AsQueryable();
AssertAreEqual(expected, result.Result);
}
public async Task TestWhereEnumEqualsIntParameter()
{
var parameters = new SqlParameters()
.Add(SqlParameter.Create("Parameter", 0));
var result = await SqlExecutor.Execute("SELECT enum FROM \"enumtable\" WHERE enum = @Parameter", parameters);
var expected = TestData.GetEnumTestData()
.Where(x => x.Enum == Models.Enum.testval)
.Select(x => new { x.Enum })
.AsQueryable();
AssertAreEqual(expected, result.Result);
}
/// <summary>
/// 创建新的Sql IN条件语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="values">数据集合</param>
/// <returns>Sql条件语句</returns>
public static SqlInsideParametersCondition In(String columnName, params Object[] values)
{
List <SqlParameter> parameters = new List <SqlParameter>();
if (values != null)
{
for (Int32 i = 0; i < values.Length; i++)
{
parameters.Add(SqlParameter.Create(columnName, columnName + "_" + i.ToString(), values[i]));
}
}
return(new SqlInsideParametersCondition(parameters));
}
public async Task TestParameter()
{
var parameters = new SqlParameters()
.Add(SqlParameter.Create("@custkey", 10));
var result = await SqlExecutor.Execute($"SELECT c.name FROM customer c where c.custkey > @custkey", parameters);
var expected = TpchData.Customers
.Where(x => x.Custkey > 10)
.Select(x =>
new { x.Name }
).AsQueryable();
AssertAreEqual(expected, result.Result);
}
public async Task TestInPredicateParameters()
{
var parameters = new SqlParameters().Add(SqlParameter.Create("P0", "5-LOW"));
var result = await SqlExecutor.Execute("SELECT Orderkey, Orderpriority FROM \"order\" WHERE Orderpriority in (@P0)", parameters);
var expectedList = new List <string> {
"5-LOW"
};
var expected = TpchData.Orders
.Where(x => expectedList.Contains(x.Orderpriority))
.Select(x => new { x.Orderkey, x.Orderpriority })
.AsQueryable();
AssertAreEqual(expected, result.Result);
}
/// <summary>
/// 创建新的Sql IN条件语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="dbType">数据类型</param>
/// <param name="values">逗号分隔的数据集合</param>
/// <returns>Sql条件语句</returns>
public static SqlInsideParametersCondition In(String columnName, DbType dbType, String values)
{
List <SqlParameter> parameters = new List <SqlParameter>();
if (!String.IsNullOrEmpty(values))
{
String[] valuesArray = values.Split(',');
for (Int32 i = 0; i < valuesArray.Length; i++)
{
parameters.Add(SqlParameter.Create(columnName, columnName + "_" + i.ToString(), dbType, valuesArray[i]));
}
}
return(new SqlInsideParametersCondition(parameters));
}
public async Task TestLimitOffsetWithParameter()
{
var parameters = new SqlParameters()
.Add(SqlParameter.Create("@limit", 10))
.Add(SqlParameter.Create("@offset", 10));
var result = await SqlExecutor.Execute($"SELECT name FROM customer LIMIT @limit OFFSET @offset", parameters);
var expected = TpchData.Customers
.Select(x =>
new { x.Name }
)
.Skip(10)
.Take(10)
.AsQueryable();
AssertAreEqual(expected, result.Result);
}
/// <summary>
/// 创建新的Sql IN条件语句
/// </summary>
/// <typeparam name="T">数据类型</typeparam>
/// <param name="columnName">字段名</param>
/// <param name="dbType">数据类型</param>
/// <param name="values">逗号分隔的数据集合</param>
/// <returns>Sql条件语句</returns>
public static SqlInsideParametersCondition In <T>(String columnName, DbType dbType, String values) where T : IConvertible
{
List <SqlParameter> parameters = new List <SqlParameter>();
if (!String.IsNullOrEmpty(values))
{
String[] valuesArray = values.Split(',');
Type t = typeof(T);
for (Int32 i = 0; i < valuesArray.Length; i++)
{
Object value = Convert.ChangeType(valuesArray[i], t);
parameters.Add(SqlParameter.Create(columnName, columnName + "_" + i.ToString(), dbType, value));
}
}
return(new SqlInsideParametersCondition(parameters));
}
public override SqlObject VisitTop_spec([NotNull] sqlParser.Top_specContext context)
{
Contract.Requires(context != null);
SqlTopSpec sqlTopSpec;
if (context.NUMERIC_LITERAL() != null)
{
Number64 topCount = CstToAstVisitor.GetNumber64ValueFromNode(context.NUMERIC_LITERAL());
sqlTopSpec = SqlTopSpec.Create(SqlNumberLiteral.Create(topCount));
}
else if (context.PARAMETER() != null)
{
sqlTopSpec = SqlTopSpec.Create(SqlParameter.Create(context.PARAMETER().GetText()));
}
else
{
throw new InvalidOperationException();
}
return(sqlTopSpec);
}
/// <summary>
/// 创建判断是否开头不包含的Sql条件语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="value">数据</param>
/// <returns>Sql条件语句</returns>
public static SqlBasicParameterCondition NotLikeStartWith(String columnName, String value)
{
return(SqlCondition.NotLike(SqlParameter.Create(columnName, "%" + value)));
}
/// <summary>
/// 插入指定参数并返回当前语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="dbType">数据类型</param>
/// <param name="value">内容</param>
/// <returns>当前语句</returns>
public InsertCommand Add(String columnName, DbType dbType, Object value)
{
this._parameters.Add(SqlParameter.Create(columnName, dbType, value));
return(this);
}
/// <summary>
/// 创建判断是否非空的Sql条件语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <returns>Sql条件语句</returns>
public static SqlBasicParameterCondition IsNotNull(String columnName)
{
return(SqlCondition.IsNotNull(SqlParameter.Create(columnName, null)));
}
/// <summary>
/// 创建判断是否结尾不包含的Sql条件语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="paramName">参数名</param>
/// <param name="value">数据</param>
/// <returns>Sql查询语句类</returns>
public static SqlBasicParameterCondition NotLikeEndWith(String columnName, String paramName, String value)
{
return(SqlCondition.NotLike(SqlParameter.Create(columnName, paramName, value + "%")));
}
/// <summary>
/// 创建判断是否不在范围内的Sql条件语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="paramName">参数名</param>
/// <param name="valueOne">数据一</param>
/// <param name="valueTwo">数据二</param>
/// <returns>Sql查询语句类</returns>
public static SqlBasicParameterCondition NotBetween(String columnName, String paramName, Object valueOne, Object valueTwo)
{
return(SqlCondition.NotBetween(SqlParameter.Create(columnName, paramName + "_One", valueOne), SqlParameter.Create(columnName, paramName + "_Two", valueTwo)));
}
/// <summary>
/// 创建判断是否大于的Sql条件语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="value">数据</param>
/// <returns>Sql条件语句</returns>
public static SqlBasicParameterCondition GreaterThan(String columnName, Object value)
{
return(SqlCondition.GreaterThan(SqlParameter.Create(columnName, value)));
}
/// <summary>
/// 创建判断是否小于等于的Sql条件语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="paramName">参数名</param>
/// <param name="value">数据</param>
/// <returns>Sql查询语句类</returns>
public static SqlBasicParameterCondition LessThanOrEqual(String columnName, String paramName, Object value)
{
return(SqlCondition.LessThanOrEqual(SqlParameter.Create(columnName, paramName, value)));
}
public override SqlObject VisitParameterRefScalarExpression([NotNull] sqlParser.ParameterRefScalarExpressionContext context)
{
Contract.Requires(context != null);
return(SqlParameterRefScalarExpression.Create(SqlParameter.Create(context.PARAMETER().GetText())));
}
/// <summary>
/// 创建判断是否包含的Sql条件语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="paramName">参数名</param>
/// <param name="value">数据</param>
/// <returns>Sql条件语句</returns>
public static SqlBasicParameterCondition LikeAll(String columnName, String paramName, String value)
{
return(SqlCondition.Like(SqlParameter.Create(columnName, paramName, "%" + value + "%")));
}
/// <summary>
/// 更新指定参数并返回当前语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="paramName">参数名称</param>
/// <param name="dbType">数据类型</param>
/// <param name="value">内容</param>
/// <returns>当前语句</returns>
public UpdateCommand Set(String columnName, String paramName, DbType dbType, Object value)
{
this._parameters.Add(SqlParameter.Create(columnName, paramName, dbType, value));
return(this);
}
/// <summary>
/// 创建新的Sql条件语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="paramName">参数名</param>
/// <param name="op">条件运算符</param>
/// <param name="dbType">数据类型</param>
/// <param name="value">数据</param>
/// <returns>Sql条件语句</returns>
public static SqlBasicParameterCondition Create(String columnName, String paramName, SqlOperator op, DbType dbType, Object value)
{
return(new SqlBasicParameterCondition(SqlParameter.Create(columnName, paramName, dbType, value), op));
}
/// <summary>
/// 创建新的Sql条件语句
/// </summary>
/// <param name="function">合计函数类型</param>
/// <param name="fieldName">要查询的字段名</param>
/// <param name="op">条件运算符</param>
/// <param name="value">数据</param>
/// <returns>Sql条件语句</returns>
public static SqlBasicParameterCondition Create(SqlAggregateFunction function, String fieldName, SqlOperator op, Object value)
{
return(new SqlBasicParameterCondition(SqlParameter.Create(String.Format("{0}({1})", function.ToString().ToUpperInvariant(), fieldName), value), op));
}
public async ValueTask <Transport.QueryResult> Execute(string sql, SqlParameters sqlParameters, HttpContext httpContext)
{
_logger.LogInformation("Executing query: " + sql);
foreach (var header in httpContext.Request.Headers)
{
if (header.Key.StartsWith("P_", StringComparison.OrdinalIgnoreCase))
{
var parameterName = header.Key.Substring(2);
if (header.Value.Count > 1)
{
throw new SqlErrorException("Two parameters found with the same name in the http headers.");
}
var value = header.Value.First();
// URL decode
value = HttpUtility.UrlDecode(value, Encoding.UTF8);
sqlParameters.Add(SqlParameter.Create(parameterName, value));
}
}
//Parse the sql
var sqlTree = _sqlParser.Parse(sql, out var errors);
//Check for parsing errors
if (errors.Count > 0)
{
throw new SqlErrorException(errors.First().Message);
}
//Apply the row level security filter on the query
await RowLevelSecurityHelper.ApplyRowLevelSecurity(sqlTree, httpContext, _metadataStore, _serviceProvider);
//Only calculate the sql after the row level security if logging level is debug
_logger.LogConditionally(LogLevel.Debug, logger => logger.LogDebug($"Sql after row level security: {sqlTree.Print()}"));
CustomMetadataStore customMetadataStore = new CustomMetadataStore();
var result = await _sqlExecutor.Execute(sqlTree, sqlParameters, new TableResolverData(
httpContext,
_serviceProvider,
customMetadataStore)).ConfigureAwait(false);
var columnsBuilder = ImmutableList.CreateBuilder <Transport.Column>();
foreach (var column in result.Columns)
{
if (!_metadataStore.TryGetTypeColumns(column.Type, out var columns))
{
columns = new List <TableColumn>();
}
var childrenList = ImmutableList.CreateBuilder <Transport.Column>();
foreach (var child in columns)
{
childrenList.Add(GetTransportColumn(child));
}
var(columnType, nullable) = ColumnTypeHelper.GetKoraliumType(column.Type);
columnsBuilder.Add(new Transport.Column(column.Name, column.Type, column.GetFunction, childrenList.ToImmutable(), columnType, nullable));
}
return(new Transport.QueryResult(
result.Result,
columnsBuilder.ToImmutable(),
customMetadataStore.GetMetadataValues().Select(x => new KeyValuePair <string, string>(x.Key, x.Value.ToString()))
));
}
public static SqlParameter DecodeParameter(KeyValue parameter)
{
return(SqlParameter.Create(parameter.Name, ScalarDecoder.DecodeScalar(parameter.Value)));
}
/// <summary>
/// 创建判断是否相似的Sql条件语句
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="value">数据</param>
/// <returns>Sql条件语句</returns>
public static SqlBasicParameterCondition Like(String columnName, String value)
{
return(SqlCondition.Like(SqlParameter.Create(columnName, value)));
}