Feature suggestion: SqliteDropCreateDatabaseIfChanged

[HarelM]

There are times when you want to know the database had changed only to delete it in order to create a new one with the new tables scheme.
I would like to use this sqlite database as some kind of cache - If all tables and columns are exactly as the last time I used this database I want to keep using it, however if the scheme had changed since last usage I want to delete the database as It does no longer reflect the code that is currently running.

Thoughts are welcomed :-)
Thanks,
Harel M.

[msallin]

Thank you for your suggestion.
I agree that this is a interesting feature.

Have you an idea how we could detect the model changes?
The simplest way would be to hash the generated SQL and save it to the database. Once the model changes in some way, the generated SQL will also change and therefore a different hash is generated. In this case the database can be "dropped".

[HarelM]

Unfortunately, I'm completely clueless when it comes to how EF works.
But from the top of my head I would assume there should be some kind of event or method being called when there is a scheme change since you should generate some data in the migration table, but these are only assumptions since I'm not familiar with the internals of EF.

:-/
Harel M.

[HarelM]

Hi,

I Looked at the source code of EF 6, There is a DropCreateDatabaseIfModelChanges initializer.
I tried copying it to the project but it fails to run because it relies on migrations.
I have found a class for migrations generation code here:
https://drive.google.com/file/d/0B-NCqWnMQpBrQjkxMkxnVlRzb00/view?usp=sharing
unfortunately the function name and description is in Spanish, but I think the code is self expenatory by the override methods. I did not take the time and see if it really work or not...
I know you have migrations in one of the mile stones, so I'm guessing once this is done you could take the following code in order to achieve the required enhancement:

public class SqliteDropCreateDatabaseIfModelChanges<TContext> : SqliteInitializerBase<TContext>
        where TContext : DbContext
    {
        public SqliteDropCreateDatabaseIfModelChanges(string connectionString, DbModelBuilder modelBuilder)
            : base(connectionString, modelBuilder) { }

        public override void InitializeDatabase(TContext context)
        {
            bool dbExists = File.Exists(DatabaseFilePath);

            if (dbExists)
            {
                if (context.Database.CompatibleWithModel(throwIfNoMetadata: true))
                {
                    return;
                }
                File.Delete(DatabaseFilePath);
            }

            // Database didn't exist or we deleted it, so we now create it again.
            base.InitializeDatabase(context);
        }
    }

Thanks, I hope I helped a bit.
Harel M.

[msallin]

Hi,

Thank you! Sounds good. Its definitively on the Road map.
I'll investigate how migration internals from the entity framework (and especially CompatibleWithModel) works and write down my findings here.

Greetings Marc

[nbaxp]

//Hi, there. I test these code worked with DropCreateDatabaseIfModelChanges.I'm not good at github.
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 : IDatabaseInitializer 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)
    {
    }
}

}

[HarelM]

This seems like a lot of code compare to context.Database.CompatibleWithModel(throwIfNoMetadata: true) isn't there a way to integrate into the migration infrastructure that already exists in entity framework? (I'm asking because I'm not familiar with EF not because I think the code won't work)

[nbaxp]

Yes,it is just a very simple code which can drop and create database when entities changed.I think DropCreateDatabaseIfChanged just use in Debug.this code I used worked well.

[HarelM]

Acctually this is not just for debug, I use it in production - I use sqlite databse as some sort of cache, to store data when the application runs. when the application is restarted I want the user to be able to see the data used in previous session, unless the schema had changed (for eaxmple in version upgrade).
Regardelss, while I believe you it works, it still feels like a workaround. I prefer to avoid workarounds :-).

[nbaxp]

Agree with you.

[msallin]

I decided to go the way purposed by @csurn.
See https://github.com/msallin/SQLiteCodeFirst/tree/Issue-16
I'm going to release a new version next week.