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 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(" LEFT OUTER JOIN after ON select_test_alias.column1 = after.column1") );
select.SetOrderByClause("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 select_test_alias.identity_column = :select_test_alias.identity_column ")
.Append("LEFT OUTER JOIN after ON select_test_alias.column1 = after.column1 ")
.Append("ORDER BY column1 DESC")
.ToString();
int numOfParams = 0;
Parameter expectedParam = null;
foreach(object part in sqlString.SqlParts)
{
if(part is Parameter)
{
numOfParams++;
expectedParam = (Parameter)part;
}
}
Assert.AreEqual(expectedSql , sqlString.ToString(), "SQL String");
Assert.AreEqual(1, numOfParams, "One parameter");
Parameter firstParam = new Parameter( "identity_column", "select_test_alias", new SqlTypes.Int64SqlType() );
Assert.AreEqual(firstParam.SqlType.DbType, expectedParam.SqlType.DbType, "First Parameter Type");
Assert.AreEqual(firstParam.Name, expectedParam.Name, "First Parameter Name");
}
protected SqlString RenderSelect(int[] tableNumbers, int[] columnNumbers, int[] formulaNumbers)
{
Array.Sort(tableNumbers); //get 'em in the right order (not that it really matters)
//render the where and from parts
int drivingTable = tableNumbers[0];
string drivingAlias = GenerateTableAlias(RootAlias, drivingTable); //we *could* regenerate this inside each called method!
SqlString where = CreateWhereByKey(drivingTable, drivingAlias);
string from = CreateFrom(drivingTable, drivingAlias);
//now render the joins
JoinFragment jf = CreateJoin(tableNumbers, drivingAlias);
//now render the select clause
SelectFragment selectFragment = CreateSelect(columnNumbers, formulaNumbers);
//now tie it all together
SqlSelectBuilder select = new SqlSelectBuilder(Factory);
select.SetSelectClause(selectFragment.ToFragmentString().Substring(2));
select.SetFromClause(from);
select.SetWhereClause(where);
select.SetOuterJoins(jf.ToFromFragmentString, jf.ToWhereFragmentString);
if (Factory.Settings.IsCommentsEnabled)
{
select.SetComment("sequential select " + EntityName);
}
return select.ToSqlString();
}
protected SqlString GenerateIdInsertSelect(IQueryable persister, string tableAlias, IASTNode whereClause)
{
var select = new SqlSelectBuilder(Factory);
SelectFragment selectFragment = new SelectFragment(Factory.Dialect)
.AddColumns(tableAlias, persister.IdentifierColumnNames, persister.IdentifierColumnNames);
select.SetSelectClause(selectFragment.ToFragmentString().Substring(2));
string rootTableName = persister.TableName;
SqlString fromJoinFragment = persister.FromJoinFragment(tableAlias, true, false);
SqlString whereJoinFragment = persister.WhereJoinFragment(tableAlias, true, false);
select.SetFromClause(rootTableName + ' ' + tableAlias + fromJoinFragment);
if (whereJoinFragment == null)
{
whereJoinFragment = SqlString.Empty;
}
else
{
whereJoinFragment = whereJoinFragment.Trim();
if (whereJoinFragment.StartsWithCaseInsensitive("and "))
{
whereJoinFragment = whereJoinFragment.Substring(4);
}
}
SqlString userWhereClause = SqlString.Empty;
if (whereClause.ChildCount != 0)
{
// If a where clause was specified in the update/delete query, use it to limit the
// returned ids here...
try
{
var nodes = new CommonTreeNodeStream(whereClause);
var gen = new SqlGenerator(Factory, nodes);
gen.whereClause();
userWhereClause = gen.GetSQL().Substring(7);
}
catch (RecognitionException e)
{
throw new HibernateException("Unable to generate id select for DML operation", e);
}
if (whereJoinFragment.Length > 0)
{
whereJoinFragment.Append(" and ");
}
}
select.SetWhereClause(whereJoinFragment + userWhereClause);
var insert = new InsertSelect();
if (Factory.Settings.IsCommentsEnabled)
{
insert.SetComment("insert-select for " + persister.EntityName + " ids");
}
insert.SetTableName(persister.TemporaryIdTableName);
insert.SetSelect(select);
return insert.ToSqlString();
}
