Ejemplo n.º 1
0
        public static Field GetField(this FunctionCall functionCall,
                                     string columnName,
                                     ILogger logger,
                                     SchemaFile file
                                     )
        {
            var functionName = functionCall.FunctionName.Value;

            columnName = columnName ?? functionCall.GetTokenText();

            // scalar valued system functions
            switch (functionName.ToUpper())
            {
            case "SCOPE_IDENTITY":
            case "@@IDENTITY":     // TODO : this is global variable and not a function
            {
                // https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql
                return(new DecimalField()
                    {
                        Name = columnName,
                        IsNullable = false,
                        Precision = 38,
                        Scale = 0,
                        Origin = OriginType.FunctionReturn,
                    });
            }

            case "NEWID":
                return(new DefaultField()
                {
                    Name = columnName,
                    IsNullable = false,
                    Type = FieldType.UniqueIdentifier,
                    Origin = OriginType.FunctionReturn,
                });

            case "COUNT":
            case "BINARY_CHECKSUM":
            case "DATEDIFF":
            case "OBJECTPROPERTY":
            case "ERROR_NUMBER":
            case "ERROR_SEVERITY":
            case "ERROR_STATE":
            case "ERROR_LINE":
            case "DB_ID":
                return(new DefaultField()
                {
                    Name = columnName,
                    IsNullable = false,
                    Type = FieldType.Int,
                    Origin = OriginType.FunctionReturn,
                });

            case "ERROR_MESSAGE":
                return(new StringField()
                {
                    Name = columnName,
                    IsNullable = false,
                    Type = FieldType.String,     // NVARCHAR
                    Origin = OriginType.FunctionReturn,
                    Length = 4000,
                });

            case "ERROR_PROCEDURE":
            case "HOST_NAME":
            case "SUSER_SNAME":
            case "DATEPART":
                return(new StringField()
                {
                    Name = columnName,
                    IsNullable = false,
                    Type = FieldType.String,     // NVARCHAR
                    Origin = OriginType.FunctionReturn,
                    Length = 128,
                });

            case "COUNT_BIG":
            case "ROW_NUMBER":
            case "RANK":
            case "DENSE_RANK":
            case "NTILE":
                return(new DefaultField()
                {
                    Name = columnName,
                    IsNullable = false,
                    Type = FieldType.BigInt,
                    Origin = OriginType.FunctionReturn,
                });

            case "LOG":
            case "PERCENTILE_CONT":
            case "PERCENTILE_DISC":
            case "PERCENT_RANK":
            case "STDEV":
            case "STDEVP":
            case "RAND":
                return(new DefaultField()
                {
                    Name = columnName,
                    IsNullable = false,
                    Type = FieldType.Float,     // TODO : some are FLOAT and some FLOAT(53), does that matter?
                    Origin = OriginType.FunctionReturn,
                });

            case "GETDATE":
            case "DATEADD":
            case "GETUTCDATE":
                return(new DefaultField()
                {
                    Name = columnName,
                    IsNullable = false,
                    Type = FieldType.DateTime,
                    Origin = OriginType.FunctionReturn,
                });

            case "DATENAME":
                return(new StringField()
                {
                    Name = columnName,
                    IsNullable = false,
                    Type = FieldType.String,
                    Origin = OriginType.FunctionReturn,
                    Length = 0,     // TODO..
                });

            case "SYSDATETIME":
                return(new DefaultField()
                {
                    Name = columnName,
                    IsNullable = false,
                    Type = FieldType.DateTime2,     // TODO : datetime2(7) .. does that matter?
                    Origin = OriginType.FunctionReturn,
                });

            case "TRIM":
            case "LTRIM":
            case "RTRIM":
            case "UPPER":
            case "LOWER":
            case "SUM":     // TODO : Returns the summation of all expression values in the most precise expression data type.
            case "MIN":
            case "MAX":
            {
                return(functionCall
                       .Parameters
                       .First()
                       .GetField(columnName, logger, file));
            }

            case "AVG":     // The evaluated result of expression determines the return type
            {
                var column = functionCall
                             .Parameters
                             .First()
                             .GetField(columnName, logger, file);

                // https://docs.microsoft.com/en-us/sql/t-sql/functions/avg-transact-sql
                switch (column.Type)
                {
                case FieldType.SmallInt:
                case FieldType.TinyInt:
                    column.Type = FieldType.Int;
                    break;

                case FieldType.Decimal:
                case FieldType.Numeric:
                    // TODO : compute the value, this will return decimal(p, s) for now
                    // decimal category (p, s) returns decimal(38, s) divided by decimal(10, 0)
                    break;

                case FieldType.SmallMoney:
                    column.Type = FieldType.Money;
                    break;

                case FieldType.Real:
                    column.Type = FieldType.Float;
                    break;
                }

                return(column);
            }

            case "STUFF":
            {
                // TODO : this can return binary data as well
                // TODO : can I determine length from character_expression and replaceWith_expression?
                return(new StringField()
                    {
                        Name = columnName,
                        IsNullable = false,
                        Type = FieldType.String,
                        Origin = OriginType.FunctionReturn,
                        Length = 0,
                    });
            }

            case "LEN":
            case "CHARINDEX":
                // TODO : bigint if expression is nvarchar(max), varbinary(max), or varchar(max) data type; int otherwise
                return(new DefaultField()
                {
                    Name = columnName,
                    IsNullable = false,
                    Type = FieldType.Int,
                    Origin = OriginType.FunctionReturn,
                });

            case "OBJECT_NAME":
                // this is the SYSNAME type
                return(new StringField()
                {
                    Name = columnName,
                    Type = FieldType.String,     // nvarchar
                    Origin = OriginType.SystemType,
                    Length = 128,
                    IsNullable = false,
                });

            default:
                break;
            }

            // scalar valued user functions
            //Microsoft.SqlServer.TransactSql.ScriptDom.ExpressionCallTarget
            //Microsoft.SqlServer.TransactSql.ScriptDom.MultiPartIdentifierCallTarget
            //Microsoft.SqlServer.TransactSql.ScriptDom.UserDefinedTypeCallTarget
            var functionKey = string.Empty;

            if (functionCall.CallTarget is MultiPartIdentifierCallTarget multiPartIdentifierCallTarget)
            {
                // TODO : make this pretty somehow
                var identifiersCount     = multiPartIdentifierCallTarget.MultiPartIdentifier.Identifiers.Count();
                var qualifiedIdentifiers = new List <string>();

                if (identifiersCount.Equals(1))
                {
                    qualifiedIdentifiers.Add(file.Context.Name);
                    qualifiedIdentifiers.Add(multiPartIdentifierCallTarget.MultiPartIdentifier.Identifiers.First().Value);
                }
                else if (identifiersCount.Equals(2))
                {
                    qualifiedIdentifiers.AddRange(multiPartIdentifierCallTarget.MultiPartIdentifier.Identifiers.Select(x => x.Value));
                }
                else
                {
                    logger.Log(LogLevel.Error, $"Unable to determine qualified identifier. Fragment: \"{functionCall.GetTokenText()}\"");
                }
                qualifiedIdentifiers.Add(functionName);

                functionKey = qualifiedIdentifiers.GetQualifiedIdentfier();
            }

            if (file.Schema.ContainsKey(functionKey))
            {
                var function    = file.Schema[functionKey];
                var firstColumn = function.Columns.FirstOrDefault();
                if (firstColumn != null)
                {
                    return(firstColumn.Copy(columnName));
                }
            }

            // TODO : table valued user functions

            // TODO : failsafe but do not return first.. compute the value
            var first = functionCall
                        .Parameters
                        .Select(x => x.GetField(columnName, logger, file))
                        .FirstOrDefault(x => x.Type != FieldType.NotSpecified);

            if (first != null)
            {
                return(first);
            }

            logger.Log(LogLevel.Error, $"Unable to determine column type from function call. Fragment: \"{functionCall.GetTokenText()}\"");

            return(new UnknownField()
            {
                Name = columnName,
            });
        }