public void Test_can_parse_arbitrary_id_as_start_node(string id)
{
CmsqlParser cmsqlParser = CmsqlParserFactory.CreateParserForQuery($"from {id}");
CmsqlParser.FromClauseContext parseTree = cmsqlParser.fromClause();
FromClauseVisitor visitor = new FromClauseVisitor();
CmsqlQueryStartNode startNode = visitor.VisitFromClause(parseTree);
startNode.StartNodeId.ShouldBeEquivalentTo(id);
startNode.StartNodeType.ShouldBeEquivalentTo(CmsqlQueryStartNodeType.Id);
}
public void Test_can_parse_root_as_start_node()
{
CmsqlParser cmsqlParser = CmsqlParserFactory.CreateParserForQuery("from root");
CmsqlParser.FromClauseContext parseTree = cmsqlParser.fromClause();
FromClauseVisitor visitor = new FromClauseVisitor();
CmsqlQueryStartNode startNode = visitor.VisitFromClause(parseTree);
startNode.StartNodeId.Should().BeNullOrEmpty();
startNode.StartNodeType.ShouldBeEquivalentTo(CmsqlQueryStartNodeType.Root);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var objName = sqlObj.Name.GetName();
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
var fromClauseVisitor = new FromClauseVisitor();
var execVisitor = new ExecuteVisitor();
fragment.Accept(fromClauseVisitor, execVisitor);
var tableVisitor = new NamedTableReferenceVisitor()
{
TypeFilter = ObjectTypeFilter.PermanentOnly
};
foreach (var from in fromClauseVisitor.Statements)
{
from.Accept(tableVisitor);
}
var offenders = tableVisitor.Statements.Where(tbl =>
{
var id = tbl.GetObjectIdentifier(null);
return(id.Parts.Count < 2 || string.IsNullOrWhiteSpace(id.Parts.First()));
});
var execOffenders = execVisitor.Statements.Where(proc => CheckProc(proc));
problems.AddRange(offenders.Select(t => new SqlRuleProblem(Message, sqlObj, t)));
problems.AddRange(execOffenders.Select(t => new SqlRuleProblem(Message, sqlObj, t)));
return(problems);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement;
var db = ruleExecutionContext.SchemaModel;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
var fromClauseVisitor = new FromClauseVisitor();
fragment.Accept(fromClauseVisitor);
if (fromClauseVisitor.Count == 0)
{
return(problems);
}
//cache all the fks we find
var fkList = new Dictionary <string, ForeignKeyInfo>();
foreach (var from in fromClauseVisitor.Statements.Where(x => x.TableReferences.First() is QualifiedJoin))
{
var joinInfos = from.GetFromClauseJoinTables()
.Where(x => x.Table1JoinColumns.Count > 0 && (x.Table1JoinColumns.Count == x.Table2JoinColumns.Count)
).ToList();
if (!joinInfos.Any())
{
continue;
}
foreach (var join in joinInfos)
{
if (join.Table1 == null || join.Table2 == null)
{
continue;
}
//get the tables belonging to this join
var table1 = db.GetObject(Table.TypeClass, join.Table1Name, DacQueryScopes.All);
var table2 = db.GetObject(Table.TypeClass, join.Table2Name, DacQueryScopes.All);
//this can happen when one of the tables is a temp table, in that case we do not care to inspect the fks
if (table1 == null || table2 == null)
{
continue;
}
fkList.AddRange(table1.GetTableFKInfos());
//only get the fks for table2 if it is a diff table, otherwise it is a self join
if (table1.Name.CompareTo(table2.Name) < 5)
{
fkList.AddRange(table2.GetTableFKInfos());
}
//find any possible fks where the table names match exactly
var possibleFks = fkList.Where(f => join.CheckTableNames(f.Value)).ToList();
//we did not find any fks where the tables in the join matched the tables in the fk
if (!possibleFks.Any())
{
problems.Add(new SqlRuleProblem(MessageNoJoin, sqlObj, join.Table2));
continue;
}
//check to see if all of the columns match
if (!possibleFks.Any(f => join.CheckFullJoin(f.Value)))
{
problems.Add(new SqlRuleProblem(Message, sqlObj, join.Table2));
}
}
}
return(problems);
}
