Current VistaDB.5 OrmLite v4 dialect

OrmLiteVistaDbCommand.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using VistaDB.Provider;

namespace ServiceStack.OrmLite.VistaDB
{
    public class OrmLiteVistaDbCommand : IDbCommand
    {
        public VistaDBCommand VistaDbCommand { get; private set; }

        private OrmLiteVistaDbConnection _connectionWrapper;
        private OrmLiteVistaDbTransaction _transactionWrapper;

        public OrmLiteVistaDbCommand(OrmLiteVistaDbConnection connectionWrapper, VistaDBCommand vistaDbCommand)
        {
            if (connectionWrapper == null)
                throw new ArgumentNullException("connectionWrapper");

            if (vistaDbCommand == null)
                throw new ArgumentNullException("vistaDbCommand");

            _connectionWrapper = connectionWrapper;

            this.VistaDbCommand = vistaDbCommand;
        }

        #region IDbCommand Members

        public void Cancel()
        {
            this.VistaDbCommand.Cancel();
        }

        public string CommandText
        {
            get { return this.VistaDbCommand.CommandText; }
            set { this.VistaDbCommand.CommandText = value; }
        }

        public int CommandTimeout
        {
            get { return this.VistaDbCommand.CommandTimeout; }
            set { this.VistaDbCommand.CommandTimeout = value; }
        }

        public CommandType CommandType
        {
            get { return this.VistaDbCommand.CommandType; }
            set { this.VistaDbCommand.CommandType = value; }
        }

        public IDbConnection Connection
        {
            get { return _connectionWrapper; }
            set
            {
                _connectionWrapper = (OrmLiteVistaDbConnection)value;
                this.VistaDbCommand.Connection = _connectionWrapper.VistaDbConnection;
            }
        }

        public IDbDataParameter CreateParameter()
        {
            return this.VistaDbCommand.CreateParameter();
        }

        public int ExecuteNonQuery()
        {
            return this.VistaDbCommand.ExecuteNonQuery();
        }

        public IDataReader ExecuteReader(CommandBehavior behavior)
        {
            return this.VistaDbCommand.ExecuteReader(behavior);
        }

        public IDataReader ExecuteReader()
        {
            return this.VistaDbCommand.ExecuteReader();
        }

        public object ExecuteScalar()
        {
            return this.VistaDbCommand.ExecuteScalar();
        }

        public IDataParameterCollection Parameters { get { return this.VistaDbCommand.Parameters; } }

        public void Prepare()
        {
            this.VistaDbCommand.Prepare();
        }

        public IDbTransaction Transaction
        {
            get
            {
                return _transactionWrapper;
            }
            set
            {
                _transactionWrapper = (OrmLiteVistaDbTransaction)value;
                
                if (_transactionWrapper != null)
                    this.VistaDbCommand.Transaction = _transactionWrapper.VistaDbTransaction;
            }
        }

        public UpdateRowSource UpdatedRowSource
        {
            get { return this.VistaDbCommand.UpdatedRowSource; }
            set { this.VistaDbCommand.UpdatedRowSource = value; }
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            this.VistaDbCommand.Dispose();
        }

        #endregion
    }
}

OrmLiteVistaDbConnection.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using VistaDB.Provider;

namespace ServiceStack.OrmLite.VistaDB
{
    public class OrmLiteVistaDbConnection : IDbConnection, ICloneable
    {
        public VistaDBConnection VistaDbConnection { get; private set; }

        public OrmLiteVistaDbConnection(VistaDBConnection conn)
        {
            if (conn == null)
                throw new ArgumentNullException("conn");

            VistaDbConnection = conn;
        }

        #region IDbConnection Members

        public IDbTransaction BeginTransaction(IsolationLevel il)
        {
            //VistaDB 4 supports only ReadCommited isolation level
            if (il != IsolationLevel.Unspecified && il != IsolationLevel.ReadCommitted)
                il = IsolationLevel.ReadCommitted;

            var tn = VistaDbConnection.BeginTransaction(il);
            return new OrmLiteVistaDbTransaction(this, tn);
        }

        public IDbTransaction BeginTransaction()
        {
            var tn = this.VistaDbConnection.BeginTransaction();

            return new OrmLiteVistaDbTransaction(this, tn);
        }

