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