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