public double GetInterval()
{
if (interval != 0)
{
return(interval);
}
else
{
return(TableProccessor.GetIntervalSize(name));
}
}
/// <summary>
/// get all numerical qfs
/// </summary>
/// <param name="Workload"></param>
/// <param name="columname"></param>
public static void GetNumericalQf(SQLQuery[] Workload, string columname)
{
double size = TableProccessor.GetIntervalSize(columname);
ColumnProperties properties = TableProccessor.ColumnProperties[columname];
List <SQLQuery> relevantQueries = new List <SQLQuery>();
int total = 0;
foreach (var query in Workload)
{
if (query.requiredValues.ContainsKey(columname))//only relevant queries are being used
{
relevantQueries.Add(query);
total += query.times;
}
}
Dictionary <double, double> Qfs = new Dictionary <double, double>();
Dictionary <double, double> RQfs = new Dictionary <double, double>();
double RQfMax = 0;
for (double d /*bij het ontbijt*/ = properties.min; d <= properties.max; d += size)
{
double qf = getNumericalQFFromU(d, relevantQueries, columname, total);
RQfs.Add(d, qf);
if (Math.Abs(RQfMax) < qf)//also take possible negative values into account
{
RQfMax = qf;
}
}
foreach (var Qf in RQfs)
{
double d;
if (RQfMax == 0)//never queried
{
d = 1;
}
else
{
d = Qf.Value / RQfMax;
}
if (double.IsNaN(d))
{
throw new Exception("value is NaN in column: " + columname);
}
Qfs.Add(Qf.Key, d);
}
MetaDbFiller.AddQfMetaTable(columname, Qfs);
relevantQueries = null;//memory cleanup
GC.Collect();
}
/// <summary>
/// recieves the CEQ query and Processes it and its results
/// </summary>
/// <param name="ceq"></param>
public static void LoadAndProccessCeq(String ceq)
{
Dictionary <string, string> parsedCeq = QueryParser.parseInput(ceq);
DataTable rows = CheckIfSingleOption(parsedCeq);
DataTable table = TableProccessor.RetrieveTable();
Dictionary <DataRow, double> ratings = new Dictionary <DataRow, double>( );
Dictionary <string, Tuple <object, double, double> > elements = new Dictionary <string, Tuple <object, double, double> >();
//retrieve k
int k = 10;
if (parsedCeq.ContainsKey("k"))
{
k = Convert.ToInt32(parsedCeq["k"]);
parsedCeq.Remove("k");
}
if (rows != null)
{
parsedCeq = new Dictionary <string, string>();
foreach (DataColumn col in rows.Columns)
{
if (col.ColumnName != "id")
{
parsedCeq.Add(col.ColumnName, rows.Rows[0][col].ToString());
}
}
}
//prepare each element in the parsed CEQ for rating calculation
foreach (var elem in parsedCeq)
{
double idf = getIDF(elem.Value, elem.Key);
double qf = getQF(elem.Value, elem.Key);
Tuple <object, double, double> element = new Tuple <object, double, double>(elem.Value, idf, qf);
//needs parsing of int64 and double i believe
elements.Add(elem.Key, element);
}
foreach (DataRow row in table.Rows)
{
ratings.Add(row, GetSimilarity(elements, row)); //add rating
}
var results = ratings.ToList(); //convert to sorted list
results.Sort((pair1, pair2) => pair2.Value.CompareTo(pair1.Value)); //TODO More efficient top k, idf and qf values can be used as well as distance for numerical and plausibly even jaccard
List <DataRow> toReturn = new List <DataRow>();
for (int i = 0; i < k; i++)
{
toReturn.Add(results[i].Key);//add top 10 rows
}
//create form
Results form = new Results();
form.Show();
DataTable resultTable = table.Clone();
foreach (DataRow row in toReturn)
{
resultTable.ImportRow(row);
Thread.Sleep(10);// for animation
}
form.BindData(resultTable);
}
/// <summary>
/// used for processing the create statements of the database
/// </summary>
/// <param name="reader"></param>
public static void ProcessTables(StreamReader reader)
{
string rawSql = reader.ReadToEnd();
TableProccessor.CreateAndFillTable(rawSql);
}
/// <summary>
/// Loads tables and calculates IDF values and other essentials
/// </summary>
public static void LoadTableAndPreprocess()
{
TableProccessor.RetrieveTable();
TableProccessor.CalculateColumnProperties();
TableProccessor.Process();
}
//Retrieves the whole metaDB
public static void LoadMetaDB()
{
// 1, because if a database is loaded, it contains more than one value, 1 acts as failsafe
if (idfs.Count > 1)
{
return;
}
TableProccessor.CalculateColumnProperties();
qf = new Dictionary <string, object>();
idfs = new Dictionary <string, object>();
//Do for each column in columnProperty
foreach (var columnProperty in TableProccessor.ColumnProperties)
{
if (columnProperty.Key != "id")
{
string name = columnProperty.Key;
DataTable tab = dbConnection.QueryForDataTable("SELECT * FROM " + name);
//check if column contains numerical value
if (!columnProperty.Value.numerical.HasValue || columnProperty.Value.numerical.Value)
{
Dictionary <double, double> idf = new Dictionary <double, double>();
Dictionary <double, double> qfs = new Dictionary <double, double>();
//for each row in DataTable tab, save the idf and qf values in Dicitonary with associated key
foreach (DataRow row in tab.Rows)
{
idf.Add((double)row[0], (double)row[1]);
qfs.Add((double)row[0], (double)row[2]);
}
//add idf and qfs dictionaries in Dictionary with associated columnname as key
idfs.Add(name, idf);
qf.Add(name, qfs);
}
else
{
Dictionary <string, double> idf = new Dictionary <string, double>();
Dictionary <string, double> qfs = new Dictionary <string, double>();
Dictionary <string, object> jacquard2 = new Dictionary <string, object>();
foreach (DataRow row in tab.Rows)
{
idf.Add((string)row[0], (double)row[1]);
qfs.Add((string)row[0], (double)row[2]);
if (tab.Columns.Count > 3)
{
Dictionary <string, double> jaq = new Dictionary <string, double>();
for (int i = 3; i < tab.Columns.Count; i++)
{
jaq.Add((string)row.Table.Columns[i].ColumnName, (double)row[i]);
}
jacquard2.Add((string)row[0], jaq);
}
}
idfs.Add(name, idf);
qf.Add(name, qfs);
if (tab.Columns.Count > 3)
{
jacquard.Add(name, jacquard2);
}
}
}
}
}
