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";
}
/// <summary>
/// at the moment only table name is allowed
/// </summary>
/// <param name="tableClause"></param>
/// <param name="tableName"></param>
/// <param name="subQuery"></param>
private void processTableClause(string tableClause, out bool _hasSubquery, out string _tableName, out SqlSelectQuery _subQuery, out string _JoinOnAttributes)
{
// pattern here is: tableName or 2 table join with a sql select query all over again
string sPattern = @"\s*(?<table1>\w+)\s*(\z|as\s+t1\s+join\s+\((?<table2>.*)\)\s+as\s+t2\s+on\s+(?<joinCondition>.+))";
Match match = Regex.Match(tableClause, sPattern, RegexOptions.IgnoreCase);
_tableName = "";
if (match.Success)
{
_tableName = match.Groups["table1"].Value;
var table2 = match.Groups["table2"].Value;
if (table2 != null && table2.Length > 0)
{
_hasSubquery = true;
_JoinOnAttributes = match.Groups["joinCondition"].Value;
SqlSelectQuery subQuery = new SqlSelectQuery(table2);
_subQuery = subQuery;
}
else
{
_hasSubquery = false;
_subQuery = null;
_JoinOnAttributes = "";
}
}
else
{
throw new Exception("parsing problem encountered within processTableClause function");
}
}
private void ProcessAndPopulateEachField()
{
// pattern to match here is: select fields from tableORsubQuery where whereCondition EVALUATE USING xxx strategy
string sPattern = @"\A\s*SELECT\s+(?<attributes>.+?)\s+FROM\s+(\[(?<tableClause>.+)\]|(?<tableName>\w+))\s*(?<conditionClause>.*)";
Match match = Regex.Match(this._sql, sPattern, RegexOptions.IgnoreCase);
if (match.Success)
{
_attributes = match.Groups["attributes"].Value;
String tableClause = match.Groups["tableClause"].Value;
String tableName = match.Groups["tableName"].Value;
if (tableName.Length > 0)
{
_tableName = tableName;
_subQuery = null;
_JoinOnAttributes = null;
_hasSubquery = false;
}
else
{
processTableClause(tableClause, out _hasSubquery, out _tableName, out _subQuery, out _JoinOnAttributes);
}
var optionalClause = match.Groups["conditionClause"].Value;
//List<String> attributesName = processAttributesClause(_attributes);
if (optionalClause.Length > 0)
{
processOptionalClause(optionalClause, out _conditionClause, out _strategyClause);
}
}
else
{
throw new Exception("query's format does not comply with SELECT QUERY");
}
}
/// <summary>
/// at the moment only table name is allowed
/// </summary>
/// <param name="tableClause"></param>
/// <param name="tableName"></param>
/// <param name="subQuery"></param>
private void processTableClause(string tableClause, out bool _hasSubquery, out string _tableName, out SqlSelectQuery _subQuery, out string _JoinOnAttributes)
{
// pattern here is: tableName or 2 table join with a sql select query all over again
string sPattern = @"\s*(?<table1>\w+)\s*(\z|as\s+t1\s+join\s+\((?<table2>.*)\)\s+as\s+t2\s+on\s+(?<joinCondition>.+))";
Match match = Regex.Match(tableClause, sPattern, RegexOptions.IgnoreCase);
_tableName = "";
if (match.Success)
{
_tableName = match.Groups["table1"].Value;
var table2 = match.Groups["table2"].Value;
if (table2 != null && table2.Length > 0)
{
_hasSubquery = true;
_JoinOnAttributes = match.Groups["joinCondition"].Value;
SqlSelectQuery subQuery = new SqlSelectQuery(table2);
_subQuery = subQuery;
}
else
{
_hasSubquery = false;
_subQuery = null;
_JoinOnAttributes = "";
}
}
else
{
throw new Exception("parsing problem encountered within processTableClause function");
}
}
private void ProcessAndPopulateEachField()
{
// pattern to match here is: select fields from tableORsubQuery where whereCondition EVALUATE USING xxx strategy
string sPattern = @"\A\s*SELECT\s+(?<attributes>.+?)\s+FROM\s+(\[(?<tableClause>.+)\]|(?<tableName>\w+))\s*(?<conditionClause>.*)";
Match match = Regex.Match(this._sql, sPattern, RegexOptions.IgnoreCase);
if (match.Success)
{
_attributes = match.Groups["attributes"].Value;
String tableClause = match.Groups["tableClause"].Value;
String tableName = match.Groups["tableName"].Value;
if (tableName.Length > 0)
{
_tableName = tableName;
_subQuery = null;
_JoinOnAttributes = null;
_hasSubquery = false;
}
else
{
processTableClause(tableClause, out _hasSubquery, out _tableName, out _subQuery, out _JoinOnAttributes);
}
var optionalClause = match.Groups["conditionClause"].Value;
//List<String> attributesName = processAttributesClause(_attributes);
if (optionalClause.Length > 0)
processOptionalClause(optionalClause, out _conditionClause, out _strategyClause);
}
else
{
throw new Exception("query's format does not comply with SELECT QUERY");
}
}
private DataTable ExecuteMonteCarloSampling(string samplingResultTable, string samplingTargetTable, int samplingRuns, SqlSelectQuery query)
{
//:Todo not used
Random random = new Random();
var sql = String.Format("select count(*) from {0}", samplingTargetTable);
var result = underlineDatabase.ExecuteSqlWithResult(sql);
if (result.Rows.Count != 1)
throw new Exception("");
var noOfWorlds = (int)result.Rows[0][0];
DataTable allSample = new DataTable();
for (int i = 1; i <= samplingRuns; i++)
{
var worldNoSelected = random.Next(1, noOfWorlds);
var selectStringWorld = string.Format("SELECT * FROM {0} WHERE worldNo = {1}", samplingTargetTable, worldNoSelected);
var aSample = underlineDatabase.ExecuteSqlWithResult(selectStringWorld);
if (i == 1)
allSample = aSample.Clone();
allSample = addOneTableToAnother(allSample, aSample, random);
}
DataTable frenquencyResult = computeFrequencyResult(allSample, samplingResultTable, query.Attributes, samplingRuns, query);
return frenquencyResult;
}
public SelectQueryHandler(SqlSelectQuery squery, IStandardDatabase underlineDatabase)
{
_query = squery;
this.underlineDatabase = underlineDatabase;
}
/// <summary>
/// Default is Extensional method, while monte carlo is sampling using frequency of event occur
/// </summary>
/// <param name="attributes"> must specify what they are, wildcard like * not supported !</param>
/// <returns></returns>
private DataTable ComputeJointResultUsingNaiveStrategy(string attributes, SqlSelectQuery query, bool intermediate)
{
int attributeSize = PreparePossibleStatesTable(query.TableName);
CreatePossibleWorldsTable(query.TableName, attributeSize);
PreparePossibleWorldsTable(query.TableName);
PreparePossibleWorldsAggregatedTable(query.TableName);
return NaiveStrategy(attributes,query.ConditionClause,query.TableName,intermediate);
}
/// <summary>
/// save allSample table back to database in order to be able to use group by keyword.
/// the temporary table called samplingResultTable is created for this purposes
/// </summary>
/// <param name="allSample"></param>
/// <param name="samplingResultTable"></param>
/// <param name="query"></param>
/// <returns></returns>
private DataTable computeFrequencyResult(DataTable allSample, string samplingResultTable, string selectedFields, int noOfSamplingRuns, SqlSelectQuery query)
{
WriteTableIntoDatabase(samplingResultTable, allSample);
var groupingSql = string.Format("SELECT {0},(COUNT(*)/{2}) as p FROM {1} GROUP BY {0}", selectedFields, samplingResultTable, noOfSamplingRuns);
return underlineDatabase.ExecuteSqlWithResult(groupingSql);
}
public void TestParsingSelectQueryWithWhereClauseAndStrategy()
{
string sentences = "Select a,b,c from Data where (a=25 and b=15) Evaluate using (monte Carlo)";
SqlSelectQuery query = new SqlSelectQuery(sentences);
Assert.IsTrue(query.Attributes.Trim() == "a,b,c");
Assert.IsTrue(query.TableName.Trim() == "Data");
Assert.IsTrue(query.ConditionClause.Trim() == "a=25 and b=15");
Assert.IsTrue(query.Strategy.Equals(EvaluationStrategy.MonteCarlo));
}
public void TestParsingSelectQueryWithThreeJoin()
{
var secondJoin = string.Format("Select e,f,g from [Data2 as t1 Join (Select * FROM Data3) as t2 on t1.he=t2.hy]");
string sentences = string.Format("Select a,b,c from [Data as t1 Join ({0}) as t2 on t1.ha=t2.ho]",secondJoin);
SqlSelectQuery query = new SqlSelectQuery(sentences);
Assert.IsTrue(query.Attributes.Trim() == "a,b,c");
Assert.IsTrue(query.TableName.Trim() == "Data");
Assert.IsTrue(query.ConditionClause.Trim() == "");
Assert.IsTrue(query.Strategy.Equals(EvaluationStrategy.Default));
Assert.IsTrue(query.JoinOnAttributes.Trim() == "t1.ha=t2.ho");
Assert.IsTrue(query.HasSubquery == true);
var subquery = query.SubQuery;
Assert.IsTrue(subquery.HasSubquery == true);
Assert.IsTrue(subquery.TableName.Trim() == "Data2");
Assert.IsTrue(subquery.Attributes == "e,f,g");
Assert.IsTrue(subquery.JoinOnAttributes.Trim() == "t1.he=t2.hy");
var subsubquery = subquery.SubQuery;
Assert.IsTrue(subsubquery.HasSubquery == false);
Assert.IsTrue(subsubquery.TableName.Trim() == "Data3");
Assert.IsTrue(subsubquery.Attributes == "*");
}
public void TestParsingSelectQueryWithJoin()
{
string sentences = "Select a,b,c from [Data as t1 Join (SELECT * FROM Data2) as t2 on t1.ha=t2.ho]";
SqlSelectQuery query = new SqlSelectQuery(sentences);
Assert.IsTrue(query.Attributes.Trim() == "a,b,c");
Assert.IsTrue(query.TableName.Trim() == "Data");
Assert.IsTrue(query.ConditionClause.Trim() == "");
Assert.IsTrue(query.Strategy.Equals(EvaluationStrategy.Default));
var subquery = query.SubQuery;
Assert.IsTrue(query.JoinOnAttributes.Trim() == "t1.ha=t2.ho");
Assert.IsTrue(query.HasSubquery);
Assert.IsTrue(subquery.HasSubquery == false);
Assert.IsTrue(subquery.TableName.Trim() == "Data2");
}
