public void HandlePositiveButtonClick(object sender, DialogClickEventArgs e) { try { //signature = Activity.FindViewById<SignaturePadView>(Resource.Id.signatureFrame); Bitmap imagen = signature.GetImage(); MemoryStream ms = new MemoryStream(); //ByteArrayOutputStream bos = new ByteArrayOutputStream(); imagen.Compress(Bitmap.CompressFormat.Png, 100, ms); byte[] bArray = ms.ToArray(); //string id = Guid.NewGuid().ToString(); System.Console.WriteLine("GUID: " + guid); loadConnection(); db.BeginTransaction(); //Se almacena en base de datos el BLOB con su respectivo GUID try { string sql = "INSERT INTO IMAGENES (Nombre,Imagen) VALUES(?,?)"; SQLiteStatement insertStmt = db.CompileStatement(sql); insertStmt.ClearBindings(); insertStmt.BindString(1, guid); insertStmt.BindBlob(2, bArray); insertStmt.ExecuteInsert(); db.SetTransactionSuccessful(); db.EndTransaction(); db.Close(); try { if (actor.Equals("notificando", StringComparison.Ordinal)) { ManejoBaseDatos.Abrir(); ManejoBaseDatos.Actualizar("Notificaciones", "ValidacionNotificando", "S", "CodigoNotificacion=" + codigo + ""); ManejoBaseDatos.Cerrar(); } if (actor.Equals("testigo", StringComparison.Ordinal)) { ManejoBaseDatos.Abrir(); ManejoBaseDatos.Actualizar("Notificaciones", "ValidacionTestigo", "S", "CodigoNotificacion=" + codigo + ""); ManejoBaseDatos.Cerrar(); } } catch (Exception ex) { System.Console.WriteLine("Error almacenando confirmacion de firma: " + ex.ToString()); Toast.MakeText(this.Activity, "Error guardando confirmacion", ToastLength.Short).Show(); } } catch (Exception ex) { System.Console.WriteLine("Error guardando imagen en db: " + ex.ToString()); } Toast.MakeText(this.Activity, "Firma capturada de forma exitosa", ToastLength.Short).Show(); } catch (Exception ex) { System.Console.WriteLine("ERROR guardando la imagen: " + ex.ToString()); } }
public MainPage() { this.InitializeComponent(); SQLiteConnection conn = new SQLiteConnection("News.db"); string sql = @"CREATE TABLE IF NOT EXISTS Contact (Id Integer Primary Key AutoIncrement Not null , name varchar(255), phoneNumber varchar(255) );"; SQLiteStatement statment = (SQLiteStatement)conn.Prepare(sql); statment.Step(); }
protected override AdhocCommandBase GetComparisonDataForSiteCheckCommand(ComparisonArgs args) { return(SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString) .WithSql(SQLiteStatement.Create( "SELECT ResultCount FROM AgentData WHERE (ResultCount IS NOT NULL) AND (EventType = 'Result') AND (lower(SiteId)=") .InsertParameter("@pSiteId", args.Site.ToLower()) .Append(") AND (lower(CheckId)=") .InsertParameter("@pCheckId", args.Check.ToLower()).Append(")") .AppendIf(() => !string.IsNullOrEmpty(args.Tag), "AND (tag='{0}')", args.Tag) .OrderBy("GeneratedOnUtc") .Append("LIMIT 2"))); }
/// <summary> /// Return JSONArray for one row of data from the statement /// </summary> /// <param name="statement"></param> /// <returns></returns> private JArray GetDataFromRow(SQLiteStatement statement) { var row = new JArray(); int columnCount = statement.ColumnCount; for (int i = 0; i < columnCount; i++) { if (statement[i] != null && (statement.ColumnName(i).EndsWith(SoupCol) || statement.ColumnName(i).StartsWith(SoupCol))) { string raw = statement.GetText(i); row.Add(JObject.Parse(raw)); } else { object raw = GetObject(statement, i); if (raw != null) { long value; if (long.TryParse(raw.ToString(), out value)) { row.Add(new JValue(value)); } else { double dvalue; if (double.TryParse(raw.ToString(), out dvalue)) { row.Add(new JValue(dvalue)); } else { string rawString = raw.ToString(); if (rawString.Contains('{')) { try { row.Add(JObject.Parse(rawString)); } catch (Exception) { row.Add(raw.ToString()); } } else { row.Add(raw.ToString()); } } } } } } return(row); }
public MainPage() { this.InitializeComponent(); Articles = new ObservableCollection <Article>(); GetAR("tesla", "2021-02-18", "publishedAt", "20f0bc79a63947818867de418e5f4ab3"); SQLiteConnection conn = new SQLiteConnection("News.db"); string sql = @"CREATE TABLE IF NOT EXISTS News (Id Integer Primary Key AutoIncrement Not null , urlLink varchar(255) );"; SQLiteStatement statment = (SQLiteStatement)conn.Prepare(sql); statment.Step(); }
protected string GetSoupTableNameFromDb(string soupName) { using ( SQLiteStatement stmt = Query(SmartStore.SoupNamesTable, new[] { SmartStore.IdCol }, String.Empty, String.Empty, SmartStore.SoupNamePredicate, soupName)) { if (stmt.DataCount == 0) { return(null); } return(SmartStore.GetSoupTableName(stmt.GetInteger(SmartStore.IdCol))); } }
protected override AdhocCommandBase GetGeckoboardPieChartForCheckCommand(PieChartArgs args) { var outcome = ConvertOutcome(args.Outcome); var operation = ConvertOperation(args.DataOperation); return(SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString) .WithSql(SQLiteStatement.Create( "SELECT Tags [SegmentId], {0} [Count] FROM AgentData WHERE CheckId=", operation) .InsertParameter("@pCheckId", args.Check) .AppendIf(() => !string.IsNullOrEmpty(outcome), "AND {0}", outcome) .AppendIf(() => !string.IsNullOrEmpty(args.Site), "AND SiteId=") .InsertParameterIf(() => !string.IsNullOrEmpty(args.Site), "@pSiteId", args.Site) .AppendIf(() => !string.IsNullOrEmpty(args.Agent), "AND AgentId=") .InsertParameterIf(() => !string.IsNullOrEmpty(args.Agent), "@pAgentId", args.Agent) .Append("GROUP BY Tags"))); }
private DatasetMetadata SqliteStmtToDatasetMetadata(SQLiteStatement stmt) { /* Column Order * IDENTITY_ID, DATASET_NAME, * CREATION_TIMESTAMP, LAST_MODIFIED_TIMESTAMP, LAST_MODIFIED_BY, * STORAGE_SIZE_BYTES, RECORD_COUNT, * LAST_SYNC_COUNT, LAST_SYNC_TIMESTAMP, LAST_SYNC_RESULT, */ return(new DatasetMetadata( stmt.Fields[DatasetColumns.DATASET_NAME].TEXT, stmt.Fields[DatasetColumns.CREATION_TIMESTAMP].DATETIME, stmt.Fields[DatasetColumns.LAST_MODIFIED_TIMESTAMP].DATETIME, stmt.Fields[DatasetColumns.LAST_MODIFIED_BY].TEXT, stmt.Fields[DatasetColumns.STORAGE_SIZE_BYTES].INTEGER, stmt.Fields[DatasetColumns.RECORD_COUNT].INTEGER )); }
/// <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 object GetObject(SQLiteStatement statement, int position) { if (statement[position] == null) { return(null); } if (statement[position].GetType().Name == "String") { return(statement.GetText(position)); } if (intTypes.Contains(statement[position].GetType().Name)) { return(statement.GetInteger(position)); } // //fallback try to detect type using exceptions // try { return(statement.GetText(position)); } catch (Exception e) { PlatformAdapter.SendToCustomLogger(e, LoggingLevel.Error); } try { return(statement.GetInteger(position)); } catch (Exception e) { PlatformAdapter.SendToCustomLogger(e, LoggingLevel.Error); } try { return(statement.GetFloat(position)); } catch (Exception e) { PlatformAdapter.SendToCustomLogger(e, LoggingLevel.Error); } return(null); }
private static void BindFilters(SQLiteStatement query, ISet <Filter> filters) { var i = 1; foreach (var filter in filters) { if (filter.Type != null) { query.Bind(i++, (int)filter.Type); } if (filter.Database != null) { query.Bind(i++, string.Format(CultureInfo.InvariantCulture, "%{0}%", filter.Database)); } if (filter.After != null && filter.After != default(DateTime)) { query.Bind(i++, filter.After.ToString("o", CultureInfo.InvariantCulture)); } if (filter.Before != null && filter.Before != default(DateTime)) { query.Bind(i++, filter.Before.ToString("o", CultureInfo.InvariantCulture)); } if (filter.Complete != null) { query.Bind(i++, default(DateTime).ToString("o", CultureInfo.InvariantCulture)); } if (filter.Text != null) { query.Bind(i++, string.Format(CultureInfo.InvariantCulture, "%{0}%", filter.Text)); } if (filter.Plan != null) { query.Bind(i++, string.Format(CultureInfo.InvariantCulture, "%{0}%", filter.Plan)); } } }
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(); } } }
private static List <string> GetAllSoupNames(string databasePath) { lock (smartlock) { var soupNames = new List <string>(); DBHelper db = DBHelper.GetInstance(databasePath); using (SQLiteStatement stmt = db.Query(SoupNamesTable, new[] { SoupNameCol }, String.Empty, String.Empty, String.Empty)) { if (stmt.DataCount > 0) { do { soupNames.Add(stmt.GetText(0)); } while (stmt.Step() == SQLiteResult.ROW); } } return(soupNames); } }
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(); } } }
protected IndexSpec[] GetIndexSpecsFromDb(String soupName) { SQLiteStatement statement = Query(SmartStore.SoupIndexMapTable, new[] { SmartStore.PathCol, SmartStore.ColumnNameCol, SmartStore.ColumnTypeCol }, null, null, SmartStore.SoupNamePredicate, soupName); if (statement.DataCount < 1) { throw new SmartStoreException(String.Format("{0} does not have any indices", soupName)); } var indexSpecs = new List <IndexSpec>(); do { String path = statement.GetText(SmartStore.PathCol); String columnName = statement.GetText(SmartStore.ColumnNameCol); var columnType = new SmartStoreType(statement.GetText(SmartStore.ColumnTypeCol)); indexSpecs.Add(new IndexSpec(path, columnType, columnName)); } while (statement.Step() == SQLiteResult.ROW); statement.ResetAndClearBindings(); return(indexSpecs.ToArray()); }
private IEnumerable <Entry> GetEntries(int offset, int length, SQLiteStatement query, int bindOffset) { query.Bind(bindOffset + 1, length); query.Bind(bindOffset + 2, offset); while (query.Step()) { var id = query.ColumnInt(0); var type = (EntryType)query.ColumnInt(1); var connectionId = query.ColumnInt(2); var database = query.ColumnText(3); var start = query.ColumnText(4); var end = query.ColumnText(5); var text = query.ColumnText(6); var plan = query.ColumnText(7); var results = query.ColumnText(8); var startDate = DateTime.Parse(start, null, DateTimeStyles.RoundtripKind); var endDate = DateTime.Parse(end, null, DateTimeStyles.RoundtripKind); var resultsTable = JsonConvert.DeserializeObject <DataTable>(results); yield return(new Entry { Id = id, Parent = this, Type = type, Connection = connectionId, Database = database, Start = startDate, End = endDate, Text = text, Plan = plan, Results = resultsTable, }); } query.Reset(); }
/// <summary> /// Run a query given by its query spec, only returning results from the selected page. /// </summary> /// <param name="querySpec"></param> /// <param name="pageIndex"></param> /// <returns></returns> public JArray Query(QuerySpec querySpec, int pageIndex) { lock (smartlock) { QuerySpec.SmartQueryType qt = querySpec.QueryType; var results = new JArray(); string sql = ConvertSmartSql(querySpec.SmartSql); if (String.IsNullOrEmpty(sql)) { return(new JArray()); } int offsetRows = querySpec.PageSize * pageIndex; int numberRows = querySpec.PageSize; string limit = offsetRows + "," + numberRows; using (SQLiteStatement statement = DBHelper.GetInstance(DatabasePath) .LimitRawQuery(sql, limit, querySpec.getArgs())) { if (statement.DataCount > 0) { do { if (qt == QuerySpec.SmartQueryType.Smart) { results.Add(GetDataFromRow(statement)); } else { results.Add(JObject.Parse(GetObject(statement, 0).ToString())); } } while (statement.Step() == SQLiteResult.ROW); } statement.ResetAndClearBindings(); } return(results); } }
/// <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(); } } } }
protected override AdhocCommandBase GetLineChartDataForCheckRateCommand(LineChartArgs args) { var outcome = ConvertOutcome(args.Outcome); var operation = ConvertOperation(args.DataOperation); return(SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString) .WithSql(SQLiteStatement.Create( "SELECT ({0}*{1}) [MinutesBucket], {2} [ResultCount] FROM AgentData WHERE CheckId=", args.Bucket, args.Multiplier, operation) .InsertParameter("@pCheckId", args.Check) .AppendIf(() => !string.IsNullOrEmpty(outcome), "AND {0}", outcome) .AppendIf(() => !string.IsNullOrEmpty(args.Site), "AND SiteId=") .InsertParameterIf(() => !string.IsNullOrEmpty(args.Site), "@pSiteId", args.Site) .AppendIf(() => !string.IsNullOrEmpty(args.Agent), "AND AgentId=") .InsertParameterIf(() => !string.IsNullOrEmpty(args.Agent), "@pAgentId", args.Agent) .AppendIf(() => !string.IsNullOrEmpty(args.Tag), "AND tag=") .InsertParameterIf(() => !string.IsNullOrEmpty(args.Tag), "@pTag", args.Tag) .Append("GROUP BY {0}", args.Bucket) .OrderBy(args.Bucket) .Append("LIMIT {0}", args.Limit))); }
public Log(EventAggregator events) { this.events = events; this.connection = new SQLiteConnection(string.Empty, true); this.CreateDatabase(); this.select = this.connection.Prepare(string.Format( CultureInfo.InvariantCulture, @"SELECT {0} FROM entries ORDER BY rowid LIMIT ? OFFSET ?", Columns)); this.count = this.connection.Prepare(@"SELECT COUNT(*) FROM entries"); this.insert = this.connection.Prepare(@"INSERT INTO entries ( type, connection, database, start, end, text, plan, results ) VALUES (?, ?, ?, julianday(?), julianday(?), ?, ?, ?)"); this.updateEnd = this.connection.Prepare( "UPDATE entries SET end = julianday(?) WHERE rowid = ?"); this.updateResults = this.connection.Prepare( "UPDATE entries SET results = ? WHERE rowid = ?"); }
private DatasetMetadata GetMetadataInternal(string identityId, string datasetName) { lock (sqlite_lock) { 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(); } } }
public void Execute() { var lastRun = Convert.ToInt64(myLastRun.ToString("yyyMMddHHmm")); using (var query = SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString) .WithSql(SQLiteStatement.Create("select s.name, datetime(year || '-' || substr('0' || month, -2,2) || '-' || substr('0' || day, -2,2) || ' ' || substr('0' || hour, -2,2) || ':' || substr('0' || min, -2,2)) [recorded], ") .Append("ch1_amps_min, ch1_amps_avg, ch1_amps_max, ch1_kw_min, CAST(ch1_kw_avg AS REAL) [ch1_kw_avg], ch1_kw_max") .Append("from energy_history h join energy_sensor s on h.addr = s.addr") .Append("where CAST((year || substr('0' || month, -2,2) || substr('0' || day, -2,2) || substr('0' || hour, -2,2) || substr('0' || min, -2,2)) AS INTEGER) > ") .InsertParameter("@lastrun", lastRun))) { using (var reader = query.ExecuteReader()) { while (reader.Read()) { // assumes that results are in ascending order myLastRun = Convert.ToDateTime(reader["recorded"]); var sensor = reader["name"].ToString(); var data = HealthCheckData.For(Identity, sensor) .AddTag(sensor); // report average watts data.ResultCount = Convert.ToDouble(reader["ch1_kw_avg"]); data.GeneratedOnUtc = myLastRun.ToUniversalTime(); data.Properties = new ResultProperties(); Messenger.Publish(data); Logger.Debug("[{0}] Sent reading {1}kw taken {2}", Identity.Name, data.ResultCount, data.GeneratedOnUtc.ToLocalTime()); } } } }
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(); } } }
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"); } }
protected override AdhocCommandBase GetPieChartDataForAllSitesCommand() { return(SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString) .WithSql(SQLiteStatement.Create("SELECT SiteId, COUNT(*) [Failures] FROM AgentData WHERE Result = 0 AND EventType = 'Result' GROUP BY SiteId"))); }
//protected override bool IsEntityUpdateable => IsEntityUpdateable; protected override void BindValues(SQLiteStatement p0, Java.Lang.Object p1) { BindValues(p0, p1); }
/// <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); }
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 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(); } } }
/// <summary> /// Re-index all soup elements for passed indexPaths /// </summary> /// <param name="soupName"></param> /// <param name="indexPaths"></param> /// <param name="handleTx"></param> public void ReIndexSoup(string soupName, string[] indexPaths, bool handleTx) { lock (smartlock) { DBHelper db = DBHelper.GetInstance(DatabasePath); string soupTableName = db.GetSoupTableName(soupName); if (String.IsNullOrWhiteSpace(soupTableName)) { throw new SmartStoreException("Soup: " + soupName + " does not exist"); } Dictionary <string, IndexSpec> mapAllSpecs = IndexSpec.MapForIndexSpecs(GetSoupIndexSpecs(soupName)); IndexSpec[] indexSpecs = (from indexPath in indexPaths where mapAllSpecs.ContainsKey(indexPath) select mapAllSpecs[indexPath]) .ToArray(); if (indexSpecs.Length == 0) { return; // nothing to do } if (handleTx) { db.BeginTransaction(); } using ( SQLiteStatement stmt = db.Query(soupTableName, new[] { IdCol, SoupCol }, String.Empty, String.Empty, String.Empty)) { if (stmt.DataCount > 0) { try { do { string soupEntryId = stmt.GetText(0); string soupRaw = stmt.GetText(1); try { JObject soupElt = JObject.Parse(soupRaw); var contentValues = new Dictionary <string, object>(); foreach (IndexSpec indexSpec in indexSpecs) { ProjectIndexedPaths(soupElt, contentValues, indexSpec); } db.Update(soupTableName, contentValues, IdPredicate, soupEntryId + String.Empty); } catch (JsonException e) { PlatformAdapter.SendToCustomLogger(e, LoggingLevel.Error); Debug.WriteLine("SmartStore.ReIndexSoup: Could not parse soup element " + soupEntryId); } } while (stmt.Step() == SQLiteResult.ROW); } finally { if (handleTx) { db.CommitTransaction(); } } } } } }