        public void ChangeDatabase(string databaseName)
        {
            VistaDbConnection.ChangeDatabase(databaseName);
        }

        public void Close()
        {
            VistaDbConnection.Close();
        }

        public string ConnectionString
        {
            get { return VistaDbConnection.ConnectionString; }
            set { VistaDbConnection.ConnectionString = value; }
        }

        public int ConnectionTimeout
        {
            get { return VistaDbConnection.ConnectionTimeout; }
        }

        public IDbCommand CreateCommand()
        {
            var cmd = VistaDbConnection.CreateCommand();

            return new OrmLiteVistaDbCommand(this, cmd);
        }

        public string Database
        {
            get { return VistaDbConnection.Database; }
        }

        public void Open()
        {
            VistaDbConnection.Open();
        }

        public ConnectionState State
        {
            get { return VistaDbConnection.State; }
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            if (State == ConnectionState.Open)
            {
                Close();
            }

            VistaDbConnection.Dispose();
        }

        #endregion

        #region ICloneable Members

        public object Clone()
        {
            var cloneable = (ICloneable) VistaDbConnection;
            var conn = (VistaDBConnection) cloneable.Clone();

            return new OrmLiteVistaDbConnection(conn);
        }

        #endregion
    }
}

OrmLiteVistaDbTransaction.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using VistaDB.Provider;

namespace ServiceStack.OrmLite.VistaDB
{
    public class OrmLiteVistaDbTransaction : IDbTransaction
    {
        public VistaDBTransaction VistaDbTransaction { get; private set; }

        private bool _finalized, _disposed;

        public OrmLiteVistaDbTransaction(OrmLiteVistaDbConnection connectionWrapper, VistaDBTransaction transaction)
        {
            if (connectionWrapper == null)
                throw new ArgumentNullException("connectionWrapper");

            if (transaction == null)
                throw new ArgumentNullException("transaction");

            _finalized = _disposed = false;

            this.Connection = connectionWrapper;
            this.VistaDbTransaction = transaction;
        }

        #region IDbTransaction Members

        public void Commit()
        {
            this.VistaDbTransaction.Commit();
            _finalized = true;
        }

        public IDbConnection Connection { get; private set; }

        public IsolationLevel IsolationLevel { get { return this.VistaDbTransaction.IsolationLevel; } }

        public void Rollback()
        {
            this.VistaDbTransaction.Rollback();
            _finalized = true;
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {            
            if (!_finalized && !_disposed)
            {
                try
                {
                    this.Rollback();
                    this.Connection = null;
                    _disposed = true;
                }
                catch { }
            }

            this.VistaDbTransaction.Dispose();
        }

        #endregion
    }
}

VistaDBDialect.cs

using ServiceStack.OrmLite.VistaDB;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ServiceStack.OrmLite
{
    public static class VistaDBDialect
    {
        public static IOrmLiteDialectProvider Provider { get { return VistaDBOrmLiteDialectProvider.Instance; } }
    }
}

VistaDBExpression.cs

namespace ServiceStack.OrmLite.VistaDB
{
    using ServiceStack.OrmLite.SqlServer;

    public class VistaDBExpression<T> : SqlServerExpression<T>
	{
	}
}

VistaDBOrmLiteDialectProvider.cs

namespace ServiceStack.OrmLite.VistaDB
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Globalization;
    using System.IO;
    using System.Text;

    using ServiceStack.Text;

    using global::VistaDB.Provider;

    public class VistaDBOrmLiteDialectProvider : OrmLiteDialectProviderBase<VistaDBOrmLiteDialectProvider>
    {
        public static VistaDBOrmLiteDialectProvider Instance = new VistaDBOrmLiteDialectProvider();

        public VistaDBOrmLiteDialectProvider()
        {
            base.AutoIncrementDefinition = "IDENTITY(1,1) NOT NULL";
            StringColumnDefinition = UseUnicode ? "NVARCHAR(4000)" : "VARCHAR(8000)";
            base.GuidColumnDefinition = "UniqueIdentifier";
            base.RealColumnDefinition = "FLOAT";
            base.BoolColumnDefinition = "BIT";
            base.DecimalColumnDefinition = "DECIMAL(38,6)";
            base.TimeColumnDefinition = "BIGINT"; //TIME"; //SQLSERVER 2008+
            base.IntColumnDefinition = "INT";
            base.BlobColumnDefinition = "VARBINARY"; //(MAX)";
            base.SelectIdentitySql = "SELECT @@IDENTITY";

            base.InitColumnTypeMap();
        }

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

