private void InitStatementString(IOuterJoinLoadable elementPersister, string alias, int batchSize, SqlString subquery)
{
int joins = CountEntityPersisters(associations);
Suffixes = BasicLoader.GenerateSuffixes(joins + 1);
int collectionJoins = CountCollectionPersisters(associations) + 1;
CollectionSuffixes = BasicLoader.GenerateSuffixes(joins + 1, collectionJoins);
SqlStringBuilder whereString = WhereString(alias, oneToManyPersister.KeyColumnNames, subquery, batchSize);
string filter = oneToManyPersister.FilterFragment(alias, EnabledFilters);
whereString.Insert(0, StringHelper.MoveAndToBeginning(filter));
JoinFragment ojf = MergeOuterJoins(associations);
SqlSelectBuilder select =
new SqlSelectBuilder(Factory).SetSelectClause(
oneToManyPersister.SelectFragment(null, null, alias, Suffixes[joins], CollectionSuffixes[0], true)
+ SelectString(associations)).SetFromClause(elementPersister.FromTableFragment(alias)
+ elementPersister.FromJoinFragment(alias, true, true)).SetWhereClause(
whereString.ToSqlString()).SetOuterJoins(ojf.ToFromFragmentString,
ojf.ToWhereFragmentString
+ elementPersister.WhereJoinFragment(alias, true, true));
select.SetOrderByClause(OrderBy(associations, oneToManyPersister.GetSQLOrderByString(alias)));
if (Factory.Settings.IsCommentsEnabled)
{
select.SetComment("load one-to-many " + oneToManyPersister.Role);
}
SqlString = select.ToSqlString();
}
private void InitStatementString(SqlString projection,SqlString condition,
string orderBy,string groupBy,LockMode lockMode)
{
int joins = CountEntityPersisters(associations);
Suffixes = BasicLoader.GenerateSuffixes(joins + 1);
JoinFragment ojf = MergeOuterJoins(associations);
SqlString selectClause = projection
??
new SqlString(persister.SelectFragment(alias, Suffixes[joins]) + SelectString(associations));
SqlSelectBuilder select = new SqlSelectBuilder(Factory)
.SetLockMode(lockMode)
.SetSelectClause(selectClause)
.SetFromClause(Dialect.AppendLockHint(lockMode, persister.FromTableFragment(alias)) +persister.FromJoinFragment(alias, true, true))
.SetWhereClause(condition)
.SetOuterJoins(ojf.ToFromFragmentString,ojf.ToWhereFragmentString + WhereFragment)
.SetOrderByClause(OrderBy(associations, orderBy))
.SetGroupByClause(groupBy);
if (Factory.Settings.IsCommentsEnabled)
select.SetComment(Comment);
SqlString = select.ToSqlString();
}
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");
}
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();
}
private void InitStatementString(string alias, int batchSize, SqlString subquery)
{
int joins = CountEntityPersisters(associations);
int collectionJoins = CountCollectionPersisters(associations) + 1;
Suffixes = BasicLoader.GenerateSuffixes(joins);
CollectionSuffixes = BasicLoader.GenerateSuffixes(joins, collectionJoins);
SqlStringBuilder whereString = WhereString(alias, collectionPersister.KeyColumnNames, subquery, batchSize);
string manyToManyOrderBy = string.Empty;
string filter = collectionPersister.FilterFragment(alias, EnabledFilters);
if (collectionPersister.IsManyToMany)
{
// from the collection of associations, locate OJA for the
// ManyToOne corresponding to this persister to fully
// define the many-to-many; we need that OJA so that we can
// use its alias here
// TODO : is there a better way here?
IAssociationType associationType = (IAssociationType)collectionPersister.ElementType;
foreach (OuterJoinableAssociation oja in associations)
{
if (oja.JoinableType == associationType)
{
// we found it
filter += collectionPersister.GetManyToManyFilterFragment(oja.RHSAlias, EnabledFilters);
manyToManyOrderBy += collectionPersister.GetManyToManyOrderByString(oja.RHSAlias);
}
}
}
whereString.Insert(0, StringHelper.MoveAndToBeginning(filter));
JoinFragment ojf = MergeOuterJoins(associations);
SqlSelectBuilder select =
new SqlSelectBuilder(Factory)
.SetSelectClause(collectionPersister.SelectFragment(alias, CollectionSuffixes[0])
+ SelectString(associations))
.SetFromClause(collectionPersister.TableName, alias)
.SetWhereClause(whereString.ToSqlString())
.SetOuterJoins(ojf.ToFromFragmentString, ojf.ToWhereFragmentString);
select.SetOrderByClause(OrderBy(associations, MergeOrderings(collectionPersister.GetSQLOrderByString(alias), manyToManyOrderBy)));
if (Factory.Settings.IsCommentsEnabled)
select.SetComment("load collection " + collectionPersister.Role);
SqlString = select.ToSqlString();
}
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 virtual object[] GetNaturalIdentifierSnapshot(object id, ISessionImplementor session)
{
if (!HasNaturalIdentifier)
{
throw new MappingException("persistent class did not define a natural-id : " + MessageHelper.InfoString(this));
}
if (log.IsDebugEnabled)
{
log.Debug("Getting current natural-id snapshot state for: " + MessageHelper.InfoString(this, id, Factory));
}
int[] naturalIdPropertyIndexes = NaturalIdentifierProperties;
int naturalIdPropertyCount = naturalIdPropertyIndexes.Length;
bool[] naturalIdMarkers = new bool[PropertySpan];
IType[] extractionTypes = new IType[naturalIdPropertyCount];
for (int i = 0; i < naturalIdPropertyCount; i++)
{
extractionTypes[i] = PropertyTypes[naturalIdPropertyIndexes[i]];
naturalIdMarkers[naturalIdPropertyIndexes[i]] = true;
}
///////////////////////////////////////////////////////////////////////
// TODO : look at perhaps caching this...
SqlSelectBuilder select = new SqlSelectBuilder(Factory);
if (Factory.Settings.IsCommentsEnabled)
{
select.SetComment("get current natural-id state " + EntityName);
}
select.SetSelectClause(ConcretePropertySelectFragmentSansLeadingComma(RootAlias, naturalIdMarkers));
select.SetFromClause(FromTableFragment(RootAlias) + FromJoinFragment(RootAlias, true, false));
string[] aliasedIdColumns = StringHelper.Qualify(RootAlias, IdentifierColumnNames);
SqlString whereClause = new SqlStringBuilder()
.Add(StringHelper.Join(new SqlString("=", Parameter.Placeholder, " and "), aliasedIdColumns))
.Add("=").AddParameter()
.Add(WhereJoinFragment(RootAlias, true, false))
.ToSqlString();
SqlString sql = select.SetOuterJoins(SqlString.Empty, SqlString.Empty).SetWhereClause(whereClause).ToStatementString();
///////////////////////////////////////////////////////////////////////
object[] snapshot = new object[naturalIdPropertyCount];
using (new SessionIdLoggingContext(session.SessionId))
try
{
IDbCommand ps = session.Batcher.PrepareCommand(CommandType.Text, sql, IdentifierType.SqlTypes(factory));
IDataReader rs = null;
try
{
IdentifierType.NullSafeSet(ps, id, 0, session);
rs = session.Batcher.ExecuteReader(ps);
//if there is no resulting row, return null
if (!rs.Read())
{
return null;
}
for (int i = 0; i < naturalIdPropertyCount; i++)
{
snapshot[i] =
extractionTypes[i].Hydrate(rs, GetPropertyAliases(string.Empty, naturalIdPropertyIndexes[i]), session, null);
if (extractionTypes[i].IsEntityType)
{
snapshot[i] = extractionTypes[i].ResolveIdentifier(snapshot[i], session, null);
}
}
return snapshot;
}
finally
{
session.Batcher.CloseCommand(ps, rs);
}
}
catch (DbException sqle)
{
var exceptionContext = new AdoExceptionContextInfo
{
SqlException = sqle,
Message = "could not retrieve snapshot: " + MessageHelper.InfoString(this, id, Factory),
Sql = sql.ToString(),
EntityName = EntityName,
EntityId = id
};
throw ADOExceptionHelper.Convert(Factory.SQLExceptionConverter, exceptionContext);
}
}
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 virtual SqlString GenerateSnapshotSelectString()
{
//TODO: should we use SELECT .. FOR UPDATE?
SqlSelectBuilder select = new SqlSelectBuilder(Factory);
if (Factory.Settings.IsCommentsEnabled)
{
select.SetComment("get current state " + EntityName);
}
string[] aliasedIdColumns = StringHelper.Qualify(RootAlias, IdentifierColumnNames);
string selectClause = StringHelper.Join(StringHelper.CommaSpace, aliasedIdColumns)
+ ConcretePropertySelectFragment(RootAlias, PropertyUpdateability);
SqlString fromClause = new SqlString(FromTableFragment(RootAlias)) +
FromJoinFragment(RootAlias, true, false);
SqlString joiner = new SqlString("=", Parameter.Placeholder, " and ");
SqlStringBuilder whereClauseBuilder = new SqlStringBuilder()
.Add(StringHelper.Join(joiner, aliasedIdColumns))
.Add("=")
.AddParameter()
.Add(WhereJoinFragment(RootAlias, true, false));
// H3.2 the Snapshot is what we found in DB without take care on version
//if (IsVersioned)
//{
// whereClauseBuilder.Add(" and ")
// .Add(VersionColumnName)
// .Add("=")
// .AddParameter();
//}
return select.SetSelectClause(selectClause)
.SetFromClause(fromClause)
.SetOuterJoins(SqlString.Empty, SqlString.Empty)
.SetWhereClause(whereClauseBuilder.ToSqlString())
.ToSqlString();
}
private SqlString GenerateGeneratedValuesSelectString(ValueInclusion[] inclusions)
{
SqlSelectBuilder select = new SqlSelectBuilder(Factory);
if (Factory.Settings.IsCommentsEnabled)
{
select.SetComment("get generated state " + EntityName);
}
string[] aliasedIdColumns = StringHelper.Qualify(RootAlias, IdentifierColumnNames);
// Here we render the select column list based on the properties defined as being generated.
// For partial component generation, we currently just re-select the whole component
// rather than trying to handle the individual generated portions.
string selectClause = ConcretePropertySelectFragment(RootAlias, inclusions);
selectClause = selectClause.Substring(2);
string fromClause = FromTableFragment(RootAlias) + FromJoinFragment(RootAlias, true, false);
SqlString whereClause = new SqlStringBuilder()
.Add(StringHelper.Join(new SqlString("=", Parameter.Placeholder, " and "), aliasedIdColumns))
.Add("=").AddParameter()
.Add(WhereJoinFragment(RootAlias, true, false))
.ToSqlString();
return select.SetSelectClause(selectClause)
.SetFromClause(fromClause)
.SetOuterJoins(SqlString.Empty, SqlString.Empty)
.SetWhereClause(whereClause)
.ToSqlString();
}
public virtual InsertSelect SetSelect(SqlSelectBuilder select)
{
this.select = select;
return this;
}
public virtual InsertSelect SetSelect(SqlSelectBuilder select)
{
this.select = select;
return(this);
}
private void InitStatementString( IQueryableCollection persister, string alias, IList associations, int batchSize, ISessionFactoryImplementor factory )
{
Suffixes = GenerateSuffixes( associations.Count );
SqlStringBuilder whereString = WhereString( factory, alias, persister.KeyColumnNames, persister.KeyType, batchSize );
if( persister.HasWhere )
{
whereString
.Add( " and " )
.Add( persister.GetSQLWhereString( alias ) );
}
JoinFragment ojf = MergeOuterJoins( associations );
SqlSelectBuilder select = new SqlSelectBuilder( factory )
.SetSelectClause(
persister.SelectFragment( alias ).Append(
SelectString( associations, factory ) ).ToString()
)
.SetFromClause( persister.TableName, alias )
.SetWhereClause( whereString.ToSqlString() )
.SetOuterJoins(
ojf.ToFromFragmentString,
ojf.ToWhereFragmentString
);
if( persister.HasOrdering )
{
select.SetOrderByClause( persister.GetSQLOrderByString( alias ) );
}
SqlString = select.ToSqlString();
}
protected virtual SqlString GenerateSnapshotSelectString()
{
//TODO: should we use SELECT .. FOR UPDATE?
SqlSelectBuilder select = new SqlSelectBuilder(Factory);
//if (Factory.Settings.IsCommentsEnabled)
//{
// select.SetComment("get current state " + ClassName);
//}
string[] aliasedIdColumns = StringHelper.Qualify(RootAlias, IdentifierColumnNames);
string selectClause = StringHelper.Join(", ", aliasedIdColumns) +
ConcretePropertySelectFragment(RootAlias, PropertyUpdateability);
SqlString fromClause = new SqlString(FromTableFragment(RootAlias)) +
FromJoinFragment(RootAlias, true, false);
SqlString joiner = new SqlString("=", Parameter.Placeholder, " and ");
SqlString whereClause = new SqlStringBuilder()
.Add(StringHelper.Join(joiner, aliasedIdColumns))
.Add("=")
.AddParameter()
.Add(WhereJoinFragment(RootAlias, true, false))
.ToSqlString();
// TODO H3: this is commented out in H3.2
if (IsVersioned)
{
whereClause.Append(" and ")
.Append(VersionColumnName)
.Append("=?");
}
return select.SetSelectClause(selectClause)
.SetFromClause(fromClause)
.SetOuterJoins(SqlString.Empty, SqlString.Empty)
.SetWhereClause(whereClause)
.ToSqlString();
}
