/// <summary> /// return number of attributes implied by tableNames /// </summary> /// <param name="tableName"></param> /// <param name="variable"></param> /// <param name="tableNames"></param> /// <param name="p"></param> /// <returns></returns> private List <string> GeneratePossibleStates(string tableName, int variable, List <string> tableNames, double p) { List <string> colNameList = new List <string>(); string selectClause = "t0.var,"; string probabilityClause = "100*(t0.p/100)"; string joinClause = tableName + "_0 t0"; List <string> emptyString = new List <string>(); var colIndex = 1; for (int i = 0; i < tableNames.Count; i++) { var tName = tableNames[i]; var aProbability = string.Format("*({0}.p/100)", tName); probabilityClause += aProbability; var aJoin = string.Format(" JOIN {0} ON t0.var = {0}.var", tName); joinClause += aJoin; int numberOfDigits = NumberOfDigits(tName); for (int j = 0; j < numberOfDigits; j++) { selectClause += "att" + (colIndex) + ","; colNameList.Add("att" + (colIndex)); emptyString.Add("null"); colIndex++; } } var statesTable = tableName + "_PossibleStates"; var tableAlreadyExist = underlineDatabase.CheckIsTableAlreadyExist(statesTable); if (tableAlreadyExist) { var sql = string.Format("INSERT INTO {2} SELECT {0}{1} as p FROM {3} WHERE t0.var={4}", selectClause, probabilityClause, statesTable, joinClause, variable); underlineDatabase.ExecuteSql(sql); } else { var sql = string.Format("SELECT {0}{1} as p INTO {2} FROM {3} WHERE t0.var={4}", selectClause, probabilityClause, statesTable, joinClause, variable); underlineDatabase.ExecuteSql(sql); } if ((int)Math.Round(p) < 100) { var sql = string.Format("INSERT INTO {0} VALUES ({2},{1},{3})", statesTable, string.Join(",", emptyString), variable, p); underlineDatabase.ExecuteSql(sql); } return(colNameList); }
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; }
private void ConvertWorldToTable(string tableName, int worldNo) { underlineDatabase.DropTableIfExist(tableName); // could store history enabling skip those case that table already in worldNo var sql = string.Format("Select * Into {0} from {0}_PossibleWorlds Where worldNo={1}", tableName, worldNo); underlineDatabase.ExecuteSql(sql); }
/// <summary> /// construct and execute sql to populate possible states table using those attribute table. /// </summary> /// <param name="query"></param> /// <param name="randomVariable"></param> private void PopulatePossibleStatesTable(SqlInsertQuery query, int randomVariable) { var attributes = query.Attributes; var numberOfAttributes = attributes.Count; var tableName = query.TableName; /* sql format is: * insert into socialData_PossibleStates (var,v ,att1 ,att2 ,att3,p) * select t1.var,row_number() over (order by t1.v ,t2.v ,t3.v ) as v, t1.att1 ,t2.att2 ,t3.att3 , (t1.p/100)* (t2.p/100)* (t3.p/100)* 100 as p * from socialData_0 as t0 cross join socialData_1 as t1 cross join socialData_2 as t2 cross join socialData_3 as t3 where t0.var=1 and t0.var = t1.var and t0.var = t2.var and t0.var = t3.var * */ string attributeClause = "var,v"; string fromClause = ""; string whereClause = ""; string combiningVField = ""; string combiningAttField = ""; string combiningPField = ""; for (int i = 0; i <= numberOfAttributes; i++) { combiningPField += string.Format(" (t{0}.p/100)* ", i); if (i == 0) { whereClause = string.Format(" t0.var={0} and ", randomVariable); // table_0 store tupleExistence data only, not attribute fromClause += string.Format(" {0}_0 as t0 ", tableName); } else { attributeClause += " ,att" + i; fromClause += string.Format(" cross join {0}_{1} as t{1} ", tableName, i); if (i == 1) { whereClause += string.Format(" t0.var = t{0}.var ", i); combiningVField += " t1.v "; combiningAttField += " t1.att1 "; } else { whereClause += string.Format(" and t0.var = t{0}.var ", i); combiningVField += " ,t" + i + ".v "; combiningAttField += string.Format(" ,t{0}.att{0} ", i); } } } attributeClause += ",p"; string selectClause = string.Format("t0.var,row_number() over (order by {0}) as v,{1},{2}100 as p", combiningVField, combiningAttField, combiningPField); string sql = string.Format("insert into {0}_PossibleStates ({1}) select {2} from {3} where {4}", tableName, attributeClause, selectClause, fromClause, whereClause); var result = underlineDatabase.ExecuteSql(sql); // Adding null state here if tuple may not exist, otherwise naive strategy would calculate incorrect result if (query.TupleP < 100.0) { var nullAttributes = ""; for (int i = 1; i <= attributes.Count; i++) { nullAttributes += "null,"; } var insertNoneExistenceState = string.Format("insert into {0}_PossibleStates values({1},0,{2}{3})", tableName, randomVariable, nullAttributes, 100 - query.TupleP); underlineDatabase.ExecuteSql(insertNoneExistenceState); } }