public long NumberOfEvents(string appID)
{
long count = 0;
string sqlCommand = string.Format(CultureInfo.InvariantCulture, "SELECT COUNT(*) C FROM {0} where {1} = ?", TABLE_NAME, MA_APP_ID_COLUMN_NAME);
Sqlite3Statement stmt = null;
try
{
stmt = ExecuteQuery(sqlCommand, appID);
while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW)
{
count = Sqlite3.sqlite3_column_int(stmt, 0);
}
}
finally
{
if (stmt != null)
{
Sqlite3.sqlite3_finalize(stmt);
}
}
return(count);
}
public List <JsonData> GetEvents(string appID, int maxAllowed)
{
List <JsonData> eventList = new List <JsonData>();
string sqlCommand = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0} WHERE {1} = ? ORDER BY {2}, ROWID LIMIT {3} ", TABLE_NAME, MA_APP_ID_COLUMN_NAME, EVENT_DELIVERY_ATTEMPT_COUNT_COLUMN_NAME, maxAllowed);
lock (_lock)
{
Sqlite3Statement stmt = null;
try
{
stmt = ExecuteQuery(sqlCommand, appID);
while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW)
{
JsonData data = new JsonData();
data["id"] = (string)GetColumnValue(stmt, typeof(string), EVENT_ID_COLUMN_NAME);
data["event"] = (string)GetColumnValue(stmt, typeof(string), EVENT_COLUMN_NAME);
data["appID"] = (string)GetColumnValue(stmt, typeof(string), MA_APP_ID_COLUMN_NAME);
eventList.Add(data);
}
}
finally
{
if (stmt != null)
{
Sqlite3.sqlite3_finalize(stmt);
}
}
}
return(eventList);
}
private void Execute(string query, params object[] parameters)
{
Sqlite3Statement statement = null;
try
{
Result r = (Result)Enum.Parse(typeof(Result), Sqlite3.sqlite3_prepare_v2(Handle, query, out statement).ToString());
if (r != Result.OK && r != Result.Done && r != Result.Row)
{
throw Sqlite3Exception.New(r, string.Format("Error executing statement {0}", r));
}
BindData(statement, parameters);
r = (Result)Enum.Parse(typeof(Result), Sqlite3.sqlite3_step(statement).ToString());
if (r != Result.OK && r != Result.Done && r != Result.Row)
{
throw Sqlite3Exception.New(r, string.Format("Error executing statement {0}", r));
}
}
finally
{
if (statement != null)
{
Sqlite3.sqlite3_finalize(statement);
}
}
}
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);
}
/// <summary>
/// Implements the Dispose pattern
/// </summary>
/// <param name="disposing">Whether this object is being disposed via a call to Dispose
/// or garbage collected.</param>
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
Sqlite3.sqlite3_close(Handle);
}
}
public static Result Open(string filename, out Sqlite3DatabaseHandle db, int flags, IntPtr zVfs)
{
#if USE_WP8_NATIVE_SQLITE
return((Result)Sqlite3.sqlite3_open_v2(filename, out db, flags, ""));
#else
return((Result)Sqlite3.sqlite3_open_v2(filename, out db, flags, null));
#endif
}
public static int BindBlob(Sqlite3Statement stmt, int index, byte[] val, int n, IntPtr free)
{
#if USE_WP8_NATIVE_SQLITE
return(Sqlite3.sqlite3_bind_blob(stmt, index, val, n));
#elif USE_SQLITEPCL_RAW
return(Sqlite3.sqlite3_bind_blob(stmt, index, val));
#else
return(Sqlite3.sqlite3_bind_blob(stmt, index, val, n, null));
#endif
}
public static int BindText(Sqlite3Statement stmt, int index, string val, int n, IntPtr free)
{
#if USE_WP8_NATIVE_SQLITE
return(Sqlite3.sqlite3_bind_text(stmt, index, val, n));
#elif USE_SQLITEPCL_RAW
return(Sqlite3.sqlite3_bind_text(stmt, index, val));
#else
return(Sqlite3.sqlite3_bind_text(stmt, index, val, n, null));
#endif
}
/// <summary>
/// Performs a SQL query without results.
/// </summary>
private static void PerformAction(string sql)
{
PerformSql(sql, stmt => {
if (Sql.sqlite3_step(stmt) != Sql.SQLITE_DONE)
{
throw new ArgumentException("SQL statement not done");
}
return(true);
});
}
/// <summary>
/// Performs an insert SQL query and returns new row ID.
/// </summary>
private static long PerformInsert(string sql)
{
return(PerformSql(sql, stmt => {
if (Sql.sqlite3_step(stmt) != Sql.SQLITE_DONE)
{
throw new ArgumentException("SQL statement not done");
}
return Sql.sqlite3_last_insert_rowid(_db);
}));
}
public static byte[] ColumnByteArray(Sqlite3Statement stmt, int index)
{
int length = Sqlite3.sqlite3_column_bytes(stmt, index);
if (length > 0)
{
return(Sqlite3.sqlite3_column_blob(stmt, index).ToArray());
}
return(new byte[0]);
}
public static Sqlite3Statement Prepare2(Sqlite3DatabaseHandle db, string query)
{
var r = Sqlite3.sqlite3_prepare_v2(db, query, out var stmt);
if (r != 0)
{
throw new SQLiteException((Result)r, GetErrorMessageUTF8(db), sql: query);
}
return(stmt);
}
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);
}
}
}
private Sqlite3Statement ExecuteQuery(string query, params object[] parameters)
{
Sqlite3Statement statement;
Result r = (Result)Enum.Parse(typeof(Result), Sqlite3.sqlite3_prepare_v2(Handle, query, out statement).ToString());
if (r != Result.OK && r != Result.Done && r != Result.Row)
{
throw Sqlite3Exception.New(r, string.Format("Error executing statement {0}", r));
}
BindData(statement, parameters);
return(statement);
}
private void BindData(Sqlite3Statement statement, params object[] parameters)
{
if (parameters != null)
{
for (int i = 1; i <= parameters.Length; i++)
{
object o = parameters[i - 1];
if (o == null)
{
Sqlite3.sqlite3_bind_null(statement, i);
continue;
}
var type = o.GetType();
var dt = o as DateTime?;
if (dt.HasValue)
{
string ticks = dt.Value.Ticks.ToString();
Sqlite3.sqlite3_bind_text(statement, i, ticks);
}
else if (type == typeof(string))
{
Sqlite3.sqlite3_bind_text(statement, i, (string)o);
}
else if ((typeof(Int32) == type) ||
(typeof(Boolean) == type) ||
(typeof(Byte) == type) ||
(typeof(UInt16) == type) ||
(typeof(Int16) == type) ||
(typeof(sbyte) == type) ||
(typeof(Int64) == type) ||
(typeof(long) == type) ||
(typeof(UInt32) == type))
{
Sqlite3.sqlite3_bind_int64(statement, i, (Int64)Convert.ChangeType(o, typeof(Int64)));
}
else if ((typeof(double) == type) ||
(typeof(float) == type) ||
(typeof(decimal) == type))
{
Sqlite3.sqlite3_bind_double(statement, i, (double)o);
}
else if (type == typeof(byte[]))
{
Sqlite3.sqlite3_bind_blob(statement, i, (byte[])o);
}
}
}
}
public static Sqlite3Statement Prepare2(Sqlite3DatabaseHandle db, string query)
{
Sqlite3Statement stmt = default(Sqlite3Statement);
#if USE_WP8_NATIVE_SQLITE || USE_SQLITEPCL_RAW
var r = Sqlite3.sqlite3_prepare_v2(db, query, out stmt);
#else
stmt = new Sqlite3Statement();
var r = Sqlite3.sqlite3_prepare_v2(db, query, -1, ref stmt, 0);
#endif
if (r != 0)
{
throw SQLiteException.New((Result)r, GetErrmsg(db));
}
return(stmt);
}
/// <summary>
/// Performs a wrapped SQL query with a prepared statement.
/// </summary>
/// <typeparam name="T">Query return type.</typeparam>
/// <param name="sql">SQL query.</param>
/// <param name="f">Statement processing function.</param>
private static T PerformSql <T>(string sql, Func <SQLitePCL.sqlite3_stmt, T> f)
{
if (Sql.sqlite3_prepare_v2(_db, sql, out SQLitePCL.sqlite3_stmt stmt) != Sql.SQLITE_OK)
{
throw new ArgumentException($"Cannot prepare SQL statement '{sql}'");
}
T ret = f(stmt);
if (Sql.sqlite3_finalize(stmt) != Sql.SQLITE_OK)
{
throw new ArgumentException("Failed to finalize SQL statement");
}
return(ret);
}
private void SetupDatabase()
{
SQLitePCL.Batteries.Init();
string dbPath = Path.Combine(PCLStorage.FileSystem.Current.LocalStorage.Path, DB_FILE_NAME);
var r = Sqlite3.sqlite3_open(dbPath, out Handle);
string createDatasetTable = "CREATE TABLE IF NOT EXISTS " + 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 + ")"
+ ")";
if (r != Sqlite3.SQLITE_OK)
{
throw Sqlite3Exception.New((Result)Enum.Parse(typeof(Result), r.ToString()), string.Format("Could not open database file: {0} ({1})", dbPath, r));
}
Execute(createDatasetTable);
string createRecordsTable = "CREATE TABLE IF NOT EXISTS " + 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 + ")"
+ ")";
Execute(createRecordsTable);
}
/// <summary>
/// Initializes the database.
/// </summary>
public static async Task Initialize()
{
Sql.SetProvider(new SQLitePCL.SQLite3Provider_e_sqlite3());
if (Sql.sqlite3_initialize() != Sql.SQLITE_OK)
{
throw new InvalidOperationException("Cannot initialize SQLite");
}
var filePath = Path.Combine(ApplicationData.Current.LocalFolder.Path, DatabaseFilename);
Debug.WriteLine("Opening database at " + filePath);
if (Sql.sqlite3_open_v2(filePath, out _db, Sql.SQLITE_OPEN_READWRITE | Sql.SQLITE_OPEN_CREATE, null) != Sql.SQLITE_OK)
{
throw new Exception("Cannot open or create SQLite database");
}
await ApplicationData.Current.SetVersionAsync(CurrentDataVersion, UpdateDataVersion);
}
/// <summary>
/// Sets up SQLite database.
/// </summary>
private void SetupSQLiteEventStore()
{
this.DBfileFullPath = System.IO.Path.Combine(PCLStorage.FileSystem.Current.LocalStorage.Path, dbFileName);
string vacuumCommand = "PRAGMA auto_vacuum = 1";
string sqlCommand = string.Format(CultureInfo.InvariantCulture, "CREATE TABLE IF NOT EXISTS {0} ({1} TEXT NOT NULL,{2} TEXT NOT NULL UNIQUE,{3} TEXT NOT NULL, {4} INTEGER NOT NULL DEFAULT 0 )",
TABLE_NAME, EVENT_COLUMN_NAME, EVENT_ID_COLUMN_NAME, MA_APP_ID_COLUMN_NAME, EVENT_DELIVERY_ATTEMPT_COUNT_COLUMN_NAME);
var r = Sqlite3.sqlite3_open(this.DBfileFullPath, out Handle);
if (r != Sqlite3.SQLITE_OK)
{
throw Sqlite3Exception.New((Result)Enum.Parse(typeof(Result), r.ToString()), string.Format("Could not open database file: {0} ({1})", this.DBfileFullPath, r));
}
lock (_lock)
{
Execute(vacuumCommand);
Execute(sqlCommand);
}
}
internal long GetLastSyncCountHelper(string query, params string[] parameters)
{
long lastSyncCount = 0;
Sqlite3Statement stmt = null;
try
{
stmt = ExecuteQuery(query, parameters);
while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW)
{
lastSyncCount = (Int64)GetColumnValue(stmt, typeof(Int64), DatasetColumns.LAST_SYNC_COUNT);
}
}
finally
{
if (stmt != null)
{
Sqlite3.sqlite3_finalize(stmt);
}
}
return(lastSyncCount);
}
private static long PerformScalarInt64(string sql)
{
return(PerformSql(sql, stmt => {
if (Sql.sqlite3_step(stmt) != Sql.SQLITE_ROW)
{
throw new ArgumentException("Scalar SQL query did not return a result");
}
long ret = Sql.sqlite3_column_int64(stmt, 0);
int resultLastStep = Sql.sqlite3_step(stmt);
if (resultLastStep == Sql.SQLITE_ROW)
{
throw new ArgumentException("Scalar SQL query did return more than one result");
}
else if (resultLastStep != Sql.SQLITE_DONE)
{
throw new ArgumentException("SQL statement not done");
}
return ret;
}));
}
internal List <Record> GetModifiedRecordsHelper(string query, params object[] parameters)
{
List <Record> records = new List <Record>();
Sqlite3Statement stmt = null;
try
{
stmt = ExecuteQuery(query, parameters);
while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW)
{
records.Add(SqliteStmtToRecord(stmt));
}
}
finally
{
if (stmt != null)
{
Sqlite3.sqlite3_finalize(stmt);
}
}
return(records);
}
internal List <DatasetMetadata> GetDatasetMetadataHelper(string query, params string[] parameters)
{
List <DatasetMetadata> datasetMetadataList = new List <DatasetMetadata>();
Sqlite3Statement stmt = null;
try
{
stmt = ExecuteQuery(query, parameters);
while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW)
{
datasetMetadataList.Add(SqliteStmtToDatasetMetadata(stmt));
}
}
finally
{
if (stmt != null)
{
Sqlite3.sqlite3_finalize(stmt);
}
}
return(datasetMetadataList);
}
private static DateTimeOffset PerformScalarDateTime(string sql)
{
return(PerformSql(sql, stmt => {
if (Sql.sqlite3_step(stmt) != Sql.SQLITE_ROW)
{
throw new ArgumentException("Scalar SQL query did not return a result");
}
string dt = Sql.sqlite3_column_text(stmt, 0);
DateTimeOffset ret = DateTimeOffset.Parse(dt, CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal);
int resultLastStep = Sql.sqlite3_step(stmt);
if (resultLastStep == Sql.SQLITE_ROW)
{
throw new ArgumentException("Scalar SQL query did return more than one result");
}
else if (resultLastStep != Sql.SQLITE_DONE)
{
throw new ArgumentException("SQL statement not done");
}
return ret;
}));
}
internal Record GetRecordHelper(string query, params string[] parameters)
{
Record record = null;
Sqlite3Statement stmt = null;
try
{
stmt = ExecuteQuery(query, parameters);
while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW)
{
record = SqliteStmtToRecord(stmt);
}
}
finally
{
if (stmt != null)
{
Sqlite3.sqlite3_finalize(stmt);
}
}
return(record);
}
private object GetColumnValue(Sqlite3Statement stmt, Type t, string columnName)
{
int columnCount = Sqlite3.sqlite3_column_count(stmt);
int columnIndex = -1;
int columnType = -1;
for (int i = 0; i < columnCount; i++)
{
string colName = Sqlite3.sqlite3_column_name(stmt, i);
if (colName.Equals(columnName, StringComparison.OrdinalIgnoreCase))
{
columnIndex = i;
columnType = Sqlite3.sqlite3_column_type(stmt, i);
break;
}
}
if (t == typeof(string))
{
return(Sqlite3.sqlite3_column_text(stmt, columnIndex));
}
else if ((typeof(Int32) == t) ||
(typeof(Boolean) == t) ||
(typeof(Byte) == t) ||
(typeof(UInt16) == t) ||
(typeof(Int16) == t) ||
(typeof(sbyte) == t))
{
return(Convert.ChangeType(Sqlite3.sqlite3_column_int(stmt, columnIndex), t));
}
else if ((typeof(double) == t) ||
(typeof(float) == t))
{
return(Convert.ChangeType(Sqlite3.sqlite3_column_double(stmt, columnIndex), t));
}
else if (typeof(DateTime) == t)
{
string time = Sqlite3.sqlite3_column_text(stmt, columnIndex);
return(new DateTime(long.Parse(time, CultureInfo.InvariantCulture.NumberFormat), DateTimeKind.Utc));
}
else if (
(typeof(Int64) == t) ||
(typeof(UInt32) == t)
)
{
return(Convert.ChangeType(Sqlite3.sqlite3_column_int64(stmt, columnIndex), t, null));
}
else if (typeof(System.Nullable <long>) == t)
{
if (columnType == Sqlite3.SQLITE_NULL)
{
return(null);
}
else
{
long?x = Sqlite3.sqlite3_column_int64(stmt, columnIndex);
return(x);
}
}
else if (typeof(System.Nullable <double>) == t)
{
if (columnType == Sqlite3.SQLITE_NULL)
{
return(null);
}
else
{
double?x = Sqlite3.sqlite3_column_double(stmt, columnIndex);
return(x);
}
}
else if (typeof(System.Nullable <int>) == t)
{
if (columnType == Sqlite3.SQLITE_NULL)
{
return(null);
}
else
{
int?x = Sqlite3.sqlite3_column_int(stmt, columnIndex);
return(x);
}
}
else if (typeof(decimal) == t)
{
return((decimal)Convert.ChangeType(Sqlite3.sqlite3_column_double(stmt, columnIndex), t));
}
else if (typeof(byte[]) == t)
{
return(Sqlite3.sqlite3_column_blob(stmt, columnIndex));
}
else
{
throw new NotSupportedException("Invalid type conversion " + t.FullName);
}
}
public static ExtendedResult ExtendedErrCode(Sqlite3DatabaseHandle db)
{
return((ExtendedResult)Sqlite3.sqlite3_extended_errcode(db));
}
public static Result EnableLoadExtension(Sqlite3DatabaseHandle db, int onoff)
{
return((Result)Sqlite3.sqlite3_enable_load_extension(db, onoff));
}
public static string ColumnString(Sqlite3Statement stmt, int index)
{
return(Sqlite3.sqlite3_column_text(stmt, index));
}
