public void NormalizeQuerySpecification(QuerySpecification qs) { foreach (var sq in qs.EnumerateSubqueries()) { NormalizeSelectStatement(sq.SelectStatement); } // Process join conditions conditions = new List <LogicalExpressions.Expression>(); var from = qs.FindDescendant <FromClause>(); if (from != null) { var tablesource = from.FindDescendant <TableSourceExpression>(); foreach (JoinedTable jt in tablesource.EnumerateDescendantsRecursive <JoinedTable>(typeof(Subquery))) { // CROSS JOIN queries have no search condition SearchCondition sc = jt.FindDescendant <SearchCondition>(); if (sc != null) { conditions.Add(GetConjunctiveNormalForm(sc)); } } } // Process where clause WhereClause where = qs.FindDescendant <WhereClause>(); if (where != null) { var sc = where.FindDescendant <SearchCondition>(); conditions.Add(GetConjunctiveNormalForm(sc)); } }
public void NormalizeQuerySpecification(QuerySpecification qs) { foreach (var sq in qs.EnumerateSubqueries()) { NormalizeSelectStatement(sq.SelectStatement); } // Process join conditions conditions = new List<LogicalExpressions.Expression>(); var from = qs.FindDescendant<FromClause>(); if (from != null) { var tablesource = from.FindDescendant<TableSourceExpression>(); foreach (JoinedTable jt in tablesource.EnumerateDescendantsRecursive<JoinedTable>(typeof(Subquery))) { // CROSS JOIN queries have no search condition SearchCondition sc = jt.FindDescendant<SearchCondition>(); if (sc != null) { conditions.Add(GetConjunctiveNormalForm(sc)); } } } // Process where clause WhereClause where = qs.FindDescendant<WhereClause>(); if (where != null) { var sc = where.FindDescendant<SearchCondition>(); conditions.Add(GetConjunctiveNormalForm(sc)); } }
protected void ResolveOrderByClause(OrderByClause orderBy, QuerySpecification firstqs) { if (orderBy != null) { ResolveTableReferences(firstqs, orderBy); ResolveColumnReferences(firstqs, orderBy, ColumnContext.OrderBy); } }
/// <summary> /// Collect list of table sources and load columns from the schema. /// </summary> /// <remarks> /// Source tables are put into a dictionary that is keyed by table alias /// or table name. /// </remarks> /// <param name="qs"></param> private void CollectSourceTableReferences(QuerySpecification qs) { // --- Collect column references from subqueries or load from the database schema foreach (var tr in qs.EnumerateSourceTableReferences(false)) { string tablekey; if (tr.IsSubquery || tr.IsComputed || tr.IsUdf || tr.Alias != null) { tablekey = tr.Alias; } else { // If no alias is used then use table name tablekey = tr.DatabaseObjectName; } // Make sure that table key is used only once if (qs.SourceTableReferences.ContainsKey(tablekey)) { throw CreateException(ExceptionMessages.DuplicateTableAlias, null, tablekey, tr.Node); } else { var ntr = new TableReference(tr); if (!ntr.IsSubquery && !ntr.IsComputed) { // Load table description from underlying schema // Attempt to load dataset and throw exception of name cannot be resolved DatasetBase ds; try { ds = schemaManager.Datasets[ntr.DatasetName]; } catch (KeyNotFoundException ex) { throw CreateException(ExceptionMessages.UnresolvableDatasetReference, ex, ntr.DatasetName, ntr.Node); } catch (SchemaException ex) { throw CreateException(ExceptionMessages.UnresolvableDatasetReference, ex, ntr.DatasetName, ntr.Node); } ntr.DatabaseObject = ds.GetObject(ntr.DatabaseName, ntr.SchemaName, ntr.DatabaseObjectName); // Load column descriptions for the table ntr.LoadColumnReferences(schemaManager); } qs.SourceTableReferences.Add(tablekey, ntr); } } }
/// <summary> /// Looks for a * in the select list and throws an exception if there's any /// </summary> /// <param name="qs"></param> protected void ValidateStarInSelectList(QuerySpecification qs) { // Look for stars (*) in the select list foreach (var i in qs.FindDescendant <SelectList>().Nodes) { if (i is Mul) { throw CreateException(ExceptionMessages.StarColumnNotAllowed, i); } } }
public static SelectList Create(QuerySpecification querySpecification) { SelectList root = null; SelectList last = null; foreach (var tr in querySpecification.SourceTableReferences.Values) { Create(ref root, ref last, tr); } return root; }
public static SelectList Create(QuerySpecification querySpecification) { SelectList root = null; SelectList last = null; foreach (var tr in querySpecification.SourceTableReferences.Values) { Create(ref root, ref last, tr); } return(root); }
private void CopyResultsColumns(QuerySpecification qs) { int index = 0; foreach (var ce in qs.EnumerateSelectListColumnExpressions()) { var cr = ce.ColumnReference; cr.SelectListIndex = index++; qs.ResultsTableReference.ColumnReferences.Add(cr); } }
/// <summary> /// Substitutes dataset and schema defaults into table source table references /// </summary> /// <param name="qs"></param> protected void SubstituteTableAndColumnDefaults(QuerySpecification qs) { foreach (var tr in qs.EnumerateSourceTableReferences(false)) { if (tr.IsTableOrView) { tr.SubstituteDefaults(SchemaManager, defaultTableDatasetName); } else if (tr.IsUdf) { tr.SubstituteDefaults(SchemaManager, defaultFunctionDatasetName); } } }
protected void ValidateColumnExpression(QuerySpecification qs, ColumnExpression ce) { // TODO: delete // eliminate star queries /*if (ce.ColumnReference.IsStar) { throw CreateException(ExceptionMessages.StarColumnNotAllowed, null, null, ce); }*/ foreach (ColumnIdentifier ci in ce.EnumerateDescendantsRecursive<ColumnIdentifier>(null)) { ValidateColumnIdentifier(qs, ci); } // *** TODO: look for duplicate aliases }
protected void ValidateColumnExpression(QuerySpecification qs, ColumnExpression ce) { // TODO: delete // eliminate star queries /*if (ce.ColumnReference.IsStar) * { * throw CreateException(ExceptionMessages.StarColumnNotAllowed, null, null, ce); * }*/ foreach (ColumnIdentifier ci in ce.EnumerateDescendantsRecursive <ColumnIdentifier>(null)) { ValidateColumnIdentifier(qs, ci); } // *** TODO: look for duplicate aliases }
private void SubstituteStars(QuerySpecification qs, Node node) { if (node != null) { var n = node.Stack.First; while (n != null) { if (n.Value is SelectList) { n.Value = ((SelectList)n.Value).SubstituteStars(); } n = n.Next; } } }
protected void ValidateQuerySpecification(QuerySpecification qs) { // Call recursively for subqueries #if false foreach (var sq in qs.EnumerateSubqueries()) { ValidateQuerySpecification(sq); } #endif ValidateTableReferences(qs); ValidateStarInSelectList(qs); // Loop through all column expressions of the select list and try to resolve them foreach (ColumnExpression ce in qs.FindDescendant <SelectList>().EnumerateDescendants <ColumnExpression>()) { ValidateColumnExpression(qs, ce); } }
/// <summary> /// Resolves all table references of all nodes below a node, /// not descending into subqueries /// </summary> /// <param name="qs"></param> /// <param name="n"></param> private void ResolveTableReferences(QuerySpecification qs, Node n) { foreach (object o in n.Nodes) { // Skip the into and clause and subqueries if (!(o is IntoClause) && !(o is SubqueryTableSource)) { if (o is Node) { ResolveTableReferences(qs, (Node)o); // Recursive call } } } if (n is ITableReference && ((ITableReference)n).TableReference != null) { ResolveTableReference(qs, (ITableReference)n); } }
/// <summary> /// Internal routine to perform the name resolution steps on a single /// query specification /// </summary> /// <param name="qs"></param> protected void ResolveQuerySpecification(QuerySpecification qs, int depth) { // At this point the table and column references are all parsed // from the query but no name resolution and cross-identification // of these references have happened yet. After the cross-idenfication // routine, the same tables and columns will be tagged by the // same TableReference and ColumnReference instances. // First of all, call everything recursively for subqueries. Subqueries // can appear within the table sources and in the where clause semi-join // expressions foreach (var sq in qs.EnumerateSubqueries()) { ResolveSelectStatement(sq.SelectStatement, depth + 1); } // Substitute default dataset names and schema names // This is typically the MYDB and dbo SubstituteTableAndColumnDefaults(qs); // Column references will be stored under the query specification CollectSourceTableReferences(qs); // Column identifiers can contain table names, aliases or nothing, // resolve them now ResolveTableReferences(qs); // Substitute SELECT * expressions SubstituteStars(qs); // Resolve column references of each occurance ResolveColumnReferences(qs); // Copy resultset columns to the appropriate collection CopyResultsColumns(qs); // Add default aliases to column expressions in the form of tablealias_columnname if (depth == 0) { AssignDefaultColumnAliases(qs); } }
/// <summary> /// Resolves all table references of all nodes below a node, /// not descending into subqueries /// </summary> /// <param name="qs"></param> /// <param name="n"></param> private void ResolveColumnReferences(QuerySpecification qs, Node n, ColumnContext context) { context = GetColumnContext(n, context); foreach (object o in n.Nodes) { // Skip the into and clause and subqueries if (!(o is IntoClause) && !(o is SubqueryTableSource)) { if (o is Node) { ResolveColumnReferences(qs, (Node)o, context); // Recursive call } } } if (n is IColumnReference) { ResolveColumnReference(qs, (IColumnReference)n, context); } }
protected void ValidateColumnIdentifier(QuerySpecification qs, ColumnIdentifier ci) { // TODO: modify to look for stars inside expressions /* * if (ci.ColumnReference.IsStar) * { * // Check if it's a count(*), that's the only expression allowed to * // contain a * * var fc = ci.FindAscendant<FunctionCall>(); * if (fc != null) * { * var fn = fc.FindDescendantRecursive<FunctionName>(); * if (fn != null && SqlParser.ComparerInstance.Compare(fn.Value, "COUNT") == 0) * { * return; * } * } * * throw CreateException(ExceptionMessages.StarColumnNotAllowed, null, null, ci); * }*/ }
/// <summary> /// Adds default aliases to columns with no aliases specified in the query /// </summary> /// <param name="qs"></param> protected void AssignDefaultColumnAliases(QuerySpecification qs) { var aliases = new HashSet <string>(SchemaManager.Comparer); foreach (var ce in qs.EnumerateSelectListColumnExpressions()) { var cr = ce.ColumnReference; string alias; if (cr.ColumnAlias == null) { if (cr.ColumnName == null) { alias = GetUniqueColumnAlias(aliases, String.Format("Col_{0}", cr.SelectListIndex)); } else { if (cr.TableReference != null && cr.TableReference.Alias != null) { alias = GetUniqueColumnAlias(aliases, String.Format("{0}_{1}", cr.TableReference.Alias, cr.ColumnName)); } else { alias = GetUniqueColumnAlias(aliases, cr.ColumnName); } } } else { // Alias is set explicitly, so do not make it unique forcibly alias = cr.ColumnAlias; } aliases.Add(alias); cr.ColumnAlias = alias; } }
protected void ValidateTableReferences(QuerySpecification qs) { #if false foreach (var ats in qs.EnumerateTableSources(false)) { var ts = ats.FindDescendant <SimpleTableSource>(); if (ts != null) { TableReference tr = ts.TableReference; // Make sure no database name specified -> invalid in skyquery if (tr.DatabaseName != null) { throw CreateException(ExceptionMessages.DatabaseNameNotAllowed, null, tr.DatabaseName, ts); } return; } throw new NotImplementedException(); } #endif }
/// <summary> /// Replace SELECT * and SELECT alias.* with explicit column lists /// </summary> /// <param name="qs"></param> private void SubstituteStars(QuerySpecification qs) { SubstituteStars(qs, qs); }
/// <summary> /// Resolves the table references of all nodes below a query specification /// not descending into subqueries /// </summary> /// <param name="qs"></param> private void ResolveTableReferences(QuerySpecification qs) { ResolveTableReferences(qs, (Node)qs); }
private void CopyMembers(QuerySpecification old) { this.sourceTableReferences = new Dictionary<string, TableReference>(old.sourceTableReferences); this.resultsTableReference = new TableReference(old.resultsTableReference); }
/// <summary> /// Looks for a * in the select list and throws an exception if there's any /// </summary> /// <param name="qs"></param> protected void ValidateStarInSelectList(QuerySpecification qs) { // Look for stars (*) in the select list foreach (var i in qs.FindDescendant<SelectList>().Nodes) { if (i is Mul) { throw CreateException(ExceptionMessages.StarColumnNotAllowed, i); } } }
protected void ValidateTableReferences(QuerySpecification qs) { #if false foreach (var ats in qs.EnumerateTableSources(false)) { var ts = ats.FindDescendant<SimpleTableSource>(); if (ts != null) { TableReference tr = ts.TableReference; // Make sure no database name specified -> invalid in skyquery if (tr.DatabaseName != null) { throw CreateException(ExceptionMessages.DatabaseNameNotAllowed, null, tr.DatabaseName, ts); } return; } throw new NotImplementedException(); } #endif }
protected void ValidateQuerySpecification(QuerySpecification qs) { // Call recursively for subqueries #if false foreach (var sq in qs.EnumerateSubqueries()) { ValidateQuerySpecification(sq); } #endif ValidateTableReferences(qs); ValidateStarInSelectList(qs); // Loop through all column expressions of the select list and try to resolve them foreach (ColumnExpression ce in qs.FindDescendant<SelectList>().EnumerateDescendants<ColumnExpression>()) { ValidateColumnExpression(qs, ce); } }
public TableReference(QuerySpecification qs) { InitializeMembers(); this.Node = qs; }
private void ResolveColumnReference(QuerySpecification qs, IColumnReference cr, ColumnContext context) { // Try to resolve the table belonging to a column based solely on // column name. This function is called only on column references with // unspecified table parts. // Star columns cannot be resolved, treat them separately if (!cr.ColumnReference.IsStar && !cr.ColumnReference.IsComplexExpression) { ColumnReference ncr = null; int q = 0; if (cr.ColumnReference.TableReference.IsUndefined) { // This has an empty table reference (only column name specified) // Look for a match based on column name only foreach (var tr in qs.SourceTableReferences.Values) { foreach (var ccr in tr.ColumnReferences) { if (cr.ColumnReference.Compare(ccr)) { if (q != 0) { throw CreateException(ExceptionMessages.AmbigousColumnReference, null, cr.ColumnReference.ColumnName, (Node)cr); } ncr = ccr; q++; } } } } else if (!cr.ColumnReference.TableReference.IsUndefined) { foreach (var ccr in cr.ColumnReference.TableReference.ColumnReferences) { if (cr.ColumnReference.Compare(ccr)) { if (q != 0) { throw CreateException(ExceptionMessages.AmbigousColumnReference, null, cr.ColumnReference.ColumnName, (Node)cr); } ncr = ccr; q++; } } } if (q == 0) { throw CreateException(ExceptionMessages.UnresolvableColumnReference, null, cr.ColumnReference.ColumnName, (Node)cr); } // Make copy here and preserve alias! ncr.ColumnContext |= context; ncr = new ColumnReference(ncr); if (cr.ColumnReference != null && cr.ColumnReference.ColumnAlias != null) { ncr.ColumnAlias = cr.ColumnReference.ColumnAlias; } cr.ColumnReference = ncr; } }
private void CopyMembers(QuerySpecification old) { this.sourceTableReferences = new Dictionary <string, TableReference>(old.sourceTableReferences); this.resultsTableReference = new TableReference(old.resultsTableReference); }
private void ResolveColumnReferences(QuerySpecification qs) { ResolveColumnReferences(qs, (Node)qs, ColumnContext.None); }
/// <summary> /// Resolves a table reference to a table listed in SourceTableReferences /// </summary> /// <param name="qs"></param> /// <param name="tr"></param> private void ResolveTableReference(QuerySpecification qs, ITableReference node) { // Try to resolve the table alias part of a table reference // If and alias or table name is specified, this can be done based on // the already collected table sources. // If no table or alias is specified and the current node is a column reference, // where the column is not a complex expression, resolution might be successful by // column name only. if (!node.TableReference.IsUndefined) { TableReference ntr = null; string alias = null; if (node.TableReference.Alias != null) { // if table alias found explicitly alias = node.TableReference.Alias; } else if (node.TableReference.DatasetName == null && node.TableReference.DatabaseName == null && node.TableReference.SchemaName == null && node.TableReference.DatabaseObjectName != null && qs.SourceTableReferences.ContainsKey(node.TableReference.DatabaseObjectName)) { // if only table name found and that's an alias alias = node.TableReference.DatabaseObjectName; } if (alias != null) { ntr = qs.SourceTableReferences[alias]; } else { // Check if dataset specified and make sure it's valid if (node.TableReference.DatasetName != null) { if (!schemaManager.Datasets.ContainsKey(node.TableReference.DatasetName)) { throw CreateException(ExceptionMessages.UnresolvableDatasetReference, null, node.TableReference.DatasetName, (Node)node); } } // if only a table name found and that's not an alias -> must be a table int q = 0; foreach (var key in qs.SourceTableReferences.Keys) { var tr = qs.SourceTableReferences[key]; if (tr.Compare(node.TableReference)) { if (q != 0) { throw CreateException(ExceptionMessages.AmbigousTableReference, null, node.TableReference.DatabaseObjectName, (Node)node); } ntr = tr; q++; } } } if (ntr == null) { throw CreateException(ExceptionMessages.UnresolvableTableReference, null, node.TableReference.DatabaseObjectName, (Node)node); } node.TableReference = ntr; } }
private void AppendPartitioningConditions(QuerySpecification qs, SimpleTableSource ts) { if (!double.IsInfinity(PartitioningKeyFrom) || !double.IsInfinity(PartitioningKeyTo)) { var cg = new SqlServerCodeGenerator(); string format; if (double.IsInfinity(PartitioningKeyFrom) && double.IsInfinity(PartitioningKeyTo)) { format = "{1} <= {0} AND {0} < {2}"; } else if (double.IsInfinity(PartitioningKeyFrom)) { format = "{0} < {2}"; } else { format = "{1} <= {0}"; } string sql = String.Format(format, cg.GetResolvedColumnName(ts.PartitioningColumnReference), PartitioningKeyFrom.ToString(System.Globalization.CultureInfo.InvariantCulture), PartitioningKeyTo.ToString(System.Globalization.CultureInfo.InvariantCulture)); var parser = new Jhu.Graywulf.SqlParser.SqlParser(); var sc = (SearchCondition)parser.Execute(new SearchCondition(), sql); var where = qs.FindDescendant<WhereClause>(); if (where == null) { where = WhereClause.Create(sc); var ws = Whitespace.Create(); var wsn = qs.Stack.AddAfter(qs.Stack.Find(qs.FindDescendant<FromClause>()), ws); qs.Stack.AddAfter(wsn, where); } else { where.AppendCondition(sc, "AND"); } } // --- remove partition clause ts.Stack.Remove(ts.FindDescendant<TablePartitionClause>()); }
/// <summary> /// Substitutes dataset and schema defaults into table source table references /// </summary> /// <param name="qs"></param> protected void SubstituteDefaults(QuerySpecification qs) { foreach (var tr in qs.EnumerateSourceTableReferences(false)) { if (tr.IsTableOrView) { tr.SubstituteDefaults(SchemaManager, defaultTableDatasetName); } else if (tr.IsUdf) { tr.SubstituteDefaults(SchemaManager, defaultFunctionDatasetName); } } }
/// <summary> /// Resolves a table reference to a table listed in SourceTableReferences /// </summary> /// <param name="qs"></param> /// <param name="tr"></param> private void ResolveTableReference(QuerySpecification qs, ITableReference node) { // Try to resolve the table alias part of a table reference // If and alias or table name is specified, this can be done based on // the already collected table sources. // If no table or alias is specified and the current node is a column reference, // where the column is not a complex expression, resolution might be successful by // column name only. if (!node.TableReference.IsUndefined) { TableReference ntr = null; string alias = null; if (node.TableReference.Alias != null) { // if table alias found explicitly alias = node.TableReference.Alias; } else if (node.TableReference.DatasetName == null && node.TableReference.DatabaseName == null && node.TableReference.SchemaName == null && node.TableReference.DatabaseObjectName != null && qs.SourceTableReferences.ContainsKey(node.TableReference.DatabaseObjectName)) { // if only table name found and that's an alias alias = node.TableReference.DatabaseObjectName; } if (alias != null) { ntr = qs.SourceTableReferences[alias]; } else { // Check if dataset specified and make sure it's valid if (node.TableReference.DatasetName != null) { if (!schemaManager.Datasets.ContainsKey(node.TableReference.DatasetName)) { throw CreateException(ExceptionMessages.UnresolvableDatasetReference, null, node.TableReference.DatasetName, (Node)node); } } // if only a table name found and that's not an alias -> must be a table int q = 0; foreach (var tr in qs.SourceTableReferences.Values) { if (tr.Compare(node.TableReference)) { if (q != 0) { throw CreateException(ExceptionMessages.AmbigousTableReference, null, node.TableReference.DatabaseObjectName, (Node)node); } ntr = tr; q++; } } } if (ntr == null) { throw CreateException(ExceptionMessages.UnresolvableTableReference, null, node.TableReference.DatabaseObjectName, (Node)node); } node.TableReference = ntr; } }
/// <summary> /// Adds default aliases to columns with no aliases specified in the query /// </summary> /// <param name="qs"></param> protected void AssignDefaultColumnAliases(QuerySpecification qs) { var aliases = new HashSet<string>(SchemaManager.Comparer); foreach (var ce in qs.EnumerateSelectListColumnExpressions()) { var cr = ce.ColumnReference; string alias; if (cr.ColumnAlias == null) { if (cr.ColumnName == null) { alias = GetUniqueColumnAlias(aliases, String.Format("Col_{0}", cr.SelectListIndex)); } else { if (cr.TableReference != null && cr.TableReference.Alias != null) { alias = GetUniqueColumnAlias(aliases, String.Format("{0}_{1}", cr.TableReference.Alias, cr.ColumnName)); } else { alias = GetUniqueColumnAlias(aliases, cr.ColumnName); } } } else { // Alias is set explicitly, so do not make it unique forcibly alias = cr.ColumnAlias; } aliases.Add(alias); cr.ColumnAlias = alias; } }
public TableReference(QuerySpecification qs) { InitializeMembers(); this.node = qs; }
protected void ValidateColumnIdentifier(QuerySpecification qs, ColumnIdentifier ci) { // TODO: modify to look for stars inside expressions /* if (ci.ColumnReference.IsStar) { // Check if it's a count(*), that's the only expression allowed to // contain a * var fc = ci.FindAscendant<FunctionCall>(); if (fc != null) { var fn = fc.FindDescendantRecursive<FunctionName>(); if (fn != null && SqlParser.ComparerInstance.Compare(fn.Value, "COUNT") == 0) { return; } } throw CreateException(ExceptionMessages.StarColumnNotAllowed, null, null, ci); }*/ }
/// <summary> /// Internal routine to perform the name resolution steps on a single /// query specification /// </summary> /// <param name="qs"></param> protected void ResolveQuerySpecification(QuerySpecification qs, int depth) { // At this point the table and column references are all parsed // from the query but no name resolution and cross-identification // of these references have happened yet. After the cross-idenfication // routine, the same tables and columns will be tagged by the // same TableReference and ColumnReference instances. // First of all, call everything recursively for subqueries. Subqueries // can appear within the table sources and in the where clause semi-join // expressions foreach (var sq in qs.EnumerateSubqueries()) { ResolveSelectStatement(sq.SelectStatement, depth + 1); } // Substitute default dataset names and schema names // This is typically the MYDB and dbo SubstituteDefaults(qs); // Column references will be stored under the query specification CollectSourceTableReferences(qs); // Column identifiers can contain table names, aliases or nothing, // resolve them now ResolveTableReferences(qs); // Substitute SELECT * expressions SubstituteStars(qs); // Resolve column references of each occurance ResolveColumnReferences(qs); // Copy resultset columns to the appropriate collection CopyResultsColumns(qs); // Add default aliases to column expressions in the form of tablealias_columnname if (depth == 0) { AssignDefaultColumnAliases(qs); } }
public QuerySpecification(QuerySpecification old) : base(old) { CopyMembers(old); }