OracleOrmLiteDialectProvider.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace ServiceStack.OrmLite.Oracle
{
    public class OracleOrmLiteDialectProvider : OrmLiteDialectProviderBase<OracleOrmLiteDialectProvider>
    {
        public const string OdpProvider = "Oracle.DataAccess.Client";
        public const string MicrosoftProvider = "System.Data.OracleClient";

        protected readonly List<string> ReservedNames = new List<string>
        {
            "ACCESS", "DEFAULT", "INTEGER", "ONLINE", "START", "ADD", "DELETE", "INTERSECT", "OPTION", "SUCCESSFUL", "ALL", "DESC",
            "INTO", "OR", "SYNONYM", "ALTER", "DISTINCT", "IS", "ORDER", "SYSDATE", "AND", "DROP", "LEVEL", "PCTFREE", "TABLE", "ANY",
            "ELSE", "LIKE", "PRIOR", "THEN", "AS", "EXCLUSIVE", "LOCK", "PRIVILEGES", "TO", "ASC", "EXISTS", "LONG", "PUBLIC", "TRIGGER",
            "AUDIT", "FILE", "MAXEXTENTS", "RAW", "UID", "BETWEEN", "FLOAT", "MINUS", "RENAME", "UNION", "BY", "FOR", "MLSLABEL", "RESOURCE",
            "UNIQUE", "CHAR", "FROM", "MODE", "REVOKE", "UPDATE", "CHECK", "GRANT", "MODIFY", "ROW", "USER", "CLUSTER", "GROUP", "NOAUDIT",
            "ROWID", "VALIDATE", "COLUMN", "HAVING", "NOCOMPRESS", "ROWNUM", "VALUES", "COMMENT", "IDENTIFIED", "NOT", "ROWS", "VARCHAR",
            "COMPRESS", "IMMEDIATE", "NOWAIT", "SELECT", "VARCHAR2", "CONNECT", "IN", "NULL", "SESSION", "VIEW", "CREATE", "INCREMENT",
            "NUMBER", "SET", "WHENEVER", "CURRENT", "INDEX", "OF", "SHARE", "WHERE", "DATE", "INITIAL", "OFFLINE", "SIZE", "WITH", "DECIMAL",
            "INSERT", "ON", "SMALLINT", "PASSWORD", "ACTIVE", "LEFT", "DOUBLE", "STRING", "DATETIME", "TYPE", "TIMESTAMP"
        };

        protected readonly List<string> ReservedParameterNames = new List<string>
        {
            "ACCESS", "DEFAULT", "INTEGER", "ONLINE", "START", "ADD", "DELETE", "INTERSECT", "OPTION", "SUCCESSFUL", "ALL", "DESC",
            "INTO", "OR", "SYNONYM", "ALTER", "DISTINCT", "IS", "ORDER", "SYSDATE", "AND", "DROP", "LEVEL", "PCTFREE", "TABLE", "ANY",
            "ELSE", "LIKE", "PRIOR", "THEN", "AS", "EXCLUSIVE", "LOCK", "PRIVILEGES", "TO", "ASC", "EXISTS", "LONG", "PUBLIC", "TRIGGER",
            "AUDIT", "FILE", "MAXEXTENTS", "RAW", "UID", "BETWEEN", "FLOAT", "MINUS", "RENAME", "UNION", "BY", "FOR", "MLSLABEL", "RESOURCE",
            "UNIQUE", "CHAR", "FROM", "MODE", "REVOKE", "UPDATE", "CHECK", "GRANT", "MODIFY", "ROW", "USER", "CLUSTER", "GROUP", "NOAUDIT",
            "ROWID", "VALIDATE", "COLUMN", "HAVING", "NOCOMPRESS", "ROWNUM", "VALUES", "COMMENT", "IDENTIFIED", "NOT", "ROWS", "VARCHAR",
            "COMPRESS", "IMMEDIATE", "NOWAIT", "SELECT", "VARCHAR2", "CONNECT", "IN", "NULL", "SESSION", "VIEW", "CREATE", "INCREMENT",
            "NUMBER", "SET", "WHENEVER", "CURRENT", "INDEX", "OF", "SHARE", "WHERE", "DATE", "INITIAL", "OFFLINE", "SIZE", "WITH", "DECIMAL",
            "INSERT", "ON", "SMALLINT"
        };

        protected const int MaxNameLength = 30;
        protected const int MaxStringColumnLength = 4000;

        private static OracleOrmLiteDialectProvider _instance;
        public static OracleOrmLiteDialectProvider Instance
        {
            // Constructing extras if we happen to hit this concurrently on separate threads is harmless enough
            get { return _instance ?? (_instance = new OracleOrmLiteDialectProvider()); }
        }

        protected bool CompactGuid;

        internal const string StringGuidDefinition = "VARCHAR2(37)";
        internal const string CompactGuidDefinition = "RAW(16)";

        private readonly DbProviderFactory _factory;
        private readonly OracleTimestampConverter _timestampConverter;

        public OracleOrmLiteDialectProvider()
            : this(false, false)
        {
        }

        public OracleOrmLiteDialectProvider(bool compactGuid, bool quoteNames, string clientProvider = OdpProvider)
        {
            ClientProvider = clientProvider;
            CompactGuid = compactGuid;
            QuoteNames = quoteNames;
            BoolColumnDefinition = "NUMBER(1)";
            GuidColumnDefinition = CompactGuid ? CompactGuidDefinition : StringGuidDefinition;
            LongColumnDefinition = "NUMERIC(18)";
            AutoIncrementDefinition = string.Empty;
            DateTimeColumnDefinition = "TIMESTAMP";
            DateTimeOffsetColumnDefinition = "TIMESTAMP WITH TIME ZONE";
            TimeColumnDefinition = LongColumnDefinition;
            RealColumnDefinition = "FLOAT";
            // Order-dependency here: must set following values before setting DefaultStringLength
            StringLengthNonUnicodeColumnDefinitionFormat = "VARCHAR2({0})";
            StringLengthUnicodeColumnDefinitionFormat = "NVARCHAR2({0})";
            MaxStringColumnDefinition = StringLengthNonUnicodeColumnDefinitionFormat.Fmt(MaxStringColumnLength);
            DefaultStringLength = 128;

            InitColumnTypeMap();
            ParamString = ":";

            NamingStrategy = new OracleNamingStrategy(MaxNameLength);
            ExecFilter = new OracleExecFilter();

            _factory = DbProviderFactories.GetFactory(ClientProvider);
            _timestampConverter = new OracleTimestampConverter(_factory.GetType());
        }

        public override void OnAfterInitColumnTypeMap()
        {
            base.OnAfterInitColumnTypeMap();

            DbTypeMap.Set<bool>(DbType.Int16, BoolColumnDefinition);
            DbTypeMap.Set<bool?>(DbType.Int16, BoolColumnDefinition);

            DbTypeMap.Set<sbyte>(DbType.Int16, IntColumnDefinition);
            DbTypeMap.Set<sbyte?>(DbType.Int16, IntColumnDefinition);
            DbTypeMap.Set<ushort>(DbType.Int32, IntColumnDefinition);
            DbTypeMap.Set<ushort?>(DbType.Int32, IntColumnDefinition);
            DbTypeMap.Set<uint>(DbType.Int64, LongColumnDefinition);
            DbTypeMap.Set<uint?>(DbType.Int64, LongColumnDefinition);
            DbTypeMap.Set<ulong>(DbType.Int64, LongColumnDefinition);
            DbTypeMap.Set<ulong?>(DbType.Int64, LongColumnDefinition);

            if (CompactGuid)
            {
                DbTypeMap.Set<Guid>(DbType.Binary, GuidColumnDefinition);
                DbTypeMap.Set<Guid?>(DbType.Binary, GuidColumnDefinition);
            }
            else
            {
                DbTypeMap.Set<Guid>(DbType.String, GuidColumnDefinition);
                DbTypeMap.Set<Guid?>(DbType.String, GuidColumnDefinition);
            }

            DbTypeMap.Set<DateTimeOffset>(DbType.String, DateTimeOffsetColumnDefinition);
            DbTypeMap.Set<DateTimeOffset?>(DbType.String, DateTimeOffsetColumnDefinition);
        }

        protected string ClientProvider = OdpProvider;
        public static string RowVersionTriggerFormat = "{0}RowVersionUpdateTrigger";

        public override string ToPostCreateTableStatement(ModelDefinition modelDef)
        {
            if (modelDef.RowVersion != null)
            {
                var triggerName = NamingStrategy.ApplyNameRestrictions(
                    RowVersionTriggerFormat.Fmt(modelDef.ModelName));
                var triggerBody = ":NEW.{0} := :OLD.{0}+1;".Fmt(
                    modelDef.RowVersion.FieldName.SqlColumn(this));

                var sql = "CREATE TRIGGER {0} BEFORE UPDATE ON {1} FOR EACH ROW BEGIN {2} END;".Fmt(
                    Quote(triggerName), NamingStrategy.GetTableName(modelDef), triggerBody);

                return sql;
            }

            return null;
        }


        public override IDbConnection CreateConnection(string connectionString, Dictionary<string, string> options)
        {
            if (options != null)
            {
                connectionString = options.Aggregate(connectionString, (current, option) => current + (option.Key + "=" + option.Value + ";"));
            }

            var connection = _factory.CreateConnection();
            if (connection != null) connection.ConnectionString = connectionString;
            return new OracleConnection(connection);
        }

        public override long GetLastInsertId(IDbCommand dbCmd)
        {
            throw new NotSupportedException();
        }

        public override long InsertAndGetLastInsertId<T>(IDbCommand dbCmd)
        {
            dbCmd.ExecuteScalar();

            var modelDef = GetModel(typeof(T));

            var primaryKey = modelDef.PrimaryKey;
            if (primaryKey == null)
                return 0;

            var identityParameter = (DbParameter)dbCmd.Parameters[this.GetParam(SanitizeFieldNameForParamName(primaryKey.FieldName))];
            if (identityParameter == null)
                return 0;

            return Convert.ToInt64(identityParameter.Value);
        }

        public override void SetDbValue(FieldDefinition fieldDef, IDataReader reader, int colIndex, object instance)
        {
            if (OrmLiteUtils.HandledDbNullValue(fieldDef, reader, colIndex, instance)) return;

            var value = reader.GetValue(colIndex);
            var convertedValue = ConvertDbValue(value, fieldDef.FieldType);

            try
            {
                fieldDef.SetValueFn(instance, convertedValue);
            }
            catch (NullReferenceException ex)
            {
                Log.Warn("Unexpected NullReferenceException", ex);
            }
        }

        public override object ConvertDbValue(object value, Type type)
        {
            if (value == null || value is DBNull) return null;

            if (type == typeof (DateTimeOffset))
            {
                return Convert.ChangeType(value, type);
            }

            if (type == typeof(bool))
                return Convert.ToBoolean(value);

            if (type == typeof(Guid))
            {
                if (CompactGuid)
                {
                    var raw = (byte[])value;
                    return new Guid(raw);
                }
                return new Guid(value.ToString());
            }

            if (type == typeof(byte[]))
                return value;

            if (type == typeof(TimeSpan))
            {
                var ticks = long.Parse(value.ToString());
                return TimeSpan.FromTicks(ticks);
            }

            return base.ConvertDbValue(value, type);
        }

        public override string GetQuotedValue(object value, Type fieldType)
        {
            if (value == null) return "NULL";

            if (fieldType == typeof(Guid))
            {
                var guid = (Guid)value;
                return CompactGuid ? string.Format("CAST('{0}' AS {1})", BitConverter.ToString(guid.ToByteArray()).Replace("-", ""), CompactGuidDefinition)
                                   : string.Format("CAST('{0}' AS {1})", guid, StringGuidDefinition);
            }

            if (fieldType == typeof(DateTime) || fieldType == typeof(DateTime?))
            {
                return GetQuotedDateTimeValue((DateTime) value);
            }

            if (fieldType == typeof(DateTimeOffset) || fieldType == typeof(DateTimeOffset?))
            {
                return GetQuotedDateTimeOffsetValue((DateTimeOffset) value);
            }

            if ((value is TimeSpan) && (fieldType == typeof(Int64) || fieldType == typeof(Int64?)))
            {
                var longValue = ((TimeSpan)value).Ticks;
                return base.GetQuotedValue(longValue, fieldType);
            }

            if (fieldType == typeof(bool?) || fieldType == typeof(bool))
            {
                var boolValue = (bool)value;
                return base.GetQuotedValue(boolValue ? "1" : "0", typeof(string));
            }

            if (fieldType == typeof(decimal?) || fieldType == typeof(decimal) ||
                fieldType == typeof(double?) || fieldType == typeof(double) ||
                fieldType == typeof(float?) || fieldType == typeof(float))
            {
                var s = base.GetQuotedValue(value, fieldType);
                if (s.Length > 20) s = s.Substring(0, 20);
                return "'" + s + "'"; // when quoted exception is more clear!
            }

            if (fieldType.IsEnum)
            {
                if (value is int && !fieldType.IsEnumFlags())
                {
                    value = fieldType.GetEnumName(value);
                }

                var enumValue = StringSerializer.SerializeToString(value);
                // Oracle stores empty strings in varchar columns as null so match that behavior here
                if (enumValue == null)
                    return null;
                enumValue = GetQuotedValue(enumValue.Trim('"'));
                return enumValue == "''"
                    ? "null"
                    : enumValue;
            }

            if (fieldType == typeof(byte[]))
            {
                return "hextoraw('" + BitConverter.ToString((byte[])value).Replace("-", "") + "')";
            }

            return base.GetQuotedValue(value, fieldType);
        }

        public override object GetParamValue(object value, Type fieldType)
        {
            if (!OrmLiteConfig.UseParameterizeSqlExpressions)
                return GetQuotedValue(value, fieldType);

            if (value == null) return DBNull.Value;

            if (fieldType == typeof(Guid))
            {
                var guid = (Guid)value;

                if (CompactGuid)
                    return guid.ToByteArray();

                return guid.ToString();
            }

            if (fieldType == typeof(DateTimeOffset) || fieldType == typeof(DateTimeOffset?))
            {
                return GetQuotedDateTimeOffsetValue((DateTimeOffset)value);
            }

            if ((value is TimeSpan) && (fieldType == typeof(Int64) || fieldType == typeof(Int64?)))
            {
                var longValue = ((TimeSpan)value).Ticks;
                return base.GetQuotedValue(longValue, fieldType);
            }

            if (fieldType == typeof(TimeSpan))
                return ((TimeSpan)value).Ticks;

            if (fieldType == typeof(bool?) || fieldType == typeof(bool))
            {
                var boolValue = (bool)value;
                return boolValue ? 1 : 0;
            }

            if (fieldType.IsEnum)
            {
                if (value is int && !fieldType.IsEnumFlags())
                {
                    value = fieldType.GetEnumName(value);
                }

                var enumValue = StringSerializer.SerializeToString(value);
                // Oracle stores empty strings in varchar columns as null so match that behavior here
                if (enumValue == null)
                    return null;
                enumValue = enumValue.Trim('"');
                return enumValue == ""
                    ? "null"
                    : enumValue;
            }

            if (fieldType == typeof(byte[]))
            {
                return "hextoraw('" + BitConverter.ToString((byte[])value).Replace("-", "") + "')";
            }

            if (fieldType.IsRefType())
            {
                return StringSerializer.SerializeToString(value);
            }

            return value;
        }

        const string IsoDateFormat = "yyyy-MM-dd";
        const string IsoTimeFormat = "HH:mm:ss";
        const string IsoMillisecondFormat = "fffffff";
        const string IsoTimeZoneFormat = "zzz";
        const string OracleDateFormat = "YYYY-MM-DD";
        const string OracleTimeFormat = "HH24:MI:SS";
        const string OracleMillisecondFormat = "FF9";
        const string OracleTimeZoneFormat = "TZH:TZM";

        private string GetQuotedDateTimeOffsetValue(DateTimeOffset dateValue)
        {
            var iso8601Format = string.Format("{0} {1}", GetIsoDateTimeFormat(dateValue.TimeOfDay), IsoTimeZoneFormat);
            var oracleFormat = string.Format("{0} {1}", GetOracleDateTimeFormat(dateValue.TimeOfDay), OracleTimeZoneFormat);
            return string.Format("TO_TIMESTAMP_TZ({0}, {1})", base.GetQuotedValue(dateValue.ToString(iso8601Format), typeof(string)), base.GetQuotedValue(oracleFormat, typeof(string)));
        }

        private string GetQuotedDateTimeValue(DateTime dateValue)
        {
            var iso8601Format = GetIsoDateTimeFormat(dateValue.TimeOfDay);
            var oracleFormat = GetOracleDateTimeFormat(dateValue.TimeOfDay);
            return string.Format("TO_TIMESTAMP({0}, {1})", base.GetQuotedValue(dateValue.ToString(iso8601Format), typeof(string)), base.GetQuotedValue(oracleFormat, typeof(string)));
        }

        private string GetIsoDateTimeFormat(TimeSpan timeOfDay)
        {
            return GetTimeFormat(timeOfDay, IsoDateFormat, IsoTimeFormat, IsoMillisecondFormat);
        }

        private string GetOracleDateTimeFormat(TimeSpan timeOfDay)
        {
            return GetTimeFormat(timeOfDay, OracleDateFormat, OracleTimeFormat, OracleMillisecondFormat);
        }

        private string GetTimeFormat(TimeSpan timeOfDay, string dateFormat, string timeFormat, string millisecondFormat)
        {
            var isStartOfDay = timeOfDay.Ticks == 0;
            if (isStartOfDay) return dateFormat;
            var hasFractionalSeconds = (timeOfDay.Milliseconds != 0) || ((timeOfDay.Ticks % TimeSpan.TicksPerMillisecond) != 0);
            return hasFractionalSeconds 
                ? string.Format("{0} {1}.{2}", dateFormat, timeFormat, millisecondFormat) 
                : string.Format("{0} {1}", dateFormat, timeFormat);
        }

        public override string ToSelectStatement(Type tableType, string sqlFilter, params object[] filterParams)
        {
            var sql = new StringBuilder();
            const string selectStatement = "SELECT ";
            var modelDef = GetModel(tableType);
            var isFullSelectStatement = false;
            if (!string.IsNullOrEmpty(sqlFilter))
            {
                var cleanFilter = sqlFilter.Trim().Replace('\r', ' ').Replace('\n', ' ').ToUpperInvariant();
                isFullSelectStatement = cleanFilter.Length > selectStatement.Length
                    && cleanFilter.Substring(0, selectStatement.Length).Equals(selectStatement);
            }

            if (isFullSelectStatement)
            {
                if (Regex.Matches(sqlFilter.Trim().ToUpperInvariant(), @"(\b|\n)FROM(\b|\n)").Count < 1)
                    sqlFilter += " FROM DUAL";
                return sqlFilter.SqlFmt(filterParams);
            }

            sql.AppendFormat("SELECT {0} FROM {1}",
                             GetColumnNames(modelDef),
                             GetQuotedTableName(modelDef));
            if (!string.IsNullOrEmpty(sqlFilter))
            {
                sqlFilter = sqlFilter.SqlFmt(filterParams);
                if (!sqlFilter.StartsWith("\nORDER ", StringComparison.OrdinalIgnoreCase)
                    && !sqlFilter.StartsWith("\nROWS ", StringComparison.OrdinalIgnoreCase)) // ROWS <m> [TO <n>])
                {
                    sql.Append("\nWHERE ");
                }
                sql.Append(sqlFilter);
            }
            return sql.ToString();
        }

        public override void PrepareParameterizedInsertStatement<T>(IDbCommand dbCommand, ICollection<string> insertFields = null)
        {
            if (insertFields == null)
                insertFields = new List<string>();

            var sbColumnNames = new StringBuilder();
            var sbColumnValues = new StringBuilder();
            var modelDef = GetModel(typeof(T));

            dbCommand.Parameters.Clear();
            dbCommand.CommandTimeout = OrmLiteConfig.CommandTimeout;
            foreach (var fieldDef in modelDef.FieldDefinitions)
            {
                if (fieldDef.IsComputed || fieldDef.IsRowVersion) continue;

                //insertFields contains Property "Name" of fields to insert (that's how expressions work)
                if (insertFields.Count > 0 && !insertFields.Contains(fieldDef.Name)) continue;

                if (sbColumnNames.Length > 0) sbColumnNames.Append(",");
                if (sbColumnValues.Length > 0) sbColumnValues.Append(",");

                try
                {
                    sbColumnNames.Append(GetQuotedColumnName(fieldDef.FieldName));
                    sbColumnValues.Append(this.GetParam(SanitizeFieldNameForParamName(fieldDef.FieldName)));

                    AddParameter(dbCommand, fieldDef);
                }
                catch (Exception ex)
                {
                    Log.Error("ERROR in CreateParameterizedInsertStatement(): " + ex.Message, ex);
                    throw;
                }
            }

            dbCommand.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})",
                GetQuotedTableName(modelDef), sbColumnNames, sbColumnValues);
        }

        public override void SetParameterValues<T>(IDbCommand dbCmd, object obj)
        {
            var modelDef = GetModel(typeof(T));
            var fieldMap = GetFieldDefinitionMap(modelDef);

            foreach (IDataParameter p in dbCmd.Parameters)
            {
                FieldDefinition fieldDef;
                var fieldName = this.ToFieldName(p.ParameterName);
                fieldMap.TryGetValue(fieldName, out fieldDef);

                if (fieldDef == null)
                    throw new ArgumentException("Field Definition '{0}' was not found".Fmt(fieldName));

                if (fieldDef.AutoIncrement || !string.IsNullOrEmpty(fieldDef.Sequence))
                {
                    if (fieldDef.AutoIncrement && string.IsNullOrEmpty(fieldDef.Sequence))
                    {
                        fieldDef.Sequence = Sequence(NamingStrategy.GetTableName(modelDef), fieldDef.FieldName, fieldDef.Sequence);
                    }

                    var pi = typeof(T).GetProperty(fieldDef.Name,
                        BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.FlattenHierarchy);

                    //TODO fix this hack! Because of the way we handle sequences we have to know whether
                    // this is an insert or update/delete. If we did sequences with triggers this would
                    // not be a problem.
                    var sql = dbCmd.CommandText.TrimStart().ToUpperInvariant();
                    bool isInsert = sql.StartsWith("INSERT", StringComparison.InvariantCulture);

                    var result = GetNextValue(dbCmd.Connection, dbCmd.Transaction, fieldDef.Sequence,
                                              pi.GetValue(obj, new object[] { }), isInsert);
                    if (pi.PropertyType == typeof(String))
                        pi.SetProperty(obj, result.ToString());
                    else if (pi.PropertyType == typeof(Int16) || pi.PropertyType == typeof(Int16?))
                        pi.SetProperty(obj, Convert.ToInt16(result));
                    else if (pi.PropertyType == typeof(Int32) || pi.PropertyType == typeof(Int32?))
                        pi.SetProperty(obj, Convert.ToInt32(result));
                    else if (pi.PropertyType == typeof(Guid) || pi.PropertyType == typeof(Guid?))
                        pi.SetProperty(obj, result);
                    else
                        pi.SetProperty(obj, Convert.ToInt64(result));
                }

                SetParameterValue<T>(fieldDef, p, obj);
            }
        }

        public override void SetParameterValue<T>(FieldDefinition fieldDef, IDataParameter p, object obj)
        {
            p.Value = GetValueOrDbNull<T>(fieldDef, obj);
        }

        protected override object GetValue<T>(FieldDefinition fieldDef, object obj)
        {
            var value = base.GetValue<T>(fieldDef, obj);

            if (value != null)
            {
                if (fieldDef.FieldType == typeof(Guid))
                {
                    var guid = (Guid)value;
                    if (CompactGuid) return guid.ToByteArray();
                    return guid.ToString();
                }

                if (fieldDef.FieldType == typeof(DateTimeOffset))
                {
                    var timestamp = (DateTimeOffset)value;
                    return _timestampConverter.ConvertToOracleTimeStampTz(timestamp);
                }
            }
            return value;
        }

        public override string ToInsertRowStatement(IDbCommand dbCommand, object objWithProperties, ICollection<string> insertFields = null)
        {
            if (insertFields == null)
                insertFields = new List<string>();

            var sbColumnNames = new StringBuilder();
            var sbColumnValues = new StringBuilder();

            var tableType = objWithProperties.GetType();
            var modelDef = GetModel(tableType);

            foreach (var fieldDef in modelDef.FieldDefinitions)
            {
                if (fieldDef.IsComputed) continue;
                if (insertFields.Count > 0 && !insertFields.Contains(fieldDef.Name)) continue;

                if ((fieldDef.AutoIncrement || !string.IsNullOrEmpty(fieldDef.Sequence))
                    && dbCommand != null)
                {
                    if (fieldDef.AutoIncrement && string.IsNullOrEmpty(fieldDef.Sequence))
                    {
                        fieldDef.Sequence = Sequence(NamingStrategy.GetTableName(modelDef), fieldDef.FieldName, fieldDef.Sequence);
                    }

                    var pi = tableType.GetProperty(fieldDef.Name,
                        BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.FlattenHierarchy);

                    var result = GetNextValue(dbCommand.Connection, dbCommand.Transaction, fieldDef.Sequence,
                                              pi.GetValue(objWithProperties, new object[] { }), isInsert: true);
                    if (pi.PropertyType == typeof(String))
                        pi.SetProperty(objWithProperties, result.ToString());
                    else if (pi.PropertyType == typeof(Int16))
                        pi.SetProperty(objWithProperties, Convert.ToInt16(result));
                    else if (pi.PropertyType == typeof(Int32))
                        pi.SetProperty(objWithProperties, Convert.ToInt32(result));
                    else if (pi.PropertyType == typeof(Guid))
                        pi.SetProperty(objWithProperties, result);
                    else
                        pi.SetProperty(objWithProperties, Convert.ToInt64(result));
                }

                if (sbColumnNames.Length > 0) sbColumnNames.Append(",");
                if (sbColumnValues.Length > 0) sbColumnValues.Append(",");

                try
                {
                    sbColumnNames.Append(string.Format("{0}", GetQuotedColumnName(fieldDef.FieldName)));
                    if (!string.IsNullOrEmpty(fieldDef.Sequence) && dbCommand == null)
                        sbColumnValues.Append(string.Format(":{0}", fieldDef.Name));
                    else
                        sbColumnValues.Append(fieldDef.GetQuotedValue(objWithProperties));
                }
                catch (Exception ex)
                {
                    Log.ErrorFormat("Error in ToInsertRowStatement on column {0}: {1}", fieldDef.FieldName, ex);
                    throw;
                }
            }

            var sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2}) ",
                                    GetQuotedTableName(modelDef), sbColumnNames, sbColumnValues);

            return sql;
        }

        public override string ToUpdateRowStatement(object objWithProperties, ICollection<string> updateFields = null)
        {
            var sqlFilter = new StringBuilder();
            var sql = new StringBuilder();
            var tableType = objWithProperties.GetType();
            var modelDef = GetModel(tableType);

            foreach (var fieldDef in modelDef.FieldDefinitions)
            {
                if (fieldDef.IsComputed) continue;

                var updateFieldsEmptyOrNull = updateFields == null || updateFields.Count == 0;
                if ((fieldDef.IsPrimaryKey || fieldDef.Name == OrmLiteConfig.IdField)
                    && updateFieldsEmptyOrNull)
                {
                    if (sqlFilter.Length > 0) sqlFilter.Append(" AND ");

                    sqlFilter.AppendFormat("{0} = {1}",
                        GetQuotedColumnName(fieldDef.FieldName),
                        fieldDef.GetQuotedValue(objWithProperties));

                    continue;
                }
                if (!updateFieldsEmptyOrNull && !updateFields.Contains(fieldDef.Name)) continue;
                if (sql.Length > 0) sql.Append(",");
                sql.AppendFormat("{0}={1}",
                    GetQuotedColumnName(fieldDef.FieldName),
                    fieldDef.GetQuotedValue(objWithProperties));
            }

            var updateSql = string.Format("UPDATE {0} \nSET {1} {2}",
                GetQuotedTableName(modelDef), sql, (sqlFilter.Length > 0 ? "\nWHERE " + sqlFilter : ""));

            return updateSql;
        }

        public override string ToDeleteRowStatement(object objWithProperties)
        {
            var tableType = objWithProperties.GetType();
            var modelDef = GetModel(tableType);

            var sqlFilter = new StringBuilder();

            foreach (var fieldDef in modelDef.FieldDefinitions)
            {
                if (fieldDef.IsPrimaryKey || fieldDef.Name == OrmLiteConfig.IdField)
                {
                    if (sqlFilter.Length > 0) sqlFilter.Append(" AND ");
                    sqlFilter.AppendFormat("{0} = {1}",
                        GetQuotedColumnName(fieldDef.FieldName),
                        fieldDef.GetQuotedValue(objWithProperties));
                }
            }

            var deleteSql = string.Format("DELETE FROM {0} WHERE {1}",
                GetQuotedTableName(modelDef), sqlFilter);

            return deleteSql;
        }

        public override string ToCreateTableStatement(Type tableType)
        {
            var sbColumns = new StringBuilder();
            var sbConstraints = new StringBuilder();
            var sbPk = new StringBuilder();

            var modelDef = GetModel(tableType);
            foreach (var fieldDef in modelDef.FieldDefinitions)
            {
                if (fieldDef.IsPrimaryKey)
                {
                    sbPk.AppendFormat(sbPk.Length != 0 ? ",{0}" : "{0}", GetQuotedColumnName(fieldDef.FieldName));
                }

                if (sbColumns.Length != 0) sbColumns.Append(", \n  ");

                var columnDefinition = GetColumnDefinition(
                    fieldDef.FieldName,
                    fieldDef.ColumnType,
                    fieldDef.IsPrimaryKey,
                    fieldDef.AutoIncrement,
                    fieldDef.IsNullable,
                    fieldDef.IsRowVersion,
                    fieldDef.FieldLength,
                    fieldDef.Scale,
                    fieldDef.DefaultValue,
                    fieldDef.CustomFieldDefinition);

                sbColumns.Append(columnDefinition);

                if (fieldDef.ForeignKey == null) continue;

                var refModelDef = GetModel(fieldDef.ForeignKey.ReferenceType);
                sbConstraints.AppendFormat(
                    ", \n\n  CONSTRAINT {0} FOREIGN KEY ({1}) REFERENCES {2} ({3})",
                    GetQuotedName(fieldDef.ForeignKey.GetForeignKeyName(modelDef, refModelDef, NamingStrategy, fieldDef)),
                    GetQuotedColumnName(fieldDef.FieldName),
                    GetQuotedTableName(refModelDef),
                    GetQuotedColumnName(refModelDef.PrimaryKey.FieldName));

                sbConstraints.Append(GetForeignKeyOnDeleteClause(fieldDef.ForeignKey));
            }

            if (sbPk.Length != 0) sbColumns.AppendFormat(", \n  PRIMARY KEY({0})", sbPk);

            var sql = new StringBuilder(string.Format(
                "CREATE TABLE {0} \n(\n  {1}{2} \n) \n", GetQuotedTableName(modelDef), sbColumns, sbConstraints));

            return sql.ToString();
        }

        public override string GetForeignKeyOnDeleteClause(ForeignKeyConstraint foreignKey)
        {
            if (string.IsNullOrEmpty(foreignKey.OnDelete)) return string.Empty;
            var onDelete = foreignKey.OnDelete.ToUpperInvariant();
            return (onDelete == "SET NULL" || onDelete == "CASCADE") ? " ON DELETE " + onDelete : string.Empty;
        }

        public override string GetLoadChildrenSubSelect<From>(SqlExpression<From> expr)
        {
            if (!expr.OrderByExpression.IsNullOrEmpty() && expr.Rows == null)
            {
                var modelDef = expr.ModelDef;
                expr.Select(this.GetQuotedColumnName(modelDef, modelDef.PrimaryKey))
                    .ClearLimits()
                    .OrderBy(""); //Invalid in Sub Selects

                var subSql = expr.ToSelectStatement();

                return subSql;
            }

            return base.GetLoadChildrenSubSelect(expr);
        }

        public override string ToCreateSequenceStatement(Type tableType, string sequenceName)
        {
            var result = "";
            var modelDef = GetModel(tableType);

            foreach (var fieldDef in modelDef.FieldDefinitions)
            {
                if (fieldDef.AutoIncrement || !fieldDef.Sequence.IsNullOrEmpty())
                {
                    var seqName = Sequence(NamingStrategy.GetTableName(modelDef), fieldDef.FieldName, fieldDef.Sequence);
                    if (seqName.EqualsIgnoreCase(sequenceName))
                    {
                        result = "CREATE SEQUENCE " + GetQuotedName(seqName);
                        break;
                    }
                }
            }
            return result;
        }

        public override List<string> ToCreateSequenceStatements(Type tableType)
        {
            return SequenceList(tableType).Select(seq => "CREATE SEQUENCE " + GetQuotedName(seq)).ToList();
        }

        public override List<string> SequenceList(Type tableType)
        {
            var gens = new List<string>();
            var modelDef = GetModel(tableType);

            foreach (var fieldDef in modelDef.FieldDefinitions)
            {
                if (fieldDef.AutoIncrement || !fieldDef.Sequence.IsNullOrEmpty())
                {
                    var seqName = Sequence(NamingStrategy.GetTableName(modelDef), fieldDef.FieldName, fieldDef.Sequence);

                    if (gens.IndexOf(seqName) == -1)
                        gens.Add(seqName);
                }
            }
            return gens;
        }

        public override string GetColumnDefinition(string fieldName, Type fieldType,
            bool isPrimaryKey, bool autoIncrement, bool isNullable, bool isRowVersion,
            int? fieldLength, int? scale, string defaultValue, string customFieldDefinition)
        {
            string fieldDefinition;

            if (customFieldDefinition != null)
            {
                fieldDefinition = customFieldDefinition;
            }
            else if (fieldType == typeof(string))
            {
                var length = fieldLength.GetValueOrDefault(DefaultStringLength);
                length = Math.Min(length, MaxStringColumnLength);
                fieldDefinition = string.Format(StringLengthColumnDefinitionFormat, length);
            }
            else if (fieldType == typeof(Decimal))
            {
                fieldDefinition = string.Format("{0} ({1},{2})", DecimalColumnDefinition,
                    fieldLength.GetValueOrDefault(DefaultDecimalPrecision),
                    scale.GetValueOrDefault(DefaultDecimalScale));
            }
            else
            {
                fieldDefinition = GetColumnTypeDefinition(fieldType);
            }

            var sql = new StringBuilder();
            sql.AppendFormat("{0} {1}", GetQuotedColumnName(fieldName), fieldDefinition);

            if (isRowVersion)
            {
                sql.AppendFormat(DefaultValueFormat, 1L);
            }
            else if (!string.IsNullOrEmpty(defaultValue))
            {
                sql.AppendFormat(DefaultValueFormat, defaultValue);
            }

            sql.Append(isNullable ? " NULL" : " NOT NULL");

            var definition = sql.ToString();

            if (fieldType == typeof(Decimal))
                return base.ReplaceDecimalColumnDefinition(definition, fieldLength, scale);

            return definition;
        }


        public override List<string> ToCreateIndexStatements(Type tableType)
        {
            var sqlIndexes = new List<string>();

            var modelDef = GetModel(tableType);
            foreach (var fieldDef in modelDef.FieldDefinitions)
            {
                if (!fieldDef.IsIndexed) continue;

                var indexName = GetIndexName(
                    fieldDef.IsUnique,
                    (modelDef.IsInSchema
                        ? modelDef.Schema + "_" + modelDef.ModelName
                        : modelDef.ModelName).SafeVarName(),
                    fieldDef.FieldName);
                indexName = NamingStrategy.ApplyNameRestrictions(indexName);

                sqlIndexes.Add(
                    ToCreateIndexStatement(fieldDef.IsUnique, indexName, modelDef, fieldDef.FieldName));
            }

            foreach (var compositeIndex in modelDef.CompositeIndexes)
            {
                var indexName = GetCompositeIndexNameWithSchema(compositeIndex, modelDef);
                indexName = NamingStrategy.ApplyNameRestrictions(indexName);
                var indexNames = string.Join(",", compositeIndex.FieldNames.ToArray());

                sqlIndexes.Add(
                    ToCreateIndexStatement(compositeIndex.Unique, indexName, modelDef, indexNames));
            }

            return sqlIndexes;
        }

        protected override string ToCreateIndexStatement(bool isUnique, string indexName, ModelDefinition modelDef, string fieldName,
            bool isCombined = false, FieldDefinition fieldDef = null)
        {
            return string.Format("CREATE {0} INDEX {1} ON {2} ({3} ) \n",
                isUnique ? "UNIQUE" : "",
                indexName,
                GetQuotedTableName(modelDef),
                GetQuotedColumnName(fieldName));
        }


        public override string ToExistStatement(Type fromTableType,
            object objWithProperties,
            string sqlFilter,
            params object[] filterParams)
        {

            var fromModelDef = GetModel(fromTableType);
            var sql = new StringBuilder();
            sql.AppendFormat("SELECT 1 FROM {0}", GetQuotedTableName(fromModelDef));

            var filter = new StringBuilder();

            if (objWithProperties != null)
            {
                var tableType = objWithProperties.GetType();

                if (fromTableType != tableType)
                {
                    var i = 0;
                    var modelDef = GetModel(tableType);

                    var fpk = modelDef.FieldDefinitions.Where(def => def.IsPrimaryKey).ToList();

                    foreach (var fieldDef in fromModelDef.FieldDefinitions)
                    {
                        if (fieldDef.IsComputed) continue;
                        if (fieldDef.ForeignKey != null
                            && GetModel(fieldDef.ForeignKey.ReferenceType).ModelName == modelDef.ModelName)
                        {
                            if (filter.Length > 0) filter.Append(" AND ");
                            filter.AppendFormat("{0} = {1}", GetQuotedColumnName(fieldDef.FieldName),
                                fpk[i].GetQuotedValue(objWithProperties));
                            i++;
                        }
                    }
                }
                else
                {
                    var modelDef = GetModel(tableType);
                    foreach (var fieldDef in modelDef.FieldDefinitions)
                    {
                        if (fieldDef.IsComputed) continue;
                        if (fieldDef.IsPrimaryKey)
                        {
                            if (filter.Length > 0) filter.Append(" AND ");
                            filter.AppendFormat("{0} = {1}",
                                GetQuotedColumnName(fieldDef.FieldName),
                                fieldDef.GetQuotedValue(objWithProperties));
                        }
                    }
                }

                if (filter.Length > 0) sql.AppendFormat("\nWHERE {0} ", filter);
            }

            if (!string.IsNullOrEmpty(sqlFilter))
            {
                sqlFilter = sqlFilter.SqlFmt(filterParams);
                if (!sqlFilter.StartsWith("\nORDER ", StringComparison.OrdinalIgnoreCase)
                    && !sqlFilter.StartsWith("\nROWS ", StringComparison.OrdinalIgnoreCase)) // ROWS <m> [TO <n>])
                {
                    sql.Append(filter.Length > 0 ? " AND  " : "\nWHERE ");
                }
                sql.Append(sqlFilter);
            }

            var sb = new StringBuilder("select 1  from dual where");
            sb.AppendFormat(" exists ({0})", sql);
            return sb.ToString();
        }

        public override string ToSelectFromProcedureStatement(
            object fromObjWithProperties,
            Type outputModelType,
            string sqlFilter,
            params object[] filterParams)
        {

            var sbColumnValues = new StringBuilder();

            Type fromTableType = fromObjWithProperties.GetType();

            var modelDef = GetModel(fromTableType);

            foreach (var fieldDef in modelDef.FieldDefinitions)
            {
                if (sbColumnValues.Length > 0) sbColumnValues.Append(",");

                sbColumnValues.Append(fieldDef.GetQuotedValue(fromObjWithProperties));
            }

            var sql = new StringBuilder();
            sql.AppendFormat("SELECT {0} FROM  {1} {2}{3}{4}  \n",
                GetColumnNames(GetModel(outputModelType)),
                GetQuotedTableName(modelDef),
                sbColumnValues.Length > 0 ? "(" : "",
                sbColumnValues,
                sbColumnValues.Length > 0 ? ")" : "");

            if (!string.IsNullOrEmpty(sqlFilter))
            {
                sqlFilter = sqlFilter.SqlFmt(filterParams);