static void ConcatSearchCondition(SelectQuery.WhereClause where1, SelectQuery.WhereClause where2) { if (where1.IsEmpty) { where1.SearchCondition.Conditions.AddRange(where2.SearchCondition.Conditions); } else { if (where1.SearchCondition.Precedence < Precedence.LogicalConjunction) { var sc1 = new SelectQuery.SearchCondition(); sc1.Conditions.AddRange(where1.SearchCondition.Conditions); where1.SearchCondition.Conditions.Clear(); where1.SearchCondition.Conditions.Add(new SelectQuery.Condition(false, sc1)); } if (where2.SearchCondition.Precedence < Precedence.LogicalConjunction) { var sc2 = new SelectQuery.SearchCondition(); sc2.Conditions.AddRange(where2.SearchCondition.Conditions); where1.SearchCondition.Conditions.Add(new SelectQuery.Condition(false, sc2)); } else { where1.SearchCondition.Conditions.AddRange(where2.SearchCondition.Conditions); } } }
protected override void BuildFunction(SqlFunction func) { func = ConvertFunctionParameters(func); switch (func.Name) { case "CASE": func = ConvertCase(func.SystemType, func.Parameters, 0); break; case "Coalesce": if (func.Parameters.Length > 2) { var parms = new ISqlExpression[func.Parameters.Length - 1]; Array.Copy(func.Parameters, 1, parms, 0, parms.Length); BuildFunction(new SqlFunction(func.SystemType, func.Name, func.Parameters[0], new SqlFunction(func.SystemType, func.Name, parms))); return; } var sc = new SelectQuery.SearchCondition(); sc.Conditions.Add(new SelectQuery.Condition(false, new SelectQuery.Predicate.IsNull(func.Parameters[0], false))); func = new SqlFunction(func.SystemType, "IIF", sc, func.Parameters[1], func.Parameters[0]); break; } base.BuildFunction(func); }
void AddSearchConditions(SelectQuery.SearchCondition search, IEnumerable <SelectQuery.Condition> conditions) { if (_additionalFilter == null) { _additionalFilter = new Dictionary <SelectQuery.SearchCondition, SelectQuery.SearchCondition>(); } SelectQuery.SearchCondition value; if (!_additionalFilter.TryGetValue(search, out value)) { if (search.Conditions.Count > 0 && search.Precedence < Precedence.LogicalConjunction) { value = new SelectQuery.SearchCondition(); var prev = new SelectQuery.SearchCondition(); prev.Conditions.AddRange(search.Conditions); search.Conditions.Clear(); search.Conditions.Add(new SelectQuery.Condition(false, value, false)); search.Conditions.Add(new SelectQuery.Condition(false, prev, false)); } else { value = search; } _additionalFilter.Add(search, value); } value.Conditions.AddRange(conditions); }
protected override void BuildFunction(SqlFunction func) { func = ConvertFunctionParameters(func); switch (func.Name) { case "CASE" : func = ConvertCase(func.SystemType, func.Parameters, 0); break; case "Coalesce" : if (func.Parameters.Length > 2) { var parms = new ISqlExpression[func.Parameters.Length - 1]; Array.Copy(func.Parameters, 1, parms, 0, parms.Length); BuildFunction(new SqlFunction(func.SystemType, func.Name, func.Parameters[0], new SqlFunction(func.SystemType, func.Name, parms))); return; } var sc = new SelectQuery.SearchCondition(); sc.Conditions.Add(new SelectQuery.Condition(false, new SelectQuery.Predicate.IsNull(func.Parameters[0], false))); func = new SqlFunction(func.SystemType, "IIF", sc, func.Parameters[1], func.Parameters[0]); break; } base.BuildFunction(func); }
void OptimizeSearchCondition(SelectQuery.SearchCondition searchCondition) { var items = searchCondition.Conditions; if (items.Any(c => c.IsOr)) { return; } for (var i1 = 0; i1 < items.Count; i1++) { var c1 = items[i1]; var cmp = EvaluateLogical(c1); if (cmp != null) { if (cmp.Value) { items.RemoveAt(i1); --i1; continue; } } switch (c1.ElementType) { case QueryElementType.Condition: case QueryElementType.SearchCondition: { var search = c1.Predicate as SelectQuery.SearchCondition; if (search != null) { OptimizeSearchCondition(search); if (search.Conditions.Count == 0) { items.RemoveAt(i1); --i1; continue; } } break; } } for (var i2 = i1 + 1; i2 < items.Count; i2++) { var c2 = items[i2]; if (CompareConditions(c2, c1)) { searchCondition.Conditions.RemoveAt(i2); --i2; } } } }
protected ISqlExpression AlternativeExists(SqlFunction func) { var query = (SelectQuery)func.Parameters[0]; if (query.Select.Columns.Count == 0) { query.Select.Columns.Add(new SelectQuery.Column(query, new SqlExpression("'.'"))); } query.Select.Take(1, null); var sc = new SelectQuery.SearchCondition(); sc.Conditions.Add( new SelectQuery.Condition(false, new SelectQuery.Predicate.IsNull(query, true))); return(sc); }
protected override void BuildWhereSearchCondition(SelectQuery.SearchCondition condition) { if (NeedTake && !NeedSkip && SelectQuery.OrderBy.IsEmpty && SelectQuery.Having.IsEmpty) { BuildPredicate( Precedence.LogicalConjunction, new SelectQuery.Predicate.ExprExpr( new SqlExpression(null, "ROWNUM", Precedence.Primary), SelectQuery.Predicate.Operator.LessOrEqual, SelectQuery.Select.TakeValue)); if (base.BuildWhere()) { StringBuilder.Append(" AND "); BuildSearchCondition(Precedence.LogicalConjunction, condition); } } else { BuildSearchCondition(Precedence.Unknown, condition); } }
//this is for Tests.Linq.Common.CoalesceLike test static SqlFunction ConvertCase(Type systemType, ISqlExpression[] parameters, int start) { var len = parameters.Length - start; const string name = "CASE"; var cond = parameters[start]; if (start == 0 && SqlExpression.NeedsEqual(cond)) { cond = new SelectQuery.SearchCondition( new SelectQuery.Condition( false, new SelectQuery.Predicate.ExprExpr(cond, SelectQuery.Predicate.Operator.Equal, new SqlValue(1)))); } if (len == 3) { return(new SqlFunction(systemType, name, cond, parameters[start + 1], parameters[start + 2])); } return(new SqlFunction(systemType, name, cond, parameters[start + 1], ConvertCase(systemType, parameters, start + 2))); }
protected override void BuildFunction(SqlFunction func) { switch (func.Name) { case "Coalesce" : if (func.Parameters.Length > 2) { var parms = new ISqlExpression[func.Parameters.Length - 1]; Array.Copy(func.Parameters, 1, parms, 0, parms.Length); BuildFunction(new SqlFunction(func.SystemType, func.Name, func.Parameters[0], new SqlFunction(func.SystemType, func.Name, parms))); return; } var sc = new SelectQuery.SearchCondition(); sc.Conditions.Add(new SelectQuery.Condition(false, new SelectQuery.Predicate.IsNull(func.Parameters[0], false))); func = new SqlFunction(func.SystemType, "Iif", sc, func.Parameters[1], func.Parameters[0]); break; case "CASE" : func = ConvertCase(func.SystemType, func.Parameters, 0); break; case "CharIndex" : func = func.Parameters.Length == 2? new SqlFunction(func.SystemType, "InStr", new SqlValue(1), func.Parameters[1], func.Parameters[0], new SqlValue(1)): new SqlFunction(func.SystemType, "InStr", func.Parameters[2], func.Parameters[1], func.Parameters[0], new SqlValue(1)); break; case "Convert" : switch (func.SystemType.ToUnderlying().GetTypeCodeEx()) { case TypeCode.String : func = new SqlFunction(func.SystemType, "CStr", func.Parameters[1]); break; case TypeCode.DateTime : if (IsDateDataType(func.Parameters[0], "Date")) func = new SqlFunction(func.SystemType, "DateValue", func.Parameters[1]); else if (IsTimeDataType(func.Parameters[0])) func = new SqlFunction(func.SystemType, "TimeValue", func.Parameters[1]); else func = new SqlFunction(func.SystemType, "CDate", func.Parameters[1]); break; default: if (func.SystemType == typeof(DateTime)) goto case TypeCode.DateTime; BuildExpression(func.Parameters[1]); return; } break; } base.BuildFunction(func); }
internal static void OptimizeSearchCondition(SelectQuery.SearchCondition searchCondition) { // This 'if' could be replaced by one simple match: // // match (searchCondition.Conditions) // { // | [SearchCondition(true, _) sc] => // searchCondition.Conditions = sc.Conditions; // OptimizeSearchCondition(searchCodition) // // | [SearchCondition(false, [SearchCondition(true, [ExprExpr]) sc])] => ... // // | [Expr(true, SqlValue(true))] // | [Expr(false, SqlValue(false))] // searchCondition.Conditions = [] // } // // One day I am going to rewrite all this crap in Nemerle. // if (searchCondition.Conditions.Count == 1) { var cond = searchCondition.Conditions[0]; if (cond.Predicate is SelectQuery.SearchCondition) { var sc = (SelectQuery.SearchCondition)cond.Predicate; if (!cond.IsNot) { searchCondition.Conditions.Clear(); searchCondition.Conditions.AddRange(sc.Conditions); OptimizeSearchCondition(searchCondition); return; } if (sc.Conditions.Count == 1) { var c1 = sc.Conditions[0]; if (!c1.IsNot && c1.Predicate is SelectQuery.Predicate.ExprExpr) { var ee = (SelectQuery.Predicate.ExprExpr)c1.Predicate; SelectQuery.Predicate.Operator op; switch (ee.Operator) { case SelectQuery.Predicate.Operator.Equal: op = SelectQuery.Predicate.Operator.NotEqual; break; case SelectQuery.Predicate.Operator.NotEqual: op = SelectQuery.Predicate.Operator.Equal; break; case SelectQuery.Predicate.Operator.Greater: op = SelectQuery.Predicate.Operator.LessOrEqual; break; case SelectQuery.Predicate.Operator.NotLess: case SelectQuery.Predicate.Operator.GreaterOrEqual: op = SelectQuery.Predicate.Operator.Less; break; case SelectQuery.Predicate.Operator.Less: op = SelectQuery.Predicate.Operator.GreaterOrEqual; break; case SelectQuery.Predicate.Operator.NotGreater: case SelectQuery.Predicate.Operator.LessOrEqual: op = SelectQuery.Predicate.Operator.Greater; break; default: throw new InvalidOperationException(); } c1.Predicate = new SelectQuery.Predicate.ExprExpr(ee.Expr1, op, ee.Expr2); searchCondition.Conditions.Clear(); searchCondition.Conditions.AddRange(sc.Conditions); OptimizeSearchCondition(searchCondition); return; } } } if (cond.Predicate.ElementType == QueryElementType.ExprPredicate) { var expr = (SelectQuery.Predicate.Expr)cond.Predicate; if (expr.Expr1 is SqlValue) { var value = (SqlValue)expr.Expr1; if (value.Value is bool) { if (cond.IsNot ? !(bool)value.Value : (bool)value.Value) { searchCondition.Conditions.Clear(); } } } } } for (var i = 0; i < searchCondition.Conditions.Count; i++) { var cond = searchCondition.Conditions[i]; if (cond.Predicate.ElementType == QueryElementType.ExprPredicate) { var expr = (SelectQuery.Predicate.Expr)cond.Predicate; if (expr.Expr1 is SqlValue) { var value = (SqlValue)expr.Expr1; if (value.Value is bool) { if (cond.IsNot ? !(bool)value.Value : (bool)value.Value) { if (i > 0) { if (searchCondition.Conditions[i - 1].IsOr) { searchCondition.Conditions.RemoveRange(0, i); OptimizeSearchCondition(searchCondition); break; } } } } } } else if (cond.Predicate is SelectQuery.SearchCondition) { var sc = (SelectQuery.SearchCondition)cond.Predicate; OptimizeSearchCondition(sc); if (sc.Conditions.Count == 0) { if (cond.IsOr) { searchCondition.Conditions.Clear(); break; } searchCondition.Conditions.RemoveAt(i); --i; } } } }
protected override void BuildFunction(SqlFunction func) { switch (func.Name) { case "Coalesce": if (func.Parameters.Length > 2) { var parms = new ISqlExpression[func.Parameters.Length - 1]; Array.Copy(func.Parameters, 1, parms, 0, parms.Length); BuildFunction(new SqlFunction(func.SystemType, func.Name, func.Parameters[0], new SqlFunction(func.SystemType, func.Name, parms))); return; } var sc = new SelectQuery.SearchCondition(); sc.Conditions.Add(new SelectQuery.Condition(false, new SelectQuery.Predicate.IsNull(func.Parameters[0], false))); func = new SqlFunction(func.SystemType, "Iif", sc, func.Parameters[1], func.Parameters[0]); break; case "CASE": func = ConvertCase(func.SystemType, func.Parameters, 0); break; case "CharIndex": func = func.Parameters.Length == 2? new SqlFunction(func.SystemType, "InStr", new SqlValue(1), func.Parameters[1], func.Parameters[0], new SqlValue(1)): new SqlFunction(func.SystemType, "InStr", func.Parameters[2], func.Parameters[1], func.Parameters[0], new SqlValue(1)); break; case "Convert": switch (func.SystemType.ToUnderlying().GetTypeCodeEx()) { case TypeCode.String: func = new SqlFunction(func.SystemType, "CStr", func.Parameters[1]); break; case TypeCode.DateTime: if (IsDateDataType(func.Parameters[0], "Date")) { func = new SqlFunction(func.SystemType, "DateValue", func.Parameters[1]); } else if (IsTimeDataType(func.Parameters[0])) { func = new SqlFunction(func.SystemType, "TimeValue", func.Parameters[1]); } else { func = new SqlFunction(func.SystemType, "CDate", func.Parameters[1]); } break; default: if (func.SystemType == typeof(DateTime)) { goto case TypeCode.DateTime; } BuildExpression(func.Parameters[1]); return; } break; } base.BuildFunction(func); }
//this is for Tests.Linq.Common.CoalesceLike test static SqlFunction ConvertCase(Type systemType, ISqlExpression[] parameters, int start) { var len = parameters.Length - start; const string name = "CASE"; var cond = parameters[start]; if (start == 0 && SqlExpression.NeedsEqual(cond)) { cond = new SelectQuery.SearchCondition( new SelectQuery.Condition( false, new SelectQuery.Predicate.ExprExpr(cond, SelectQuery.Predicate.Operator.Equal, new SqlValue(1)))); } if (len == 3) return new SqlFunction(systemType, name, cond, parameters[start + 1], parameters[start + 2]); return new SqlFunction(systemType, name, cond, parameters[start + 1], ConvertCase(systemType, parameters, start + 2)); }
void AddSearchCondition(SelectQuery.SearchCondition search, SelectQuery.Condition condition) { AddSearchConditions(search, new[] { condition }); }
static void ConcatSearchCondition(SelectQuery.WhereClause where1, SelectQuery.WhereClause where2) { if (where1.IsEmpty) { where1.SearchCondition.Conditions.AddRange(where2.SearchCondition.Conditions); } else { if (where1.SearchCondition.Precedence < Precedence.LogicalConjunction) { var sc1 = new SelectQuery.SearchCondition(); sc1.Conditions.AddRange(where1.SearchCondition.Conditions); where1.SearchCondition.Conditions.Clear(); where1.SearchCondition.Conditions.Add(new SelectQuery.Condition(false, sc1)); } if (where2.SearchCondition.Precedence < Precedence.LogicalConjunction) { var sc2 = new SelectQuery.SearchCondition(); sc2.Conditions.AddRange(where2.SearchCondition.Conditions); where1.SearchCondition.Conditions.Add(new SelectQuery.Condition(false, sc2)); } else where1.SearchCondition.Conditions.AddRange(where2.SearchCondition.Conditions); } }