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