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 Cte)
{
foreach (CommonTableExpression Expression in Cte.CommonTableExpressions)
{
_smells.ProcessQueryExpression(Expression.QueryExpression, "RG", false, Cte);
}
}
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());
}
}
public void ProcessWithCtesAndXmlNamespaces(WithCtesAndXmlNamespaces ctes)
{
foreach (var cte in ctes.CommonTableExpressions)
{
ProcessQueryExpression(cte.QueryExpression);
}
}
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 Process(FromClause FromClause, WithCtesAndXmlNamespaces cte)
{
foreach (TableReference TableRef in FromClause.TableReferences)
{
ProcessTableReference(TableRef, cte);
}
}
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;
}
}
