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;
}
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);
}
public string GetSql(QueryTree queryTree, out List <string> atts)
{
List <QueryTree> subquery;
string sql;
List <string> AttsSelectedByQuery;
switch (queryTree.treeNodeType)
{
case QueryTree.TreeNodeType.Select:
// If node is select condition, then Select * From H(query.Subquey[0]) Where query.condition
subquery = queryTree.subquery;
if (subquery != null && subquery.Count == 1)
{
var R1 = subquery[0];
List <string> attributes;
var fromClause = GetSql(R1, out attributes);
var condition = queryTree.condition;
sql = string.Format("select * from ({0}) as R where {1}", fromClause, condition);
atts = attributes;
return(sql);
}
throw new Exception("number of subquery mismatch");
case QueryTree.TreeNodeType.Project:
// If node is project A, then Select R.A,IndepProject(R.P) as p from R group by R.A
subquery = queryTree.subquery;
if (subquery != null && subquery.Count == 1)
{
var R1 = subquery[0];
if (R1.treeNodeType == QueryTree.TreeNodeType.GroundTable)
{
queryTree.treeNodeType = QueryTree.TreeNodeType.GroundTable;
queryTree.tableName = R1.tableName;
queryTree.subquery = new List <QueryTree>();
return(GetSql(queryTree, out atts));
}
List <string> attributes;
var fromClause = GetSql(R1, out attributes);
AttsSelectedByQuery = _queryTree.attributes;
sql = string.Format("Select {0},dbo.IndependentProject(R.P) as p from ({1}) as R group by {0}", string.Join(",", AttsSelectedByQuery), fromClause);
atts = AttsSelectedByQuery;
return(sql);
}
throw new Exception("number of subquery mismatch");
case QueryTree.TreeNodeType.Join:
// If node is project A, then Select R.A,IndepProject(R.P) as p from R group by R.A
subquery = queryTree.subquery;
if (subquery != null && subquery.Count == 2)
{
List <string> a1;
List <string> a2;
var R1 = GetSql(subquery[0], out a1);
var R2 = GetSql(subquery[1], out a2);
var joinCondition = queryTree.condition;
var r1 = a1.Select(prefixR1);
var r2 = a2.Select(prefixR2);
var R1Atts = String.Join(",", r1);
var R2Atts = String.Join(",", r2);
sql = string.Format("Select {0},{1},(R1.p*R2.p/100) as p " +
"from ({2}) as R1 " +
"join ({3}) as R2 on {4}", R1Atts, R2Atts, R1, R2, joinCondition);
atts = (r1.Concat(r2)).ToList();
return(sql);
}
throw new Exception("number of subquery mismatch");
case QueryTree.TreeNodeType.Union:
subquery = queryTree.subquery;
if (subquery != null && subquery.Count == 2)
{
List <string> a1;
List <string> a2;
var R1 = GetSql(subquery[0], out a1);
var R2 = GetSql(subquery[1], out a2);
var R1Atts = String.Join(",", a1);
var R2Atts = String.Join(",", a2);
sql = string.Format("select coalesce(R1.{0}, R2.{3}), " +
"(case when R1.p is null then R2.p " +
"when R2.p is null then R1.p " +
"else 100-(100-R1.p)*(100-R2.p)/100 " +
"end) as p from " +
"({1}) as R1 full outer join " +
"({2}) as R2 on R1.{0} = R2.{0}", R1Atts, R1, R2, R2Atts);
atts = a2;
return(sql);
}
throw new Exception("number of subquery mismatch");
case QueryTree.TreeNodeType.Difference:
subquery = queryTree.subquery;
if (subquery != null && subquery.Count == 2)
{
List <string> a1;
List <string> a2;
var R1 = GetSql(subquery[0], out a1);
var R2 = GetSql(subquery[1], out a2);
var r1 = a1.Select(prefixR1);
var r2 = a2.Select(prefixR2);
var R1Atts = String.Join(",", r1);
var R2Atts = String.Join(",", r2);
sql = string.Format("Select {2},(R1.p*R2.p/100) as p " +
"from ({0}) as R1 " +
"join (Select {4}, 100-R.P as P from ({1}) as R) as R2 on {2}={3}", R1, R2, R1Atts, R2Atts, String.Join(",", a2));
atts = a1;
return(sql);
}
throw new Exception("number of subquery mismatch");
case QueryTree.TreeNodeType.GroundTable:
var tName = queryTree.tableName;
var stateTable = tName + "_PossibleStates";
underlineDatabase.DropTableIfExist(stateTable);
var allAttsFromTable = PreparePossibleStatesTable(tName);
AttsSelectedByQuery = queryTree.attributes;
if (AttsSelectedByQuery == null || (AttsSelectedByQuery.Count == 1 && AttsSelectedByQuery[0] == "*"))
{
AttsSelectedByQuery = allAttsFromTable;
}
var attBeforeRenaming = AttsSelectedByQuery.Select(beforeRenaming).ToList();
var attAfterRenaming = AttsSelectedByQuery.Select(afterRenaming).ToList();
sql = string.Format("select var,{1},Sum(p) as p from {0} Group By var,{2}", stateTable, string.Join(",", AttsSelectedByQuery), string.Join(",", attBeforeRenaming));
var finalSql = string.Format("select {1},dbo.IndependentProject(p) as p from ({0}) as R Group By {1}", sql, string.Join(",", attAfterRenaming));
atts = attAfterRenaming;
return(finalSql);
default:
throw new Exception("tree node type is invalid");
}
return("");
}