            DbTypeMap.Set<TimeSpan>(DbType.DateTime, TimeColumnDefinition);
            DbTypeMap.Set<TimeSpan?>(DbType.DateTime, TimeColumnDefinition);
        }

        public override string GetQuotedValue(string paramValue)
        {
            return (UseUnicode ? "N'" : "'") + paramValue.Replace("'", "''") + "'";
        }

        public override IDbConnection CreateConnection(string connectionString, Dictionary<string, string> options)
        {
            var isFullConnectionString = connectionString.Contains(";");

            if (!isFullConnectionString)
            {
                var filePath = connectionString;
                var filePathWithExt = filePath.ToLower().EndsWith(".vdb5")
                    ? filePath
                    : filePath + ".vdb5";

                var fileName = Path.GetFileName(filePathWithExt);

                connectionString = string.Format(@"Data Source={0};Open Mode=NonExclusiveReadWrite;", filePathWithExt);
            }

            if (options != null)
            {
                foreach (var option in options)
                    connectionString += option.Key + "=" + option.Value + ";";
            }

            return new OrmLiteVistaDbConnection(
                new VistaDBConnection(connectionString));
        }

        public override string ToCreateTableStatement(Type tableType)
        {
            var modelDefinition = GetModelDefinition(tableType);
            var quotedTableName = this.GetQuotedTableName(modelDefinition);

            var columns = new StringBuilder();
            var constraints = new StringBuilder();

            foreach (var fd in modelDefinition.FieldDefinitions)
            {
                if (columns.Length != 0)
                    columns.Append(", \n  ");

                var columnDefinition = this.GetColumnDefinition(
                    fd.FieldName, fd.FieldType, false, fd.AutoIncrement, fd.IsNullable, fd.FieldLength, null, fd.DefaultValue, fd.CustomFieldDefinition);

                columns.Append(columnDefinition);

                if (fd.IsPrimaryKey)
                {
                    constraints.AppendFormat("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY ({2});\n",
                        quotedTableName,
                        this.GetQuotedName("PK_" + modelDefinition.ModelName),
                        this.GetQuotedColumnName(fd.FieldName));
                }
                else if (fd.ForeignKey != null)
                {
                    var foreignModelDefinition = GetModelDefinition(fd.ForeignKey.ReferenceType);
                    constraints.AppendFormat("ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3} ({4}){5}{6};\n",
                        quotedTableName,
                        this.GetQuotedName(fd.ForeignKey.GetForeignKeyName(modelDefinition, foreignModelDefinition, this.NamingStrategy, fd)),
                        this.GetQuotedColumnName(fd.FieldName),
                        this.GetQuotedTableName(foreignModelDefinition),
                        this.GetQuotedColumnName(foreignModelDefinition.PrimaryKey.FieldName),
                        this.GetForeignKeyOnDeleteClause(fd.ForeignKey),
                        this.GetForeignKeyOnUpdateClause(fd.ForeignKey));
                }
            }

            return String.Format("CREATE TABLE {0} \n(\n  {1} \n); \n {2}\n",
                quotedTableName,
                columns,
                constraints);
        }

        public override string GetColumnDefinition(string fieldName, Type fieldType, bool isPrimaryKey, bool autoIncrement, bool isNullable,
            int? fieldLength, int? scale, string defaultValue, string customFieldDefinition)
        {
            string fieldDefinition;
            if (customFieldDefinition != null)
                fieldDefinition = customFieldDefinition;
            else if (fieldType == typeof(string))
                fieldDefinition = string.Format(this.StringLengthColumnDefinitionFormat, fieldLength.GetValueOrDefault(this.DefaultStringLength));
            else if (!this.DbTypeMap.ColumnTypeMap.TryGetValue(fieldType, out fieldDefinition))
                fieldDefinition = this.GetUndefinedColumnDefinition(fieldType, fieldLength);
            
            var sql = new StringBuilder();
            sql.AppendFormat("{0} {1}", this.GetQuotedColumnName(fieldName), fieldDefinition);
            if (isPrimaryKey)
            {
                sql.Append(" PRIMARY KEY");                
            }
            else
            {
                if (isNullable && !autoIncrement)
                    sql.Append(" NULL");
                else
                    sql.Append(" NOT NULL");
            }

            if (autoIncrement)
                sql.Append(" ").Append(this.AutoIncrementDefinition);

            if (!String.IsNullOrEmpty(defaultValue))
                sql.AppendFormat(this.DefaultValueFormat, defaultValue);
            
            return sql.ToString();
        }

