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