public void Can_Format_Muliple_Ctes()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("WITH A AS( SELECT Num =1), B AS (SELECT 1 AS P FROM X JOIN Y ON X.I = Y.I) SELECT * FROM A");
// Verify outcome
var expected = new[]
{
@"WITH A AS (",
"",
" SELECT Num = 1",
"),",
"B AS (",
"",
" SELECT 1 AS P",
"",
" FROM X",
"",
" JOIN Y",
" ON X.I = Y.I",
")",
"SELECT *",
"FROM A",
};
Compare(actual, expected);
}
public void Can_Format_Begin_End_Block_With_If_Statement()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( "IF @A > 1 BEGIN SELECT TOP 20 Field1, Field2 FROM dbo.Table T END" );
// Verify outcome
var expected = new[]
{
@"IF @A > 1",
@"BEGIN",
"",
" SELECT TOP 20",
" Field1,",
" Field2",
"",
" FROM dbo.Table T",
"",
"END"
};
Compare( actual, expected );
}
public void Can_Format_Muliple_Ctes()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("WITH A AS( SELECT Num =1), B AS (SELECT 1 AS P FROM X JOIN Y ON X.I = Y.I) SELECT * FROM A");
// Verify outcome
var expected = new[]
{
@"WITH A AS (",
"",
" SELECT Num = 1",
"),",
"B AS (",
"",
" SELECT 1 AS P",
"",
" FROM X",
"",
" JOIN Y",
" ON X.I = Y.I",
")",
"SELECT *",
"FROM A",
};
Compare(actual, expected);
}
public void Can_Format_Select_Statement_With_Nested_Select_In_From()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("SELECT A.ID, COUNT(*) FROM ( SELECT Name FROM Server.db.owner.Tables T JOIN OtherTable O ON O.ID = T.ID WHERE T.ID = 'ben' ) AS X ");
// Verify outcome
var expected = new[]
{
@"SELECT",
" A.ID,",
" COUNT(*)",
"",
"FROM (",
"",
" SELECT Name",
"",
" FROM Server.db.owner.Tables T",
"",
" JOIN OtherTable O",
" ON O.ID = T.ID",
"",
" WHERE T.ID = 'ben'",
"",
") AS X",
};
Compare(actual, expected);
}
public void Can_Format_Select_Statement_With_Joins()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(
@"SELECT * FROM dbo.Table T JOIN dbo.OtherJoin AS O ON O.ID = T.ID
AND O.Num = T.Num AND O.Field = 0 LEFT JOIN dbo.Joined J ON T.ID = J.ID AND J.Field = 1"
);
// Verify outcome
var expected = new[]
{
@"SELECT *",
"",
"FROM dbo.Table T",
"",
"JOIN dbo.OtherJoin AS O",
" ON O.ID = T.ID",
" AND O.Num = T.Num",
" AND O.Field = 0",
"",
"LEFT JOIN dbo.Joined J",
" ON T.ID = J.ID",
" AND J.Field = 1"
};
Compare(actual, expected);
}
public void Can_Format_Select_With_Nested_Criteria_Within_Nested_Criteria_On_Where_Clause()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(@"
SELECT * FROM dbo.States S JOIN dbo.Localities L
ON L.StateID = S.StateID AND (L.ID = S.ID OR (L.Key <> S.Key))"
);
// Verify outcome
var expected = new[]
{
@"SELECT *",
"",
"FROM dbo.States S",
"",
"JOIN dbo.Localities L",
" ON L.StateID = S.StateID",
" AND (",
"",
" L.ID = S.ID",
" OR",
" (L.Key <> S.Key)",
"",
")"
};
Compare(actual, expected);
}
public void Can_Format_Select_With_Sub_Select()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(@"
SELECT * FROM dbo.Events E WHERE Date=(SELECT MAX(Date)
FROM dbo.Events WHERE Date
> Now - 10 )"
);
// Verify outcome
var expected = new[]
{
@"SELECT *",
"FROM dbo.Events E",
"WHERE Date = (",
"",
" SELECT MAX(Date)",
" FROM dbo.Events",
" WHERE Date > Now - 10",
"",
")"
};
Compare(actual, expected);
}
private static async Task <int> RunAsync(CommandLineOptions options, CancellationToken cancellationToken)
{
var assemblies = OptionsHelper.DefaultCompositionAssemblies;
var engine = FormattingEngine.Create(assemblies);
var configBuilder = ImmutableArray.CreateBuilder <string[]>();
configBuilder.Add(options.PreprocessorConfigurations.ToArray());
engine.PreprocessorConfigurations = configBuilder.ToImmutableArray();
engine.FormattingOptionsFilePath = options.OptionsFilePath;
engine.Verbose = options.Verbose;
engine.AllowTables = options.DefineDotNetFormatter;
engine.FileNames = options.FileFilters.ToImmutableArray();
engine.CopyrightHeader = options.CopyrightHeaderText;
engine.ApplyFixes = options.ApplyFixes;
engine.LogOutputPath = options.LogOutputPath;
if (options.TargetAnalyzers != null && options.TargetAnalyzerText != null && options.TargetAnalyzerText.Count() > 0)
{
AddCustomAnalyzers(engine, options.TargetAnalyzerText);
}
// Analyzers will hydrate rule enabled/disabled settings
// directly from the options referenced by file path
// in options.OptionsFilePath
if (!options.UseAnalyzers)
{
if (!SetRuleMap(engine, options.RuleMap))
{
return(FAILED);
}
}
foreach (var item in options.Targets)
{
// target was a text file with a list of project files to run against
if (StringComparer.OrdinalIgnoreCase.Equals(Path.GetExtension(item), ".txt"))
{
var targets = File.ReadAllLines(item);
foreach (var target in targets)
{
try
{
await RunItemAsync(engine, target, options.Language, options.UseAnalyzers, options.UseTabs, cancellationToken);
}
catch (Exception e)
{
Console.WriteLine("Exception: {0} with project {1}", e.Message, target);
}
}
}
else
{
await RunItemAsync(engine, item, options.Language, options.UseAnalyzers, options.UseTabs, cancellationToken);
}
}
return(SUCCEEDED);
}
public void Can_Format_Select_Statement_With_Multiple_Table_Hints_On_Joined_Table()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(@"
SELECT TOP 20 Field1, Field2
FROM dbo.Table T JOIN dbo.Other O
WITH (NOLOCK,HOLDLOCK) ON O.Id = T.Id"
);
// Verify outcome
var expected = new[]
{
@"SELECT TOP 20",
" Field1,",
" Field2",
"",
"FROM dbo.Table T",
"",
"JOIN dbo.Other O WITH (NOLOCK, HOLDLOCK)",
" ON O.Id = T.Id"
};
Compare(actual, expected);
}
public void Can_Format_Create_Procedure_Statement_With_Block()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("CREATE PROCEDURE ben.MyProc AS BEGIN SELECT * FROM [Table] SELECT * FROM [Table] END");
// Verify outcome
var expected = new[]
{
"CREATE PROCEDURE ben.MyProc",
"AS",
"BEGIN",
"",
" SELECT *",
" FROM [Table]",
"",
" SELECT *",
" FROM [Table]",
"",
"END",
};
Compare(actual, expected);
}
private static async Task RunAsync(
string projectOrSolutionPath,
ImmutableArray <string> ruleTypes,
ImmutableArray <string> fileNames,
ImmutableArray <string[]> preprocessorConfigurations,
ImmutableArray <string> copyrightHeader,
CancellationToken cancellationToken)
{
var workspace = MSBuildWorkspace.Create();
var engine = FormattingEngine.Create(ruleTypes);
engine.PreprocessorConfigurations = preprocessorConfigurations;
engine.FileNames = fileNames;
engine.CopyrightHeader = copyrightHeader;
string extension = Path.GetExtension(projectOrSolutionPath);
if (StringComparer.OrdinalIgnoreCase.Equals(extension, ".sln"))
{
var solution = await workspace.OpenSolutionAsync(projectOrSolutionPath, cancellationToken);
await engine.FormatSolutionAsync(solution, cancellationToken);
}
else
{
workspace.LoadMetadataForReferencedProjects = true;
var project = await workspace.OpenProjectAsync(projectOrSolutionPath, cancellationToken);
await engine.FormatProjectAsync(project, cancellationToken);
}
}
public void Can_Format_Update_Statement_With_Joins()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(
@"UPDATE T SET Name = O.Name FROM dbo.Table T JOIN dbo.OtherJoin AS O ON O.ID = T.ID
AND O.Num = T.Num AND O.Field = 0 LEFT JOIN dbo.Joined J ON T.ID = J.ID AND J.Field = 1"
);
// Verify outcome
var expected = new[]
{
"UPDATE T",
" SET Name = O.Name",
"",
"FROM dbo.Table T",
"",
"JOIN dbo.OtherJoin AS O",
" ON O.ID = T.ID",
" AND O.Num = T.Num",
" AND O.Field = 0",
"",
"LEFT JOIN dbo.Joined J",
" ON T.ID = J.ID",
" AND J.Field = 1"
};
Compare( actual, expected );
}
public void Can_Format_Simple_Create_Statement()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( @"
create table Test
(
id1 [int] NOT NULL IDENTITY(100, 1),
id2 varchar(10)
)" );
// Verify outcome
var expected = new[]
{
"CREATE TABLE [dbo].[Clients]",
"(",
" [ClientID] [int] IDENTITY(1,1) NOT NULL,",
" [Name] [nvarchar](255) NOT NULL",
")"
};
Compare( actual, expected );
}
public void Can_Format_Simple_Create_Statement()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(@"
create table Test
(
id1 [int] NOT NULL IDENTITY(100, 1),
id2 varchar(10)
)");
// Verify outcome
var expected = new[]
{
"CREATE TABLE [dbo].[Clients]",
"(",
" [ClientID] [int] IDENTITY(1,1) NOT NULL,",
" [Name] [nvarchar](255) NOT NULL",
")"
};
Compare(actual, expected);
}
internal static PropertyBag BuildDefaultPropertyBag()
{
PropertyBag allOptions = new PropertyBag();
// The export command could be updated in the future to accept an arbitrary set
// of analyzers for which to build an options XML file suitable for configuring them.
// Currently, we perform discovery against the built-in CodeFormatter rules
// and analyzers only.
foreach (IOptionsProvider provider in FormattingEngine.GetOptionsProviders(DefaultCompositionAssemblies))
{
foreach (IOption option in provider.GetOptions())
{
allOptions.SetProperty(option, option.DefaultValue);
}
}
// TODO: this needs to be fixed. Instead of maintaining a hard-coded list of analyzers, that needs to
// be updated on adding a new check, we need to expose the analyzer name on an appropriate interface.
// Retrieving each of these bools populates the property bag with the default value of 'true'
bool enabled;
foreach (string analyzerName in AllAnalyzerNames)
{
enabled = allOptions.GetProperty(BuildDefaultEnabledProperty(analyzerName));
}
return(allOptions);
}
public void Can_Format_Insert_Statement_With_Column_Listing_And_Select_Values()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( @"
Insert into dbo.Table ( ID, Greeting, MaxiumumDaysBetweenEvents, Description, EffectiveFromDate,
EffectiveToDate ) SELECT ID, Greeting, MaxiumumDaysBetweenEvents * 2, Description, EffectiveFromDate,
EffectiveToDate FROM dbo.Events WHERE IsCancelled = 1 "
);
// Verify outcome
var expected = new[]
{
"INSERT INTO dbo.Table (",
" ID, Greeting, MaxiumumDaysBetweenEvents, Description, EffectiveFromDate, EffectiveToDate",
" )",
" SELECT",
" ID,",
" Greeting,",
" MaxiumumDaysBetweenEvents * 2,",
" Description,",
" EffectiveFromDate,",
" EffectiveToDate",
"",
" FROM dbo.Events",
"",
" WHERE IsCancelled = 1",
};
Compare( actual, expected );
}
public void Can_Format_Select_Statement_With_Group_By_Multiple_Columns_With_Having_Clause()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( "SELECT COUNT(*) FROM dbo.Table T GROUP BY A.FieldID, B.FieldID, C.FieldID HAVING COUNT(*) > 1" );
// Verify outcome
var expected = new[]
{
@"SELECT COUNT(*)",
"",
"FROM dbo.Table T",
"",
"GROUP BY",
" A.FieldID,",
" B.FieldID,",
" C.FieldID",
"",
"HAVING COUNT(*) > 1"
};
Compare( actual, expected );
}
public void Can_Format_Select_With_Case_When_With_Nested_Case_When()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(@"
SELECT CASE WHEN A >10 THEN 'Ten' WHEN A>1000
THEN CASE WHEN A> 10 THEN 'Ten' WHEN A>1000
THEN 'Thousand' ELSE 'Lots' END ELSE 'Lots' END"
);
// Verify outcome
var expected = new[]
{
"SELECT",
" CASE",
" WHEN A > 10 THEN 'Ten'",
" WHEN A > 1000 THEN ",
" CASE",
" WHEN A > 10 THEN 'Ten'",
" WHEN A > 1000 THEN 'Thousand'",
" ELSE",
" 'Lots'",
" END",
" ELSE",
" 'Lots'",
" END"
};
Compare(actual, expected);
}
public void Setup()
{
// Setup
FormattingEngine engine = new FormattingEngine();
_appender = new ParamBuilderFormatter(engine);
}
public void Can_Format_Select_With_Function_With_Long_Param_Signature()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(@"
SELECT dbo.SomeFunction(A.AReallyLongField,A.AReallyLongFieldAgain,
A.AReallyLongFieldOneMoreTime) FROM dbo.Ark A"
);
// Verify outcome
var expected = new[]
{
"SELECT",
" dbo.SomeFunction(",
" A.AReallyLongField,",
" A.AReallyLongFieldAgain,",
" A.AReallyLongFieldOneMoreTime",
" )",
"FROM dbo.Ark A"
};
Compare(actual, expected);
}
public void Can_Format_Begin_End_Block_With_If_Else_Statement()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("IF @A > 1 BEGIN SELECT TOP 20 Field1, Field2 FROM dbo.Table T END ELSE BEGIN SELECT @ID END");
// Verify outcome
var expected = new[]
{
@"IF @A > 1",
@"BEGIN",
"",
" SELECT TOP 20",
" Field1,",
" Field2",
"",
" FROM dbo.Table T",
"",
"END",
"ELSE",
"BEGIN",
"",
" SELECT @ID",
"",
"END"
};
Compare(actual, expected);
}
public void Can_Format_Select_With_Not_Exists_Function()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(
@"SELECT CASE WHEN NOT EXISTS( SELECT 1 FROM dbo.Notes WHERE A=1) THEN 1 ELSE 0 END FROM dbo.Ark A"
);
// Verify outcome
var expected = new[]
{
"SELECT",
" CASE",
" WHEN NOT EXISTS(",
"",
" SELECT 1",
" FROM dbo.Notes",
" WHERE A = 1",
"",
" ) THEN 1",
" ELSE",
" 0",
" END",
"FROM dbo.Ark A"
};
Compare(actual, expected);
}
public void Can_Format_Exec_Statement_With_Explicit_Arguments_That_Do_Require_Wrapping()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var sql = @"exec SomeFunc @Id =10,
@Name= 'Ben', @BirthDate = '2016-12-05',
@Ready = 1, @Raw = 'a093203jwjlsdflsldfkjhdkfsjdkfjshdkjfhskjdsdfoirDFsds23234534dfsd'";
var actual = sut.Execute(sql);
// Verify outcome
var expected = new[]
{
"EXEC SomeFunc",
" @Id = 10,",
" @Name = 'Ben',",
" @BirthDate = '2016-12-05',",
" @Ready = 1,",
" @Raw = 'a093203jwjlsdflsldfkjhdkfsjdkfjshdkjfhskjdsdfoirDFsds23234534dfsd'"
};
Compare(actual, expected);
}
public void Can_Format_Select_With_Only_One_Field_As_Case()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(@"
SELECT CASE A.field WHEN 1 THEN 'Y' WHEN @A + 2 THEN 'N' WHEN @A / 4 THEN 'X' ELSE 'U' END"
);
// Verify outcome
var expected = new[]
{
@"SELECT",
" CASE A.field",
" WHEN 1 THEN 'Y'",
" WHEN @A + 2 THEN 'N'",
" WHEN @A / 4 THEN 'X'",
" ELSE",
" 'U'",
" END"
};
Compare(actual, expected);
}
public void Can_Format_Select_With_Multiple_Froms_With_Joins()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("select id from table1 t1 join other1 o1 on t1.id = o1.id, table2 t2 join other2 o2 on t2.id = o2.id");
// Verify outcome
var expected = new[]
{
@"SELECT id",
"",
"FROM table1 t1",
"",
" JOIN other1 o1",
" ON t1.id = o1.id,",
"",
" table2 t2",
"",
" JOIN other2 o2",
" ON t2.id = o2.id",
};
Compare(actual, expected);
}
public void Can_Format_Select_With_Simple_Case()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(@"
SELECT A.Field1, CASE WHEN A.Field1 = 1 THEN 'Y' WHEN A.Field2 <> 2 THEN 'N' WHEN A.Field4 = 3 THEN 'M' ELSE 'U' END"
);
// Verify outcome
var expected = new[]
{
@"SELECT",
" A.Field1,",
" CASE",
" WHEN A.Field1 = 1 THEN 'Y'",
" WHEN A.Field2 <> 2 THEN 'N'",
" WHEN A.Field4 = 3 THEN 'M'",
" ELSE",
" 'U'",
" END"
};
Compare(actual, expected);
}
public void Can_Format_Select_With_Four_Nested_Criteria()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(@"
SELECT * FROM dbo.Events E WHERE ( E.ID IS NULL OR E.Type = 'X'
AND E.ID = 20 AND E.Type != 'Y'
AND E.OtherID = 40)"
);
// Verify outcome
var expected = new[]
{
@"SELECT *",
"FROM dbo.Events E",
"WHERE (",
"",
" E.ID IS NULL",
" OR",
" E.Type = 'X'",
" AND",
" E.ID = 20",
" AND",
" E.Type != 'Y'",
" AND",
" E.OtherID = 40",
"",
")"
};
Compare(actual, expected);
}
protected void btnConvert_Click(object sender, EventArgs e)
{
string output = "";
var engine = new FormattingEngine();
try
{
var timer = new System.Diagnostics.Stopwatch();
timer.Start();
try
{
output = engine.Execute(sqlInput.Text);
}
finally
{
timer.Stop();
}
timeTaken.Text = timer.ElapsedMilliseconds.ToString("0:00:0000");
}
catch (Exception ex)
{
output = "ERROR" + Environment.NewLine + ex.ToString();
}
sqlOutput.DataSource = output.Split(new[] { "\r\n" }, StringSplitOptions.None);
sqlOutput.DataBind();
}
public void Can_Format_Insert_Statement_With_Column_Listing_And_Select_Values()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(@"
Insert into dbo.Table ( ID, Greeting, MaxiumumDaysBetweenEvents, Description, EffectiveFromDate,
EffectiveToDate ) SELECT ID, Greeting, MaxiumumDaysBetweenEvents * 2, Description, EffectiveFromDate,
EffectiveToDate FROM dbo.Events WHERE IsCancelled = 1 "
);
// Verify outcome
var expected = new[]
{
"INSERT INTO dbo.Table (",
" ID, Greeting, MaxiumumDaysBetweenEvents, Description, EffectiveFromDate, EffectiveToDate",
" )",
" SELECT",
" ID,",
" Greeting,",
" MaxiumumDaysBetweenEvents * 2,",
" Description,",
" EffectiveFromDate,",
" EffectiveToDate",
"",
" FROM dbo.Events",
"",
" WHERE IsCancelled = 1",
};
Compare(actual, expected);
}
public void Can_Format_Exec_Statement_With_Explicit_Arguments_That_Do_Require_Wrapping()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var sql = @"exec SomeFunc @Id =10,
@Name= 'Ben', @BirthDate = '2016-12-05',
@Ready = 1, @Raw = 'a093203jwjlsdflsldfkjhdkfsjdkfjshdkjfhskjdsdfoirDFsds23234534dfsd'";
var actual = sut.Execute(sql);
// Verify outcome
var expected = new[]
{
"EXEC SomeFunc",
" @Id = 10,",
" @Name = 'Ben',",
" @BirthDate = '2016-12-05',",
" @Ready = 1,",
" @Raw = 'a093203jwjlsdflsldfkjhdkfsjdkfjshdkjfhskjdsdfoirDFsds23234534dfsd'"
};
Compare(actual, expected);
}
public void Can_Format_Select_Statement_With_Group_By_Multiple_Columns_With_Having_Clause()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("SELECT COUNT(*) FROM dbo.Table T GROUP BY A.FieldID, B.FieldID, C.FieldID HAVING COUNT(*) > 1");
// Verify outcome
var expected = new[]
{
@"SELECT COUNT(*)",
"",
"FROM dbo.Table T",
"",
"GROUP BY",
" A.FieldID,",
" B.FieldID,",
" C.FieldID",
"",
"HAVING COUNT(*) > 1"
};
Compare(actual, expected);
}
protected void btnConvert_Click( object sender, EventArgs e )
{
string output = "";
var engine = new FormattingEngine();
try
{
var timer = new System.Diagnostics.Stopwatch();
timer.Start();
try
{
output = engine.Execute( sqlInput.Text );
}
finally
{
timer.Stop();
}
timeTaken.Text = timer.ElapsedMilliseconds.ToString( "0:00:0000" );
}
catch ( Exception ex )
{
output = "ERROR" + Environment.NewLine + ex.ToString();
}
sqlOutput.DataSource = output.Split( new[] { "\r\n" }, StringSplitOptions.None );
sqlOutput.DataBind();
}
public CombinationTest()
{
_formattingEngine = (FormattingEngineImplementation)FormattingEngine.Create();
_formattingEngine.CopyrightHeader = ImmutableArray.Create("// header");
_formattingEngine.AllowTables = true;
_formattingEngine.FormatLogger = new EmptyFormatLogger();
_formattingEngine.PreprocessorConfigurations = ImmutableArray <string[]> .Empty;
}
public void Can_Create_Formatting_Engine()
{
// Setup
var sut = new FormattingEngine();
// Exercise
// Verify outcome
Assert.IsNotNull( sut );
}
public void Can_Create_Formatting_Engine()
{
// Setup
var sut = new FormattingEngine();
// Exercise
// Verify outcome
Assert.IsNotNull(sut);
}
private static async Task RunAsync(
string projectSolutionOrRspPath,
ImmutableArray <string> ruleTypes,
ImmutableArray <string> fileNames,
ImmutableArray <string[]> preprocessorConfigurations,
ImmutableArray <string> copyrightHeader,
string language,
bool allowTables,
bool convertUnicode,
bool verbose,
FormattingLevel formattingLevel,
CancellationToken cancellationToken)
{
var engine = FormattingEngine.Create(ruleTypes);
engine.PreprocessorConfigurations = preprocessorConfigurations;
engine.FileNames = fileNames;
engine.CopyrightHeader = copyrightHeader;
engine.AllowTables = allowTables;
engine.ConvertUnicodeCharacters = convertUnicode;
engine.Verbose = verbose;
engine.FormattingLevel = formattingLevel;
Console.WriteLine(Path.GetFileName(projectSolutionOrRspPath));
string extension = Path.GetExtension(projectSolutionOrRspPath);
if (StringComparer.OrdinalIgnoreCase.Equals(extension, ".rsp"))
{
using (var workspace = ResponseFileWorkspace.Create())
{
Project project = workspace.OpenCommandLineProject(projectSolutionOrRspPath, language);
await engine.FormatProjectAsync(project, cancellationToken);
}
}
else if (StringComparer.OrdinalIgnoreCase.Equals(extension, ".sln"))
{
using (var workspace = MSBuildWorkspace.Create())
{
workspace.LoadMetadataForReferencedProjects = true;
var solution = await workspace.OpenSolutionAsync(projectSolutionOrRspPath, cancellationToken);
await engine.FormatSolutionAsync(solution, cancellationToken);
}
}
else
{
using (var workspace = MSBuildWorkspace.Create())
{
workspace.LoadMetadataForReferencedProjects = true;
var project = await workspace.OpenProjectAsync(projectSolutionOrRspPath, cancellationToken);
await engine.FormatProjectAsync(project, cancellationToken);
}
}
}
private static void RunListRules()
{
var rules = FormattingEngine.GetFormattingRules();
Console.WriteLine("{0,-20} {1}", "Name", "Description");
Console.WriteLine("==============================================");
foreach (var rule in rules)
{
Console.WriteLine("{0,-20} :{1}", rule.Name, rule.Description);
}
}
private static void RunListRules()
{
var rules = FormattingEngine.GetFormattingRules();
Console.WriteLine("{0,-20} {1}", "Name", "Description");
Console.WriteLine("==============================================");
foreach (var rule in rules)
{
Console.WriteLine("{0,-20} :{1} {2}", rule.Name, rule.Description, !rule.IsDefaultEnabled ? "(disabled by default)" : string.Empty);
}
}
public void TestMethod()
{
// Arrange
var engine = new FormattingEngine();
var sut = new ParamBuilderFormatter(engine);
// Act
string result = sut.Execute(sample);
// Assert
Assert.AreEqual(expected, result);
}
public void Can_Format_Simple_Create_Index_Statement()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( @"CREATE UNIQUE NONCLUSTERED INDEX [IX_Sites_Code] ON dbo.Sites (Code) WITH (IGNORE_DUP_KEY = ON )" );
// Verify outcome
var expected = new[]
{
"CREATE UNIQUE NONCLUSTERED INDEX [IX_Sites_Code] ON dbo.Sites ( Code ) WITH ( IGNORE_DUP_KEY = ON )"
};
Compare( actual, expected );
}
public void Can_Format_Few_Column_Insert_Statement_With_Column_Listing()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( "Insert into dbo.Table (ID, Greeting) VALUES (1, 'Hello World')" );
// Verify outcome
var expected = new[]
{
"INSERT INTO dbo.Table (ID, Greeting)",
" VALUES (1, 'Hello World')"
};
Compare( actual, expected );
}
public void Can_Format_Delete_Statement_With_Top_N()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( "DELETE TOP (10) FROM dbo.Table" );
// Verify outcome
var expected = new[]
{
@"DELETE TOP (10)",
"FROM dbo.Table"
};
Compare( actual, expected );
}
public void Can_Format_Simple_Update_Statement()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( "UPDATE dbo.Table SET Field1 = 'Value'" );
// Verify outcome
var expected = new[]
{
@"UPDATE dbo.Table",
" SET Field1 = 'Value'"
};
Compare( actual, expected );
}
public void Can_Format_Exec_Statement_With_Implicit_Arguments_That_Dont_Require_Wrapping()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var sql = @"exec SomeFunc 10, 'Ben', '2016-12-05', 1";
var actual = sut.Execute(sql);
// Verify outcome
var expected = new[]
{
"EXEC SomeFunc 10, 'Ben', '2016-12-05', 1",
};
Compare(actual, expected);
}
public void Can_Format_Exec_Statement_Without_Arguments()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var sql = @"exec SomeFunc";
var actual = sut.Execute(sql);
// Verify outcome
var expected = new[]
{
"EXEC SomeFunc",
};
Compare(actual, expected);
}
public void Can_Format_Simple_Create_Index_Statement(string modificationType)
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(string.Format(@"{0} VIEW ben.MyView AS SELECT * FROM [Table]", modificationType));
// Verify outcome
var expected = new[]
{
modificationType.ToUpper() + " VIEW ben.MyView",
"AS",
"SELECT *",
"FROM [Table]"
};
Compare(actual, expected);
}
public void Can_Format_Select_With_Multiple_Froms_With_Joins()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var sql = @"exec sp_executesql
N'select TOP (@p0) T.Id, T.Name from [Transaction] T
where T.Type in (''Process'', ''TransferFrom'')
and T.Code in (@p1) and T.Name <> @p2',
N'@p0 int,@p1 int,@p2 nvarchar(4000)',
@p0=100,@p1=44,@p2=N'WOO'
";
var actual = sut.Execute(sql);
// Verify outcome
var expected = new[]
{
@"DECLARE",
" @p0 int,",
" @p1 int,",
" @p2 nvarchar(4000)",
"",
"SELECT",
" @p0 = 100,",
" @p1 = 44,",
" @p2 = N'WOO'",
"",
"SELECT TOP (@p0)",
" T.Id,",
" T.Name",
"",
"FROM [Transaction] T",
"",
"WHERE T.Type IN ('Process', 'TransferFrom')",
" AND T.Code IN (@p1)",
" AND T.Name <> @p2"
};
Compare(actual, expected);
}
public void Can_Format_Simple_Select_Statement()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( "SELECT TOP 20 Field1, Field2 FROM dbo.Table T" );
// Verify outcome
var expected = new[]
{
@"SELECT TOP 20",
" Field1,",
" Field2",
"",
"FROM dbo.Table T",
};
Compare( actual, expected );
}
public void Can_Format_Select_With_Between_Expression()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(
@"SELECT * FROM dbo.Table A WHERE A.Field BETWEEN 10 AND (SELECT TOP 1 ID FROM Keys )"
);
// Verify outcome
var expected = new[]
{
"SELECT *",
"FROM dbo.Table A",
"WHERE A.Field BETWEEN 10 AND (SELECT TOP 1 ID FROM Keys)",
};
Compare( actual, expected );
}
public void Can_Format_Simple_Cte()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("WITH A AS( SELECT Num =1) SELECT * FROM A");
// Verify outcome
var expected = new[]
{
@"WITH A AS (",
"",
" SELECT Num = 1",
")",
"SELECT *",
"FROM A",
};
Compare(actual, expected);
}
public void Can_Format_Delete_Statement_With_Multiple_Condition_Where_Clause()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( "Delete T FROM dbo.Table T WHERE T.TableID = 10 AND T.Data IS NULL" );
// Verify outcome
var expected = new[]
{
@"DELETE T",
"",
"FROM dbo.Table T",
"",
"WHERE T.TableID = 10",
" AND T.Data IS NULL"
};
Compare( actual, expected );
}
public void Can_Format_Begin_Transaction_With_Rollback()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( "BEGIN TRAN SELECT TOP 20 Field1, Field2 FROM dbo.Table T ROLLBACK" );
// Verify outcome
var expected = new[]
{
@"BEGIN TRAN",
"",
" SELECT TOP 20",
" Field1,",
" Field2",
"",
" FROM dbo.Table T",
"",
"ROLLBACK"
};
Compare( actual, expected );
}
public void Can_Format_Complex_Create_Index_Statement()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( @"CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address] ( [AddressLine1] ASC, [AddressLine2] DESC, [City] ASC, [StateProvinceID] DESC, [PostalCode] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" );
// Verify outcome
var expected = new[]
{
"CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address]",
"(",
" [AddressLine1],",
" [AddressLine2] DESC,",
" [City],",
" [StateProvinceID] DESC,",
" [PostalCode]",
")",
"WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]"
};
Compare( actual, expected );
}
public void Can_Format_Select_With_Case_When_With_Nested_Case_Switch()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( @"
SELECT CASE WHEN A.ThisField = 1 THEN 'One' WHEN A = 2 THEN CASE A.Field
WHEN 2 THEN 'Two' WHEN 3 THEN 'Three' END WHEN A > 3 THEN 'Many' END"
);
// Verify outcome
var expected = new[]
{
"SELECT",
" CASE",
" WHEN A.ThisField = 1 THEN 'One'",
" WHEN A = 2 THEN CASE A.Field WHEN 2 THEN 'Two' WHEN 3 THEN 'Three' END",
" WHEN A > 3 THEN 'Many'",
" END",
};
Compare( actual, expected );
}
public SqlFormatResult PostFormatted([FromBody]string query)
{
var output = String.Empty;
var engine = new FormattingEngine();
var timer = new System.Diagnostics.Stopwatch();
timer.Start();
try
{
output = engine.Execute(query);
return new SqlFormatResult
{
Sql = output.Split(new[] { "\r\n" }, StringSplitOptions.None),
Duration = timer.Elapsed
};
}
catch (ParserException ex)
{
output = ex.Message;
}
catch (Exception ex)
{
output = "ERROR" + Environment.NewLine + ex.ToString();
}
finally
{
timer.Stop();
}
return new SqlFormatResult
{
Sql = new[] { output },
Duration = timer.Elapsed
};
}
public void Setup()
{
// Setup
FormattingEngine engine = new FormattingEngine();
_appender = new ParamBuilderFormatter( engine );
}
public void Can_Format_Update_Statement_With_Multiple_Fields_With_Aligned_Assignments()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( "UPDATE dbo.Table SET Field = 'Value', SomeOtherLongField = 'Some Really Long Field'" );
// Verify outcome
var expected = new[]
{
"UPDATE dbo.Table",
" SET Field = 'Value',",
" SomeOtherLongField = 'Some Really Long Field'"
};
Compare( actual, expected );
}
public void Can_Format_Update_Statement_With_Where_Clause()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( "Update T SET T.Name = 'New' FROM dbo.Table T WHERE T.TableID = 10" );
// Verify outcome
var expected = new[]
{
@"UPDATE T",
" SET T.Name = 'New'",
"",
"FROM dbo.Table T",
"",
"WHERE T.TableID = 10"
};
Compare( actual, expected );
}
public void Can_Format_Simple_Select_Statement_With_Multiple_From_Tables()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( "SELECT Field1, Field2 FROM dbo.Table T1, dbo.Table T2, dbo.Table T3" );
// Verify outcome
var expected = new[]
{
@"SELECT",
" Field1,",
" Field2",
"",
"FROM dbo.Table T1,",
"",
" dbo.Table T2,",
"",
" dbo.Table T3",
};
Compare( actual, expected );
}
public void Can_Format_Select_With_Union()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( @"SELECT A.ID FROM table UNION SELECT A.ID FROM table" );
// Verify outcome
var expected = new[]
{
@"SELECT A.ID",
"FROM table",
"",
"UNION",
"",
"SELECT A.ID",
"FROM table",
};
Compare( actual, expected );
}
