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 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 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);
}
}
/// <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 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();
}
}
}
/// <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);
}
/// <summary>
/// Sets up database.
/// </summary>
/// <param name="dbPath">Db path.</param>
private static void SetUpDatabase(String dbPath)
{
lock (_lock)
{
SQLiteStatement stmt = null;
try
{
db = new SQLiteDatabase(System.IO.Path.Combine(AmazonHookedPlatformInfo.Instance.PersistentDataPath, dbPath));
//turn on auto vacuuming so that when events are deleted, then we can recover the table space.
string query = "PRAGMA auto_vacuum = 1";
db.Exec(query);
query = "SELECT count(*) as count FROM sqlite_master WHERE type='table' AND name='" + TABLE_NAME + "'";
stmt = db.Prepare(query);
if (stmt.Read() && stmt.Fields["count"].INTEGER == 0)
{
query = "CREATE TABLE " + TABLE_NAME + " ("
+ EVENT_COLUMN_NAME + " TEXT NOT NULL," + EVENT_ID_COLUMN_NAME + " TEXT NOT NULL UNIQUE,"
+ MA_APP_ID_COLUMN_NAME + " TEXT NOT NULL,"
+ EVENT_DELIVERY_ATTEMPT_COUNT_COLUMN_NAME + " INTEGER NOT NULL DEFAULT 0)";
db.Exec(query);
}
}
catch (Exception e)
{
_logger.Error(e, "");
}
finally
{
if (stmt != null)
{
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();
}
}
}
private void SetupDatabase()
{
lock (sqlite_lock)
{
SQLiteStatement stmt = null;
try
{
db = new SQLiteDatabase(this.dataPath);
string query = "SELECT count(*) as count FROM sqlite_master WHERE type='table' AND name='" + TABLE_DATASETS + "'";
stmt = db.Prepare(query);
if (stmt.Read() && stmt.Fields["count"].INTEGER == 0)
{
_logger.InfoFormat("{0}", @"Cognito Sync - SQLiteStorage - running create dataset");
db.Exec(
"CREATE TABLE " + TABLE_DATASETS + "("
+ DatasetColumns.IDENTITY_ID + " TEXT NOT NULL,"
+ DatasetColumns.DATASET_NAME + " TEXT NOT NULL,"
+ DatasetColumns.CREATION_TIMESTAMP + " TEXT DEFAULT '0',"
+ DatasetColumns.LAST_MODIFIED_TIMESTAMP + " TEXT DEFAULT '0',"
+ DatasetColumns.LAST_MODIFIED_BY + " TEXT,"
+ DatasetColumns.STORAGE_SIZE_BYTES + " INTEGER DEFAULT 0,"
+ DatasetColumns.RECORD_COUNT + " INTEGER DEFAULT 0,"
+ DatasetColumns.LAST_SYNC_COUNT + " INTEGER NOT NULL DEFAULT 0,"
+ DatasetColumns.LAST_SYNC_TIMESTAMP + " INTEGER DEFAULT '0',"
+ DatasetColumns.LAST_SYNC_RESULT + " TEXT,"
+ "UNIQUE (" + DatasetColumns.IDENTITY_ID + ", "
+ DatasetColumns.DATASET_NAME + ")"
+ ")");
}
stmt.FinalizeStm();
query = "SELECT count(*) as count FROM sqlite_master WHERE type='table' AND name='" + TABLE_RECORDS + "'";
stmt = db.Prepare(query);
if (stmt.Read() && stmt.Fields["count"].INTEGER == 0)
{
_logger.InfoFormat("{0}", @"Cognito Sync - SQLiteStorage - running create dataset");
db.Exec(
"CREATE TABLE " + TABLE_RECORDS + "("
+ RecordColumns.IDENTITY_ID + " TEXT NOT NULL,"
+ RecordColumns.DATASET_NAME + " TEXT NOT NULL,"
+ RecordColumns.KEY + " TEXT NOT NULL,"
+ RecordColumns.VALUE + " TEXT,"
+ RecordColumns.SYNC_COUNT + " INTEGER NOT NULL DEFAULT 0,"
+ RecordColumns.LAST_MODIFIED_TIMESTAMP + " TEXT DEFAULT '0',"
+ RecordColumns.LAST_MODIFIED_BY + " TEXT,"
+ RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP + " TEXT DEFAULT '0',"
+ RecordColumns.MODIFIED + " INTEGER NOT NULL DEFAULT 1,"
+ "UNIQUE (" + RecordColumns.IDENTITY_ID + ", " + RecordColumns.DATASET_NAME
+ ", " + RecordColumns.KEY + ")"
+ ")");
}
}
finally
{
if (stmt != null)
{
stmt.FinalizeStm();
}
}
_logger.InfoFormat("{0}", @"Cognito Sync - SQLiteStorage - completed setupdatabase");
}
}
