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))); }