public void Test_SemiColon_Inside_Inline_Comment()
{
//arrange
var traceService = new Mock <ITraceService>();
var sqlStatementRaw =
$@"--; inline comment
SELECT 1
SELECT 2;
SELECT 3;
";
//act
var sut = new SqlBatchParser(traceService.Object, new SemiColonSqlBatchLineAnalyzer(), new CommentAnalyzer());
var results = sut.Parse(sqlStatementRaw);
//assert
results.Count.ShouldBe(2);
results[0].BatchText.ShouldBe(
$@"--; inline comment
SELECT 1
SELECT 2;");
results[1].BatchText.ShouldBe(
$@"SELECT 3;");
}
public void Test_Go_Inside_Nested_Multiline_Block_Comment()
{
//arrange
var traceService = new Mock <ITraceService>();
var sqlStatementRaw =
$@"/* multiline comment block
/*
GO
*/
*/
SELECT 1;
SELECT 2;
GO
SELECT 3;
GO
";
//act
var sut = new SqlBatchParser(traceService.Object, new GoSqlBatchLineAnalyzer(), new CommentAnalyzer());
var results = sut.Parse(sqlStatementRaw);
//assert
results.Count.ShouldBe(2);
results.First().BatchText.ShouldBe(
$@"/* multiline comment block
/*
GO
*/
*/
SELECT 1;
SELECT 2;");
results[1].BatchText.ShouldBe(
$@"SELECT 3;");
}
static int Main(string[] args)
{
var app = new CommandLineApplication();
app.HelpOption();
var scriptFileName = app.Argument <string>("script", "SQL script file name")
.IsRequired();
var stripComments = app.Option("--strip", "Strip comments", CommandOptionType.NoValue);
var singleStatement = app.Option("-s|--single", "Output single statements", CommandOptionType.NoValue);
app.OnExecute(
() =>
{
// The default range searchers
var rangeSearchers = new List <IRangeSearcher>
{
new MultiLineComment(),
new DoubleDashSingleLineComment(),
new PoundSignSingleLineComment(),
new SqlString(),
};
// The special token searchers
var specialTokenSearchers = new List <ISpecialTokenSearcher>();
// Add SQL server specific range searcher
rangeSearchers.Add(new SqlServerIdentifier());
// Add SQL server specific token searcher
specialTokenSearchers.Add(new GoSearcher());
// We want every single SQL statement
_outputEverySqlStatement = singleStatement.HasValue();
if (_outputEverySqlStatement)
{
specialTokenSearchers.Add(new SemicolonSearcher());
}
var batchParser = new SqlBatchParser(rangeSearchers, specialTokenSearchers);
batchParser.SpecialToken += BatchParserOnSpecialToken;
batchParser.SqlText += BatchParserOnSqlText;
using (var source = new TextReaderSource(new StreamReader(scriptFileName.Value), true))
{
batchParser.Process(source, stripComments.HasValue());
}
RunSql();
return(0);
});
return(app.Execute(args));
}
public void create_a_list_with_an_item_for_each_batch_and_disabled_foreign_keys()
{
const string script = @"
SET IDENTITY_INSERT [dbo].[Table_1] ON
INSERT [dbo].[Table_1] (Id, Name) VALUES (1, 'a')
INSERT [dbo].[Table_1] (Id, Name) VALUES (2, 'b')
INSERT [dbo].[Table_1] (Id, Name) VALUES (3, 'c')
SET IDENTITY_INSERT [dbo].[Table_1] OFF
SET IDENTITY_INSERT [dbo].[Table_2] ON
INSERT [dbo].[Table_2] (Id, Name) VALUES (1, 'a')
INSERT [dbo].[Table_2] (Id, Name) VALUES (2, 'b')
INSERT [dbo].[Table_2] (Id, Name) VALUES (3, 'c')
SET IDENTITY_INSERT [dbo].[Table_2] OFF
";
using (var reader = CreateStreamReader(script))
{
var statements = SqlBatchParser.ParseScriptData(reader, batchSize: 2).ToList();
statements.Should().BeEquivalentTo(new ParsedStatementSet[]
{
new ParsedStatementSet(
new ParsedStatement[] {
new ParsedStatement("[dbo].[Table_1]", "ALTER TABLE [dbo].[Table_1] NOCHECK CONSTRAINT ALL;", ParsedStatementKind.NoCheckConstraint),
new ParsedStatement("[dbo].[Table_2]", "ALTER TABLE [dbo].[Table_2] NOCHECK CONSTRAINT ALL;", ParsedStatementKind.NoCheckConstraint),
}),
new ParsedInsertStatementSet(
new ParsedStatement[] {
new ParsedStatement("[dbo].[Table_1]", "SET IDENTITY_INSERT [dbo].[Table_1] ON;", ParsedStatementKind.IdentityInsertOn),
new ParsedStatement("[dbo].[Table_1]", "INSERT [dbo].[Table_1] (Id, Name) VALUES\n(1, 'a'),\n(2, 'b');", ParsedStatementKind.Insert, 2),
new ParsedStatement("[dbo].[Table_1]", "INSERT [dbo].[Table_1] (Id, Name) VALUES\n(3, 'c');", ParsedStatementKind.Insert, 1),
new ParsedStatement("[dbo].[Table_1]", "SET IDENTITY_INSERT [dbo].[Table_1] OFF;", ParsedStatementKind.IdentityInsertOff)
}),
new ParsedInsertStatementSet(
new ParsedStatement[] {
new ParsedStatement("[dbo].[Table_2]", "SET IDENTITY_INSERT [dbo].[Table_2] ON;", ParsedStatementKind.IdentityInsertOn),
new ParsedStatement("[dbo].[Table_2]", "INSERT [dbo].[Table_2] (Id, Name) VALUES\n(1, 'a'),\n(2, 'b');", ParsedStatementKind.Insert, 2),
new ParsedStatement("[dbo].[Table_2]", "INSERT [dbo].[Table_2] (Id, Name) VALUES\n(3, 'c');", ParsedStatementKind.Insert, 1),
new ParsedStatement("[dbo].[Table_2]", "SET IDENTITY_INSERT [dbo].[Table_2] OFF;", ParsedStatementKind.IdentityInsertOff),
}),
new ParsedStatementSet(
new ParsedStatement[] {
new ParsedStatement("[dbo].[Table_1]", "ALTER TABLE [dbo].[Table_1] WITH CHECK CHECK CONSTRAINT ALL;", ParsedStatementKind.CheckConstraint),
new ParsedStatement("[dbo].[Table_2]", "ALTER TABLE [dbo].[Table_2] WITH CHECK CHECK CONSTRAINT ALL;", ParsedStatementKind.CheckConstraint)
})
});
}
}
private IEnumerable <string> SplitIntoSingleStatements(string sql)
{
var sqlStatements = new List <string>();
// The default range searchers
var rangeSearchers = new List <IRangeSearcher>
{
new MultiLineComment(),
new DoubleDashSingleLineComment(),
new PoundSignSingleLineComment(),
new SqlString(),
new SqlServerIdentifier(),
};
// The special token searchers
var specialTokenSearchers = new List <ISpecialTokenSearcher>()
{
new GoSearcher(),
new SemicolonSearcher(),
};
var parser = new SqlBatchParser(rangeSearchers, specialTokenSearchers);
parser.SqlText += (sender, args) =>
{
var content = args.SqlText.Trim();
if (!string.IsNullOrEmpty(content))
{
sqlStatements.Add(content + ";");
}
};
using (var source = new TextReaderSource(new StringReader(sql), true))
{
parser.Process(source);
}
return(sqlStatements);
}
public void Test_SemiColon_Inside_Sql_Statement_Literal()
{
//arrange
var traceService = new Mock <ITraceService>();
var sqlStatementRaw =
$@"SELECT 1
SELECT 'This is ; inside valid sql statement';
SELECT 3;
";
//act
var sut = new SqlBatchParser(traceService.Object, new SemiColonSqlBatchLineAnalyzer(), new CommentAnalyzer());
var results = sut.Parse(sqlStatementRaw);
//assert
results.Count.ShouldBe(2);
results[0].BatchText.ShouldBe(
$@"SELECT 1
SELECT 'This is ; inside valid sql statement';");
results[1].BatchText.ShouldBe(
$@"SELECT 3;");
}
public void Test_Go_Without_Go_In_Last_Line()
{
//arrange
var traceService = new Mock <ITraceService>();
var sqlStatementRaw =
$@"SELECT 1;
SELECT 2;
GO
SELECT 3;
";
//act
var sut = new SqlBatchParser(traceService.Object, new GoSqlBatchLineAnalyzer(), new CommentAnalyzer());
var results = sut.Parse(sqlStatementRaw);
//assert
results.Count.ShouldBe(2);
results[0].BatchText.ShouldBe(
$@"SELECT 1;
SELECT 2;");
results[1].BatchText.ShouldBe(
$@"SELECT 3;");
}
public List <string> BreakStatements(string sqlStatementRaw)
{
var sqlBatchParser = new SqlBatchParser(_traceService, new GoSqlBatchLineAnalyzer(), new CommentAnalyzer());
return(sqlBatchParser.Parse(sqlStatementRaw).Select(s => s.BatchText).ToList());
}
