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