public void When_inserting_from_select_Then_builds_properly() { var select = SqlStatements.Select("Name", "EmailAddress") .From("Users") .InnerJoin("Teams", "Users.TeamID = Teams.ID") .Where("Teams.IsOld = FALSE"); var statement = new InsertStatement(select) .Into("Users"); var sql = statement.ToSql(); Assert.That( sql, SqlCompareConstraint.EqualTo(@"INSERT INTO Users ( Name, EmailAddress ) SELECT Name, EmailAddress FROM Users INNER JOIN Teams ON Users.TeamID = Teams.ID WHERE Teams.IsOld = FALSE")); }
public void When_inserting_rows_Then_builds_properly() { var statement = SqlStatements.Insert().Columns(new[] { "Name", "EmailAddress" }) .Into("Users") .Rows(3); var sql = statement.ToSql(); Assert.That( sql, SqlCompareConstraint.EqualTo(@"INSERT INTO Users ( Name, EmailAddress ) VALUES ( @Name0, @EmailAddress0 ), ( @Name1, @EmailAddress1 ), ( @Name2, @EmailAddress2 )")); }
public void When_converting_implicitely_Then_works() { FromClause clause = "Teams t"; Assert.That(clause, Is.InstanceOf <TableClause>()); Assert.That(clause.ToSql(), SqlCompareConstraint.EqualTo("Teams t")); }
public void When_cloning_with_all_properties_Then_copies_stuff() { var statement = SqlStatements.Select("u.ID, u.Name, u.EmailAddress") .From("Users u") .Where("u.IsCool IS NULL") .GroupBy("u.ID") .OrderBy("u.Name") .Limit(1).Offset(2); const string resultStatement = @"SELECT u.ID, u.Name, u.EmailAddress FROM Users u WHERE u.IsCool IS NULL GROUP BY u.ID ORDER BY u.Name ASC LIMIT 1 OFFSET 2"; Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(resultStatement)); var clone = statement.Clone(); Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(resultStatement)); Assert.That( clone.ToSql(), SqlCompareConstraint.EqualTo(resultStatement)); }
public void When_selecting_with_no_table_by_Then_doesnt_output_from_clause() { var statement = SqlStatements.Select("(SELECT COUNT(*) FROM Users) AS UserCount"); Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT (SELECT COUNT(*) FROM Users) AS UserCount")); }
public void When_building_Then_builds_properly() { var join = new OuterJoin(new TableClause("Lol l"), new TableClause("Wat w"), "l.WatID = w.ID"); Assert.That( join.ToSql(), SqlCompareConstraint.EqualTo(@"Lol l OUTER JOIN Wat w ON l.WatID = w.ID")); }
public void When_adding_alias_Then_is_in_output() { FromClause clause = "Teams"; clause.As("t"); Assert.That(clause, Is.InstanceOf <TableClause>()); Assert.That(clause.ToSql(), SqlCompareConstraint.EqualTo("Teams t")); }
public void When_building_nested_Then_builds_properly() { var join = new OuterJoin(new TableClause("Lol l"), new TableClause("Wat w"), "l.WatID = w.ID"); var innerJoin = new InnerJoin(join, new TableClause("Derp d"), "l.DerpID = d.ID"); Assert.That( innerJoin.ToSql(), SqlCompareConstraint.EqualTo(@"Lol l OUTER JOIN Wat w ON l.WatID = w.ID INNER JOIN Derp d ON l.DerpID = d.ID")); }
public void When_selecting_with_full_join_Then_works() { var statement = SqlStatements.Select("u.ID, t.ID") .From("Users u") .FullJoin(new TableClause("Teams t"), "u.TeamID = t.ID"); Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT u.ID, t.ID FROM Users u FULL JOIN Teams t ON u.TeamID = t.ID")); }
public void When_selecting_with_group_by_Then_works() { var statement = SqlStatements.Select("u.ID, COUNT(*)") .From("Users u") .GroupBy("u.ID"); Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT u.ID, COUNT(*) FROM Users u GROUP BY u.ID")); }
public void When_deleting_Then_builds_properly() { var statement = SqlStatements.Delete() .From("Users") .Where("u.IsCool = TRUE") .Where("u.Name LIKE @Query"); var sql = statement.ToSql(); Assert.That( sql, SqlCompareConstraint.EqualTo(@"DELETE Users WHERE u.IsCool = TRUE AND u.Name LIKE @Query")); }
public void When_selecting_from_other_select_Then_outputs_properly() { var temp = SqlStatements.Select("u.ID").From("Users u").As("Sub"); var statement = SqlStatements.Select("*").From(temp); Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT * FROM ( SELECT u.ID FROM Users u ) Sub")); }
public void When_selecting_with_triple_join_Then_works() { var statement = SqlStatements.Select("u.ID, t.ID") .From("Users u") .FullJoin(new TableClause("Teams t"), "u.TeamID = t.ID") .OuterJoin(new TableClause("Settings s"), "u.SettingID = s.ID") .LeftOuterJoin(new TableClause("Parameters p"), "u.ParameterID = p.ID"); Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT u.ID, t.ID FROM Users u FULL JOIN Teams t ON u.TeamID = t.ID OUTER JOIN Settings s ON u.SettingID = s.ID LEFT OUTER JOIN Parameters p ON u.ParameterID = p.ID")); }
public void When_offsetting_and_limiting_Then_renders_differently() { var statement = new SelectStatement(new[] { "u.ID" }) .From("Users u") .Limit(3) .Offset(6); var sql = this.renderer.RenderSelect(statement); Assert.That( sql, SqlCompareConstraint.EqualTo(@"SELECT u.ID FROM Users u OFFSET 6 ROWS FETCH NEXT 3 ROWS ONLY")); }
public void When_deleting_from_two_tables_Then_builds_properly() { var statement = new DeleteStatement(tableToDelete: "u") .From("Users u") .InnerJoin("Teams t", "u.TeamID = t.ID") .Where("t.IsOld = TRUE"); var sql = statement.ToSql(); Assert.That( sql, SqlCompareConstraint.EqualTo(@"DELETE u FROM Users u INNER JOIN Teams t ON u.TeamID = t.ID WHERE t.IsOld = TRUE")); }
public void When_clearing_columns_Then_empties_list() { var statement = SqlStatements.Select("u.ID, u.Name, u.EmailAddress") .From("Users u"); Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT u.ID, u.Name, u.EmailAddress FROM Users u")); statement.Columns(true, "u.ID"); Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT u.ID FROM Users u")); }
public void When_building_union_Then_builds_properly() { var first = SqlStatements.Select("*").From("Users u"); var second = SqlStatements.Select("*").From("Teams t"); var union = new UnionOperation(first, second); Assert.That( union.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT * FROM Users u UNION SELECT * FROM Teams t")); }
public void When_updating_Then_builds_properly() { var statement = SqlStatements.Update("ID", "Name", "EmailAddress") .From("Users") .Where("u.IsCool = TRUE") .Where("u.Name LIKE @Query"); var sql = statement.ToSql(); Assert.That( sql, SqlCompareConstraint.EqualTo(@"UPDATE Users SET ID = @ID, Name = @Name, EmailAddress = @EmailAddress WHERE u.IsCool = TRUE AND u.Name LIKE @Query")); }
public void When_inserting_one_row_Then_builds_properly() { var statement = new InsertStatement(new[] { "Name", "EmailAddress" }) .Into("Users"); var sql = statement.ToSql(); Assert.That( sql, SqlCompareConstraint.EqualTo(@"INSERT INTO Users ( Name, EmailAddress ) VALUES ( @Name, @EmailAddress )")); }
public void When_updating_from_two_tables_Then_builds_properly() { var statement = new UpdateStatement(new[] { "Name" }, tableToUpdate: "u") .From("Users u") .InnerJoin("Teams t", "u.TeamID = t.ID") .Where("t.IsOld = TRUE"); var sql = statement.ToSql(); Assert.That( sql, SqlCompareConstraint.EqualTo(@"UPDATE u SET Name = @Name FROM Users u INNER JOIN Teams t ON u.TeamID = t.ID WHERE t.IsOld = TRUE")); }
public void When_selecting_with_multiple_values_Then_builds_properly() { var statement = SqlStatements.Select("u.ID, u.Name, u.EmailAddress") .Where("u.IsCool = TRUE AND u.Name LIKE @Query") .OrderBy("u.EmailAddress") .From("Users u") .OrderBy("u.Name", false); var sql = statement.ToSql(); Assert.That( sql, SqlCompareConstraint.EqualTo(@"SELECT u.ID, u.Name, u.EmailAddress FROM Users u WHERE u.IsCool = TRUE AND u.Name LIKE @Query ORDER BY u.EmailAddress ASC, u.Name DESC")); }
public void When_getting_count_statement_Then_removes_unnecessary_stuff() { var statement = SqlStatements.Select("u.ID, u.Name, u.EmailAddress") .From("Users u") .Where("u.IsCool IS NULL") .GroupBy("u.ID") .OrderBy("u.Name") .Limit(1).Offset(2); var count = statement.ToCount(); Assert.That( count.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT COUNT(*) FROM Users u WHERE u.IsCool IS NULL GROUP BY u.ID")); }
public void When_selecting_not_in_order_Then_builds_properly() { var statement = new SelectStatement(new[] { "u.ID", "u.Name", "u.EmailAddress" }) .Where("u.IsCool = TRUE") .From("Users u") .Where("u.Name LIKE @Query") .OrderBy("u.Name", false); var sql = statement.ToSql(); Assert.That( sql, SqlCompareConstraint.EqualTo(@"SELECT u.ID, u.Name, u.EmailAddress FROM Users u WHERE u.IsCool = TRUE AND u.Name LIKE @Query ORDER BY u.Name DESC")); }
public void When_selecting_with_intersect_and_except_Then_outputs_properly() { var first = SqlStatements.Select("u.ID").From("Users u"); var second = SqlStatements.Select("t.ID").From("Teams t"); var third = SqlStatements.Select("w.ID").From("Wot w"); var statement = SqlStatements.Select("*") .From(first.Intersect(second).Except(third, all: true).As("Sub")) .Where("ID > 3"); Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT * FROM ( SELECT u.ID FROM Users u INTERSECT SELECT t.ID FROM Teams t EXCEPT ALL SELECT w.ID FROM Wot w ) Sub WHERE ID > 3")); }
public void When_selecting_with_union_all_Then_outputs_properly() { var firstUnion = SqlStatements.Select("u.ID").From("Users u"); var secondUnion = SqlStatements.Select("t.ID").From("Teams t"); var thirdUnion = SqlStatements.Select("w.ID").From("Wot w"); var statement = SqlStatements.Select("*") .From(firstUnion.Union(secondUnion, all: true).Union(thirdUnion).As("Sub")) .Where("ID > 3"); Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT * FROM ( SELECT u.ID FROM Users u UNION ALL SELECT t.ID FROM Teams t UNION SELECT w.ID FROM Wot w ) Sub WHERE ID > 3")); }
public void When_cloning_Then_copies_stuff() { var statement = SqlStatements.Select("u.ID, u.Name, u.EmailAddress") .From("Users u"); Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT u.ID, u.Name, u.EmailAddress FROM Users u")); var clone = statement.Clone(); clone.From("Teams t") .Columns("u.IsCool") .Where("u.Name = @Query") .OrderBy("u.Name", false); Assert.That( statement.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT u.ID, u.Name, u.EmailAddress FROM Users u")); Assert.That( clone.ToSql(), SqlCompareConstraint.EqualTo(@"SELECT u.ID, u.Name, u.EmailAddress, u.IsCool FROM Users u, Teams t WHERE u.Name = @Query ORDER BY u.Name DESC")); }