public void TestSession08_ChangesTracked() { MakeAndValidateTableChanges(); // done with our session SQLiteSession.Delete(session); }
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 TestSession10_IterateChangeSet() { MakeAndValidateTableChanges(); // create a change set Assert.That(SQLiteSession.GenerateChangeSet(session, out SQLiteChangeSet changeSet), Is.EqualTo(SQLite3.Result.OK)); using (changeSet) { Assert.That(changeSet.size, Is.GreaterThan(0)); // create an iterator so can iterate through change set Assert.That(SQLiteSession.ChangeSetStart(out Sqlite3ChangesetIterator iter, changeSet), Is.EqualTo(SQLite3.Result.OK)); using (iter) { SQLite3.Result result; while ((result = SQLiteSession.ChangeSetNext(iter)) == SQLite3.Result.Row) { Assert.That(result, Is.EqualTo(SQLite3.Result.Row)); // get table, column, and other information about this change Assert.That(SQLiteSession.ChangeSetOp(iter, out string table, out int columnCount, out SQLiteSession.ActionCode op, out bool indirect), Is.EqualTo(SQLite3.Result.OK)); Assert.That(table, Is.EqualTo("TestTable1")); Assert.That(columnCount, Is.GreaterThan(0)); Assert.That(op, Is.EqualTo(SQLiteSession.ActionCode.SQLITE_INSERT)); Assert.That(indirect, Is.False); // since insert, should be able to get new values inserted #pragma warning disable IDE0018 // Inline variable declaration SQLiteValue value; // 0th column is primary key #pragma warning restore IDE0018 // Inline variable declaration Assert.That(SQLiteSession.ChangeSetNewValue(iter, 1, out value), Is.EqualTo(SQLite3.Result.OK)); Assert.That(value.GetValue(), Is.EqualTo("test")); // 1st column = myString Assert.That(SQLiteSession.ChangeSetNewValue(iter, 2, out value), Is.EqualTo(SQLite3.Result.OK)); Assert.That(value.GetValue(), Is.EqualTo(3)); // 2nd column = myInt // DateTime is stored as long, WARNING may vary, see storeDateTimeAsTicks when creating db connection // Guid is stored as string // verify we can retrieve all values regardless of type for (int i = 0; i < columnCount; i++) { Assert.That(SQLiteSession.ChangeSetNewValue(iter, i, out value), Is.EqualTo(SQLite3.Result.OK)); Assert.That(value.GetValue(), Is.Not.Null); } } Assert.That(result, Is.EqualTo(SQLite3.Result.Done)); } Assert.That(iter.IsInvalid, Is.True); } Assert.That(changeSet.buffer.IsInvalid, Is.True); // done with our session SQLiteSession.Delete(session); }
public void TestSession09_GenerateChangeSet() { MakeAndValidateTableChanges(); // create a change set Assert.That(SQLiteSession.GenerateChangeSet(session, out SQLiteChangeSet changeSet), Is.EqualTo(SQLite3.Result.OK)); Assert.That(changeSet.size, Is.GreaterThan(0)); // done with our session SQLiteSession.Delete(session); }
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 Start() { try { log.InfoFormat("Start {0} {1}", AppDomain.CurrentDomain.BaseDirectory, root); string dbpath = Path.Combine(root, "sqlite.db"); using (SQLiteSession sqlite = new SQLiteSession(dbpath)) { string tn = DateTime.Now.ToString("yyyyMMddHHmmss"); sqlite.Execute($"CREATE TABLE table_{tn} (id INTEGER PRIMARY KEY AUTOINCREMENT, iii INTEGER,ddd DATE DEFAULT((date('now', 'localtime'))) NOT NULL);"); } } catch (Exception e) { log.ErrorFormat("异常: {0} {1}", e.Message, e.StackTrace); } }
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 TestSession11_IterateChangeSetShowPKValues() { MakeAndValidateTableChanges(); // create a change set Assert.That(SQLiteSession.GenerateChangeSet(session, out SQLiteChangeSet changeSet), Is.EqualTo(SQLite3.Result.OK)); using (changeSet) { Assert.That(changeSet.size, Is.GreaterThan(0)); // create an iterator so can iterate through change set Assert.That(SQLiteSession.ChangeSetStart(out Sqlite3ChangesetIterator iter, changeSet), Is.EqualTo(SQLite3.Result.OK)); using (iter) { SQLite3.Result result; while ((result = SQLiteSession.ChangeSetNext(iter)) == SQLite3.Result.Row) { Assert.That(result, Is.EqualTo(SQLite3.Result.Row)); // get table, column, and other information about this change Assert.That(SQLiteSession.ChangeSetOp(iter, out string table, out int columnCount, out SQLiteSession.ActionCode op, out bool indirect), Is.EqualTo(SQLite3.Result.OK)); Assert.That(table, Is.EqualTo("TestTable1")); Assert.That(columnCount, Is.GreaterThan(0)); Assert.That(op, Is.EqualTo(SQLiteSession.ActionCode.SQLITE_INSERT)); Assert.That(indirect, Is.False); // based on insert change var pkValues = SQLiteSession.ChangeSetPrimaryKeyValues(iter); Assert.That(pkValues.Count, Is.EqualTo(1)); Assert.That(pkValues[0].Item2, Is.EqualTo(0)); // 0th column is primary key, Item2==column# SQLiteValue value = pkValues[0].Item1; Assert.That(value.HasValue(), Is.True); System.Diagnostics.Debug.WriteLine($"PK is {value.GetValue()}."); } Assert.That(result, Is.EqualTo(SQLite3.Result.Done)); } Assert.That(iter.IsInvalid, Is.True); } Assert.That(changeSet.buffer.IsInvalid, Is.True); // 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); }
private void Wrapper(Action <IAppRespository> action, string tableName) { var ormSession = new SQLiteSession(this.OrmConfiguration); var appRespository = new AppRespository(tableName, ormSession, this.CurrentUser); try { ormSession.UnitOfWork.Start(); action(appRespository); ormSession.UnitOfWork.Commit(); } catch (Exception exception) { ormSession.UnitOfWork.Rollback(); Assert.Fail(exception.Message + exception.StackTrace); } finally { ormSession.UnitOfWork.Stop(); } }
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(); }
public void TestSession02_CreateandDeleteSession() { Assert.That(SQLiteSession.Create(dbPrimary, out session), Is.EqualTo(SQLite3.Result.OK)); SQLiteSession.Delete(session); }
/// <summary> /// Conflict handler, allows multiple methods of conflict resolution. /// </summary> /// <param name="ctx">information about how to handler conflicts per table</param> /// <param name="eConflict">reason for callback</param> /// <param name="pIter">conflict change set, may iterate through values</param> /// <returns>how to respond to conflicting value, leave unchanged, replace, or abort</returns> public static ConflictResolution CallbackConflictHandler(IntPtr pCtx, ConflictReason eConflict, IntPtr pIter) { try { // get our context object ctx = null; if (pCtx != IntPtr.Zero) { System.Runtime.InteropServices.GCHandle gch = System.Runtime.InteropServices.GCHandle.FromIntPtr(pCtx); ctx = gch.Target; } var dbSync = ctx as DBSync; var dbConnection = dbSync.db; // Note: pIter should be converted to a Sqlite3ChangesetIterator to allow using ChangeSetOp,ChangeSet*Value methods // however must actually create Sqlite3ConflictChangesetIterator to avoid unnecessary finalize of iterator var iter = new Sqlite3ConflictChangesetIterator(pIter); // get table, column, and other information about this change ChangeSetOp(iter, out string tableName, out int columnCount, out ActionCode op, out bool indirect); // get information about columns in current table var tableInfo = dbSync.tableInformation[tableName]; object currentValue, oldValue, newValue; // get primary key value for current row - NOTE: we support a single or multiple column PK var pkValues = SQLiteSession.ChangeSetPrimaryKeyValues(iter); if (pkValues.Count < 1) { // we require a primary key and failed to get it! return(ConflictResolution.SQLITE_CHANGESET_ABORT); } PrimaryKeyData[] pk = new PrimaryKeyData[pkValues.Count]; for (var i = 0; i < pkValues.Count; i++) { // get primary key column's value pk[i].value = pkValues[i].Item1.GetValue(); // get primary key column's name from index for table pk[i].name = tableInfo[pkValues[i].Item2].Name; } // log some debug information about conflict (data to sync) { var sb = new StringBuilder($"Conflict ({eConflict}) in table '{tableName}' for pk='{pk}', op={op}, Row values:"); sb.AppendLine(); for (int i = 0; i < tableInfo.Count; i++) { GetConflictValues(ref iter, op, i, out currentValue, out oldValue, out newValue); sb.AppendLine($"[{i}] current={currentValue}, old={oldValue}, new={newValue}"); } logger.Debug(sb.ToString()); } // TODO handle constraint violations better if ((eConflict == ConflictReason.SQLITE_CHANGESET_CONSTRAINT) || (eConflict == ConflictReason.SQLITE_CHANGESET_FOREIGN_KEY)) { return(ConflictResolution.SQLITE_CHANGESET_ABORT); } // if op==insert then in current db and not in replica // so is this a new record (keep) or a deleted record in replica (do we want to replicate delete?) // newValue (and currentValue) has current's value including timestamp if available // oldValue is null, need to query replica's shadow table to see if deleted (and timestamp) or if // not found then assume a new row (so keep) // // if op==delete then in replica db and not in current // so is this a new record (insert) or a deleted record in current (do we want keep delete?) // oldValue has the replica's value including timestamp if available // newValue (and currentValue) are null, need to query current's shadow table to see if deleted (and timestamp) or if // not found then assume a new row (so insert) // // if op==update then which do we keep, current or replica => we can do this per column or per row // oldValue and newValue are null then column has no conflict (currentValue has column's value) // oldValue is replica's value, newValue (and currentValue) is current's value // // TODO - how to handle constraint and foreign key violations, structuring order of replicating tables // should avoid these in most cases, for now we punt and abort! // get timestamps from current and replica row, if have a timestamp (-1 for column if not one) int timestampColumn = FindColumn(tableInfo, "timestamp"); DateTime?currentTimestamp = null, replicaTimestamp = null; if (timestampColumn >= 0) { GetConflictValues(ref iter, op, timestampColumn, out currentValue, out oldValue, out newValue); if (op == ActionCode.SQLITE_UPDATE) // both are in conflict values { currentTimestamp = currentValue as DateTime?; replicaTimestamp = (oldValue as DateTime?) ?? currentTimestamp; // no oldValue indicates identical values } else // must query a shadow table { DateTime?shadowTimestamp = null; var query = new StringBuilder($"SELECT [del_timestamp] FROM "); query.Append((op == ActionCode.SQLITE_INSERT) ? "replica" : "main"); query.Append("."); query.Append(tableName); query.Append("_ WHERE "); // shadow table has _ suffix, e.g. [MyTable] shadow table is [MyTable_] var keys = new List <object>(pk.Length); for (var i = 0; i < pk.Length; i++) { if (i != 0) { query.Append(" AND "); } query.Append(pk[i].name); query.Append("=?"); keys.Add(pk[i].value); } query.Append(";"); try { // if no shadow table or not a deleted row then this will throw since doesn't exist shadowTimestamp = dbConnection.ExecuteScalar <DateTime>(query.ToString(), keys.ToArray()); } catch (Exception) { /* swallow error if no shadow record */ } if (op == ActionCode.SQLITE_INSERT) { if (currentValue != null) { currentTimestamp = new DateTime((long)currentValue); } replicaTimestamp = shadowTimestamp; } else { currentTimestamp = shadowTimestamp; if (oldValue != null) { replicaTimestamp = new DateTime((long)oldValue); } } } } // Note: the way we are using Session, we explicitly make the changes // in all cases except on Update where we currently keep or replace whole row // TODO allow selection of individual fields? which would require us to manually // do the update - hence also returning SQLITE_CHANGESET_OMIT var action = ConflictResolution.SQLITE_CHANGESET_OMIT; if (op == ActionCode.SQLITE_INSERT) { // keep current value unless replicaTimestamp is newer if ((currentTimestamp != null) && (replicaTimestamp != null) && (currentTimestamp < replicaTimestamp)) { // TODO - we need to actually issue a DELETE! // TODO - then update shadow table so del_timestamp matches replica } } else if (op == ActionCode.SQLITE_DELETE) { // indicate nothing to do, as we have to manually insert if desired to add action = ConflictResolution.SQLITE_CHANGESET_OMIT; // are we a new row only in replica? or was modified in replica after we deleted so re-add modified value? if ((currentTimestamp == null) || ((replicaTimestamp != null) && (currentTimestamp < replicaTimestamp))) { // then insert it { var sb = new StringBuilder("INSERT INTO "); sb.Append(tableName); sb.Append(" ("); for (var i = 0; i < tableInfo.Count; i++) { if (i != 0) { sb.Append(", "); } sb.Append("["); // double quoted "column names" are standard, but [name] works and easier to read sb.Append(tableInfo[i].Name); // if any chance of user driven names then use " and escape internal "s sb.Append("]"); } sb.Append(") VALUES ("); var values = new object[tableInfo.Count]; for (var i = 0; i < tableInfo.Count; i++) { if (i != 0) { sb.Append(", "); } sb.Append("?"); oldValue = null; if (ChangeSetOldValue(iter, i, out SQLiteNetSessionModule.SQLiteValue value) == SQLite3.Result.OK) { if (value.HasValue()) { oldValue = value.GetValue(); } } values[i] = oldValue; } sb.Append(");"); logger.Debug("Inserting: '{0}' with values={1}", sb.ToString(), statePrinter.PrintObject(values)); dbConnection.Execute(sb.ToString(), values); } } // else assume we deleted on purpose so leave that way } else if (op == ActionCode.SQLITE_UPDATE) { // TODO allow more fine grained selection - for now simply keep latest change if ((currentTimestamp == null) || (replicaTimestamp == null) || (currentTimestamp >= replicaTimestamp)) { action = ConflictResolution.SQLITE_CHANGESET_OMIT; } else { action = ConflictResolution.SQLITE_CHANGESET_REPLACE; } } else { logger.Error($"Unknown or unexpected op {op} - aborting!"); } // replace only valid on conflict or data //if ((eConflict == ConflictReason.SQLITE_CHANGESET_CONFLICT) || (eConflict == ConflictReason.SQLITE_CHANGESET_DATA)) logger.Debug($"returning action={action}\r\n"); return(action); } catch (Exception e) { logger.Error(e, "Unexpected error during conflict handler callback."); return(ConflictResolution.SQLITE_CHANGESET_ABORT); } }
/// <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"); } }