/// <summary>
/// Since it is implementation-dependent whether to use the sql parameters collection
/// or not, this method should be implemented in each implementation.
/// </summary>
/// <param name="queryToAddTo">Query to add the parameter to.</param>
/// <param name="columnType">Type of data actually stored in the DB column. For example,
/// Enums may be stored as strings. May be null if no type cast
/// is necessary.</param>
/// <param name="value">Actual value that we need to append to our SQL.</param>
public override void AppendParameter(SqlDaQuery queryToAddTo, object value,
Type columnType)
{
queryToAddTo.Sql.Append("?");
if (columnType != null)
{
value = CoerceType(columnType, value);
}
queryToAddTo.Params.Add(value);
}
/// <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.
///
/// 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 the list of expressions from this criteria into SQL, and appends to the
/// given string builder.
/// </summary>
/// <param name="queryToAddTo">Query we're adding the expression to.</param>
/// <param name="boolType">Whether to AND or OR the expressions together.</param>
/// <param name="expressions">The expressions to add to the query.</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>
protected void ExpressionListToQuery(SqlDaQuery queryToAddTo,
BooleanOperator boolType,
IEnumerable<IExpression> expressions,
ClassMapping mapping, string colPrefix)
{
// starts out false for the first one.
bool needsBooleanOperator = false;
string boolText = BoolTypeToString(boolType);
foreach (IExpression expr in expressions)
{
try
{
if (expr == null)
{
throw new NullReferenceException("Can't convert a null expression to SQL.");
}
// After the first guy writes something, we need an operator.
if (ExpressionToQuery(queryToAddTo, expr, mapping, colPrefix,
needsBooleanOperator ? boolText : ""))
{
needsBooleanOperator = true;
}
}
catch (Exception e)
{
throw new UnableToConstructSqlException("Unable to add expression to query: " + expr, _connDesc, e);
}
}
}
/// <summary>
/// Takes a DaoCriteria, converts it to an " ORDER BY ..." chunk of SQL.
/// The SQL will begin with a space if non-empty.
/// </summary>
public virtual void OrdersToQuery(SqlDaQuery queryToAddTo, DaoCriteria crit,
ClassMapping mapping)
{
if (crit != null)
{
if (crit.Orders.Count > 0)
{
queryToAddTo.Sql.Append(" ORDER BY ");
OrderListToSql(queryToAddTo.Sql, crit, mapping);
}
}
}
/// <summary>
/// Adds the group by fields to the "column" list ("column"
/// since they may not all be columns) in the beginning of the
/// select (I.E. "SELECT COUNT(*), Field1, Field2, etc).
/// </summary>
/// <param name="query">Query to append to.</param>
/// <param name="groupExpressions">Group by expressions.</param>
/// <param name="mapping">Class mapping for the class we're dealing with.</param>
public virtual void GroupBysToStartOfQuery(SqlDaQuery query,
ICollection<AbstractGroupExpression> groupExpressions, ClassMapping mapping)
{
int exprNum = 0;
foreach (AbstractGroupExpression expression in groupExpressions)
{
query.Sql.Append(", ");
if (expression is MemberGroupExpression)
{
query.Sql.Append(
mapping.AllDataColsByObjAttrs[((MemberGroupExpression)expression).MemberName]);
query.Sql.Append(_connDesc.NeedAsForColumnAliases() ? " AS " : " ")
.Append(_connDesc.ColumnAliasPrefix())
.Append("gb_").Append(exprNum).Append(_connDesc.ColumnAliasSuffix());
}
else
{
throw new ArgumentException(
"Group expression '" + expression + "' is an unsupported type.",
"groupExpressions");
}
exprNum++;
}
}
/// <summary>
/// Adds the group by fields to the end of the query, including
/// the keyword "GROUP BY" if necessary.
/// </summary>
/// <param name="query">Query to append to.</param>
/// <param name="groupExpressions">Group by expressions.</param>
/// <param name="mapping">Class mapping for the class we're dealing with.</param>
public virtual void GroupBysToEndOfQuery(SqlDaQuery query,
ICollection<AbstractGroupExpression> groupExpressions, ClassMapping mapping)
{
if (groupExpressions.Count > 0)
{
query.Sql.Append(" GROUP BY ");
}
bool first = true;
foreach (AbstractGroupExpression expression in groupExpressions)
{
if (first)
{
first = false;
}
else
{
query.Sql.Append(", ");
}
if (expression is MemberGroupExpression)
{
query.Sql.Append(
mapping.AllDataColsByObjAttrs[((MemberGroupExpression) expression).MemberName]);
}
else
{
throw new ArgumentException(
"Group expression '" + expression + "' is an unsupported type.",
"groupExpressions");
}
}
}
/// <summary>
/// Takes a DaoCriteria, converts it to a " WHERE ..." chunk of SQL.
/// The SQL will begin with a space if non-empty.
/// </summary>
/// <param name="queryToAddTo">Query we're adding the expression to.</param>
/// <param name="crit">Serializable critera to get the expressions from.</param>
/// <param name="mapping">Class mapping for the class we're dealing with.</param>
public virtual void ExpressionsToQuery(SqlDaQuery queryToAddTo, DaoCriteria crit,
ClassMapping mapping)
{
if (crit != null)
{
if (crit.Expressions.Count > 0)
{
string colPrefix = _fullyQualifyColumnNames ? mapping.Table + "." : null;
queryToAddTo.Sql.Append(" WHERE ");
ExpressionListToQuery(queryToAddTo, crit.BoolType, crit.Expressions,
mapping, colPrefix);
}
}
}
/// <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;
}
