public void TestInsertWithFullyDependentColumns()
{
string sentences = "INSERT INTO socialData(att1,att3,att2,att4) VALUES (351,Smith,PROBABLY [785 Single] 25%/ [185 Married] 75%)";
SqlInsertQuery query = new SqlInsertQuery(sentences);
query.ProcessAndPopulateEachField();
Assert.IsTrue((int)query.TupleP == 100);
Assert.IsTrue(query.TableName == "socialData");
Assert.IsTrue(query.Attributes.Count == 3);
var multiColumn = (ProbabilisticMultiAttribute)query.Attributes[2];
Assert.IsTrue(multiColumn.MultiAttrbutes.Count == 2);
Assert.IsTrue((int)multiColumn.PValues[0] == 25);
Assert.IsTrue((int)multiColumn.PValues[1] == 75);
var value1 = multiColumn.MultiAttrbutes[0];
Assert.IsTrue(value1.Count == 2);
Assert.IsTrue(value1[0] == "785");
Assert.IsTrue(value1[1] == "Single");
var value2 = multiColumn.MultiAttrbutes[1];
Assert.IsTrue(value2.Count == 2);
Assert.IsTrue(value2[0] == "185");
Assert.IsTrue(value2[1] == "Married");
}
public string submitQuerySQL(string sql, out DataTable answerSet)
{
SqlQuery rawQuery = new SqlQuery(sql);
QueryType qType = rawQuery.ProcessType();
answerSet = null;
switch (qType)
{
case QueryType.INSERT:
var query = new SqlInsertQuery(sql);
query.ProcessAndPopulateEachField();
var iHandler = new InsertQueryHandler(query, underlineDatabase);
answerSet = iHandler.HandleInsertQuery();
break;
case QueryType.SELECT:
var squery = new SqlSelectQuery(sql);
var sHandler = new SelectQueryHandler(squery, underlineDatabase);
answerSet = sHandler.HandleSelectSqlQuery();
break;
case QueryType.CREATE:
var cquery = new SqlCreateTableQuery(sql);
var handler = new CreateTableHandler(cquery,underlineDatabase);
answerSet = handler.HandleCreateTableQuery();
break;
default:
break;
}
return "end of submitSQL function";
}
private void createAttributeTables(SqlInsertQuery query, List<Object> attributes)
{
String[] attributeNames1 = { "var", "v", "att0","p" };
String[] attributeTypes1 = { "INT", "INT","NVARCHAR(MAX)","float" };
underlineDatabase.CreateNewTable(query.TableName + "_0", attributeNames1, attributeTypes1);
for (int i = 1; i <= attributes.Count; i++)
{
string attributeTableName = query.TableName + "_" + i;
string ai = "att" + i;
String[] attributeNames = { "var", "v", ai , "p" };
String[] attributeTypes = { "INT", "INT", "NVARCHAR(MAX)", "float" };
underlineDatabase.CreateNewTable(attributeTableName, attributeNames, attributeTypes);
}
}
public void TestInsertWithColumnNameSpecified()
{
string sentences = "INSERT INTO socialData(att1,att3,att2,att4) VALUES (351,Smith,hoho,haha)";
SqlInsertQuery query = new SqlInsertQuery(sentences);
query.ProcessAndPopulateEachField();
var colNames = query.ColNames;
Assert.IsNotNull(colNames);
Assert.IsTrue(colNames.Count == 4);
Assert.IsTrue(colNames[0] == "att1");
Assert.IsTrue(colNames[1] == "att3");
Assert.IsTrue(colNames[2] == "att2");
Assert.IsTrue(colNames[3] == "att4");
}
public void TestComplexProbabilisticSqlInsert()
{
string sentences = "INSERT INTO socialData VALUES (351,PROBABLY 785 25%/ 185 70% ,Smith,Single) probably 50%";
SqlInsertQuery query = new SqlInsertQuery(sentences);
query.ProcessAndPopulateEachField();
Assert.IsTrue(query.TupleP == 50.0);
Assert.IsTrue(query.TableName == "socialData");
Assert.IsTrue(query.Attributes.Count == 4);
ProbabilisticSingleAttribute p = (ProbabilisticSingleAttribute)query.Attributes[1];
Assert.IsTrue(p.Values[0] == "785");
Assert.IsTrue(p.Probs[0] == 25.0);
Assert.IsTrue(p.Values[1] == "185");
Assert.IsTrue(p.Probs[1] == 70.0);
}
public InsertQueryHandler(SqlInsertQuery query, IStandardDatabase underlineDatabase)
{
this.query = query;
this.underlineDatabase = underlineDatabase;
}
private void createPossibleStatesTable(SqlInsertQuery query, List<object> attributes)
{
String[] attributeNames = { "var", "v" };
String[] attributeTypes = { "INT", "INT" };
string attributeTableName = query.TableName + "_PossibleStates";
List<String> attributeNamesList = attributeNames.ToList();
List<String> attributeTypesList = attributeTypes.ToList();
for (int i = 1; i <= attributes.Count; i++)
{
string ai = "att" + i;
attributeNamesList.Add(ai);
attributeTypesList.Add("NVARCHAR(MAX)");
}
// p is the last attribute
attributeNamesList.Add("p");
attributeTypesList.Add("float");
underlineDatabase.CreateNewTable(attributeTableName, attributeNamesList.ToArray(), attributeTypesList.ToArray());
}
/// <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);
}
/// <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);
}
}
private void InsertAttributeValue(SqlInsertQuery query, List<Object> attributes, int randomVariable)
{
underlineDatabase.InsertValueIntoAttributeTable(query.TableName + "_0", randomVariable, 1,"TupleExistence",query.TupleP);
for (int i = 1; i <= attributes.Count; i++)
{
string attributeTableName = query.TableName + "_" + i;
var value = attributes[i-1] as DeterministicAttribute;
if (value != null)
{
DeterministicAttribute attribute = value;
double prob = 0;
prob = 100;
underlineDatabase.InsertValueIntoAttributeTable(attributeTableName, randomVariable, 1, attribute.AttributeValue1, prob);
}
else
{
var probabilisticAttribute = attributes[i-1] as ProbabilisticSingleAttribute;
if (probabilisticAttribute != null)
{
var attribute = probabilisticAttribute;
List<String> v = attribute.Values;
List<double> p = attribute.Probs;
for (int j = 0; j < v.Count; j++)
{
// attribute value starting from 1 upto number of possible values,
// because 0 is system reserve for null state.
underlineDatabase.InsertValueIntoAttributeTable(attributeTableName, randomVariable, j + 1, v[j], p[j]);
}
}
}
}
}
/// <summary>
/// its operation is described by google doc chapter 1 storage,
/// if table already exist then ignore the create table operation
/// just do the insert value
/// </summary>
/// <param name="query"></param>
private void HandleInsertSqlQuery(SqlInsertQuery query)
{
// only check prime field table here, is it safe enough ?
bool isTableExist = underlineDatabase.CheckIsTableAlreadyExist(query.TableName+"_0");
List<Object> attributes = query.Attributes;
int randomVariable = 1;
if (!isTableExist)
{
// table creation operation here
createAttributeTables(query, attributes);
createPossibleStatesTable(query, attributes);
createPossibleWorldsTable(query, attributes);
}
else
{
randomVariable = underlineDatabase.GetNextFreeVariableId(query.TableName);
if (randomVariable <= 0)
{
randomVariable = 1;
}
}
// insert value into tables starting here
InsertAttributeValue(query, attributes, randomVariable);
PopulatePossibleStatesTable(query, randomVariable);
PopulatePossibleWorlds(query, randomVariable);
}
public void TestNormalSqlInsert()
{
string sentences = "INSERT INTO socialData VALUES (351,785,Smith,Single)";
SqlInsertQuery query = new SqlInsertQuery(sentences);
query.ProcessAndPopulateEachField();
Assert.IsTrue(query.TupleP == 100.0);
Assert.IsTrue(query.TableName == "socialData");
Assert.IsTrue(query.Attributes.Count == 4);
}
