public void Select_With_Two_Aliased_Table_With_As()
{
// Exercise
SelectStatement statement = ParserFactory.Execute <SelectStatement>("select * from table1 as t1, table2 as t2").First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual(2, statement.From.Count);
Assert.AreEqual("table1", statement.From[0].Name);
Assert.AreEqual("t1", statement.From[0].Alias.Name);
Assert.AreEqual("table2", statement.From[1].Name);
Assert.AreEqual("t2", statement.From[1].Alias.Name);
}
public void Select_Field_With_Alias_As_Identifier_With_Spaces()
{
// Exercise
SelectStatement statement = ParserFactory.Execute <SelectStatement>("select field as [an identifier with spaces] from table").First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual(1, statement.Fields.Count);
Assert.AreEqual("field", statement.Fields[0].Expression.Value);
Assert.AreEqual("[an identifier with spaces]", statement.Fields[0].Alias.Name);
Assert.AreEqual(AliasType.As, statement.Fields[0].Alias.Type);
}
public void Can_Execute_Stored_Proc_Without_Arguments()
{
var sql = @"exec dbo.SomeStoredProc";
// Exercise
var statement = ParserFactory.Execute <ExecStatement>(sql).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("dbo.SomeStoredProc", statement.FunctionName);
Assert.AreEqual(0, statement.Arguments.Count);
}
public void Select_Top_50_Percent_With_Brackets()
{
// Exercise
SelectStatement statement = ParserFactory.Execute <SelectStatement>("select top (50) percent * from table").First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual(1, statement.Fields.Count);
Assert.AreEqual("*", statement.Fields[0].Expression.Value);
Assert.AreEqual("(50)", statement.Top.Expression.Value);
Assert.IsTrue(statement.Top.Percent);
Assert.AreEqual("table", statement.From[0].Name);
}
public void Select_With_Group_By()
{
// Exercise
SelectStatement statement = ParserFactory.Execute <SelectStatement>(@"
select * from table group by field1, field2
").First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual(1, statement.From.Count);
Assert.AreEqual("table", statement.From[0].Name);
Assert.AreEqual(2, statement.GroupBy.Count);
}
public void Select_Field_With_Alias_As_String()
{
// Exercise
SelectStatement statement = ParserFactory.Execute <SelectStatement>("select field as 'a string' from table").First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual(1, statement.Fields.Count);
Assert.AreEqual("field", statement.Fields[0].Expression.Value);
Assert.AreEqual("'a string'", statement.Fields[0].Alias.Name);
Assert.AreEqual(AliasType.As, statement.Fields[0].Alias.Type);
}
public void Select_StarField_Only(string modificationType)
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>(string.Format("{0} view v1 as select * from table", modificationType)).First();
SelectStatement statement = (SelectStatement)sut.Definition;
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("v1", sut.Name);
Assert.AreEqual(1, statement.Fields.Count);
Assert.AreEqual("*", statement.Fields[0].Expression.Value);
Assert.IsNull(statement.Top);
Assert.AreEqual("table", statement.From[0].Name);
}
public void Create_View_With_Schema()
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>("create view dbo.v1 as select * from table").First();
SelectStatement statement = (SelectStatement)sut.Definition;
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("dbo.v1", sut.Name);
Assert.AreEqual(1, statement.Fields.Count);
Assert.AreEqual("*", statement.Fields[0].Expression.Value);
Assert.IsNull(statement.Top);
Assert.AreEqual("table", statement.From[0].Name);
}
public void Add_Foreign_Key()
{
// Exercise
var statement = ParserFactory.Execute <AlterTableStatement>(@"
alter table [dbo].[Test]
add constraint [fk_test] foreign key ([otherID]) references [dbo].[OtherTable] ([id])
"
).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("[dbo].[Test]", statement.TableName);
}
public void When_Join_Is_Missing_On_Clause_Throw_SyntaxError()
{
var sql = @"
select * from Table Outer
join (
select * from Table
) Inner --on inner.Value = Outer.Value
";
Assert.Catch <ExpectedTokenNotFoundException>(
() => ParserFactory.Execute <SelectStatement>(sql),
"Expected: 'ON' but found: 'EOF' at Row: 6, Col: 13"
);
}
public void Set_Variable_To_Complex_Expression()
{
// Setup
var sql = "SET @A = (@B + 5) / 2";
// Exercise
var statement = ParserFactory.Execute <SetVariableStatement>(sql).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("@A", statement.Variable);
Assert.AreEqual("(@B + 5) / 2", statement.Assignment.Value);
Assert.IsTrue(statement.Assignment is OperatorExpression);
}
public void Update_Statement_With_Top_N_Clause_With_Missing_Value()
{
// Exercise
try
{
UpdateStatement statement = ParserFactory.Execute <UpdateStatement>(
@"update top () t set field = 1 from dbo.table as t join dbo.other o on o.id = a.id where field <> 2"
).First();
}
catch (SyntaxException ex)
{
Assert.AreEqual("expected alpha, numeric, or variable, found )", ex.Message);
}
}
public void Select_Top_10_StarField()
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>("create view v1 as select top 10 * from table").First();;
SelectStatement statement = sut.SelectBlock;
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("v1", sut.Name);
Assert.AreEqual(1, statement.Fields.Count);
Assert.AreEqual("*", statement.Fields[0].Expression.Value);
Assert.AreEqual("10", statement.Top.Expression.Value);
Assert.AreEqual("table", statement.From[0].Name);
}
public void Add_Clustered_Primary_Key_With_No_Check()
{
// Exercise
var statement = ParserFactory.Execute <AlterTableStatement>(@"
alter table [dbo].[Test] with nocheck
add constraint [PK_Test] primary key clustered ( [id] )
"
).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("[dbo].[Test]", statement.TableName);
}
public void Select_Count_Distinct_Field()
{
// Exercise
SelectStatement statement = ParserFactory.Execute <SelectStatement>(
"select count(distinct Name) from table"
).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.IsInstanceOf <CountExpression>(statement.Fields.First().Expression);
Assert.IsTrue(((CountExpression)statement.Fields.First().Expression).Distinct);
Assert.AreEqual("COUNT(DISTINCT Name)", statement.Fields.First().Expression.Value);
Assert.AreEqual("table", statement.From.First().Name);
}
public void Select_Distinct_Top_10_StarField()
{
// Exercise
SelectStatement statement = ParserFactory.Execute <SelectStatement>("select distinct top 10 * from table").First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual(1, statement.Fields.Count);
Assert.AreEqual("*", statement.Fields[0].Expression.Value);
Assert.IsNotNull(statement.Top);
Assert.AreEqual("10", statement.Top.Expression.Value);
Assert.IsTrue(statement.Distinct);
Assert.AreEqual("table", statement.From[0].Name);
}
public void Add_Unique_Non_Clustered_Index()
{
// Exercise
var statement = ParserFactory.Execute <AlterTableStatement>(@"
ALTER TABLE [dbo].[Computers]
ADD CONSTRAINT [IX_Computers] UNIQUE NONCLUSTERED ([MachineName])
"
).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("[dbo].[Computers]", statement.TableName);
}
public void Declare_Statement_With_One_Variable()
{
// Setup
var sql = "DECLARE @Variable INT";
// Exercise
var statement = ParserFactory.Execute <DeclareStatement>(sql).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual(1, statement.Definitions.Count);
Assert.AreEqual("@Variable", statement.Definitions.First().Name);
Assert.AreEqual("INT", statement.Definitions.First().Type);
}
public void Select_Statement_With_Inline_Comment()
{
// Exercise
var statement = ParserFactory.Execute <SelectStatement>(
"select * -- all fields\n\r from dbo.table"
).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual(1, statement.Fields.Count);
Assert.AreEqual("*", statement.Fields[0].Expression.Value);
Assert.IsNull(statement.Top);
Assert.AreEqual("dbo.table", statement.From[0].Name);
}
public void Select_With_Order_By()
{
// Exercise
SelectStatement statement = ParserFactory.Execute <SelectStatement>(@"
select * from table order by field1 desc, field2 asc
").First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual(1, statement.From.Count);
Assert.AreEqual("table", statement.From[0].Name);
Assert.AreEqual(2, statement.OrderBy.Count);
Assert.AreEqual(Laan.Sql.Parser.Entities.SortOrder.Descending, (statement.OrderBy[0] as SortedField).SortOrder);
Assert.AreEqual(Laan.Sql.Parser.Entities.SortOrder.Ascending, (statement.OrderBy[1] as SortedField).SortOrder);
}
public void Can_Execute_Stored_Proc_With_One_Named_Argument()
{
var sql = @"exec dbo.SomeStoredProc @Arg = 15";
// Exercise
var statement = ParserFactory.Execute <ExecStatement>(sql).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("dbo.SomeStoredProc", statement.FunctionName);
Assert.AreEqual(1, statement.Arguments.Count);
Assert.AreEqual("15", statement.Arguments[0].Value);
Assert.AreEqual("@Arg", statement.Arguments[0].Name);
}
public void Declare_Statement_With_One_Variable_Without_Type_Should_Fail()
{
// Setup
var sql = "DECLARE @Variable";
try
{
// Exercise
ParserFactory.Execute <DeclareStatement>(sql);
}
catch (SyntaxException ex)
{
Assert.AreEqual("type missing for declaration of variable '@Variable'", ex.Message);
}
}
public void Select_With_Two_Aliased_Table_With_As()
{
// Exercise
CreateViewStatement sut = ParserFactory.Execute <CreateViewStatement>("create view v1 as select * from table1 as t1, table2 as t2").First();
SelectStatement statement = (SelectStatement)sut.Definition;
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("v1", sut.Name);
Assert.AreEqual(2, statement.From.Count);
Assert.AreEqual("table1", statement.From[0].Name);
Assert.AreEqual("t1", statement.From[0].Alias.Name);
Assert.AreEqual("table2", statement.From[1].Name);
Assert.AreEqual("t2", statement.From[1].Alias.Name);
}
public void Select_With_Multi_Part_Where_Condition_With_Several_Nested_Conditions()
{
// Exercise
SelectStatement statement = ParserFactory.Execute <SelectStatement>(@"
select fielda
from table1 t1
where ( t1.fieldd = 0 or t1.fieldc < 10 )
and ( ( select top 1 field from table1 ) = ( select top 1 fieldb from table2 ) )
"
).First();
// Verify outcome
Assert.IsNotNull(statement);
}
public void Primary_Key_As_Constraint()
{
// Setup
var statement = ParserFactory.Execute <CreateTableStatement>(@"
create table Test
(
id1 int not null,
id2 varchar(10),
id3 varchar not null,
id4 [decimal](10,2) not null,
constraint [PK_Name] primary key clustered ( [id1] ASC )
)"
).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("Test", statement.TableName);
var expected = new[]
{
new FieldDefinition()
{
Name = "id1", Type = new SqlType("int"), IsPrimaryKey = true, Nullability = Nullability.NotNullable
},
new FieldDefinition()
{
Name = "id2", Type = new SqlType("varchar")
{
Length = 10
}, IsPrimaryKey = false, Nullability = Nullability.Nullable
},
new FieldDefinition()
{
Name = "id3", Type = new SqlType("varchar"), IsPrimaryKey = false, Nullability = Nullability.NotNullable
},
new FieldDefinition()
{
Name = "id4", Type = new SqlType("decimal")
{
Length = 10, Scale = 2
}, IsPrimaryKey = false, Nullability = Nullability.NotNullable
},
};
Assert.AreEqual(expected, statement.Fields);
}
public void On_Off_Set_Options(string option)
{
foreach (bool value in new[] { false, true })
{
// Setup
var sql = String.Format("SET {0} {1}", option, value ? "ON" : "OFF");
// Exercise
var statement = ParserFactory.Execute <SetOptionStatement>(sql).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual(option, statement.Option);
Assert.AreEqual(value, statement.Assignment.Value == "ON");
}
}
public void Declare_Statement_With_One_Variable_With_National_Character_Set()
{
// Setup
var sql = "DECLARE @Variable NVARCHAR(MAX) = 'blah'";
// Exercise
var statement = ParserFactory.Execute <DeclareStatement>(sql).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual(1, statement.Definitions.Count);
VariableDefinition definition = statement.Definitions.First();
Assert.AreEqual("@Variable", definition.Name);
}
public void Update_With_Tablock()
{
// Arrange
const string sql = @"UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
";
// Act
var statement = Enumerable.First(ParserFactory.Execute <UpdateStatement>(sql));
// Assert
Assert.IsNotNull(statement);
Assert.IsNotEmpty(statement.TableHints);
Assert.IsTrue(statement.TableHints.Count(th => th.Hint == "TABLOCK") == 1);
}
public void Basic_Insert_Statement()
{
// Exercise
InsertStatement statement = ParserFactory.Execute <InsertStatement>("insert into table values (1, 'A')").First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("table", statement.TableName);
Assert.AreEqual(1, statement.Values.Count);
List <string> row = statement.Values[0];
Assert.AreEqual(2, row.Count);
Assert.AreEqual("1", row[0]);
Assert.AreEqual("'A'", row[1]);
}
public void Set_Variable_To_Select_Expression()
{
// Setup
var sql = "SET @A = (SELECT TOP 1 Name FROM dbo.Names)";
// Exercise
var statement = ParserFactory.Execute <SetVariableStatement>(sql).First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("@A", statement.Variable);
Assert.IsTrue(statement.Assignment is NestedExpression);
NestedExpression nestedExpression = (NestedExpression)statement.Assignment;
Assert.IsTrue(nestedExpression.Expression is SelectExpression);
}
