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