public override void Visit(Function function) { bool firstIsType = ((int)_ExprLevel.Peek() == EXPR); _ExprLevel.Push(_ExprLevel.Peek()); ISQLExpression expression = null; EntityMapping entityMap = null; if (!firstIsType) entityMap = (EntityMapping)entityMappingContext.Peek(); SelectStatement subQuery = new SelectStatement(entityMap); int lastIndex = function.Path.Identifiers.Count - 1; if (!firstIsType) { for (int index = 0; index < lastIndex; index++) { string refName = function.Path.Identifiers[index].Value; ReferenceMapping refeMap = entityMap.References[refName]; // If refeMap == null, cannot find the reference in the child // Then try to get it from parent types if (refeMap == null) { Evaluant.Uss.Models.Entity current = Model.GetEntity(entityMap.Type); if (refeMap == null) { while (Model.GetParent(current) != null) { current = Model.GetParent(current); refeMap = _Mapping.Entities[current.Type].References[refName]; if (refeMap != null) { entityMap = refeMap.EntityParent; break; } } } } if (refeMap == null) throw new SqlMapperException(string.Concat("Cannot find the reference", refName, " from the entity ", entityMap.Type)); //entityMap = _Mapping.Entities[refeMap.EntityChild]; } } Table currentTable = null; switch (function.Type) { case FunctionEnum.Average: if (firstIsType) { subQuery = (SelectStatement)TransformToSql(function.Path, new string[0], new string[0], true); ; subQuery.TableAlias = CreateUniqueTableAlias(); if (subQuery is UnionStatement) { SelectStatement select = new SelectStatement(null); select.FromClause.Add(subQuery as Table); subQuery = select; } tableContext.Push(subQuery as Table); _IsFirstAttribute = true; expression = ProcessAttributes((EntityMapping)entityMappingContext.Peek(), function.Path.Identifiers[lastIndex].Value, false, true); subQuery.SelectList = new ExpressionCollection(); subQuery.SelectList.Add(new AggregateFunction(null, AggregateFunctionEnum.Avg, expression)); sqlExpressionContext.Push(subQuery); } else { currentTable = new TableSource(entityMap, ((TableSource)tableContext.Peek()).TableName, CreateUniqueTableAlias()); tableContext.Push(currentTable); queryContext.Push(subQuery); ConvertToSQL(function.Path, false, true, true); expression = ProcessAttributes(entityMap, function.Path.Identifiers[lastIndex].Value, false, true); subQuery = (SelectStatement)queryContext.Peek(); if (subQuery.GetType() == typeof(UnionStatement)) { SelectStatement sel = new SelectStatement(null); subQuery.TableAlias = CreateUniqueTableAlias(); sel.FromClause.Add(subQuery); sel.SelectList = new ExpressionCollection(); sel.SelectList.Add(new AggregateFunction(entityMap, AggregateFunctionEnum.Avg, expression)); sqlExpressionContext.Push(sel); } else { subQuery.SelectList = new ExpressionCollection(); subQuery.SelectList.Add(new AggregateFunction(entityMap, AggregateFunctionEnum.Avg, expression)); sqlExpressionContext.Push(subQuery); } // link tables for inline views ((SelectStatement)sqlExpressionContext.Peek()).WhereClause.SearchCondition.Add(new BinaryLogicExpression(new Column(null, subQuery.TableAlias, "ParentId"), BinaryLogicOperator.Equals, linkTableContext.Pop() as Column)); tableContext.Pop(); queryContext.Pop(); } break; case FunctionEnum.Count: if (firstIsType) { subQuery = (SelectStatement)TransformToSql(function.Path, new string[0], null, false); subQuery.TableAlias = CreateUniqueTableAlias(); if (subQuery is UnionStatement) { SelectStatement select = new SelectStatement(null); select.FromClause.Add(subQuery as Table); subQuery = select; } subQuery.SelectList = new ExpressionCollection(); subQuery.SelectList.Add(new AggregateFunction(null, AggregateFunctionEnum.Count, new Constant("*", DbType.StringFixedLength))); sqlExpressionContext.Push(subQuery); } else { currentTable = new TableSource(entityMap, entityMap.Table, CreateUniqueTableAlias()); tableContext.Push(currentTable); queryContext.Push(subQuery); ConvertToSQL(function.Path, false, true, false); currentTable = (Table)tableContext.Peek(); subQuery = (SelectStatement)queryContext.Peek(); if (subQuery.GetType() == typeof(UnionStatement)) { SelectStatement sel = new SelectStatement(null); subQuery.TableAlias = CreateUniqueTableAlias(); sel.FromClause.Add(subQuery); sel.SelectList = new ExpressionCollection(); sel.SelectList.Add(new AggregateFunction(entityMap, AggregateFunctionEnum.Count, new Constant("*", DbType.StringFixedLength))); sqlExpressionContext.Push(sel); } else { subQuery.SelectList = new ExpressionCollection(); subQuery.SelectList.Add(new AggregateFunction(entityMap, AggregateFunctionEnum.Count, new Constant("*", DbType.StringFixedLength))); sqlExpressionContext.Push(subQuery); } // link tables for inline views ((SelectStatement)sqlExpressionContext.Peek()).WhereClause.SearchCondition.Add(new BinaryLogicExpression(new Column(null, subQuery.TableAlias, "ParentId"), BinaryLogicOperator.Equals, linkTableContext.Pop() as Column)); tableContext.Pop(); queryContext.Pop(); } break; case FunctionEnum.Exists: currentTable = new TableSource(entityMap, entityMap.Table, CreateUniqueTableAlias()); tableContext.Push(currentTable); queryContext.Push(subQuery); ConvertToSQL(function.Path, false, true, false); subQuery = (SelectStatement)queryContext.Peek(); // the exists function just need ids fields // do not list attribute field to avoid problems with data type fields (text, ntext, image for sql server) // Remove none needed selected item, according to the path // no references: keep Type, Id (2 fields) // references: keep Type, IdParent, Id (3 fields) int keepFieldNb = (function.Path.Identifiers.Count == 1) ? 2 : 3; if (subQuery is UnionStatement) { foreach (SelectStatement select in ((UnionStatement)subQuery).SelectExpressions) { for (int i = select.SelectList.Count - 1; i >= keepFieldNb; i--) select.SelectList.RemoveAt(i); // link tables for inline views Column cLeft = linkTableContext.Pop() as Column; Column cRight = linkTableContext.Pop() as Column; if (cLeft is MultipledKey) { for (int index = 0; index < ((MultipledKey)cLeft).Collection.Count; index++) { select.WhereClause.SearchCondition.Add(new BinaryLogicExpression(((MultipledKey)cLeft).Collection[index], BinaryLogicOperator.Equals, ((MultipledKey)cRight).Collection[index])); } } else { select.WhereClause.SearchCondition.Add(new BinaryLogicExpression(cLeft, BinaryLogicOperator.Equals, cRight)); } } } else { for (int i = subQuery.SelectList.Count - 1; i >= keepFieldNb; i--) subQuery.SelectList.RemoveAt(i); // link tables for inline views subQuery.WhereClause.SearchCondition.Add(new BinaryLogicExpression(linkTableContext.Pop() as Column, BinaryLogicOperator.Equals, linkTableContext.Pop() as Column)); } subQuery.SelectedAllColumns = true; expression = new ExistsPredicate(subQuery); sqlExpressionContext.Push(expression); tableContext.Pop(); queryContext.Pop(); break; case FunctionEnum.IsNull: _IsFirstAttribute = true; expression = new IsNullPredicate(ProcessAttributes(entityMap, function.Path.Identifiers[lastIndex].Value, false)); sqlExpressionContext.Push(expression); break; case FunctionEnum.Max: if (firstIsType) { subQuery = (SelectStatement)TransformToSql(function.Path, new string[0], new string[0], true); subQuery.TableAlias = CreateUniqueTableAlias(); if (subQuery is UnionStatement) { SelectStatement select = new SelectStatement(null); select.FromClause.Add(subQuery as Table); subQuery = select; } tableContext.Push(subQuery as Table); _IsFirstAttribute = true; expression = ProcessAttributes((EntityMapping)entityMappingContext.Peek(), function.Path.Identifiers[lastIndex].Value, false, true); subQuery.SelectList = new ExpressionCollection(); subQuery.SelectList.Add(new AggregateFunction(null, AggregateFunctionEnum.Max, expression)); sqlExpressionContext.Push(subQuery); } else { currentTable = new TableSource(entityMap, ((TableSource)tableContext.Peek()).TableName, CreateUniqueTableAlias()); tableContext.Push(currentTable); queryContext.Push(subQuery); entityMappingContext.Push(entityMap); ConvertToSQL(function.Path, false, true, true); EntityMapping lastEM = (EntityMapping)entityMappingContext.Peek(); //entityModelContext.Pop(); entityModelContext.Peek(); expression = ProcessAttributes(lastEM, function.Path.Identifiers[lastIndex].Value, false, true); subQuery = (SelectStatement)queryContext.Peek(); if (subQuery.GetType() == typeof(UnionStatement)) { SelectStatement sel = new SelectStatement(null); subQuery.TableAlias = CreateUniqueTableAlias(); sel.FromClause.Add(subQuery); sel.SelectList = new ExpressionCollection(); sel.SelectList.Add(new AggregateFunction(lastEM, AggregateFunctionEnum.Max, expression)); sqlExpressionContext.Push(sel); } else { subQuery.SelectList = new ExpressionCollection(); subQuery.SelectList.Add(new AggregateFunction(lastEM, AggregateFunctionEnum.Max, expression)); sqlExpressionContext.Push(subQuery); } // link tables for inline views if (linkTableContext.Count > 0) ((SelectStatement)sqlExpressionContext.Peek()).WhereClause.SearchCondition.Add( new BinaryLogicExpression(new Column(null, subQuery.TableAlias, "ParentId"), BinaryLogicOperator.Equals, linkTableContext.Pop() as Column)); tableContext.Pop(); queryContext.Pop(); entityMappingContext.Pop(); } break; case FunctionEnum.Min: if (firstIsType) { subQuery = (SelectStatement)TransformToSql(function.Path, new string[0], new string[0], true); ; subQuery.TableAlias = CreateUniqueTableAlias(); if (subQuery is UnionStatement) { SelectStatement select = new SelectStatement(null); select.FromClause.Add(subQuery as Table); subQuery = select; } tableContext.Push(subQuery as Table); _IsFirstAttribute = true; expression = ProcessAttributes((EntityMapping)entityMappingContext.Peek(), function.Path.Identifiers[lastIndex].Value, false, true); subQuery.SelectList = new ExpressionCollection(); subQuery.SelectList.Add(new AggregateFunction(null, AggregateFunctionEnum.Min, expression)); sqlExpressionContext.Push(subQuery); } else { currentTable = new TableSource(entityMap, ((TableSource)tableContext.Peek()).TableName, CreateUniqueTableAlias()); tableContext.Push(currentTable); queryContext.Push(subQuery); ConvertToSQL(function.Path, false, true, true); expression = ProcessAttributes(entityMap, function.Path.Identifiers[lastIndex].Value, false, true); subQuery = (SelectStatement)queryContext.Peek(); if (subQuery.GetType() == typeof(UnionStatement)) { SelectStatement sel = new SelectStatement(null); subQuery.TableAlias = CreateUniqueTableAlias(); sel.FromClause.Add(subQuery); sel.SelectList = new ExpressionCollection(); sel.SelectList.Add(new AggregateFunction(entityMap, AggregateFunctionEnum.Min, expression)); sqlExpressionContext.Push(sel); } else { subQuery.SelectList = new ExpressionCollection(); subQuery.SelectList.Add(new AggregateFunction(entityMap, AggregateFunctionEnum.Min, expression)); sqlExpressionContext.Push(subQuery); } // link tables for inline views ((SelectStatement)sqlExpressionContext.Peek()).WhereClause.SearchCondition.Add(new BinaryLogicExpression(new Column(null, subQuery.TableAlias, "ParentId"), BinaryLogicOperator.Equals, linkTableContext.Pop() as Column)); tableContext.Pop(); queryContext.Pop(); } break; case FunctionEnum.Sum: if (firstIsType) { subQuery = (SelectStatement)TransformToSql(function.Path, new string[0], new string[0], true); subQuery.TableAlias = CreateUniqueTableAlias(); if (subQuery is UnionStatement) { SelectStatement select = new SelectStatement(null); select.FromClause.Add(subQuery as Table); subQuery = select; } tableContext.Push(subQuery as Table); _IsFirstAttribute = true; expression = ProcessAttributes((EntityMapping)entityMappingContext.Peek(), function.Path.Identifiers[lastIndex].Value, false, true); subQuery.SelectList = new ExpressionCollection(); subQuery.SelectList.Add(new AggregateFunction(null, AggregateFunctionEnum.Sum, expression)); sqlExpressionContext.Push(subQuery); } else { currentTable = new TableSource(entityMap, ((TableSource)tableContext.Peek()).TableName, CreateUniqueTableAlias()); tableContext.Push(currentTable); queryContext.Push(subQuery); ConvertToSQL(function.Path, false, true, true); expression = ProcessAttributes(entityMap, function.Path.Identifiers[lastIndex].Value, false, true); subQuery = (SelectStatement)queryContext.Peek(); if (subQuery.GetType() == typeof(UnionStatement)) { SelectStatement sel = new SelectStatement(null); subQuery.TableAlias = CreateUniqueTableAlias(); sel.FromClause.Add(subQuery); sel.SelectList = new ExpressionCollection(); sel.SelectList.Add(new AggregateFunction(entityMap, AggregateFunctionEnum.Sum, expression)); sqlExpressionContext.Push(sel); } else { subQuery.SelectList = new ExpressionCollection(); subQuery.SelectList.Add(new AggregateFunction(entityMap, AggregateFunctionEnum.Sum, expression)); sqlExpressionContext.Push(subQuery); } // link tables for inline views ((SelectStatement)sqlExpressionContext.Peek()).WhereClause.SearchCondition.Add(new BinaryLogicExpression(new Column(null, subQuery.TableAlias, "ParentId"), BinaryLogicOperator.Equals, linkTableContext.Pop() as Column)); tableContext.Pop(); queryContext.Pop(); } break; } _ExprLevel.Pop(); }
public virtual void Visit(IsNullPredicate predicate) { if (IsSelectStatement(predicate.Expression)) _Query.Append(OPENBRACE); predicate.Expression.Accept(this); if (IsSelectStatement(predicate.Expression)) _Query.Append(CLOSEBRACE); _Query.Append(IS).Append(NULL); }