CODEWITHSUNDEEP
windowssqlitewindows-runtimemicrosoft-metrowindows-8.1-universal
Kishor Bikram Oli
Kishor Bikram Oli

Reputation: 1828

Windows Runtime Apps: Upgrading SQLite database and adding a new table

I have an SQLite database in the first version of my Windows app (which is on the Windows store). Now I want to release the second version of the application, which also has an SQLite database in it with new tables added. I have my data saved in first version and don't want to lose them. I found that Android has onCreate and onUpgrade methods for handling sqlite database versions. Android: upgrading DB version and adding new table .

Similar question is here. But that is for iOS.

Is there any similar solutions for Windows Runtime Apps(Windows 8.1 and Windows Phone 8.1)? Please suggest some alternatives.

Thanks in advance.

Upvotes: 0

Views: 163

Answers (2)

Jonathan ANTOINE
Jonathan ANTOINE

Reputation: 9223

A better (for performance) way to have a version of the DB is to use the "PRAGMA user_version"

var sqLiteAsyncConnection = new SQLiteAsyncConnection(path);

// read the user version
var version = sqLiteAsyncConnection.ExecuteScalar<string>("PRAGMA user_version");

perfMon.TraceSinceLast("StandardQueryBase : db version read");

if (version == "0")
{
   // update the schema here   

    // store the new version number in the DB
    sqLiteAsyncConnection.ExecuteScalar<string>("PRAGMA user_version=1;");
}

Upvotes: 1

Thomas LEBRUN
Thomas LEBRUN

Reputation: 436

A good way to handle this kind of issue is to add a versionning system in the database. Before using the connection to the database, simply check the app version in the database and, if the new version is higher of the previous one, run all the necessary commands to update your database.

Ex:

public async Task<SQLite.SQLiteConnection> GetSqliteConnectionForUserAsync(string login)
{
    using (await _mutex.LockAsync())
    {
        if (login == null)
        {
            login = "__anonymous__";
        }

        SQLite.SQLiteConnection conn;
        if (!_userConnections.TryGetValue(login, out conn))
        {
                conn = new SQLite.SQLiteConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path,
                string.Format("{0}.db", Uri.EscapeDataString(login))));
                await SqlSchemaHandler.EnsureSchemaReadyAsync(conn, s =>
                {
                    _logger.Info("Schema handler message : {0}", s);
                });
            _userConnections[login] = conn;
         }
     return conn;
}

}

And (SqlSchemaHandler):

public static Task EnsureSchemaReadyAsync(SQLiteConnection connection, Action<string> actionReporter)
{
    return Task.Run(() =>
    {
        connection.CreateTable<SchemaInfo>();
        var schemaInfo = connection.Table<SchemaInfo>().FirstOrDefault();
        if (schemaInfo == null)
        {
            ApplyV0ToV1(connection);
            schemaInfo = new SchemaInfo { Id = 1, Version = 1 };
            connection.Insert(schemaInfo);
        }
    });
}

private static void ApplyV0ToV1(SQLiteConnection connection)
{
    connection.CreateTable<Test>();
}

Thanks,

Upvotes: 2

Related Questions