/// <summary>
/// Create a sub-select statement for a query condition where the operator specifies that the specified multivalued attribute value must not exist
/// </summary>
/// <param name="builder">The builder for this query</param>
/// <param name="parameterNames">The parameters to evaluate against</param>
/// <returns>An SQL SELECT statement</returns>
private string CreateSubSelectStatementForMVNotEquals(DBQueryBuilder builder, IList <string> parameterNames)
{
string paramNameAttribute = builder.AddParameter(this.SearchAttribute.Name);
WhereClause subClause = new WhereClause(WhereClauseRelationship.Or);
WhereClause existWhere = new WhereClause(WhereClauseRelationship.And);
AcmaSchemaAttribute objectIdAttribute = ActiveConfig.DB.GetAttribute("objectId");
existWhere.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("objectId", this.SearchAttribute.DBTable), SqlExpression.Field(objectIdAttribute.Name, objectIdAttribute.DBTable), Reeb.SqlOM.CompareOperator.Equal));
existWhere.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("attributeName", this.SearchAttribute.DBTable), SqlExpression.Parameter(paramNameAttribute), Reeb.SqlOM.CompareOperator.Equal));
foreach (string paramNameValue in parameterNames)
{
subClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, this.SearchAttribute.DBTable), SqlExpression.Parameter(paramNameValue), Reeb.SqlOM.CompareOperator.Equal));
}
existWhere.SubClauses.Add(subClause);
SelectQuery subQuery = new SelectQuery();
subQuery.Columns.Add(new SelectColumn("objectId"));
subQuery.Top = 1;
subQuery.FromClause.BaseTable = this.SearchAttribute.DBTable;
subQuery.WherePhrase.SubClauses.Add(existWhere);
Reeb.SqlOM.Render.SqlServerRenderer render = new Reeb.SqlOM.Render.SqlServerRenderer();
string sql = render.RenderSelect(subQuery);
return(sql);
}
/// <summary>
/// Creates parameters for each of the values used in the query
/// </summary>
/// <param name="builder">The DBQueryBuilder used by this query</param>
/// <param name="values">The values to search for</param>
/// <returns>A list of parameter names that were added to the query</returns>
protected IList <string> CreateParameters(DBQueryBuilder builder, IList <object> values)
{
List <string> parameterNames = new List <string>();
if (!(this.Operator == ValueOperator.IsPresent || this.Operator == ValueOperator.NotPresent))
{
foreach (object value in values)
{
object parameterValue = value;
if (this.Operator == ValueOperator.Contains && parameterValue is string)
{
parameterValue = "%" + (string)parameterValue + "%";
}
if (this.Operator == ValueOperator.StartsWith && parameterValue is string)
{
parameterValue = (string)parameterValue + "%";
}
if (this.Operator == ValueOperator.EndsWith && parameterValue is string)
{
parameterValue = "%" + (string)parameterValue;
}
parameterNames.Add(builder.AddParameter(parameterValue));
}
}
return(parameterNames);
}
/// <summary>
/// Creates a WHERE clause for a multivalued attribute
/// </summary>
/// <param name="builder">The builder for this query</param>
/// <param name="parameterNames">The parameters to evaluate against</param>
/// <returns>The WhereClause object for this query</returns>
private WhereClause CreateWhereClauseforAVPTarget(DBQueryBuilder builder, IList <string> parameterNames)
{
FromTerm fromTable;
fromTable = builder.GetNextAVPTableReference(this.SearchAttribute.TableName);
WhereClause clause = new WhereClause(WhereClauseRelationship.And);
if (this.Operator != ValueOperator.NotPresent)
{
string paramName = builder.AddParameter(this.SearchAttribute.Name);
WhereTerm attributeNameTerm = WhereTerm.CreateCompare(SqlExpression.Field("attributeName", fromTable), SqlExpression.Parameter(paramName), CompareOperator.Equal);
clause.Terms.Add(attributeNameTerm);
}
if (this.Operator == ValueOperator.IsPresent)
{
clause.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable)));
}
else if (this.Operator == ValueOperator.NotPresent)
{
string sql = this.CreateSubSelectStatementForMVNotExists(builder);
clause.Terms.Add(WhereTerm.CreateNotExists(sql));
}
else
{
WhereClause subClause = new WhereClause(WhereClauseRelationship.Or);
switch (this.Operator)
{
case ValueOperator.And:
case ValueOperator.Contains:
case ValueOperator.GreaterThan:
case ValueOperator.GreaterThanOrEq:
case ValueOperator.LessThan:
case ValueOperator.StartsWith:
case ValueOperator.EndsWith:
case ValueOperator.LessThanOrEq:
foreach (string parameterName in parameterNames)
{
subClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterName), this.CompareOperator));
}
break;
case ValueOperator.Equals:
subClause.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString()));
break;
case ValueOperator.NotEquals:
string sql = this.CreateSubSelectStatementForMVNotEquals(builder, parameterNames);
clause.Terms.Add(WhereTerm.CreateNotExists(sql));
break;
default:
throw new InvalidOperationException();
}
clause.SubClauses.Add(subClause);
}
return(clause);
}
