/// <summary>
/// Generate the SQL DELETE that deletes a particular row
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateDeleteRowString()
{
SqlDeleteBuilder delete = new SqlDeleteBuilder(Factory.Dialect, Factory);
delete.SetTableName(qualifiedTableName);
if (hasIdentifier)
{
delete.AddWhereFragment(new string[] { IdentifierColumnName }, IdentifierType, " = ");
}
else if (HasIndex && !indexContainsFormula)
{
delete
.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);
delete.AddWhereFragment(cnames, ctypes, " = ");
}
if (Factory.Settings.IsCommentsEnabled)
delete.SetComment("delete collection row " + Role);
return delete.ToSqlCommandInfo();
}
public void MultipleValuesOr()
{
// Creating node hierarchy.
var node = new Node();
node.Add(new Node("table", "foo"));
var where = new Node("where");
var or = new Node("or");
or.Add(new Node("field1", "value1"));
or.Add(new Node("field2", "value2"));
where.Add(or);
node.Add(where);
var builder = new SqlDeleteBuilder(node, "'");
// Extracting SQL + params, and asserting correctness.
var result = builder.Build();
var sql = result.Get <string>();
Assert.Equal("delete from 'foo' where 'field1' = @0 or 'field2' = @1", sql);
var arg1 = result.Children.First();
Assert.Equal("@0", arg1.Name);
Assert.Equal("value1", arg1.Get <string>());
var arg2 = result.Children.Skip(1).First();
Assert.Equal("@1", arg2.Name);
Assert.Equal("value2", arg2.Get <string>());
}
public void DeleteSqlStringTest()
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory();
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlDeleteBuilder delete = new SqlDeleteBuilder(factoryImpl.Dialect, 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");
SqlCommandInfo sqlCommand = delete.ToSqlCommandInfo();
string expectedSql = "DELETE FROM test_delete_builder WHERE decimalColumn = ? AND versionColumn = ? AND a=b";
Assert.AreEqual(expectedSql, sqlCommand.Text.ToString(), "SQL String");
SqlType[] actualParameterTypes = sqlCommand.ParameterTypes;
Assert.AreEqual(2, actualParameterTypes.Length, "Two parameters");
Assert.AreEqual(SqlTypeFactory.Decimal, actualParameterTypes[0], "firstParam Type");
Assert.AreEqual(SqlTypeFactory.Int32, actualParameterTypes[1], "secondParam Type");
}
/// <summary>
/// Implementation of your slot.
/// </summary>
/// <param name="signaler">Signaler used to raise the signal.</param>
/// <param name="input">Arguments to your slot.</param>
public void Signal(ISignaler signaler, Node input)
{
var builder = new SqlDeleteBuilder(input, "'");
var result = builder.Build();
input.Value = result.Value;
input.Clear();
input.AddRange(result.Children.ToList());
}
/// <summary>
/// Generate the SQL DELETE that deletes all rows
/// </summary>
/// <returns></returns>
protected override SqlString GenerateDeleteString()
{
SqlDeleteBuilder delete = new SqlDeleteBuilder(factory)
.SetTableName(qualifiedTableName)
.SetIdentityColumn(KeyColumnNames, KeyType);
if (HasWhere)
{
delete.AddWhereFragment(sqlWhereString);
}
return(delete.ToSqlString());
}
/// <summary>
/// Generate the SQL DELETE that deletes all rows
/// </summary>
/// <returns></returns>
protected override SqlCommandInfo GenerateDeleteString()
{
SqlDeleteBuilder delete = new SqlDeleteBuilder(Factory.Dialect, Factory)
.SetTableName(qualifiedTableName)
.SetIdentityColumn(KeyColumnNames, KeyType);
if (HasWhere)
delete.AddWhereFragment(sqlWhereString);
if (Factory.Settings.IsCommentsEnabled)
delete.SetComment("delete collection " + Role);
return delete.ToSqlCommandInfo();
}
// Generate all the SQL
/// <summary>
/// Generate the SQL that deletes a row by id (and version)
/// </summary>
/// <returns>A SqlString for a Delete</returns>
protected virtual SqlString GenerateDeleteString()
{
SqlDeleteBuilder deleteBuilder = new SqlDeleteBuilder(factory);
deleteBuilder.SetTableName(TableName)
.SetIdentityColumn(IdentifierColumnNames, IdentifierType);
if (IsVersioned)
{
deleteBuilder.SetVersionColumn(new string[] { VersionColumnName }, VersionType);
}
return(deleteBuilder.ToSqlString());
}
public void WrongBooleanGroup_Throws()
{
// Creating node hierarchy.
var node = new Node();
node.Add(new Node("table", "foo"));
var where = new Node("where");
var and = new Node("xor");
and.Add(new Node("field1", "value1"));
where.Add(and);
node.Add(where);
var builder = new SqlDeleteBuilder(node, "'");
Assert.Throws <ArgumentException>(() => builder.Build());
}
/// <inheritdoc />
protected override SqlCommandInfo GenerateDeleteRowString(bool[] columnNullness)
{
var delete = new SqlDeleteBuilder(Factory.Dialect, Factory);
delete.SetTableName(qualifiedTableName);
if (hasIdentifier)
{
delete.AddWhereFragment(new[] { IdentifierColumnName }, IdentifierType, " = ");
}
else
{
var useIndex = HasIndex && !indexContainsFormula;
var additionalFilterType = useIndex ? IndexType : ElementType;
var additionalFilterColumns = useIndex ? IndexColumnNames : ElementColumnNames;
var includes = useIndex ? null : Combine(elementColumnIsInPrimaryKey, columnNullness);
var cnames = includes == null
? ArrayHelper.Join(KeyColumnNames, additionalFilterColumns)
: ArrayHelper.Join(KeyColumnNames, additionalFilterColumns, includes);
var ctypes = includes == null
? ArrayHelper.Join(KeyType.SqlTypes(Factory), additionalFilterType.SqlTypes(Factory))
: ArrayHelper.Join(KeyType.SqlTypes(Factory), additionalFilterType.SqlTypes(Factory), includes);
delete.AddWhereFragment(cnames, ctypes, " = ");
if (columnNullness != null)
{
for (var i = 0; i < columnNullness.Length; i++)
{
if (columnNullness[i])
{
continue;
}
delete.AddWhereFragment($"{additionalFilterColumns[i]} is null");
}
}
}
if (Factory.Settings.IsCommentsEnabled)
{
delete.SetComment("delete collection row " + Role);
}
return(delete.ToSqlCommandInfo());
}
/// <summary>
/// Generate the SQL DELETE that deletes a particular row
/// </summary>
/// <returns></returns>
protected override SqlString GenerateDeleteRowString()
{
SqlDeleteBuilder delete = new SqlDeleteBuilder(factory);
delete.SetTableName(qualifiedTableName);
if (hasIdentifier)
{
delete.AddWhereFragment(rowSelectColumnNames, rowSelectType, " = ");
}
else
{
delete.AddWhereFragment(KeyColumnNames, KeyType, " = ")
.AddWhereFragment(rowSelectColumnNames, rowSelectType, " = ");
}
return(delete.ToSqlString());
}
private void ConstructBody()
{
CSBlock usingBlock = new CSBlock("using (SqlCommand sqlCommand = new SqlCommand())");
string sqlString = $"{Quotes}{SqlDeleteBuilder.DeleteByColumn(datatable, datacolumn)}{Quotes};";
string parameterName = $"{Quotes}@{datacolumn.ColumnName.Replace(" ","")}{Quotes}";
string parameterValue = ProperVarName.Get(datacolumn.ColumnName.ToLower());
usingBlock.Statements.Add($"sqlCommand.CommandText = {sqlString}");
if (datacolumn.AllowDBNull)
{
parameterValue += " ?? (object)DBNull.Value";
}
usingBlock.Statements.Add($"sqlCommand.Parameters.AddWithValue({parameterName}, {parameterValue});");
usingBlock.Statements.Add($"table.DbAccess.Commands.Add(sqlCommand);");
this.Statements.Add(usingBlock.ToString());
}
private void ConstructBody()
{
VBBlock usingBlock = new VBBlock(VBBlockStatement.Using, "Using sqlCommand As New SqlCommand()");
string sqlString = $"{Quotes}{SqlDeleteBuilder.DeleteByColumn(datatable, datacolumn)}{Quotes}";
string parameterName = $"{Quotes}@{datacolumn.ColumnName.Replace(" ", "")}{Quotes}";
string parameterValue = ProperVarName.Get(datacolumn.ColumnName.ToLower());
usingBlock.Statements.Add($"sqlCommand.CommandText = {sqlString}");
if (datacolumn.AllowDBNull)
{
parameterValue = $"If({parameterValue}, CType(DBNull.Value, Object))";
}
usingBlock.Statements.Add($"sqlCommand.Parameters.AddWithValue({parameterName}, {parameterValue})");
usingBlock.Statements.Add($"table.DbAccess.Commands.Add(sqlCommand)");
this.Statements.Add(usingBlock.ToString());
}
private void ConstructBody()
{
VBBlock usingBlock = new VBBlock(VBBlockStatement.Using, "Using sqlCommand As New SqlCommand()");
string sqlString = $"{Quotes}{SqlDeleteBuilder.DeleteByPrimaryKey(datatable)}{Quotes}";
string parameterName = string.Empty;
string parameterValue = string.Empty;
usingBlock.Statements.Add($"sqlCommand.CommandText = {sqlString}");
foreach (DataColumn clm in this.datatable.PrimaryKey)
{
parameterName = $"{Quotes}@{clm.ColumnName}{Quotes}";
parameterValue = GetParameterValue(clm);
usingBlock.Statements.Add($"sqlCommand.Parameters.AddWithValue({parameterName}, {parameterValue})");
}
usingBlock.Statements.Add($"table.DbAccess.Commands.Add(sqlCommand)");
this.Statements.Add(usingBlock.ToString());
}
public void Signal(Node input)
{
var builder = new SqlDeleteBuilder(input, _signaler);
var sqlNode = builder.Build();
// Checking if this is a "build only" invocation.
if (builder.IsGenerateOnly)
{
input.Value = sqlNode.Value;
input.Clear();
input.AddRange(sqlNode.Children.ToList());
return;
}
// Executing SQL, now parametrized.
Executor.Execute(sqlNode, _connections, _signaler, (cmd) =>
{
input.Value = cmd.ExecuteNonQuery();
input.Clear();
});
}
public MultiTableDeleteExecutor(IStatement statement)
: base(statement, log)
{
if (!Factory.Dialect.SupportsTemporaryTables)
{
throw new HibernateException("cannot perform multi-table deletes using dialect not supporting temp tables");
}
var deleteStatement = (DeleteStatement)statement;
FromElement fromElement = deleteStatement.FromClause.GetFromElement();
string bulkTargetAlias = fromElement.TableAlias;
persister = fromElement.Queryable;
idInsertSelect = GenerateIdInsertSelect(persister, bulkTargetAlias, deleteStatement.WhereClause);
log.Debug("Generated ID-INSERT-SELECT SQL (multi-table delete) : {0}", idInsertSelect);
string[] tableNames = persister.ConstraintOrderedTableNameClosure;
string[][] columnNames = persister.ConstraintOrderedTableKeyColumnClosure;
string idSubselect = GenerateIdSubselect(persister);
deletes = new SqlString[tableNames.Length];
for (int i = tableNames.Length - 1; i >= 0; i--)
{
// TODO : an optimization here would be to consider cascade deletes and not gen those delete statements;
// the difficulty is the ordering of the tables here vs the cascade attributes on the persisters ->
// the table info gotten here should really be self-contained (i.e., a class representation
// defining all the needed attributes), then we could then get an array of those
SqlDeleteBuilder delete = new SqlDeleteBuilder(Factory.Dialect, Factory)
.SetTableName(tableNames[i])
.SetWhere("(" + string.Join(", ", columnNames[i]) + ") IN (" + idSubselect + ")");
if (Factory.Settings.IsCommentsEnabled)
{
delete.SetComment("bulk delete");
}
deletes[i] = delete.ToSqlString();
}
}
public void BootStrapTheApplication()
{
EventStoreDatabaseBootStrapper.BootStrap();
ReportingDatabaseBootStrapper.BootStrap();
var bus = new FakeBus();
ServiceLocator.Bus = bus;
//var storage = new InMemoryEventStore(bus);
//var storage = new NHibernateSqliteEventStore(bus, NHibernateSessionHelper.CreateSessionFactory(), new BinaryFormatter());
var storage = new SqliteEventStore(bus, new BinaryFormatter(), "Data Source=eventStore.db3");
var rep = new DomainRepository<InventoryItem>(storage);
var commands = new InventoryCommandHandlers(rep);
bus.RegisterHandler<CheckInItemsToInventory>(commands.Handle);
bus.RegisterHandler<CreateInventoryItem>(commands.Handle);
bus.RegisterHandler<DeactivateInventoryItem>(commands.Handle);
bus.RegisterHandler<RemoveItemsFromInventory>(commands.Handle);
bus.RegisterHandler<RenameInventoryItem>(commands.Handle);
var sqlInsertBuilder = new SqlInsertBuilder();
var sqlSelectBuilder = new SqlSelectBuilder();
var sqlUpdateBuilder = new SqlUpdateBuilder();
var sqlDeleteBuilder = new SqlDeleteBuilder();
var reportingRepository = new SQLiteReportingRepository("Data Source=reportingDataBase.db3", sqlSelectBuilder,
sqlInsertBuilder, sqlUpdateBuilder, sqlDeleteBuilder);
var detail = new InventoryItemDetailViewHandler(reportingRepository);
ServiceLocator.ReportingRepository = reportingRepository;
bus.RegisterHandler<InventoryItemCreated>(detail.Handle);
bus.RegisterHandler<InventoryItemDeactivated>(detail.Handle);
bus.RegisterHandler<InventoryItemRenamed>(detail.Handle);
bus.RegisterHandler<ItemsCheckedInToInventory>(detail.Handle);
bus.RegisterHandler<ItemsRemovedFromInventory>(detail.Handle);
var list = new InventoryItemListViewHandler(reportingRepository);
bus.RegisterHandler<InventoryItemCreated>(list.Handle);
bus.RegisterHandler<InventoryItemRenamed>(list.Handle);
bus.RegisterHandler<InventoryItemDeactivated>(list.Handle);
}
public void SingleWhere()
{
// Creating node hierarchy.
var node = new Node();
node.Add(new Node("table", "foo"));
var where = new Node("where");
var and = new Node("and");
and.Add(new Node("field1", "value1"));
where.Add(and);
node.Add(where);
var builder = new SqlDeleteBuilder(node, "'");
// Extracting SQL + params, and asserting correctness.
var result = builder.Build();
var sql = result.Get <string>();
Assert.Equal("delete from 'foo' where 'field1' = @0", sql);
var arg1 = result.Children.First();
Assert.Equal("@0", arg1.Name);
Assert.Equal("value1", arg1.Get <string>());
}
public void SetUp()
{
_sqlDeleteBuilder = new SqlDeleteBuilder();
}
public SqlDeleteOperation(SqlDataSet <TEntity> dataSet, IQueryProvider queryProvider) : base(dataSet, queryProvider)
{
_queryBuilder = new SqlDeleteBuilder <TEntity>(DataSet.DataModel);
}
public void SetUp()
{
_sqlDeleteBuilder = new SqlDeleteBuilder();
}
