/// <summary> /// Converts a single Expression to SQL (mapping the columns as appropriate) and appends /// to the given string builder. /// /// Remember to wrap the SQL in parends if necessary. /// </summary> /// <param name="queryToAddTo">Query we're adding the expression to.</param> /// <param name="expr">The expression. NOTE: It should NOT be null. This method does not check.</param> /// <param name="mapping">Class mapping for the class we're dealing with.</param> /// <param name="colPrefix">What to prefix column names with, I.E. "Table." for "Table.Column". /// May be null if no prefix is desired. May be something other than /// the table name if the tables are being aliased.</param> /// <param name="booleanOperator">The boolean operator (AND or OR) to insert before /// this expression. Blank ("") if we don't need one.</param> /// <returns>Whether or not this expression modified the sql string. /// Typically true, but may be false for special query types /// that use other parameters for certain types of expressions.</returns> protected virtual bool ExpressionToQuery(SqlDaQuery queryToAddTo, IExpression expr, ClassMapping mapping, string colPrefix, string booleanOperator) { string col; Type dbDataType; bool trueOrNot = expr.TrueOrNot(); // add the operator if one was specified. queryToAddTo.Sql.Append(booleanOperator); // Add some parends. queryToAddTo.Sql.Append("("); if (expr is BetweenExpression) { BetweenExpression between = (BetweenExpression)expr; col = colPrefix + mapping.AllDataColsByObjAttrs[between.Property]; dbDataType = mapping.DataColTypesByObjAttr[between.Property]; queryToAddTo.Sql.Append(col); queryToAddTo.Sql.Append(trueOrNot ? " >= " : " < "); AppendParameter(queryToAddTo, between.Min, dbDataType); queryToAddTo.Sql.Append(trueOrNot ? " AND " : " OR "); queryToAddTo.Sql.Append(col); queryToAddTo.Sql.Append(trueOrNot ? " <= " : " > "); AppendParameter(queryToAddTo, between.Max, dbDataType); } else if (expr is EqualExpression) { EqualExpression equal = (EqualExpression)expr; col = colPrefix + mapping.AllDataColsByObjAttrs[equal.Property]; if (equal.Value == null) { queryToAddTo.Sql.Append(col); queryToAddTo.Sql.Append(trueOrNot ? " IS NULL" : " IS NOT NULL"); } else { queryToAddTo.Sql.Append(col); queryToAddTo.Sql.Append(trueOrNot ? " = " : " <> "); dbDataType = mapping.DataColTypesByObjAttr[equal.Property]; AppendParameter(queryToAddTo, equal.Value, dbDataType); } } else if (expr is EqualInsensitiveExpression) { EqualInsensitiveExpression iequal = (EqualInsensitiveExpression)expr; col = colPrefix + mapping.AllDataColsByObjAttrs[iequal.Property]; if (iequal.Value == null) { queryToAddTo.Sql.Append(col); queryToAddTo.Sql.Append(trueOrNot ? " IS NULL" : " IS NOT NULL"); } else { string lower = _connDesc.LowerCaseFunction(); queryToAddTo.Sql.Append(lower).Append("("); queryToAddTo.Sql.Append(col).Append(") "); queryToAddTo.Sql.Append(trueOrNot ? "= " : "<> ").Append(lower).Append("("); dbDataType = mapping.DataColTypesByObjAttr[iequal.Property]; AppendParameter(queryToAddTo, iequal.Value, dbDataType); queryToAddTo.Sql.Append(")"); } } else if (expr is GreaterExpression) { GreaterExpression greater = (GreaterExpression)expr; queryToAddTo.Sql.Append(colPrefix); queryToAddTo.Sql.Append(mapping.AllDataColsByObjAttrs[greater.Property]); queryToAddTo.Sql.Append(trueOrNot ? " > " : " <= "); dbDataType = mapping.DataColTypesByObjAttr[greater.Property]; AppendParameter(queryToAddTo, greater.Value, dbDataType); } else if (expr is LesserExpression) { LesserExpression lesser = (LesserExpression)expr; queryToAddTo.Sql.Append(colPrefix); queryToAddTo.Sql.Append(mapping.AllDataColsByObjAttrs[lesser.Property]); queryToAddTo.Sql.Append(trueOrNot ? " < " : " >= "); dbDataType = mapping.DataColTypesByObjAttr[lesser.Property]; AppendParameter(queryToAddTo, lesser.Value, dbDataType); } else if (expr is BitwiseAndExpression) { BitwiseAndExpression bitwise = (BitwiseAndExpression)expr; string colName = colPrefix + mapping.AllDataColsByObjAttrs[bitwise.Property]; SqlClauseWithValue clause = _connDesc.MakeBitwiseAndClause(colName); queryToAddTo.Sql.Append(clause.PartBeforeValue); dbDataType = mapping.DataColTypesByObjAttr[bitwise.Property]; AppendParameter(queryToAddTo, bitwise.Value, dbDataType); queryToAddTo.Sql.Append(clause.PartAfterValue); DbCaches.Clauses.Return(clause); queryToAddTo.Sql.Append(trueOrNot ? " = " : ") <> "); AppendParameter(queryToAddTo, bitwise.Value, dbDataType); } else if (expr is LikeExpression) { LikeExpression like = (LikeExpression)expr; queryToAddTo.Sql.Append(colPrefix); queryToAddTo.Sql.Append(mapping.AllDataColsByObjAttrs[like.Property]); queryToAddTo.Sql.Append(trueOrNot ? " LIKE " : " NOT LIKE "); dbDataType = mapping.DataColTypesByObjAttr[like.Property]; AppendParameter(queryToAddTo, like.Value, dbDataType); } else if (expr is LikeInsensitiveExpression) { LikeInsensitiveExpression ilike = (LikeInsensitiveExpression)expr; col = colPrefix + mapping.AllDataColsByObjAttrs[ilike.Property]; if (_connDesc.HasCaseInsensitiveLikeOperator()) { string iLikeOperator = _connDesc.CaseInsensitiveLikeOperator(); queryToAddTo.Sql.Append(col); queryToAddTo.Sql.Append(trueOrNot ? String.Format(" {0} ", iLikeOperator) : String.Format(" NOT {0} ", iLikeOperator)); dbDataType = mapping.DataColTypesByObjAttr[ilike.Property]; AppendParameter(queryToAddTo, ilike.Value, dbDataType); } else { string lower = _connDesc.LowerCaseFunction(); queryToAddTo.Sql.Append(lower).Append("("); queryToAddTo.Sql.Append(col).Append(") "); queryToAddTo.Sql.Append(trueOrNot ? "LIKE " : "NOT LIKE ").Append(lower).Append("("); dbDataType = mapping.DataColTypesByObjAttr[ilike.Property]; AppendParameter(queryToAddTo, ilike.Value, dbDataType); queryToAddTo.Sql.Append(")"); } } else if (expr is PropertyInListExpression) { PropertyInListExpression inList = (PropertyInListExpression)expr; IEnumerable listVals = inList.Values; queryToAddTo.Sql.Append(colPrefix); queryToAddTo.Sql.Append(mapping.AllDataColsByObjAttrs[inList.Property]); dbDataType = mapping.DataColTypesByObjAttr[inList.Property]; queryToAddTo.Sql.Append(trueOrNot ? " IN (" : " NOT IN ("); bool firstIn = true; foreach (object val in listVals) { if (val == null) { throw new NullReferenceException( "Cannot include a null value in a list of possible values for " + inList.Property + "."); } if (firstIn) { firstIn = false; } else { queryToAddTo.Sql.Append(", "); } AppendParameter(queryToAddTo, val, dbDataType); } if (firstIn) { throw new ArgumentException("Cannot query for " + inList.Property + " values in an empty list."); } queryToAddTo.Sql.Append(")"); } else if (expr is CriteriaExpression) { CriteriaExpression critExp = (CriteriaExpression)expr; queryToAddTo.Sql.Append(trueOrNot ? "(" : " NOT ("); // This is slightly hacky, but basically even though we're now partway through // assembling a SQL statement, we might have an empty nested expression. So rather // than having "AND () AND" which isn't valid, we put "1=1" for empty nested criteria. if ((critExp.NestedCriteria.Expressions != null) && (critExp.NestedCriteria.Expressions.Count > 0)) { ExpressionListToQuery(queryToAddTo, critExp.NestedCriteria.BoolType, critExp.NestedCriteria.Expressions, mapping, colPrefix); } else { queryToAddTo.Sql.Append("1=1"); } queryToAddTo.Sql.Append(")"); } else if (expr is HandWrittenExpression) { if (!trueOrNot) { throw new ArgumentException("You'll have to manually NOT your custom SQL."); } HandWrittenExpression hand = (HandWrittenExpression)expr; // We'll assume it's SQL, hopefully parameterized. queryToAddTo.Sql.Append(hand.Expression); // If there are any parameters, add 'em. if (hand.Parameters != null) { foreach (object aParam in hand.Parameters) { queryToAddTo.Params.Add(aParam); } } } else { throw new NotSupportedException("Expression type '" + expr.GetType() + "' is not supported."); } // Remember to close the parend. queryToAddTo.Sql.Append(")"); return true; }
/// <summary> /// Converts a single Expression to SQL (mapping the columns as appropriate) and appends /// to the given string builder. /// /// The expression's SQL will already be wrapped in parends, so you do not need to add them /// here. /// </summary> /// <param name="queryToAddTo">Query we're adding the expression to.</param> /// <param name="expr">The expression. NOTE: It should NOT be null. This method does not check.</param> /// <param name="mapping">Class mapping for the class we're dealing with.</param> /// <param name="colPrefix">What to prefix column names with, I.E. "Table." for "Table.Column". /// May be null if no prefix is desired. May be something other than /// the table name if the tables are being aliased.</param> /// <param name="booleanOperator">The boolean operator (AND or OR) to insert before /// this expression. Blank ("") if we don't need one.</param> /// <returns>Whether or not this expression modified the sql string. /// Typically true, but may be false for special query types /// that use other parameters for certain types of expressions.</returns> protected override bool ExpressionToQuery(SqlDaQuery queryToAddTo, IExpression expr, ClassMapping mapping, string colPrefix, string booleanOperator) { // All the spatial expressions we support modify the sql. bool retVal = true; bool trueOrNot = expr.TrueOrNot(); if (expr is IntersectsExpression) { queryToAddTo.Sql.Append(booleanOperator); IntersectsExpression intersects = (IntersectsExpression)expr; queryToAddTo.Sql.Append("ST_Intersects(ST_GeomFromEWKT(?),"); queryToAddTo.Params.Add(_ewktWriter.Write(intersects.Shape)); queryToAddTo.Sql.Append(colPrefix).Append(mapping.AllDataColsByObjAttrs[intersects.Property]); queryToAddTo.Sql.Append(")"); } else if (expr is WithinExpression) { queryToAddTo.Sql.Append(booleanOperator); WithinExpression within = (WithinExpression)expr; queryToAddTo.Sql.Append("ST_Contains(ST_GeomFromEWKT(?),"); queryToAddTo.Params.Add(_ewktWriter.Write(within.Shape)); queryToAddTo.Sql.Append(colPrefix).Append(mapping.AllDataColsByObjAttrs[within.Property]); queryToAddTo.Sql.Append(")"); } else if (expr is ContainsExpression) { queryToAddTo.Sql.Append(booleanOperator); ContainsExpression contains = (ContainsExpression)expr; queryToAddTo.Sql.Append("ST_Contains("); queryToAddTo.Sql.Append(colPrefix).Append(mapping.AllDataColsByObjAttrs[contains.Property]); queryToAddTo.Sql.Append(",ST_GeomFromEWKT(?)"); queryToAddTo.Params.Add(_ewktWriter.Write(contains.Shape)); queryToAddTo.Sql.Append(")"); } else if (expr is AbstractDistanceExpression) { queryToAddTo.Sql.Append(booleanOperator); AbstractDistanceExpression dist = (AbstractDistanceExpression)expr; queryToAddTo.Sql.Append("ST_Distance("); queryToAddTo.Sql.Append(colPrefix).Append(mapping.AllDataColsByObjAttrs[dist.Property]); queryToAddTo.Sql.Append(", ST_GeomFromEWKT(?))"); queryToAddTo.Params.Add(EWKTWriter.ToPoint((IPoint)dist.Shape)); if (dist is LesserDistanceExpression) { queryToAddTo.Sql.Append(trueOrNot ? " < ?" : " >= ?"); } else if (expr is GreaterDistanceExpression) { queryToAddTo.Sql.Append(trueOrNot ? " > ?" : " <= ?"); } else { throw new ArgumentException("Distance expression type " + expr.GetType() + " not supported.", "expr"); } queryToAddTo.Params.Add(dist.Distance); } else if (expr is AbstractDistanceSphereExpression) { queryToAddTo.Sql.Append(booleanOperator); AbstractDistanceSphereExpression dist = (AbstractDistanceSphereExpression)expr; if (!(dist.Shape is IPoint)) { throw new ArgumentException("Spherical distance from a non-point is not supported."); } queryToAddTo.Sql.Append("ST_distance_sphere("); queryToAddTo.Sql.Append(colPrefix).Append(mapping.AllDataColsByObjAttrs[dist.Property]); queryToAddTo.Sql.Append(", ST_GeomFromEWKT(?))"); queryToAddTo.Params.Add(EWKTWriter.ToPoint((IPoint)dist.Shape)); if (dist is LesserDistanceSphereExpression) { queryToAddTo.Sql.Append(trueOrNot ? " < ?" : " >= ?"); } else if (expr is GreaterDistanceSphereExpression) { queryToAddTo.Sql.Append(trueOrNot ? " > ?" : " <= ?"); } else { throw new ArgumentException("Distance expression type " + expr.GetType() + " not supported.", "expr"); } queryToAddTo.Params.Add(dist.Distance); } else { // Fall back to the stuff supported by the base class. retVal = base.ExpressionToQuery(queryToAddTo, expr, mapping, colPrefix, booleanOperator); } return retVal; }
/// <summary> /// Converts a single Expression to SQL (mapping the columns as appropriate) and appends /// to the given string builder. /// /// The expression's SQL will already be wrapped in parens, so you do not need to add them /// here. /// </summary> /// <param name="queryToAddTo">Query we're adding the expression to.</param> /// <param name="expr">The expression. NOTE: It should NOT be null. This method does not check.</param> /// <param name="mapping">Class mapping for the class we're dealing with.</param> /// <param name="colPrefix">What to prefix column names with, I.E. "Table." for "Table.Column". /// May be null if no prefix is desired. May be something other than /// the table name if the tables are being aliased.</param> /// <param name="booleanOperator">The boolean operator (AND or OR) to insert before /// this expression. Blank ("") if we don't need one.</param> /// <returns>Whether or not this expression modified the sql string. /// Typically true, but may be false for special query types /// that use other parameters for certain types of expressions.</returns> protected override bool ExpressionToQuery(SqlDaQuery queryToAddTo, IExpression expr, ClassMapping mapping, string colPrefix, string booleanOperator) { // All the spatial expressions we support modify the sql. bool retVal = true; bool trueOrNot = expr.TrueOrNot(); if (expr is IntersectsExpression) { queryToAddTo.Sql.Append(booleanOperator); IntersectsExpression intersects = (IntersectsExpression)expr; const string intersectsFormatString = "SDE.ST_Intersects({0}, {1}) = 1"; // It is important that the input geometry is the second parameter, otherwise the // spatial index does not get used. queryToAddTo.Sql.Append(string.Format(intersectsFormatString, colPrefix + mapping.AllDataColsByObjAttrs[intersects.Property], // Shape column name string.Format("SDE.ST_Geometry(?,{0})", intersects.Shape.SRID))); // geom param converted from WKT queryToAddTo.Params.Add(_wktWriter.Write(intersects.Shape)); } else if (expr is WithinExpression) { queryToAddTo.Sql.Append(booleanOperator); WithinExpression within = (WithinExpression)expr; const string withinFormatString = "SDE.ST_Within({0}, {1}) = 1"; // It is important that the input geometry is the second parameter, otherwise the // spatial index does not get used. queryToAddTo.Sql.Append(string.Format(withinFormatString, colPrefix + mapping.AllDataColsByObjAttrs[within.Property], // Shape column name string.Format("SDE.ST_Geometry(?,{0})", within.Shape.SRID))); // geom param converted from WKT queryToAddTo.Params.Add(_wktWriter.Write(within.Shape)); } else if (expr is ContainsExpression) { queryToAddTo.Sql.Append(booleanOperator); ContainsExpression contains = (ContainsExpression)expr; const string containsFormatString = "SDE.ST_Contains({0}, {1}) = 1"; // It is important that the input geometry is the second parameter, otherwise the // spatial index does not get used. queryToAddTo.Sql.Append(string.Format(containsFormatString, colPrefix + mapping.AllDataColsByObjAttrs[contains.Property], // Shape column name string.Format("SDE.ST_Geometry(?,{0})", contains.Shape.SRID))); // geom param converted from WKT queryToAddTo.Params.Add(_wktWriter.Write(contains.Shape)); } else if (expr is AbstractDistanceExpression) { queryToAddTo.Sql.Append(booleanOperator); AbstractDistanceExpression dist = (AbstractDistanceExpression)expr; queryToAddTo.Sql.Append("SDE.ST_Distance("); queryToAddTo.Sql.Append(colPrefix).Append(mapping.AllDataColsByObjAttrs[dist.Property]); queryToAddTo.Sql.Append(string.Format("SDE.ST_Contains(SDE.ST_Geometry(?,{0}),", dist.Shape.SRID)); queryToAddTo.Params.Add(WKTWriter.ToPoint(((IPoint)dist.Shape).Coordinate)); if (dist is LesserDistanceExpression) { queryToAddTo.Sql.Append(trueOrNot ? " < ?" : " >= ?"); } else if (expr is GreaterDistanceExpression) { queryToAddTo.Sql.Append(trueOrNot ? " > ?" : " <= ?"); } else { throw new ArgumentException("Distance expression type " + expr.GetType() + " not supported.", "expr"); } queryToAddTo.Params.Add(dist.Distance); } else if (expr is AbstractDistanceSphereExpression) { throw new ArgumentException("Distance expression type " + expr.GetType() + " not supported.", "expr"); } else { // The expression type does not required special handling by this subclass retVal = base.ExpressionToQuery(queryToAddTo, expr, mapping, colPrefix, booleanOperator); } return retVal; }