public List <Record> GetModifiedRecords(string identityId, string datasetName)
{
lock (sqlite_lock)
{
List <Record> records = new List <Record>();
SQLiteStatement stmt = null;
try
{
stmt = db.Prepare(
RecordColumns.BuildQuery(
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName AND " +
RecordColumns.MODIFIED + " = @whereModified"
));
stmt.BindText(1, identityId);
stmt.BindText(2, datasetName);
stmt.BindInt(3, 1);
while (stmt.Read())
{
records.Add(this.SqliteStmtToRecord(stmt));
}
}
finally
{
stmt.FinalizeStm();
}
return(records);
}
}
public Record GetRecord(string identityId, string datasetName, string key)
{
lock (sqlite_lock)
{
Record record = null;
SQLiteStatement stmt = null;
try
{
stmt = db.Prepare(RecordColumns.BuildQuery(
RecordColumns.IDENTITY_ID + " = @identityId AND " +
RecordColumns.DATASET_NAME + " = @datasetName AND " +
RecordColumns.KEY + " = @key"
));
stmt.BindText(1, identityId);
stmt.BindText(2, datasetName);
stmt.BindText(3, key);
if (stmt.Read())
{
record = this.SqliteStmtToRecord(stmt);
}
}
finally
{
stmt.FinalizeStm();
}
return(record);
}
}
internal void UpdateOrInsertRecord(string identityId, string datasetName, Record record)
{
lock (sqlite_lock)
{
string checkRecordExistsQuery = "SELECT COUNT(*) FROM " + SQLiteLocalStorage.TABLE_RECORDS + " WHERE " +
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName AND " +
RecordColumns.KEY + " = @whereKey ";
bool recordsFound = false;
using (var command = connection.CreateCommand())
{
command.CommandText = checkRecordExistsQuery;
BindData(command, identityId, datasetName, record.Key);
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
recordsFound = reader.GetInt32(0) > 0;
}
}
}
if (recordsFound)
{
string updateRecordQuery =
RecordColumns.BuildUpdate(
new string[] {
RecordColumns.VALUE,
RecordColumns.SYNC_COUNT,
RecordColumns.MODIFIED,
RecordColumns.LAST_MODIFIED_TIMESTAMP,
RecordColumns.LAST_MODIFIED_BY,
RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP
},
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName AND " +
RecordColumns.KEY + " = @whereKey "
);
using (var command = connection.CreateCommand())
{
command.CommandText = updateRecordQuery;
BindData(command, record.Value, record.SyncCount, record.IsModified ? 1 : 0, record.LastModifiedDate, record.LastModifiedBy, record.DeviceLastModifiedDate, identityId, datasetName, record.Key);
command.ExecuteNonQuery();
}
}
else
{
string insertRecord = RecordColumns.BuildInsert();
using (var command = new SQLiteCommand(insertRecord, connection))
{
BindData(command, identityId, datasetName, record.Key, record.Value, record.SyncCount, record.LastModifiedDate, record.LastModifiedBy, record.DeviceLastModifiedDate, record.IsModified ? 1 : 0);
command.ExecuteNonQuery();
}
}
}
}
public void WipeData()
{
lock (sqlite_lock)
{
db.Exec(DatasetColumns.BuildDelete(null));
db.Exec(RecordColumns.BuildDelete(null));
}
}
public override void WipeData()
{
lock (SQLiteDatabase.SQLiteLock)
{
db.Exec(DatasetColumns.BuildDelete(null));
db.Exec(RecordColumns.BuildDelete(null));
}
}
internal void UpdateOrInsertRecord(string identityId, string datasetName, Record record)
{
lock (sqlite_lock)
{
string checkRecordExistsQuery = "SELECT count(*) FROM " + SQLiteLocalStorage.TABLE_RECORDS + " WHERE " +
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName AND " +
RecordColumns.KEY + " = @whereKey ";
bool recordsFound = false;
Sqlite3Statement stmt = null;
try
{
stmt = ExecuteQuery(checkRecordExistsQuery, identityId, datasetName, record.Key);
while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW)
{
recordsFound = Sqlite3.sqlite3_column_int(stmt, 0) > 0;
}
}
finally
{
if (stmt != null)
{
Sqlite3.sqlite3_finalize(stmt);
}
}
if (recordsFound)
{
string updateRecordQuery =
RecordColumns.BuildUpdate(
new string[] {
RecordColumns.VALUE,
RecordColumns.SYNC_COUNT,
RecordColumns.MODIFIED,
RecordColumns.LAST_MODIFIED_TIMESTAMP,
RecordColumns.LAST_MODIFIED_BY,
RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP
},
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName AND " +
RecordColumns.KEY + " = @whereKey "
);
Execute(updateRecordQuery, record.Value, record.SyncCount, record.IsModified ? 1 : 0, record.LastModifiedDate, record.LastModifiedBy, record.DeviceLastModifiedDate, identityId, datasetName, record.Key);
}
else
{
string insertRecord = RecordColumns.BuildInsert();
Execute(insertRecord, identityId, datasetName, record.Key, record.Value, record.SyncCount, record.LastModifiedDate, record.LastModifiedBy, record.DeviceLastModifiedDate, record.IsModified ? 1 : 0);
}
}
}
internal void UpdateOrInsertRecord(string identityId, string datasetName, Record record)
{
lock (sqlite_lock)
{
string checkRecordExistsQuery = "SELECT count(*) FROM " + SQLiteLocalStorage.TABLE_RECORDS + " WHERE " +
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName AND " +
RecordColumns.KEY + " = @whereKey ";
bool recordsFound = false;
using (var sqliteStatement = connection.Prepare(checkRecordExistsQuery))
{
BindData(sqliteStatement, identityId, datasetName, record.Key);
var result = sqliteStatement.Step();
recordsFound = sqliteStatement.GetInteger(0) > 0;
}
if (recordsFound)
{
string updateRecordQuery =
RecordColumns.BuildUpdate(
new string[] {
RecordColumns.VALUE,
RecordColumns.SYNC_COUNT,
RecordColumns.MODIFIED,
RecordColumns.LAST_MODIFIED_TIMESTAMP,
RecordColumns.LAST_MODIFIED_BY,
RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP
},
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName AND " +
RecordColumns.KEY + " = @whereKey "
);
using (var sqliteStatement = connection.Prepare(updateRecordQuery))
{
BindData(sqliteStatement, record.Value, record.SyncCount, record.IsModified ? 1 : 0, record.LastModifiedDate, record.LastModifiedBy, record.DeviceLastModifiedDate, identityId, datasetName, record.Key);
var result = sqliteStatement.Step();
}
}
else
{
string insertRecord = RecordColumns.BuildInsert();
using (var sqliteStatement = connection.Prepare(insertRecord))
{
BindData(sqliteStatement, identityId, datasetName, record.Key, record.Value, record.SyncCount, record.LastModifiedDate, record.LastModifiedBy, record.DeviceLastModifiedDate, record.IsModified ? 1 : 0);
sqliteStatement.Step();
}
}
}
}
/// <summary>
/// Retrieves a list of locally modified records since last successful sync
/// operation.
/// </summary>
/// <returns>a list of locally modified records</returns>
/// <param name="identityId">Identity identifier.</param>
/// <param name="datasetName">Dataset name.</param>
public List <Record> GetModifiedRecords(string identityId, string datasetName)
{
lock (sqlite_lock)
{
string query =
RecordColumns.BuildQuery(
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName AND " +
RecordColumns.MODIFIED + " = @whereModified "
);
return(GetModifiedRecordsHelper(query, identityId, datasetName, 1));;
}
}
/// <summary>
/// Gets a raw record from local store. If the dataset/key combo doesn't
/// // exist, null will be returned.
/// </summary>
/// <returns>a Record object if found, null otherwise.</returns>
/// <param name="identityId">Identity identifier.</param>
/// <param name="datasetName">Dataset name.</param>
/// <param name="key">Key for the record.</param>
public Record GetRecord(string identityId, string datasetName, string key)
{
lock (sqlite_lock)
{
Record record = null;
string query = RecordColumns.BuildQuery(
RecordColumns.IDENTITY_ID + " = @identityId AND " +
RecordColumns.DATASET_NAME + " = @datasetName AND " +
RecordColumns.KEY + " = @key "
);
record = GetRecordHelper(query, identityId, datasetName, key);
return(record);
}
}
/// <summary>
/// Wipes all locally cached data including dataset metadata and records. All
/// opened dataset handler should not perform further operations to avoid
/// inconsistent state.
/// </summary>
public void WipeData()
{
lock (sqlite_lock)
{
string query1 = DatasetColumns.BuildDelete(null);
string query2 = RecordColumns.BuildDelete(null);
ExecuteMultipleHelper(new List <Statement>()
{
new Statement {
Query = query1
}, new Statement {
Query = query2
}
});
}
}
private void DeleteDataset(string identityId, string datasetName, List <Statement> additionalStatements)
{
lock (sqlite_lock)
{
string deleteRecordsQuery =
RecordColumns.BuildDelete(
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName "
);
Statement s1 = new Statement
{
Query = deleteRecordsQuery,
Parameters = new string[] { identityId, datasetName }
};
string deleteDatasetQuery =
DatasetColumns.BuildUpdate(
new string[] {
DatasetColumns.LAST_MODIFIED_TIMESTAMP,
DatasetColumns.LAST_SYNC_COUNT
},
DatasetColumns.IDENTITY_ID + " = @whereIdentityId AND " +
DatasetColumns.DATASET_NAME + " = @whereDatasetName "
);
Statement s2 = new Statement
{
Query = deleteDatasetQuery,
Parameters = new object[] { AWSSDKUtils.CorrectedUtcNow.ToLocalTime(), -1, identityId, datasetName }
};
List <Statement> statementsToExecute = new List <Statement>()
{
s1, s2
};
if (additionalStatements != null)
{
statementsToExecute.AddRange(additionalStatements);
}
ExecuteMultipleHelper(statementsToExecute);
}
}
private bool PutValueHelper(string identityId, string datasetName, string key, string value)
{
lock (sqlite_lock)
{
Record record = GetRecord(identityId, datasetName, key);
if (record != null && string.Equals(record.Value, value))
{
return(true);
}
if (record == null)
{
string insertRecord = RecordColumns.BuildInsert();
ExecuteMultipleHelper(new List <Statement> {
new Statement {
Query = insertRecord,
Parameters = new object[] { identityId, datasetName, key, value, 0, AWSSDKUtils.CorrectedUtcNow.ToLocalTime(), string.Empty, AWSSDKUtils.CorrectedUtcNow.ToLocalTime(), 1 }
}
});
return(true);
}
else
{
string insertRecord =
RecordColumns.BuildUpdate(
new string[] {
RecordColumns.IDENTITY_ID, RecordColumns.DATASET_NAME, RecordColumns.KEY,
RecordColumns.VALUE, RecordColumns.MODIFIED, RecordColumns.SYNC_COUNT,
RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP
},
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName AND " +
RecordColumns.KEY + " = @whereKey "
);
ExecuteMultipleHelper(new List <Statement> {
new Statement {
Query = insertRecord,
Parameters = new object[] { identityId, datasetName, key, value, 1, record.SyncCount, AWSSDKUtils.CorrectedUtcNow.ToLocalTime(), identityId, datasetName, key }
}
});
return(true);
}
}
}
public void DeleteDataset(string identityId, string datasetName)
{
lock (sqlite_lock)
{
SQLiteStatement stmt = null;
try
{
stmt = db.Prepare(
RecordColumns.BuildDelete(
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName"
));
stmt.BindText(1, identityId);
stmt.BindText(2, datasetName);
stmt.Step();
stmt.FinalizeStm();
stmt = db.Prepare(
DatasetColumns.BuildUpdate(
new string[] {
DatasetColumns.LAST_MODIFIED_TIMESTAMP,
DatasetColumns.LAST_SYNC_COUNT
},
DatasetColumns.IDENTITY_ID + " = @whereIdentityId AND " +
DatasetColumns.DATASET_NAME + " = @whereDatasetName"
));
stmt.BindDateTime(1, DateTime.Now);
stmt.BindInt(2, -1);
stmt.BindText(3, identityId);
stmt.BindText(4, datasetName);
stmt.Step();
}
finally
{
stmt.FinalizeStm();
}
}
}
public override void ChangeIdentityId(string oldIdentityId, string newIdentityId)
{
lock (SQLiteDatabase.SQLiteLock)
{
SQLiteStatement stmt = null;
try
{
stmt = db.Prepare(
DatasetColumns.BuildUpdate(
new string[] { DatasetColumns.IDENTITY_ID },
DatasetColumns.IDENTITY_ID + " = @whereIdentityId"
));
stmt.BindText(1, newIdentityId);
stmt.BindText(2, oldIdentityId);
stmt.Step();
stmt.FinalizeStm();
stmt = db.Prepare(
RecordColumns.BuildUpdate(
new string[] { RecordColumns.IDENTITY_ID },
RecordColumns.IDENTITY_ID + " = @whereIdentityId"
));
stmt.BindText(1, newIdentityId);
stmt.BindText(2, oldIdentityId);
stmt.Step();
}
finally
{
stmt.FinalizeStm();
}
}
}
private bool PutValueInternal(string identityId, string datasetName, string key, string value)
{
lock (sqlite_lock)
{
Record record = this.GetRecord(identityId, datasetName, key);
if (record != null && string.Equals(record.Value, value))
{
return(true);
}
SQLiteStatement stmt = null;
try
{
if (record == null)
{
stmt = db.Prepare(RecordColumns.BuildInsert());
stmt.BindText(1, identityId);
stmt.BindText(2, datasetName);
stmt.BindText(3, key);
stmt.BindText(4, value);
stmt.BindInt(5, 0);
stmt.BindDateTime(6, DateTime.Now);
stmt.BindText(7, "");
stmt.BindInt(8, 0);
stmt.BindInt(9, 1);
stmt.Step();
return(true);
}
else
{
stmt = db.Prepare(
RecordColumns.BuildUpdate(
new string[] {
RecordColumns.IDENTITY_ID, RecordColumns.DATASET_NAME, RecordColumns.KEY,
RecordColumns.VALUE, RecordColumns.MODIFIED, RecordColumns.SYNC_COUNT,
RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP
},
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName AND " +
RecordColumns.KEY + " = @whereKey "
));
stmt.BindText(1, identityId);
stmt.BindText(2, datasetName);
stmt.BindText(3, key);
stmt.BindText(4, value);
stmt.BindInt(5, 1);
stmt.BindInt(6, record.SyncCount);
stmt.BindDateTime(7, DateTime.Now);
stmt.BindText(8, identityId);
stmt.BindText(9, datasetName);
stmt.BindText(10, key);
stmt.Step();
return(true);
}
}
finally
{
stmt.FinalizeStm();
}
}
}
/// <summary>
/// Reparents all datasets from old identity id to a new one.
/// </summary>
/// <param name="oldIdentityId">Old identity identifier.</param>
/// <param name="newIdentityId">New identity identifier.</param>
public void ChangeIdentityId(string oldIdentityId, string newIdentityId)
{
_logger.DebugFormat("Reparenting datasets from {0} to {1}", oldIdentityId, newIdentityId);
lock (sqlite_lock)
{
List <Statement> statements = new List <Statement>();
// if oldIdentityId is unknown, aka the dataset is created prior to
// having a cognito id, just reparent datasets from unknown to
// newIdentityId
if (DatasetUtils.UNKNOWN_IDENTITY_ID == oldIdentityId)
{
HashSet <string> commonDatasetNames = GetCommonDatasetNames(oldIdentityId, newIdentityId);
// append UNKNOWN to the name of all non unique datasets
foreach (String oldDatasetName in commonDatasetNames)
{
string updateDatasetQuery = "UPDATE " + TABLE_DATASETS
+ " SET " + DatasetColumns.DATASET_NAME + " = @" + DatasetColumns.DATASET_NAME
+ " WHERE " + DatasetColumns.IDENTITY_ID + " = @" + DatasetColumns.IDENTITY_ID
+ " AND " + DatasetColumns.DATASET_NAME + " = @old" + DatasetColumns.DATASET_NAME + " ";
string timestamp = AWSSDKUtils.ConvertToUnixEpochMilliSeconds(AWSSDKUtils.CorrectedUtcNow).ToString(CultureInfo.InvariantCulture);
Statement updateDatasetStatement = new Statement()
{
Query = updateDatasetQuery,
Parameters = new string[] { oldDatasetName + "." + oldIdentityId + "-" + timestamp, oldIdentityId, oldDatasetName }
};
statements.Add(updateDatasetStatement);
string updateRecordsQuery = "UPDATE " + TABLE_RECORDS
+ " SET " + RecordColumns.DATASET_NAME + " = @" + RecordColumns.DATASET_NAME
+ " WHERE " + RecordColumns.IDENTITY_ID + " = @" + RecordColumns.IDENTITY_ID
+ " AND " + RecordColumns.DATASET_NAME + " = @old" + RecordColumns.DATASET_NAME + " ";
Statement updateRecordsStatement = new Statement()
{
Query = updateRecordsQuery,
Parameters = new string[] { oldDatasetName + "." + oldIdentityId + "-" + timestamp, oldIdentityId, oldDatasetName }
};
statements.Add(updateRecordsStatement);
}
string updateIdentityDatasetQuery = DatasetColumns.BuildUpdate(
new string[] { DatasetColumns.IDENTITY_ID },
DatasetColumns.IDENTITY_ID + " = @oldIdentityId "
);
Statement UpdateIdentityDatasetStatement = new Statement()
{
Query = updateIdentityDatasetQuery,
Parameters = new string[] { newIdentityId, oldIdentityId }
};
statements.Add(UpdateIdentityDatasetStatement);
string updateRecordsIdentityQuery = RecordColumns.BuildUpdate(
new string[] { RecordColumns.IDENTITY_ID },
RecordColumns.IDENTITY_ID + " = @oldIdentityId "
);
Statement UpdateIdentityRecordsStatement = new Statement()
{
Query = updateRecordsIdentityQuery,
Parameters = new string[] { newIdentityId, oldIdentityId }
};
statements.Add(UpdateIdentityRecordsStatement);
}
else
{
// 1. copy oldIdentityId/dataset to newIdentityId/dataset
// datasets table
string copyDatasetToNewIdentity = "INSERT INTO " + TABLE_DATASETS + "("
+ DatasetColumns.IDENTITY_ID + ","
+ DatasetColumns.DATASET_NAME + ","
+ DatasetColumns.CREATION_TIMESTAMP + ","
+ DatasetColumns.STORAGE_SIZE_BYTES + ","
+ DatasetColumns.RECORD_COUNT
// last sync count is reset to default 0
+ ")"
+ " SELECT "
+ "'" + newIdentityId + "'," // assign new owner
+ DatasetColumns.DATASET_NAME + ","
+ DatasetColumns.CREATION_TIMESTAMP + ","
+ DatasetColumns.STORAGE_SIZE_BYTES + ","
+ DatasetColumns.RECORD_COUNT
+ " FROM " + TABLE_DATASETS
+ " WHERE " + DatasetColumns.IDENTITY_ID + " = @" + DatasetColumns.IDENTITY_ID + " ";
statements.Add(new Statement
{
Query = copyDatasetToNewIdentity,
Parameters = new string[] { oldIdentityId }
});
// records table
string copyRecordsToNewIdentity = "INSERT INTO " + TABLE_RECORDS + "("
+ RecordColumns.IDENTITY_ID + ","
+ RecordColumns.DATASET_NAME + ","
+ RecordColumns.KEY + ","
+ RecordColumns.VALUE + ","
// sync count is resset to default 0
+ RecordColumns.LAST_MODIFIED_TIMESTAMP + ","
+ RecordColumns.LAST_MODIFIED_BY + ","
+ RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP
// modified is reset to default 1 (dirty)
+ ")"
+ " SELECT "
+ "'" + newIdentityId + "'," // assign new owner
+ RecordColumns.DATASET_NAME + ","
+ RecordColumns.KEY + ","
+ RecordColumns.VALUE + ","
+ RecordColumns.LAST_MODIFIED_TIMESTAMP + ","
+ RecordColumns.LAST_MODIFIED_BY + ","
+ RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP
+ " FROM " + TABLE_RECORDS
+ " WHERE " + RecordColumns.IDENTITY_ID + " = @" + RecordColumns.IDENTITY_ID + " ";
statements.Add(new Statement
{
Query = copyRecordsToNewIdentity,
Parameters = new string[] { oldIdentityId }
});
// 2. rename oldIdentityId/dataset to
// newIdentityId/dataset.oldIdentityId
// datasets table
string updateDatasetToNewIdentityQuery = "UPDATE " + TABLE_DATASETS
+ " SET "
+ DatasetColumns.IDENTITY_ID + " = '" + newIdentityId + "', "
+ DatasetColumns.DATASET_NAME + " = "
+ DatasetColumns.DATASET_NAME + " || '." + oldIdentityId + "', "
+ DatasetColumns.LAST_SYNC_COUNT + " = 1" // set the sync count to one, because that is what the server did
+ " WHERE " + DatasetColumns.IDENTITY_ID + " = @" + DatasetColumns.IDENTITY_ID + " ";
statements.Add(new Statement
{
Query = updateDatasetToNewIdentityQuery,
Parameters = new string[] { oldIdentityId }
});
// records table
string updateRecordsToNewIdentityQuery = "UPDATE " + TABLE_RECORDS
+ " SET "
+ RecordColumns.IDENTITY_ID + " = '" + newIdentityId + "', "
+ RecordColumns.DATASET_NAME + " = "
+ RecordColumns.DATASET_NAME + " || '." + oldIdentityId + "'"
+ " WHERE " + RecordColumns.IDENTITY_ID + " = @" + RecordColumns.IDENTITY_ID + " ";
statements.Add(new Statement
{
Query = updateRecordsToNewIdentityQuery,
Parameters = new string[] { oldIdentityId }
});
}
//execute all of them
ExecuteMultipleHelper(statements);
}
}
public static string BuildInsert()
{
return(RecordColumns.BuildInsert(ALL));
}
private void UpdateOrInsertRecord(string identityId, string datasetName, Record record)
{
lock (sqlite_lock)
{
SQLiteStatement stmt = null;
try
{
stmt = db.Prepare(
RecordColumns.BuildQuery(
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName AND " +
RecordColumns.KEY + " = @whereKey "
));
stmt.BindText(1, identityId);
stmt.BindText(2, datasetName);
stmt.BindText(3, record.Key);
bool recordsFound = false;
while (stmt.Read())
{
recordsFound = true;
}
stmt.FinalizeStm();
if (recordsFound)
{
stmt = db.Prepare(
RecordColumns.BuildUpdate(
new string[] {
RecordColumns.VALUE,
RecordColumns.SYNC_COUNT,
RecordColumns.MODIFIED,
RecordColumns.LAST_MODIFIED_TIMESTAMP,
RecordColumns.LAST_MODIFIED_BY,
RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP
},
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName AND " +
RecordColumns.KEY + " = @whereKey "
));
stmt.BindText(1, record.Value);
stmt.BindInt(2, record.SyncCount);
stmt.BindInt(3, record.IsModified ? 1 : 0);
stmt.BindDateTime(4, record.LastModifiedDate);
stmt.BindText(5, record.LastModifiedBy);
stmt.BindDateTime(6, record.DeviceLastModifiedDate);
stmt.BindText(7, identityId);
stmt.BindText(8, datasetName);
stmt.BindText(9, record.Key);
stmt.Step();
}
else
{
stmt = db.Prepare(RecordColumns.BuildInsert());
stmt.BindText(1, identityId);
stmt.BindText(2, datasetName);
stmt.BindText(3, record.Key);
stmt.BindText(4, record.Value);
stmt.BindInt(5, record.SyncCount);
stmt.BindDateTime(6, record.LastModifiedDate);
stmt.BindText(7, record.LastModifiedBy);
stmt.BindDateTime(8, record.DeviceLastModifiedDate);
stmt.BindInt(9, record.IsModified ? 1 : 0);
stmt.Step();
}
}
finally
{
stmt.FinalizeStm();
}
}
}
public void ChangeIdentityId(string oldIdentityId, string newIdentityId)
{
Debug.Log("Reparenting datasets from " + oldIdentityId + " to " + newIdentityId);
GetCommonDatasetNames(oldIdentityId, newIdentityId);
lock (sqlite_lock)
{
SQLiteStatement stmt = null;
try
{
// if oldIdentityId is unknown, aka the dataset is created prior to
// having a cognito id, just reparent datasets from unknown to
// newIdentityId
if (DatasetUtils.UNKNOWN_IDENTITY_ID == oldIdentityId)
{
HashSet <string> commonDatasetNames = GetCommonDatasetNames(oldIdentityId, newIdentityId);
// append UNKNOWN to the name of all non unique datasets
foreach (String oldDatasetName in commonDatasetNames)
{
stmt = db.Prepare("UPDATE " + TABLE_DATASETS
+ " SET " + DatasetColumns.DATASET_NAME + " = ?"
+ " WHERE " + DatasetColumns.IDENTITY_ID + " = ?"
+ " AND " + DatasetColumns.DATASET_NAME + " = ?"
);
DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);
string timestamp = ((DateTime.UtcNow - epoch).TotalSeconds).ToString();
stmt.BindText(1, oldDatasetName + "." + oldIdentityId + "-" + timestamp);
stmt.BindText(2, oldIdentityId);
stmt.BindText(3, oldDatasetName);
stmt.Step();
stmt.FinalizeStm();
stmt = db.Prepare("UPDATE " + TABLE_RECORDS
+ " SET " + RecordColumns.DATASET_NAME + " = ?"
+ " WHERE " + RecordColumns.IDENTITY_ID + " = ?"
+ " AND " + RecordColumns.DATASET_NAME + " = ?"
);
stmt.BindText(1, oldDatasetName + "." + oldIdentityId + "-" + timestamp);
stmt.BindText(2, oldIdentityId);
stmt.BindText(3, oldDatasetName);
stmt.Step();
stmt.FinalizeStm();
}
stmt = db.Prepare(
DatasetColumns.BuildUpdate(
new string[] { DatasetColumns.IDENTITY_ID },
DatasetColumns.IDENTITY_ID + " = ?"
)
);
stmt.BindText(1, newIdentityId);
stmt.BindText(2, oldIdentityId);
stmt.Step();
stmt.FinalizeStm();
stmt = db.Prepare(
RecordColumns.BuildUpdate(
new string[] { RecordColumns.IDENTITY_ID },
RecordColumns.IDENTITY_ID + " = ?"
)
);
stmt.BindText(1, newIdentityId);
stmt.BindText(2, oldIdentityId);
stmt.Step();
}
else
{
// 1. copy oldIdentityId/dataset to newIdentityId/dataset
// datasets table
stmt = db.Prepare("INSERT INTO " + TABLE_DATASETS + "("
+ DatasetColumns.IDENTITY_ID + ","
+ DatasetColumns.DATASET_NAME + ","
+ DatasetColumns.CREATION_TIMESTAMP + ","
+ DatasetColumns.STORAGE_SIZE_BYTES + ","
+ DatasetColumns.RECORD_COUNT
// last sync count is reset to default 0
+ ")"
+ " SELECT "
+ "'" + newIdentityId + "'," // assign new owner
+ DatasetColumns.DATASET_NAME + ","
+ DatasetColumns.CREATION_TIMESTAMP + ","
+ DatasetColumns.STORAGE_SIZE_BYTES + ","
+ DatasetColumns.RECORD_COUNT
+ " FROM " + TABLE_DATASETS
+ " WHERE " + DatasetColumns.IDENTITY_ID + " = ?"
);
stmt.BindText(1, oldIdentityId);
stmt.Step();
stmt.FinalizeStm();
// records table
stmt = db.Prepare("INSERT INTO " + TABLE_RECORDS + "("
+ RecordColumns.IDENTITY_ID + ","
+ RecordColumns.DATASET_NAME + ","
+ RecordColumns.KEY + ","
+ RecordColumns.VALUE + ","
// sync count is resset to default 0
+ RecordColumns.LAST_MODIFIED_TIMESTAMP + ","
+ RecordColumns.LAST_MODIFIED_BY + ","
+ RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP
// modified is reset to default 1 (dirty)
+ ")"
+ " SELECT "
+ "'" + newIdentityId + "'," // assign new owner
+ RecordColumns.DATASET_NAME + ","
+ RecordColumns.KEY + ","
+ RecordColumns.VALUE + ","
+ RecordColumns.LAST_MODIFIED_TIMESTAMP + ","
+ RecordColumns.LAST_MODIFIED_BY + ","
+ RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP
+ " FROM " + TABLE_RECORDS
+ " WHERE " + RecordColumns.IDENTITY_ID + " = ?"
);
stmt.BindText(1, oldIdentityId);
stmt.Step();
stmt.FinalizeStm();
// 2. rename oldIdentityId/dataset to
// newIdentityId/dataset.oldIdentityId
// datasets table
stmt = db.Prepare("UPDATE " + TABLE_DATASETS
+ " SET "
+ DatasetColumns.IDENTITY_ID + " = '" + newIdentityId + "', "
+ DatasetColumns.DATASET_NAME + " = "
+ DatasetColumns.DATASET_NAME + " || '." + oldIdentityId + "'"
+ " WHERE " + DatasetColumns.IDENTITY_ID + " = ?"
);
stmt.BindText(1, oldIdentityId);
stmt.Step();
stmt.FinalizeStm();
// records table
stmt = db.Prepare("UPDATE " + TABLE_RECORDS
+ " SET "
+ RecordColumns.IDENTITY_ID + " = '" + newIdentityId + "', "
+ RecordColumns.DATASET_NAME + " = "
+ RecordColumns.DATASET_NAME + " || '." + oldIdentityId + "'"
+ " WHERE " + RecordColumns.IDENTITY_ID + " = ?"
);
stmt.BindText(1, oldIdentityId);
stmt.Step();
}
}
finally
{
stmt.FinalizeStm();
}
}
}