        public override string GetQuotedTableName(ModelDefinition modelDef)
        {
            if (!modelDef.IsInSchema)
                return base.GetQuotedTableName(modelDef);

            var escapedSchema = modelDef.Schema.Replace(".", "\".\"");
            return string.Format("\"{0}\".\"{1}\"", escapedSchema, NamingStrategy.GetTableName(modelDef.ModelName));
        }

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

        //    if (type == typeof(bool) && !(value is bool))
        //    {
        //        var intVal = Convert.ToInt32(value.ToString());
        //        return intVal != 0;
        //    }

        //    if (type == typeof(TimeSpan) && value is DateTime)
        //    {
        //        var dateTimeValue = (DateTime)value;
        //        return dateTimeValue - timeSpanOffset;
        //    }

        //    if (this._ensureUtc && type == typeof(DateTime))
        //    {
        //        var result = base.ConvertDbValue(value, type);
        //        if (result is DateTime)
        //            return DateTime.SpecifyKind((DateTime)result, DateTimeKind.Utc);
        //        return result;
        //    }

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

        //    return base.ConvertDbValue(value, type);
        //}

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

            if (fieldType == typeof(Guid))
            {
                var guidValue = (Guid)value;
                return string.Format("CAST('{0}' AS UNIQUEIDENTIFIER)", guidValue);
            }
            if (fieldType == typeof(DateTime))
            {
                var dateValue = (DateTime)value;
                if (_ensureUtc && dateValue.Kind == DateTimeKind.Local)
                    dateValue = dateValue.ToUniversalTime();
                const string iso8601Format = "yyyy-MM-dd HH:mm:ss.fff";
                return base.GetQuotedValue(dateValue.ToString(iso8601Format, CultureInfo.InvariantCulture), typeof(string));
            }
            if (fieldType == typeof(DateTimeOffset))
            {
                var dateValue = (DateTimeOffset)value;
                const string iso8601Format = "yyyy-MM-dd HH:mm:ss.fff zzz";
                return base.GetQuotedValue(dateValue.ToString(iso8601Format, CultureInfo.InvariantCulture), typeof(string));
            }
            if (fieldType == typeof(bool))
            {
                var boolValue = (bool)value;
                return base.GetQuotedValue(boolValue ? 1 : 0, typeof(int));
            }
            if (fieldType == typeof(string))
            {
                return GetQuotedValue(value.ToString());
            }

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

