コード例 #1
0
        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);
        }
コード例 #2
0
        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 );
        }
コード例 #3
0
        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);
        }
コード例 #4
0
        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);
        }
コード例 #5
0
        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);
        }
コード例 #6
0
        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);
        }
コード例 #7
0
        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);
        }
コード例 #8
0
        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);
        }
コード例 #9
0
        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);
        }
コード例 #10
0
        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);
        }
コード例 #11
0
ファイル: Program.cs プロジェクト: n8ohu/codeformatter
        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);
            }
        }
コード例 #12
0
        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 );
        }
コード例 #13
0
        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 );
        }
コード例 #14
0
        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);
        }
コード例 #15
0
        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);
        }
コード例 #16
0
        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 );
        }
コード例 #17
0
        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 );
        }
コード例 #18
0
        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);
        }
コード例 #19
0
ファイル: TestAppender.cs プロジェクト: xinqinglhj/sqlformat
        public void Setup()
        {
            // Setup
            FormattingEngine engine = new FormattingEngine();

            _appender = new ParamBuilderFormatter(engine);
        }
コード例 #20
0
        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);
        }
コード例 #21
0
        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);
        }
コード例 #22
0
        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);
        }
コード例 #23
0
        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);
        }
コード例 #24
0
        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);
        }
コード例 #25
0
        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);
        }
コード例 #26
0
        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);
        }
コード例 #27
0
        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);
        }
コード例 #28
0
        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();
        }
コード例 #29
0
        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);
        }
コード例 #30
0
        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);
        }
コード例 #31
0
        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);
        }
コード例 #32
0
ファイル: Default.aspx.cs プロジェクト: Bigjono/sqlformat
        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();
        }
コード例 #33
0
 public CombinationTest()
 {
     _formattingEngine = (FormattingEngineImplementation)FormattingEngine.Create();
     _formattingEngine.CopyrightHeader            = ImmutableArray.Create("// header");
     _formattingEngine.AllowTables                = true;
     _formattingEngine.FormatLogger               = new EmptyFormatLogger();
     _formattingEngine.PreprocessorConfigurations = ImmutableArray <string[]> .Empty;
 }
コード例 #34
0
        public void Can_Create_Formatting_Engine()
        {
            // Setup
            var sut = new FormattingEngine();

            // Exercise

            // Verify outcome
            Assert.IsNotNull( sut );
        }
コード例 #35
0
        public void Can_Create_Formatting_Engine()
        {
            // Setup
            var sut = new FormattingEngine();

            // Exercise

            // Verify outcome
            Assert.IsNotNull(sut);
        }
コード例 #36
0
        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);
                }
            }
        }
コード例 #37
0
        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);
            }
        }
コード例 #38
0
ファイル: Program.cs プロジェクト: xamarin/codeformatter
        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);
            }
        }
コード例 #39
0
        public void TestMethod()
        {
            // Arrange
            var engine = new FormattingEngine();
            var sut    = new ParamBuilderFormatter(engine);

            // Act
            string result = sut.Execute(sample);


            // Assert
            Assert.AreEqual(expected, result);
        }
コード例 #40
0
        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 );
        }
コード例 #41
0
        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 );
        }
コード例 #42
0
        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 );
        }
コード例 #43
0
        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 );
        }
コード例 #44
0
        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);
        }
コード例 #45
0
        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);
        }
コード例 #46
0
        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);
        }
コード例 #47
0
        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);
        }
コード例 #48
0
        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 );
        }
コード例 #49
0
        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 );
        }
コード例 #50
0
        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);
        }
コード例 #51
0
        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 );
        }
コード例 #52
0
        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 );
        }
コード例 #53
0
        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 );
        }
コード例 #54
0
        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 );
        }
コード例 #55
0
ファイル: SqlController.cs プロジェクト: benlaan/sqlformat
        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
            };
        }
コード例 #56
0
ファイル: TestAppender.cs プロジェクト: Bigjono/sqlformat
 public void Setup()
 {
     // Setup
     FormattingEngine engine = new FormattingEngine();
     _appender = new ParamBuilderFormatter( engine );
 }
コード例 #57
0
        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 );
        }
コード例 #58
0
        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 );
        }
コード例 #59
0
        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 );
        }
コード例 #60
0
        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 );
        }