static void Main(string[] args)
{
if (args.Count() < 1)
{
Console.WriteLine("No input file specified");
return;
}
FileInfo input = new FileInfo(args[0]);
bool initialQuotedIdentifiers = false;
TSqlParser parser = new TSql110Parser(initialQuotedIdentifiers);
StreamReader sr = input.OpenText();
IList <ParseError> errors;
TSqlFragment fragment = parser.Parse(sr, out errors);
sr.Close();
if (errors.Count > 0)
{
Console.WriteLine("Parse {0} errors input stream", errors.Count);
return;
}
TableVisitor tableVisitor = new TableVisitor();
fragment.Accept(tableVisitor);
ViewVisitor viewVisitor = new ViewVisitor();
fragment.Accept(viewVisitor);
ProcVisitor procVisitor = new ProcVisitor();
fragment.Accept(procVisitor);
foreach (var table in tableVisitor.Nodes)
{
Console.WriteLine("table {0}.{1}",
table.SchemaObjectName.SchemaIdentifier.Value,
table.SchemaObjectName.BaseIdentifier.Value);
}
foreach (var view in viewVisitor.Nodes)
{
Console.WriteLine("view {0}.{1}",
view.SchemaObjectName.SchemaIdentifier.Value,
view.SchemaObjectName.BaseIdentifier.Value);
}
foreach (var proc in procVisitor.Nodes)
{
Console.WriteLine("proc {0}.{1}",
proc.ProcedureReference.Name.SchemaIdentifier.Value,
proc.ProcedureReference.Name.BaseIdentifier.Value);
}
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var visitor = new SetOptionsVisitor();
fragment.Accept(visitor);
return(this.CreateProblemsAsWarning(ruleDescriptor, elementName, modelElement, visitor.InvalidSetStatements));
}
private TableNameDeclareCheckVisitor visitTSql(string tsql)
{
TSqlParser parser = new TSql100Parser(false);
IList <ParseError> errors;
TextReader reader = new StringReader(tsql);
TSqlFragment parsed = parser.Parse(reader, out errors);
if (errors.Count != 0)
{
string errorMessage = "";
foreach (ParseError error in errors)
{
errorMessage = errorMessage + "\n" + error.Line + " : " + error.Message;
}
throw new Exception("パース失敗\n" + errorMessage);
}
TableNameDeclareCheckVisitor visitor = new TableNameDeclareCheckVisitor();
parsed.Accept(visitor);
return(visitor);
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var visitor = new AvoidUsingGlobalVariableForIdentityVisitor();
fragment.Accept(visitor);
return(this.CreateProblemsAsError(ruleDescriptor, elementName, modelElement, visitor.InvalidVariables));
}
public static void ParseSQL()
{
var sql = @"select AreaId = A.mcw_areaId, SurrogateKey = A.AreaKey, Code = S.statecode, Name = S.statename From CRM.dim_Area as A inner join CRM.dim_AreaState as S ON A.statecode = S.statecode ; DELET blogs SET url = 'aaa' where url='dasfds'";
//@"select p.firstname, p.lastname, p.custid FROM persons as p ;
//SELECT id, name FROM companies;
//select s.test from (select 'hello' as test) as s; ";
TSqlParser parser = new TSql120Parser(true);
IList <ParseError> parseErrors;
var tReader = new StringReader(sql);
TSqlFragment sqlFragment = parser.Parse(tReader, out parseErrors);
var queryTokens = parser.GetTokenStream(tReader, out parseErrors);
if (parseErrors.Count > 0)
{
Console.WriteLine("Errors:");
}
parseErrors.Select(e => e.Message.Indent(2)).ToList().ForEach(Console.WriteLine);
OwnVisitor visitor = new OwnVisitor();
sqlFragment.Accept(visitor);
// sqlFragment.AcceptChildren(visitor);
Console.WriteLine("Done.");
Console.ReadKey();
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var problems = new List <SqlRuleProblem>();
var visitor = new InvalidTypesVisitor();
fragment.Accept(visitor);
foreach (var item in visitor.ColumnDefinitions)
{
var description = string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription);
var problem = new SqlRuleProblem(description, modelElement, item)
{
Severity = SqlRuleProblemSeverity.Warning
};
problems.Add(problem);
}
foreach (var item in visitor.ProcedureParameters)
{
var description = string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription);
var problem = new SqlRuleProblem(description, modelElement, item)
{
Severity = SqlRuleProblemSeverity.Warning
};
problems.Add(problem);
}
return(problems);
}
public static CodeExpression BuildFromCheckConstraintDefinition(string schemaName, DataTable table, string check_name, string check_definition, out bool hasError, out string errorText)
{
string select_statement = "SELECT colx = IIF((" + check_definition + "), 1, 0) FROM " + schemaName + ".[" + table.TableName + "] WHERE " + check_definition;
TSqlFragment sqlF = ScriptDomFacade.Parse(select_statement);
IIFVisitor iif_visitor = new IIFVisitor();
sqlF.Accept(iif_visitor);
if (iif_visitor.result == null)
{
throw new NotSupportedException("Expression 'IIF' could not be found:" + sqlF.AsText());
}
BooleanExpression iif_predicate = iif_visitor.result.Predicate;
//if (iif_visitor != null)
//{
// //return null; // comment out if it needed
//}
//Console.WriteLine("");
//Console.WriteLine("==== " + check_name + " ============");
//Console.WriteLine(ScriptDomFacade.GenerateScript(iif_predicate));
//Console.WriteLine("-------------------------------------------");
//Console.WriteLine("");
var databaseMetadata = new DatabaseMetadataOnTable(table);
hasError = false;
errorText = null;
return(BooleanExpressionGeneratorVisitor.TryBuildFromFragment(databaseMetadata, iif_predicate, ref hasError, ref errorText));
}
public string PrepareScript(string script, Dictionary <string, object> parameters, out IList <ParseError> errors)
{
if (MetadataService.CurrentDatabase == null)
{
throw new InvalidOperationException("Current database is not defined!");
}
TSqlFragment fragment = Parser.Parse(new StringReader(script), out errors);
if (errors.Count > 0)
{
return(script);
}
DeclareVariableStatementVisitor parametersVisitor = new DeclareVariableStatementVisitor(parameters);
fragment.Accept(parametersVisitor);
ScriptNode result = new ScriptNode()
{
Database = MetadataService.CurrentDatabase
};
SyntaxTreeVisitor visitor = new SyntaxTreeVisitor(MetadataService);
visitor.Visit(fragment, result);
Generator.GenerateScript(fragment, out string sql);
return(sql);
}
public static IList <SelectStatement> VisitSelectStatements(TSqlFragment fragment)
{
TSqlSelectVisitor visitor = new TSqlSelectVisitor();
fragment.Accept(visitor);
return(visitor.SelectStatements);
}
public static IList <ProcedureParameter> VisitProcedureParameters(TSqlFragment fragment)
{
TSqlSelectVisitor visitor = new TSqlSelectVisitor();
fragment.Accept(visitor);
return(visitor.ProcedureParameters);
}
private void button1_Click(object sender, EventArgs e)
{
TextReader rdr = new StringReader(textBox1.Text);
IList <ParseError> errors = null;
TSql110Parser parser = new TSql110Parser(true);
TSqlFragment tree = parser.Parse(rdr, out errors);
foreach (ParseError err in errors)
{
Console.WriteLine(err.Message);
}
MyVisitor checker = new MyVisitor();
tree.Accept(checker);
if (false == checker.containsOnlySelects)
{
MessageBox.Show("The code contains something other than SELECT statements!");
}
else
{
MessageBox.Show("Looks ok!");
}
rdr.Dispose();
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var visitor = new UsingFullTextIndexVisitor();
fragment.Accept(visitor);
return(this.CreateProblemsAsWarning(ruleDescriptor, elementName, modelElement, visitor.FullTextIndexes));
}
private void ParseDelete(string query)
{
TSqlFragment tree = InitiateTSql110Parser(query);
if (SyntaxError)
{
return;
}
if (tree is TSqlScript)
{
TSqlScript tSqlScript = tree as TSqlScript;
foreach (TSqlBatch sqlBatch in tSqlScript.Batches)
{
foreach (TSqlStatement sqlStatement in sqlBatch.Statements)
{
ParseSqlDeleteStatement(sqlStatement);
}
}
}
RbacTSqlFragmentVisitor rbacTSqlFragmentVisitor = new RbacTSqlFragmentVisitor();
tree.Accept(rbacTSqlFragmentVisitor);
this.Warnings.AddRange(rbacTSqlFragmentVisitor.Warnings);
TablesReferred = new List <RbacTable>(TablesReferred.DistinctBy(t => t.Name));
IsParsed = true;
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var visitor = new ColumnNameVisitor();
fragment.Accept(visitor);
return(this.CreateProblemsAsError(ruleDescriptor, elementName, modelElement, visitor.InvalidInsertStatementsWithoutColumnNames));
}
public static void Accept(this TSqlFragment fragment, params TSqlFragmentVisitor[] visitors)
{
foreach (var visitor in visitors)
{
fragment.Accept(visitor);
}
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var problems = new List <SqlRuleProblem>();
var visitor = new DataTypesVisitor();
fragment.Accept(visitor);
foreach (var columnDef in visitor.ColumnDefinitions)
{
var description = string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription);
var problem = new SqlRuleProblem(description, modelElement)
{
Severity = SqlRuleProblemSeverity.Error
};
problem.SetSourceInformation(new SourceInformation(problem.SourceName, columnDef.StartLine, columnDef.StartColumn));
problems.Add(problem);
}
foreach (var parameter in visitor.ProcedureParameters)
{
var description = string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription);
var problem = new SqlRuleProblem(description, modelElement)
{
Severity = SqlRuleProblemSeverity.Error
};
problem.SetSourceInformation(new SourceInformation(problem.SourceName, parameter.StartLine, parameter.StartColumn));
problems.Add(problem);
}
return(problems);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlObject modelElement = ruleExecutionContext.ModelElement;
if (IsInlineTableValuedFunction(modelElement))
{
return(problems);
}
string elementName = GetElementName(ruleExecutionContext, modelElement);
TSqlFragment fragment = ruleExecutionContext.ScriptFragment;
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
WaitForDelayVisitor visitor = new WaitForDelayVisitor();
fragment.Accept(visitor);
IList <WaitForStatement> waitForDelayStatements = visitor.WaitForDelayStatements;
foreach (WaitForStatement waitForStatement in waitForDelayStatements)
{
var problem = new SqlRuleProblem(string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, elementName), modelElement, waitForStatement);
problems.Add(problem);
}
return(problems);
}
private void AnalyzeQueries(IList <SqlRuleProblem> problems, TSqlFragment fragment,
SqlRuleExecutionContext ruleExecutionContext)
{
// Find all queries in current checking fragment.
var visitor = new GetQuerySpecificationsVisitor();
fragment.Accept(visitor);
foreach (var querySpecification in visitor.Queries)
{
// Get the specified tables in the query.
var unspecifiedPartitions = CollectFromClause(ruleExecutionContext, querySpecification);
// If the query isn't querying a partition table, we don't need to check it.
if (unspecifiedPartitions.Count > 0)
{
// Get all constraints in the query.
if (querySpecification.WhereClause != null)
{
SearchConstraints(querySpecification.WhereClause, unspecifiedPartitions);
}
}
// If there are any unspecified partitions left, these are considered problems.
foreach (var unspecifiedPartition in unspecifiedPartitions)
{
SqlRuleProblem problem = CreateProblem(ruleExecutionContext, unspecifiedPartition);
problems.Add(problem);
}
AnalyzeJoins(problems, ruleExecutionContext, querySpecification);
}
}
private void ChangeNewDatabaseLocation(DeploymentPlanContributorContext context, string databasePath,
string logPath)
{
DeploymentStep nextStep = context.PlanHandle.Head;
// Loop through all steps in the deployment plan
bool finished = false;
while (nextStep != null && !finished)
{
// Increment the step pointer, saving both the current and next steps
DeploymentStep currentStep = nextStep;
// Only interrogate up to BeginPreDeploymentScriptStep - setvars must be done before that
if (currentStep is BeginPreDeploymentScriptStep)
{
finished = true;
break;
}
SqlCreateDatabaseStep createDbStep = currentStep as SqlCreateDatabaseStep;
if (createDbStep != null)
{
TSqlFragment fragment = createDbStep.Script;
CreateDatabaseStatementVisitor visitor = new CreateDatabaseStatementVisitor(databasePath, logPath);
fragment.Accept(visitor);
finished = true;
}
nextStep = currentStep.Next;
}
}
public static IList <AlterTableAddTableElementStatement> VisitAlterTableAddTableElementStatements(TSqlFragment fragment)
{
TSqlSelectVisitor visitor = new TSqlSelectVisitor();
fragment.Accept(visitor);
return(visitor.AlterTableAddTableElementStatements);
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var visitor = new HintsVisitor();
fragment.Accept(visitor);
return(this.CreateProblemsAsError(ruleDescriptor, elementName, modelElement, visitor.Hints));
}
public static IList <CreateProcedureStatement> VisitCreateProcedureStatements(TSqlFragment fragment)
{
TSqlSelectVisitor visitor = new TSqlSelectVisitor();
fragment.Accept(visitor);
return(visitor.CreateProcedureStatements);
}
static void Main(string[] args)
{
TextReader txtRdr = new StringReader(@"select AreaId = A.mcw_areaId, SurrogateKey = A.AreaKey, Code = S.statecode, Name = S.statename From CRM.dim_Area as A inner join CRM.dim_AreaState as S ON A.statecode = S.statecode ; DELET FROM blogs where url='dasfds'");
//TextReader txtRdr = new StreamReader("myscriptfile.sql");
TSql120Parser parser = new TSql120Parser(true);
IList <ParseError> errors;
TSqlFragment sqlFragment = parser.Parse(txtRdr, out errors);
foreach (var err in errors)
{
Console.WriteLine(err.Message);
}
SQLVisitor myVisitor = new SQLVisitor();
sqlFragment.Accept(myVisitor);
myVisitor.DumpStatistics();
ParseSQL();
Console.ReadKey();
}
public static SqlServerRawTree CreateRawTree(TSqlFragment fragment)
{
var visitor = new SqlServerRawTreeVisitor();
fragment.Accept(visitor);
return(visitor.Root ?? throw new InvalidOperationException());
}
private void GetDatabasesUpdated(TSqlFragment node)
{
if (IsWithinTransaction(node))
{
node.Accept(childDatabaseNameVisitor);
DatabasesUpdated.UnionWith(childDatabaseNameVisitor.DatabasesUpdated);
}
}
private void ChangeCreateIndexOperationalProps(DeploymentPlanContributorContext context,
IList <IndexOption> options)
{
DeploymentStep nextStep = context.PlanHandle.Head;
// Loop through all steps in the deployment plan
bool foundMainSection = false;
while (nextStep != null)
{
DeploymentStep currentStep = nextStep;
nextStep = currentStep.Next;
// We only want to analyze the main part of the deployment script - we'll skip
// any steps until we pass the end of the predeployment section, and stop once
// we hit the start of the postdeployment section
if (currentStep is EndPreDeploymentScriptStep)
{
foundMainSection = true;
continue;
}
if (!foundMainSection)
{
// Haven't gotten past predeployment yet
continue;
}
if (currentStep is BeginPostDeploymentScriptStep)
{
break;
}
// We need to care about CreateElementSteps and AlterElementSteps for Indexes.
DeploymentScriptDomStep domStep = currentStep as DeploymentScriptDomStep;
TSqlObject elementObject = null;
if (domStep is CreateElementStep)
{
elementObject = ((CreateElementStep)domStep).SourceElement;
}
else if (domStep is AlterElementStep)
{
elementObject = ((AlterElementStep)domStep).SourceElement;
}
if (elementObject != null)
{
if (Index.TypeClass.Equals(elementObject.ObjectType) && !(View.TypeClass.Equals(elementObject.GetParent().ObjectType)))
{
TSqlFragment fragment = domStep.Script;
IndexStatementVisitor visitor = new IndexStatementVisitor(options);
fragment.Accept(visitor);
}
}
}
}
private void FindAndRenameUnnamedDefaultConstraints(DeploymentPlanContributorContext context)
{
DeploymentStep nextStep = context.PlanHandle.Head;
// Loop through all steps in the deployment plan
bool foundMainSection = false;
while (nextStep != null)
{
DeploymentStep currentStep = nextStep;
nextStep = currentStep.Next;
// We only want to analyze the main part of the deployment script - we'll skip
// any steps until we pass the end of the predeployment section, and stop once
// we hit the start of the postdeployment section
if (currentStep is EndPreDeploymentScriptStep)
{
foundMainSection = true;
continue;
}
if (!foundMainSection)
{
// Haven't gotten past predeployment yet
continue;
}
if (currentStep is BeginPostDeploymentScriptStep)
{
break;
}
// We need to care about CreateElementSteps and AlterElementSteps for default constraints.
DeploymentScriptDomStep domStep = currentStep as DeploymentScriptDomStep;
TSqlObject elementObject = null;
// most of the default constraints in the deployment plan seem to be deployed as Alter Table statements, but
// just in case the default constraint was deployed as part of a Create Table (don't see how it is possible, but just being safe)
if (domStep is CreateElementStep)
{
elementObject = ((CreateElementStep)domStep).SourceElement;
}
else if (domStep is AlterElementStep)
{
elementObject = ((AlterElementStep)domStep).SourceElement;
}
if (elementObject != null)
{
TSqlFragment fragment = domStep.Script;
// call the visitor, which in turn will auto-name these constraints
var visitor = new DefaultConstraintDefinitionVisitor();
fragment.Accept(visitor);
}
}
}
public static ObjectIdentifier GetIdentifierFromSqlProcedure(TSqlFragment fragment)
{
var identifier = new ObjectIdentifier();
var symbolVisitor = new ProcedureObjectNameVisitor(identifier);
fragment.Accept(symbolVisitor);
return(identifier);
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var visitor = new UsingIndexedViewsVisitor();
fragment.Accept(visitor);
var problems = (List <SqlRuleProblem>) this.CreateProblemsAsWarning(ruleDescriptor, elementName, modelElement, visitor.WithBindings);
problems.AddRange(this.CreateProblemsAsWarning(ruleDescriptor, elementName, modelElement, visitor.IndexedViews));
return(problems);
}
public static string ResolveAlias(TSqlFragment fragment, string alias)
{
var visitor = new AliasVisitor();
fragment.Accept(visitor);
return(visitor.Aliases.ContainsKey(alias)
? visitor.Aliases[alias]
: alias);
}
public static string ResolveAlias(TSqlFragment fragment, string alias)
{
var visitor = new AliasVisitor();
fragment.Accept(visitor);
return visitor.Aliases.ContainsKey(alias)
? visitor.Aliases[alias]
: alias;
}
