private void WriteToDatabase(IEnumerable <Report> reports, string filename)
{
using (var conn = SQLiteHelpers.CreateConnection(filename))
{
conn.Open();
var command = new SQLiteCommand(conn);
command.CommandText =
"CREATE TABLE IF NOT EXISTS reports (id INTEGER PRIMARY KEY, class TEXT NULL, method TEXT NULL, timeInMs INTEGER NULL);";
command.ExecuteNonQuery();
command.CommandText = "DELETE FROM reports;";
command.ExecuteNonQuery();
command.Dispose();
foreach (var report in reports)
{
using (command = new SQLiteCommand(conn))
{
command.CommandText = $"INSERT INTO reports (class, method, timeInMs) VALUES (@Param1, @Param2, @Param3);";
command.Parameters.AddWithValue("@Param1", report.ClassName);
command.Parameters.AddWithValue("@Param2", report.MethodName);
command.Parameters.AddWithValue("@Param3", report.Time.TotalMilliseconds);
command.ExecuteNonQuery();
}
}
conn.Close();
}
}
private static List <Data> RetrieveData(StatisticalTableOptions options)
{
var metric = GetMetric(options.Metric);
List <Data> data = new List <Data>();
using (var connection = SQLiteHelpers.CreateConnection(options.DatabaseFile))
{
connection.Open();
var secondaries = string.Join(",", options.Secondaries);
//string cText = $"SELECT {options.Main} as _main, {metric}({options.Value}) as _value, _machine, {secondaries}{GetMainGroup(options)} FROM {GetUnion(options.DatabaseTable)} group by _machine, {options.Main}, {secondaries}";
string cText =
$"SELECT {options.Main} as _main, _value, _machine, {secondaries}{GetMainGroup(options)} FROM {GetUnion(options.DatabaseTable, options.Main, secondaries, metric, options.Value)}";
if (options.Verbose)
{
Console.WriteLine("Command Text: " + cText);
}
using (var command = new SQLiteCommand(connection)
{
CommandText = cText
})
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var d = new Data
{
Main = TypeHelpers.GetAsString(reader["_main"]),
Value = TypeHelpers.GetAsDouble(reader["_value"]),
Secondaries = options.Secondaries.Select(name => TypeHelpers.GetAsInt(reader[name]))
.ToArray(),
Machine = TypeHelpers.GetAsInt(reader["_machine"])
};
if (!string.IsNullOrEmpty(options.MainGroup))
{
d.MainGroup = TypeHelpers.GetAsString(reader[options.MainGroup]);
}
data.Add(d);
}
reader.Close();
}
connection.Close();
}
return(data);
}
public override int ExecuteCommand(SqlPlotToolsHackOptions options)
{
var input = FileService.ReadLinesLazily(options.InputFile);
using (var connection = SQLiteHelpers.CreateConnection(options.DatabaseFile))
{
connection.Open();
foreach (var block in GetBlocks(input.Where(IsResultLine).Select(GetValue), blockSize: 1000))
{
using (var command = new SQLiteCommand(connection))
{
command.CommandText = $"INSERT INTO {options.DatabaseTable} VALUES {block};";
command.ExecuteNonQuery();
}
}
connection.Close();
}
return(0);
}
private static List <Data> RetrieveData(TestOptions options)
{
var data = new List <Data>();
using (var connection = SQLiteHelpers.CreateConnection(options.DatabaseFile))
{
connection.Open();
string cText = $"SELECT s, a, average, _machine FROM {GetUnion(options)}";
if (options.Verbose)
{
Console.WriteLine("Command Text: " + cText);
}
using (var command = new SQLiteCommand(connection)
{
CommandText = cText
})
using (var reader = command.ExecuteReader())
{
var getMachine = GetMachine(options);
while (reader.Read())
{
var d = new Data
{
Sorter = TypeHelpers.GetAsString(reader["s"]),
ArraySize = TypeHelpers.GetAsInt(reader["a"]),
Average = TypeHelpers.GetAsDouble(reader["average"]),
Machine = getMachine(TypeHelpers.GetAsInt(reader["_machine"]))
};
data.Add(d);
}
reader.Close();
}
connection.Close();
}
return(data);
}
private List <Data> RetrieveData(StatisticalFunctionsOptions options)
{
List <Data> data = new List <Data>();
Log.Info($"Reading data from '{options.DatabaseFile}'");
using (var connection = SQLiteHelpers.CreateConnection(options.DatabaseFile))
{
connection.Open();
var groups = string.Join(",", options.Groups);
foreach (var table in options.DatabaseTable)
{
var commandText = $"SELECT {options.Value} as _value, {groups} FROM {table}";
Log.Info($"Executing command '{commandText}'");
using (var command = new SQLiteCommand(connection)
{
CommandText = commandText
})
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
data.Add(new Data
{
Value = TypeHelpers.GetAsDouble(reader["_value"]),
Groups = options.Groups.Select(g => TypeHelpers.GetAsString(reader[g])).ToArray()
});
}
reader.Close();
}
}
Log.Info("Closing Database");
connection.Close();
}
return(data);
}
public int ExecuteCommand(StatisticalFunctionsOptions options)
{
Log = new VerboseLogger(options);
List <Data> data;
try
{
data = RetrieveData(options);
}
catch (Exception exc)
{
Log.Error(exc, "Error while reading data from database");
return(-1);
}
if (data == null || data.Count == 0)
{
Log.Error($"No data found in table {options.DatabaseTable}");
return(1);
}
var funcs = options.Functions.ToArray();
var results = new string[funcs.Length];
Data[][] res = new Data[funcs.Length][];
for (int i = 0; i < funcs.Length; i += 1)
{
switch (funcs[i])
{
case "average":
Log.Info("Calculating average");
res[i] = Calculate(data, d => d.Average(x => x.Value));
break;
case "median":
Log.Info("Calculating median");
res[i] = Calculate(data, CalculateMedian);
break;
case "variance":
Log.Info("Calculating variance");
res[i] = Calculate(data, CalculateVariance);
break;
}
}
Log.Info($"Opening {options.DatabaseFile}");
using (var connection = SQLiteHelpers.CreateConnection(options.DatabaseFile))
{
connection.Open();
var command = new SQLiteCommand(connection)
{
CommandText =
$"CREATE TABLE IF NOT EXISTS {options.DatabaseTableOut} ({GetColumns(options.Groups, "TEXT")}, {GetColumns(funcs, "REAL")});"
};
Log.Info($"Executing Command: {command.CommandText}");
command.ExecuteNonQuery();
List <string> valuesList = new List <string>();
foreach (var d in res[0])
{
var groups = string.Join(",", d.Groups.Select(g => $"'{g}'"));
var values = string.Join(",", FindValues(d, res));
valuesList.Add($"({groups}, {values})");
}
command.CommandText = $"INSERT INTO {options.DatabaseTableOut} VALUES {string.Join(",", valuesList)};";
Log.Info($"Inserting Values into: {options.DatabaseTableOut}");
command.ExecuteNonQuery();
command.Dispose();
Log.Info("Closing Database");
connection.Close();
}
return(0);
}