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