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 SqlInsert CreateInsert(SqlTableRef tableRef)
{
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef["id"], nextId++);
return(insert);
}
public void InsertAndSelectTest()
{
var schema = ExtractDefaultSchema();
EnsureTableNotExists(schema, TableName);
var table = schema.CreateTable(TableName);
var idColumnType = Driver.TypeMappings[typeof(int)].MapType();
var idColumn = table.CreateColumn(IdColumnName, idColumnType);
table.CreatePrimaryKey("PK_" + TableName, idColumn);
for (int columnIndex = 0; columnIndex < typeMappings.Length; columnIndex++)
{
var mapping = typeMappings[columnIndex];
var column = table.CreateColumn(GetColumnName(columnIndex), mapping.MapType());
column.IsNullable = true;
}
ExecuteNonQuery(SqlDdl.Create(table));
var tableRef = SqlDml.TableRef(table);
using (var insertCommand = Connection.CreateCommand()) {
var insertQuery = SqlDml.Insert(tableRef);
var idParameter = insertCommand.CreateParameter();
idParameter.DbType = DbType.Int32;
idParameter.ParameterName = IdParameterName;
insertCommand.Parameters.Add(idParameter);
insertQuery.Values.Add(tableRef[IdColumnName], SqlDml.ParameterRef(IdParameterName));
var parameters = new List <DbParameter>();
for (int columnIndex = 0; columnIndex < typeMappings.Length; columnIndex++)
{
var mapping = typeMappings[columnIndex];
var parameterName = GetParameterName(columnIndex);
SqlExpression parameterExpression = SqlDml.ParameterRef(parameterName);
if (mapping.ParameterCastRequired)
{
parameterExpression = SqlDml.Cast(parameterExpression, mapping.MapType());
}
insertQuery.Values.Add(tableRef[GetColumnName(columnIndex)], parameterExpression);
var parameter = insertCommand.CreateParameter();
parameter.ParameterName = parameterName;
parameters.Add(parameter);
insertCommand.Parameters.Add(parameter);
}
var insertQueryText = Driver.Compile(insertQuery).GetCommandText();
insertCommand.CommandText = insertQueryText;
for (int rowIndex = 0; rowIndex < testValues[0].Length; rowIndex++)
{
idParameter.Value = rowIndex;
for (int columnIndex = 0; columnIndex < typeMappings.Length; columnIndex++)
{
typeMappings[columnIndex].BindValue(parameters[columnIndex], testValues[columnIndex][rowIndex]);
}
insertCommand.ExecuteNonQuery();
}
}
var resultQuery = SqlDml.Select(tableRef);
resultQuery.Columns.Add(SqlDml.Asterisk);
resultQuery.OrderBy.Add(tableRef[IdColumnName]);
VerifyResults(Connection.CreateCommand(resultQuery));
}
public virtual void CheckConstraintTest()
{
var table = schema.CreateTable(CheckedTableName);
CreatePrimaryKey(table);
var valueColumn1 = table.CreateColumn("value1", Driver.TypeMappings[typeof(int)].MapType());
valueColumn1.IsNullable = true;
var valueColumn2 = table.CreateColumn("value2", Driver.TypeMappings[typeof(int)].MapType());
valueColumn2.IsNullable = false;
var tableRef = SqlDml.TableRef(table);
table.CreateCheckConstraint("check_me", tableRef[valueColumn1.Name] > 0);
ExecuteNonQuery(SqlDdl.Create(table));
// violation of NOT NULL constraint
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[IdColumnName], 1);
insert.Values.Add(tableRef[valueColumn1.Name], 1);
insert.Values.Add(tableRef[valueColumn2.Name], SqlDml.Null);
AssertExceptionType(insert, SqlExceptionType.CheckConstraintViolation);
// violation of CHECK constraint
insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[IdColumnName], 2);
insert.Values.Add(tableRef[valueColumn1.Name], 0);
insert.Values.Add(tableRef[valueColumn2.Name], 0);
AssertExceptionType(insert, SqlExceptionType.CheckConstraintViolation);
}
public virtual void UniqueConstraintTestTest()
{
var table = schema.CreateTable(UniqueTableName);
CreatePrimaryKey(table);
var column = table.CreateColumn("value", Driver.TypeMappings[typeof(int)].MapType());
column.IsNullable = true;
table.CreateUniqueConstraint("unique_me", column);
ExecuteNonQuery(SqlDdl.Create(table));
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[IdColumnName], 1);
// violation of PRIMARY KEY constraint
ExecuteNonQuery(insert);
AssertExceptionType(insert, SqlExceptionType.UniqueConstraintViolation);
// violation of UNIQUE constraint
insert.Values.Clear();
insert.Values.Add(tableRef[IdColumnName], 2);
insert.Values.Add(tableRef[column.Name], 0);
ExecuteNonQuery(insert);
insert.Values.Clear();
insert.Values.Add(tableRef[IdColumnName], 3);
insert.Values.Add(tableRef[column.Name], 0);
AssertExceptionType(insert, SqlExceptionType.UniqueConstraintViolation);
}
protected virtual List <PersistRequest> BuildInsertRequest(PersistRequestBuilderContext context)
{
var result = new List <PersistRequest>();
foreach (var index in context.AffectedIndexes)
{
var table = context.Mapping[index.ReflectedType];
var tableRef = SqlDml.TableRef(table);
var query = SqlDml.Insert(tableRef);
var bindings = new List <PersistParameterBinding>();
foreach (var column in index.Columns)
{
int fieldIndex = GetFieldIndex(context.Type, column);
if (fieldIndex >= 0)
{
var binding = GetBinding(context, column, table, fieldIndex);
query.Values[tableRef[column.Name]] = binding.ParameterReference;
bindings.Add(binding);
}
}
result.Add(CreatePersistRequest(query, bindings, context.NodeConfiguration));
}
return(result);
}
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);
}
private QueryCommand CreateCommand()
{
var insert = SqlDml.Insert(SqlDml.TableRef(PrimaryIndexes[0].Table));
setOperation.Statement = SetStatement.Create(insert);
setOperation.AddValues();
return ToCommand(insert);
}
private void DoInsert()
{
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[IdColumn], nextId++);
using (var command = Connection.CreateCommand(insert))
command.ExecuteNonQuery();
}
public void RowInsertionTest()
{
var defaultSchema = GetSchema();
var tableRef = SqlDml.TableRef(defaultSchema.Tables[TableName]);
var insertQuery = SqlDml.Insert(tableRef);
insertQuery.Values.Add(tableRef["Id"], 1);
insertQuery.Values.Add(tableRef["CreationDate"], DateTime.UtcNow);
TestQueryNamesReadable(insertQuery, defaultSchema);
}
protected override int ExecuteInternal()
{
Bindings = new List <QueryParameterBinding>();
if (PrimaryIndexes.Length > 1)
{
throw new NotImplementedException("Inheritance is not implemented");
}
SqlInsert insert = SqlDml.Insert(SqlDml.TableRef(PrimaryIndexes[0].Table));
setOperation.Statement = SetStatement.Create(insert);
setOperation.AddValues();
QueryCommand command = ToCommand(insert);
return(command.ExecuteNonQuery());
}
private void PreapareTypeTable(Schema schema, MetadataMapping mapping)
{
var columnsTypeMap = GetColumnsTypeMap(typeof(Xtensive.Orm.Metadata.Type));
var table = schema.CreateTable(mapping.Type);
var create = SqlDdl.Create(table);
_ = table.CreateColumn(mapping.TypeId, columnsTypeMap[mapping.TypeId]);
_ = table.CreateColumn(mapping.TypeName, columnsTypeMap[mapping.TypeName]);
Execute(create);
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[mapping.TypeId], 1);
insert.Values.Add(tableRef[mapping.TypeName], "name");
Execute(insert);
}
private void PrepareExtensionTable(Schema schema, MetadataMapping mapping)
{
var columnsTypeMap = GetColumnsTypeMap(typeof(Xtensive.Orm.Metadata.Extension));
var table = schema.CreateTable(mapping.Extension);
var create = SqlDdl.Create(table);
table.CreateColumn(mapping.ExtensionName, columnsTypeMap[mapping.ExtensionName]);
table.CreateColumn(mapping.ExtensionText, columnsTypeMap[mapping.ExtensionText]);
Execute(create);
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[mapping.ExtensionName], "name");
insert.Values.Add(tableRef[mapping.ExtensionText], "text");
Execute(insert);
}
private void PrepareAssemblyTable(Schema schema, MetadataMapping mapping)
{
var columnsTypeMap = GetColumnsTypeMap(typeof(Xtensive.Orm.Metadata.Assembly));
var table = schema.CreateTable(mapping.Assembly);
var create = SqlDdl.Create(table);
table.CreateColumn(mapping.AssemblyName, columnsTypeMap[mapping.AssemblyName]);
table.CreateColumn(mapping.AssemblyVersion, columnsTypeMap[mapping.AssemblyVersion]);
Execute(create);
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[mapping.AssemblyName], "name");
insert.Values.Add(tableRef[mapping.AssemblyVersion], "version");
Execute(insert);
}
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
});
}
public void SqlInsertCloneTest()
{
SqlTableRef t = SqlDml.TableRef(table1);
SqlInsert i = SqlDml.Insert(t);
i.Values[t[0]] = 1;
i.Values[t[1]] = "Anonym";
i.Hints.Add(SqlDml.FastFirstRowsHint(10));
SqlInsert iClone = (SqlInsert)i.Clone();
Assert.AreNotEqual(i, iClone);
Assert.AreNotEqual(i.Into, iClone.Into);
Assert.AreEqual(i.NodeType, iClone.NodeType);
Assert.AreEqual(i.Values.Count, iClone.Values.Count);
foreach (KeyValuePair <SqlColumn, SqlExpression> p in i.Values)
{
Assert.IsFalse(iClone.Values.ContainsKey(p.Key));
Assert.IsFalse(iClone.Values.ContainsValue(p.Value));
}
Assert.AreEqual(i.Hints.Count, iClone.Hints.Count);
}
private SqlInsert MakeUpInsertQuery(SqlTableRef temporaryTable, TypeMapping[] typeMappings, List <PersistParameterBinding> storeRequestBindings, bool hasColumns)
{
var insertStatement = SqlDml.Insert(temporaryTable);
if (hasColumns)
{
var fieldIndex = 0;
foreach (var column in temporaryTable.Columns)
{
TypeMapping typeMapping = typeMappings[fieldIndex];
var binding = new PersistParameterBinding(typeMapping, fieldIndex);
insertStatement.Values[column] = binding.ParameterReference;
storeRequestBindings.Add(binding);
fieldIndex++;
}
}
else
{
insertStatement.Values[temporaryTable.Columns[0]] = SqlDml.Literal(0);
}
return(insertStatement);
}
private void ExecuteInsert(SqlConnection connection, Table table, Dictionary <string, object> values)
{
var tableRef = SqlDml.TableRef(table);
SqlInsert insertQuery = SqlDml.Insert(tableRef);
foreach (var nameValue in values)
{
if (nameValue.Value != null)
{
insertQuery.Values.Add(tableRef[nameValue.Key], SqlDml.Literal(nameValue.Value));
}
else
{
insertQuery.Values.Add(tableRef[nameValue.Key], SqlDml.Null);
}
}
using (var command = connection.CreateCommand(insertQuery)) {
Console.WriteLine(command.CommandText);
command.ExecuteNonQuery();
}
}
public virtual void TimeoutTest()
{
var table = schema.CreateTable(TimeoutTableName);
CreatePrimaryKey(table);
ExecuteNonQuery(SqlDdl.Create(table));
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[IdColumnName], 1);
using (var connectionOne = Driver.CreateConnection()) {
connectionOne.Open();
connectionOne.BeginTransaction();
using (var connectionTwo = Driver.CreateConnection()) {
connectionTwo.Open();
connectionTwo.BeginTransaction(IsolationLevel.ReadCommitted);
using (var command = connectionTwo.CreateCommand(insert))
command.ExecuteNonQuery();
AssertExceptionType(connectionOne, insert, SqlExceptionType.OperationTimeout);
}
}
}
protected override void PopulateData()
{
Catalog catalog;
using (var session = Domain.OpenSession())
using (var transaction = session.OpenTransaction()) {
var connection = ((SqlSessionHandler)session.Handler).Connection;
var extractionResult = Domain.Handlers.StorageDriver.Extract(connection, new[] { new SqlExtractionTask(connection.UnderlyingConnection.Database) });
catalog = extractionResult.Catalogs[0];
transaction.Complete();
}
using (var session = Domain.OpenSession())
using (var transaction = session.OpenTransaction()) {
var connection = ((SqlSessionHandler)session.Handler).Connection;
var queryBuilder = session.Services.Get <QueryBuilder>();
var type = Domain.Model.Types[typeof(PassportOffice)];
var insert1 = SqlDml.Insert(SqlDml.TableRef(catalog.DefaultSchema.Tables[type.MappingName]));
insert1.Values.Add(insert1.Into.Columns[type.Fields["Id"].MappingName], 1);
insert1.Values.Add(insert1.Into.Columns[type.Fields["Title"].MappingName], "Department #1");
var compiledQuery = queryBuilder.CompileQuery(insert1);
using (var command = connection.CreateCommand(insert1)) {
command.ExecuteNonQuery();
}
type = Domain.Model.Types[typeof(Person)];
var insert2 = SqlDml.Insert(SqlDml.TableRef(catalog.DefaultSchema.Tables[type.MappingName]));
insert2.Values.Add(insert2.Into.Columns[type.Fields["Id"].MappingName], 1);
insert2.Values.Add(insert2.Into.Columns[type.Fields["FirstName"].MappingName], "John");
insert2.Values.Add(insert2.Into.Columns[type.Fields["LastName"].MappingName], "Smith");
using (var command = connection.CreateCommand(insert2)) {
command.ExecuteNonQuery();
}
transaction.Complete();
}
}
public virtual void DeadlockTest()
{
var table = schema.CreateTable(DeadlockTableName);
CreatePrimaryKey(table);
var column = table.CreateColumn("value", Driver.TypeMappings[typeof(int)].MapType());
column.IsNullable = true;
ExecuteNonQuery(SqlDdl.Create(table));
Connection.BeginTransaction();
var tableRef = SqlDml.TableRef(table);
var insert = SqlDml.Insert(tableRef);
insert.Values.Add(tableRef[IdColumnName], 1);
ExecuteNonQuery(insert);
insert.Values.Clear();
insert.Values.Add(tableRef[IdColumnName], 2);
ExecuteNonQuery(insert);
Connection.Commit();
var update1To1 = SqlDml.Update(tableRef);
update1To1.Where = tableRef[IdColumnName] == 1;
update1To1.Values.Add(tableRef[column.Name], 1);
var update1To2 = SqlDml.Update(tableRef);
update1To2.Where = tableRef[IdColumnName] == 1;
update1To2.Values.Add(tableRef[column.Name], 2);
var update2To1 = SqlDml.Update(tableRef);
update2To1.Where = tableRef[IdColumnName] == 2;
update2To1.Values.Add(tableRef[column.Name], 1);
var update2To2 = SqlDml.Update(tableRef);
update2To2.Where = tableRef[IdColumnName] == 2;
update2To2.Values.Add(tableRef[column.Name], 2);
using (var connectionOne = this.Driver.CreateConnection()) {
connectionOne.Open();
connectionOne.BeginTransaction(IsolationLevel.ReadCommitted);
using (var connectionTwo = Driver.CreateConnection()) {
connectionTwo.Open();
connectionTwo.BeginTransaction(IsolationLevel.ReadCommitted);
using (var command = connectionOne.CreateCommand(update1To1))
command.ExecuteNonQuery();
using (var command = connectionTwo.CreateCommand(update2To2))
command.ExecuteNonQuery();
var startEvent = new EventWaitHandle(false, EventResetMode.ManualReset);
var arg1 = new EvilThreadArgument
{
Connection = connectionOne,
StartEvent = startEvent,
Statement = update2To1
};
var arg2 = new EvilThreadArgument
{
Connection = connectionTwo,
StartEvent = startEvent,
Statement = update1To2
};
var thread1 = StartEvilThread(arg1);
var thread2 = StartEvilThread(arg2);
startEvent.Set();
thread1.Join();
thread2.Join();
startEvent.Close();
var actual = arg1.ExceptionType ?? arg2.ExceptionType ?? SqlExceptionType.Unknown;
AssertExceptionType(SqlExceptionType.Deadlock, actual);
}
}
}
public void TableIsNotSetTest2()
{
SqlInsert insert = SqlDml.Insert();
Assert.Throws <SqlCompilerException>(() => sqlDriver.Compile(insert));
}