private void ProcessSelectElement(SelectElement SelectElement, string ParentType, WithCtesAndXmlNamespaces Cte) { string ElemType = FragmentTypeParser.GetFragmentType(SelectElement); switch (ElemType) { case "SelectStarExpression": _smells.SendFeedBack(5, SelectElement); break; case "SelectScalarExpression": var ScalarExpression = (SelectScalarExpression)SelectElement; string ExpressionType = FragmentTypeParser.GetFragmentType(ScalarExpression.Expression); switch (ExpressionType) { case "ScalarSubquery": var SubQuery = (ScalarSubquery)ScalarExpression.Expression; _smells.ProcessQueryExpression(SubQuery.QueryExpression, ParentType, false, Cte); break; case "ColumnReferenceExpression": var Expression = (ColumnReferenceExpression)ScalarExpression.Expression; break; case "FunctionCall": _smells.FunctionProcessor.ProcessFunctionCall((FunctionCall)ScalarExpression.Expression); break; case "IntegerLiteral": break; case "ConvertCall": break; } break; case "SelectSetVariable": _smells.SelectSetProcessor.ProcessSelectSetVariable((SelectSetVariable)SelectElement); break; } }
public void ProcessWithCtesAndXmlNamespaces(WithCtesAndXmlNamespaces Cte) { foreach (CommonTableExpression Expression in Cte.CommonTableExpressions) { _smells.ProcessQueryExpression(Expression.QueryExpression, "RG", false, Cte); } }
public void Process(FromClause FromClause, WithCtesAndXmlNamespaces cte) { foreach (TableReference TableRef in FromClause.TableReferences) { ProcessTableReference(TableRef, cte); } }
public void Process(InsertStatement Fragment) { if (Fragment.InsertSpecification.Columns.Count == 0) { _smells.SendFeedBack(12, Fragment); } switch (FragmentTypeParser.GetFragmentType(Fragment.InsertSpecification.InsertSource)) { case "SelectInsertSource": var InsSource = (SelectInsertSource)Fragment.InsertSpecification.InsertSource; WithCtesAndXmlNamespaces Cte = Fragment.WithCtesAndXmlNamespaces; _smells.ProcessQueryExpression(InsSource.Select, "RG", false, Cte); if (Cte != null) { ProcessWithCtesAndXmlNamespaces(Cte); } break; case "ExecuteInsertSource": var ExecSource = (ExecuteInsertSource)Fragment.InsertSpecification.InsertSource; //ProcessExecuteSpecification(ExecSource.Execute); ExecutableEntity ExecutableEntity = ExecSource.Execute.ExecutableEntity; _smells.ExecutableEntityProcessor.ProcessExecutableEntity(ExecutableEntity); break; } }
public void ProcessWithCtesAndXmlNamespaces(WithCtesAndXmlNamespaces ctes) { foreach (var cte in ctes.CommonTableExpressions) { ProcessQueryExpression(cte.QueryExpression); } }
public override void Visit(WithCtesAndXmlNamespaces node) { /*Savoir s'il existe un with*/ filtreWith = true; string fromWith = GetNodeTokenText(node).ToLower(); /*On récupère la clause qui nous intéresse*/ Regex regex = new Regex(@"(from[\s+]*\[[0-9.A-z]*\].[\s+]*\[[A-z0-9. ,'-]*\]|from[\s+]*[A-z.0-9- ]+|join[\s+]+\[[A-z0-9\-]*\].\[[A-z0-9 .-]*\]|join[\s+]+[A-z.0-9\- ]*)"); Match match = regex.Match(fromWith); fromClause = ""; /*Tant qu'il y a des from qui nous intéresse*/ /*on extrait la clause*/ string tmp = fromWith.Substring(match.Index, match.Length); /*on met à jour le node*/ fromWith = fromWith.Substring(0, match.Index) + " " + fromWith.Substring(match.Index + match.Length, fromWith.Length - match.Index - match.Length); /*on supprime les join et from*/ tmp = (new Regex(@"from |join ")).Replace(tmp, ""); match = regex.Match(fromWith); /*s'il existe plusieurs tables séparées par ,*/ foreach (var seperate in tmp.Split(",")) { if (!tmp.Equals("") && !fromClause.Contains(seperate) && !selection.Contains(seperate)) { fromClause += " | " + seperate; } } }
public void ProcessSelectElements(IList<SelectElement> SelectElements, string ParentType, WithCtesAndXmlNamespaces Cte) { foreach (SelectElement SelectElement in SelectElements) { ProcessSelectElement(SelectElement, ParentType, Cte); } }
public void ProcessSelectElements(IList <SelectElement> SelectElements, string ParentType, WithCtesAndXmlNamespaces Cte) { foreach (SelectElement SelectElement in SelectElements) { ProcessSelectElement(SelectElement, ParentType, Cte); } }
public QsiTableDirectivesNode VisitWithCtesAndXmlNamespaces(WithCtesAndXmlNamespaces selectStatementWithCtesAndXmlNamespaces) { return(TreeHelper.Create <QsiTableDirectivesNode>(n => { n.IsRecursive = true; n.Tables.AddRange(selectStatementWithCtesAndXmlNamespaces.CommonTableExpressions.Select(VisitCommonTableExpression)); SqlServerTree.PutFragmentSpan(n, selectStatementWithCtesAndXmlNamespaces); })); }
public void Process(SelectStatement SelStatement, string ParentType, bool TestTop = false, WithCtesAndXmlNamespaces Cte = null) { if (Cte == null && SelStatement.WithCtesAndXmlNamespaces != null) { Cte = SelStatement.WithCtesAndXmlNamespaces; if (Cte != null) _smells.InsertProcessor.ProcessWithCtesAndXmlNamespaces(Cte); } _smells.ProcessQueryExpression(SelStatement.QueryExpression, ParentType, false, Cte); ProcessOptimizerHints(SelStatement.OptimizerHints, SelStatement); }
private bool isCteName(SchemaObjectName ObjectName, WithCtesAndXmlNamespaces cte) { if (cte == null) return false; foreach (CommonTableExpression Expression in cte.CommonTableExpressions) { if (Expression.ExpressionName.Value == ObjectName.BaseIdentifier.Value) { return true; } } return false; }
public void ProcessQueryExpression(QueryExpression queryExpression, string parentType, bool testTop = false, WithCtesAndXmlNamespaces cte = null) { string expressionType = FragmentTypeParser.GetFragmentType(queryExpression); switch (expressionType) { case "QuerySpecification": //{$Query = $Stmt.QueryExpression; var querySpec = (QuerySpecification)queryExpression; _selectStatementProcessor.ProcessSelectElements(querySpec.SelectElements, parentType, cte); if (querySpec.FromClause != null) { _fromProcessor.Process(querySpec.FromClause, cte); } if (querySpec.WhereClause != null) { _whereProcessor.Process(querySpec.WhereClause); } if (querySpec.OrderByClause != null) { _orderByProcessor.Process(querySpec.OrderByClause); if (parentType == "VW") { SendFeedBack(28, querySpec.OrderByClause); } } if (querySpec.TopRowFilter != null) { _topProcessor.ProcessTopFilter(querySpec.TopRowFilter); } break; case "QueryParenthesisExpression": //{$Query=$Stmt.QueryExpression.QueryExpression;break} var expression = (QueryParenthesisExpression)queryExpression; ProcessQueryExpression(expression.QueryExpression, "RG", testTop, cte); break; case "BinaryQueryExpression": var binaryQueryExpression = (BinaryQueryExpression)queryExpression; ProcessQueryExpression(binaryQueryExpression.FirstQueryExpression, parentType, testTop, cte); ProcessQueryExpression(binaryQueryExpression.SecondQueryExpression, parentType, testTop, cte); //BinaryQueryExpression. //{Process-BinaryQueryExpression $Stmt.QueryExpression;break;} break; } }
public void Process(SelectStatement SelStatement, string ParentType, bool TestTop = false, WithCtesAndXmlNamespaces Cte = null) { if (Cte == null && SelStatement.WithCtesAndXmlNamespaces != null) { Cte = SelStatement.WithCtesAndXmlNamespaces; if (Cte != null) { _smells.InsertProcessor.ProcessWithCtesAndXmlNamespaces(Cte); } } _smells.ProcessQueryExpression(SelStatement.QueryExpression, ParentType, false, Cte); ProcessOptimizerHints(SelStatement.OptimizerHints, SelStatement); }
private bool isCteName(SchemaObjectName ObjectName, WithCtesAndXmlNamespaces cte) { if (cte == null) { return(false); } foreach (CommonTableExpression Expression in cte.CommonTableExpressions) { if (Expression.ExpressionName.Value == ObjectName.BaseIdentifier.Value) { return(true); } } return(false); }
private OutputColumnDescriptor TryResolveColumnReferenceCoreSN(TSqlFragment node, string sourceNameOrAlias, string columnName) { OutputColumnDescriptor resultColumnType; if (statement is SelectStatement stmt_sel) { if (TryQueryExpression(stmt_sel.QueryExpression, stmt_sel.WithCtesAndXmlNamespaces, sourceNameOrAlias, columnName, out resultColumnType)) { return(resultColumnType); } return(null); // resolved failed!!! } if (statement is DataModificationStatement stmt_mod) { WithCtesAndXmlNamespaces source_ctes = stmt_mod.WithCtesAndXmlNamespaces; DataModificationSpecification source_specification = null; if (statement is InsertStatement stmt_ins && string.Equals(sourceNameOrAlias, "INSERTED", StringComparison.OrdinalIgnoreCase)) { source_specification = stmt_ins.InsertSpecification; } else if (statement is UpdateStatement stmt_upd) { source_specification = stmt_upd.UpdateSpecification; } else if (statement is DeleteStatement stmt_del) { source_specification = stmt_del.DeleteSpecification; } else if (statement is MergeStatement stmt_mrg) { source_specification = stmt_mrg.MergeSpecification; } if (source_specification != null && source_specification.OutputClause != null) { if (TryTableReference(true, true, source_specification.Target, source_ctes, null, columnName, out resultColumnType)) { return(resultColumnType); } throw new NotImplementedException("Target could not be found." + statement.WhatIsThis()); } }
private void ProcessTableReference(TableReference TableRef, WithCtesAndXmlNamespaces cte) { string Type = FragmentTypeParser.GetFragmentType(TableRef); switch (Type) { case "NamedTableReference": var NamedTableRef = (NamedTableReference) TableRef; if (NamedTableRef.SchemaObject.BaseIdentifier.Value[0] != '#' && NamedTableRef.SchemaObject.BaseIdentifier.Value[0] != '@') { if (NamedTableRef.SchemaObject.ServerIdentifier != null) { _smells.SendFeedBack(1, NamedTableRef); } if (NamedTableRef.SchemaObject.SchemaIdentifier == null && !isCteName(NamedTableRef.SchemaObject, cte)) { _smells.SendFeedBack(2, NamedTableRef); } } if (NamedTableRef.TableHints != null) { foreach (TableHint TableHint in NamedTableRef.TableHints) { switch (TableHint.HintKind) { case TableHintKind.NoLock: _smells.SendFeedBack(3, TableHint); break; case TableHintKind.ReadPast: break; case TableHintKind.ForceScan: _smells.SendFeedBack(44, TableHint); break; case TableHintKind.Index: _smells.SendFeedBack(45, TableHint); break; default: _smells.SendFeedBack(4, TableHint); break; } } } break; case "QueryDerivedTable": var QueryDerivedRef = (QueryDerivedTable) TableRef; String Alias = QueryDerivedRef.Alias.Value; if (Alias.Length == 1) { _smells.SendFeedBack(11, QueryDerivedRef); } if (FragmentTypeParser.GetFragmentType(QueryDerivedRef.QueryExpression) == "QuerySpecification") { // QuerySpecification QuerySpec = (QuerySpecification)QueryDerivedRef.QueryExpression; // Process(QuerySpec.FromClause, cte); _smells.ProcessQueryExpression(QueryDerivedRef.QueryExpression, "RG", true, cte); } break; case "QualifiedJoin": var QualifiedJoin = (QualifiedJoin) TableRef; ProcessTableReference(QualifiedJoin.FirstTableReference, cte); ProcessTableReference(QualifiedJoin.SecondTableReference, cte); break; } }
public (IEnumerable <Cte>, IEnumerable <Column>, IEnumerable <Table>) HandleCtes(WithCtesAndXmlNamespaces withCtesAndXmlNamespaces, Cte[] parentCtes, Table[] parentTables) { _level++; var tables = new List <Table>(); var columns = new List <Column>(); var ctes = new List <Cte>(); if (withCtesAndXmlNamespaces == null) { Dump($"{_pad}WithCtesAndXmlNamespaces is NULL"); _level--; return(Enumerable.Empty <Cte>(), Enumerable.Empty <Column>(), Enumerable.Empty <Table>()); } var i = 0; foreach (var commonTableExpression in withCtesAndXmlNamespaces.CommonTableExpressions) { Dump($"{_pad}| CommonTableExpressions[{i++}]"); var cte = new Cte { Name = commonTableExpression.ExpressionName?.Value }; ctes.Add(cte); parentCtes = parentCtes.Concat(new[] { cte }).Distinct().ToArray(); if (commonTableExpression.QueryExpression != null) { Dump($"{_pad}PROPERTY [QueryExpression]: handling..."); var result = HandleQuery(commonTableExpression.QueryExpression, parentCtes.ToArray(), parentTables.Distinct().ToArray()); columns.AddRange(result.Item1); tables.AddRange(result.Item2); } cte.LinkedTables = tables.Distinct().ToList(); foreach (var linkedTable in cte.LinkedTables) { linkedTable.SelectColumns = columns .Where(c => ReferenceEquals(c.AbsoluteTableReference, linkedTable)).Select(c => c.Name.ToLowerInvariant()) .ToArray(); linkedTable.PossibleSelectColumns = columns .Where(c => c.AmbiguousTableReferences != null && c.AmbiguousTableReferences.Any(t => ReferenceEquals(t, linkedTable))) .Select(c => c.Name.ToLowerInvariant()) .ToArray(); } if (cte.LinkedTables.Any()) { Dump($"{_pad}FOUND CTE-LINKED TABLES: {cte.Name}, tables: {string.Join(", ", cte.LinkedTables.Select(t => t.FullyQualifiedName))}"); } } _level--; return(ctes, columns.Where(c => c != null), tables); }
private void ProcessTableReference(TableReference TableRef, WithCtesAndXmlNamespaces cte) { string Type = FragmentTypeParser.GetFragmentType(TableRef); switch (Type) { case "NamedTableReference": var NamedTableRef = (NamedTableReference)TableRef; if (NamedTableRef.SchemaObject.BaseIdentifier.Value[0] != '#' && NamedTableRef.SchemaObject.BaseIdentifier.Value[0] != '@') { if (NamedTableRef.SchemaObject.ServerIdentifier != null) { _smells.SendFeedBack(1, NamedTableRef); } if (NamedTableRef.SchemaObject.SchemaIdentifier == null && !isCteName(NamedTableRef.SchemaObject, cte)) { _smells.SendFeedBack(2, NamedTableRef); } } if (NamedTableRef.TableHints != null) { foreach (TableHint TableHint in NamedTableRef.TableHints) { switch (TableHint.HintKind) { case TableHintKind.NoLock: _smells.SendFeedBack(3, TableHint); break; case TableHintKind.ReadPast: break; case TableHintKind.ForceScan: _smells.SendFeedBack(44, TableHint); break; case TableHintKind.Index: _smells.SendFeedBack(45, TableHint); break; default: _smells.SendFeedBack(4, TableHint); break; } } } break; case "QueryDerivedTable": var QueryDerivedRef = (QueryDerivedTable)TableRef; String Alias = QueryDerivedRef.Alias.Value; if (Alias.Length == 1) { _smells.SendFeedBack(11, QueryDerivedRef); } if (FragmentTypeParser.GetFragmentType(QueryDerivedRef.QueryExpression) == "QuerySpecification") { // QuerySpecification QuerySpec = (QuerySpecification)QueryDerivedRef.QueryExpression; // Process(QuerySpec.FromClause, cte); _smells.ProcessQueryExpression(QueryDerivedRef.QueryExpression, "RG", true, cte); } break; case "QualifiedJoin": var QualifiedJoin = (QualifiedJoin)TableRef; ProcessTableReference(QualifiedJoin.FirstTableReference, cte); ProcessTableReference(QualifiedJoin.SecondTableReference, cte); break; } }
public override void Visit(WithCtesAndXmlNamespaces node) { this.action(node); }
public override void ExplicitVisit(WithCtesAndXmlNamespaces fragment) { _fragments.Add(fragment); }
public void ProcessQueryExpression(QueryExpression queryExpression, string parentType, bool testTop = false, WithCtesAndXmlNamespaces cte = null) { string expressionType = FragmentTypeParser.GetFragmentType(queryExpression); switch (expressionType) { case "QuerySpecification": //{$Query = $Stmt.QueryExpression; var querySpec = (QuerySpecification) queryExpression; _selectStatementProcessor.ProcessSelectElements(querySpec.SelectElements, parentType, cte); if (querySpec.FromClause != null) _fromProcessor.Process(querySpec.FromClause, cte); if (querySpec.WhereClause != null) _whereProcessor.Process(querySpec.WhereClause); if (querySpec.OrderByClause != null) { _orderByProcessor.Process(querySpec.OrderByClause); if (parentType == "VW") { SendFeedBack(28, querySpec.OrderByClause); } } if (querySpec.TopRowFilter != null) _topProcessor.ProcessTopFilter(querySpec.TopRowFilter); break; case "QueryParenthesisExpression": //{$Query=$Stmt.QueryExpression.QueryExpression;break} var expression = (QueryParenthesisExpression) queryExpression; ProcessQueryExpression(expression.QueryExpression, "RG", testTop, cte); break; case "BinaryQueryExpression": var binaryQueryExpression = (BinaryQueryExpression) queryExpression; ProcessQueryExpression(binaryQueryExpression.FirstQueryExpression, parentType, testTop, cte); ProcessQueryExpression(binaryQueryExpression.SecondQueryExpression, parentType, testTop, cte); //BinaryQueryExpression. //{Process-BinaryQueryExpression $Stmt.QueryExpression;break;} break; } }