コード例 #1
0
ファイル: SsdlToFb.cs プロジェクト: hfoffani/NETProvider
        static GenerateColumnResult GenerateColumn(EdmProperty property)
        {
            var column         = new StringBuilder();
            var columnComments = new Dictionary <string, string>();

            column.Append(SqlGenerator.QuoteIdentifier(property.Name));
            column.Append(" ");
            column.Append(SqlGenerator.GetSqlPrimitiveType(property.TypeUsage));
            switch (MetadataHelpers.GetEdmType <PrimitiveType>(property.TypeUsage).PrimitiveTypeKind)
            {
            case PrimitiveTypeKind.Boolean:
                column.AppendFormat(" CHECK ({0} IN (1,0))", SqlGenerator.QuoteIdentifier(property.Name));
                columnComments.Add(property.Name, "#BOOL#");
                break;

            case PrimitiveTypeKind.Guid:
                columnComments.Add(property.Name, "#GUID#");
                break;
            }
            if (!property.Nullable)
            {
                column.Append(" NOT NULL");
            }
            return(new GenerateColumnResult()
            {
                ColumnName = column.ToString(), ColumnComments = columnComments
            });
        }
コード例 #2
0
        /// <summary>
        /// Translate a NewInstance(Element(X)) expression into
        ///   "select top(1) * from X"
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        private ISqlFragment VisitCollectionConstructor(DbNewInstanceExpression e)
        {
            Debug.Assert(e.Arguments.Count <= 1);

            if (e.Arguments.Count == 1 && e.Arguments[0].ExpressionKind == DbExpressionKind.Element)
            {
                DbElementExpression elementExpr = e.Arguments[0] as DbElementExpression;
                SqlSelectStatement  result      = VisitExpressionEnsureSqlStatement(elementExpr.Argument);

                if (!IsCompatible(result, DbExpressionKind.Element))
                {
                    Symbol    fromSymbol;
                    TypeUsage inputType = MetadataHelpers.GetElementTypeUsage(elementExpr.Argument.ResultType);

                    result = CreateNewSelectStatement(result, "element", inputType, out fromSymbol);
                    AddFromSymbol(result, "element", fromSymbol, false);
                }
                result.Top.SetTopCount(1);
                return(result);
            }


            // Otherwise simply build this out as a union-all ladder
            CollectionType collectionType = MetadataHelpers.GetEdmType <CollectionType>(e.ResultType);

            Debug.Assert(collectionType != null);
            bool isScalarElement = MetadataHelpers.IsPrimitiveType(collectionType.TypeUsage);

            SqlBuilder resultSql = new SqlBuilder();
            string     separator = "";

            // handle empty table
            if (e.Arguments.Count == 0)
            {
                Debug.Assert(isScalarElement);
                resultSql.Append(" select cast(null as ");
                resultSql.Append(MetadataHelpers.GetSqlPrimitiveType(collectionType.TypeUsage));
                resultSql.Append(") as x from (select 1) as y where 1=0");
            }

            foreach (DbExpression arg in e.Arguments)
            {
                resultSql.Append(separator);
                resultSql.Append(" select ");
                resultSql.Append(arg.Accept(this));
                // For scalar elements, no alias is appended yet. Add this.
                if (isScalarElement)
                {
                    resultSql.Append(" as x ");
                }
                separator = " union all ";
            }

            return(resultSql);
        }
