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