Final (Stable) Version of AdvantageDatabaseServer8 Dialect
using System;
using System.Data;
using System.Data.Common;
using NHibernate.Dialect.Function;
using NHibernate.Dialect.Schema;
using NHibernate.Driver;
using NHibernate.SqlCommand;
using Environment = NHibernate.Cfg.Environment;
namespace NHibernate.Dialect
{
/// <summary>
/// A dialect for Sybase's Advantage Database Server 8.0 and
above.
/// </summary>
public class AdvantageDatabaseServer8Dialect : Dialect
{
public AdvantageDatabaseServer8Dialect()
{
RegisterDefaultProperties();
RegisterKeywords();
RegisterCharacterTypeMappings();
RegisterNumericTypeMappings();
RegisterDateTimeTypeMappings();
RegisterLargeObjectTypeMappings();
RegisterFunctions();
}
protected virtual void RegisterDefaultProperties()
{
DefaultProperties[Environment.ConnectionProvider] =
"NHibernate.Connection.DriverConnectionProvider";
DefaultProperties[Environment.ConnectionDriver] =
"NHibernate.Driver.AdvantageDatabaseServerDriver";
DefaultProperties[Environment.BatchSize] = "20";
DefaultProperties[Environment.QuerySubstitutions] = "true
1, false 0, yes 'Y', no 'N'";
DefaultProperties[Environment.Hbm2ddlKeyWords] = "none";
}
protected virtual void RegisterKeywords()
{
#region Programability
RegisterKeyword("top");
RegisterKeyword("declare");
RegisterKeyword("as");
RegisterKeyword("between");
RegisterKeyword("cast");
RegisterKeyword("begin");
RegisterKeyword("end");
RegisterKeyword("constraint");
RegisterKeyword("fetch");
RegisterKeyword("function");
RegisterKeyword("in");
RegisterKeyword("into");
RegisterKeyword("left");
#endregion
#region DataTypes
RegisterKeyword("shortint");
RegisterKeyword("integer");
RegisterKeyword("double");
RegisterKeyword("curdouble");
RegisterKeyword("logical");
RegisterKeyword("autoincrement");
RegisterKeyword("binary");
RegisterKeyword("image");
RegisterKeyword("raw");
RegisterKeyword("character");
RegisterKeyword("cicharacter");
RegisterKeyword("nvarchar");
RegisterKeyword("memo");
RegisterKeyword("numeric");
RegisterKeyword("date");
RegisterKeyword("shortdate");
RegisterKeyword("time");
RegisterKeyword("timestamp");
RegisterKeyword("money");
RegisterKeyword("rowversion");
RegisterKeyword("modtime");
#endregion
RegisterKeyword("cursor");
RegisterKeyword("timestamp");
RegisterKeyword("uniqueidentifier");
RegisterKeyword("sql_variant");
RegisterKeyword("table");
}
protected virtual void RegisterFunctions()
{
RegisterFunction("count", new
StandardSQLFunction("count"));
RegisterFunction("abs", new StandardSQLFunction("abs"));
RegisterFunction("absval", new
StandardSQLFunction("absval"));
RegisterFunction("sign", new StandardSQLFunction("sign",
NHibernateUtil.Int32));
RegisterFunction("ceiling", new
StandardSQLFunction("ceiling"));
RegisterFunction("ceil", new StandardSQLFunction("ceil"));
RegisterFunction("floor", new
StandardSQLFunction("floor"));
RegisterFunction("round", new
StandardSQLFunction("round"));
RegisterFunction("acos", new StandardSQLFunction("acos",
NHibernateUtil.Double));
RegisterFunction("asin", new StandardSQLFunction("asin",
NHibernateUtil.Double));
RegisterFunction("atan", new StandardSQLFunction("atan",
NHibernateUtil.Double));
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("float", new StandardSQLFunction("float",
NHibernateUtil.Double));
RegisterFunction("hex", new StandardSQLFunction("hex",
NHibernateUtil.String));
RegisterFunction("ln", new StandardSQLFunction("ln",
NHibernateUtil.Double));
RegisterFunction("log", new StandardSQLFunction("log",
NHibernateUtil.Double));
RegisterFunction("log10", new StandardSQLFunction("log10",
NHibernateUtil.Double));
RegisterFunction("mod", new
SQLFunctionTemplate(NHibernateUtil.Int32, "((?1) % (?2))"));
RegisterFunction("radians", new
StandardSQLFunction("radians", NHibernateUtil.Double));
RegisterFunction("rand", new NoArgSQLFunction("rand",
NHibernateUtil.Double));
RegisterFunction("sin", new StandardSQLFunction("sin",
NHibernateUtil.Double));
RegisterFunction("soundex", new
StandardSQLFunction("soundex", NHibernateUtil.String));
RegisterFunction("sqrt", new StandardSQLFunction("sqrt",
NHibernateUtil.Double));
RegisterFunction("stddev", new
StandardSQLFunction("stddev", NHibernateUtil.Double));
RegisterFunction("tan", new StandardSQLFunction("tan",
NHibernateUtil.Double));
RegisterFunction("variance", new
StandardSQLFunction("variance", NHibernateUtil.Double));
RegisterFunction("left", new
SQLFunctionTemplate(NHibernateUtil.String, "left(?1, ?2)"));
RegisterFunction("right", new
SQLFunctionTemplate(NHibernateUtil.String, "right(?1, ?2)"));
RegisterFunction("locate", new
StandardSQLFunction("charindex", NHibernateUtil.Int32));
RegisterFunction("current_timestamp", new
NoArgSQLFunction("now", NHibernateUtil.DateTime, true));
RegisterFunction("second", new
SQLFunctionTemplate(NHibernateUtil.Int32, "extract(second from ?1)"));
RegisterFunction("minute", new
SQLFunctionTemplate(NHibernateUtil.Int32, "extract(minute from ?1)"));
RegisterFunction("hour", new
SQLFunctionTemplate(NHibernateUtil.Int32, "extract(hour from ?1)"));
RegisterFunction("day", new
SQLFunctionTemplate(NHibernateUtil.Int32, "extract(day from ?1)"));
RegisterFunction("month", new
SQLFunctionTemplate(NHibernateUtil.Int32, "extract(month from ?1)"));
RegisterFunction("year", new
SQLFunctionTemplate(NHibernateUtil.Int32, "extract(year from ?1)"));
RegisterFunction("concat", new
VarArgsSQLFunction(NHibernateUtil.String, "(", "+", ")"));
RegisterFunction("chr", new StandardSQLFunction("chr",
NHibernateUtil.Character));
RegisterFunction("upper", new
StandardSQLFunction("upper"));
RegisterFunction("ucase", new
StandardSQLFunction("ucase"));
RegisterFunction("lcase", new
StandardSQLFunction("lcase"));
RegisterFunction("lower", new
StandardSQLFunction("lower"));
RegisterFunction("length", new StandardSQLFunction("len",
NHibernateUtil.Int32));
RegisterFunction("ltrim", new
StandardSQLFunction("ltrim"));
RegisterFunction("trim", new
SQLFunctionTemplate(NHibernateUtil.String, "trim(?1)"));
RegisterFunction("iif", new SQLFunctionTemplate(null,
"iif(?1, ?2, ?3)"));
RegisterFunction("replace", new
StandardSafeSQLFunction("replace", NHibernateUtil.String, 3));
RegisterFunction("str", new
SQLFunctionTemplate(NHibernateUtil.String, "cast(?1 as
sql_varchar)"));
RegisterFunction("substring", new
EmulatedLengthSubstringFunction());
}
protected virtual void RegisterLargeObjectTypeMappings()
{
RegisterColumnType(DbType.Binary, "BINARY");
RegisterColumnType(DbType.Binary, "IMAGE");
}
protected virtual void RegisterDateTimeTypeMappings()
{
RegisterColumnType(DbType.Date, "DATE");
RegisterColumnType(DbType.Date, "SHORTDATE");
RegisterColumnType(DbType.Time, "TIME");
RegisterColumnType(DbType.DateTime, "TIMESTAMP");
RegisterColumnType(DbType.DateTime, "MODTIME");
}
protected virtual void RegisterNumericTypeMappings()
{
RegisterColumnType(DbType.Boolean, "NUMERIC(1)");
RegisterColumnType(DbType.Byte, "NUMERIC(3)");
RegisterColumnType(DbType.Currency, "NUMERIC(19,6)");
RegisterColumnType(DbType.Decimal, "NUMERIC(19,5)");
RegisterColumnType(DbType.Decimal, 19, "NUMERIC($p, $s)");
RegisterColumnType(DbType.Double, "DOUBLE");
RegisterColumnType(DbType.Int16, "SHORTINT");
RegisterColumnType(DbType.Int32, "INTEGER");
RegisterColumnType(DbType.Int64, "NUMERIC(19,0)");
RegisterColumnType(DbType.Single, "NUMERIC(19,6)");
}
protected virtual void RegisterCharacterTypeMappings()
{
RegisterColumnType(DbType.AnsiStringFixedLength,
"CHARACTER(255)");
RegisterColumnType(DbType.AnsiStringFixedLength, 4000,
"CHARACTER($l)");
RegisterColumnType(DbType.AnsiString, "NVARCHAR(255)");
RegisterColumnType(DbType.AnsiString,
SqlClientDriver.MaxSizeForLengthLimitedAnsiString, "NVARCHAR($l)");
RegisterColumnType(DbType.AnsiString,
SqlClientDriver.MaxSizeForAnsiClob, "MEMO");
RegisterColumnType(DbType.StringFixedLength,
"CHARACTER(255)");
RegisterColumnType(DbType.StringFixedLength,
SqlClientDriver.MaxSizeForLengthLimitedString, "CHARACTER($l)");
RegisterColumnType(DbType.String, "NVARCHAR(255)");
RegisterColumnType(DbType.String,
SqlClientDriver.MaxSizeForLengthLimitedString, "NVARCHAR($l)");
RegisterColumnType(DbType.String,
SqlClientDriver.MaxSizeForClob, "MEMO");
}
public override string AddColumnString
{
get { return "add"; }
}
public override string NullColumnString
{
get { return " null"; }
}
public override string CurrentTimestampSQLFunctionName
{
get { return "NOW"; }
}
public override string CurrentTimestampSelectString
{
get { return "SELECT NOW() from system.iota"; }
}
public override bool IsCurrentTimestampSelectStringCallable
{
get { return true; }
}
public override bool SupportsCurrentTimestampSelection
{
get { return true; }
}
public override bool QualifyIndexName
{
get { return false; }
}
public override string SelectGUIDString
{
get { return "select NEWIDSTRING() from system.iota"; }
}
/// <summary>
/// Generates the string to drop the table using Advantage No
Drop Clause.
/// </summary>
/// <param name="tableName">The name of the table to drop.</
param>
public override string GetDropTableString(string tableName)
{
string dropTable = "drop table {0} from database
no_delete";
return String.Format(dropTable);
}
public override string ForUpdateString
{
get { return string.Empty; }
}
public override SqlString
AppendIdentitySelectToInsert(SqlString insertSql)
{
return insertSql.Append("; " + IdentitySelectString);
}
public override bool SupportsInsertSelectIdentity
{
get { return true; }
}
public override bool SupportsIdentityColumns
{
get { return true; }
}
public override string IdentitySelectString
{
get { return "select NEWIDSTRING() from system.iota"; }
}
public override string IdentityColumnString
{
get { return "IDENTITY NOT NULL"; }
}
public override string NoColumnsInsertString
{
get { return "DEFAULT VALUES"; }
}
public override char CloseQuote
{
get { return ']'; }
}
public override char OpenQuote
{
get { return '['; }
}
/// <summary>
/// Using SELECT TOP N as limiting clause.
/// </summary>
public override bool SupportsLimit
{
get { return true; }
}
/// <summary>
/// This dialect supports limit offset.
/// </summary>
public override bool SupportsLimitOffset
{
get { return true; }
}
/// <summary>
/// Can parameters be used for a statement containing a LIMIT?
/// </summary>
public override bool SupportsVariableLimit
{
get { return false; }
}
public override SqlString GetLimitString(SqlString
queryString, SqlString offset, SqlString limit)
{
/*
* "SELECT TOP 10 START AT 11 rest of sql statement[* FROM
*]
*/
int insertIndex = GetAfterSelectInsertPoint(queryString);
var limitFragment = new SqlStringBuilder();
if (limit != null)
{
limitFragment.Add(" top ");
limitFragment.Add(limit);
}
if (offset != null)
{
limitFragment.Add(" start at ");
limitFragment.Add(offset);
}
return
queryString.Insert(GetAfterSelectInsertPoint(queryString),
limitFragment.ToSqlString());
}
public override bool SupportsTemporaryTables
{
get { return true; }
}
public override string GenerateTemporaryTableName(string
baseTableName)
{
return "#" + baseTableName;
}
public override bool DropTemporaryTableAfterUse()
{
return true;
}
/// <summary />
/// <param name="name"></param>
/// <returns></returns>
/// <remarks>
protected override string Quote(string name)
{
return OpenQuote + name.Replace(CloseQuote.ToString(), new
string(CloseQuote, 2)) + CloseQuote;
}
public override string UnQuote(string quoted)
{
if (IsQuoted(quoted))
{
quoted = quoted.Substring(1, quoted.Length - 2);
}
return quoted.Replace(new string(CloseQuote, 2),
CloseQuote.ToString());
}
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'");
}
protected bool NeedsLockHint(LockMode lockMode)
{
return lockMode.GreaterThan(LockMode.Read);
}
public override string AppendLockHint(LockMode lockMode,
string tableName)
{
if (NeedsLockHint(lockMode))
{
return tableName + " with (updlock, rowlock)";
}
return tableName;
}
public override long TimestampResolutionInTicks
{
get
{
return TimeSpan.TicksPerMillisecond * 10L;
}
}
public override bool SupportsCircularCascadeDeleteConstraints
{
get
{
return false;
}
}
public override IDataBaseSchema GetDataBaseSchema(DbConnection
connection)
{
return new MsSqlDataBaseSchema(connection);
}
public override bool SupportsUnionAll
{
get { return true; }
}
public override bool SupportsSqlBatches
{
get { return true; }
}
public override bool IsKnownToken(string currentToken, string
nextToken)
{
return currentToken == "n" && nextToken == "'";
}
}
}
