/// <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 the WHERE clause for this query
/// </summary>
/// <param name="builder">The builder used by this query</param>
/// <param name="hologram">The hologram used as the source of query values</param>
/// <returns>A WhereClause object containing the terms of the query</returns>
public WhereClause CreateWhereClause(DBQueryBuilder builder, MAObjectHologram hologram)
{
if (this.DBQueries.Count == 0)
{
throw new InvalidOperationException("The query group cannot generate a query when no child queries are specified");
}
WhereClause parentClause;
WhereClause querySubClause;
if (this.Operator == GroupOperator.None)
{
// Workaround to stop SqlOm from dropping the operator when there is only a single term in the where clause
parentClause = new WhereClause(WhereClauseRelationship.Not);
parentClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(1), SqlExpression.Number(1), CompareOperator.Equal));
querySubClause = new WhereClause(WhereClauseRelationship.Or);
parentClause.SubClauses.Add(querySubClause);
}
else
{
parentClause = new WhereClause(this.GetWhereClauseRelationship());
querySubClause = parentClause;
}
IList <DBQueryObject> queryObjects = this.ProcessChildDBQueryObjects(querySubClause, builder, hologram);
if (queryObjects.Count == 0)
{
throw new QueryValueNullException("The query group contained no valid queries to execute");
}
else
{
return(parentClause);
}
}
/// <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 this query
/// </summary>
/// <param name="builder">The builder for this query</param>
/// <param name="parameterNames">The names of the parameters added to the query</param>
/// <returns>The WhereClause object for this query</returns>
protected WhereClause CreateWhereClause(DBQueryBuilder builder, IList <string> parameterNames)
{
if (this.SearchAttribute.IsInAVPTable)
{
return(this.CreateWhereClauseforAVPTarget(builder, parameterNames));
}
else
{
return(this.CreateWhereClauseforSVTarget(parameterNames));
}
}
/// <summary>
/// Gets a WHERE clause from a child query
/// </summary>
/// <param name="builder">The builder for this query</param>
/// <param name="queryObject">The query object to get the WHERE clause from</param>
/// <returns>A WhereClause object containing the terms of the child query</returns>
private WhereClause GetWhereClauseFromDBQueryObject(DBQueryBuilder builder, DBQueryObject queryObject)
{
if (queryObject is DBQueryByValue)
{
return(((DBQueryByValue)queryObject).CreateWhereClause(builder));
}
else if (queryObject is DBQueryGroup)
{
return(((DBQueryGroup)queryObject).CreateWhereClause(builder));
}
else
{
throw new InvalidOperationException("The DBQueryObject type is unknown");
}
}
/// <summary>
/// Creates the WHERE clause for this query
/// </summary>
/// <param name="builder">The builder for this query</param>
/// <returns>A WhereClause object representing the terms of this query</returns>
public WhereClause CreateWhereClause(DBQueryBuilder builder)
{
if (this.Operator == ValueOperator.IsPresent || this.Operator == ValueOperator.NotPresent)
{
return(this.CreateWhereClause(builder, new List <string>()));
}
else
{
if (this.ValueDeclarations == null || this.ValueDeclarations.Count == 0 || this.ValueDeclarations.Any(t => t == null))
{
throw new QueryValueNullException();
}
IList <object> values = this.GetValuesFromDelarations();
if (values == null || values.Count == 0)
{
throw new QueryValueNullException();
}
IList <string> parameterNames = this.CreateParameters(builder, values);
return(this.CreateWhereClause(builder, parameterNames));
}
}
/// <summary>
/// Process the child query objects in this group
/// </summary>
/// <param name="parentClause">The parent clause for this query</param>
/// <param name="builder">The builder for this query</param>
/// <returns>A list of successfully processed query objects</returns>
private IList <DBQueryObject> ProcessChildDBQueryObjects(WhereClause parentClause, DBQueryBuilder builder)
{
IList <DBQueryObject> objectsToProcess = this.DBQueries;
IList <DBQueryObject> processedObjects = new List <DBQueryObject>();
foreach (DBQueryObject obj in objectsToProcess)
{
WhereClause clause = null;
try
{
clause = this.GetWhereClauseFromDBQueryObject(builder, obj);
}
catch (QueryValueNullException)
{
if (this.Operator != GroupOperator.Any)
{
throw;
}
}
if (clause == null)
{
continue;
}
processedObjects.Add(obj);
parentClause.SubClauses.Add(clause);
}
return(processedObjects);
}
/// <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);
}