コード例 #3
0
        /// <summary>
        /// This is called from <see cref="GenerateSql(DbQueryCommandTree)"/> and nodes which require a
        /// select statement as an argument e.g. <see cref="Visit(DbIsEmptyExpression)"/>,
        /// <see cref="Visit(DbUnionAllExpression)"/>.
        ///
        /// SqlGenerator needs its child to have a proper alias if the child is
        /// just an extent or a join.
        ///
        /// The normal relational nodes result in complete valid SQL statements.
        /// For the rest, we need to treat them as there was a dummy
        /// <code>
        /// -- originally {expression}
        /// -- change that to
        /// SELECT *
        /// FROM {expression} as c
        /// </code>
        ///
        /// DbLimitExpression needs to start the statement but not add the default columns
        /// </summary>
        /// <param name="e"></param>
        /// <param name="addDefaultColumns"></param>
        /// <returns></returns>
        private SqlSelectStatement VisitExpressionEnsureSqlStatement(DbExpression e, bool addDefaultColumns)
        {
            Debug.Assert(MetadataHelpers.IsCollectionType(e.ResultType.EdmType));

            SqlSelectStatement result;

            switch (e.ExpressionKind)
            {
            case DbExpressionKind.Project:
            case DbExpressionKind.Filter:
            case DbExpressionKind.GroupBy:
            case DbExpressionKind.Sort:
                result = e.Accept(this) as SqlSelectStatement;
                break;

            default:
                string inputVarName = "c";      // any name will do - this is my random choice.
                symbolTable.EnterScope();

                TypeUsage type = null;
                switch (e.ExpressionKind)
                {
                case DbExpressionKind.Scan:
                case DbExpressionKind.CrossJoin:
                case DbExpressionKind.FullOuterJoin:
                case DbExpressionKind.InnerJoin:
                case DbExpressionKind.LeftOuterJoin:
                case DbExpressionKind.CrossApply:
                case DbExpressionKind.OuterApply:
                    // It used to be type = e.ResultType.
                    type = MetadataHelpers.GetElementTypeUsage(e.ResultType);
                    break;

                default:
                    Debug.Assert(MetadataHelpers.IsCollectionType(e.ResultType.EdmType));
                    type = MetadataHelpers.GetEdmType <CollectionType>(e.ResultType).TypeUsage;
                    break;
                }

                Symbol fromSymbol;
                result = VisitInputExpression(e, inputVarName, type, out fromSymbol);
                AddFromSymbol(result, inputVarName, fromSymbol);
                symbolTable.ExitScope();
                break;
            }

            if (addDefaultColumns && result.Select.IsEmpty)
            {
                AddDefaultColumns(result);
            }

            return(result);
        }
