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_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_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_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_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_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_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_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_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_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_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);
}
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_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);
}
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_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_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_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_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_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);
}
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_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_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 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);
}
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_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_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_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_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_And_Table_Alias()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("DELETE TOP (10) T FROM dbo.Table T");
// Verify outcome
var expected = new[]
{
@"DELETE TOP (10) T",
"FROM dbo.Table T"
};
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_Update_Statement_With_Top_N()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("UPDATE TOP (10) dbo.Table SET Field1 = 'Value'");
// Verify outcome
var expected = new[]
{
@"UPDATE TOP (10) dbo.Table",
" SET Field1 = 'Value'"
};
Compare(actual, expected);
}
public void Can_Format_More_Columns_Than_MaxOneLineColumnCount_But_Fitting_Up_To_WrapMarginColumn()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("INSERT INTO Product (Name, Price, ExpiryDate, Aisle, Shelf) VALUES ('Bread', 1.29, NULL, NULL, NULL)");
// Verify outcome
var expected = new[]
{
"INSERT INTO Product (Name, Price, ExpiryDate, Aisle, Shelf)",
" VALUES ('Bread', 1.29, NULL, NULL, NULL)",
};
Compare(actual, expected);
}
public void Can_Format_Simple_Delete_Statement()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("DELETE FROM dbo.Table");
// Verify outcome
var expected = new[]
{
@"DELETE",
"FROM dbo.Table"
};
Compare(actual, expected);
}
public void Can_Format_Select_With_Alias_Using_As()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute(@"SELECT A.ID AS SomeAlias FROM table ");
// Verify outcome
var expected = new[]
{
@"SELECT A.ID AS SomeAlias",
"FROM 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_Select_Statement_With_Order_By_One_Column()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("SELECT * FROM dbo.Table T ORDER BY A.FieldID");
// Verify outcome
var expected = new[]
{
@"SELECT *",
"FROM dbo.Table T",
"ORDER BY A.FieldID"
};
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 override void Execute()
{
var textDocument = AddIn.TextDocument;
if (textDocument.Selection.IsEmpty)
{
textDocument.Selection.SelectAll();
}
try
{
AddIn.InsertText(_engine.Execute(textDocument.Selection.Text + Environment.NewLine));
}
finally
{
textDocument.Selection.Cancel();
}
}
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_Select_Statement_With_Where_Clause()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute("SELECT * FROM dbo.Table T WHERE T.TableID = 10");
// Verify outcome
var expected = new[]
{
@"SELECT *",
"FROM dbo.Table T",
"WHERE T.TableID = 10"
};
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_Exec_Statement_With_Explicit_Arguments_That_Dont_Require_Wrapping()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var sql = @"exec SomeFunc @Id = 10,
@Name = 'Ben', @BirthDate = '2016-12-05',
@Ready = 1";
var actual = sut.Execute(sql);
// Verify outcome
var expected = new[]
{
"EXEC SomeFunc @Id = 10, @Name = 'Ben', @BirthDate = '2016-12-05', @Ready = 1",
};
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 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 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 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_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_Select_With_Two_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 )"
);
// Verify outcome
var expected = new[]
{
@"SELECT *",
"FROM dbo.Events E",
"WHERE (",
"",
" E.ID IS NULL",
" OR",
" E.Type = 'X'",
" AND",
" E.ID = 20",
"",
")"
};
Compare( actual, expected );
}
public void Can_Format_Select_With_Simple_Sub_Select()
{
// Setup
var sut = new FormattingEngine();
// Exercise
var actual = sut.Execute( @"
SELECT * FROM dbo.Events E WHERE Date=(SELECT TOP 1 Date FROM dbo.Events)"
);
// Verify outcome
var expected = new[]
{
@"SELECT *",
"FROM dbo.Events E",
"WHERE Date = (SELECT TOP 1 Date FROM dbo.Events)"
};
Compare( actual, expected );
}
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_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 );
}
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_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 );
}
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 );
}