            return base.GetQuotedValue(value, fieldType);
        }

        protected override string GetUndefinedColumnDefinition(Type fieldType, int? fieldLength)
        {
            if (TypeSerializer.CanCreateFromString(fieldType))
            {
                return string.Format(StringLengthColumnDefinitionFormat, fieldLength.HasValue ? fieldLength.Value.ToString() : "MAX");
            }
            return base.GetUndefinedColumnDefinition(fieldType, fieldLength);
        }

        protected bool _useDateTime2;
        public void UseDatetime2(bool shouldUseDatetime2)
        {
            _useDateTime2 = shouldUseDatetime2;
            DateTimeColumnDefinition = shouldUseDatetime2 ? "datetime2" : "datetime";
            base.DbTypeMap.Set<DateTime>(shouldUseDatetime2 ? DbType.DateTime2 : DbType.DateTime, DateTimeColumnDefinition);
            base.DbTypeMap.Set<DateTime?>(shouldUseDatetime2 ? DbType.DateTime2 : DbType.DateTime, DateTimeColumnDefinition);
        }

        protected bool _ensureUtc;
        public void EnsureUtc(bool shouldEnsureUtc)
        {
            _ensureUtc = shouldEnsureUtc;
        }

        public override SqlExpression<T> SqlExpression<T>()
        {
            return new VistaDBExpression<T>();
        }

        public override bool DoesTableExist(IDbCommand dbCmd, string tableName)
        {
            var sql = "SELECT COUNT(*) FROM [database schema] WHERE typeid = 1 AND name = {0}"
                .SqlFmt(tableName);

            dbCmd.CommandText = sql;
            var result = dbCmd.LongScalar();

            return result > 0;
        }

        public override bool UseUnicode
        {
            get
            {
                return useUnicode;
            }
            set
            {
                useUnicode = value;
                if (useUnicode && this.DefaultStringLength > 4000)
                {
                    this.DefaultStringLength = 4000;
                }

                // UpdateStringColumnDefinitions(); is called by changing DefaultStringLength 
            }
        }

        public override string GetForeignKeyOnDeleteClause(OrmLite.ForeignKeyConstraint foreignKey)
        {
            return "RESTRICT" == (foreignKey.OnDelete ?? "").ToUpper()
                ? ""
                : base.GetForeignKeyOnDeleteClause(foreignKey);
        }

        public override string GetForeignKeyOnUpdateClause(OrmLite.ForeignKeyConstraint foreignKey)
        {
            return "RESTRICT" == (foreignKey.OnDelete ?? "").ToUpper()
                ? ""
                : base.GetForeignKeyOnUpdateClause(foreignKey);
        }

        public override string GetDropForeignKeyConstraints(ModelDefinition modelDef)
        {
            //TODO: find out if this should go in base class?

            var sb = new StringBuilder();
            foreach (var fieldDef in modelDef.FieldDefinitions)
            {
                if (fieldDef.ForeignKey != null)
                {
                    var foreignKeyName = fieldDef.ForeignKey.GetForeignKeyName(
                        modelDef,
                        GetModelDefinition(fieldDef.ForeignKey.ReferenceType),
                        NamingStrategy,
                        fieldDef);

                    var tableName = GetQuotedTableName(modelDef);
                    sb.AppendFormat("IF EXISTS (SELECT name FROM [database schema] WHERE typeid = 7 AND name = '{0}')\n", foreignKeyName);
                    sb.AppendLine("BEGIN");
                    sb.AppendLine("  ALTER TABLE {0} DROP {1};".Fmt(tableName, foreignKeyName));
                    sb.AppendLine("END");
                }
            }

            return sb.ToString();
        }

        public override string ToAddColumnStatement(Type modelType, FieldDefinition fieldDef)
        {
            var column = GetColumnDefinition(fieldDef.FieldName,
                                             fieldDef.FieldType,
                                             fieldDef.IsPrimaryKey,
                                             fieldDef.AutoIncrement,
                                             fieldDef.IsNullable,
                                             fieldDef.FieldLength,
                                             fieldDef.Scale,
                                             fieldDef.DefaultValue,
                                             fieldDef.CustomFieldDefinition);

            return string.Format("ALTER TABLE {0} ADD {1};",
                                 GetQuotedTableName(GetModel(modelType).ModelName),
                                 column);
        }

        public override string ToAlterColumnStatement(Type modelType, FieldDefinition fieldDef)
        {
            var column = GetColumnDefinition(fieldDef.FieldName,
                                             fieldDef.FieldType,
                                             fieldDef.IsPrimaryKey,
                                             fieldDef.AutoIncrement,
                                             fieldDef.IsNullable,
                                             fieldDef.FieldLength,
                                             fieldDef.Scale,
                                             fieldDef.DefaultValue,
                                             fieldDef.CustomFieldDefinition);

            return string.Format("ALTER TABLE {0} ALTER COLUMN {1};",
                                 GetQuotedTableName(GetModel(modelType).ModelName),
                                 column);
        }

        public override string ToChangeColumnNameStatement(Type modelType, FieldDefinition fieldDef, string oldColumnName)
        {
            var objectName = string.Format("{0}.{1}",
                NamingStrategy.GetTableName(GetModel(modelType).ModelName),
                oldColumnName);

            return string.Format("EXEC sp_rename {0}, {1}, {2};",
                                 GetQuotedValue(objectName),
                                 GetQuotedValue(fieldDef.FieldName),
                                 GetQuotedValue("COLUMN"));
        }
    }
}