/// <summary> /// Creates a TopClause with the given topCount and withTies. /// </summary> /// <param name="topCount"> </param> /// <param name="withTies"> </param> internal TopClause(int topCount, bool withTies) { var sqlBuilder = new SqlBuilder(); sqlBuilder.Append(topCount.ToString(CultureInfo.InvariantCulture)); this.topCount = sqlBuilder; this.withTies = withTies; }
/// <summary> /// Turns a predicate into a statement returning a bit /// PREDICATE => CASE WHEN (PREDICATE) THEN CAST(1 AS BIT) WHEN (NOT (PREDICATE)) CAST (O AS BIT) END /// The predicate is produced by the given predicateTranslator. /// </summary> /// <param name="predicateTranslator"> </param> /// <param name="e"> </param> /// <returns> </returns> private static ISqlFragment WrapPredicate( Func<SqlGenerator, IList<DbExpression>, SqlBuilder, SqlBuilder> predicateTranslator, SqlGenerator sqlgen, DbFunctionExpression e) { var result = new SqlBuilder(); result.Append("CASE WHEN ("); predicateTranslator(sqlgen, e.Arguments, result); result.Append(") THEN cast(1 as bit) WHEN ( NOT ("); predicateTranslator(sqlgen, e.Arguments, result); result.Append(")) THEN cast(0 as bit) END"); return result; }
/// <summary> /// ENDSWITH(arg0, arg1) => arg0 LIKE '%arg1' /// </summary> /// <param name="sqlgen"> </param> /// <param name="args"> </param> /// <param name="result"> </param> /// <returns> </returns> private static SqlBuilder HandleCanonicalFunctionEndsWith(SqlGenerator sqlgen, IList<DbExpression> args, SqlBuilder result) { Debug.Assert(args.Count == 2, "EndsWith should have two arguments"); // Check if args[1] is a DbConstantExpression and if args [0] is a DbPropertyExpression var constSearchParamExpression = args[1] as DbConstantExpression; var targetParamExpression = args[0] as DbPropertyExpression; if ((constSearchParamExpression != null) && (targetParamExpression != null) && (string.IsNullOrEmpty(constSearchParamExpression.Value as string) == false)) { // The LIKE optimization for EndsWith can only be used when the target is a column in table and // the search string is a constant. This is because SQL Server ignores a trailing space in a query like: // EndsWith('abcd ', 'cd'), which translates to: // SELECT // CASE WHEN ('abcd ' LIKE '%cd') THEN cast(1 as bit) WHEN ( NOT ('abcd ' LIKE '%cd')) THEN cast(0 as bit) END AS [C1] // FROM ( SELECT 1 AS X ) AS [SingleRowTable1] // and "incorrectly" returns 1 (true), but the CLR would expect a 0 (false) back. TranslateConstantParameterForLike(sqlgen, args[0], constSearchParamExpression, result, true, false); } else { result.Append("CHARINDEX( REVERSE("); result.Append(args[1].Accept(sqlgen)); result.Append("), REVERSE("); result.Append(args[0].Accept(sqlgen)); result.Append(")) = 1"); } return result; }
/// <summary> /// STARTSWITH(arg0, arg1) => arg0 LIKE 'arg1%' /// </summary> /// <param name="sqlgen"> </param> /// <param name="args"> </param> /// <param name="result"> </param> /// <returns> </returns> private static SqlBuilder HandleCanonicalFunctionStartsWith(SqlGenerator sqlgen, IList<DbExpression> args, SqlBuilder result) { Debug.Assert(args.Count == 2, "StartsWith should have two arguments"); // Check if args[1] is a DbConstantExpression var constSearchParamExpression = args[1] as DbConstantExpression; if ((constSearchParamExpression != null) && (string.IsNullOrEmpty(constSearchParamExpression.Value as string) == false)) { TranslateConstantParameterForLike(sqlgen, args[0], constSearchParamExpression, result, false, true); } else { // We use CHARINDEX when the search param is a DbNullExpression because all of SQL Server 2008, 2005 and 2000 // consistently return NULL as the result. // However, if instead we use the optimized LIKE translation when the search param is a DbNullExpression, // only SQL Server 2005 yields a True instead of a DbNull as compared to SQL Server 2008 and 2000. This is // bug 32315 in LIKE in SQL Server 2005. result.Append("CHARINDEX( "); result.Append(args[1].Accept(sqlgen)); result.Append(", "); result.Append(args[0].Accept(sqlgen)); result.Append(") = 1"); } return result; }
/// <summary> /// Function to translate the StartsWith, EndsWith and Contains canonical functions to LIKE expression in T-SQL /// and also add the trailing ESCAPE '~' when escaping of the search string for the LIKE expression has occurred /// </summary> /// <param name="sqlgen"> </param> /// <param name="targetExpression"> </param> /// <param name="constSearchParamExpression"> </param> /// <param name="result"> </param> /// <param name="insertPercentStart"> </param> /// <param name="insertPercentEnd"> </param> private static void TranslateConstantParameterForLike( SqlGenerator sqlgen, DbExpression targetExpression, DbConstantExpression constSearchParamExpression, SqlBuilder result, bool insertPercentStart, bool insertPercentEnd) { result.Append(targetExpression.Accept(sqlgen)); result.Append(" LIKE "); // If it's a DbConstantExpression then escape the search parameter if necessary. bool escapingOccurred; var searchParamBuilder = new StringBuilder(); if (insertPercentStart) { searchParamBuilder.Append("%"); } searchParamBuilder.Append( SqlProviderManifest.EscapeLikeText(constSearchParamExpression.Value as string, false, out escapingOccurred)); if (insertPercentEnd) { searchParamBuilder.Append("%"); } var escapedSearchParamExpression = constSearchParamExpression.ResultType.Constant(searchParamBuilder.ToString()); result.Append(escapedSearchParamExpression.Accept(sqlgen)); // If escaping did occur (special characters were found), then append the escape character used. if (escapingOccurred) { result.Append(" ESCAPE '" + SqlProviderManifest.LikeEscapeChar + "'"); } }
/// <summary> /// Handles functions that are translated into TSQL operators. /// The given function should have one or two arguments. /// Functions with one arguemnt are translated into /// op arg /// Functions with two arguments are translated into /// arg0 op arg1 /// Also, the arguments can be optionaly enclosed in parethesis /// </summary> /// <param name="e"> </param> /// <param name="parenthesiseArguments"> Whether the arguments should be enclosed in parethesis </param> /// <returns> </returns> private static ISqlFragment HandleSpecialFunctionToOperator(SqlGenerator sqlgen, DbFunctionExpression e, bool parenthesiseArguments) { var result = new SqlBuilder(); Debug.Assert(e.Arguments.Count > 0 && e.Arguments.Count <= 2, "There should be 1 or 2 arguments for operator"); if (e.Arguments.Count > 1) { if (parenthesiseArguments) { result.Append("("); } result.Append(e.Arguments[0].Accept(sqlgen)); if (parenthesiseArguments) { result.Append(")"); } } result.Append(" "); Debug.Assert(_functionNameToOperatorDictionary.ContainsKey(e.Function.Name), "The function can not be mapped to an operator"); result.Append(_functionNameToOperatorDictionary[e.Function.Name]); result.Append(" "); if (parenthesiseArguments) { result.Append("("); } result.Append(e.Arguments[e.Arguments.Count - 1].Accept(sqlgen)); if (parenthesiseArguments) { result.Append(")"); } return result; }
private static void WriteFunctionArguments(SqlGenerator sqlgen, IEnumerable<DbExpression> functionArguments, SqlBuilder result) { result.Append("("); var separator = ""; foreach (var arg in functionArguments) { result.Append(separator); result.Append(arg.Accept(sqlgen)); separator = ", "; } result.Append(")"); }
private static ISqlFragment WrapWithCast(string returnType, Action<SqlBuilder> toWrap) { var result = new SqlBuilder(); if (returnType != null) { result.Append(" CAST("); } toWrap(result); if (returnType != null) { result.Append(" AS "); result.Append(returnType); result.Append(")"); } return result; }
/// <summary> /// Handler for all date/time addition canonical functions. /// Translation, e.g. /// DiffYears(datetime, number) => DATEDIFF(year, number, datetime) /// </summary> /// <param name="sqlgen"> </param> /// <param name="e"> </param> /// <returns> </returns> private static ISqlFragment HandleCanonicalFunctionDateDiff(SqlGenerator sqlgen, DbFunctionExpression e) { var result = new SqlBuilder(); result.Append("DATEDIFF ("); result.Append(_dateDiffFunctionNameToDatepartDictionary[e.Function.Name]); result.Append(", "); result.Append(e.Arguments[0].Accept(sqlgen)); result.Append(", "); result.Append(e.Arguments[1].Accept(sqlgen)); result.Append(")"); return result; }
/// <summary> /// TruncateTime(DateTime X) /// PreKatmai: TRUNCATETIME(X) => CONVERT(DATETIME, CONVERT(VARCHAR(255), expression, 102), 102) /// Katmai: TRUNCATETIME(X) => CONVERT(DATETIME2, CONVERT(VARCHAR(255), expression, 102), 102) /// TruncateTime(DateTimeOffset X) /// TRUNCATETIME(X) => CONVERT(datetimeoffset, CONVERT(VARCHAR(255), expression, 102) /// + ' 00:00:00 ' + Right(convert(varchar(255), @arg, 121), 6), 102) /// </summary> /// <param name="sqlgen"> </param> /// <param name="e"> </param> /// <returns> </returns> private static ISqlFragment HandleCanonicalFunctionTruncateTime(SqlGenerator sqlgen, DbFunctionExpression e) { //The type that we need to return is based on the argument type. string typeName = null; var isDateTimeOffset = false; var typeKind = e.Arguments[0].ResultType.GetPrimitiveTypeKind(); if (typeKind == PrimitiveTypeKind.DateTime) { typeName = sqlgen.IsPreKatmai ? "datetime" : "datetime2"; } else if (typeKind == PrimitiveTypeKind.DateTimeOffset) { typeName = "datetimeoffset"; isDateTimeOffset = true; } else { Debug.Assert(true, "Unexpected type to TruncateTime" + typeKind.ToString()); } var result = new SqlBuilder(); result.Append("convert ("); result.Append(typeName); result.Append(", convert(varchar(255), "); result.Append(e.Arguments[0].Accept(sqlgen)); result.Append(", 102) "); if (isDateTimeOffset) { result.Append("+ ' 00:00:00 ' + Right(convert(varchar(255), "); result.Append(e.Arguments[0].Accept(sqlgen)); result.Append(", 121), 6) "); } result.Append(", 102)"); return result; }
/// <summary> /// Helper method that wrapps the given expession with a conver to varchar(255) /// </summary> /// <param name="result"> </param> /// <param name="e"> </param> private static void AppendConvertToVarchar(SqlGenerator sqlgen, SqlBuilder result, DbExpression e) { result.Append("convert(varchar(255), "); result.Append(e.Accept(sqlgen)); result.Append(")"); }
/// <summary> /// Helper for all date and time types creating functions. /// The given expression is in general trainslated into: /// CONVERT(@typename, [datePart] + [timePart] + [timeZonePart], 121), where the datePart and the timeZonePart are optional /// Only on Katmai, if a date part is present it is wrapped with a call for adding years as shown below. /// The individual parts are translated as: /// Date part: /// PRE KATMAI: convert(varchar(255), @year) + '-' + convert(varchar(255), @month) + '-' + convert(varchar(255), @day) /// KATMAI: DateAdd(year, @year-1, covert(@typename, '0001' + '-' + convert(varchar(255), @month) + '-' + convert(varchar(255), @day) + [possibly time ], 121) /// Time part: /// PRE KATMAI: convert(varchar(255), @hour)+ ':' + convert(varchar(255), @minute)+ ':' + str(@second, 6, 3) /// KATMAI: convert(varchar(255), @hour)+ ':' + convert(varchar(255), @minute)+ ':' + str(@second, 10, 7) /// Time zone part: /// (case when @tzoffset >= 0 then '+' else '-' end) + convert(varchar(255), ABS(@tzoffset)/60) + ':' + convert(varchar(255), ABS(@tzoffset)%60) /// </summary> /// <param name="typeName"> </param> /// <param name="args"> </param> /// <param name="hasDatePart"> </param> /// <param name="hasTimeZonePart"> </param> /// <returns> </returns> private static ISqlFragment HandleCanonicalFunctionDateTimeTypeCreation( SqlGenerator sqlgen, string typeName, IList<DbExpression> args, bool hasDatePart, bool hasTimeZonePart) { Debug.Assert( args.Count == (hasDatePart ? 3 : 0) + 3 + (hasTimeZonePart ? 1 : 0), "Invalid number of parameters for a date time creating function"); var result = new SqlBuilder(); var currentArgumentIndex = 0; if (!sqlgen.IsPreKatmai && hasDatePart) { result.Append("DATEADD(year, "); sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex++], result); result.Append(" - 1, "); } result.Append("convert ("); result.Append(typeName); result.Append(","); //Building the string representation if (hasDatePart) { // YEAR: PREKATMAI: CONVERT(VARCHAR, @YEAR) // KATMAI : '0001' if (!sqlgen.IsPreKatmai) { result.Append("'0001'"); } else { AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]); } // MONTH result.Append(" + '-' + "); AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]); // DAY result.Append(" + '-' + "); AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]); result.Append(" + ' ' + "); } // HOUR AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]); // MINUTE result.Append(" + ':' + "); AppendConvertToVarchar(sqlgen, result, args[currentArgumentIndex++]); // SECOND result.Append(" + ':' + str("); result.Append(args[currentArgumentIndex++].Accept(sqlgen)); if (sqlgen.IsPreKatmai) { result.Append(", 6, 3)"); } else { result.Append(", 10, 7)"); } // TZOFFSET if (hasTimeZonePart) { result.Append(" + (CASE WHEN "); sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex], result); result.Append(" >= 0 THEN '+' ELSE '-' END) + convert(varchar(255), ABS("); sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex], result); result.Append("/60)) + ':' + convert(varchar(255), ABS("); sqlgen.ParenthesizeExpressionIfNeeded(args[currentArgumentIndex], result); result.Append("%60))"); } result.Append(", 121)"); if (!sqlgen.IsPreKatmai && hasDatePart) { result.Append(")"); } return result; }
/// <summary> /// Handler for turning a canonical function into DATEPART /// Results in DATEPART(datepart, e) /// </summary> /// <param name="datepart"> </param> /// <param name="e"> </param> /// <returns> </returns> private static ISqlFragment HandleCanonicalFunctionDatepart(SqlGenerator sqlgen, string datepart, DbFunctionExpression e) { var result = new SqlBuilder(); result.Append("DATEPART ("); result.Append(datepart); result.Append(", "); Debug.Assert(e.Arguments.Count == 1, "Canonical datepart functions should have exactly one argument"); result.Append(e.Arguments[0].Accept(sqlgen)); result.Append(")"); return result; }
/// <summary> /// Handles special case in which datapart 'type' parameter is present. all the functions /// handles here have *only* the 1st parameter as datepart. datepart value is passed along /// the QP as string and has to be expanded as TSQL keyword. /// </summary> /// <param name="sqlgen"> </param> /// <param name="e"> </param> /// <returns> </returns> internal static ISqlFragment HandleDatepartDateFunction(SqlGenerator sqlgen, DbFunctionExpression e) { Debug.Assert(e.Arguments.Count > 0, "e.Arguments.Count > 0"); var constExpr = e.Arguments[0] as DbConstantExpression; if (null == constExpr) { throw new InvalidOperationException( Strings.SqlGen_InvalidDatePartArgumentExpression(e.Function.NamespaceName, e.Function.Name)); } var datepart = constExpr.Value as string; if (null == datepart) { throw new InvalidOperationException( Strings.SqlGen_InvalidDatePartArgumentExpression(e.Function.NamespaceName, e.Function.Name)); } // // check if datepart value is valid // if (!_datepartKeywords.Contains(datepart)) { throw new InvalidOperationException( Strings.SqlGen_InvalidDatePartArgumentValue(datepart, e.Function.NamespaceName, e.Function.Name)); } var result = new SqlBuilder(); // // finaly, expand the function name // WriteFunctionName(result, e.Function); result.Append("("); // expand the datepart literal as tsql kword result.Append(datepart); // expand remaining arguments for (var i = 1; i < e.Arguments.Count; i++) { result.Append(", "); result.Append(e.Arguments[i].Accept(sqlgen)); } result.Append(")"); return result; }
/// <summary> /// Writes the function name to the given SqlBuilder. /// </summary> /// <param name="function"> </param> /// <param name="result"> </param> internal static void WriteFunctionName(SqlBuilder result, EdmFunction function) { string storeFunctionName; if (null != function.StoreFunctionNameAttribute) { storeFunctionName = function.StoreFunctionNameAttribute; } else { storeFunctionName = function.Name; } // If the function is a builtin (i.e. the BuiltIn attribute has been // specified, both store and canonical functions have this attribute), // then the function name should not be quoted; // additionally, no namespace should be used. if (function.IsCanonicalFunction()) { result.Append(storeFunctionName.ToUpperInvariant()); } else if (IsStoreFunction(function)) { result.Append(storeFunctionName); } else { // Should we actually support this? if (String.IsNullOrEmpty(function.Schema)) { result.Append(SqlGenerator.QuoteIdentifier(function.NamespaceName)); } else { result.Append(SqlGenerator.QuoteIdentifier(function.Schema)); } result.Append("."); result.Append(SqlGenerator.QuoteIdentifier(storeFunctionName)); } }
private static ISqlFragment HandleSpatialStaticMethodFunctionAppendSrid( SqlGenerator sqlgen, DbFunctionExpression functionExpression, string functionName) { if (functionExpression.Arguments.Count == 2) { return HandleFunctionDefaultGivenName(sqlgen, functionExpression, functionName); } else { var sridExpression = functionExpression.ResultType.IsPrimitiveType(PrimitiveTypeKind.Geometry) ? _defaultGeometrySridExpression : _defaultGeographySridExpression; var result = new SqlBuilder(); result.Append(functionName); WriteFunctionArguments(sqlgen, functionExpression.Arguments.Concat(new[] { sridExpression }), result); return result; } }
/// <summary> /// Common handler for the canonical functions ROUND and TRUNCATE /// </summary> /// <param name="e"> </param> /// <param name="round"> </param> /// <returns> </returns> private static ISqlFragment HandleCanonicalFunctionRoundOrTruncate(SqlGenerator sqlgen, DbFunctionExpression e, bool round) { var result = new SqlBuilder(); // Do not add the cast for the Round() overload having two arguments. // Round(Single,Int32) maps to Round(Double,Int32)due to implicit casting. // We don't need to cast in that case, since the server returned type is same // as the expected type. Cast is only required for the overload - Round(Single) var requiresCastToSingle = false; if (e.Arguments.Count == 1) { requiresCastToSingle = CastReturnTypeToSingle(e); if (requiresCastToSingle) { result.Append(" CAST("); } } result.Append("ROUND("); Debug.Assert(e.Arguments.Count <= 2, "Round or truncate should have at most 2 arguments"); result.Append(e.Arguments[0].Accept(sqlgen)); result.Append(", "); if (e.Arguments.Count > 1) { result.Append(e.Arguments[1].Accept(sqlgen)); } else { result.Append("0"); } if (!round) { result.Append(", 1"); } result.Append(")"); if (requiresCastToSingle) { result.Append(" AS real)"); } return result; }
/// <summary> /// Default handling on function arguments. /// Appends the list of arguments to the given result /// If the function is niladic it does not append anything, /// otherwise it appends (arg1, arg2, .., argn) /// </summary> /// <param name="e"> </param> /// <param name="result"> </param> private static void HandleFunctionArgumentsDefault(SqlGenerator sqlgen, DbFunctionExpression e, SqlBuilder result) { var isNiladicFunction = e.Function.NiladicFunctionAttribute; Debug.Assert( !(isNiladicFunction && (0 < e.Arguments.Count)), "function attributed as NiladicFunction='true' in the provider manifest cannot have arguments"); if (isNiladicFunction && e.Arguments.Count > 0) { throw new MetadataException(Strings.SqlGen_NiladicFunctionsCannotHaveParameters); } if (!isNiladicFunction) { WriteFunctionArguments(sqlgen, e.Arguments, result); } }
/// <summary> /// Handle the canonical function Abs(). /// </summary> /// <param name="sqlgen"> </param> /// <param name="e"> </param> /// <returns> </returns> private static ISqlFragment HandleCanonicalFunctionAbs(SqlGenerator sqlgen, DbFunctionExpression e) { // Convert the call to Abs(Byte) to a no-op, since Byte is an unsigned type. if (e.Arguments[0].ResultType.IsPrimitiveType(PrimitiveTypeKind.Byte)) { var result = new SqlBuilder(); result.Append(e.Arguments[0].Accept(sqlgen)); return result; } else { return HandleFunctionDefault(sqlgen, e); } }
/// <summary> /// TRIM(string) -> LTRIM(RTRIM(string)) /// </summary> /// <param name="sqlgen"> </param> /// <param name="e"> </param> /// <returns> </returns> private static ISqlFragment HandleCanonicalFunctionTrim(SqlGenerator sqlgen, DbFunctionExpression e) { var result = new SqlBuilder(); result.Append("LTRIM(RTRIM("); Debug.Assert(e.Arguments.Count == 1, "Trim should have one argument"); result.Append(e.Arguments[0].Accept(sqlgen)); result.Append("))"); return result; }
// <summary> // Creates a SkipClause with the given skipCount. // </summary> internal SkipClause(int skipCount) { var sqlBuilder = new SqlBuilder(); sqlBuilder.Append(skipCount.ToString(CultureInfo.InvariantCulture)); this.skipCount = sqlBuilder; }