public void DoesAutomaticJoinWithGroupByDistrict()
{
var fld = ViewRow.Fields;
var query = new SqlQuery()
.From(fld)
.Select("1")
.GroupBy(fld.District);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT 1 " +
"FROM ViewTable T0 " +
"LEFT JOIN Districts d ON (d.DistrictID = T0.DistrictID) " +
"GROUP BY d.Name"),
TestSqlHelper.Normalize(
query.DebugText));
}
public void SubQueryCanBeUsedAsCriteriaUsingVar()
{
var query = new SqlQuery()
.From("ParentTable")
.Select("ParentColumn");
query.Where(new Criteria(query.SubQuery()
.From("SubTable")
.Take(1)
.Select("SubColumn")) >= 1);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT ParentColumn FROM ParentTable WHERE " +
"((SELECT TOP 1 SubColumn FROM SubTable) >= @p1)"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void DoesAutomaticJoinProperlyWithViewRowIfOnlyCityIsSelected()
{
var fld = ViewRow.Fields;
var query = new SqlQuery()
.From(fld)
.Select(fld.City);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT " +
"c.Name AS [City] " +
"FROM ViewTable T0 " +
"LEFT JOIN Districts d ON (d.DistrictID = T0.DistrictID) " +
"LEFT JOIN Cities c ON (c.CityID = d.CityID) "),
TestSqlHelper.Normalize(
query.ToString()));
}
public void SkipUsesRowNumberForSql2005Dialect()
{
var query = new SqlQuery()
.Dialect(SqlServer2005Dialect.Instance)
.Select("c")
.From("t")
.OrderBy("x")
.Skip(10)
.Take(20);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT * FROM (\n" +
"SELECT TOP 30 c, ROW_NUMBER() OVER (ORDER BY x) AS __num__ FROM t) __results__ " +
"WHERE __num__ > 10"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void DoesAutomaticJoinWithWhereDistrict()
{
var fld = ViewRow.Fields;
var query = new SqlQuery()
.From(fld)
.Select("1")
.Where(fld.District.Contains("x"));
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT 1 " +
"FROM ViewTable T0 " +
"LEFT JOIN Districts d ON (d.DistrictID = T0.DistrictID) " +
"WHERE d.Name LIKE N'%x%'"),
TestSqlHelper.Normalize(
query.DebugText));
}
public void UnionExceptWorksProperly()
{
var query = new SqlQuery()
.Dialect(SqlServer2000Dialect.Instance)
.From("T")
.Select("A")
.Select("B")
.Union(SqlUnionType.Except)
.From("X")
.Select("U", "A")
.Select("W", "B")
.OrderBy("A");
Assert.Equal(
TestSqlHelper.Normalize(
"(SELECT A, B FROM T) EXCEPT SELECT U AS [A], W AS [B] FROM X ORDER BY A"),
TestSqlHelper.Normalize(query.ToString()));
}
public void DeleteOneRowTest()
{
TestHelper.Init();
TestSqlHelper.Init(new UnitTestInit {
databases = Databases.LearnTransactSql, cryptData = true
});
Dictionary <string, MSColumnsDB> s = new Dictionary <string, MSColumnsDB>();
const string tableName = "Test_PageVT";
s.Add(tableName, new MSColumnsDB(true,
MSSloupecDB.CI(SqlDbType2.Int, "IDPage"),
MSSloupecDB.CI(SqlDbType2.TinyInt, "IDTable"),
MSSloupecDB.CI(SqlDbType2.Int, "IDItem"),
MSSloupecDB.CI(SqlDbType2.SmallInt, "Day"),
MSSloupecDB.CI(SqlDbType2.Int, XlfKeys.Views)
));
int IDPage = 0;
byte IDTable = 0;
int IDItem = 0;
short Day = NormalizeDate.To(DateTime.Today);
int Views = int.MaxValue;
foreach (var item in s)
{
MSStoredProceduresI.ci.DropAndCreateTable(item.Key, item.Value);
}
for (int i = 0; i < 3; i++)
{
MSStoredProceduresI.ci.Insert4(tableName, IDPage, IDTable, IDItem, Day, Views);
}
var c = MSStoredProceduresI.ci.SelectCount(tableName);
Assert.AreEqual(3, c);
MSStoredProceduresI.ci.DeleteOneRow(tableName, AB.Get("IDTable", IDTable), AB.Get("IDItem", IDItem), AB.Get("Day", Day));
c = MSStoredProceduresI.ci.SelectCount(tableName);
Assert.AreEqual(2, c);
}
public void DoesAutomaticJoinWithOrderByCountry()
{
var fld = ViewRow.Fields;
var query = new SqlQuery()
.From(fld)
.Select("1")
.OrderBy(fld.Country);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT 1 " +
"FROM ViewTable T0 " +
"LEFT JOIN Districts d ON (d.DistrictID = T0.DistrictID) " +
"LEFT JOIN Cities c ON (c.CityID = d.CityID) " +
"LEFT JOIN Countries o ON (o.CountryID = c.CountryID) " +
"ORDER BY o.Name"),
TestSqlHelper.Normalize(
query.DebugText));
}
public void UnionClearsSkipTake()
{
var query = new SqlQuery()
.Dialect(SqlServer2012Dialect.Instance)
.From("T")
.Skip(4)
.Take(3)
.Select("A")
.Select("B")
.OrderBy("C")
.Union()
.From("X")
.Select("U", "A")
.Select("W", "B")
.OrderBy("A");
Assert.Equal(
TestSqlHelper.Normalize(
"(SELECT A, B FROM T ORDER BY C OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY) UNION SELECT U AS [A], W AS [B] FROM X ORDER BY A"),
TestSqlHelper.Normalize(query.ToString()));
}
public void DoesAutomaticJoinProperlyWithAliasedAndJoinedFieldsAndSelect()
{
var x = RowMappingTests.ComplexRow.Fields.As("x");
var y = RowMappingTests.ComplexRow.Fields.As("y");
var query = new SqlQuery()
.From(x)
.LeftJoin(y, y.ID == x.ID)
.Select(x.CountryName, "CountryNameX")
.Select(y.CountryName, "CountryNameY");
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT " +
"x_c.Name AS CountryNameX, " +
"y_c.Name AS CountryNameY " +
"FROM ComplexTable x " +
"LEFT JOIN ComplexTable y ON (y.ComplexID = x.ComplexID) " +
"LEFT JOIN TheCountryTable x_c ON (x_c.TheCountryID = x.CountryID) " +
"LEFT JOIN TheCountryTable y_c ON (y_c.TheCountryID = y.CountryID)"),
TestSqlHelper.Normalize(
query.ToString()));
}
public void DoesAutomaticJoinProperlyWithViewRowIfAreSelectedInJoinOrderAliased()
{
var vw = ViewRow.Fields.As("vw");
var query = new SqlQuery()
.From(vw)
.Select(vw.District)
.Select(vw.City)
.Select(vw.Country);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT " +
"vw_d.Name AS District, " +
"vw_c.Name AS City, " +
"vw_o.Name AS Country " +
"FROM ViewTable vw " +
"LEFT JOIN Districts vw_d ON (vw_d.DistrictID = vw.DistrictID) " +
"LEFT JOIN Cities vw_c ON (vw_c.CityID = vw_d.CityID) " +
"LEFT JOIN Countries vw_o ON (vw_o.CountryID = vw_c.CountryID) "),
TestSqlHelper.Normalize(
query.ToString()));
}
public void DoesAutomaticJoinProperlyWithViewRowIfAllNamesAreSelectedInJoinOrder()
{
var fld = ViewRow.Fields;
var query = new SqlQuery()
.From(fld)
.Select(fld.District)
.Select(fld.City)
.Select(fld.Country);
Assert.Equal(
TestSqlHelper.Normalize(
"SELECT " +
"d.Name AS District, " +
"c.Name AS City, " +
"o.Name AS Country " +
"FROM ViewTable T0 " +
"LEFT JOIN Districts d ON (d.DistrictID = T0.DistrictID) " +
"LEFT JOIN Cities c ON (c.CityID = d.CityID) " +
"LEFT JOIN Countries o ON (o.CountryID = c.CountryID) "),
TestSqlHelper.Normalize(
query.ToString()));
}
public void TestMethod1()
{
TestSqlHelper test1 = new TestSqlHelper();
Assert.IsNotNull(test1.ToString(), "测试失败");
}
public void NormalizeRemovesSpacesAndLineEndsAtStartOrEnd()
{
Assert.Equal(
"SELECT FROM",
TestSqlHelper.Normalize(" \r\n\nSELECT FROM \n\r\n"));
}
public void NormalizeRemovesSpacesAndLineEndsAfterDoubleComma()
{
Assert.Equal(
"SELECT FROM;TEST STATEMENT 2;TEST STATEMENT 3;",
TestSqlHelper.Normalize("SELECT FROM; TEST STATEMENT 2;\n TEST STATEMENT 3; "));
}
public void NormalizeConvertsLineEndsToSpaces()
{
Assert.Equal(
"SELECT FROM",
TestSqlHelper.Normalize("SELECT \r\n FROM"));
}
public void NormalizeRemovesTripleSpaces()
{
Assert.Equal(
"SELECT FROM",
TestSqlHelper.Normalize("SELECT FROM"));
}
