private QueryTree ProcessFromClause(string fromClause, QueryTree resultingSQL)
{
// form clause 3 alternative (table| sudbquery join subquery | subq union subq)
string sPattern = @"(\((?<subquery1>(?>\((?<DEPTH>)|\)(?<-DEPTH>)|[^()]+)*(?(DEPTH)(?!)))\) as R1 union \((?<subquery2>(?>\((?<DEPTH>)|\)(?<-DEPTH>)|[^()]+)*(?(DEPTH)(?!)))\) as R2" +
@"|\((?<subquery1>(?>\((?<DEPTH>)|\)(?<-DEPTH>)|[^()]+)*(?(DEPTH)(?!)))\) as R1 join \((?<subquery2>(?>\((?<DEPTH>)|\)(?<-DEPTH>)|[^()]+)*(?(DEPTH)(?!)))\) as R2 on (?<joinCondition>.+)" +
@"|(?<tableName>[\w\d]+))";
Match match = Regex.Match(fromClause, sPattern, RegexOptions.IgnoreCase);
if (match.Success)
{
var tName = match.Groups["tableName"].Value;
var subquery1 = match.Groups["subquery1"].Value;
var subquery2 = match.Groups["subquery2"].Value;
var joinCondition = match.Groups["joinCondition"].Value;
if (!String.IsNullOrEmpty(tName))
{
resultingSQL.treeNodeType = QueryTree.TreeNodeType.GroundTable;
resultingSQL.tableName = tName;
}
else if (!String.IsNullOrEmpty(joinCondition))
{
resultingSQL.treeNodeType = QueryTree.TreeNodeType.Join;
resultingSQL.subquery.Add(ProcessExtensionalQuery(subquery1));
resultingSQL.subquery.Add(ProcessExtensionalQuery(subquery2));
resultingSQL.condition = joinCondition;
}
else if (!String.IsNullOrEmpty(subquery1))
{
resultingSQL.treeNodeType = QueryTree.TreeNodeType.Union;
resultingSQL.subquery.Add(ProcessExtensionalQuery(subquery1));
resultingSQL.subquery.Add(ProcessExtensionalQuery(subquery2));
}
else
{
throw new Exception("could not parse from clause, some value is null");
}
}
else
{
throw new Exception("query's format does not comply with SELECT QUERY");
}
return(resultingSQL);
}
//------------------ Getter/Setter ends -------------------
public SqlSelectQuery(string sql)
{
_sql = sql;
ParseEvaluationStrategyEnum();
if(_strategy==EvaluationStrategy.Extensional)
{
string sPattern = @"evaluate\s+using";
Match match = Regex.Match(_sql, sPattern, RegexOptions.IgnoreCase);
var index = match.Index;
var newSQL = _sql.Remove(index);
QueryTree query = ProcessExtensionalQuery(newSQL);
_queryTree = query;
}
else
{
ProcessAndPopulateEachField();
}
}
//------------------ Getter/Setter ends -------------------
public SqlSelectQuery(string sql)
{
_sql = sql;
ParseEvaluationStrategyEnum();
if (_strategy == EvaluationStrategy.Extensional)
{
string sPattern = @"evaluate\s+using";
Match match = Regex.Match(_sql, sPattern, RegexOptions.IgnoreCase);
var index = match.Index;
var newSQL = _sql.Remove(index);
QueryTree query = ProcessExtensionalQuery(newSQL);
_queryTree = query;
}
else
{
ProcessAndPopulateEachField();
}
}
private QueryTree ProcessFromAndWhere(string fromAndWhere, QueryTree resultingSQL)
{
string sPattern = @"where\s*(?<whereClause>.+)\z";
Match match = Regex.Match(fromAndWhere, sPattern, RegexOptions.IgnoreCase);
if (match.Success)
{
// string contain both clause
String whereClause = match.Groups["whereClause"].Value;
resultingSQL = ProcessWhereClause(whereClause, resultingSQL);
var position = match.Index;
var fromClause = fromAndWhere.Remove(position);
if (resultingSQL.treeNodeType == QueryTree.TreeNodeType.Select)
{
var SQLwithoutWhere = string.Format("Select * from {0}", fromClause);
resultingSQL.subquery.Add(ProcessExtensionalQuery(SQLwithoutWhere));
return(resultingSQL);
}
if (resultingSQL.treeNodeType == QueryTree.TreeNodeType.Difference)
{
//var SQLwithoutWhere = string.Format("Select * from {0}", fromClause);
fromClause = fromClause.Trim();
var fromClauseWithoutParenthsis = fromClause.TrimStart('(');
fromClauseWithoutParenthsis = fromClauseWithoutParenthsis.TrimEnd(')');
resultingSQL.subquery.Add(ProcessExtensionalQuery(fromClauseWithoutParenthsis));
return(resultingSQL);
}
resultingSQL = ProcessFromClause(fromClause, resultingSQL);
}
else
{
resultingSQL = ProcessFromClause(fromAndWhere, resultingSQL);
}
return(resultingSQL);
}
public string GetSql(QueryTree queryTree, out List<string> atts)
{
List<QueryTree> subquery;
string sql;
List<string> AttsSelectedByQuery;
switch (queryTree.treeNodeType)
{
case QueryTree.TreeNodeType.Select:
// If node is select condition, then Select * From H(query.Subquey[0]) Where query.condition
subquery = queryTree.subquery;
if ( subquery != null && subquery.Count==1)
{
var R1 = subquery[0];
List<string> attributes;
var fromClause = GetSql(R1, out attributes);
var condition = queryTree.condition;
sql = string.Format("select * from ({0}) as R where {1}", fromClause, condition);
atts = attributes;
return sql;
}
throw new Exception("number of subquery mismatch");
case QueryTree.TreeNodeType.Project:
// If node is project A, then Select R.A,IndepProject(R.P) as p from R group by R.A
subquery = queryTree.subquery;
if (subquery != null && subquery.Count == 1)
{
var R1 = subquery[0];
if(R1.treeNodeType==QueryTree.TreeNodeType.GroundTable)
{
queryTree.treeNodeType=QueryTree.TreeNodeType.GroundTable;
queryTree.tableName = R1.tableName;
queryTree.subquery = new List<QueryTree>();
return GetSql(queryTree,out atts);
}
List<string> attributes;
var fromClause = GetSql(R1, out attributes);
AttsSelectedByQuery = _queryTree.attributes;
sql = string.Format("Select {0},dbo.IndependentProject(R.P) as p from ({1}) as R group by {0}", string.Join(",", AttsSelectedByQuery), fromClause);
atts = AttsSelectedByQuery;
return sql;
}
throw new Exception("number of subquery mismatch");
case QueryTree.TreeNodeType.Join:
// If node is project A, then Select R.A,IndepProject(R.P) as p from R group by R.A
subquery = queryTree.subquery;
if (subquery != null && subquery.Count == 2)
{
List<string> a1;
List<string> a2;
var R1 = GetSql(subquery[0],out a1);
var R2 = GetSql(subquery[1],out a2);
var joinCondition=queryTree.condition;
var r1 = a1.Select(prefixR1);
var r2 = a2.Select(prefixR2);
var R1Atts = String.Join(",", r1);
var R2Atts = String.Join(",", r2);
sql = string.Format("Select {0},{1},(R1.p*R2.p/100) as p "+
"from ({2}) as R1 "+
"join ({3}) as R2 on {4}", R1Atts, R2Atts, R1, R2,joinCondition);
atts = (r1.Concat(r2)).ToList();
return sql;
}
throw new Exception("number of subquery mismatch");
case QueryTree.TreeNodeType.Union:
subquery = queryTree.subquery;
if (subquery != null && subquery.Count == 2)
{
List<string> a1;
List<string> a2;
var R1 = GetSql(subquery[0], out a1);
var R2 = GetSql(subquery[1], out a2);
var R1Atts = String.Join(",", a1);
var R2Atts = String.Join(",", a2);
sql = string.Format("select coalesce(R1.{0}, R2.{3}), " +
"(case when R1.p is null then R2.p " +
"when R2.p is null then R1.p "+
"else 100-(100-R1.p)*(100-R2.p)/100 "+
"end) as p from "+
"({1}) as R1 full outer join "+
"({2}) as R2 on R1.{0} = R2.{0}", R1Atts, R1, R2, R2Atts);
atts = a2;
return sql;
}
throw new Exception("number of subquery mismatch");
case QueryTree.TreeNodeType.Difference:
subquery = queryTree.subquery;
if (subquery != null && subquery.Count == 2)
{
List<string> a1;
List<string> a2;
var R1 = GetSql(subquery[0], out a1);
var R2 = GetSql(subquery[1], out a2);
var r1 = a1.Select(prefixR1);
var r2 = a2.Select(prefixR2);
var R1Atts = String.Join(",", r1);
var R2Atts = String.Join(",", r2);
sql = string.Format("Select {2},(R1.p*R2.p/100) as p " +
"from ({0}) as R1 " +
"join (Select {4}, 100-R.P as P from ({1}) as R) as R2 on {2}={3}", R1, R2, R1Atts, R2Atts, String.Join(",", a2));
atts= a1;
return sql;
}
throw new Exception("number of subquery mismatch");
case QueryTree.TreeNodeType.GroundTable:
var tName = queryTree.tableName;
var stateTable = tName + "_PossibleStates";
underlineDatabase.DropTableIfExist(stateTable);
var allAttsFromTable = PreparePossibleStatesTable(tName);
AttsSelectedByQuery = queryTree.attributes;
if ( AttsSelectedByQuery==null || (AttsSelectedByQuery.Count==1 && AttsSelectedByQuery[0]=="*"))
{
AttsSelectedByQuery = allAttsFromTable;
}
var attBeforeRenaming = AttsSelectedByQuery.Select(beforeRenaming).ToList();
var attAfterRenaming = AttsSelectedByQuery.Select(afterRenaming).ToList();
sql = string.Format("select var,{1},Sum(p) as p from {0} Group By var,{2}", stateTable, string.Join(",", AttsSelectedByQuery), string.Join(",", attBeforeRenaming));
var finalSql = string.Format("select {1},dbo.IndependentProject(p) as p from ({0}) as R Group By {1}", sql, string.Join(",", attAfterRenaming));
atts = attAfterRenaming;
return finalSql;
default:
throw new Exception("tree node type is invalid");
}
return "";
}
public ExtensionalTreeWalker(QueryTree queryTree, IStandardDatabase underlineDatabase)
{
this.underlineDatabase = underlineDatabase;
_queryTree = queryTree;
}
private QueryTree ProcessWhereClause(string whereClause, QueryTree resultingSQL)
{
// where clause could be (condition | not exists subquery1)
string sPattern = @"\A(not exists\s+(?<subquery>.*)|(?<condition>.*))";
Match match = Regex.Match(whereClause, sPattern, RegexOptions.IgnoreCase);
if (match.Success)
{
var subquery = match.Groups["subquery"].Value;
var condition = match.Groups["condition"].Value;
if (!string.IsNullOrEmpty(subquery))
{
resultingSQL.treeNodeType = QueryTree.TreeNodeType.Difference;
subquery = subquery.Trim();
var subWithoutParenthesis1 = subquery.TrimStart('(');
var subWithoutParenthesis2 = subWithoutParenthesis1.TrimEnd(')');
resultingSQL.subquery.Add(ProcessExtensionalQuery(subWithoutParenthesis2));
}
else
{
resultingSQL.treeNodeType = QueryTree.TreeNodeType.Select;
resultingSQL.condition = condition;
}
}
else
{
throw new Exception("query's format does not comply with SELECT QUERY");
}
return resultingSQL;
}
private QueryTree ProcessFromClause(string fromClause, QueryTree resultingSQL)
{
// form clause 3 alternative (table| sudbquery join subquery | subq union subq)
string sPattern = @"(\((?<subquery1>(?>\((?<DEPTH>)|\)(?<-DEPTH>)|[^()]+)*(?(DEPTH)(?!)))\) as R1 union \((?<subquery2>(?>\((?<DEPTH>)|\)(?<-DEPTH>)|[^()]+)*(?(DEPTH)(?!)))\) as R2" +
@"|\((?<subquery1>(?>\((?<DEPTH>)|\)(?<-DEPTH>)|[^()]+)*(?(DEPTH)(?!)))\) as R1 join \((?<subquery2>(?>\((?<DEPTH>)|\)(?<-DEPTH>)|[^()]+)*(?(DEPTH)(?!)))\) as R2 on (?<joinCondition>.+)"+
@"|(?<tableName>[\w\d]+))";
Match match = Regex.Match(fromClause, sPattern, RegexOptions.IgnoreCase);
if (match.Success)
{
var tName = match.Groups["tableName"].Value;
var subquery1 = match.Groups["subquery1"].Value;
var subquery2 = match.Groups["subquery2"].Value;
var joinCondition = match.Groups["joinCondition"].Value;
if (!String.IsNullOrEmpty(tName))
{
resultingSQL.treeNodeType=QueryTree.TreeNodeType.GroundTable;
resultingSQL.tableName = tName;
}else if (!String.IsNullOrEmpty(joinCondition))
{
resultingSQL.treeNodeType=QueryTree.TreeNodeType.Join;
resultingSQL.subquery.Add(ProcessExtensionalQuery(subquery1));
resultingSQL.subquery.Add(ProcessExtensionalQuery(subquery2));
resultingSQL.condition = joinCondition;
}
else if (!String.IsNullOrEmpty(subquery1))
{
resultingSQL.treeNodeType=QueryTree.TreeNodeType.Union;
resultingSQL.subquery.Add(ProcessExtensionalQuery(subquery1));
resultingSQL.subquery.Add(ProcessExtensionalQuery(subquery2));
}
else
{
throw new Exception("could not parse from clause, some value is null");
}
}
else
{
throw new Exception("query's format does not comply with SELECT QUERY");
}
return resultingSQL;
}
private QueryTree ProcessFromAndWhere(string fromAndWhere, QueryTree resultingSQL)
{
string sPattern = @"where\s*(?<whereClause>.+)\z";
Match match = Regex.Match(fromAndWhere, sPattern, RegexOptions.IgnoreCase);
if (match.Success)
{
// string contain both clause
String whereClause = match.Groups["whereClause"].Value;
resultingSQL = ProcessWhereClause(whereClause, resultingSQL);
var position = match.Index;
var fromClause = fromAndWhere.Remove(position);
if (resultingSQL.treeNodeType == QueryTree.TreeNodeType.Select)
{
var SQLwithoutWhere = string.Format("Select * from {0}", fromClause);
resultingSQL.subquery.Add(ProcessExtensionalQuery(SQLwithoutWhere));
return resultingSQL;
}
if (resultingSQL.treeNodeType==QueryTree.TreeNodeType.Difference)
{
//var SQLwithoutWhere = string.Format("Select * from {0}", fromClause);
fromClause = fromClause.Trim();
var fromClauseWithoutParenthsis = fromClause.TrimStart('(');
fromClauseWithoutParenthsis = fromClauseWithoutParenthsis.TrimEnd(')');
resultingSQL.subquery.Add(ProcessExtensionalQuery(fromClauseWithoutParenthsis));
return resultingSQL;
}
resultingSQL = ProcessFromClause(fromClause, resultingSQL);
}
else
{
resultingSQL = ProcessFromClause(fromAndWhere, resultingSQL);
}
return resultingSQL;
}
private QueryTree ProcessExtensionalQuery(string sql)
{
QueryTree resultingSQL = new QueryTree();
string sPattern = @"\A\s*select\s+(?<attributes>.+?)\s+from\s+(?<fromAndWhere>.+)";
Match match = Regex.Match(sql, sPattern, RegexOptions.IgnoreCase);
if (match.Success)
{
var csv = match.Groups["attributes"].Value;
String fromAndWhere = match.Groups["fromAndWhere"].Value;
if (csv != "*")
{
resultingSQL.attributes = csv.Split(',').ToList();
resultingSQL.treeNodeType = QueryTree.TreeNodeType.Project;
var SQLwithoutAtt = string.Format("Select * from {0}", fromAndWhere);
resultingSQL.subquery.Add(ProcessExtensionalQuery(SQLwithoutAtt));
return resultingSQL;
}
resultingSQL = ProcessFromAndWhere(fromAndWhere,resultingSQL);
}else{
throw new Exception("query's format does not comply with SELECT QUERY");
}
return resultingSQL;
}