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(""); }