Efcore: 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:
C# sqlite3_update_hook( connection.Handle, (user_data, type, database, table, rowid) => { /* TODO: Handle it. */ }, null);

[AlexanderTaeschner]

What about the following addition to SqliteConnection:

```[csharp]
public event SqliteDataChangeEventHandler DataChange;

protected virtual void OnDataChange(SqliteDataChangeEventArgs e)

```[csharp]
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:
```[csharp]
public event SQLiteUpdateEventHandler Update;

```[csharp]
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 643a28620674799870ceacd61b017d495d154ad5. We need to fix #441 before re-merging.

[bricelam]

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

[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 https://github.com/aspnet/Microsoft.Data.Sqlite/issues/441