/// <summary>
/// Gets the current rows from the table. Since this is accomplished with a trigger table its just a small querie.
/// </summary>
/// <param name="table">The table to get the current rows from.</param>
/// <returns>Returns the number of rows in the given table</returns>
public override int GetCurrentRowsFromTable(Table table)
{
try
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Select().AddValue("rowCount").From().AddValue(table.TableName + "_count").Where().AddValue("id").Equal().AddValue("1");
List <List <object> > result = ReadQuery(sqb.ToString(),
new List <KeyValuePair <int, Type> >()
{
new KeyValuePair <int, Type>(0, typeof(int))
});
if (result == null)
{
return(0);
}
else
{
return((int)result[0][0]);
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Generates a querie list to create tables.
/// <para/>
/// SQL query Example: <para/>
/// CREATE TABLE table1 (id INTEGER PRIMARY KEY, firstColumn TEXT NOT NULL, secondColumn REAL NOT NULL)
/// </summary>
/// <returns>Returns a list of queries to create the local tables.</returns>
private List <string> CreateTableQueries(Dictionary <string, Table> tables)
{
List <string> tableQueries = new List <string>();
foreach (KeyValuePair <string, Table> table in tables)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
List <string> paramList = new List <string>();
int iter = 0;
foreach (KeyValuePair <string, Type> column in table.Value.Columns)
{
sqb.AddValue(column.Key);
if (column.Value == typeof(int))
{
sqb.TypeInteger();
}
else if (column.Value == typeof(string))
{
sqb.TypeText();
}
else if (column.Value == typeof(double))
{
sqb.TypeReal();
}
else if (column.Value == typeof(float))
{
sqb.TypeReal();
}
else if (column.Value == typeof(DateTime))
{
sqb.TypeText();
}
else
{
throw new NotSupportedException(column.Value.Name + " Datatype not supported");
}
if (iter.Equals(0))
{
sqb.ParamPrimaryKey();
}
else
{
sqb.ParamNot().Null();
}
paramList.Add(sqb.Flush());
iter++;
}
string values = sqb.Brackets_Multiple(paramList, false).Flush();
sqb.Create().Table().IfNotExists().AddValue(table.Value.TableName).AddValue(values);
tableQueries.Add(sqb.ToString());
tableQueries.Add(CreateTriggerTable(table.Value.TableName));
tableQueries.Add(InsertStartValueTriggerTable(table.Value.TableName));
tableQueries.Add(CreateCounterAddTriger(table.Value.TableName));
tableQueries.Add(CreateCounterSubTriger(table.Value.TableName));
}
return(tableQueries);
}
/// <summary>
/// Gets the current rows from the table. There are no triggers in access so i had to do it with the count method.
/// </summary>
/// <param name="table">The table to get the current rows from.</param>
/// <returns>Returns the number of rows in the given table</returns>
public override int GetCurrentRowsFromTable(Table table)
{
try
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
string columnName = table.Columns.First().Key;
sqb.Select().AddValue("Count(" + columnName + ")").From().AddValue(table.TableName);
List <List <object> > result = ReadQuery(sqb.ToString(),
new List <KeyValuePair <int, Type> >()
{
new KeyValuePair <int, Type>(0, typeof(int))
});
if (result == null)
{
return(0);
}
else
{
return((int)result[0][0]);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Creates the trigger table where the row count is automatically update through a custom insert and delete trigger.
/// <para/>
/// SQL query Example: <para/>
/// create table if not exists tableName_count (id integer primary key, number int);
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <returns>The finished sql string.</returns>
private string CreateTriggerTable(string tableName)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
string param = sqb.Brackets(sqb.AddValue("id").TypeInteger().ParamPrimaryKey().Comma().AddValue("rowCount").TypeInteger().Flush()).Flush();
sqb.Create().Table().IfNotExists().AddValue(tableName + "_count").AddValue(param);
return(sqb.ToString());
}
/// <summary>
/// Inserts the first and only row of this table. Bascially its set's the rowCOunt to 0.
/// <para/>
/// SQL query Example: <para/>
/// insert into table1Count (rowCount) values (0);
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <returns>The finished sql string.</returns>
private string InsertStartValueTriggerTable(string tableName)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
string param = sqb.AddValue("id").Comma().AddValue("rowCount").Flush();
sqb.Insert().Or().Ignore().Into().AddValue(tableName + "_count").Brackets(param).Values().Brackets("1, 0");
return(sqb.ToString());
}
/// <summary>
/// Get's the last n rows from the specified table.
/// <para/>
/// SQL query Example:<para/>
/// SELECT * FROM table ORDER BY id DESC LIMIT 100
/// </summary>
/// <param name="rows">number of the rows to display.</param>
/// <param name="table">The table to get the values from.</param>
/// <returns></returns>
public override List <List <object> > GetLastNRowsFromTable(Table table, int rows)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Select().ALL().From().AddValue(table.TableName).OrderBY().AddValue(table.Columns.First().Key).Desc().Limit().AddValue(rows.ToString());
List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
return(results);
}
/// <summary>
/// Creates the delete trigger to keep track of the row count.
/// Basically every delete command subtracts +1 to the row count.
/// <para/>
/// SQL query Example: <para/>
/// create trigger if not exists table_trigger_sub after delete on table BEGIN update table_count set rowCount = rowCount - 1 where id = 1; END
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <returns>The finished sql string.</returns>
private string CreateCounterSubTriger(string tableName)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Create().Trigger().IfNotExists().AddValue(tableName + "_trigger_sub").After().Delete().On().AddValue(tableName);
sqb.Begin().Update().AddValue(tableName + "_count").Set().AddValue("rowCount").Equal().AddValue("rowCount - 1");
sqb.Where().AddValue("id").Equal().AddValue("1").CommaPoint(true).End();
return(sqb.ToString());
}
/// <summary>
/// Drops the tables with the given table names.
/// <para/>
/// SQL query Example: <para/>
/// DROP TABLE table1
/// </summary>
/// <param name="tableNames">A list of table names to delete.</param>
public override void DropTables(List <string> tableNames)
{
foreach (var tableName in tableNames)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.DropTable().AddValue(tableName);
CommitQuery(sqb.ToString());
}
}
/// <summary>
/// Deletes the last n rows of the given table.
/// </summary>
/// <param name="table">The table to delete the last n data from.</param>
/// <param name="rows">The amount of data to delete.</param>
public override void DeleteLastNRows(Table table, int rows)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
string columnName = table.Columns.First().Key;
string param = sqb.Select().AddValue(columnName).From().AddValue(table.TableName).OrderBY().
AddValue(columnName).Asc().Limit().AddValue(rows.ToString()).Flush();
sqb.Delete().From().AddValue(table.TableName).Where().AddValue(columnName).In().Brackets(param);
CommitQuery(sqb.ToString());
}
/// <summary>
/// Gets all rows in the given id slot.
/// <para/>
/// SQL query Example: <para/>
/// SELECT * from table3 where id >= 0 and id -= 1
/// </summary>
/// <param name="table"></param>
/// <param name="start"></param>
/// <param name="end"></param>
/// <returns></returns>
public override List <List <object> > GetRowsFromTableWithIndex(Table table, int start, int end)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(table.Columns.First().Key);
sqb.GreaterThen().AddValue(start.ToString()).AND().AddValue(table.Columns.First().Key).LesserThen().AddValue(end.ToString());
List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
return(results);
}
/// <summary>
/// Gets all rows in the given DateTime slot.
/// <para/>
/// SQL query Example: <para/>
/// select * from table3 where Date >= "2018-12-06 11:10:32.632" and Date -= "2018-12-06 12:05:57.526";
/// </summary>
/// <param name="table">The name of the table to get the data from.</param>
/// <param name="DateTimeColumnName">The name of the column with the DateTime values.</param>
/// <param name="from">A DateTime object with the beginning of the timeslot.</param>
/// <param name="until">A DateTime object with the end of the timeslot.</param>
/// <returns></returns>
public override List <List <object> > GetRowsFromTableWithTime(Table table, string DateTimeColumnName, DateTime from, DateTime until)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
string stringFrom = sqb.Apostrophe(from.ToString(_stringFormat)).Flush();
string stringUntil = sqb.Apostrophe(until.ToString(_stringFormat)).Flush();
sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(DateTimeColumnName);
sqb.GreaterThen().AddValue(stringFrom).AND().AddValue(DateTimeColumnName).LesserThen().AddValue(stringUntil);
List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
return(results);
}
/// <summary>
/// If the current rowCount value is higher then the MaxRows value the deletion will be started.
/// Now the size of the table will be shrinked to 70% of the MaxRows value. The oldest values will be deleted.
/// If the amount of the rows to be deleted is higher than the global _maxDeleteRowSize then only every 5 seconds the highest amount possible
/// will be deleted until the initial calculated amountToDelete is 0.
/// <para/>
/// Example: <para/>
/// If you have 50 000 000 as MaxRows defined. Then 70% would be 35 000 000 rows. So 15 000 000 would have to be deleted.
/// If youre _maxDeleteRowSize is 100 000 this would mean 150 turns every 5 seconds. This would take then round about 12,5 minutes to delete all of them.
/// If you calculated with 500 entries per second. This would only mean 375 000 entries in these 12,5 minutes.
/// So this should be enough time to delete all entries with plenty of time in between for other sources to write to the database.
/// </summary>
/// <param name="tables">All saved tables.</param>
public override void CheckDeleteTables(ConcurrentDictionary <string, Table> tables)
{
try
{
foreach (Table table in tables.Values)
{
// Get the current row value
long result = GetCurrentRowsFromTable(table);
if (result >= table.MaxRows)
{
// Calculate 70% and the amount to delete
double seventyPercent = (double)table.MaxRows * (double)0.7;
long amountToDelete = result - (long)Math.Round(seventyPercent);
string text = "Started to delete entries on table: " + table.TableName + Environment.NewLine;
text += "Current Rows: " + result + " | Max table rows: " + table.MaxRows + " | Amount to delete: " + amountToDelete;
OnDeleteEvent(text);
while (amountToDelete > 0)
{
long rows;
if (amountToDelete > MaxDeleteRowSize)
{
rows = MaxDeleteRowSize;
}
else
{
rows = amountToDelete;
}
amountToDelete -= rows;
SQLQueryBuilder sqb = new SQLQueryBuilder();
string columnName = table.Columns.First().Key;
string param = sqb.Select().Top().AddValue(rows.ToString()).AddValue(columnName).From().AddValue(table.TableName)
.OrderBY().AddValue(columnName).Desc().Flush();
sqb.Delete().From().AddValue(table.TableName).Where().AddValue(columnName).In().Brackets(param);
CommitQuery(sqb.ToString());
Thread.Sleep(5000);
}
OnDeleteEvent("Finished the Deletion of the table: " + table.TableName);
}
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Creates the insert trigger to keep track of the row count.
/// Basically every insert command adds +1 to the row count.
/// <para/>
/// SQL query Example: <para/>
/// create trigger if not exists table_trigger_add after insert on table BEGIN update table_count set rowCount = rowCount + 1 where id = 1; END
/// </summary>
/// <param name="tableName">The name of the table.</param>
/// <returns>The finished sql string.</returns>
private string CreateCounterAddTriger(string tableName)
{
try
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Create().Trigger().IfNotExists().AddValue(tableName + "_trigger_add").After().Insert().On().AddValue(tableName);
sqb.Begin().Update().AddValue(tableName + "_count").Set().AddValue("rowCount").Equal().AddValue("rowCount + 1");
sqb.Where().AddValue("id").Equal().AddValue("1").CommaPoint(true).End();
return(sqb.ToString());
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Deletes the last n rows of the given table.
/// </summary>
/// <param name="table">The table to delete the last n data from.</param>
/// <param name="rows">The amount of data to delete.</param>
public override void DeleteLastNRows(Table table, int rows)
{
// TODO: Check Query
try
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
string columnName = table.Columns.First().Key;
string param = sqb.Select().Top().AddValue(rows.ToString()).AddValue(columnName).From().AddValue(table.TableName)
.OrderBY().AddValue(columnName).Desc().Flush();
sqb.Delete().From().AddValue(table.TableName).Where().AddValue(columnName).In().Brackets(param);
CommitQuery(sqb.ToString());
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Drops the tables with the given table names.
/// DROP TABLE table1
/// </summary>
/// <param name="tableNames">A list of table names to delete.</param>
public override void DropTables(List <string> tableNames)
{
try
{
List <string> tableQueries = new List <string>();
foreach (var tableName in tableNames)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.DropTable().AddValue(tableName);
tableQueries.Add(sqb.ToString());
}
CommitBatchQuery(tableQueries);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Gets all rows in the given id slot.
/// <para/>
/// </summary>
/// <param name="table"></param>
/// <param name="start"></param>
/// <param name="end"></param>
/// <param name="ascending">Ascending or descending by first param.</param>
/// <returns></returns>
public override List <List <object> > GetRowsFromTableWithIndex(Table table, int start, int end, bool ascending = true)
{
try
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(table.Columns.First().Key);
sqb.GreaterThen().AddValue(start.ToString()).AND().AddValue(table.Columns.First().Key).LesserThen().AddValue(end.ToString());
if (!ascending)
{
sqb.OrderBY().AddValue(table.Columns.First().Key).Desc();
}
List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
return(results);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Get's the last n rows from the specified table.
/// <para/>
/// SQL query Example:<para/>
/// SELECT * FROM (SELECT * FROM name.table3 ORDER BY id DESC LIMIT 1) ORDER BY id ASC;
/// </summary>
/// <param name="rows">number of the rows to display.</param>
/// <param name="table">The table to get the values from.</param>
/// <param name="ascending">Ascending or descending by first param.</param>
/// <returns></returns>
public override List <List <object> > GetLastNRowsFromTable(Table table, int rows, bool ascending = true)
{
try
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Select().ALL().From().AddValue(table.TableName).OrderBY().AddValue(table.Columns.First().Key).Desc().Limit().AddValue(rows.ToString());
if (ascending)
{
string value = sqb.Flush();
sqb.Select().ALL().From().Brackets(value).OrderBY().AddValue(table.Columns.First().Key).Asc();
}
List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
return(results);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Gets all rows in the given DateTime slot.
/// <para/>
/// </summary>
/// <param name="table">The name of the table to get the data from.</param>
/// <param name="DateTimeColumnName">The name of the column with the DateTime values.</param>
/// <param name="from">A DateTime object with the beginning of the timeslot.</param>
/// <param name="until">A DateTime object with the end of the timeslot.</param>
/// <param name="ascending">Ascending or descending by DateTimeColumn param.</param>
/// <returns></returns>
public override List <List <object> > GetRowsFromTableWithTime(Table table, string DateTimeColumnName, DateTime from, DateTime until, bool ascending = true)
{
try
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
string stringFrom = sqb.Tags(from.ToString(_stringFormat)).Flush();
string stringUntil = sqb.Tags(until.ToString(_stringFormat)).Flush();
sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(DateTimeColumnName);
sqb.GreaterThen().AddValue(stringFrom).AND().AddValue(DateTimeColumnName).LesserThen().AddValue(stringUntil);
if (!ascending)
{
sqb.OrderBY().AddValue(DateTimeColumnName).Desc();
}
List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
return(results);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Updates the given columns with the given id in the first column.
/// Each row of rowsToUpdate must have the same size as rowsData.
/// </summary>
/// <param name="tableName">The table where rows should be updated.</param>
/// <param name="rowsToUpdate">The rows with the name and data type to update.</param>
/// <param name="rowsData">The rows with all column data which should be updated.</param>
public override void UpdateTable(string tableName, List <Dictionary <string, Type> > rowsToUpdate, List <List <object> > rowsData)
{
int rowIter = 0;
List <string> queryList = new List <string>();
foreach (Dictionary <string, Type> row in rowsToUpdate)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
List <object> columnData = rowsData[rowIter];
List <string> columns = new List <string>();
if (!row.Count.Equals(columnData.Count))
{
throw new InvalidDataException(rowIter.ToString() + ". row size from rowsToUpdate doesn't match current row size from rowsData");
}
int columnIter = 0;
foreach (KeyValuePair <string, Type> column in row)
{
if (columnIter.Equals(0))
{
columnIter++;
continue;
}
if (!columnData[columnIter].GetType().Equals(column.Value))
{
throw new TypeLoadException("Type of the data doesn't match the columns type!");
}
if (column.Value == typeof(int))
{
sqb.AddValue(columnData[columnIter].ToString());
}
else if (column.Value == typeof(string))
{
sqb.Apostrophe(sqb.AddValue(columnData[columnIter].ToString()).Flush());
}
else if (column.Value == typeof(double))
{
sqb.AddValue(columnData[columnIter].ToString().Replace(',', '.'));
}
else if (column.Value == typeof(float))
{
sqb.AddValue(columnData[columnIter].ToString().Replace(',', '.'));
}
else if (column.Value == typeof(DateTime))
{
DateTime convertTime = (DateTime)columnData[columnIter];
sqb.Apostrophe(sqb.AddValue(convertTime.ToString(_stringFormat)).Flush());
}
else
{
throw new NotSupportedException(column.Value.Name + " Datatype not supported");
}
string value = sqb.Flush();
sqb.AddValue(column.Key).Equal().AddValue(value);
columns.Add(sqb.Flush());
columnIter++;
}
sqb.Update().AddValue(tableName).Set().AddValues(columns).Where().AddValue(row.First().Key).Equal().AddValue(columnData[0].ToString());
queryList.Add(sqb.ToString());
rowIter++;
}
CommitBatchQuery(queryList);
}
/// <summary>
/// Inserts the given objects into the given table. The first object "id" is ignored due to the auto increment,
/// <para/>
/// SQL query Example: <para/>
/// INSERT INTO table3 (Name, Date, value) VALUES ('John Doe', '2018-12-06 12:01:16.767', 22.5);
/// </summary>
/// <param name="tableName">The name of the table to insert rows to.</param>
/// <param name="rows">A list of rows with all column objects to insert.</param>
/// <param name="tables">All saved tables.</param>
public override void InsertIntoTable(string tableName, Dictionary <string, Table> tables, List <List <object> > rows)
{
List <string> queryList = new List <string>();
foreach (List <object> row in rows)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
List <string> firstBracket = new List <string>();
List <string> secondBracket = new List <string>();
int listIter = 0;
foreach (KeyValuePair <string, Type> column in tables[tableName].Columns)
{
if (listIter.Equals(0))
{
listIter++;
continue;
}
if (!row[listIter].GetType().Equals(column.Value))
{
throw new TypeLoadException("Type of the data doesn't match the columns type!");
}
firstBracket.Add(sqb.AddValue(column.Key).Flush());
if (column.Value == typeof(int))
{
sqb.AddValue(row[listIter].ToString());
}
else if (column.Value == typeof(string))
{
sqb.Apostrophe(sqb.AddValue(row[listIter].ToString()).Flush());
}
else if (column.Value == typeof(double))
{
sqb.AddValue(row[listIter].ToString().Replace(',', '.'));
}
else if (column.Value == typeof(float))
{
sqb.AddValue(row[listIter].ToString().Replace(',', '.'));
}
else if (column.Value == typeof(DateTime))
{
DateTime convertTime = (DateTime)row[listIter];
sqb.Apostrophe(sqb.AddValue(convertTime.ToString(_stringFormat)).Flush());
}
else
{
throw new NotSupportedException(column.Value.Name + " Datatype not supported");
}
secondBracket.Add(sqb.Flush());
listIter++;
}
string columnNames = sqb.Brackets_Multiple(firstBracket, false).Flush();
string columnValues = sqb.Brackets_Multiple(secondBracket, false).Flush();
sqb.InsertInto().AddValue(tableName).AddValue(columnNames).Values().AddValue(columnValues);
queryList.Add(sqb.ToString());
}
CommitBatchQuery(queryList);
}
/// <summary>
/// Generates a querie list to create tables.
/// <para/>
/// </summary>
/// <returns>Returns a list of queries to create the local tables.</returns>
private List <string> CreateTableQueries(ConcurrentDictionary <string, Table> tables)
{
try
{
List <string> tableQueries = new List <string>();
foreach (KeyValuePair <string, Table> table in tables)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
List <string> paramList = new List <string>();
int iter = 0;
foreach (KeyValuePair <string, Type> column in table.Value.Columns)
{
sqb.AddValue(column.Key);
if (iter.Equals(0))
{
sqb.AddValue("AUTOINCREMENT").ParamPrimaryKey();
paramList.Add(sqb.Flush());
iter++;
continue;
}
if (column.Value == typeof(int))
{
sqb.TypeInteger();
}
else if (column.Value == typeof(string))
{
sqb.TypeText();
}
else if (column.Value == typeof(double))
{
sqb.TypeReal();
}
else if (column.Value == typeof(float))
{
sqb.TypeReal();
}
else if (column.Value == typeof(DateTime))
{
sqb.TypeDateTime();
}
else
{
throw new NotSupportedException(column.Value.Name + " Datatype not supported");
}
sqb.ParamNot().Null();
paramList.Add(sqb.Flush());
iter++;
}
string values = sqb.Brackets_Multiple(paramList, false).Flush();
sqb.Create().Table().AddValue(table.Value.TableName).AddValue(values);
tableQueries.Add(sqb.ToString());
}
return(tableQueries);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Gets called if the Timer.Elapsed event is raised.
/// If the current rowCount value is higher then the MaxRows value in each table a deleteThread will be started.
/// Now the size of the table will be shrinked to 70% of the MaxRows value. The oldest values will be deleted.
/// If the amount of the rows to be deleted is higher than the global _maxDeleteRowSize then only every 5 seconds the highest amount possible
/// will be deleted until the the initial calculated RowsToDelete, which is stored in the Table object, is 0.
/// The thread is also stored in a global Dictionary so it can be aborted.
/// Every table can only have one DeleteThread active which is marked with the DeleteThreadActive variable.
/// <para/>
/// Example: <para/>
/// If you have 50 000 000 as MaxRows defined. Then 70% would be 35 000 000 rows. So 15 000 000 would have to be deleted.
/// If youre _maxDeleteRowSize is 100 000 this would mean 150 turns every 5 seconds. This would take then round about 12,5 minutes to delete all of them.
/// If you calculated with 500 entries per second. This would only mean 375 000 entries in these 12,5 minutes.
/// So this should be enough time to delete all entries with plenty of time in between for other sources to write to the database.
/// </summary>
/// <param name="tables">All saved tables.</param>
public override void OnDeleteTimer(Dictionary <string, Table> tables)
{
foreach (Table table in tables.Values)
{
long result = GetCurrentRowsFromTable(table);
if (result == 0)
{
continue;
}
if (result >= table.MaxRows)
{
// Calculate 70% and the amount to delete
double seventyPercent = (double)table.MaxRows * (double)0.7;
long amountToDelete = result - (long)Math.Round(seventyPercent);
Console.WriteLine(result + ">= " + table.MaxRows + " -> Clear the table: " + table.TableName);
// Start the Thread if it isn't active allready
if (!table.DeleteThreadActive)
{
table.RowsToDelete = amountToDelete;
table.DeleteThreadActive = true;
Thread deleteThread = new Thread(() =>
{
try
{
while (table.RowsToDelete > 0)
{
long rows;
if (table.RowsToDelete > MaxDeleteRowSize)
{
rows = MaxDeleteRowSize;
}
else
{
rows = table.RowsToDelete;
}
table.RowsToDelete -= rows;
SQLQueryBuilder deleteQuery = new SQLQueryBuilder();
string columnName = table.Columns.First().Key;
string param = deleteQuery.Select().AddValue(columnName).From().AddValue(table.TableName).OrderBY().
AddValue(columnName).Asc().Limit().AddValue(rows.ToString()).Flush();
deleteQuery.Delete().From().AddValue(table.TableName).Where().AddValue(columnName).In().Brackets(param);
CommitQuery(deleteQuery.ToString());
Console.WriteLine("Delete Thread deleted {0} rows!", rows);
Thread.Sleep(5000);
}
table.DeleteThreadActive = false;
_tableDeleteThreads.Remove(table.TableName);
}
catch (Exception)
{
}
});
_tableDeleteThreads.Add(table.TableName, deleteThread);
deleteThread.Start();
Console.WriteLine("Started Thread on: " + table.TableName);
}
}
}
}