EntityFramework系列：SQLite的CodeFrist和RowVersion

剛哥521發表於2015-05-01
FrameworkSQLite

沒什麼好說的，能支援DropCreateDatabaseIfModelChanges和RowVersion的Sqlite誰都想要。EntityFramework7正在新增對Sqlite的支援，雖然EF7不知道猴年馬月才能完成正式版，更不知道MySql等第三方提供程式會在什麼時候跟進支援，但是EF7中的確出現了Sqlite的相關程式碼。Sqlite支援EF6的CodeFirst，只是不支援從實體生成資料庫，估計有很多人因為這個原因放棄了使用它。現在SQLite.CodeFirst的簡單實現可以讓我們生成資料庫，因此在等待EF7的可以預見的長時間等待中，我們可以使用SQLite.CodeFirst，畢竟我們只是開發的時候使用DropCreateDatabaseIfModelChanges，Release時不會使用更不用擔心SQLite.CodeFirst的簡單實現會帶來什麼問題。可以直接修改原始碼，也可以參考最後面通過反射實現自定義DropCreateDatabaseIfModelChanges的方式。

1.RowVersion的支援：

可以從我的上一篇：在MySql中使用和SqlServer一致的RowVersion併發控制中採用相同的策略即可。我已經測試過在Sqlite中的可行性。

1.首先是RowVersion的配置：

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Configurations.AddFromAssembly(typeof(SqliteDbContext).Assembly);
            modelBuilder.Properties()
                            .Where(o => o.Name == "RowVersion")
                            .Configure(o => o.IsConcurrencyToken()
                            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None));
            Database.SetInitializer(new SqliteDbInitializer(Database.Connection.ConnectionString, modelBuilder));
        }

2.然後是SaveChanges的重寫：

public override int SaveChanges()
        {
            this.ChangeTracker.DetectChanges();
            var objectContext = ((IObjectContextAdapter)this).ObjectContext;
            foreach (ObjectStateEntry entry in objectContext.ObjectStateManager.GetObjectStateEntries(EntityState.Modified | EntityState.Added))
            {
                var v = entry.Entity as IRowVersion;
                if (v != null)
                {
                    v.RowVersion = System.Text.Encoding.UTF8.GetBytes(Guid.NewGuid().ToString());
                }
            }
            return base.SaveChanges();
        }

3.生成__MigrationHistory：

DropCreateDatabaseIfModelChanges則需要修改SQLite.CodeFirst的程式碼，SQLite.CodeFirst生成的資料庫不包含__MigrationHistory資訊，所以我們首先修改SqliteInitializerBase新增__MigrationHistory，__MigrationHistory表是通過HistoryRow實體的對映，我們直接在EF原始碼中找到相關部分作為參考。修改SqliteInitializerBase的SqliteInitializerBase方法，配置HistoryRow實體的對映。

public const string DefaultTableName = "__MigrationHistory";

        internal const int ContextKeyMaxLength = 300;
        internal const int MigrationIdMaxLength = 150;

        protected SqliteInitializerBase(string connectionString, DbModelBuilder modelBuilder)
        {
            DatabaseFilePath = SqliteConnectionStringParser.GetDataSource(connectionString);
            ModelBuilder = modelBuilder;

            // This convention will crash the SQLite Provider before "InitializeDatabase" gets called.
            // See https://github.com/msallin/SQLiteCodeFirst/issues/7 for details.
            modelBuilder.Conventions.Remove<TimestampAttributeConvention>();


            modelBuilder.Entity<HistoryRow>().ToTable(DefaultTableName);
            modelBuilder.Entity<HistoryRow>().HasKey(
                h => new
                {
                    h.MigrationId,
                    h.ContextKey
                });
            modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(MigrationIdMaxLength).IsRequired();
            modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(ContextKeyMaxLength).IsRequired();
            modelBuilder.Entity<HistoryRow>().Property(h => h.Model).IsRequired().IsMaxLength();
            modelBuilder.Entity<HistoryRow>().Property(h => h.ProductVersion).HasMaxLength(32).IsRequired();
        }

4.初始化__MigrationHistory：

繼續修改InitializeDatabase方法，在建立資料庫後，初始化__MigrationHistory的資訊。雖然採用了HistoryRow，但初始化資訊我們只簡單的使用生成的SQL語句作為判定實體和配置是否改變的依據，因為後面的InitializeDatabase方法中也是我們自己來判定實體和配置是否改變。

