public void Where_Nested()
{
Query query = new Query("table")
.Where(q => q.Where("a", 1).OrWhere("a", 2));
string[] engines = new[] {
EngineCodes.SqlServer,
};
TestSqlResultContainer c = Compilers.Compile(engines, query);
Assert.Equal("SELECT * FROM [table] WHERE ([a] = 1 OR [a] = 2)", c[EngineCodes.SqlServer].ToString());
}
public void Offset_Takes_Generic_If_Needed()
{
Query query = new Query("mytable")
.Limit(5)
.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 Can_Change_Generic_Offset_After_SpecificLimit()
{
Query query = new Query("mytable")
.Limit(5)
.Offset(10)
.ForPostgreSql(q => q.Limit(20))
.Offset(7);
string[] engines = new[] { EngineCodes.MySql, EngineCodes.PostgreSql };
TestSqlResultContainer 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 CompilerSpecificOffset()
{
Query query = new Query("mytable")
.ForMySql(q => q.Offset(5))
.ForPostgreSql(q => q.Offset(10));
string[] engines = new[] { EngineCodes.SqlServer, EngineCodes.MySql, EngineCodes.PostgreSql };
TestSqlResultContainer 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 CompilerSpecificLimit()
{
Query query = new Query("mytable")
.ForSqlServer(q => q.Limit(5))
.ForPostgreSql(q => q.Limit(10));
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 OneFromPerEngine()
{
Query query = new Query("generic")
.ForSqlServer(q => q.From("dnu"))
.ForSqlServer(q => q.From("mssql"));
string[] engines = new[] { EngineCodes.SqlServer, EngineCodes.MySql, EngineCodes.PostgreSql };
TestSqlResultContainer 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()
{
Query query = new Query()
.ForSqlServer(q => q.From("mssql"))
.ForPostgreSql(q => q.FromRaw("[pgsql]"))
.ForMySql(q => q.From("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 InsertWithByteArray()
{
byte[] fauxImagebytes = new byte[] { 0x1, 0x3, 0x3, 0x7 };
Query query = new Query("Books")
.AsInsert(new[] { "Id", "CoverImageBytes" },
new object[]
{
1,
fauxImagebytes
});
TestSqlResultContainer c = Compilers.Compile(query);
Assert.All(c.Values, a => Assert.Equal(2, a.NamedBindings.Count));
SqlResult 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);
}
