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