Microsoft.Data.Sqlite: Implement Data Change Notifications Callback

[allanrsmith]

Data Change Notifications Callback

Unable to register a callback on the SqliteConnection object for updates to specified tables

Functional impact

With separate App/DbContext instances I would like callbacks to be called in a second instance when one instance updates a table row. This will allow for performant responsive applications.

Minimal repro steps

Functionality is missing

Expected result

Register a callback on the SqliteConnection for a specific table/set of tables, perhaps for types of table changes, to allow my code to react to table updates.

Actual result

No functionality exists to support this

Further technical details

https://sqlite.org/c3ref/update_hook.html

CC: @bricelam

[bricelam]

Since we expose the native handle, one workaround could be:

sqlite3_update_hook(
    connection.Handle,
    (user_data, type, database, table, rowid) => { /* TODO: Handle it. */ },
    null);

[AlexanderTaeschner]

What about the following addition to SqliteConnection:

public event SqliteDataChangeEventHandler DataChange;

protected virtual void OnDataChange(SqliteDataChangeEventArgs e)

with

public delegate void SqliteDataChangeEventHandler(object sender, SqliteDataChangeEventArgs e);

public class SqliteDataChangeEventArgs : EventArgs
{
    public SqliteDataChangeEventArgs(
        DataChangeAction dataChangeAction,
        string database,
        string table,
        long rowid)

    public DataChangeAction DataChangeAction { get; }
    public string Database { get; }
    public string Table { get; }
    public long Rowid { get; }
}

public enum DataChangeAction
{
    Unknown,
    Update,
    Delete,
    Insert
}

See https://github.com/AlexanderTaeschner/Microsoft.Data.Sqlite/commit/a3b0938cd42c5207723c01c0be1c54aa661afdb5 for a test implementation.

[bricelam]

Does System.Data.SQLite expose any API for it?

[AlexanderTaeschner]

I didn't look at System.Data.SQLite before I made my suggestion. System.Data.SQLite.SQLiteConnection has the following event:

public event SQLiteUpdateEventHandler Update;

with

public delegate void SQLiteUpdateEventHandler(object sender, UpdateEventArgs e);

public class UpdateEventArgs : EventArgs
{
  public readonly string Database;
  public readonly string Table;
  public readonly UpdateEventType Event;
  public readonly Int64 RowId;
}

public enum UpdateEventType
{
    Delete = 9,
    Insert = 18,
    Update = 23,
}

[bricelam]

We should keep the event name Update. Otherwise, I liked your original proposal better. Should also use EventHandler<SqliteDataChangeEventArgs> instead of the SQLiteUpdateEventHandler delegate.

[bricelam]

There might be some value in re-using the type and member names too... (UpdateEventType, Event, and RowId) But I'll leave that up to you.

[bricelam]

Re-opening this. It was reverted in 643a286. We need to fix #441 before re-merging.

[bricelam]

Re-opening. Still seeing #441 even after #472. Reverted in 2e3c403

[bricelam]

To move forward on this, we'll need to create a test that fails with the current implementation so we can debug and be confident in our fix. The fix may belong in SQLitePCL.raw

[AlexanderTaeschner]

Are the failures you are seeing reproducible or could it be, that the fact that neither SQLitePCL.raw nor this library is thread safe is the real problem?

[ericsink]

FWIW, I am following along with this and #441, interested in the possibility that I might need to make a fix of some kind.

[bricelam]

I haven't seen them locally. They only seem to be on the CI configuration that run all of the ASP.NET and EF Core tests together. I suspect it's a multi-threaded race condition or something.

[harvinders]

@bricelam Any update? Is it likely to be in 3.0 release?

[bricelam]

It's more or less on hold. Someone needs to dig into the EF Core failures to fully understand them. It isn't currently a priority for our team, so someone from the community will need to drive the effort.

[0xced]

Issue #441 is about renaming the OnModelCreating argument from builder to modelBuilder. How is this related to data change notifications callback? Was the issues migrated from another repository without fixing the issue numbers? I am confused. 😕

[bricelam]

Yep, migrated. See aspnet/Microsoft.Data.Sqlite#441

[MikeK-10]

Looking for documentation or examples for SqliteDataChangeEventHandler if possible?

[bricelam]

No docs. This feature isn't implemented yet.

Personally, I think this feature is kinda useless since sqlite3_update_hook only fires events for data changed by the current connection. In other words, you won't get notifications for data changed by other connections (or applications). (Nevermind; it might actually fire for all changes.)

[Ciantic]

I was experimenting with this, sqlite3_update_hook seems to be firing on changes happening per connection. It makes it rather useless for multi process subscribers/events.

It might still be useful for someone, it can be of course used even if the patch never lands, just use it directly from SQLitePCL.raw:

    public static void ListenSqlite(DbContext context)
    {
        context.Database.OpenConnection();
        var c = context.Database.GetDbConnection() as SqliteConnection;
        SQLitePCL.raw.sqlite3_update_hook(c.Handle, test_func, new object());
    }

    static readonly delegate_update test_func = (db, wint, char1, char2, sqlitei64) =>
    {
        Console.WriteLine("Yeah?");
    };

In CSProj:

    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.0" />
    <PackageReference Include="Microsoft.Data.Sqlite" Version="6.0.0" />
    <PackageReference Include="SQLitePCLRaw.core" Version="2.0.7" />

[jtheisen]

@Ciantic Can somebody else confirm this? It would make the Sqlite documentation irresponsibly misleading as it says nothing about this restriction and it would make this feature indeed almost entirely useless.

Each connection itself obviously already knows what it does.

If that's true they need to update their docs.

[jtheisen]

I just got confirmation that @Ciantic is correct.

[bricelam]

...it would make this feature indeed almost entirely useless.

Each connection itself obviously already knows what it does.

💯 Completely agree--that's why this really hasn't been a priority for the team. Just track your own changes in app code.

[bricelam]

Most people are probably looking for an event that will fire when data is changed by another process. This is not that.

[ryanmsnyder]

Is there a workaround to listen for changes made by other processes?

[bricelam]

AFAIK, SQLite doesn't have a feature to do that. You can probably use FileSystemWatcher and some sort of journal table (like DotMim.Sync uses) to design your own.

[sgf]

This event can be registered on each connection and directed to the same delegate. In this way, at least within the process, we get event notifications for all connections.