public override void HandleNode()
{
ChkColumnRefTables = ASTHelperMethod.GetTableRefsInCurSelect(Node).Where(d =>
{
if (d.Type != 1)
{
return(false);
}
return(DatabaseManager.GetTableColumns(d.TableName).Contains(ColumnName));
}).ToList();
if (!string.IsNullOrEmpty(TableName))
{
ChkColumnRefTables.RemoveAll(t => !t.TableName.Equals(TableName, StringComparison.InvariantCultureIgnoreCase));
}
var boolComparsions = Node.SelectNodes(".//SqlComparisonBooleanExpression");
foreach (XmlNode boolComparsion in boolComparsions)
{
DoValidStatus(boolComparsion);
}
if (ChkColumnRefTables.Count > 0)
{
ChkColumnRefTables.ForEach(t =>
{
WriteLog(string.Format("Table [{0}] does not contain the filter {1}", t.TableName, Filter), 2, t.LocationStr);
});
}
}
public void Init()
{
Positions = new List <string>();
Logger = new StringBuilder();
LogList = new List <LogInfo>();
UserDefinedDict.Clear();
Doc = ASTHelperMethod.ParseAndGetAST(SqlScript);
HasRun = false;
}
public override void HandleNode()
{
var leftJoinNodes = Node.SelectNodes("//SqlQualifiedJoinTableExpression[@JoinOperator='LeftOuterJoin']").Cast <XmlNode>();
foreach (var node in leftJoinNodes)
{
var p1 = ASTHelperMethod.GetPosition(node.ChildNodes[0]).Item2; // left table, location Y
var p2 = ASTHelperMethod.GetPosition(node.ChildNodes[1]).Item1;
var tokens = ASTHelperMethod.GetTokensBetweenPoints(Node.OwnerDocument, p1, p2);
if (tokens.Find(n => n.Attributes["type"].Value == "TOKEN_OUTER") == null)
{
WriteLog("Use LFET OUTER JOIN instead of LEFT JOIN", pos: node.Attributes["Location"].Value);
}
}
}
private void DoValidStatus(XmlNode n)
{
var schemaName = DoValidInternal(n);
if (!string.IsNullOrEmpty(schemaName))
{
var tableName = ASTHelperMethod.GetTableNameIfHasAlias(StyleCopContext.Doc, schemaName);
var firstMatch = ChkColumnRefTables.FirstOrDefault(t => schemaName.Equals(t.Alias, StringComparison.InvariantCultureIgnoreCase));
if (firstMatch == null)
{
firstMatch = ChkColumnRefTables.FirstOrDefault(t => t.TableName.Equals(tableName, StringComparison.InvariantCultureIgnoreCase));
}
if (firstMatch != null)
{
ChkColumnRefTables.Remove(firstMatch);
}
}
}
public override void HandleNode()
{
var schemaName = Node.Attributes["SchemaName"] != null ? Node.Attributes["SchemaName"].Value : null;
var objectName = Node.Attributes["ObjectName"] != null ? Node.Attributes["ObjectName"].Value : null;
string tableName = null;
string colName = null;
string tablePos = null;
string colPos = null;
if (schemaName != null)
{
tableName = ASTHelperMethod.GetTableNameIfHasAlias(Node.OwnerDocument, schemaName);
colName = objectName;
tablePos = Node.ChildNodes[0].Attributes["Location"].Value;
colPos = Node.ChildNodes[1].Attributes["Location"].Value;
}
else if (objectName != null)
{
if (Node.LookUpForFirstNode("SqlTableRefExpression"
, "SqlSelectStarExpression") != null)
{
tableName = ASTHelperMethod.GetTableNameIfHasAlias(Node.OwnerDocument, objectName);
tablePos = Node.ChildNodes[0].Attributes["Location"].Value;
}
else if (Node.LookUpForFirstNode("SqlColumnRefExpression") != null)
{
colName = objectName;
colPos = Node.ChildNodes[0].Attributes["Location"].Value;
}
}
if (tableName != null)
{
var isActualTable = ASTHelperMethod.GetTableDefinationNode(Node.OwnerDocument, tableName) != null;
if (!isActualTable)
{
return;
}
var lowerTableName = tableName.ToLower();
if (TableLowers.ContainsKey(lowerTableName))
{
var isAlias = tableName != schemaName && tableName != objectName;
if (!isAlias && TableLowers[lowerTableName] != tableName)
{
WriteLog(string.Format("Table name [{0}] does not exactly match.", tableName), 1, tablePos);
}
var cols = TableSchemas[TableLowers[lowerTableName]];
var colDict = cols.ToDictionary(i => i.ToLower());
if (colName == null)
{
return;
}
var lowerColName = colName.ToLower();
if (!colDict.ContainsKey(lowerColName))
{
WriteLog(string.Format("Table {0} does not contains the column {1}.", tableName, colName), 1, colPos);
}
else
{
if (colDict[lowerColName] != colName)
{
WriteLog(string.Format("Column name [{0}] does not exactly match.", colName), 1, colPos);
}
}
}
}
}
public override void HandleNode()
{
var varDict = new Dictionary <string, string>();
Node.SelectNodes(".//SqlInlineTableVariableDeclaration").Cast <XmlNode>()
.Select(n => new KeyValuePair <string, string>(n.Attributes["Name"].Value, n.Attributes["Location"].Value))
.ToList()
.ForEach(n => varDict.Add(n.Key, n.Value));
Node.SelectNodes(".//SqlVariableDeclaration").Cast <XmlNode>()
.Select(n => new KeyValuePair <string, string>(n.Attributes["Name"].Value, n.Attributes["Location"].Value))
.ToList()
.ForEach(n => varDict.Add(n.Key, n.Value));
Node.SelectNodes(".//SqlParameterDeclaration").Cast <XmlNode>()
.Select(n => new KeyValuePair <string, string>(n.Attributes["Name"].Value, n.Attributes["Location"].Value))
.ToList()
.ForEach(n => varDict.Add(n.Key, n.Value));
//Remove used
Node.SelectNodes(".//SqlScalarVariableRefExpression").Cast <XmlNode>().ToList()
.ForEach(n =>
{
var varName = n.Attributes["VariableName"].Value;
var key = varDict.Keys.FirstOrDefault(k => k.ToLower() == varName.ToLower());
if (key != null)
{
varDict.Remove(key);
}
});
Node.SelectNodes(".//SqlTableVariableRefExpression").Cast <XmlNode>().ToList()
.ForEach(n =>
{
var varName = n.Attributes["Name"].Value;
var key = varDict.Keys.FirstOrDefault(k => k.ToLower() == varName.ToLower());
if (key != null)
{
varDict.Remove(key);
}
});
// Special case handle temporarily
// Can not handle such below statement
///INSERT INTO @productLevels EXEC [MyRptSharedGetSAPProductLevel] @salesorg = @salesorg,
Node.SelectNodes(".//SqlNullInsertSource").Cast <XmlNode>()
.ToList()
.ForEach(n =>
{
var tokens = ASTHelperMethod.GetTokensUnderNode(n);
tokens.ForEach(t =>
{
if (t.Attributes["type"].Value != "TOKEN_VARIABLE")
{
return;
}
var key = varDict.Keys.FirstOrDefault(k => k.ToLower() == t.InnerText.ToLower());
if (key != null)
{
varDict.Remove(key);
}
});
});
foreach (var kv in varDict)
{
WriteLog(string.Format("Parameter {0} is declared but never used.", kv.Key), 1, kv.Value);
}
}
