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 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 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 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 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);
}
}
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();
}
}
}
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 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);
}
}
/// <summary>
/// Gets Numbers the of events.
/// </summary>
/// <returns>The number of events.</returns>
public long NumberOfEvents(string appid)
{
long count = 0;
lock (_lock)
{
SQLiteStatement stmt = null;
try
{
string query = "SELECT COUNT(*) C FROM " + TABLE_NAME + " where " + MA_APP_ID_COLUMN_NAME + " = ?";
stmt = db.Prepare(query);
stmt.BindText(1, appid);
while (stmt.Read())
{
count = stmt.Fields["C"].INTEGER;
}
}
finally
{
if (stmt != null)
{
stmt.FinalizeStm();
}
}
}
return(count);
}
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();
}
}
}
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>
/// Get All event from the Event Store
/// </summary>
/// <param name="appid">Appid.</param>
/// <returns>All the events as a List of <see cref="ThirdParty.Json.LitJson.JsonData"/>.</returns>
public List <JsonData> GetAllEvents(string appId)
{
List <JsonData> eventList = new List <JsonData>();
lock (_lock)
{
SQLiteStatement stmt = null;
try
{
#if SQL_DEBUG
DateTime _dbExecutionStartTime = DateTime.Now;
#endif
string query = "SELECT * FROM " + TABLE_NAME + " WHERE " + MA_APP_ID_COLUMN_NAME + " = ? ORDER BY " + EVENT_DELIVERY_ATTEMPT_COUNT_COLUMN_NAME + ",ROWID LIMIT " + MAX_ALLOWED_SELECTS;
stmt = db.Prepare(query);
stmt.BindText(1, appId);
while (stmt.Read())
{
JsonData data = new JsonData();
data["id"] = stmt.Fields[EVENT_ID_COLUMN_NAME].TEXT;
data["event"] = stmt.Fields[EVENT_COLUMN_NAME.ToLower()].TEXT;
data["appId"] = stmt.Fields[MA_APP_ID_COLUMN_NAME].TEXT;
eventList.Add(data);
}
#if SQL_DEBUG
DateTime _dbExecutionEndTime = DateTime.Now;
double totalSeconds = _dbExecutionEndTime.Subtract(_dbExecutionStartTime).TotalSeconds;
AmazonLogging.Log(AmazonLogging.AmazonLoggingLevel.Verbose, "SQLiteEventStore", "Select All Operation completed from local store in " + totalSeconds + " seconds");
#endif
}
catch (Exception e)
{
_logger.Error(e, "Exception happens when getting events.");
}
finally
{
if (stmt != null)
{
stmt.FinalizeStm();
}
}
}
return(eventList);
}
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();
}
}
}
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();
}
}
}
/// <summary>
/// Add an event to the store.
/// </summary>
/// <returns><c>true</c>, if event was put, <c>false</c> otherwise.</returns>
public bool PutEvent(string eventString, string appId)
{
bool success = false;
bool proceedToInsert = false;
long currentDatabaseSize = GetDatabaseSize();
if (string.IsNullOrEmpty(appId))
{
throw new ArgumentNullException("AppId");
}
if (currentDatabaseSize >= _maxDbSize)
{
proceedToInsert = false;
InvalidOperationException e = new InvalidOperationException();
_logger.Error(e, "The database size has exceeded the threshold limit. Unable to insert any new events");
}
else if (currentDatabaseSize / _maxDbSize >= _dbWarningThreshold)
{
proceedToInsert = true;
_logger.InfoFormat("The database size is almost full");
}
else
{
proceedToInsert = true;
}
//keep the lock as short as possible
if (proceedToInsert)
{
lock (_lock)
{
SQLiteStatement stmt = null;
try
{
#if SQL_DEBUG
DateTime _dbExecutionStartTime = DateTime.Now;
#endif
string query = "INSERT INTO " + TABLE_NAME + " (" + EVENT_COLUMN_NAME + "," + EVENT_ID_COLUMN_NAME + "," + MA_APP_ID_COLUMN_NAME + ") values(?,?,?)";
stmt = db.Prepare(query);
stmt.BindText(1, eventString);
stmt.BindText(2, Guid.NewGuid().ToString());
stmt.BindText(3, appId);
stmt.Step();
success = true;
#if SQL_DEBUG
DateTime _dbExecutionEndTime = DateTime.Now;
double totalSeconds = _dbExecutionEndTime.Subtract(_dbExecutionStartTime).TotalSeconds;
AmazonLogging.Log(AmazonLogging.AmazonLoggingLevel.Verbose, "SQLiteEventStore", "Put Operation completed on local store in " + totalSeconds + " seconds");
#endif
}
finally
{
if (stmt != null)
{
stmt.FinalizeStm();
}
}
}
}
return(success);
}
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();
}
}
}