protected override SqlStatement GetLiteralDateTimePart(SqlStatement dateExpression, SqlFunctionType operationType) { switch (operationType) { case SqlFunctionType.Date: return(SqlStatement.Format("DATE({0})", dateExpression)); case SqlFunctionType.Week: return(SqlStatement.Format("WEEK({0})", dateExpression)); case SqlFunctionType.Year: return(SqlStatement.Format("YEAR({0})", dateExpression)); case SqlFunctionType.Month: return(SqlStatement.Format("MONTH({0})", dateExpression)); case SqlFunctionType.Day: return(SqlStatement.Format("Day({0})", dateExpression)); // case SqlFunctionType.Time: // TIME() returns string, so this does not work // return SqlStatement.Format("TIME({0})", dateExpression); default: Util.Throw("SQL function {0} not supported.", operationType); return(null); } }
public override SqlStatement GetLiteralConvert(SqlStatement a, Type type) { if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable <>)) { type = type.GetGenericArguments().First(); } if (type.IsValueType && a[0].Sql.StartsWith("@")) { return(a); } SqlStatement sqlTypeName; if (typeMapping.ContainsKey(type)) { sqlTypeName = typeMapping[type]; } else { sqlTypeName = "sql_variant"; } return(SqlStatement.Format("CONVERT({0},{1})", sqlTypeName, a)); }
protected override SqlStatement GetLiteralDateTimePart(SqlStatement dateExpression, SqlFunctionType operationType) { switch (operationType) { case SqlFunctionType.Date: return(SqlStatement.Format("DATE({0})", dateExpression)); case SqlFunctionType.Time: return(SqlStatement.Format("DATE_PART('time', {0})", dateExpression)); case SqlFunctionType.Week: return(SqlStatement.Format("EXTRACT(WEEK FROM {0})", dateExpression)); case SqlFunctionType.Year: return(SqlStatement.Format("EXTRACT(YEAR FROM {0})", dateExpression)); case SqlFunctionType.Month: return(SqlStatement.Format("EXTRACT(MONTH FROM {0})", dateExpression)); case SqlFunctionType.Day: return(SqlStatement.Format("EXTRACT(DAY FROM {0})", dateExpression)); default: Util.Throw("SQL function {0} not supported.", operationType); return(null); } }
protected override SqlStatement GetLiteralStringIndexOf(SqlStatement baseString, SqlStatement searchString, SqlStatement startIndex) { // SUBSTR(baseString,StartIndex) var substring = GetLiteralSubString(baseString, startIndex); // INSTR(SUBSTR(baseString, StartIndex), searchString) ---> range 1:n , 0 => doesn't exist return(SqlStatement.Format("INSTR({0},{1})", substring, searchString)); }
protected override SqlStatement GetEqualNullables(SqlStatement a, SqlStatement b) { // Default impl: // return SqlStatement.Format("({0} = {1} OR ({0} IS NULL) AND ({1} IS NULL))", a, b); // SQL CE has some strange restrictions on use of Parameters/IsNull methods and Memo fields // for ex: (@P1 IS NULL) is not supported. So we fall back to reqular Equal statement return(SqlStatement.Format("{0} = {1}", a, b)); }
protected override SqlStatement GetLiteralStringEqual(SqlStatement x, SqlStatement y, bool forceIgnoreCase) { if (forceIgnoreCase) //Use ILIKE { return(SqlStatement.Format("({0} ILIKE {1} ESCAPE '{2}')", x, y, Driver.DefaultLikeEscapeChar.ToString())); } return(base.GetLiteralStringEqual(x, y, forceIgnoreCase)); }
protected override SqlStatement GetLiteralLike(SqlStatement column, SqlStatement pattern, bool forceIgnoreCase) { if (forceIgnoreCase) //Use ILIKE { return(SqlStatement.Format("{0} ILIKE {1} ESCAPE '{2}'", column, pattern, Driver.DefaultLikeEscapeChar.ToString())); } else { return(base.GetLiteralLike(column, pattern, forceIgnoreCase)); } }
public override SqlStatement GetInsertIds(SqlStatement table, IList <SqlStatement> autoPKColumn, IList <SqlStatement> inputPKColumns, IList <SqlStatement> inputPKValues, IList <SqlStatement> outputColumns, IList <SqlStatement> outputParameters, IList <SqlStatement> outputExpressions) { // no parameters? no need to get them back if (outputParameters.Count == 0) { return(""); } // otherwise we keep track of the new values return(SqlStatement.Format("SELECT {0} INTO {1} FROM DUAL", SqlStatement.Join(", ", (from outputExpression in outputExpressions select outputExpression.Replace(".NextVal", ".CurrVal", true)).ToArray()), SqlStatement.Join(", ", outputParameters.ToArray()))); }
public override SqlStatement GetLiteralLimit(SqlStatement select, SqlStatement limit, SqlStatement offset, SqlStatement offsetAndLimit) { if (ServerVersion == MsSqlVersion.V2008) { return(GetLiteralLimit2008(select, limit, offset, offsetAndLimit)); } // 2012 and up var strOffset = offset == null ? "0" : offset.ToString(); var strLimit = limit == null ? "1000000" : limit.ToString(); return(SqlStatement.Format("{0} \r\n OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", select, strOffset, strLimit)); }
public override SqlStatement GetLiteralLimit(SqlStatement select, SqlStatement limit) { var trimSelect = "SELECT "; if (select.Count > 0 && select[0].Sql.StartsWith(trimSelect)) { var selectBuilder = new SqlStatementBuilder(select); var remaining = select[0].Sql.Substring(trimSelect.Length); selectBuilder.Parts[0] = new SqlLiteralPart(remaining); return(SqlStatement.Format("SELECT TOP ({0}) {1}", limit, selectBuilder.ToSqlStatement())); } throw new ArgumentException("S0051: Unknown select format"); }
public override SqlStatement GetLiteralLimit(SqlStatement select, SqlStatement limit) { // return string.Format("SELECT FIRST {0} FROM ({1})", limit, select); var trimSelect = "SELECT "; if (select.Count > 0 && select[0].Sql.StartsWith(trimSelect)) { var selectBuilder = new SqlStatementBuilder(select); var remaining = select[0].Sql.Substring(trimSelect.Length); selectBuilder.Parts[0] = new SqlLiteralPart(remaining); return(SqlStatement.Format("SELECT FIRST {0} {1}", limit, selectBuilder.ToSqlStatement())); } throw new ArgumentException("Invalid SELECT format"); }
protected override SqlStatement GetLiteralDateDiff(SqlStatement dateA, SqlStatement dateB) { return(SqlStatement.Format("(CONVERT(BigInt,DATEDIFF(DAY, {0}, {1}))) * 86400000 +" //diffierence in milliseconds regards days + "DATEDIFF(MILLISECOND, " // (DateA-DateB) in days +DateB = difference in time + @"DATEADD(DAY, DATEDIFF(DAY, {0}, {1}) ,{0})" + ",{1})", dateB, dateA)); //this trick is needed in sqlserver since DATEDIFF(MILLISECONDS,{0},{1}) usually crhases in the database engine due an overflow: //System.Data.SqlClient.SqlException : Difference of two datetime columns caused overflow at runtime. }
public SqlStatement GetLiteralLimit2008(SqlStatement select, SqlStatement limit, SqlStatement offset, SqlStatement offsetAndLimit) { //V2008 //TODO: fix this, get rid of all this string matching var sql = select.ToString(); var from = "\r\nFROM "; var orderBy = "\r\nORDER BY "; var selectK = "SELECT "; int fromIdx = sql.IndexOf(from); int orderByIdx = sql.IndexOf(orderBy); if (fromIdx < 0) { throw new ArgumentException("S0051: Unknown select format: " + sql); } string orderByClause = null; string sourceClause = null; if (orderByIdx >= 0) { orderByClause = sql.Substring(orderByIdx); sourceClause = sql.Substring(fromIdx, orderByIdx - fromIdx); } else { orderByClause = "ORDER BY " + sql.Substring(selectK.Length, fromIdx - selectK.Length); sourceClause = sql.Substring(fromIdx); } orderByClause = orderByClause.Replace("\r\n", string.Empty); var selectFieldsClause = sql.Substring(0, fromIdx); var finalSql = SqlStatement.Format( "SELECT *{0}" + "FROM ({0}" + " {1},{0}" + " ROW_NUMBER() OVER({2}) AS [__ROW_NUMBER]{0}" + " {3}" + " ) AS [t0]{0}" + "WHERE [__ROW_NUMBER] BETWEEN {4}+1 AND {4}+{5}{0}" + "ORDER BY [__ROW_NUMBER]", NewLine, selectFieldsClause, orderByClause, sourceClause, offset, limit); return(finalSql); }
protected override SqlStatement GetLiteralNotEqual(SqlStatement a, SqlStatement b) { // See comment above, in GetLiteralEqual(). if (b.Count == 1 && b[0].Sql == "NULL") { return(SqlStatement.Format("{0} IS NOT {1}", a, b)); } else if (a.Count == 1 && a[0].Sql == "NULL") { return(SqlStatement.Format("{0} IS NOT {1}", b, a)); } else { return(SqlStatement.Format("{0} <> {1}", a, b)); } }
protected override SqlStatement GetLiteralDateTimePart(SqlStatement dateExpression, SqlFunctionType operationType) { switch (operationType) { case SqlFunctionType.Week: return(SqlStatement.Format("DATEPART(ISOWK,{0})", dateExpression)); case SqlFunctionType.Date: return(SqlStatement.Format("CONVERT(DATE, {0})", dateExpression)); case SqlFunctionType.Time: return(SqlStatement.Format("CONVERT(TIME, {0})", dateExpression)); default: return(SqlStatement.Format("DATEPART({0},{1})", operationType.ToString().ToUpper(), dateExpression)); } }
public override SqlStatement GetLiteralLimit(SqlStatement select, SqlStatement limit, SqlStatement offset, SqlStatement offsetAndLimit) { var from = "FROM "; var orderBy = "ORDER BY "; var selectK = "SELECT "; int fromIdx = select[0].Sql.IndexOf(from); int orderByIdx = select[0].Sql.IndexOf(orderBy); if (fromIdx < 0) { throw new ArgumentException("S0051: Unknown select format: " + select[0].Sql); } string orderByClause = null; string sourceClause = null; if (orderByIdx >= 0) { orderByClause = select[0].Sql.Substring(orderByIdx); sourceClause = select[0].Sql.Substring(fromIdx, orderByIdx - fromIdx); } else { orderByClause = "ORDER BY " + select[0].Sql.Substring(selectK.Length, fromIdx - selectK.Length); sourceClause = select[0].Sql.Substring(fromIdx); } var selectFieldsClause = select[0].Sql.Substring(0, fromIdx); return(SqlStatement.Format( "SELECT *{0}" + "FROM ({0}" + " {1},{0}" + " ROW_NUMBER() OVER({2}) AS [__ROW_NUMBER]{0}" + " {3}" + " ) AS [t0]{0}" + "WHERE [__ROW_NUMBER] BETWEEN {4}+1 AND {4}+{5}{0}" + "ORDER BY [__ROW_NUMBER]", NewLine, selectFieldsClause, orderByClause, sourceClause, offset, limit)); }
protected override SqlStatement GetLiteralEqual(SqlStatement a, SqlStatement b) { // PostgreSQL return NULL (and not a boolean) for every comparaison involving // a NULL value, unless the operator used is "IS" (or "IS NOT"). Also, // using those two operators when the right-hand value is not a literal // NULL is an error. The only possibility is to explicitly check for NULL // literals and even swap the operands to make sure NULL gets to the // right place. if (b.Count == 1 && b[0].Sql == "NULL") { return(SqlStatement.Format("{0} IS {1}", a, b)); } else if (a.Count == 1 && a[0].Sql == "NULL") { return(SqlStatement.Format("{0} IS {1}", b, a)); } else { return(SqlStatement.Format("{0} = {1}", a, b)); } }
public override SqlStatement GetInsertIds(SqlStatement table, IList <SqlStatement> autoPKColumn, IList <SqlStatement> inputPKColumns, IList <SqlStatement> inputPKValues, IList <SqlStatement> outputColumns, IList <SqlStatement> outputParameters, IList <SqlStatement> outputExpressions) { // No parameters? no need to get them back. if (outputParameters.Count == 0) { return(SqlStatement.Empty); } // Otherwise we keep track of the new values. Note that we avoid null expressions // that can be present in the passed list (is this a bug above us?) IList <SqlStatement> ids = new List <SqlStatement>(); foreach (SqlStatement outputExpression in outputExpressions) { if (outputExpression != null) { ids.Add(outputExpression.Replace("nextval(", "currval(", true)); } } return(SqlStatement.Format("SELECT {0}", SqlStatement.Join(", ", ids.ToArray()))); }
protected override SqlStatement GetLiteralStringToUpper(SqlStatement a) { return(SqlStatement.Format("UPPER({0})", a)); }
protected override SqlStatement GetLiteralStringLength(SqlStatement a) { return(SqlStatement.Format("LENGTH({0})", a)); }
protected override SqlStatement GetLiteralStringIndexOf(SqlStatement baseString, SqlStatement searchString, SqlStatement startIndex, SqlStatement count) { return(GetLiteralSubtract(SqlStatement.Format("CHARINDEX({0},{1},{2})", searchString, GetLiteralSubString(baseString, "1", GetLiteralStringConcat(count, startIndex)), startIndex), "1")); }
protected override SqlStatement GetLiteralStringIndexOf(SqlStatement baseString, SqlStatement searchString, SqlStatement startIndex) { return(GetLiteralSubtract(SqlStatement.Format("CHARINDEX({0},{1},{2})", searchString, baseString, startIndex), "1")); }
protected override SqlStatement GetLiteralStringToLower(SqlStatement a) { return(SqlStatement.Format("LOWER({0})", a)); }
protected override SqlStatement GetLiteralStringConcat(SqlStatement a, SqlStatement b) { return(SqlStatement.Format("{0} + {1}", a.Replace("sql_variant", "varchar", false), b.Replace("sql_variant", "varchar", false))); }
protected override SqlStatement GetLiteralTrim(SqlStatement a) { return(SqlStatement.Format("RTRIM(LTRIM({0}))", a)); }
protected override SqlStatement GetLiteralSubString(SqlStatement baseString, SqlStatement startIndex, SqlStatement count) { //in standard sql base string index is 1 instead 0 return(SqlStatement.Format("SUBSTRING({0}, {1}, {2})", baseString, startIndex, count)); }
protected override SqlStatement GetLiteralMathLog(SqlStatement p, SqlStatement p_2) { return(SqlStatement.Format("(LOG({0})/LOG({1}))", p, p_2)); }
protected override SqlStatement GetLiteralMathPow(SqlStatement p, SqlStatement p_2) { return(SqlStatement.Format("POWER({0},{1})", p, p_2)); }
protected override SqlStatement GetLiteralStringConcat(SqlStatement a, SqlStatement b) { return(SqlStatement.Format("{0} || {1}", a, b)); }
protected override SqlStatement GetLiteralDateTimePart(SqlStatement dateExpression, SpecialExpressionType operationType) { return(SqlStatement.Format("DATEPART({0},{1})", operationType.ToString().ToUpper(), dateExpression)); }