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