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