using System;
using System.Data.Common;
using NHibernate.Dialect.Function;
using NHibernate.Dialect.Schema;
using NHibernate.SqlCommand;
namespace NHibernate.Dialect
{
public class MsSqlCe40Dialect : MsSqlCeDialect
{
public MsSqlCe40Dialect()
{
RegisterKeywords();
RegisterFunctions();
}
///
/// Does this Dialect have some kind of LIMIT syntax?
///
/// True, we'll use the SELECT TOP nn syntax.
public override bool SupportsLimit
{
get { return true; }
}
///
/// Does this Dialect support an offset?
///
public override bool SupportsLimitOffset
{
get { return true; }
}
protected virtual void RegisterKeywords()
{
RegisterKeyword("@@IDENTITY");
RegisterKeyword("ADD");
RegisterKeyword("ALL");
RegisterKeyword("ALTER");
RegisterKeyword("AND");
RegisterKeyword("ANY");
RegisterKeyword("AS");
RegisterKeyword("ASC");
RegisterKeyword("AUTHORIZATION");
RegisterKeyword("AVG");
RegisterKeyword("BACKUP");
RegisterKeyword("BEGIN");
RegisterKeyword("BETWEEN");
RegisterKeyword("BREAK");
RegisterKeyword("BROWSE");
RegisterKeyword("BULK");
RegisterKeyword("BY");
RegisterKeyword("CASCADE");
RegisterKeyword("CASE");
RegisterKeyword("CHECK");
RegisterKeyword("CHECKPOINT");
RegisterKeyword("CLOSE");
RegisterKeyword("CLUSTERED");
RegisterKeyword("COALESCE");
RegisterKeyword("COLLATE");
RegisterKeyword("COLUMN");
RegisterKeyword("COMMIT");
RegisterKeyword("COMPUTE");
RegisterKeyword("CONSTRAINT");
RegisterKeyword("CONTAINS");
RegisterKeyword("CONTAINSTABLE");
RegisterKeyword("CONTINUE");
RegisterKeyword("CONVERT");
RegisterKeyword("COUNT");
RegisterKeyword("CREATE");
RegisterKeyword("CROSS");
RegisterKeyword("CURRENT");
RegisterKeyword("CURRENT_DATE");
RegisterKeyword("CURRENT_TIME");
RegisterKeyword("CURRENT_TIMESTAMP");
RegisterKeyword("CURRENT_USER");
RegisterKeyword("CURSOR");
RegisterKeyword("DATABASE");
RegisterKeyword("DATABASEPASSWORD");
RegisterKeyword("DATEADD");
RegisterKeyword("DATEDIFF");
RegisterKeyword("DATENAME");
RegisterKeyword("DATEPART");
RegisterKeyword("DBCC");
RegisterKeyword("DEALLOCATE");
RegisterKeyword("DECLARE");
RegisterKeyword("DEFAULT");
RegisterKeyword("DELETE");
RegisterKeyword("DENY");
RegisterKeyword("DESC");
RegisterKeyword("DISK");
RegisterKeyword("DISTINCT");
RegisterKeyword("DISTRIBUTED");
RegisterKeyword("DOUBLE");
RegisterKeyword("DROP");
RegisterKeyword("DUMP");
RegisterKeyword("ELSE");
RegisterKeyword("ENCRYPTION");
RegisterKeyword("END");
RegisterKeyword("ERRLVL");
RegisterKeyword("ESCAPE");
RegisterKeyword("EXCEPT");
RegisterKeyword("EXEC");
RegisterKeyword("EXECUTE");
RegisterKeyword("EXISTS");
RegisterKeyword("EXIT");
RegisterKeyword("EXPRESSION");
RegisterKeyword("FETCH");
RegisterKeyword("FILE");
RegisterKeyword("FILLFACTOR");
RegisterKeyword("FOR");
RegisterKeyword("FOREIGN");
RegisterKeyword("FREETEXT");
RegisterKeyword("FREETEXTTABLE");
RegisterKeyword("FROM");
RegisterKeyword("FULL");
RegisterKeyword("FUNCTION");
RegisterKeyword("GOTO");
RegisterKeyword("GRANT");
RegisterKeyword("GROUP");
RegisterKeyword("HAVING");
RegisterKeyword("HOLDLOCK");
RegisterKeyword("IDENTITY");
RegisterKeyword("IDENTITY_INSERT");
RegisterKeyword("IDENTITYCOL");
RegisterKeyword("IF");
RegisterKeyword("IN");
RegisterKeyword("INDEX");
RegisterKeyword("INNER");
RegisterKeyword("INSERT");
RegisterKeyword("INTERSECT");
RegisterKeyword("INTO");
RegisterKeyword("IS");
RegisterKeyword("JOIN");
RegisterKeyword("KEY");
RegisterKeyword("KILL");
RegisterKeyword("LEFT");
RegisterKeyword("LIKE");
RegisterKeyword("LINENO");
RegisterKeyword("LOAD");
RegisterKeyword("MAX");
RegisterKeyword("MIN");
RegisterKeyword("NATIONAL");
RegisterKeyword("NOCHECK");
RegisterKeyword("NONCLUSTERED");
RegisterKeyword("NOT");
RegisterKeyword("NULL");
RegisterKeyword("NULLIF");
RegisterKeyword("OF");
RegisterKeyword("OFF");
RegisterKeyword("OFFSETS");
RegisterKeyword("ON");
RegisterKeyword("OPEN");
RegisterKeyword("OPENDATASOURCE");
RegisterKeyword("OPENQUERY");
RegisterKeyword("OPENROWSET");
RegisterKeyword("OPENXML");
RegisterKeyword("OPTION");
RegisterKeyword("OR");
RegisterKeyword("ORDER");
RegisterKeyword("OUTER");
RegisterKeyword("OVER");
RegisterKeyword("PERCENT");
RegisterKeyword("PLAN");
RegisterKeyword("PRECISION");
RegisterKeyword("PRIMARY");
RegisterKeyword("PRINT");
RegisterKeyword("PROC");
RegisterKeyword("PROCEDURE");
RegisterKeyword("PUBLIC");
RegisterKeyword("RAISERROR");
RegisterKeyword("READ");
RegisterKeyword("READTEXT");
RegisterKeyword("RECONFIGURE");
RegisterKeyword("REFERENCES");
RegisterKeyword("REPLICATION");
RegisterKeyword("RESTORE");
RegisterKeyword("RESTRICT");
RegisterKeyword("RETURN");
RegisterKeyword("REVOKE");
RegisterKeyword("RIGHT");
RegisterKeyword("ROLLBACK");
RegisterKeyword("ROWCOUNT");
RegisterKeyword("ROWGUIDCOL");
RegisterKeyword("RULE");
RegisterKeyword("SAVE");
RegisterKeyword("SCHEMA");
RegisterKeyword("SELECT");
RegisterKeyword("SESSION_USER");
RegisterKeyword("SET");
RegisterKeyword("SETUSER");
RegisterKeyword("SHUTDOWN");
RegisterKeyword("SOME");
RegisterKeyword("STATISTICS");
RegisterKeyword("SUM");
RegisterKeyword("SYSTEM_USER");
RegisterKeyword("TABLE");
RegisterKeyword("TEXTSIZE");
RegisterKeyword("THEN");
RegisterKeyword("TO");
RegisterKeyword("TOP");
RegisterKeyword("TRAN");
RegisterKeyword("TRANSACTION");
RegisterKeyword("TRIGGER");
RegisterKeyword("TRUNCATE");
RegisterKeyword("TSEQUAL");
RegisterKeyword("UNION");
RegisterKeyword("UNIQUE");
RegisterKeyword("UPDATE");
RegisterKeyword("UPDATETEXT");
RegisterKeyword("USE");
RegisterKeyword("USER");
RegisterKeyword("VALUES");
RegisterKeyword("VARYING");
RegisterKeyword("VIEW");
RegisterKeyword("WAITFOR");
RegisterKeyword("WHEN");
RegisterKeyword("WHERE");
RegisterKeyword("WHILE");
RegisterKeyword("WITH");
RegisterKeyword("WRITETEXT");
RegisterKeyword("smallint");
RegisterKeyword("int");
RegisterKeyword("real");
RegisterKeyword("float");
RegisterKeyword("money");
RegisterKeyword("bit");
RegisterKeyword("tinyint");
RegisterKeyword("bigint");
RegisterKeyword("uniqueidentifier");
RegisterKeyword("varbinary");
RegisterKeyword("binary");
RegisterKeyword("image");
RegisterKeyword("nvarchar");
RegisterKeyword("nchar");
RegisterKeyword("ntext");
RegisterKeyword("numeric");
RegisterKeyword("datetime");
RegisterKeyword("rowversion");
RegisterKeyword("@@DBTS");
RegisterKeyword("@@SHOWPLAN");
RegisterKeyword("ABS");
RegisterKeyword("ACOS");
RegisterKeyword("ASIN");
RegisterKeyword("ATAN");
RegisterKeyword("ATN2");
RegisterKeyword("CEILING");
RegisterKeyword("CHARINDEX");
RegisterKeyword("CAST");
RegisterKeyword("COS");
RegisterKeyword("COT");
RegisterKeyword("DATALENGTH");
RegisterKeyword("DEGREES");
RegisterKeyword("EXP");
RegisterKeyword("FLOOR");
RegisterKeyword("GETDATE");
RegisterKeyword("LEN");
RegisterKeyword("LOG");
RegisterKeyword("LOG10");
RegisterKeyword("LOWER");
RegisterKeyword("LTRIM");
RegisterKeyword("NCHAR");
RegisterKeyword("NEWID");
RegisterKeyword("PATINDEX");
RegisterKeyword("PI");
RegisterKeyword("POWER");
RegisterKeyword("RADIANS");
RegisterKeyword("RAND");
RegisterKeyword("REPLACE");
RegisterKeyword("REPLICATE");
RegisterKeyword("RTRIM");
RegisterKeyword("SHOWPLAN");
RegisterKeyword("XML");
RegisterKeyword("SIGN");
RegisterKeyword("SIN");
RegisterKeyword("SPACE");
RegisterKeyword("SQRT");
RegisterKeyword("STR");
RegisterKeyword("STUFF");
RegisterKeyword("SUBSTRING");
RegisterKeyword("TAN");
RegisterKeyword("UNICODE");
RegisterKeyword("UPPER");
RegisterKeyword("FORCE");
RegisterKeyword("ROWLOCK");
RegisterKeyword("PAGLOCK");
RegisterKeyword("TABLOCK");
RegisterKeyword("DBLOCK");
RegisterKeyword("UPDLOCK");
RegisterKeyword("XLOCK");
RegisterKeyword("HOLDLOCK");
RegisterKeyword("NOLOCK");
RegisterKeyword("GO");
RegisterKeyword("NEXT");
RegisterKeyword("OFFSET");
RegisterKeyword("ONLY");
RegisterKeyword("ROWS");
}
protected virtual void RegisterFunctions()
{
//Date and Time Functions
RegisterFunction("second", new SQLFunctionTemplate(NHibernateUtil.Int32, "datepart(second, ?1)"));
RegisterFunction("minute", new SQLFunctionTemplate(NHibernateUtil.Int32, "datepart(minute, ?1)"));
RegisterFunction("hour", new SQLFunctionTemplate(NHibernateUtil.Int32, "datepart(hour, ?1)"));
RegisterFunction("day", new SQLFunctionTemplate(NHibernateUtil.Int32, "datepart(day, ?1)"));
RegisterFunction("month", new SQLFunctionTemplate(NHibernateUtil.Int32, "datepart(month, ?1)"));
RegisterFunction("year", new SQLFunctionTemplate(NHibernateUtil.Int32, "datepart(year, ?1)"));
RegisterFunction("date", new SQLFunctionTemplate(NHibernateUtil.Date, "dateadd(dd, 0, datediff(dd, 0, ?1))"));
RegisterFunction("datename", new StandardSQLFunction("datename", NHibernateUtil.String));
RegisterFunction("current_timestamp", new NoArgSQLFunction("getdate", NHibernateUtil.DateTime, true));
RegisterFunction("datediff", new StandardSQLFunction("datediff", NHibernateUtil.Int32));
//Mathematical Functions
RegisterFunction("abs", new StandardSQLFunction("abs"));
RegisterFunction("acos", new StandardSQLFunction("acos", NHibernateUtil.Double));
RegisterFunction("asin", new StandardSQLFunction("asin", NHibernateUtil.Double));
RegisterFunction("atan", new StandardSQLFunction("atan", NHibernateUtil.Double));
RegisterFunction("atan2", new StandardSQLFunction("atan2", NHibernateUtil.Double));
RegisterFunction("ceiling", new StandardSQLFunction("ceiling"));
RegisterFunction("cos", new StandardSQLFunction("cos", NHibernateUtil.Double));
RegisterFunction("cot", new StandardSQLFunction("cot", NHibernateUtil.Double));
RegisterFunction("degrees", new StandardSQLFunction("degrees", NHibernateUtil.Double));
RegisterFunction("exp", new StandardSQLFunction("exp", NHibernateUtil.Double));
RegisterFunction("floor", new StandardSQLFunction("floor"));
RegisterFunction("log", new StandardSQLFunction("log", NHibernateUtil.Double));
RegisterFunction("log10", new StandardSQLFunction("log10", NHibernateUtil.Double));
RegisterFunction("pi", new NoArgSQLFunction("pi", NHibernateUtil.Double, true));
RegisterFunction("power", new StandardSQLFunction("power", NHibernateUtil.Double));
RegisterFunction("radians", new StandardSQLFunction("radians", NHibernateUtil.Double));
RegisterFunction("rand", new NoArgSQLFunction("rand", NHibernateUtil.Double));
RegisterFunction("round", new StandardSQLFunction("round"));
RegisterFunction("sign", new StandardSQLFunction("sign", NHibernateUtil.Int32));
RegisterFunction("sin", new StandardSQLFunction("sin", NHibernateUtil.Double));
RegisterFunction("sqrt", new StandardSQLFunction("sqrt", NHibernateUtil.Double));
RegisterFunction("tan", new StandardSQLFunction("tan", NHibernateUtil.Double));
//String Functions
RegisterFunction("locate", new StandardSQLFunction("charindex", NHibernateUtil.Int32));
RegisterFunction("length", new StandardSQLFunction("len", NHibernateUtil.Int32));
RegisterFunction("lower", new StandardSQLFunction("lower"));
RegisterFunction("ltrim", new StandardSQLFunction("ltrim"));
RegisterFunction("patindex", new StandardSQLFunction("patindex", NHibernateUtil.Int32));
RegisterFunction("replace", new StandardSafeSQLFunction("replace", NHibernateUtil.String, 3));
RegisterFunction("replicate", new StandardSQLFunction("replicate", NHibernateUtil.String));
RegisterFunction("rtrim", new StandardSQLFunction("rtrim", NHibernateUtil.String));
RegisterFunction("space", new StandardSQLFunction("space", NHibernateUtil.String));
RegisterFunction("str", new VarArgsSQLFunction(NHibernateUtil.String, "str(", ",", ")"));
RegisterFunction("stuff", new StandardSQLFunction("stuff", NHibernateUtil.String));
RegisterFunction("substring", new AnsiSubstringFunction());
RegisterFunction("unicode", new StandardSQLFunction("unicode", NHibernateUtil.Int32));
RegisterFunction("upper", new StandardSQLFunction("upper"));
RegisterFunction("trim", new AnsiTrimEmulationFunction());
RegisterFunction("concat", new VarArgsSQLFunction(NHibernateUtil.String, "(", "+", ")"));
//System Functions
RegisterFunction("coalesce", new VarArgsSQLFunction("coalesce(", ",", ")"));
RegisterFunction("newid", new NoArgSQLFunction("newid", NHibernateUtil.String, true));
}
public override string SelectGUIDString
{
get { return "select newid()"; }
}
public override bool SupportsIdentityColumns
{
get { return true; }
}
public override char CloseQuote
{
get { return ']'; }
}
public override char OpenQuote
{
get { return '['; }
}
///
/// Can parameters be used for a statement containing a LIMIT?
///
public override bool SupportsVariableLimit
{
get { return false; }
}
///
/// Add a LIMIT (TOP) clause to the given SQL SELECT
///
/// A Query in the form of a SqlString.
/// Maximum number of rows to be returned by the query
/// Offset of the first row to process in the result set
/// A new SqlString that contains the LIMIT clause.
public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit)
{
if (offset > 0)
{
if (querySqlString.IndexOfCaseInsensitive(" ORDER BY ") < 0)
{
querySqlString = querySqlString.Append(" ORDER BY GETDATE()");
}
querySqlString = querySqlString.Append(string.Format(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", offset, limit));
}
/*
* "SELECT TOP limit rest-of-sql-statement"
*/
return querySqlString.Insert(GetAfterSelectInsertPoint(querySqlString), string.Format(" top ({0})", limit));
}
private static int GetAfterSelectInsertPoint(SqlString sql)
{
if (sql.StartsWithCaseInsensitive("select distinct"))
{
return 15;
}
else if (sql.StartsWithCaseInsensitive("select"))
{
return 6;
}
throw new NotSupportedException("The query should start with 'SELECT' or 'SELECT DISTINCT'");
}
///
/// Does the LIMIT clause take a "maximum" row number
/// instead of a total number of returned rows?
///
/// false, unless overridden
public override bool UseMaxForLimit
{
get { return true; }
}
public override bool SupportsSqlBatches
{
get { return false; }
}
public override bool IsKnownToken(string currentToken, string nextToken)
{
return currentToken == "n" && nextToken == "'"; // unicode character
}
public override bool SupportsUnionAll
{
get { return true; }
}
public override IDataBaseSchema GetDataBaseSchema(DbConnection connection)
{
return new MsSqlCeDataBaseSchema(connection);
}
public override bool SupportsCircularCascadeDeleteConstraints
{
get { return false; }
}
}
}