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