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