public virtual void InitializeDatabase(TContext context)
        {
            var model = ModelBuilder.Build(context.Database.Connection);

            using (var transaction = context.Database.BeginTransaction())
            {
                try
                {
                    var sqliteDatabaseCreator = new SqliteDatabaseCreator(context.Database, model);
                    sqliteDatabaseCreator.Create();
                    /*start*/
                    context.Set<HistoryRow>().Add(
                        new HistoryRow
                        {
                            MigrationId = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fffffff"),
                            ContextKey = context.GetType().FullName,
                            Model = System.Text.Encoding.UTF8.GetBytes(sqliteDatabaseCreator.GetSql().ToCharArray()),
                            ProductVersion = "6.1.2"
                        });
                    /*end*/
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }

            using (var transaction = context.Database.BeginTransaction())
            {
                try
                {
                    Seed(context);
                    context.SaveChanges();
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

5.新增DropCreateDatabaseIfModelChanges支援

新增SqliteDropCreateDatabaseIfModelChanges類，在InitializeDatabase方法中判讀實體和配置是否改變。需要注意的是，刪除sqlite檔案時，即使關閉Connection和呼叫GC.Collect()仍然在第一次無法刪除檔案，所以必須進行多次嘗試。

public override void InitializeDatabase(TContext context)
        {
            bool dbExists = File.Exists(DatabaseFilePath);
            if (dbExists)
            {
                var model = ModelBuilder.Build(context.Database.Connection);
                var sqliteDatabaseCreator = new SqliteDatabaseCreator(context.Database, model);
                var newSql = sqliteDatabaseCreator.GetSql();
                var oldSql = "";
                oldSql = System.Text.Encoding.UTF8.GetString(context.Set<System.Data.Entity.Migrations.History.HistoryRow>().AsNoTracking().FirstOrDefault().Model);
                context.Database.Connection.Close();
                GC.Collect(); 
                if (oldSql == newSql)
                {
                    return;
                }
                for (int i = 0; i < 10; i++)
                {
                    try
                    {
                        File.Delete(DatabaseFilePath);
                        break;
                    }
                    catch (Exception)
                    {
                        System.Threading.Thread.Sleep(1);
                    }
                }
            }

            base.InitializeDatabase(context);
        }

 

核心的程式碼已經貼出來，SQLite.CodeFirst本身的實現就比較簡易，我新增的程式碼也比較簡陋，因此在程式碼上沒什麼參考價值，只有使用和實用價值。畢竟只是在Debug開發時才需要這些功能的支援，對Sqlite本身和EF的提供程式沒有任何影響。到這裡終於鬆了口氣，我們現在可以使用：Sql Server（CE）、Sqlite和Mysql進行Code First開發，採用相同的實體定義和配置，並且採用相同的併發控制。非Sql Server（CE）的併發控制和Sqlite不支援從程式碼生成資料庫這兩點終於克服了。

6.不修改原始碼，使用反射實現

修改原始碼是由於SQLite.CodeFirst的內部類無法呼叫，考慮到可以使用反射，於是有了下面不需要修改原始碼，通過反射實現直接自定義DropCreateDatabaseIfModelChanges的方式：

using SQLite.CodeFirst.Statement;
using System;
using System.Data.Entity;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Migrations.History;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.IO;
using System.Linq;
using System.Reflection;

namespace SQLite.CodeFirst
{
    public class SqliteDropCreateDatabaseIfModelChanges<TContext> : IDatabaseInitializer<TContext> where TContext : DbContext
    {
        protected readonly DbModelBuilder ModelBuilder;
        protected readonly string DatabaseFilePath;

        public const string DefaultTableName = "__MigrationHistory";
        private const string DataDirectoryToken = "|datadirectory|";

        internal const int ContextKeyMaxLength = 300;
        internal const int MigrationIdMaxLength = 150;

        public SqliteDropCreateDatabaseIfModelChanges(string connectionString, DbModelBuilder modelBuilder)
        {
            DatabaseFilePath = ConnectionStringParse(connectionString);
            ModelBuilder = modelBuilder;

            // This convention will crash the SQLite Provider before "InitializeDatabase" gets called.
            // See https://github.com/msallin/SQLiteCodeFirst/issues/7 for details.
            modelBuilder.Conventions.Remove<TimestampAttributeConvention>();
            ConfigMigrationHistory(modelBuilder);
        }

        private string ConnectionStringParse(string connectionString)
        {
            var path = connectionString.Trim(' ', ';').Split(';').FirstOrDefault(o => o.StartsWith("data source", StringComparison.OrdinalIgnoreCase)).Split('=').Last().Trim();
            if (!path.StartsWith("|datadirectory|", StringComparison.OrdinalIgnoreCase))
            {
                return path;
            }
            string fullPath;

            // find the replacement path
            object rootFolderObject = AppDomain.CurrentDomain.GetData("DataDirectory");
            string rootFolderPath = (rootFolderObject as string);
            if (rootFolderObject != null && rootFolderPath == null)
            {
                throw new InvalidOperationException("The value stored in the AppDomains 'DataDirectory' variable has to be a string!");
            }
            if (string.IsNullOrEmpty(rootFolderPath))
            {
                rootFolderPath = AppDomain.CurrentDomain.BaseDirectory;
            }

            // We don't know if rootFolderpath ends with '\', and we don't know if the given name starts with onw
            int fileNamePosition = DataDirectoryToken.Length;    // filename starts right after the '|datadirectory|' keyword
            bool rootFolderEndsWith = (0 < rootFolderPath.Length) && rootFolderPath[rootFolderPath.Length - 1] == '\\';
            bool fileNameStartsWith = (fileNamePosition < path.Length) && path[fileNamePosition] == '\\';

            // replace |datadirectory| with root folder path
            if (!rootFolderEndsWith && !fileNameStartsWith)
            {
                // need to insert '\'
                fullPath = rootFolderPath + '\\' + path.Substring(fileNamePosition);
            }
            else if (rootFolderEndsWith && fileNameStartsWith)
            {
                // need to strip one out
                fullPath = rootFolderPath + path.Substring(fileNamePosition + 1);
            }
            else
            {
                // simply concatenate the strings
                fullPath = rootFolderPath + path.Substring(fileNamePosition);
            }
            return fullPath;
        }

        private void ConfigMigrationHistory(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<HistoryRow>().ToTable(DefaultTableName);
            modelBuilder.Entity<HistoryRow>().HasKey(
                h => new
                {
                    h.MigrationId,
                    h.ContextKey
                });
            modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(MigrationIdMaxLength).IsRequired();
            modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(ContextKeyMaxLength).IsRequired();
            modelBuilder.Entity<HistoryRow>().Property(h => h.Model).IsRequired().IsMaxLength();
            modelBuilder.Entity<HistoryRow>().Property(h => h.ProductVersion).HasMaxLength(32).IsRequired();
        }

        public string GetSql(DbModel model)
        {
            Assembly asm = Assembly.GetAssembly(typeof(SqliteInitializerBase<>));
            Type builderType = asm.GetType("SQLite.CodeFirst.Builder.CreateDatabaseStatementBuilder");

            ConstructorInfo builderConstructor = builderType.GetConstructor(new Type[] { typeof(EdmModel) });
            Object builder = builderConstructor.Invoke(new Object[] { model.StoreModel });

            MethodInfo method = builderType.GetMethod("BuildStatement", BindingFlags.Instance | BindingFlags.InvokeMethod | BindingFlags.Public);

            var statement = (IStatement)method.Invoke(builder, new Object[] { });
            string sql = statement.CreateStatement();
            return sql;
        }

        public void InitializeDatabase(TContext context)
        {
            var model = ModelBuilder.Build(context.Database.Connection);
            var sqliteDatabaseCreator = new SqliteDatabaseCreator(context.Database, model);
            var newSql = this.GetSql(model);

            bool dbExists = File.Exists(DatabaseFilePath);
            if (dbExists)
            {
                var oldSql = System.Text.Encoding.UTF8.GetString(context.Set<System.Data.Entity.Migrations.History.HistoryRow>().AsNoTracking().FirstOrDefault().Model);
                context.Database.Connection.Close();
                GC.Collect();
                if (oldSql == newSql)
                {
                    return;
                }
                for (int i = 0; i < 10; i++)
                {
                    try
                    {
                        File.Delete(DatabaseFilePath);
                        break;
                    }
                    catch (Exception)
                    {
                        System.Threading.Thread.Sleep(1);
                    }
                }
            }
            using (var transaction = context.Database.BeginTransaction())
            {
                try
                {
                    sqliteDatabaseCreator.Create();
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }

            using (var transaction = context.Database.BeginTransaction())
            {
                try
                {
                    context.Set<HistoryRow>().Add(
                    new HistoryRow
                    {
                        MigrationId = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fffffff"),
                        ContextKey = context.GetType().FullName,
                        Model = System.Text.Encoding.UTF8.GetBytes(newSql.ToCharArray()),
                        ProductVersion = "6.1.3"
                    });
                    Seed(context);
                    context.SaveChanges();
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        protected virtual void Seed(TContext context)
        {
        }
    }
}
View Code

 

希望你不是找了好久才找到這個解決方案。

相關文章