/// <summary>
/// Configures the TableGenerator by reading the value of <c>table</c>,
/// <c>column</c>, and <c>schema</c> from the <c>parms</c> parameter.
/// </summary>
/// <param name="type">The <see cref="IType"/> the identifier should be.</param>
/// <param name="parms">An <see cref="IDictionary"/> of Param values that are keyed by parameter name.</param>
/// <param name="dialect">The <see cref="Dialect.Dialect"/> to help with Configuration.</param>
public virtual void Configure( IType type, IDictionary parms, Dialect.Dialect dialect )
{
this.tableName = PropertiesHelper.GetString( Table, parms, "hibernate_unique_key" );
this.columnName = PropertiesHelper.GetString( Column, parms, "next_hi" );
string schemaName = ( string ) parms[ Schema ];
if( schemaName != null && tableName.IndexOf( StringHelper.Dot ) < 0 )
{
tableName = schemaName + "." + tableName;
}
query = "select " + columnName + " from " + tableName;
if( dialect.SupportsForUpdate )
{
query += " for update";
}
// build the sql string for the Update since it uses parameters
Parameter setParam = new Parameter( columnName, new Int32SqlType() );
Parameter whereParam = new Parameter( columnName, new Int32SqlType() );
SqlStringBuilder builder = new SqlStringBuilder();
builder.Add( "update " + tableName + " set " )
.Add( columnName )
.Add( " = " )
.Add( setParam )
.Add( " where " )
.Add( columnName )
.Add( " = " )
.Add( whereParam );
updateSql = builder.ToSqlString();
}
void ISqlStringVisitor.Parameter(Parameter parameter)
{
if (hasReturnParameter && !foundReturnParameter)
{
result.Append(parameter);
foundReturnParameter = true;
return;
}
string name;
if (queryIndexToNumberOfPreceedingParameters.Count == 0)
{
// there's only one query... no need to worry about indexes of parameters of previous queries
name = formatter.GetParameterName(parameter.OriginalPositionInQuery ?? parameterIndex);
}
else
{
// multiple queries... in case the parameters were switched around (for SQL paging for instance) we need
// to keep the number of preceeding parameters (in previous queries of the batch) into account
if (parameter.OriginalPositionInQuery != null)
{
name = formatter.GetParameterName(GetNumberOfPreceedingParameters() + parameter.OriginalPositionInQuery.Value);
}
else
{
name = formatter.GetParameterName(parameterIndex);
}
}
parameterIndex++;
result.Append(name);
}
/// <summary>
/// Generates an array of parameters for the given <see cref="SqlType">SqlTypes</see>.
/// </summary>
/// <param name="count">The number of parameters to generate.</param>
/// <returns>An array of <see cref="Parameter"/> objects</returns>
public static Parameter[] GenerateParameters(int count)
{
Parameter[] result = new Parameter[count];
for (int i = 0; i < count; i++)
{
result[i] = Placeholder;
}
return result;
}
public void EqualsLengthDiffType()
{
Parameter x = new Parameter( "name", "alias", new SqlTypes.AnsiStringSqlType(5) );
ParameterLength y = new ParameterLength( "name", "alias", new SqlTypes.AnsiStringSqlType(5) );
// even though these contain the exact same values - they should not be
// equal because they are different types
Assert.IsFalse( x.Equals(y) );
Assert.IsFalse( y.Equals(x) );
}
/// <summary>
/// Generates an IDbDataParameter for the IDbCommand. It does not add the IDbDataParameter to the IDbCommand's
/// Parameter collection.
/// </summary>
/// <param name="command">The IDbCommand to use to create the IDbDataParameter.</param>
/// <param name="name">The name to set for IDbDataParameter.Name</param>
/// <param name="parameter">The Parameter to convert to an IDbDataParameter.</param>
/// <param name="dialect">The Dialect to use for Default lengths if needed.</param>
/// <returns>An IDbDataParameter ready to be added to an IDbCommand.</returns>
/// <remarks>
/// This adds logic to ensure that a DbType.Boolean parameter is not created since
/// ODP.NET doesn't support it.
/// </remarks>
protected override System.Data.IDbDataParameter GenerateParameter(IDbCommand command, string name, Parameter parameter, NHibernate.Dialect.Dialect dialect)
{
// if the parameter coming in contains a boolean then we need to convert it
// to another type since ODP.NET doesn't support DbType.Boolean
if( parameter.SqlType.DbType==DbType.Boolean )
{
parameter = new Parameter( parameter.Name, NHibernateUtil.Int16.SqlType ) ;
}
return base.GenerateParameter( command, name, parameter, dialect );
}
public void EqualsPrecisionDiffType()
{
Parameter x = new Parameter( "name", "alias", new SqlTypes.DecimalSqlType( 20, 4 ) );
ParameterPrecisionScale y = new ParameterPrecisionScale( "name", "alias", new SqlTypes.DecimalSqlType( 20, 4 ) );
// even though these contain the exact same values - they should not be
// equal because they are different types
Assert.IsFalse( x.Equals(y) );
Assert.IsFalse( y.Equals(x) );
}
protected void CompareSqlStrings(SqlString actualSqlString, string expectedString, int expectedNumOfParameters)
{
Parameter[] actualParameters = new Parameter[expectedNumOfParameters];
int numOfParameters = 0;
GetParameters(actualSqlString, ref actualParameters, ref numOfParameters);
Assert.AreEqual(expectedString, actualSqlString.ToString(), "SqlString.ToString()");
Assert.AreEqual(expectedNumOfParameters, numOfParameters, "Num of Parameters");
}
private static Parameter[ ] GenerateValueParameters( string prefix, SqlType sqlType, int count )
{
Parameter[ ] parameters = new Parameter[count];
for( int i = 0; i < count; i++ )
{
string parameterName = StringHelper.Suffix( prefix, StringHelper.Underscore + i.ToString() );
parameters[ i ] = new Parameter( parameterName, sqlType );
}
return parameters;
}
/// <summary>
/// Used to pull the Parameter[] and Number of Parameters out of the SqlString
/// </summary>
/// <param name="sqlString"></param>
/// <param name="parameters"></param>
/// <param name="numOfParameters"></param>
protected void GetParameters(SqlString sqlString, ref Parameter[] parameters, ref int numOfParameters)
{
foreach(object part in sqlString.SqlParts)
{
if(part is Parameter)
{
parameters[numOfParameters] = (Parameter)part;
numOfParameters++;
}
}
}
public void CompactWithNoString()
{
Parameter p1 = new Parameter( "p1" );
Parameter p2 = new Parameter( "p2" );
SqlString sql = new SqlString( new object[] {p1, p2} );
SqlString compacted = sql.Compact();
Assert.AreEqual( 2, compacted.SqlParts.Length );
Assert.AreEqual( ":p1:p2", compacted.ToString() );
}
private void AddValueOrProjection(Parameter[] parameters, int paramIndex, ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters, SqlStringBuilder result)
{
if (_projection == null)
{
result.Add(parameters[paramIndex]);
}
else
{
SqlString sql = _projection.ToSqlString(criteria, GetHashCode(), criteriaQuery, enabledFilters);
result.Add(SqlStringHelper.RemoveAsAliasesFromSql(sql));
}
}
void ISqlStringVisitor.Parameter(Parameter parameter)
{
if (hasReturnParameter && !foundReturnParameter)
{
result.Append(parameter);
foundReturnParameter = true;
return;
}
string name = formatter.GetParameterName(parameter.ParameterPosition ?? ++parameterIndex);
result.Append(name);
}
protected void CompareSqlStrings(SqlString actualSqlString, string expectedString, Parameter[] expectedParameters)
{
Parameter[] actualParameters = new Parameter[expectedParameters.Length];
int numOfParameters = 0;
GetParameters(actualSqlString, ref actualParameters, ref numOfParameters);
Assert.AreEqual(expectedString, actualSqlString.ToString(), "SqlString.ToString()");
Assert.AreEqual(expectedParameters.Length, numOfParameters, "Num of Parameters");
for(int i = 0; i < expectedParameters.Length; i++)
{
Assert.AreEqual(expectedParameters[i], actualParameters[i], "Parameter[" + i + "]");
}
}
public void EqualsSameType()
{
Parameter x = new Parameter( "name", "alias", new SqlTypes.Int32SqlType() );
Parameter y = new Parameter( "name", "alias", new SqlTypes.Int32SqlType() );
Parameter z = new Parameter( "name", "alias", new SqlTypes.Int32SqlType() );
Assert.IsTrue( x.Equals(y) );
Assert.IsTrue( y.Equals(x) );
Assert.IsTrue( y.Equals(z) );
Assert.IsTrue( x.Equals(z) );
Assert.IsFalse( x.Equals(null) );
y = new Parameter( "name2", "alias", new SqlTypes.Int32SqlType() );
Assert.IsFalse( x.Equals(y) );
Assert.IsFalse( y.Equals(x) );
}
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");
}
public void NotSqlStringTest()
{
ISession session = factory.OpenSession();
NExpression.ICriterion notExpression = NExpression.Expression.Not(NExpression.Expression.Eq("Address", "12 Adress"));
SqlString sqlString = notExpression.ToSqlString(factoryImpl, typeof(Simple), "simple_alias", BaseExpressionFixture.EmptyAliasClasses );
string expectedSql = dialect is Dialect.MySQLDialect ?
"not (simple_alias.address = :simple_alias.address)" :
"not simple_alias.address = :simple_alias.address";
Parameter firstParam = new Parameter( "address", "simple_alias", new SqlTypes.StringSqlType() );
CompareSqlStrings(sqlString, expectedSql, new Parameter[] {firstParam});
session.Close();
}
public void InsertSqlStringTest()
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory( );
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlInsertBuilder insert = new SqlInsertBuilder(factoryImpl);
insert.SetTableName("test_insert_builder");
insert.AddColumn(new string[] {"intColumn"}, NHibernateUtil.Int32);
insert.AddColumn(new string[] {"longColumn"}, NHibernateUtil.Int64);
insert.AddColumn("literalColumn", false, (Type.ILiteralType) NHibernateUtil.Boolean);
insert.AddColumn("stringColumn", 5.ToString());
SqlString sqlString = insert.ToSqlString();
Parameter[] actualParams = new Parameter[2];
int numOfParameters = 0;
string expectedSql = "INSERT INTO test_insert_builder (intColumn, longColumn, literalColumn, stringColumn) VALUES (:intColumn, :longColumn, 0, 5)";
Assert.AreEqual(expectedSql , sqlString.ToString(), "SQL String");
foreach(object part in sqlString.SqlParts)
{
if(part is Parameter)
{
actualParams[numOfParameters] = (Parameter)part;
numOfParameters++;
}
}
Assert.AreEqual(2, numOfParameters, "Two parameters");
Parameter firstParam = new Parameter( "intColumn", new SqlTypes.Int32SqlType() );
Parameter secondParam = new Parameter( "longColumn", new SqlTypes.Int64SqlType() );
Assert.AreEqual(firstParam.SqlType.DbType, actualParams[0].SqlType.DbType, "First Parameter Type");
Assert.AreEqual(firstParam.Name, actualParams[0].Name, "First Parameter Name");
Assert.AreEqual(secondParam.SqlType.DbType, actualParams[1].SqlType.DbType, "Second Parameter Type");
Assert.AreEqual(secondParam.Name, actualParams[1].Name, "Second Parameter Name");
}
public void SimpleDateExpression()
{
ISession session = factory.OpenSession();
NExpression.ICriterion andExpression = NExpression.Expression.Ge( "Date", DateTime.Now );
SqlString sqlString = andExpression.ToSqlString( factoryImpl, typeof(Simple), "simple_alias", BaseExpressionFixture.EmptyAliasClasses );
string expectedSql = "simple_alias.date_ >= :simple_alias.date_";
Parameter[] expectedParams = new Parameter[1];
Parameter firstAndParam = new Parameter( "date_", "simple_alias", new SqlTypes.DateTimeSqlType() );
expectedParams[0] = firstAndParam;
CompareSqlStrings(sqlString, expectedSql, expectedParams);
session.Close();
}
public void DeleteSqlStringTest() {
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory( );
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlDeleteBuilder delete = new SqlDeleteBuilder(factoryImpl);
delete.SetTableName("test_delete_builder");
delete.SetIdentityColumn(new string[] {"decimalColumn"}, NHibernateUtil.Decimal);
delete.SetVersionColumn(new string[] {"versionColumn"}, (IVersionType)NHibernateUtil.Int32);
delete.AddWhereFragment("a=b");
SqlString sqlString = delete.ToSqlString();
Parameter[] actualParams = new Parameter[2];
int numOfParameters = 0;
string expectedSql = "DELETE FROM test_delete_builder WHERE decimalColumn = :decimalColumn AND versionColumn = :versionColumn AND a=b";
Assert.AreEqual(expectedSql , sqlString.ToString(), "SQL String");
foreach(object part in sqlString.SqlParts) {
if(part is Parameter) {
actualParams[numOfParameters] = (Parameter)part;
numOfParameters++;
}
}
Assert.AreEqual(2, numOfParameters, "Two parameters");
Parameter firstParam = new Parameter( "decimalColumn", new SqlTypes.DecimalSqlType() );
Parameter secondParam = new Parameter( "versionColumn", new SqlTypes.Int32SqlType());
Assert.AreEqual(firstParam.SqlType.DbType, actualParams[0].SqlType.DbType, "firstParam Type");
Assert.AreEqual(firstParam.Name, actualParams[0].Name, "firstParam Name");
Assert.AreEqual(secondParam.SqlType.DbType, actualParams[1].SqlType.DbType, "secondParam Type");
Assert.AreEqual(secondParam.Name, actualParams[1].Name, "secondParam Name");
}
public void CompactWithParams()
{
SqlStringBuilder builder = new SqlStringBuilder();
Parameter param = new Parameter( "id", new SqlTypes.Int32SqlType() );
builder.Add("select from table ");
builder.Add("where ");
builder.Add("id = ");
builder.Add(param);
builder.Add(" and ");
builder.Add("'a'='a'");
SqlString sql = builder.ToSqlString();
sql = sql.Compact();
Assert.AreEqual( 3, sql.SqlParts.Length );
Assert.AreEqual( "select from table where id = :id and 'a'='a'", sql.ToString() );
}
public void InSqlStringTest()
{
ISession session = factory.OpenSession();
ICriterion inExpression = Expression.Expression.In( "Count", new int[ ] {3, 4, 5} );
SqlString sqlString = inExpression.ToSqlString( factoryImpl, typeof( Simple ), "simple_alias", BaseExpressionFixture.EmptyAliasClasses );
string expectedSql = "simple_alias.count_ in (:simple_alias.count__0, :simple_alias.count__1, :simple_alias.count__2)";
Parameter[ ] expectedParams = new Parameter[3];
for( int i = 0; i < expectedParams.Length; i++ )
{
Parameter param = new Parameter( "count_" + "_" + i, "simple_alias", new Int32SqlType() );
expectedParams[ i ] = param;
}
CompareSqlStrings( sqlString, expectedSql, expectedParams );
session.Close();
}
void ISqlStringVisitor.Parameter(Parameter parameter)
{
if (hasReturnParameter && !foundReturnParameter)
{
result.Append(parameter);
assignedParameterNames.Add(String.Empty);
foundReturnParameter = true;
return;
}
// NH: even if using SqlType[] the final commad may have X parameters, with this line we will use Y parameters in the IDbCommand
// for example the ParameterCollection may contains two parameters called @p0 and @p1 but the command contains just @p0.
// In this way the same parameter can be used in different places in the query without create a problem to the dear SQL-server (see NH1981)
// TODO: find a way to have exactly the same amount of parameters between the final IDbCommand and its IDataParameterCollection
// A candidateplace is making DriverBase.SetCommandParameters a little bit more intelligent... perhaps SqlString aware (see also DriverBase.SetCommandText, DriverBase.GenerateCommand)
string name = formatter.GetParameterName(parameter.ParameterPosition ?? parameterIndex);
assignedParameterNames.Add(name);
parameterIndex++;
result.Append(name);
}
public void BetweenSqlStringTest()
{
ISession session = factory.OpenSession();
NExpression.ICriterion betweenExpression = NExpression.Expression.Between("Count", 5, 10);
SqlString sqlString = betweenExpression.ToSqlString(factoryImpl, typeof(Simple), "simple_alias", BaseExpressionFixture.EmptyAliasClasses );
string expectedSql = "simple_alias.count_ between :simple_alias.count__lo and :simple_alias.count__hi";
Parameter[] expectedParams = new Parameter[2];
Parameter firstBetweenParam = new Parameter( "count__lo", "simple_alias", new SqlTypes.Int32SqlType() );
expectedParams[0] = firstBetweenParam;
Parameter secondBetweenParam = new Parameter( "count__hi", "simple_alias", new SqlTypes.Int32SqlType() );
expectedParams[1] = secondBetweenParam;
CompareSqlStrings(sqlString, expectedSql, expectedParams);
session.Close();
}
public void SimpleSqlStringTest()
{
ISession session = factory.OpenSession();
NExpression.ICriterion andExpression = NExpression.Expression.Eq("Address", "12 Adress");
SqlString sqlString = andExpression.ToSqlString(factoryImpl, typeof(Simple), "simple_alias", BaseExpressionFixture.EmptyAliasClasses );
string expectedSql = "simple_alias.address = :simple_alias.address";
Parameter[] expectedParams = new Parameter[1];
// even though a String parameter is a Size based Parameter it will not
// be a ParameterLength unless in the mapping file it is defined as
// type="String(200)" -> in the mapping file it is now defined as
// type="String" length="200"
Parameter firstAndParam = new Parameter( "address", "simple_alias", new SqlTypes.StringSqlType() );
expectedParams[0] = firstAndParam;
CompareSqlStrings(sqlString, expectedSql, expectedParams);
session.Close();
}
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 WithParameterTest()
{
ISession session = factory.OpenSession();
SqlStringBuilder builder = new SqlStringBuilder();
string expectedSql = "simple_alias.address = :address";
Parameter[] expectedParams = new Parameter[1];
Parameter firstAndParam = new Parameter( "address", new SqlTypes.StringSqlType() );
expectedParams[0] = firstAndParam;
builder.Add( "{alias}.address = " );
builder.Add( firstAndParam );
NExpression.ICriterion sqlExpression = NExpression.Expression.Sql(builder.ToSqlString(), "some address", NHibernateUtil.String );
SqlString sqlString = sqlExpression.ToSqlString(factoryImpl, typeof(Simple), "simple_alias", BaseExpressionFixture.EmptyAliasClasses );
CompareSqlStrings(sqlString, expectedSql, expectedParams);
session.Close();
}
public void InsentitiveLikeSqlStringTest()
{
ISession session = factory.OpenSession();
NExpression.ICriterion expression = NExpression.Expression.InsensitiveLike("Address", "12 Adress");
SqlString sqlString = expression.ToSqlString(factoryImpl, typeof(Simple), "simple_alias", BaseExpressionFixture.EmptyAliasClasses );
string expectedSql = "lower(simple_alias.address) like :simple_alias.address";
if ((factory as ISessionFactoryImplementor).Dialect is Dialect.PostgreSQLDialect)
{
expectedSql = "simple_alias.address ilike :simple_alias.address";
}
Parameter[] expectedParams = new Parameter[1];
Parameter firstParam = new Parameter( "address", "simple_alias", new SqlTypes.StringSqlType() );
expectedParams[0] = firstParam;
CompareSqlStrings(sqlString, expectedSql, expectedParams);
session.Close();
}
public SqlString GetSpatialFilterString(string tableAlias, string geometryColumnName, string primaryKeyColumnName, string tableName, Parameter parameter)
{
return worker.GetSpatialFilterString(tableAlias, geometryColumnName, primaryKeyColumnName, tableName, parameter);
}
public ConditionalFragment SetCondition(string[] lhs, Parameter[] rhs)
{
this.lhs = lhs;
this.rhs = rhs;
return this;
}
public void PushParameter(Parameter parameter)
{
builder.Add(parameter);
}