public void CanCompile()
{
var results = Compilers.Compile(new Query("Table"));
Assert.NotNull(results);
Assert.Equal(Compilers.KnownEngineCodes.Count(), results.Count);
}
public void CanCompileSelectively()
{
var desiredEngines = new[] { EngineCodes.SqlServer, EngineCodes.MySql };
var results = Compilers.Compile(desiredEngines, new Query("Table"));
Assert.Equal(desiredEngines.Length, results.Count);
Assert.Contains(results, a => a.Key == EngineCodes.SqlServer);
Assert.Contains(results, a => a.Key == EngineCodes.MySql);
}
public void Passing_Negative_Boolean_False_To_Where_Should_Call_WhereTrue_Or_WhereFalse()
{
var query = new Query("Table").Where("Col", "!=", false);
var engines = new[] {
EngineCodes.SqlServer,
};
var c = Compilers.Compile(engines, query);
Assert.Equal("SELECT * FROM [Table] WHERE [Col] != cast(0 as bit)", c[EngineCodes.SqlServer].ToString());
}
public void OneFromPerEngine()
{
var query = new Query("generic")
.ForSqlServer(q => q.From("dnu"))
.ForSqlServer(q => q.From("mssql"));
var engines = new[] { EngineCodes.SqlServer, EngineCodes.MySql, EngineCodes.PostgreSql };
var c = Compilers.Compile(engines, query);
Assert.Equal(2, query.Clauses.OfType <AbstractFrom>().Count());
Assert.Equal("SELECT * FROM [mssql]", c[EngineCodes.SqlServer].RawSql);
Assert.Equal("SELECT * FROM \"generic\"", c[EngineCodes.PostgreSql].RawSql);
Assert.Equal("SELECT * FROM `generic`", c[EngineCodes.MySql].RawSql);
}
public void CompilerSpecificFromMixed()
{
var query = new Query()
.ForSqlServer(q => q.From("mssql"))
.ForPostgreSql(q => q.FromRaw("[pgsql]"))
.ForMySql(q => q.From("mysql"));
var engines = new[] { EngineCodes.SqlServer, EngineCodes.MySql, EngineCodes.PostgreSql };
var c = Compilers.Compile(engines, query);
Assert.Equal("SELECT * FROM [mssql]", c[EngineCodes.SqlServer].RawSql);
Assert.Equal("SELECT * FROM \"pgsql\"", c[EngineCodes.PostgreSql].RawSql);
Assert.Equal("SELECT * FROM `mysql`", c[EngineCodes.MySql].RawSql);
}
public void Offset_Takes_Generic_If_Needed()
{
var query = new Query("mytable")
.Limit(5)
.Offset(10)
.ForPostgreSql(q => q.Limit(20));
var engines = new[] { EngineCodes.MySql, EngineCodes.PostgreSql };
var c = Compilers.Compile(engines, query);
Assert.Equal("SELECT * FROM `mytable` LIMIT 5 OFFSET 10", c[EngineCodes.MySql].ToString());
Assert.Equal("SELECT * FROM \"mytable\" LIMIT 20 OFFSET 10", c[EngineCodes.PostgreSql].ToString());
}
public void Where_Nested()
{
var query = new Query("table")
.Where(q => q.Where("a", 1).OrWhere("a", 2));
var engines = new[] {
EngineCodes.SqlServer,
};
var c = Compilers.Compile(engines, query);
Assert.Equal("SELECT * FROM [table] WHERE ([a] = 1 OR [a] = 2)", c[EngineCodes.SqlServer].ToString());
}
public void Can_Change_Generic_Limit_After_SpecificOffset()
{
Query query = new Query("mytable")
.Limit(5)
.Offset(10)
.ForPostgreSql(q => q.Offset(20))
.Limit(7);
string[] engines = new[] { EngineCodes.MySql, EngineCodes.PostgreSql };
TestSqlResultContainer c = Compilers.Compile(engines, query);
Assert.Equal("SELECT * FROM `mytable` LIMIT 7 OFFSET 10", c[EngineCodes.MySql].ToString());
Assert.Equal("SELECT * FROM \"mytable\" LIMIT 7 OFFSET 20", c[EngineCodes.PostgreSql].ToString());
}
public void CompilerSpecificOffset()
{
var query = new Query("mytable")
.ForMySql(q => q.Offset(5))
.ForPostgreSql(q => q.Offset(10));
var engines = new[] { EngineCodes.SqlServer, EngineCodes.MySql, EngineCodes.PostgreSql };
var c = Compilers.Compile(engines, query);
Assert.Equal(2, query.GetComponents("offset").Count);
Assert.Equal("SELECT * FROM `mytable` LIMIT 18446744073709551615 OFFSET 5", c[EngineCodes.MySql].ToString());
Assert.Equal("SELECT * FROM \"mytable\" OFFSET 10", c[EngineCodes.PostgreSql].ToString());
Assert.Equal("SELECT * FROM [mytable]", c[EngineCodes.SqlServer].ToString());
}
public void CompilerSpecificFromRaw()
{
Query query = new Query()
.ForSqlServer(q => q.FromRaw("[mssql]"))
.ForPostgreSql(q => q.FromRaw("[pgsql]"))
.ForMySql(q => q.FromRaw("[mysql]"));
string[] engines = new[] { EngineCodes.SqlServer, EngineCodes.MySql, EngineCodes.PostgreSql };
TestSqlResultContainer c = Compilers.Compile(engines, query);
Assert.Equal("SELECT * FROM [mssql]", c[EngineCodes.SqlServer].RawSql);
Assert.Equal("SELECT * FROM \"pgsql\"", c[EngineCodes.PostgreSql].RawSql);
Assert.Equal("SELECT * FROM `mysql`", c[EngineCodes.MySql].RawSql);
}
public void Can_Change_Generic_Offset_After_SpecificLimit()
{
var query = new Query("mytable")
.Limit(5)
.Offset(10)
.ForPostgreSql(q => q.Limit(20))
.Offset(7);
var engines = new[] { EngineCodes.MySql, EngineCodes.PostgreSql };
var c = Compilers.Compile(engines, query);
Assert.Equal("SELECT * FROM `mytable` LIMIT 5 OFFSET 7", c[EngineCodes.MySql].ToString());
Assert.Equal("SELECT * FROM \"mytable\" LIMIT 20 OFFSET 7", c[EngineCodes.PostgreSql].ToString());
}
public void CompilerSpecificLimit()
{
var query = new Query("mytable")
.ForSqlServer(q => q.Limit(5))
.ForPostgreSql(q => q.Limit(10));
var engines = new[] { EngineCodes.SqlServer, EngineCodes.MySql, EngineCodes.PostgreSql };
var c = Compilers.Compile(engines, query);
Assert.Equal(2, query.GetComponents("limit").Count);
Assert.Equal("SELECT TOP (5) * FROM [mytable]", c[EngineCodes.SqlServer].ToString());
Assert.Equal("SELECT * FROM \"mytable\" LIMIT 10", c[EngineCodes.PostgreSql].ToString());
Assert.Equal("SELECT * FROM `mytable`", c[EngineCodes.MySql].ToString());
}
public void Offset_Takes_Generic_If_Needed_With_Long_Limit()
{
long limit = 5;
Query query = new Query("mytable")
.Limit(limit)
.Offset(10)
.ForPostgreSql(q => q.Limit(20));
string[] engines = new[] { EngineCodes.MySql, EngineCodes.PostgreSql };
TestSqlResultContainer c = Compilers.Compile(engines, query);
Assert.Equal("SELECT * FROM `mytable` LIMIT 5 OFFSET 10", c[EngineCodes.MySql].ToString());
Assert.Equal("SELECT * FROM \"mytable\" LIMIT 20 OFFSET 10", c[EngineCodes.PostgreSql].ToString());
}
public void CompilerSpecificLongLimit()
{
long limit = 10;
Query query = new Query("mytable")
.ForSqlServer(q => q.Limit(5))
.ForPostgreSql(q => q.Limit(limit));
string[] engines = new[] { EngineCodes.SqlServer, EngineCodes.MySql, EngineCodes.PostgreSql };
TestSqlResultContainer c = Compilers.Compile(engines, query);
Assert.Equal(2, query.GetComponents("limit").Count());
Assert.Equal("SELECT TOP (5) * FROM [mytable]", c[EngineCodes.SqlServer].ToString());
Assert.Equal("SELECT * FROM \"mytable\" LIMIT 10", c[EngineCodes.PostgreSql].ToString());
Assert.Equal("SELECT * FROM `mytable`", c[EngineCodes.MySql].ToString());
}
public void UnsafeLiteral_Update()
{
var query = new Query("Table").AsUpdate(new
{
Count = new UnsafeLiteral("Count + 1")
});
var engines = new[] {
EngineCodes.SqlServer,
};
var c = Compilers.Compile(engines, query);
Assert.Equal("UPDATE [Table] SET [Count] = Count + 1", c[EngineCodes.SqlServer].ToString());
}
public void UnsafeLiteral_Insert()
{
var query = new Query("Table").AsInsert(new
{
Count = new UnsafeLiteral("Count + 1")
});
var engines = new[] {
EngineCodes.SqlServer,
};
var c = Compilers.Compile(engines, query);
Assert.Equal("INSERT INTO [Table] ([Count]) VALUES (Count + 1)", c[EngineCodes.SqlServer].ToString());
}
public void AdHoc_SingletonRow()
{
var query = new Query("rows").With("rows",
new[] { "a" },
new object[][] {
new object[] { 1 },
});
var c = Compilers.Compile(query);
Assert.Equal("WITH [rows] AS (SELECT [a] FROM (VALUES (1)) AS tbl ([a]))\nSELECT * FROM [rows]", c[EngineCodes.SqlServer].ToString());
Assert.Equal("WITH \"rows\" AS (SELECT 1 AS \"a\")\nSELECT * FROM \"rows\"", c[EngineCodes.PostgreSql].ToString());
Assert.Equal("WITH `rows` AS (SELECT 1 AS `a`)\nSELECT * FROM `rows`", c[EngineCodes.MySql].ToString());
Assert.Equal("WITH \"rows\" AS (SELECT 1 AS \"a\")\nSELECT * FROM \"rows\"", c[EngineCodes.Sqlite].ToString());
Assert.Equal("WITH \"ROWS\" AS (SELECT 1 AS \"A\" FROM RDB$DATABASE)\nSELECT * FROM \"ROWS\"", c[EngineCodes.Firebird].ToString());
Assert.Equal("WITH \"rows\" AS (SELECT 1 AS \"a\" FROM DUAL)\nSELECT * FROM \"rows\"", c[EngineCodes.Oracle].ToString());
}
public void AdHoc_TwoRows()
{
var query = new Query("rows").With("rows",
new[] { "a", "b", "c" },
new object[][] {
new object[] { 1, 2, 3 },
new object[] { 4, 5, 6 },
});
var c = Compilers.Compile(query);
Assert.Equal("WITH [rows] AS (SELECT [a], [b], [c] FROM (VALUES (1, 2, 3), (4, 5, 6)) AS tbl ([a], [b], [c]))\nSELECT * FROM [rows]", c[EngineCodes.SqlServer].ToString());
Assert.Equal("WITH \"rows\" AS (SELECT 1 AS \"a\", 2 AS \"b\", 3 AS \"c\" UNION ALL SELECT 4 AS \"a\", 5 AS \"b\", 6 AS \"c\")\nSELECT * FROM \"rows\"", c[EngineCodes.PostgreSql].ToString());
Assert.Equal("WITH `rows` AS (SELECT 1 AS `a`, 2 AS `b`, 3 AS `c` UNION ALL SELECT 4 AS `a`, 5 AS `b`, 6 AS `c`)\nSELECT * FROM `rows`", c[EngineCodes.MySql].ToString());
Assert.Equal("WITH \"rows\" AS (SELECT 1 AS \"a\", 2 AS \"b\", 3 AS \"c\" UNION ALL SELECT 4 AS \"a\", 5 AS \"b\", 6 AS \"c\")\nSELECT * FROM \"rows\"", c[EngineCodes.Sqlite].ToString());
Assert.Equal("WITH \"ROWS\" AS (SELECT 1 AS \"A\", 2 AS \"B\", 3 AS \"C\" FROM RDB$DATABASE UNION ALL SELECT 4 AS \"A\", 5 AS \"B\", 6 AS \"C\" FROM RDB$DATABASE)\nSELECT * FROM \"ROWS\"", c[EngineCodes.Firebird].ToString());
Assert.Equal("WITH \"rows\" AS (SELECT 1 AS \"a\", 2 AS \"b\", 3 AS \"c\" FROM DUAL UNION ALL SELECT 4 AS \"a\", 5 AS \"b\", 6 AS \"c\" FROM DUAL)\nSELECT * FROM \"rows\"", c[EngineCodes.Oracle].ToString());
}
public void InsertWithByteArray()
{
var fauxImagebytes = new byte[] { 0x1, 0x3, 0x3, 0x7 };
var query = new Query("Books")
.AsInsert(new[] { "Id", "CoverImageBytes" },
new object[]
{
1,
fauxImagebytes
});
var c = Compilers.Compile(query);
Assert.All(c.Values, a => Assert.Equal(2, a.NamedBindings.Count));
var exemplar = c[EngineCodes.SqlServer];
Assert.Equal("INSERT INTO [Books] ([Id], [CoverImageBytes]) VALUES (?, ?)", exemplar.RawSql);
Assert.Equal("INSERT INTO [Books] ([Id], [CoverImageBytes]) VALUES (@p0, @p1)", exemplar.Sql);
}
public void AdHoc_ProperBindingsPlacement()
{
var query = new Query("rows")
.With("othercte", q => q.From("othertable").Where("othertable.status", "A"))
.Where("rows.foo", "bar")
.With("rows",
new[] { "a", "b", "c" },
new object[][] {
new object[] { 1, 2, 3 },
new object[] { 4, 5, 6 },
})
.Where("rows.baz", "buzz");
var c = Compilers.Compile(query);
Assert.Equal(string.Join("\n", new[] {
"WITH [othercte] AS (SELECT * FROM [othertable] WHERE [othertable].[status] = 'A'),",
"[rows] AS (SELECT [a], [b], [c] FROM (VALUES (1, 2, 3), (4, 5, 6)) AS tbl ([a], [b], [c]))",
"SELECT * FROM [rows] WHERE [rows].[foo] = 'bar' AND [rows].[baz] = 'buzz'",
}), c[EngineCodes.SqlServer].ToString());
}
public void ShouldThrowIfAnyEngineCodesAreInvalid()
{
var codes = new[] { EngineCodes.SqlServer, "123", EngineCodes.MySql, "abc" };
Assert.Throws <InvalidOperationException>(() => Compilers.Compile(codes, new Query()));
}