private void OnTimedEvent(Object source, System.Timers.ElapsedEventArgs e)
{
App.log($"Query status check @ {e.SignalTime} | Runtime : {runtime()} | Max RunTime : {App.queryMaxRunTime}");
if (state == states.Running)
{
if (runtime() > App.queryMaxRunTime)
{
App.log($"need to cancel query.| Runtime : {runtime()} | Max RunTime : {App.queryMaxRunTime} ");
Timing timing = new Timing(this.db);
cmd.Cancel();
timing.stop();
timing.log("Cancelling Query");
state = states.Done;
result = results.Stopped;
timer.Stop();
throw new TimeoutException($"SQL Query exceeded maximum allowed runtime. | Runtime : {runtime()} | Max RunTime : {App.queryMaxRunTime} ");
// I should save/log this efffort and allow a retry - now while in memory?
// or leave the scheduler retry
}
else
{
timer.Start(); // schedule another run
}
}
else
{
App.log($@"This query should have stopped. Will stop
{errorMsg}
{sql}");
timer.Stop();
}
}
public int getNewRecordCount()
{
App.log("Getting NEW record count for " + name);
// way too slow
//string sql = $@"SELECT Count(*) as RecCount FROM [{name}]
// WHERE [{name}].[_id] NOT IN
// (SELECT [_id] FROM [snapshot] WHERE [tableId] = {id} );";
//string sql = $@"SELECT SUM(IIF(snapshot.[value] IS NULL,1,0)) as RecCount FROM [{name}]
// LEFT JOIN snapshot
// ON snapshot.[_id] = {name}.[_id];";
string sql = $@"SELECT COUNT(*) as RecCount FROM [{name}]
LEFT JOIN
(SELECT * FROM snapshot
WHERE snapshot.[tableId] = {id}) AS snapshot
ON snapshot.[_id] = [{name}].[_id]
WHERE snapshot.op IS NULL
;";
//OleDbDataReader reader = App.snapshot.sql(sql);
Query q = App.allDb.reader(sql);
int count = 0;
if (q.read())
{
count = q.getInt("RecCount");
newRecordCount = count;
}
App.log("done. Count : " + count);
return(count);
}
public void start()
{
App.log("Queue Startup");
App.log("wait for delay before starting queue processing");
timer.Start();
}
new private void start()
{
App.log("start query");
base.start(); // timing;
timer.Start();
state = states.Running;
}
private void OnTimedEvent(Object source, System.Timers.ElapsedEventArgs e)
{
App.log("refresh Tables");
// this is throwing an error
// dgvTables.Refresh();
dgvTables.DataSource = snapshot.fromSnapshot();
dgvTables.Update();
}
new private void stop()
{
App.log("query complete");
base.stop(); // timing
timer.Stop(); // cancel timer
state = states.Done;
result = results.Success;
// need to save this object
}
public void insert(string tableName, string[] fields, object[] values)
{
string _fields = String.Join(",", fields);
string _values = "";
for (int i = 0; i < values.Length; i++)
{
if (i > 0)
{
_values += ",";
}
_values += $"@" + fields[i]; // param placeholders
}
string sql = $"INSERT INTO [{tableName}] ({_fields}) VALUES ({_values})";
if (!connected)
{
connect();
}
timing.start();
cmd = new OleDbCommand(sql, conn);
// add the params
int _i = 0;
foreach (object value in values)
{
cmd.Parameters.AddWithValue($"@" + fields[_i], values[_i]);
_i++;
}
string errMsg = "";
try
{
App.log("exec insert query " + sql);
rowsAffected = cmd.ExecuteNonQuery();
App.log("done");
}
catch (Exception e)
{
errMsg = e.Message;
App.error($@"Db.sql
{e.Message}
{sql}
");
}
timing.stop();
timing.log($@"{errMsg} : {sql}");
}
public static void loadQueues()
{
queues = new Dictionary <int, Queue>();
App.log("load queues");
OleDbDataReader reader = App.snapshotDb.query("queues", "", "priority");
while (reader.Read())
{
Queue q = new Queue(reader);
queues.Add(q.id, q);
q.thread = new Thread(new ThreadStart(q.start));
q.thread.Start();
}
}
public void delete(string tableName, string where = "", bool confirmAll = false)
{
string sql = $"DELETE * FROM {tableName} ";
if (where.Length == 0)
{
if (!confirmAll)
{
Console.WriteLine($"*** SQL Delete table {tableName} : You must confirm All if no where clause specified");
return;
}
else
{
Console.WriteLine($"*** SQL Delete : Warning - deleting all records table : {tableName}");
}
}
else
{
sql += $" WHERE {where} ;";
}
if (!connected)
{
connect();
}
timing.start();
cmd = new OleDbCommand(sql, conn);
string errMsg = "";
try
{
App.log("exec delete query " + sql);
rowsAffected = cmd.ExecuteNonQuery();
App.log("done");
}
catch (Exception e)
{
errMsg = e.Message;
App.error($@"Db.sql
{e.Message}
{sql}
");
}
timing.stop();
timing.log($@"{errMsg} : {sql}");
}
public static void checkForDeltas()
{
// each run must rerember the batchId processed
// get next batch
App.log("get last delta batch");
string sql = $@"SELECT TOP 1 * FROM [deltas] ORDER BY id DESC ;";
Query query = Query.reader(App.allDb, sql);
int lastStatsId = 0;
if (query.read())
{
lastStatsId = query.getInt("statsId");
App.log($"last batch : {lastStatsId}");
}
App.log("get next delta batch");
sql = $@"SELECT TOP 1 * FROM [snapshotValues] AS sv
INNER JOIN [tables]
ON
sv.[tableId]=[tables].[id]
WHERE [statsId]>{lastStatsId} ORDER BY [statsId] ASC;";
query = Query.reader(App.allDb, sql);
if (query.read())
{
int nextStatsId = query.getInt("statsId");
string tableName = query.get("name");
string op = query.get("op");
Table table = App.allDb.tables[tableName];
App.log($"next batch : {nextStatsId}");
int records = table.processDeltas(nextStatsId);
// on success
sql = $@"INSERT INTO [deltas]
([statsId],[op],[records])
VALUES
({nextStatsId},'{op}',{records})
;";
App.snapshotDb.run(sql);
}
else
{
App.log("no pending deltas");
return;
}
}
public int processDeltas(int statsId)
{
App.log("load values for delta batch");
string sql = $@"SELECT * FROM [snapshotValues] AS sv
INNER JOIN [tables]
ON
sv.[tableId]=[tables].[id]
WHERE sv.[statsId]={statsId} AND [op]='u'
ORDER BY [_id],[op]
;";
Query query = Query.reader(App.allDb, sql);
int i = 0;
while (query.read())
{
string op = query.get("op");
int _id = query.getInt("_id"); // the object id
int ordinal = query.getInt("ordinal");
string current = query.get("current");
string old = query.get("old"); // previous value
string[] _oldValues = old.Split(App.fieldListSeparatorChar);
Dictionary <string, object> currentValues = parseSnapshotFieldListValues(current, ordinal);
if (op == "u")
{
}
else if (op == "i")
{
}
else if (op == "d")
{
}
else
{
}
i++;
}
if (i == 0)
{
App.log("no records in delta batch");
return(0);
}
return(i);
}
public void update(string tableName, string[] fields, string[] values, string where)
{
string sql = $"UPDATE [{tableName}] SET ";
int i = 0;
foreach (string field in fields)
{
if (i > 0)
{
sql += ",";
}
string value = values[i];
i++;
sql += $"[{field}] = {value}";
}
sql += $" WHERE {where} ;";
if (!connected)
{
connect();
}
timing.start();
cmd = new OleDbCommand(sql, conn);
string errMsg = "";
try
{
App.log("exec update query " + sql);
rowsAffected = cmd.ExecuteNonQuery();
App.log("done");
}
catch (Exception e)
{
errMsg = e.Message;
App.error($@"Db.sql
{e.Message}
{sql}
");
}
timing.stop();
timing.log($@"{errMsg} : {sql}");
}
public bool getLastUpdate(bool willSave = true)
{
// TODO : originalDb not updated via UI edits
DataTable schema = App.originalDb.getTables(name);
DateTime lastUpdate = DateTime.Parse(schema.Rows[0]["DATE_MODIFIED"].ToString());
TimeSpan t = lastUpdate - updated;
if (!lastUpdate.Equals(updated))
{
App.log($"Table changed (last remembered update : {updated} | latest update ({lastUpdate}) ");
updated = lastUpdate;
updatedChanged = true;
if (willSave)
{
save(false);
}
}
else
{
App.log($"no changes based on table lupdate date | (last remembered update : {updated} | latest update ({lastUpdate}) ");
}
return(updatedChanged);
}
public int execute()
{
if (!db.connected)
{
db.connect();
}
start();
cmd = new OleDbCommand(sql, db.conn);
App.log("exec query " + sql);
rowsAffected = 0;
try
{
rowsAffected = cmd.ExecuteNonQuery();
App.log("done");
stop();
log(sql);
}
catch (Exception e)
{
error(e);
}
return(rowsAffected);
}
public void next()
{
App.log("next file");
// load the next file
string tables = "tables";
string where = $"queueId={id} and queueRun<{queueRun}";
int count = App.snapshotDb.recCount(tables, where);
if (count == 0)
{
App.log("queue processed/empty, next queue run");
// next run
queueRun++;
where = $"queueId={id} and queueRun<{queueRun}";
save();
}
// sort by high numbers first - by default tables will by 0
OleDbDataReader reader = App.snapshotDb.query(tables, where, "priority DESC");
if (reader.HasRows)
{
// still jobs in queue
App.log("jobs in queue");
reader.Read(); // load the table details
string sql;
// change back to tableId when using queueTables
int tableId = int.Parse(reader.GetValue(reader.GetOrdinal("id")).ToString());
//string tableName = App.snapshot.lookup("tables", tableId,null,"id");
string tableName = reader.GetValue(reader.GetOrdinal("name")).ToString();
reader.Close(); //
Table table = App.allDb.tables[tableName];
// once per pass
table.getRecCount();
// need to retain the query objects
App.log("look for updates " + tableName);
Table.checkForUpdates(id, tableName);
App.log("look for inserts " + tableName);
Table.checkForInserts(id, tableName);
App.log("look for deletes " + tableName);
Table.checkForInserts(id, tableName);
// only on success
App.log("move this table to next run");
// TODO change to tableid qhen using queueTables
// starnge I didn't get a query error on tableId I think tableId has meaning in msaccess!! It just hung!!
App.snapshotDb.update(tables, "queueRun", $"{queueRun}", $"queueId={id} AND id={tableId}");
App.log("queue pause");
// **** now set the wait
timer.Start();
}
else
{
App.log("looks like no tables in this queue at all");
// guessing an empty queue!!
}
}
private void OnTimedEvent(Object source, System.Timers.ElapsedEventArgs e)
{
App.log("queue wakeup " + e.SignalTime);
next();
}
public void insert(string tableName, string fields, string values)
{
string[] _fields = fields.Split(',');
fields = String.Join(",", _fields.Select(x => $"[{x}]").ToArray());
// another kludge for sql strings -
// I'll assume 1 delimited by a special pair of chars - like sql dates
// ~sql~
// split string BEFORE break into commas
var hasSql = false;
var sqlQuery = "";
if (values.Contains(App.sqlStringValueDelim))
{
hasSql = true;
string[] temp = values.Split(App.sqlStringValueDelim);
// assuming 1 sql strung only, so 3 parts
values = temp[0] + "@sql" + temp[2];
sqlQuery = temp[1];
}
// this will f**k with function calls and expressions
char sep = ',';
if (values[0] == '|')
{
sep = '|';
values = values.Substring(1);
}
// now we can split the values on , without worrying about 's in sql strings
// damn - I coulda used the | hack I wrote earlier!!
string[] _values = values.Split(sep);
// temp array for params
string[] _params = new string[10];
int offset = 0; // keep track of param
int i = 0;
foreach (string value in _values)
{
if (value[0] == App.sqlStringValueMarker)
{
_params[offset] = value.Substring(1); // remove marker
_values[i] = $"@{offset}";
offset++; // only increment when sql found
}
i++; // loop counter
}
int maxOffset = offset;
// oh balls would need to convert them back to their original data types so sql can convert them back
// I'm only worried about strings, so I'll use placeholders only for strings
// in most cases, I'm trying to encode SQL queries!!
// maybe use a special syntax such as ^ to denote an sql query string
// now use the values with params - use ordinals as placeholders
// need to rejoin them!! this is simply for compat with update
values = String.Join(",", _values); // includes placeholders
//values = values.Replace("'", "\""); // escape
string sql = $"INSERT INTO [{tableName}] ({fields}) VALUES ({values})";
if (!connected)
{
connect();
}
timing.start();
// escaping single quotes
//sql = sql.Replace("'", "'''"); // 3 '
cmd = new OleDbCommand(sql, conn);
offset = 0;
for (offset = 0; offset < maxOffset; offset++)
{
cmd.Parameters.AddWithValue($"@{offset}", _params[offset]);
}
if (hasSql)
{
cmd.Parameters.AddWithValue($"@sql", sqlQuery);
}
string errMsg = "";
try
{
App.log("exec insert query " + sql);
rowsAffected = cmd.ExecuteNonQuery();
App.log("done");
}
catch (Exception e)
{
errMsg = e.Message;
App.error($@"Db.sql
{e.Message}
{sql}
");
}
timing.stop();
timing.log($@"{errMsg} : {sql}");
}
/*
* change so that snapshotValues holds the actual data - 1 rec pre fieldlist
* this makes updates eaaier to detect
*/
public static void checkForInserts(int queueId, string tableName)
{
Db db = App.allDb;
Table table = App.snapshotDb.tables[tableName];
string op = "i";
//int count = db.recCount($"{table.name}");
//table.getRecCount();
// an unchanged record count not sufficient.
if (table.recCount == 0)
{
App.log($"Check for Inserts - no records on table {table.name}");
return;
}
// check last update v last scan - don't scan if no change since last scan
if (App.useTableLastUpdateInQueries && !table.updatedChanged)
{
App.log($"Check for Inserts - no updates based on date {table.name}");
return;
}
// if no change
table.getNewRecordCount();
if (table.newRecordCount == 0) // set by getNewRecordCount
{
Console.WriteLine($"Check for Inserts - no NEW records on table {table.name}");
return;
}
if (table.fieldLists == null)
{
table.getFieldLists();
}
int statsId = App.allDb.getNewStats(op, table.id);
// no longer needed
//string inClause = table.getNewRecordIdsClause();
// we have new inserts - get the expressions
Query query = null;
int i = 0;
string sql = "";
foreach (string expr in table.fieldLists)
{
int fieldListId = table.fieldListIds[i];
/*
* The joins are provi9ng very troublesome to update
* go for multiple passes to update the two tables
*/
if (i == 0) // first pass only, insert into snapshot
{
sql = $@"UPDATE [{table.name}] AS lhs
LEFT JOIN
(SELECT * FROM snapshot
WHERE [tableId] = {table.id}
) AS snapshot
ON lhs.[_id] = [snapshot].[_id]
SET
snapshot.[op] = 'i',
snapshot.[_id] = [lhs].[_id],
snapshot.[tableId] = {table.id},
snapshot.[statsId] = {statsId}
";
query = App.allDb.run(sql);
// App.allDb.recordStats(statsId, table.newRecordCount);
query.rowsAffected = db.recCount("snapshot", $"statsId = {statsId}");
sql = $@"INSERT INTO queueStats
([tableId],[op],[statsId],[start],[end],[duration],[records],[recCount],[errorMsg])
VALUES
({table.id},'i',{statsId},#{query.getStart()}#,#{query.getEnd()}#,
{query.getDuration()},{query.rowsAffected},{table.recCount},'{query.errorMsg}')
;";
db.sql(sql); // I really don't want to log this!!
}
// now update the snapshotValues
// all in table, and matching values snapshot & values
sql = $@"UPDATE [{table.name}] AS lhs
LEFT JOIN
(SELECT * FROM snapshotValues
WHERE [fieldlistId]={fieldListId}
) AS sv
ON lhs.[_id] = [sv].[_id]
SET
sv.[_id] = lhs.[_id],
sv.[statsId] = {statsId},
sv.[op] = 'i',
sv.[tableId] = {table.id},
sv.[ordinal] = {i + 1},
sv.[fieldListId] = {fieldListId},
sv.[current] = {expr}
";
query = App.allDb.run(sql);
query.rowsAffected = db.recCount("snapshotValues", $"statsId = {statsId}");
sql = $@"INSERT INTO queueStats
([queueId],[tableId],[op],[statsId],[start],[end],[duration],[records],[recCount],[errorMsg])
VALUES
({queueId},{table.id},'i',{statsId},#{query.getStart()}#,#{query.getEnd()}#,
{query.getDuration()},{query.rowsAffected},{table.recCount},'{query.errorMsg}')
;";
db.sql(sql); // I really don't want to log this!!
i++; //next field list
}
//int count = db.recCount("snapshot", $"statsId={statsId}");
//return ;
}
public static void checkForUpdates(int queueId, string tableName)
{
Db db = App.allDb;
Table table = App.snapshotDb.tables[tableName];
string op = "u";
//int count = db.recCount($"{table.name}");
// an unchanged record count not sufficient.
if (table.recCount == 0)
{
App.log($"Check for Updates - no records on table {table.name}");
return;
}
// check last update v last scan - don't scan if no change since last scan
if (App.useTableLastUpdateInQueries && !table.updatedChanged)
{
App.log($"Check for Updates - no updates based on date {table.name}");
return;
}
// if no change
if (table.fieldLists == null)
{
table.getFieldLists();
}
int statsId = App.allDb.getNewStats(op, table.id);
Timing timing = new Timing(db);
string errMsg = "";
int i = 0;
string sql = "";
foreach (string expr in table.fieldLists)
{
int fieldListId = table.fieldListIds[i];
// now update the snapshotValues
// break into 2 queries - its generating lots of unnecessary records
sql = $@"UPDATE [{table.name}] AS lhs
LEFT JOIN
( SELECT * FROM snapshotValues
WHERE [fieldlistId] = {fieldListId}
) as sv
ON
lhs.[_id] = sv.[_id]
SET
sv.[_id] = lhs.[_id],
sv.[statsId] = {statsId},
sv.[op] = 'u',
sv.[tableId] = {table.id},
sv.[ordinal] = {i + 1},
sv.[fieldListId] = {fieldListId},
sv.[old]=sv.[current],
sv.[current] = {expr}
WHERE
sv.[current] <> {expr} OR sv.[current] IS NULL
";
Query query = App.allDb.run(sql);
errMsg += query.errorMsg;
i++; //next field list
}
timing.stop();
int rowsAffected = db.recCount("snapshotValues", $"statsId = {statsId}");
App.log("update stats");
sql = $@"INSERT INTO queueStats
([queueId],[tableId],[op],[statsId],[start],[end],[duration],[records],[recCount],[errorMsg])
VALUES
({queueId},{table.id},'u',{statsId},#{timing.getStart()}#,#{timing.getEnd()}#,
{timing.getDuration()},{rowsAffected},{table.recCount},'{errMsg}')
;";
db.sql(sql); // I really don't want to log this!!
}
public static Query checkForDeletes(int queueId, string tableName)
{
Db db = App.allDb;
Table table = App.snapshotDb.tables[tableName];
string op = "d";
//int count = db.recCount($"{table.name}");
// an unchanged record count not sufficient.
// deletes may be why count is 0!!
//if (table.recCount == 0)
//{
// App.log($"Check for Deletes - no records on table {table.name}");
// return;
//}
// check last update v last scan - don't scan if no change since last scan
if (App.useTableLastUpdateInQueries && !table.updatedChanged)
{
App.log($"Check for Deletes - no updates based on date {table.name}");
return(null);
}
// if no change
if (table.fieldLists == null)
{
table.getFieldLists();
}
int statsId = App.allDb.getNewStats(op, table.id);
int i = 0;
string sql = "";
// no need to update snapshotValues
//foreach (string expr in table.fieldLists)
//{
// int fieldListId = table.fieldListIds[i];
// now update the snapshotValues
sql = $@"UPDATE [{table.name}] AS lhs
RIGHT JOIN
(SELECT snapshot.[_id],snapshot.[op] as op1,snapshot.[statsId],sv.[op] as op2,sv.snapshotId FROM snapshot
RIGHT JOIN snapshotValues AS sv
ON snapshot.id = sv.snapshotId
WHERE [snapshot].[tableId] = {table.id} AND op1 <> 'd'
) AS snapshot
ON lhs.[_id] = [snapshot].[_id]
SET
snapshot.[op1] = 'd',
snapshot.[op2] = 'd',
snapshot.[statsId] = {statsId}
WHERE
lhs.[_id] IS NULL
";
Query query = App.allDb.run(sql);
query.rowsAffected = db.recCount("snapshot", $"statsId = {statsId}");
sql = $@"INSERT INTO queueStats
([queueId],[tableId],[op],[statsId],[start],[end],[duration],[records],[recCount],[errorMsg])
VALUES
({queueId},{table.id},'d',{statsId},#{query.getStart()}#,#{query.getEnd()}#,
{query.getDuration()},{query.rowsAffected},{table.recCount},'{query.errorMsg}')
;";
db.sql(sql); // I really don't want to log this!!
// i++; //next field list
//}
return(query);
}
