public List <DatasetMetadata> GetDatasetMetadata(string identityId)
{
lock (sqlite_lock)
{
List <DatasetMetadata> datasets = new List <DatasetMetadata>();
SQLiteStatement stmt = null;
try
{
stmt = db.Prepare(DatasetColumns.BuildQuery(
DatasetColumns.IDENTITY_ID + " = @whereIdentityId"
));
stmt.BindText(1, identityId);
while (stmt.Read())
{
datasets.Add(this.SqliteStmtToDatasetMetadata(stmt));
}
}
finally
{
stmt.FinalizeStm();
}
return(datasets);
}
}
public long GetLastSyncCount(string identityId, string datasetName)
{
long lastSyncCount = 0;
lock (sqlite_lock)
{
SQLiteStatement stmt = null;
try
{
stmt = db.Prepare(
DatasetColumns.BuildQuery(
DatasetColumns.IDENTITY_ID + " = @whereIdentityId AND " +
DatasetColumns.DATASET_NAME + " = @whereDatasetName"
));
stmt.BindText(1, identityId);
stmt.BindText(2, datasetName);
if (stmt.Read())
{
lastSyncCount = stmt.Fields[DatasetColumns.LAST_SYNC_COUNT].INTEGER;
}
}
finally
{
stmt.FinalizeStm();
}
return(lastSyncCount);
}
}
public void CreateDataset(string identityId, string datasetName)
{
lock (sqlite_lock)
{
DatasetMetadata metadata = this.GetMetadataInternal(identityId, datasetName);
if (metadata == null)
{
SQLiteStatement stmt = null;
try
{
string query = DatasetColumns.BuildInsert(
new string[] {
DatasetColumns.IDENTITY_ID,
DatasetColumns.DATASET_NAME,
DatasetColumns.CREATION_TIMESTAMP,
DatasetColumns.LAST_MODIFIED_TIMESTAMP
});
stmt = db.Prepare(query);
stmt.BindText(1, identityId);
stmt.BindText(2, datasetName);
stmt.BindDateTime(3, DateTime.Now);
stmt.BindDateTime(4, DateTime.Now);
stmt.Step();
}
finally
{
stmt.FinalizeStm();
}
}
}
}
public void UpdateLastSyncCount(string identityId, string datasetName, long lastSyncCount)
{
lock (sqlite_lock)
{
SQLiteStatement stmt = null;
try
{
stmt = db.Prepare(
DatasetColumns.BuildUpdate(
new string[] {
DatasetColumns.LAST_SYNC_COUNT,
DatasetColumns.LAST_SYNC_TIMESTAMP
},
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName"
));
stmt.BindInt(1, lastSyncCount);
stmt.BindDateTime(2, DateTime.Now);
stmt.BindText(3, identityId);
stmt.BindText(4, datasetName);
stmt.Step();
}
finally
{
stmt.FinalizeStm();
}
}
}
internal DatasetMetadata GetMetadataHelper(string identityId, string datasetName)
{
string query = DatasetColumns.BuildQuery(
DatasetColumns.IDENTITY_ID + " = @identity_id AND " +
DatasetColumns.DATASET_NAME + " = @dataset_name "
);
DatasetMetadata metadata = null;
var stmt = ExecuteQuery(query, identityId, datasetName);
try
{
while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW)
{
metadata = SqliteStmtToDatasetMetadata(stmt);
}
}
finally
{
if (stmt != null)
{
Sqlite3.sqlite3_finalize(stmt);
}
}
return(metadata);
}
private DatasetMetadata GetMetadataInternal(string identityId, string datasetName)
{
lock (SQLiteDatabase.SQLiteLock)
{
SQLiteStatement stmt = null;
try
{
stmt = db.Prepare(DatasetColumns.BuildQuery(
DatasetColumns.IDENTITY_ID + " = '" + identityId + "' AND " +
DatasetColumns.DATASET_NAME + " = '" + datasetName + "'"
));
DatasetMetadata metadata = null;
while (stmt.Read())
{
metadata = this.SqliteStmtToDatasetMetadata(stmt);
}
return(metadata);
}
finally
{
stmt.FinalizeStm();
}
}
}
public void PurgeDataset(string identityId, string datasetName)
{
lock (sqlite_lock)
{
this.DeleteDataset(identityId, datasetName);
SQLiteStatement stmt = null;
try
{
stmt = db.Prepare(
DatasetColumns.BuildDelete(
DatasetColumns.IDENTITY_ID + " = @whereIdentityId AND " +
DatasetColumns.DATASET_NAME + " = @whereDatasetName"
));
stmt.BindText(1, identityId);
stmt.BindText(2, datasetName);
stmt.Step();
}
finally
{
stmt.FinalizeStm();
}
}
}
public void UpdateLastModifiedTimestamp(string identityId, string datasetName)
{
lock (sqlite_lock)
{
SQLiteStatement stmt = null;
try
{
stmt = db.Prepare(
DatasetColumns.BuildUpdate(
new string[] { DatasetColumns.LAST_MODIFIED_TIMESTAMP },
DatasetColumns.IDENTITY_ID + " = @whereIdentityId AND " +
DatasetColumns.DATASET_NAME + " = @whereDatasetName"
));
stmt.BindDateTime(1, DateTime.Now);
stmt.BindText(2, identityId);
stmt.BindText(3, datasetName);
stmt.Step();
}
finally
{
stmt.FinalizeStm();
}
}
}
public override void WipeData()
{
lock (SQLiteDatabase.SQLiteLock)
{
db.Exec(DatasetColumns.BuildDelete(null));
db.Exec(RecordColumns.BuildDelete(null));
}
}
public void WipeData()
{
lock (sqlite_lock)
{
db.Exec(DatasetColumns.BuildDelete(null));
db.Exec(RecordColumns.BuildDelete(null));
}
}
private bool UpdateDatasetMetadataInternal(string identityId, DatasetMetadata metadata)
{
lock (sqlite_lock)
{
DatasetMetadata local = this.GetMetadataInternal(identityId, metadata.DatasetName);
SQLiteStatement stmt = null;
try
{
if (local == null)
{
stmt = db.Prepare(DatasetColumns.BuildInsert());
stmt.BindText(1, identityId);
stmt.BindText(2, metadata.DatasetName);
stmt.BindDateTime(3, metadata.CreationDate);
stmt.BindDateTime(4, metadata.LastModifiedDate);
stmt.BindInt(5, metadata.RecordCount);
stmt.BindInt(6, metadata.StorageSizeBytes);
stmt.BindInt(7, 0);
stmt.BindInt(8, 0);
stmt.BindText(9, null);
stmt.Step();
}
else
{
stmt = db.Prepare(
DatasetColumns.BuildUpdate(
new string[] {
DatasetColumns.DATASET_NAME,
DatasetColumns.CREATION_TIMESTAMP,
DatasetColumns.LAST_MODIFIED_TIMESTAMP,
DatasetColumns.LAST_MODIFIED_BY,
DatasetColumns.RECORD_COUNT,
DatasetColumns.STORAGE_SIZE_BYTES
},
DatasetColumns.IDENTITY_ID + " = @whereIdentityId AND " +
DatasetColumns.DATASET_NAME + " = @whereDatasetName"
));
stmt.BindText(1, metadata.DatasetName);
stmt.BindDateTime(2, metadata.CreationDate);
stmt.BindDateTime(3, metadata.LastModifiedDate);
stmt.BindText(4, metadata.LastModifiedBy);
stmt.BindInt(5, metadata.RecordCount);
stmt.BindInt(6, metadata.StorageSizeBytes);
stmt.BindText(7, identityId);
stmt.BindText(8, metadata.DatasetName);
stmt.Step();
}
return(true);
}
finally
{
stmt.FinalizeStm();
}
}
}
/// <summary>
/// Gets a list of dataset's metadata information.
/// </summary>
/// <returns>a list of dataset metadata</returns>
/// <param name="identityId">Identity identifier.</param>
/// <exception cref="DataStorageException"></exception>
public List <DatasetMetadata> GetDatasetMetadata(string identityId)
{
lock (sqlite_lock)
{
string query = DatasetColumns.BuildQuery(
DatasetColumns.IDENTITY_ID + " = @whereIdentityId "
);
return(GetDatasetMetadataHelper(query, identityId));
}
}
/// <summary>
/// Retrieves the last sync count. This sync count is a counter that
/// represents when the last sync happened. The counter should be updated on
/// a successful sync.
/// </summary>
/// <returns>The last sync count.</returns>
/// <param name="identityId">Identity identifier.</param>
/// <param name="datasetName">Dataset name.</param>
public long GetLastSyncCount(string identityId, string datasetName)
{
lock (sqlite_lock)
{
string query = DatasetColumns.BuildQuery(
DatasetColumns.IDENTITY_ID + " = @whereIdentityId AND " +
DatasetColumns.DATASET_NAME + " = @whereDatasetName "
);
return(GetLastSyncCountHelper(query, identityId, datasetName));
}
}
/// <summary>
/// Updates the last modified timestamp
/// </summary>
/// <param name="identityId">Identity Identifier.</param>
/// <param name="datasetName">Dataset name.</param>
public void UpdateLastModifiedTimestamp(string identityId, string datasetName)
{
lock (sqlite_lock)
{
string query =
DatasetColumns.BuildUpdate(
new string[] { DatasetColumns.LAST_MODIFIED_TIMESTAMP },
DatasetColumns.IDENTITY_ID + " = @whereIdentityId AND " +
DatasetColumns.DATASET_NAME + " = @whereDatasetName "
);
UpdateLastModifiedTimestampHelper(query, AWSSDKUtils.CorrectedUtcNow.ToLocalTime(), identityId, datasetName);
}
}
/// <summary>
/// Updates the last sync count after successful sync with the remote data
/// store.
/// </summary>
/// <param name="identityId">Identity identifier.</param>
/// <param name="datasetName">Dataset name.</param>
/// <param name="lastSyncCount">Last sync count.</param>
public void UpdateLastSyncCount(string identityId, string datasetName, long lastSyncCount)
{
lock (sqlite_lock)
{
string query = DatasetColumns.BuildUpdate(
new string[] {
DatasetColumns.LAST_SYNC_COUNT,
DatasetColumns.LAST_SYNC_TIMESTAMP
},
RecordColumns.IDENTITY_ID + " = @whereIdentityId AND " +
RecordColumns.DATASET_NAME + " = @whereDatasetName "
);
UpdateLastSyncCountHelper(query, lastSyncCount, AWSSDKUtils.CorrectedUtcNow.ToLocalTime(), identityId, datasetName);
}
}
/// <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);
}
}
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();
}
}
}
private bool UpdateDatasetMetadataInternal(string identityId, DatasetMetadata metadata)
{
lock (sqlite_lock)
{
DatasetMetadata local = GetMetadataHelper(identityId, metadata.DatasetName);
if (local == null)
{
string updateDatasetMetadataQuery = DatasetColumns.BuildInsert();
ExecuteMultipleHelper(new List <Statement>()
{
new Statement {
Query = updateDatasetMetadataQuery,
Parameters = new object[] { identityId, metadata.DatasetName, metadata.CreationDate, metadata.LastModifiedDate, metadata.RecordCount, metadata.StorageSizeBytes, 0, 0, null }
}
});
}
else
{
string updateDatasetMetadataQuery = DatasetColumns.BuildUpdate(
new string[] {
DatasetColumns.DATASET_NAME,
DatasetColumns.CREATION_TIMESTAMP,
DatasetColumns.LAST_MODIFIED_TIMESTAMP,
DatasetColumns.LAST_MODIFIED_BY,
DatasetColumns.RECORD_COUNT,
DatasetColumns.STORAGE_SIZE_BYTES
},
DatasetColumns.IDENTITY_ID + " = @whereIdentityId AND " +
DatasetColumns.DATASET_NAME + " = @whereDatasetName "
);
ExecuteMultipleHelper(new List <Statement>()
{
new Statement {
Query = updateDatasetMetadataQuery,
Parameters = new object[] { metadata.DatasetName, metadata.CreationDate, metadata.LastModifiedDate, metadata.LastModifiedBy, metadata.RecordCount, metadata.StorageSizeBytes, identityId, metadata.DatasetName }
}
});
}
return(true);
}
}
internal DatasetMetadata GetMetadataHelper(string identityId, string datasetName)
{
string query = DatasetColumns.BuildQuery(
DatasetColumns.IDENTITY_ID + " = @identity_id AND " +
DatasetColumns.DATASET_NAME + " = @dataset_name "
);
DatasetMetadata metadata = null;
using (var sqliteStatement = connection.Prepare(query))
{
BindData(sqliteStatement, identityId, datasetName);
while (sqliteStatement.Step() == SQLiteResult.ROW)
{
metadata = SqliteStmtToDatasetMetadata(sqliteStatement);
}
}
return(metadata);
}
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();
}
}
}
/// <summary>
/// This is different from <see cref="DeleteDataset(String,String)"/>. Not only does it
/// clears all records in the dataset, it also remove it from metadata table.
/// It won't be visible in <see cref="GetDatasetMetadata(String,String)"/>.
/// </summary>
/// <param name="identityId">Identity identifier.</param>
/// <param name="datasetName">Dataset name.</param>
public void PurgeDataset(string identityId, string datasetName)
{
lock (sqlite_lock)
{
string query = DatasetColumns.BuildDelete(
DatasetColumns.IDENTITY_ID + " = @whereIdentityId AND " +
DatasetColumns.DATASET_NAME + " = @whereDatasetName "
);
Statement s1 = new Statement
{
Query = query,
Parameters = new string[] { identityId, datasetName }
};
DeleteDataset(identityId, datasetName, new List <Statement>()
{
s1
});
}
}
/// <summary>
/// Create a dataset
/// </summary>
/// <param name="identityId">Identity Id</param>
/// <param name="datasetName">Dataset name.</param>
public void CreateDataset(string identityId, string datasetName)
{
lock (sqlite_lock)
{
DatasetMetadata metadata = GetMetadataHelper(identityId, datasetName);
if (metadata == null)
{
string query = DatasetColumns.BuildInsert(
new string[]
{
DatasetColumns.IDENTITY_ID,
DatasetColumns.DATASET_NAME,
DatasetColumns.CREATION_TIMESTAMP,
DatasetColumns.LAST_MODIFIED_TIMESTAMP
});
CreateDatasetHelper(query, identityId, datasetName, AWSSDKUtils.CorrectedUtcNow.ToLocalTime(), AWSSDKUtils.CorrectedUtcNow.ToLocalTime());
}
}
}
internal DatasetMetadata GetMetadataHelper(string identityId, string datasetName)
{
string query = DatasetColumns.BuildQuery(
DatasetColumns.IDENTITY_ID + " = @identityId AND " +
DatasetColumns.DATASET_NAME + " = @datasetName "
);
DatasetMetadata metadata = null;
using (var command = new SQLiteCommand(connection))
{
command.CommandText = query;
BindData(command, identityId, datasetName);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows && reader.Read())
{
metadata = SqliteStmtToDatasetMetadata(reader);
}
}
}
return(metadata);
}
public void AddLogsToDatabase(int jobId, ICollection <LogItem> completeLog)
{
if (completeLog == null || completeLog.Count == 0)
{
return;
}
using (var table = new DataTable("ExportImportJobLogs"))
{
// must create the columns from scratch with each iteration
table.Columns.AddRange(DatasetColumns.Select(
column => new DataColumn(column.Item1, column.Item2)).ToArray());
// batch specific amount of record each time
const int batchSize = 500;
var toSkip = 0;
while (toSkip < completeLog.Count)
{
foreach (var item in completeLog.Skip(toSkip).Take(batchSize))
{
var row = table.NewRow();
row["JobId"] = jobId;
row["Name"] = item.Name.TrimToLength(Constants.LogColumnLength);
row["Value"] = item.Value.TrimToLength(Constants.LogColumnLength);
row["Level"] = (int)item.ReportLevel;
row["CreatedOnDate"] = item.CreatedOnDate;
table.Rows.Add(row);
}
PlatformDataProvider.Instance().BulkInsert("ExportImportJobLogs_AddBulk", "@DataTable", table);
toSkip += batchSize;
table.Rows.Clear();
}
}
completeLog.Clear();
}
internal DatasetMetadata GetMetadataHelper(string identityId, string datasetName)
{
string query = DatasetColumns.BuildQuery(
DatasetColumns.IDENTITY_ID + " = @identityId AND " +
DatasetColumns.DATASET_NAME + " = @datasetName "
);
DatasetMetadata metadata = null;
try
{
connection = new SqliteConnection("URI=file:" + filePath);
connection.Open();
using (var command = new SqliteCommand(connection))
{
command.CommandText = query;
BindData(command, identityId, datasetName);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows && reader.Read())
{
metadata = DatasetMetadataFromReader(reader);
}
}
}
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
return(metadata);
}
/// <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 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();
}
}
}
public static string BuildInsert()
{
return(DatasetColumns.BuildInsert(ALL));
}
