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));
}
