[Breaking Changes] Switching from System.Data.Sqlite to Microsoft.Data.Sqlite

[Mimetis]

The last version (called 0.1.5) will integrate a major update on the SqliteSyncProvider

Actually, I have switched the base Sqlite provider from System.Data.Sqlite to Microsoft.Data.Sqlite.

For references, you will find usefuls informations from those two SQLite providers here:

• Microsoft.Data.Sqlite
• System.Data.Sqlite

Why ?

• System.Data.Sqlite is not (yet) .Net Standard compliant. Actually, it works, thanks to the .net standard shim system, but in many ways, it's also painful in some situations.
• Microsoft.Data.Sqlite is a leightweight version, developed by @bricelam from the Entity Framework team.

Impact and breaking changes

One of the most impactful issue comes from the Guid behavior in Sqlite. Actually, it's stored as blob by default in SQLite.

System.Data.Sqlite allows you to specify a BinaryGUID directive to be able to store Guid as String.
This behavior is not a SQLite behavior, just a system flag managed by System.Data.Sqlite

Microsoft.Data.Sqlite does not allow us to add any parameter on the connection string.
See why here :

• Add BinaryGUID connection string to select binary/string GUID storage
• Connection string settings

So, Every SQLite databases will now be generated with blob data type for every Guid type.

If you want to use the last version (and you have to !) You have to regenerate your SQLite databases.
The breaking changes from a string to a blob type for any Guid is not compatible.

Tips

Requesting a guid stored as a blob

If you are Inside a .Net application, using Microsoft.Data.Sqlite framewok, you have to use a SqliteParameter to be able to interact with a Guid stored as a blob:

var insertRowScript =
$@"INSERT INTO [ServiceTickets] ([ServiceTicketID], [Title], [Description], [StatusValue], [EscalationLevel], [Opened], [Closed], [CustomerID]) 
    VALUES (@id, 'Insert One Row in Sqlite client', 'Description Insert One Row', 1, 0, datetime('now'), NULL, 1)";

int nbRowsInserted = 0;

using (var sqlConnection = new SqliteConnection(fixture.ClientSqliteConnectionString))
{
    using (var sqlCmd = new SqliteCommand(insertRowScript, sqlConnection))
    {
        sqlCmd.Parameters.AddWithValue("@id", newId);

        sqlConnection.Open();
        nbRowsInserted = sqlCmd.ExecuteNonQuery();
        sqlConnection.Close();
    }

If you want to make a SQLite Select statement on any table with a blob data type containing a guid, you could make a call like that.

select quote(sync_scope_id), sync_scope_name from scope_info

Using a PRAGMA directive

If you want to use a PRAGMA directive, you can't use a connection string parameter anymore. But hopefully you can send a PRAGMA request, once your connection is opened:

SqliteConnection c = new SqliteConnection($"Data Source=fabrikam");
c.StateChange += (_, e) =>
{
    if (e.CurrentState == ConnectionState.Open)
    {
        var cmd = c.CreateCommand();
        cmd.CommandText = "PRAGMA journal_mode=WAL;";
        var i = cmd.ExecuteScalar();
    }
};

[bricelam]

In Microsoft.Data.Sqlite version 2.1, you'll be able to set SqliteParameter.SqliteType to Text to use the Guid's string value.

SqliteDataReader.GetGuid supports reading from either type.

One way to mitigate this break with 2.0 is to just call Guid.ToString() when setting SqliteParameter.Value. (in Dotmim.Sync)

...just my two cents.

[Mimetis]

Thx for the feedback @bricelam !

But in the Dotmim.Sync.Core assembly, I have generic implementation of setting value to any DbParameter.

I don't want to have one Guid implementation in the core (where I can't call Guid.ToString() coz of potentials issues to other providers like SQL Server or MySQL) and one implementation in the Sqlite specific code.

Since I'm generic I have to deal with DbParameter. Even with the 2.1, I'm not sure I will be able to implement the SqliteParameter.SqliteType (Once again, in the core assembly, I don't have any ref to any relational providers)

Thx !

[bricelam]

Sounds good, the break doesn't seem bad. I just wanted to make you were aware of the possible mitigation.

[thefonzi]

Acutally the framework I use in my applications creates GUID columns as char(36) and I will be forced to write a custom connection provider to change the behavior. Don' t you think there is a workaround, which doesn't break your implementation to remain with string columns for guid?