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 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();
}
}
}
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>
/// 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);
}
}
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 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);
}
}
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();
}
}
}
/// <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();
}
}
}
