/// <summary>
/// Generate the SQL UPDATE that updates all the foreign keys to null
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateDeleteString()
{
var update = new SqlUpdateBuilder(Factory.Dialect, Factory)
.SetTableName(qualifiedTableName)
.AddColumns(JoinColumnNames, "null");
if (CollectionType.UseLHSPrimaryKey)
{
update.SetIdentityColumn(KeyColumnNames, KeyType);
}
else
{
var ownerPersister = (IOuterJoinLoadable)OwnerEntityPersister;
update.SetJoin(ownerPersister.TableName, KeyColumnNames, KeyType, JoinColumnNames, ownerPersister.GetPropertyColumnNames(CollectionType.LHSPropertyName));
}
if (HasIndex)
update.AddColumns(IndexColumnNames, "null");
if (HasWhere)
update.AddWhereFragment(sqlWhereString);
if (Factory.Settings.IsCommentsEnabled)
update.SetComment("delete one-to-many " + Role);
return update.ToSqlCommandInfo();
}
public MultiTableUpdateExecutor(IStatement statement) : base(statement, log)
{
if (!Factory.Dialect.SupportsTemporaryTables)
{
throw new HibernateException("cannot perform multi-table updates using dialect not supporting temp tables");
}
var updateStatement = (UpdateStatement) statement;
FromElement fromElement = updateStatement.FromClause.GetFromElement();
string bulkTargetAlias = fromElement.TableAlias;
persister = fromElement.Queryable;
idInsertSelect = GenerateIdInsertSelect(persister, bulkTargetAlias, updateStatement.WhereClause);
log.Debug("Generated ID-INSERT-SELECT SQL (multi-table update) : " + idInsertSelect);
string[] tableNames = persister.ConstraintOrderedTableNameClosure;
string[][] columnNames = persister.ConstraintOrderedTableKeyColumnClosure;
string idSubselect = GenerateIdSubselect(persister);
IList<AssignmentSpecification> assignmentSpecifications = Walker.AssignmentSpecifications;
updates = new SqlString[tableNames.Length];
hqlParameters = new IParameterSpecification[tableNames.Length][];
for (int tableIndex = 0; tableIndex < tableNames.Length; tableIndex++)
{
bool affected = false;
var parameterList = new List<IParameterSpecification>();
SqlUpdateBuilder update =
new SqlUpdateBuilder(Factory.Dialect, Factory).SetTableName(tableNames[tableIndex])
.SetWhere(
string.Format("({0}) IN ({1})", StringHelper.Join(", ", columnNames[tableIndex]), idSubselect));
if (Factory.Settings.IsCommentsEnabled)
{
update.SetComment("bulk update");
}
foreach (var specification in assignmentSpecifications)
{
if (specification.AffectsTable(tableNames[tableIndex]))
{
affected = true;
update.AppendAssignmentFragment(specification.SqlAssignmentFragment);
if (specification.Parameters != null)
{
for (int paramIndex = 0; paramIndex < specification.Parameters.Length; paramIndex++)
{
parameterList.Add(specification.Parameters[paramIndex]);
}
}
}
}
if (affected)
{
updates[tableIndex] = update.ToSqlString();
hqlParameters[tableIndex] = parameterList.ToArray();
}
}
}
/// <summary>
/// Generate the SQL UPDATE that updates a foreign key to a value
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateInsertRowString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder(Factory);
update.SetTableName(qualifiedTableName)
.AddColumns(KeyColumnNames, KeyType)
.SetIdentityColumn(ElementColumnNames, ElementType);
if (HasIndex)
{
update.AddColumns(IndexColumnNames, IndexType);
}
//identifier collections not supported for 1-to-many
return update.ToSqlCommandInfo();
}
private SqlString GenerateLockString()
{
ISessionFactoryImplementor factory = lockable.Factory;
SqlUpdateBuilder update = new SqlUpdateBuilder(factory.Dialect, factory);
update.SetTableName(lockable.RootTableName);
update.SetIdentityColumn(lockable.RootTableIdentifierColumnNames, lockable.IdentifierType);
update.SetVersionColumn(new string[] { lockable.VersionColumnName }, lockable.VersionType);
update.AddColumns(new string[] { lockable.VersionColumnName }, null, lockable.VersionType);
if (factory.Settings.IsCommentsEnabled)
{
update.SetComment(lockMode + " lock " + lockable.EntityName);
}
return update.ToSqlString();
}
/// <summary>
/// Generate the SQL UPDATE that updates a particular row's foreign
/// key to null
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateDeleteRowString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder(Factory);
update.SetTableName(qualifiedTableName)
.AddColumns(KeyColumnNames, "null");
if (HasIndex /* && TODO H3: !indexContainsFormula */)
{
update.AddColumns(IndexColumnNames, "null");
}
update.AddWhereFragment(KeyColumnNames, KeyType, " = ")
.AddWhereFragment(ElementColumnNames, ElementType, " = ");
return update.ToSqlCommandInfo();
}
/// <summary>
/// Generate the SQL UPDATE that updates all the foreign keys to null
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateDeleteString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder(Factory.Dialect, Factory)
.SetTableName(qualifiedTableName)
.AddColumns(KeyColumnNames, "null")
.SetIdentityColumn(KeyColumnNames, KeyType);
if (HasIndex)
update.AddColumns(IndexColumnNames, "null");
if (HasWhere)
update.AddWhereFragment(sqlWhereString);
if (Factory.Settings.IsCommentsEnabled)
update.SetComment("delete one-to-many " + Role);
return update.ToSqlCommandInfo();
}
/// <summary>
/// Generate the SQL UPDATE that updates all the foreign keys to null
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateDeleteString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder(Factory)
.SetTableName(qualifiedTableName)
.AddColumns(KeyColumnNames, "null")
.SetIdentityColumn(KeyColumnNames, KeyType);
if (HasIndex)
{
update.AddColumns(IndexColumnNames, "null");
}
if (HasWhere)
{
update.AddWhereFragment(sqlWhereString);
}
return update.ToSqlCommandInfo();
}
/// <summary>
/// Generate the SQL UPDATE that updates a row
/// </summary>
/// <returns></returns>
protected override SqlString GenerateUpdateRowString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder( factory )
.SetTableName( qualifiedTableName )
.AddColumns( ElementColumnNames, ElementType );
if( hasIdentifier )
{
update.AddWhereFragment( rowSelectColumnNames, rowSelectType, " = " );
}
else
{
update.AddWhereFragment( KeyColumnNames, KeyType, " = " )
.AddWhereFragment( rowSelectColumnNames, rowSelectType, " = " );
}
return update.ToSqlString();
}
public void UpdateStringSqlTest()
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory();
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor) factory;
SqlUpdateBuilder update = new SqlUpdateBuilder(factoryImpl.Dialect, factoryImpl);
update.SetTableName("test_update_builder");
update.AddColumns(new string[] {"intColumn"}, NHibernateUtil.Int32);
update.AddColumns(new string[] {"longColumn"}, NHibernateUtil.Int64);
update.AddColumn("literalColumn", false, (ILiteralType) NHibernateUtil.Boolean);
update.AddColumn("stringColumn", 5.ToString());
update.SetIdentityColumn(new string[] {"decimalColumn"}, NHibernateUtil.Decimal);
update.SetVersionColumn(new string[] {"versionColumn"}, (IVersionType) NHibernateUtil.Int32);
update.AddWhereFragment("a=b");
SqlCommandInfo sqlCommand = update.ToSqlCommandInfo();
Assert.AreEqual(CommandType.Text, sqlCommand.CommandType);
string falseString = factoryImpl.Dialect.ToBooleanValueString(false);
string expectedSql =
"UPDATE test_update_builder SET intColumn = ?, longColumn = ?, literalColumn = " + falseString + ", stringColumn = 5 WHERE decimalColumn = ? AND versionColumn = ? AND a=b";
Assert.AreEqual(expectedSql, sqlCommand.Text.ToString(), "SQL String");
SqlType[] actualParameterTypes = sqlCommand.ParameterTypes;
Assert.AreEqual(4, actualParameterTypes.Length, "Four parameters");
SqlType[] expectedParameterTypes = new SqlType[]
{
SqlTypeFactory.Int32,
SqlTypeFactory.Int64,
SqlTypeFactory.Decimal,
SqlTypeFactory.Int32
};
Assert.AreEqual(expectedParameterTypes[0], actualParameterTypes[0], "firstParam Type");
Assert.AreEqual(expectedParameterTypes[1], actualParameterTypes[1], "secondParam Type");
Assert.AreEqual(expectedParameterTypes[2], actualParameterTypes[2], "thirdParam Type");
Assert.AreEqual(expectedParameterTypes[3], actualParameterTypes[3], "fourthParam Type");
}
/// <summary>
/// Generate the SQL UPDATE that updates a row
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateUpdateRowString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder(Factory)
.SetTableName(qualifiedTableName)
.AddColumns(ElementColumnNames, elementColumnIsSettable, ElementType);
if (hasIdentifier)
{
update.AddWhereFragment(new string[] {IdentifierColumnName}, IdentifierType, " = ");
}
else if (HasIndex /* && !indexContainsFormula */)
{
update.AddWhereFragment(KeyColumnNames, KeyType, " = ")
.AddWhereFragment(IndexColumnNames, IndexType, " = ");
}
else
{
update.AddWhereFragment(KeyColumnNames, KeyType, " = ")
.AddWhereFragment(ElementColumnNames, ElementType, " = ");
}
return update.ToSqlCommandInfo();
}
/// <summary> Generate the SQL that updates a row by id (and version)</summary>
protected internal SqlCommandInfo GenerateUpdateString(bool[] includeProperty, int j, object[] oldFields, bool useRowId)
{
SqlUpdateBuilder updateBuilder = new SqlUpdateBuilder(Factory.Dialect, Factory)
.SetTableName(GetTableName(j));
// select the correct row by either pk or rowid
if (useRowId)
updateBuilder.SetIdentityColumn(new string[] { rowIdName }, NHibernateUtil.Int32); //TODO: eventually, rowIdName[j]
else
updateBuilder.SetIdentityColumn(GetKeyColumns(j), IdentifierType);
bool hasColumns = false;
for (int i = 0; i < entityMetamodel.PropertySpan; i++)
{
if (includeProperty[i] && IsPropertyOfTable(i, j))
{
// this is a property of the table, which we are updating
updateBuilder.AddColumns(GetPropertyColumnNames(i), propertyColumnUpdateable[i], PropertyTypes[i]);
hasColumns = hasColumns || GetPropertyColumnSpan(i) > 0;
}
}
if (j == 0 && IsVersioned && entityMetamodel.OptimisticLockMode == Versioning.OptimisticLock.Version)
{
// this is the root (versioned) table, and we are using version-based
// optimistic locking; if we are not updating the version, also don't
// check it (unless this is a "generated" version column)!
if (CheckVersion(includeProperty))
{
updateBuilder.SetVersionColumn(new string[] { VersionColumnName }, VersionType);
hasColumns = true;
}
}
else if (entityMetamodel.OptimisticLockMode > Versioning.OptimisticLock.Version && oldFields != null)
{
// we are using "all" or "dirty" property-based optimistic locking
bool[] includeInWhere =
OptimisticLockMode == Versioning.OptimisticLock.All
? PropertyUpdateability
: includeProperty; //optimistic-lock="dirty", include all properties we are updating this time
bool[] versionability = PropertyVersionability;
IType[] types = PropertyTypes;
for (int i = 0; i < entityMetamodel.PropertySpan; i++)
{
bool include = includeInWhere[i] && IsPropertyOfTable(i, j) && versionability[i];
if (include)
{
// this property belongs to the table, and it is not specifically
// excluded from optimistic locking by optimistic-lock="false"
string[] _propertyColumnNames = GetPropertyColumnNames(i);
bool[] propertyNullness = types[i].ToColumnNullness(oldFields[i], Factory);
SqlType[] sqlt = types[i].SqlTypes(Factory);
for (int k = 0; k < propertyNullness.Length; k++)
{
if (propertyNullness[k])
{
updateBuilder.AddWhereFragment(_propertyColumnNames[k], sqlt[k], " = ");
}
else
{
updateBuilder.AddWhereFragment(_propertyColumnNames[k] + " is null");
}
}
}
}
}
if (Factory.Settings.IsCommentsEnabled)
{
updateBuilder.SetComment("update " + EntityName);
}
return hasColumns ? updateBuilder.ToSqlCommandInfo() : null;
}
private SqlCommandInfo GenerateVersionIncrementUpdateString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder(Factory.Dialect, Factory);
update.SetTableName(GetTableName(0));
if (Factory.Settings.IsCommentsEnabled)
{
update.SetComment("forced version increment");
}
update.AddColumn(VersionColumnName, VersionType);
update.SetIdentityColumn(IdentifierColumnNames, IdentifierType);
update.SetVersionColumn(new string[] { VersionColumnName }, VersionType);
return update.ToSqlCommandInfo();
}
/// <summary>
/// Generate the SQL UPDATE that updates a foreign key to a value
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateInsertRowString()
{
var update = new SqlUpdateBuilder(Factory.Dialect, Factory);
update.SetTableName(qualifiedTableName)
.AddColumns(KeyColumnNames, KeyType);
if (HasIndex && !indexContainsFormula)
update.AddColumns(IndexColumnNames, IndexType);
//identifier collections not supported for 1-to-many
if (Factory.Settings.IsCommentsEnabled)
update.SetComment("create one-to-many row " + Role);
update.SetIdentityColumn(ElementColumnNames, ElementType);
return update.ToSqlCommandInfo();
}
/// <summary>
/// Generate the SQL UPDATE that updates a particular row's foreign
/// key to null
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateDeleteRowString()
{
var update = new SqlUpdateBuilder(Factory.Dialect, Factory);
update.SetTableName(qualifiedTableName)
.AddColumns(KeyColumnNames, "null");
if (HasIndex && !indexContainsFormula)
update.AddColumns(IndexColumnNames, "null");
if (Factory.Settings.IsCommentsEnabled)
update.SetComment("delete one-to-many row " + Role);
//use a combination of foreign key columns and pk columns, since
//the ordering of removal and addition is not guaranteed when
//a child moves from one parent to another
update
.AddWhereFragment(KeyColumnNames, KeyType, " = ")
.AddWhereFragment(ElementColumnNames, ElementType, " = ");
return update.ToSqlCommandInfo();
}
protected virtual SqlCommandInfo GenerateUpdateString(bool[] includeProperty, int j, object[] oldFields)
{
SqlUpdateBuilder updateBuilder = new SqlUpdateBuilder(Factory)
.SetTableName(GetTableName(j))
.SetIdentityColumn(GetKeyColumns(j), IdentifierType);
bool hasColumns = false;
for (int i = 0; i < entityMetamodel.PropertySpan; i++)
{
if (includeProperty[i] && IsPropertyOfTable(i, j))
{
updateBuilder.AddColumns(GetPropertyColumnNames(i), propertyColumnUpdateable[i], PropertyTypes[i]);
hasColumns = hasColumns || GetPropertyColumnSpan(i) > 0;
}
}
if (j == 0 && IsVersioned && entityMetamodel.OptimisticLockMode == OptimisticLockMode.Version)
{
updateBuilder.SetVersionColumn(new string[] {VersionColumnName}, VersionType);
hasColumns = true;
}
else if (entityMetamodel.OptimisticLockMode > OptimisticLockMode.Version && oldFields != null)
{
bool[] versionability = PropertyVersionability;
bool[] includeInWhere =
OptimisticLockMode == OptimisticLockMode.All
? PropertyUpdateability
: includeProperty;
for (int i = 0; i < entityMetamodel.PropertySpan; i++)
{
bool include = includeInWhere[i] &&
IsPropertyOfTable(i, j) &&
versionability[i];
if (include)
{
string[] propertyColumnNames = GetPropertyColumnNames(i);
if (PropertyTypes[i].IsDatabaseNull(oldFields[i]))
{
foreach (string column in propertyColumnNames)
{
updateBuilder.AddWhereFragment(column + " is null");
}
}
else
{
updateBuilder.AddWhereFragment(propertyColumnNames, PropertyTypes[i], "=");
}
}
}
}
return hasColumns ? updateBuilder.ToSqlCommandInfo() : null;
}
/// <summary>
/// Generate the SQL that updates rows by id (and version)
/// </summary>
/// <param name="includeProperty"></param>
/// <returns>An array of SqlStrings</returns>
protected virtual SqlString[ ] GenerateUpdateStrings( bool[ ] includeProperty )
{
SqlString[ ] results = new SqlString[naturalOrderTableNames.Length];
for( int j = 0; j < naturalOrderTableNames.Length; j++ )
{
SqlUpdateBuilder updateBuilder = new SqlUpdateBuilder( factory )
.SetTableName( naturalOrderTableNames[ j ] )
.SetIdentityColumn( naturalOrderTableKeyColumns[ j ], IdentifierType );
if( j == 0 && IsVersioned )
{
updateBuilder.SetVersionColumn( new string[ ] {VersionColumnName}, VersionType );
}
//TODO: figure out what the hasColumns variable comes into play for??
bool hasColumns = false;
for( int i = 0; i < propertyColumnNames.Length; i++ )
{
if( includeProperty[ i ] && naturalOrderPropertyTables[ i ] == j )
{
updateBuilder.AddColumns( propertyColumnNames[ i ], PropertyTypes[ i ] );
hasColumns = hasColumns || propertyColumnNames[ i ].Length > 0;
}
}
results[ j ] = hasColumns ? updateBuilder.ToSqlString() : null;
}
return results;
}
/// <summary>
/// Generate the SQL UPDATE that updates a row
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateUpdateRowString()
{
SqlUpdateBuilder update = new SqlUpdateBuilder(Factory.Dialect, Factory)
.SetTableName(qualifiedTableName)
.AddColumns(ElementColumnNames, elementColumnIsSettable, ElementType);
if (hasIdentifier)
{
update.AddWhereFragment(new string[] { IdentifierColumnName }, IdentifierType, " = ");
}
else if (HasIndex && !indexContainsFormula)
{
update.AddWhereFragment(KeyColumnNames, KeyType, " = ")
.AddWhereFragment(IndexColumnNames, IndexType, " = ");
}
else
{
string[] cnames = ArrayHelper.Join(KeyColumnNames, ElementColumnNames, elementColumnIsInPrimaryKey);
SqlType[] ctypes = ArrayHelper.Join(KeyType.SqlTypes(Factory), ElementType.SqlTypes(Factory), elementColumnIsInPrimaryKey);
update.AddWhereFragment(cnames, ctypes, " = ");
}
if (Factory.Settings.IsCommentsEnabled)
update.SetComment("update collection row " + Role);
return update.ToSqlCommandInfo();
}
