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