public void TestSession05_BothDbWithAttachMainTablesAndEmptySession()
{
// close replica we just created and attach db file to our primary db as "replica"
dbReplica.Close();
dbPrimary.AttachDB(dbReplicaPath, "replica");
// create a session for "main" database
//Assert.That(SQLiteSession.Create(dbPrimary, out session), Is.EqualTo(SQLite3.Result.OK));
Assert.That(dbPrimary.CreateSession("main", out session), Is.EqualTo(SQLite3.Result.OK));
// attach all tables [from "main"] to it
Assert.That(SQLiteSession.AttachToTable(session, null), Is.EqualTo(SQLite3.Result.OK));
// enable watching for changes with this session
Assert.That(SQLiteSession.Enable(session, SQLiteSession.EnableState.EnableSession), Is.EqualTo(SQLiteSession.EnableState.EnableSession));
// validate no changes have occurred
Assert.That(SQLiteSession.IsEmptySession(session), Is.True);
// remove secondary db
dbPrimary.DetachDB("replica");
// done with our session
SQLiteSession.Delete(session);
}
/// <summary>
/// attach session to "main" and update table with new row
/// </summary>
private void MakeAndValidateTableChanges()
{
// create a session for "main" database
Assert.That(dbPrimary.CreateSessionAndAttachTable("main", out session, zTab: null), Is.EqualTo(SQLite3.Result.OK));
// validate no changes have occurred
Assert.That(SQLiteSession.IsEmptySession(session), Is.True);
// verify that "main" has 2 rows in TestTable1
var mainTestTable1Rows = dbPrimary.Query <TestTable1>("Select * FROM main.TestTable1;");
Assert.That(mainTestTable1Rows.Count, Is.EqualTo(2));
// add a row to table
var newRow = new TestTable1
{
myString = "test",
myInt = 3,
myDate = DateTime.Now,
myTable2Object = sampleData.myTable2Object
};
Assert.That(dbPrimary.Insert(newRow), Is.EqualTo(1));
// verify that "main" now has 3 rows in TestTable1
mainTestTable1Rows = dbPrimary.Query <TestTable1>("Select * FROM main.TestTable1;");
Assert.That(mainTestTable1Rows.Count, Is.EqualTo(3));
// session should no longer be empty, as we have inserted a row
Assert.That(SQLiteSession.IsEmptySession(session), Is.False);
}
public void TestSession03_WithAttachAndEmptySession()
{
Assert.That(SQLiteSession.Create(dbPrimary, out session), Is.EqualTo(SQLite3.Result.OK));
Assert.That(SQLiteSession.AttachToTable(session, nameof(TestTable1)), Is.EqualTo(SQLite3.Result.OK));
Assert.That(SQLiteSession.AttachToTable(session, nameof(TestTable2)), Is.EqualTo(SQLite3.Result.OK));
Assert.That(SQLiteSession.IsEmptySession(session), Is.True);
SQLiteSession.Delete(session);
}
public void TestSession04_WithAttachAllAndEmptySession()
{
// create a session for "main" database
Assert.That(SQLiteSession.Create(dbPrimary, out session), Is.EqualTo(SQLite3.Result.OK));
// attach all tables to it
Assert.That(SQLiteSession.AttachToTable(session, null), Is.EqualTo(SQLite3.Result.OK));
// enable watching for changes with this session
Assert.That(SQLiteSession.Enable(session, SQLiteSession.EnableState.EnableSession), Is.EqualTo(SQLiteSession.EnableState.EnableSession));
// validate no changes have occurred
Assert.That(SQLiteSession.IsEmptySession(session), Is.True);
// done with our session
SQLiteSession.Delete(session);
}
public void TestSession06_BothDbWithAttachMainTablesAndEmptySessionConvenience()
{
// close replica we just created and attach db file to our primary db as "replica"
dbReplica.Close();
dbPrimary.AttachDB(dbReplicaPath, "replica");
// create a session for "main" database
Assert.That(dbPrimary.CreateSessionAndAttachTable("main", out session, zTab: null), Is.EqualTo(SQLite3.Result.OK));
// validate no changes have occurred
Assert.That(SQLiteSession.IsEmptySession(session), Is.True);
// remove secondary db
dbPrimary.DetachDB("replica");
// done with our session
SQLiteSession.Delete(session);
}
public void TestSession07_CreateTableDiffForSession()
{
// close replica we just created and attach db file to our primary db as "replica"
dbReplica.Close(); dbReplica = null;
dbPrimary.AttachDB(dbReplicaPath, "replica");
// verify that "main" has rows in TestTable1 and "replica" currently empty
var mainTestTable1Rows = dbPrimary.Query <TestTable1>("Select * FROM main.TestTable1;");
Assert.That(mainTestTable1Rows.Count, Is.GreaterThan(0));
var replicaTestTable1Rows = dbPrimary.Query <TestTable1>("Select * FROM replica.TestTable1;");
Assert.That(replicaTestTable1Rows.Count, Is.EqualTo(0));
// create a session for "replica" database <== note replica not main
Assert.That(dbPrimary.CreateSession("main", out session), Is.EqualTo(SQLite3.Result.OK));
// attach all tables [from "main"] to it
Assert.That(SQLiteSession.AttachToTable(session, null), Is.EqualTo(SQLite3.Result.OK));
// Note: we have not enabled watching for changes
// validate no changes have occurred
Assert.That(SQLiteSession.IsEmptySession(session), Is.True);
// see what it would take to make replica like primary
#pragma warning disable IDE0018 // Inline variable declaration
string errMsg;
#pragma warning restore IDE0018 // Inline variable declaration
Assert.That(SQLiteSession.AddTableDiffToSession(session, "replica", nameof(TestTable1), out errMsg), Is.EqualTo(SQLite3.Result.OK));
Assert.That(SQLiteSession.AddTableDiffToSession(session, "replica", nameof(TestTable2), out errMsg), Is.EqualTo(SQLite3.Result.OK));
// session should no longer be empty, as tables differ and session should have the diff
Assert.That(SQLiteSession.IsEmptySession(session), Is.False);
// remove secondary db
dbPrimary.DetachDB("replica");
// done with our session
SQLiteSession.Delete(session);
}
public void TestSession42_ReplicateSimple()
{
// close replica we just created and attach db file to our primary db as "replica"
dbReplica.Close();
dbPrimary.AttachDB(dbReplicaPath, "replica");
// verify that "main" has rows in TestTable1 and "replica" currently empty
var mainTestTable1Rows = dbPrimary.Query <TestTable1>("Select * FROM main.TestTable1;");
Assert.That(mainTestTable1Rows.Count, Is.GreaterThan(0));
var replicaTestTable1Rows = dbPrimary.Query <TestTable1>("Select * FROM replica.TestTable1;");
Assert.That(replicaTestTable1Rows.Count, Is.EqualTo(0));
// create a session for "main" database
Assert.That(dbPrimary.CreateSession("main", out session), Is.EqualTo(SQLite3.Result.OK));
// attach all tables [from "main"] to it
Assert.That(SQLiteSession.AttachToTable(session, null), Is.EqualTo(SQLite3.Result.OK));
// Note: we have not enabled watching for changes
// validate no changes have occurred
Assert.That(SQLiteSession.IsEmptySession(session), Is.True);
// see what it would take to make replica.table like main.table
#pragma warning disable IDE0018 // Inline variable declaration
string errMsg;
#pragma warning restore IDE0018 // Inline variable declaration
Assert.That(SQLiteSession.AddTableDiffToSession(session, "replica", nameof(TestTable1), out errMsg), Is.EqualTo(SQLite3.Result.OK));
Assert.That(SQLiteSession.AddTableDiffToSession(session, "replica", nameof(TestTable2), out errMsg), Is.EqualTo(SQLite3.Result.OK));
// session should no longer be empty, as tables differ and session should have the diff
Assert.That(SQLiteSession.IsEmptySession(session), Is.False);
// create change set
Assert.That(SQLiteSession.GenerateChangeSet(session, out SQLiteChangeSet changeSet), Is.EqualTo(SQLite3.Result.OK));
// done with our session
SQLiteSession.Delete(session);
// remove secondary db
dbPrimary.DetachDB("replica");
// reopen dbReplica
dbReplica = new SQLiteConnection(dbReplicaPath, storeDateTimeAsTicks: true);
// validate reopened replica db has no rows
replicaTestTable1Rows = dbReplica.Query <TestTable1>("Select * FROM TestTable1;");
Assert.That(replicaTestTable1Rows.Count, Is.EqualTo(0));
// apply change set to dbReplica
// should be no conflicts so conflict handler not called
Assert.That(dbReplica.ApplySessionChangeSet(changeSet, null, null, null), Is.EqualTo(SQLite3.Result.OK));
// validate has contents we expect
replicaTestTable1Rows = dbReplica.Query <TestTable1>("Select * FROM TestTable1;");
Assert.That(replicaTestTable1Rows.Count, Is.GreaterThan(0));
// generate inverse change set
Assert.That(SQLiteSession.InvertChangeSet(changeSet, out SQLiteChangeSet inverseChangeSet), Is.EqualTo(SQLite3.Result.OK));
using (inverseChangeSet)
{
// apply the inverse
Assert.That(dbReplica.ApplySessionChangeSet(inverseChangeSet, null, null, null), Is.EqualTo(SQLite3.Result.OK));
// validate replica db once again has no rows
replicaTestTable1Rows = dbReplica.Query <TestTable1>("Select * FROM TestTable1;");
Assert.That(replicaTestTable1Rows.Count, Is.EqualTo(0));
}
// reset
CleanupTest();
SetupTest();
// insert differing row to dbReplica with same primary key, i.e. force conflict
// add a row to table
var newRow = new TestTable1
{
pk = sampleData.pk,
myString = "test conflict", // <== only conflicting piece of data
myInt = sampleData.myInt,
myDate = sampleData.myDate,
myTable2Object = sampleData.myTable2Object
};
Assert.That(dbReplica.Insert(newRow), Is.EqualTo(1));
// apply change set to dbReplica
// should now be a conflict so conflict handler is called
Assert.That(dbReplica.ApplySessionChangeSet(changeSet, DummyFilterCallback /* null */, SQLiteSession.CallbackReplaceOnConflicts, null /*"my text context"*/), Is.EqualTo(SQLite3.Result.OK));
// valid has contents we expect
replicaTestTable1Rows = dbReplica.Query <TestTable1>("Select * FROM TestTable1;");
Assert.That(replicaTestTable1Rows.Count, Is.GreaterThan(0));
// release our change set buffer explicitly
changeSet.Dispose();
}
/// <summary>
/// Perform one way synchronization where all changes in from db are replicated
/// to current db with conflict resolution. Once complete the current db will
/// contain all changes made to syncFromDb (minus conflicting changes) and all
/// changes to current db since last sync (minus conflicting changes).
/// Note: dbToSyncFrom is not modified, i.e. changes within current db are not
/// replicated back (hence the one way).
/// On conflicting change only one or the other is used (obviously) - each
/// table has its own conflict handler and that determines whether lastest
/// change wins, current db wins, or user is asked.
/// Uses active db connection for database to sync (db to update)
/// </summary>
/// <param name="dbToSyncFromFilename">filename (including path) to synchronize with</param>
public void SyncOneWay(string dbToSyncFromFilename, string[] tablesToSync)
{
// todo fix properly
_tablesToSync = tablesToSync;
// session used during replication, Zero if no session currently open
var session = IntPtr.Zero;
try
{
logger.Info($"SyncOneWay with {dbToSyncFromFilename}");
// open both database files in same db instance as 'main' and 'replica'
db.AttachDB(dbToSyncFromFilename, "replica");
// create a session, used to obtain differences
// Note: we use reverse of desired to force each change to conflict, if we created session on "replica"
// and diff to "main" below, then no conflicts will occur during applying of changset and when complete
// main db will be identical to replica db - but we want a chance to evaluate each change before
// applying, so we create the inverse changeset which forces a conflict for each change
IfNotOKThenThrowReplicationException(db.CreateSession("main", out session), "Failed to create session");
// do for each table that should be synchronized
// attach all tables [from "main"] to it
IfNotOKThenThrowReplicationException(SQLiteSession.AttachToTable(session, null), "Failed to attach tables");
// Note: we have not enabled watching for changes
// validate no changes have occurred
if (!SQLiteSession.IsEmptySession(session))
{
throw new ReplicationException("Session is not empty!");
}
// see what it would take to make main.table like replica.table
foreach (var tableName in tablesToSync)
{
IfNotOKThenThrowReplicationException(SQLiteSession.AddTableDiffToSession(session, "replica", tableName, out string errMsg), $"Unable to determine differences for table {tableName}: {errMsg}");
}
// if there are any changes then session will not be empty, it should contain the difference
if (SQLiteSession.IsEmptySession(session))
{
logger.Debug("Replication - no changes, session is empty after differencing.");
}
// create change set
IfNotOKThenThrowReplicationException(SQLiteSession.GenerateChangeSet(session, out SQLiteChangeSet changeSet), "Failed to generate change set from session");
using (changeSet) // ensure changeSet.Dispose() is called to release change set buffer
{
// on conflicts our conflict handler callback uses our context to handle conflicts accordingly
IfNotOKThenThrowReplicationException(db.ApplySessionChangeSet(changeSet, null, CallbackConflictHandler, ctx: this), "Failed to apply replicated changes");
}
}
finally
{
// ensure session is closed since we are done with our session,
// Ensure attached databases and sessions are cleaned up.
// May be called if error occurs or as part of normal cleanup
// Note: we must ensure replica is still available for conflict handler so don't do earlier
if (session != IntPtr.Zero)
{
SQLiteSession.Delete(session);
}
session = IntPtr.Zero;
// remove secondary db
db?.DetachDB("replica");
}
}