public string BuildCleanUpQuery(SchemaNode generatorNode, NodeConfiguration nodeConfiguration)
{
var table = (Table)generatorNode;
var delete = SqlDml.Delete(SqlDml.TableRef(table));
return(Compile(delete, nodeConfiguration).GetCommandText());
}
private QueryCommand CreateCommand(QueryTranslationResult request)
{
var delete = SqlDml.Delete(SqlDml.TableRef(PrimaryIndexes[0].Table));
Join(delete, (SqlSelect)request.Query);
return(ToCommand(delete));
}
public virtual void ReferentialConstraintTest()
{
var masterTable = schema.CreateTable(MasterTableName);
var masterId = CreatePrimaryKey(masterTable);
var slaveTable = schema.CreateTable(SlaveTableName);
var slaveId = CreatePrimaryKey(slaveTable);
var fk = slaveTable.CreateForeignKey("foreign_me");
fk.ReferencedTable = masterTable;
fk.ReferencedColumns.Add(masterId);
fk.Columns.Add(slaveId);
ExecuteNonQuery(SqlDdl.Create(masterTable));
ExecuteNonQuery(SqlDdl.Create(slaveTable));
var slaveTableRef = SqlDml.TableRef(slaveTable);
var slaveInsert = SqlDml.Insert(slaveTableRef);
slaveInsert.Values.Add(slaveTableRef[IdColumnName], 1);
AssertExceptionType(slaveInsert, SqlExceptionType.ReferentialConstraintViolation);
var masterTableRef = SqlDml.TableRef(masterTable);
var masterInsert = SqlDml.Insert(masterTableRef);
masterInsert.Values.Add(masterTableRef[IdColumnName], 1);
ExecuteNonQuery(masterInsert);
ExecuteNonQuery(slaveInsert);
var masterDelete = SqlDml.Delete(masterTableRef);
masterDelete.Where = masterTableRef[IdColumnName] == 1;
AssertExceptionType(masterDelete, SqlExceptionType.ReferentialConstraintViolation);
}
private ISqlCompileUnit GetTableBasedNextImplementation(SchemaNode generatorNode)
{
var table = (Table)generatorNode;
var idColumn = GetColumn(table, WellKnown.GeneratorColumnName);
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
var delete = SqlDml.Delete(tableRef);
if (!hasInsertDefaultValues)
{
var fakeColumn = GetColumn(table, WellKnown.GeneratorFakeColumnName);
insert.Values[tableRef[fakeColumn.Name]] = SqlDml.Null;
}
var result = SqlDml.Batch();
if (storesAutoIncrementSettingsInMemory)
{
result.Add(delete);
}
result.Add(insert);
result.Add(SqlDml.Select(SqlDml.LastAutoGeneratedId()));
return(result);
}
public void DeleteTest01()
{
var tableRef = SqlDml.TableRef(Catalog.DefaultSchema.Tables["Department"]);
var update = SqlDml.Delete(tableRef);
Console.WriteLine(Driver.Compile(update).GetCommandText());
using (var command = Connection.CreateCommand(update)) {
command.ExecuteNonQuery();
}
}
public void RowDeletionTest()
{
var defaultSchema = GetSchema();
var tableRef = SqlDml.TableRef(defaultSchema.Tables[TableName]);
var deleteQuery = SqlDml.Delete(tableRef);
deleteQuery.Where = tableRef["Id"] == 1;
TestQueryNamesReadable(deleteQuery, defaultSchema);
}
public void DeleteTest02()
{
var tableRef = SqlDml.TableRef(Catalog.DefaultSchema.Tables["Department"]);
var update = SqlDml.Delete(tableRef);
update.Where = SqlDml.Equals(tableRef.Columns["Name"], SqlDml.Literal("Human Resources Department"));
Console.WriteLine(Driver.Compile(update).GetCommandText());
using (var command = Connection.CreateCommand(update)) {
command.ExecuteNonQuery();
}
}
/// <summary>
/// Builds the descriptor of a temporary table.
/// </summary>
/// <param name="modelMapping">Model mapping.</param>
/// <param name="name">The name of the temporary table.</param>
/// <param name="source">The source.</param>
/// <param name="fieldNames">The names of field in temporary table.</param>
/// <returns>Built descriptor.</returns>
public TemporaryTableDescriptor BuildDescriptor(ModelMapping modelMapping, string name, TupleDescriptor source, string[] fieldNames)
{
EnsureTemporaryTablesSupported();
var hasColumns = source.Count > 0;
// TODO: split this method to a set of various simple virtual methods
var driver = Handlers.StorageDriver;
var catalog = new Catalog(modelMapping.TemporaryTableDatabase);
var schema = catalog.CreateSchema(modelMapping.TemporaryTableSchema);
var collation = modelMapping.TemporaryTableCollation != null
? new Collation(schema, modelMapping.TemporaryTableCollation)
: null;
if (fieldNames == null)
{
fieldNames = BuildFieldNames(source);
}
var typeMappings = source
.Select(driver.GetTypeMapping)
.ToArray();
// table
var table = CreateTemporaryTable(schema, name, source, typeMappings, fieldNames, collation);
var tableRef = SqlDml.TableRef(table);
// select statement
var queryStatement = MakeUpSelectQuery(tableRef, hasColumns);
// insert statement
var storeRequestBindings = new List <PersistParameterBinding>();
var insertStatement = MakeUpInsertQuery(tableRef, typeMappings, storeRequestBindings, hasColumns);
var result = new TemporaryTableDescriptor(name)
{
TupleDescriptor = source,
QueryStatement = queryStatement,
CreateStatement = driver.Compile(SqlDdl.Create(table)).GetCommandText(),
DropStatement = driver.Compile(SqlDdl.Drop(table)).GetCommandText(),
StoreRequest = new PersistRequest(Handlers.StorageDriver, insertStatement, storeRequestBindings),
ClearRequest = new PersistRequest(Handlers.StorageDriver, SqlDml.Delete(tableRef), null)
};
result.StoreRequest.Prepare();
result.ClearRequest.Prepare();
return(result);
}
protected override int ExecuteInternal()
{
base.ExecuteInternal();
QueryTranslationResult request = GetRequest(query);
Bindings = request.ParameterBindings.ToList();
if (PrimaryIndexes.Length > 1)
{
throw new NotImplementedException("Inheritance is not implemented");
}
SqlDelete delete = SqlDml.Delete(SqlDml.TableRef(PrimaryIndexes[0].Table));
Join(delete, (SqlSelect)request.Query);
QueryCommand command = ToCommand(delete);
int result = command.ExecuteNonQuery();
return(result);
}
private IPersistDescriptor CreateDescriptor(string tableName,
TypeMapping mapping1, string columnName1, ParameterTransmissionType transmissionType1,
TypeMapping mapping2, string columnName2, ParameterTransmissionType transmissionType2,
Action <SqlDelete> deleteTransform = null)
{
var catalog = new Catalog(task.Catalog);
var schema = catalog.CreateSchema(task.Schema);
var table = schema.CreateTable(tableName);
var columnNames = new[] { columnName1, columnName2 };
var mappings = new[] { mapping1, mapping2 };
var transmissionTypes = new[] { transmissionType1, transmissionType2 };
var columns = columnNames.Select(table.CreateColumn).ToList();
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
var bindings = new PersistParameterBinding[columns.Count];
for (int i = 0; i < columns.Count; i++)
{
var binding = new PersistParameterBinding(mappings[i], i, transmissionTypes[i]);
insert.Values[tableRef.Columns[i]] = binding.ParameterReference;
bindings[i] = binding;
}
var delete = SqlDml.Delete(tableRef);
if (deleteTransform != null)
{
deleteTransform.Invoke(delete);
}
var storeRequest = new PersistRequest(driver, insert, bindings);
var clearRequest = new PersistRequest(driver, delete, null);
storeRequest.Prepare();
clearRequest.Prepare();
return(new Descriptor {
StoreRequest = storeRequest,
ClearRequest = clearRequest
});
}
protected virtual List <PersistRequest> BuildRemoveRequest(PersistRequestBuilderContext context)
{
var result = new List <PersistRequest>();
for (int i = context.AffectedIndexes.Count - 1; i >= 0; i--)
{
var index = context.AffectedIndexes[i];
var tableRef = SqlDml.TableRef(context.Mapping[index.ReflectedType]);
var query = SqlDml.Delete(tableRef);
var bindings = new List <PersistParameterBinding>();
query.Where = BuildKeyFilter(context, tableRef, bindings);
if (context.Task.ValidateVersion)
{
query.Where &= BuildVersionFilter(context, tableRef, bindings);
}
result.Add(CreatePersistRequest(query, bindings, context.NodeConfiguration));
}
return(result);
}
public void SqlDeleteCloneTest()
{
SqlTableRef t = SqlDml.TableRef(table1);
SqlDelete d = SqlDml.Delete(t);
d.Where = t[0] < 6;
d.Hints.Add(SqlDml.FastFirstRowsHint(10));
SqlDelete dClone = (SqlDelete)d.Clone();
Assert.AreNotEqual(d, dClone);
Assert.AreNotEqual(d.Delete, dClone.Delete);
Assert.AreEqual(d.NodeType, dClone.NodeType);
Assert.AreEqual(d.Hints.Count, dClone.Hints.Count);
if (!d.Where.IsNullReference())
{
Assert.AreNotEqual(d.Where, dClone.Where);
Assert.AreEqual(d.Where.NodeType, dClone.Where.NodeType);
}
else
{
Assert.AreEqual(dClone.Where, null);
}
}
public void TableIsNotSetTest1()
{
SqlDelete delete = SqlDml.Delete();
Assert.Throws <SqlCompilerException>(() => sqlDriver.Compile(delete));
}