public void EqOrNullTest()
{
using (ISession session = OpenSession())
{
var criteria = (CriteriaImpl)session.CreateCriteria(typeof(Simple));
var criteriaQuery = new CriteriaQueryTranslator(sessions, criteria, criteria.EntityOrClassName, "sql_alias");
ICriterion exp = Criterion.EqOrNull("Name", "foo");
SqlString sqlString = exp.ToSqlString(criteria, criteriaQuery, new CollectionHelper.EmptyMapClass <string, IFilter>());
string expectedSql = "sql_alias.Name = ?";
Assert.AreEqual(expectedSql, sqlString.ToString());
Assert.AreEqual(1, sqlString.GetParameterCount());
exp = Criterion.EqOrNull("Name", null);
sqlString = exp.ToSqlString(criteria, criteriaQuery, new CollectionHelper.EmptyMapClass <string, IFilter>());
expectedSql = "sql_alias.Name is null";
Assert.AreEqual(expectedSql, sqlString.ToString());
Assert.AreEqual(0, sqlString.GetParameterCount());
// Check that the result is the same than using official Restriction
ICriterion orExpExpected = Restrictions.Or(Restrictions.IsNull("Name"), Restrictions.Eq("Name", "foo"));
ICriterion orExpActual = Restrictions.Or(Criterion.EqOrNull("Name", null), Criterion.EqOrNull("Name", "foo"));
SqlString sqlStringExpected = orExpExpected.ToSqlString(criteria, criteriaQuery,
new CollectionHelper.EmptyMapClass <string, IFilter>());
SqlString sqlStringActual = orExpActual.ToSqlString(criteria, criteriaQuery,
new CollectionHelper.EmptyMapClass <string, IFilter>());
Assert.AreEqual(sqlStringExpected.ToString(), sqlStringActual.ToString());
}
}
public void SelectStringSqlTest()
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory();
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlSelectBuilder select = new SqlSelectBuilder(factoryImpl);
select.SetSelectClause("column1, column2");
select.SetFromClause("select_test", "select_test_alias");
select.SetOuterJoins(
new SqlString(" LEFT OUTER JOIN before ON select_test_alias.column1 = before.column1"),
new SqlString(" after.some_field = after.another_field "));
select.SetOrderByClause(new SqlString("column1 DESC"));
select.SetWhereClause("select_test_alias", new string[] { "identity_column" }, NHibernateUtil.Int64);
SqlString sqlString = select.ToSqlString();
string expectedSql = new StringBuilder().Append("SELECT ")
.Append("column1, column2 ")
.Append("FROM select_test select_test_alias ")
.Append("LEFT OUTER JOIN before ON select_test_alias.column1 = before.column1 ")
.Append("WHERE ")
.Append("after.some_field = after.another_field")
.Append(" AND ")
.Append("select_test_alias.identity_column = ? ")
.Append("ORDER BY column1 DESC")
.ToString();
Assert.AreEqual(expectedSql, sqlString.ToString(), "SQL String");
Assert.AreEqual(1, sqlString.GetParameterCount(), "One parameter");
}
public void GetLimitString_NoOffsetSpecified_UsesFetchFirstOnly()
{
// arrange
DB2Dialect dialect = new DB2Dialect();
SqlString sql = new SqlString(
new object[]
{
"select a, b, c ",
"from d",
" where X = ",
Parameter.Placeholder,
" and Z = ",
Parameter.Placeholder,
" order by a, x"
});
// act
SqlString limited = dialect.GetLimitString(sql, null, new SqlString("222"));
// assert
Assert.AreEqual(
"select a, b, c from d where X = ? and Z = ? order by a, x fetch first 222 rows only",
limited.ToString());
Assert.AreEqual(2, limited.GetParameterCount());
}
public void StartsWithTest()
{
using (ISession session = OpenSession())
{
var criteria = (CriteriaImpl)session.CreateCriteria(typeof(Simple));
var criteriaQuery = new CriteriaQueryTranslator(sessions, criteria, criteria.EntityOrClassName, "sql_alias");
ICriterion exp = Criterion.StartsWith("Number", "2");
SqlString sqlString = exp.ToSqlString(criteria, criteriaQuery, new CollectionHelper.EmptyMapClass <string, IFilter>());
string expectedSql = "sql_alias.Number like ?";
Assert.AreEqual(expectedSql, sqlString.ToString());
Assert.AreEqual(1, sqlString.GetParameterCount());
}
}
public void SimpleSelectStringSqlTest()
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory();
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlSimpleSelectBuilder select = new SqlSimpleSelectBuilder(factoryImpl.Dialect, factoryImpl);
select.SetTableName("test_simple_select_builder");
select.AddColumn("column_no_alias");
select.AddColumn("aliased_column", "aliased_column_alias");
select.AddColumns(new string[] { "column1_no_alias", "column2_no_alias" });
select.AddColumns(new string[] { "column1_with_alias", "column2_with_alias" }, new string[] { "c1_alias", "c2_alias" });
select.SetIdentityColumn(new string[] { "identity_column" }, NHibernateUtil.Int64);
select.SetVersionColumn(new string[] { "version_column" }, (IVersionType)NHibernateUtil.Int32);
select.AddWhereFragment(new string[] { "where_frag_column" }, NHibernateUtil.Int32, " = ");
SqlString sqlString = select.ToSqlString();
Parameter[] actualParams = new Parameter[3];
string expectedSql = new StringBuilder().Append("SELECT ")
.Append("column_no_alias, ")
.Append("aliased_column AS aliased_column_alias, ")
.Append("column1_no_alias, ")
.Append("column2_no_alias, ")
.Append("column1_with_alias AS c1_alias, ")
.Append("column2_with_alias AS c2_alias ")
.Append("FROM test_simple_select_builder ")
.Append("WHERE identity_column = ? AND version_column = ?")
.Append(" AND where_frag_column = ?")
.ToString();
Assert.AreEqual(expectedSql, sqlString.ToString(), "SQL String");
Assert.AreEqual(3, sqlString.GetParameterCount(), "3 parameters");
}
public void GetLimitString()
{
DB2Dialect dialect = new DB2Dialect();
SqlString sql = new SqlString(
new object[]
{
"select a, b, c ",
"from d",
" where X = ",
Parameter.Placeholder,
" and Z = ",
Parameter.Placeholder,
" order by a, x"
});
SqlString limited = dialect.GetLimitString(sql, new SqlString("111"), new SqlString("222"));
Assert.AreEqual(
"select * from (select rownumber() over(order by a, x) as rownum, a, b, c from d where X = ? and Z = ? order by a, x) as tempresult where rownum between 111+1 and 222",
limited.ToString());
Assert.AreEqual(2, limited.GetParameterCount());
}
protected void CompareSqlStrings(SqlString actualSqlString, string expectedString, int expectedNumOfParameters)
{
Assert.AreEqual(expectedString, actualSqlString.ToString(), "SqlString.ToString()");
Assert.AreEqual(expectedNumOfParameters, actualSqlString.GetParameterCount(), "Num of Parameters");
}