private void CreateOrderedSetOfWorldNoTable(List <string> tables, List <List <Tuple <int, double> > > worldNumbers)
{
underlineDatabase.DropTableIfExist(tempTable);
var noOfTable = tables.Count;
if (tables.Count != worldNumbers.Count)
{
throw new Exception("number of tables is inconsistent");
}
string selectClause = string.Format(" {0}_PossibleWorldsAggregated.worldNo as {0}", tables[0]);
string fromClause = string.Format(" {0}_PossibleWorldsAggregated", tables[0]);
string jointProbabilityClause = string.Format("{0}_PossibleWorldsAggregated.p/100", tables[0]);;
// SQL statement select worldNo from all relevent table and do a cross join
for (int i = 1; i < noOfTable; i++)
{
var tName = tables[i];
selectClause += string.Format(",{0}_PossibleWorldsAggregated.worldNo as {0}", tName);
fromClause += string.Format(" cross join {0}_PossibleWorldsAggregated", tName);
jointProbabilityClause += string.Format("*{0}_PossibleWorldsAggregated.p/100", tName);
}
selectClause += "," + jointProbabilityClause + "*100 as p ";
var sql = string.Format("Select {0} INTO {2} From {1}", selectClause, fromClause, tempTable);
underlineDatabase.ExecuteSql(sql);
var result = underlineDatabase.ExecuteSqlWithResult("Select * FROM " + tempTable);
_dataRows = result.Rows;
}
/// <summary>
/// construct and execute sql to populate possible states table using those attribute table.
/// </summary>
/// <param name="tableName"></param>
private List <string> PreparePossibleStatesTable(string tableName)
{
if (!underlineDatabase.CheckIsTableAlreadyExist(tableName + "_0"))
{
throw new Exception(tableName + "_0 table doesn't exist");
}
List <List <string> > colNamesList = new List <List <string> >();
var getVariables = string.Format("SELECT var,att0,p FROM {0}_0", tableName);
var getVariblesResult = underlineDatabase.ExecuteSqlWithResult(getVariables);
foreach (DataRow row in getVariblesResult.Rows)
{
var variable = row.Field <int>("var");
var tableNameString = row.Field <string>("att0");
var tableNames = new List <string>(tableNameString.Split(','));
var p = row.Field <double>("p");
List <string> colNames = GeneratePossibleStates(tableName, variable, tableNames, p);
colNamesList.Add(colNames);
}
if ((colNamesList.Count) > 1)
{
return(colNamesList[0]);
}
else
{
throw new Exception("inconsistency in attribute sizes of different tuples");
}
}
/// <summary>
/// Find out all states that a new random variable i can has
/// for each i's state, duplicate the existing possibleWorld table
/// and insert this state as a row in every world
/// </summary>
/// <param name="query"></param>
/// <param name="randomVariable"></param>
private void PopulatePossibleWorlds(SqlInsertQuery query, int randomVariable)
{
var tableName = query.TableName;
var statesTable = underlineDatabase.ExecuteSqlWithResult("select * from " + tableName + "_PossibleStates");
IEnumerable <DataRow> newlyAddedPossibleStates = from newRow in statesTable.AsEnumerable()
where newRow.Field <int>("var") == randomVariable
select newRow;
var newVarStates = newlyAddedPossibleStates.Count();
var existingWorldsTable = underlineDatabase.ExecuteSqlWithResult("select * from " + tableName + "_PossibleWorlds");
var result = existingWorldsTable.Copy();
// replicate existingWorldTable, number of copy depends on number of state in new variable.
var worldNumbers = (from dataRow in existingWorldsTable.AsEnumerable()
select dataRow.Field <int>("worldNo")).Distinct().ToList();
// if old worlds is empty, for each new state create a world
if (!worldNumbers.Any())
{
int index = 0;
foreach (var eachPossibleState in newlyAddedPossibleStates)
{
index++;
insertVariableState(index, eachPossibleState, ref result);
}
}
else
{
for (int i = 1; i <= newVarStates; i++)
{
var eachNewState = newlyAddedPossibleStates.ElementAt(i - 1);
// for each new variable state, duplicate existing worlds
var duplicatedWorldNumbers = generateNewRandomVariables(worldNumbers, i, existingWorldsTable, ref result);
// current new state i is assign to each world
foreach (var worldNumber in duplicatedWorldNumbers)
{
insertVariableState(worldNumber, eachNewState, ref result);
}
}
}
underlineDatabase.WriteTableBacktoDatabase(tableName + "_PossibleWorlds", result);
}
public DataTable HandleSelectSqlQuery()
{
DataTable result = new DataTable();
var evaluationStrategy = _query.Strategy;
switch (evaluationStrategy)
{
case EvaluationStrategy.Lazy:
case EvaluationStrategy.Default:
List <string> tables = _query.GetRevelentTables();
PrepareReleventTables(tables);
underlineDatabase.DropTableIfExist(answerTableName);
OrderedSetIterator iterator = new OrderedSetIterator(tables, underlineDatabase);
while (iterator.HasNext())
{
var worldNoTuple = iterator.NextSetOfWorldNo();
var worldNo = worldNoTuple.Item1;
var probability = worldNoTuple.Item2;
for (int i = 0; i < worldNo.Count; i++)
{
ConvertWorldToTable(tables[i], worldNo[i]);
}
//TODO: need to get rid of strategy in ProSQL for execution on DB, or utilise default strategy
var a = underlineDatabase.ExecuteSqlWithResult(_query.Sql);
WriteResultToAnswerTable(iterator.GetIndex(), a, probability);
}
result = NormalisingTableByAttributes(answerTableName);
return(result);
case EvaluationStrategy.Naive:
result = HandleSelectSqlQuery(false);
return(result);
case EvaluationStrategy.Extensional:
var treeWalker = new ExtensionalTreeWalker(_query.QueryTree, underlineDatabase);
var sql = treeWalker.GetSql();
result = underlineDatabase.ExecuteSqlWithResult(sql);
return(result);
}
return(result);
// return HandleSelectSqlQuery(false);
}
