context: using System;
  using System.Data;
  using System.Data.Common;
  using System.Text;
  using NHibernate.Dialect.Function;
  using NHibernate.SqlCommand;
  using NHibernate.Util;
  
  namespace NHibernate.Dialect
  {
  	/// <summary>
  	/// A SQL dialect for SQLite.
  	/// </summary>
  	/// <remarks>
  	/// <p>
  	/// Author: <a href="mailto:ib@stalker.ro"> Ioan Bizau </a>
  	/// </p>
  	/// </remarks>
  	public class SQLiteDialect : Dialect
  	{
  		/// <summary>
  		/// 
  		/// </summary>
  		public SQLiteDialect()
  		{
  			RegisterColumnTypes();
  			RegisterFunctions();
  			RegisterKeywords();
  			RegisterDefaultProperties();
  		}
  
  		protected virtual void RegisterColumnTypes()
  		{
  			RegisterColumnType(DbType.Binary, "BLOB");
  			RegisterColumnType(DbType.Byte, "TINYINT");
  			RegisterColumnType(DbType.Int16, "SMALLINT");
  			RegisterColumnType(DbType.Int32, "INT");
  			RegisterColumnType(DbType.Int64, "BIGINT");
  			RegisterColumnType(DbType.SByte, "INTEGER");
  			RegisterColumnType(DbType.UInt16, "INTEGER");
  			RegisterColumnType(DbType.UInt32, "INTEGER");
  			RegisterColumnType(DbType.UInt64, "INTEGER");
  			RegisterColumnType(DbType.Currency, "NUMERIC");
  			RegisterColumnType(DbType.Decimal, "NUMERIC");
  			RegisterColumnType(DbType.Double, "DOUBLE");
  			RegisterColumnType(DbType.Single, "DOUBLE");
  			RegisterColumnType(DbType.VarNumeric, "NUMERIC");
  			RegisterColumnType(DbType.AnsiString, "TEXT");
  			RegisterColumnType(DbType.String, "TEXT");
  			RegisterColumnType(DbType.AnsiStringFixedLength, "TEXT");
  			RegisterColumnType(DbType.StringFixedLength, "TEXT");
  
  			RegisterColumnType(DbType.Date, "DATE");
  			RegisterColumnType(DbType.DateTime, "DATETIME");
  			RegisterColumnType(DbType.Time, "TIME");
  			RegisterColumnType(DbType.Boolean, "BOOL");
  			RegisterColumnType(DbType.Guid, "UNIQUEIDENTIFIER");
  		}
  
  		protected virtual void RegisterFunctions()
  		{
  			// Using strftime returns 0-padded strings.  '07' <> 7, so it is better to convert to an integer.
  			RegisterFunction("second", new SQLFunctionTemplate(NHibernateUtil.Int32, "cast(strftime('%S', ?1) as int)"));
  			RegisterFunction("minute", new SQLFunctionTemplate(NHibernateUtil.Int32, "cast(strftime('%M', ?1) as int)"));
  			RegisterFunction("hour", new SQLFunctionTemplate(NHibernateUtil.Int32, "cast(strftime('%H', ?1) as int)"));
  			RegisterFunction("day", new SQLFunctionTemplate(NHibernateUtil.Int32, "cast(strftime('%d', ?1) as int)"));
  			RegisterFunction("month", new SQLFunctionTemplate(NHibernateUtil.Int32, "cast(strftime('%m', ?1) as int)"));
  			RegisterFunction("year", new SQLFunctionTemplate(NHibernateUtil.Int32, "cast(strftime('%Y', ?1) as int)"));
  			// Uses local time like MSSQL and PostgreSQL.
  			RegisterFunction("current_timestamp", new SQLFunctionTemplate(NHibernateUtil.DateTime, "datetime(current_timestamp, 'localtime')"));
  			// The System.Data.SQLite driver stores both Date and DateTime as 'YYYY-MM-DD HH:MM:SS'
  			// The SQLite date() function returns YYYY-MM-DD, which unfortunately SQLite does not consider
  			// as equal to 'YYYY-MM-DD 00:00:00'.  Because of this, it is best to return the
  			// 'YYYY-MM-DD 00:00:00' format for the date function.
  			RegisterFunction("date", new SQLFunctionTemplate(NHibernateUtil.Date, "datetime(date(?1))"));
  
  			RegisterFunction("substring", new StandardSQLFunction("substr", NHibernateUtil.String));
  			RegisterFunction("left", new SQLFunctionTemplate(NHibernateUtil.String, "substr(?1,1,?2)"));
  			RegisterFunction("trim", new AnsiTrimEmulationFunction());
  			RegisterFunction("replace", new StandardSafeSQLFunction("replace", NHibernateUtil.String, 3));
  			RegisterFunction("chr", new StandardSQLFunction("char", NHibernateUtil.Character));
  
  			RegisterFunction("mod", new SQLFunctionTemplate(NHibernateUtil.Int32, "((?1) % (?2))"));
  
  			RegisterFunction("iif", new SQLFunctionTemplate(null, "case when ?1 then ?2 else ?3 end"));
  
  			RegisterFunction("cast", new SQLiteCastFunction());
  
  			RegisterFunction("round", new StandardSQLFunction("round"));
  
  			// NH-3787: SQLite requires the cast in SQL too for not defaulting to string.
  			RegisterFunction("transparentcast", new CastFunction());
  		}
  
  		#region private static readonly string[] DialectKeywords = { ... }
  
  		private static readonly string[] DialectKeywords =
  		{
  			"abort",
  			"action",
  			"after",
  			"analyze",
  			"asc",
  			"attach",
  			"autoincrement",
  			"before",
  			"bit",
  			"bool",
  			"boolean",
  			"cascade",
  			"conflict",
  			"counter",
  			"currency",
  			"database",
  			"datetime",
  			"deferrable",
  			"deferred",
  			"desc",
  			"detach",
  			"exclusive",
  			"explain",
  			"fail",
  			"general",
  			"glob",
  			"guid",
  			"ignore",
  			"image",
  			"index",
  			"indexed",
  			"initially",
  			"instead",
  			"isnull",
  			"key",
  			"limit",
  			"logical",
  			"long",
  			"longtext",
  			"memo",
  			"money",
  			"note",
  			"notnull",
  			"ntext",
  			"nvarchar",
  			"offset",
  			"oleobject",
  			"plan",
  			"pragma",
  			"query",
  			"raise",
  			"regexp",
  			"reindex",
  			"rename",
  			"replace",
  			"restrict",
  			"single",
  			"smalldate",
  			"smalldatetime",
  			"smallmoney",
  			"sql_variant",
  			"string",
  			"temp",
  			"temporary",
  			"text",
  			"tinyint",
  			"transaction",
  			"uniqueidentifier",
  			"vacuum",
  			"varbinary",
  			"view",
  			"virtual",
  			"yesno",
  		};
  
  		#endregion
  
  		protected virtual void RegisterKeywords()
  		{
  			RegisterKeywords(DialectKeywords);
  		}
  
  		protected virtual void RegisterDefaultProperties()
  		{
  			DefaultProperties[Cfg.Environment.ConnectionDriver] = "NHibernate.Driver.SQLite20Driver";
  			DefaultProperties[Cfg.Environment.QuerySubstitutions] = "true 1, false 0, yes 'Y', no 'N'";
  		}
  
  		public override Schema.IDataBaseSchema GetDataBaseSchema(DbConnection connection)
  		{
  			return new Schema.SQLiteDataBaseMetaData(connection);
  		}
  
  		public override string AddColumnString
  		{
  			get
  			{
  				return "add column";
  			}
  		}
  
  		public override string IdentitySelectString
  		{
  			get { return "select last_insert_rowid()"; }
  		}
  
  		public override SqlString AppendIdentitySelectToInsert(SqlString insertSql)
  		{
  
gt: 			return insertSql.Append("; " + IdentitySelectString);