コード例 #4
0
        /// <summary>
        /// <see cref="Visit(DbFilterExpression)"/> for general details.
        /// We modify both the GroupBy and the Select fields of the SqlSelectStatement.
        /// GroupBy gets just the keys without aliases,
        /// and Select gets the keys and the aggregates with aliases.
        ///
        /// Whenever there exists at least one aggregate with an argument that is not is not a simple
        /// <see cref="DbPropertyExpression"/>  over <see cref="DbVariableReferenceExpression"/>,
        /// we create a nested query in which we alias the arguments to the aggregates.
        /// That is due to the following two limitations of Sql Server:
        /// <list type="number">
        /// <item>If an expression being aggregated contains an outer reference, then that outer
        /// reference must be the only column referenced in the expression </item>
        /// <item>Sql Server cannot perform an aggregate function on an expression containing
        /// an aggregate or a subquery. </item>
        /// </list>
        ///
        /// The default translation, without inner query is:
        ///
        ///     SELECT
        ///         kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn,
        ///         aggf1(aexpr1) AS agg1, .. aggfn(aexprn) AS aggn
        ///     FROM input AS a
        ///     GROUP BY kexp1, kexp2, .. kexpn
        ///
        /// When we inject an innner query, the equivalent translation is:
        ///
        ///     SELECT
        ///         key1 AS key1, key2 AS key2, .. keyn AS keys,
        ///         aggf1(agg1) AS agg1, aggfn(aggn) AS aggn
        ///     FROM (
        ///             SELECT
        ///                 kexp1 AS key1, kexp2 AS key2,... kexpn AS keyn,
        ///                 aexpr1 AS agg1, .. aexprn AS aggn
        ///             FROM input AS a
        ///         ) as a
        ///     GROUP BY key1, key2, keyn
        ///
        /// </summary>
        /// <param name="e"></param>
        /// <returns>A <see cref="SqlSelectStatement"/></returns>
        public override ISqlFragment Visit(DbGroupByExpression e)
        {
            Symbol             fromSymbol;
            SqlSelectStatement innerQuery = VisitInputExpression(e.Input.Expression,
                                                                 e.Input.VariableName, e.Input.VariableType, out fromSymbol);

            // GroupBy is compatible with Filter and OrderBy
            // but not with Project, GroupBy
            if (!IsCompatible(innerQuery, e.ExpressionKind))
            {
                innerQuery = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, out fromSymbol);
            }

            selectStatementStack.Push(innerQuery);
            symbolTable.EnterScope();

            AddFromSymbol(innerQuery, e.Input.VariableName, fromSymbol);
            // This line is not present for other relational nodes.
            symbolTable.Add(e.Input.GroupVariableName, fromSymbol);

            // The enumerator is shared by both the keys and the aggregates,
            // so, we do not close it in between.
            RowType groupByType = MetadataHelpers.GetEdmType <RowType>(MetadataHelpers.GetEdmType <CollectionType>(e.ResultType).TypeUsage);

            //Whenever there exists at least one aggregate with an argument that is not simply a PropertyExpression
            // over a VarRefExpression, we need a nested query in which we alias the arguments to the aggregates.
            bool needsInnerQuery = NeedsInnerQuery(e.Aggregates);

            SqlSelectStatement result;

            if (needsInnerQuery)
            {
                //Create the inner query
                result = CreateNewSelectStatement(innerQuery, e.Input.VariableName, e.Input.VariableType, false, out fromSymbol);
                AddFromSymbol(result, e.Input.VariableName, fromSymbol, false);
            }
            else
            {
                result = innerQuery;
            }

            using (IEnumerator <EdmProperty> members = groupByType.Properties.GetEnumerator())
            {
                members.MoveNext();
                Debug.Assert(result.Select.IsEmpty);

                string separator = "";

                foreach (DbExpression key in e.Keys)
                {
                    EdmProperty member = members.Current;
                    var         alias  = new Symbol(member.Name);

                    result.GroupBy.Append(separator);

                    ISqlFragment keySql = key.Accept(this);

                    if (!needsInnerQuery)
                    {
                        //Default translation: Alias = Key
                        result.Select.Append(new SelectColumn(alias, keySql));
                        result.GroupBy.Append(keySql);
                    }
                    else
                    {
                        // The inner query contains the default translation Alias = Key
                        result.Select.Append(new SelectColumn(alias, keySql));

                        //The outer resulting query projects over the key aliased in the inner query:
                        //  fromSymbol.Alias AS Alias
                        result.Select.Append(new SelectColumn(alias, fromSymbol, alias));
                        result.GroupBy.Append(alias);
                    }

                    separator = ", ";
                    members.MoveNext();
                }

                foreach (DbAggregate aggregate in e.Aggregates)
                {
                    EdmProperty member = members.Current;
                    var         alias  = new Symbol(member.Name);

                    Debug.Assert(aggregate.Arguments.Count == 1);
                    ISqlFragment translatedAggregateArgument = aggregate.Arguments[0].Accept(this);

                    ISqlFragment aggregateArgument;

                    if (needsInnerQuery)
                    {
                        //In this case the argument to the aggratete is reference to the one projected out by the
                        // inner query
                        SqlBuilder wrappingAggregateArgument = new SqlBuilder();
                        wrappingAggregateArgument.Append(fromSymbol);
                        wrappingAggregateArgument.Append(".");
                        wrappingAggregateArgument.Append(SqlGenerator.QuoteIdentifier(alias.Name));
                        aggregateArgument = wrappingAggregateArgument;

                        innerQuery.Select.Append(new SelectColumn(alias, translatedAggregateArgument));
                    }
                    else
                    {
                        aggregateArgument = translatedAggregateArgument;
                    }

                    ISqlFragment aggregateResult = VisitAggregate(aggregate, aggregateArgument);

                    result.Select.Append(new SelectColumn(alias, aggregateResult));

                    separator = ", ";
                    members.MoveNext();
                }
            }

            symbolTable.ExitScope();
            selectStatementStack.Pop();

            return(result);
        }