public void Test152()
{
SqlDropSchema drop = SqlDdl.Drop(schema);
Console.Write(Compile(drop));
}
public void Test150()
{
SqlCreateTable create = SqlDdl.Create(schema.Tables["customer"]);
Console.Write(Compile(create));
}
public void Test151()
{
SqlDropTable drop = SqlDdl.Drop(schema.Tables["customer"]);
Console.Write(Compile(drop));
}
private void ManuallyInsertIgnoredItems(Catalog catalog)
{
var validatableSchemas = new List <Schema>();
if (nodeToSchemaMap.Count > 0)
{
validatableSchemas.Add(catalog.Schemas[nodeToSchemaMap[MainNodeId]]);
validatableSchemas.Add(catalog.Schemas[nodeToSchemaMap[AdditionalNodeId]]);
}
else
{
validatableSchemas.Add(catalog.DefaultSchema);
}
foreach (var schema in validatableSchemas)
{
using (var connection = driver.CreateConnection()) {
connection.Open();
var productTable = schema.Tables["Product"];
var hiddenNameColumn = productTable.CreateColumn("HiddenName", GetTypeForString(255));
hiddenNameColumn.IsNullable = true;
using (var command = connection.CreateCommand(driver.Compile(SqlDdl.Alter(productTable, SqlDdl.AddColumn(hiddenNameColumn))).GetCommandText()))
command.ExecuteNonQuery();
var priceListTable = schema.Tables["PriceList"];
var hiddenCommentColumn = priceListTable.CreateColumn("HiddenComment", GetTypeForString(255));
hiddenCommentColumn.IsNullable = true;
using (var command = connection.CreateCommand(driver.Compile(SqlDdl.Alter(priceListTable, SqlDdl.AddColumn(hiddenCommentColumn))).GetCommandText()))
command.ExecuteNonQuery();
var currencyTable = schema.Tables["Currency"];
var prefixColumnTemplate = "NotInDomain{0}";
var columns = new[] { "Column1", "Column2", "Column3" };
foreach (var column in columns)
{
var prefixColumn = currencyTable.CreateColumn(string.Format(prefixColumnTemplate, column), GetTypeForString(255));
prefixColumn.IsNullable = true;
using (var command = connection.CreateCommand(driver.Compile(SqlDdl.Alter(currencyTable, SqlDdl.AddColumn(prefixColumn))).GetCommandText()))
command.ExecuteNonQuery();
}
var ignoredTable = schema.CreateTable("HiddenTable");
var idColumn = ignoredTable.CreateColumn("Id", new SqlValueType(SqlType.Int64));
idColumn.IsNullable = false;
var name = ignoredTable.CreateColumn("Name", GetTypeForString(255));
name.IsNullable = false;
var pk = ignoredTable.CreatePrimaryKey("PK_HiddenTable", idColumn);
using (var command = connection.CreateCommand(SqlDdl.Create(ignoredTable)))
command.ExecuteNonQuery();
var notInDomainTable1 = schema.CreateTable("NotInDomain1");
idColumn = notInDomainTable1.CreateColumn("Id", new SqlValueType(SqlType.Int64));
idColumn.IsNullable = false;
name = notInDomainTable1.CreateColumn("Name", GetTypeForString(255));
name.IsNullable = false;
pk = notInDomainTable1.CreatePrimaryKey("PK_NotInDomain1", idColumn);
using (var command = connection.CreateCommand(SqlDdl.Create(notInDomainTable1)))
command.ExecuteNonQuery();
var notInDomainTable2 = schema.CreateTable("NotInDomain2");
idColumn = notInDomainTable2.CreateColumn("Id", new SqlValueType(SqlType.Int64));
idColumn.IsNullable = false;
name = notInDomainTable2.CreateColumn("Name", GetTypeForString(255));
name.IsNullable = false;
pk = notInDomainTable2.CreatePrimaryKey("PK_NotInDomain2", idColumn);
using (var command = connection.CreateCommand(SqlDdl.Create(notInDomainTable2)))
command.ExecuteNonQuery();
var notInDomainTable3 = schema.CreateTable("NotInDomain3");
idColumn = notInDomainTable3.CreateColumn("Id", new SqlValueType(SqlType.Int64));
idColumn.IsNullable = false;
name = notInDomainTable3.CreateColumn("Name", GetTypeForString(255));
name.IsNullable = false;
pk = notInDomainTable3.CreatePrimaryKey("PK_NotInDomain3", idColumn);
using (var command = connection.CreateCommand(SqlDdl.Create(notInDomainTable3)))
command.ExecuteNonQuery();
}
}
}
private static void DropForeignKeys(StorageDriver driver, IEnumerable <Table> tables, ISqlExecutor executor)
{
var statements = BreakEvery(tables
.SelectMany(t => t.TableConstraints.OfType <ForeignKey>())
.Select(fk => driver.Compile(SqlDdl.Alter(fk.Table, SqlDdl.DropConstraint(fk))).GetCommandText()), 25);
executor.ExecuteMany(statements);
}
public void Test157()
{
var renameColumn = SqlDdl.Rename(schema.Tables["customer"].TableColumns["first_name"], "FirstName");
Console.Write(Compile(renameColumn));
}
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 Test155()
{
SqlAlterTable alter = SqlDdl.Alter(Catalog.Schemas["main"].Tables["customer"], SqlDdl.AddColumn(Catalog.Schemas["main"].Tables["customer"].TableColumns["CompanyName"]));
Console.Write(Compile(alter));
}
public void Test156()
{
SqlAlterTable alter = SqlDdl.Alter(Catalog.Schemas["main"].Tables["customer"], SqlDdl.DropColumn(Catalog.Schemas["main"].Tables["customer"].TableColumns["CompanyName"]));
Assert.Throws <NotSupportedException>(() => Console.Write(Compile(alter)));
}
public void Test153()
{
SqlCreateView create = SqlDdl.Create(Catalog.Schemas["main"].Views["Invoice Subtotals"]);
Console.Write(Compile(create));
}
public void Test154()
{
SqlCreateSchema create = SqlDdl.Create(Catalog.Schemas["main"]);
Console.Write(Compile(create));
}
public void Test152()
{
SqlDropSchema drop = SqlDdl.Drop(Catalog.Schemas["main"]);
Assert.Throws <NotSupportedException>(() => Console.Write(Compile(drop)));
}
public void Test151()
{
SqlDropTable drop = SqlDdl.Drop(Catalog.Schemas["main"].Tables["customer"]);
Console.Write(Compile(drop));
}
protected virtual void CreateTables(Catalog catalog)
{
var schema = catalog.DefaultSchema;
Table table;
TableColumn column;
var createBatch = SqlDml.Batch();
table = schema.CreateTable("EmployeeAddress");
table.CreateColumn("EmployeeID", new SqlValueType(SqlType.Int32));
table.CreateColumn("AddressID", new SqlValueType(SqlType.Int32));
table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
createBatch.Add(SqlDdl.Create(table));
table = schema.CreateTable("EmployeeDepartmentHistory");
table.CreateColumn("EmployeeID", new SqlValueType(SqlType.Int32));
table.CreateColumn("DepartmentID", new SqlValueType(SqlType.Int16));
table.CreateColumn("ShiftID", new SqlValueType(SqlType.DateTime));
table.CreateColumn("StartDate", new SqlValueType(SqlType.DateTime));
table.CreateColumn("EndDate", new SqlValueType(SqlType.DateTime));
table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
createBatch.Add(SqlDdl.Create(table));
table = schema.CreateTable("EmployeePayHistory");
table.CreateColumn("EmployeeID", new SqlValueType(SqlType.Int32));
table.CreateColumn("RateChangeDate", new SqlValueType(SqlType.DateTime));
table.CreateColumn("Rate", new SqlValueType(SqlType.Double));
table.CreateColumn("PayFrequency", new SqlValueType(SqlType.Int16));
table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
createBatch.Add(SqlDdl.Create(table));
table = schema.CreateTable("Employee");
table.CreateColumn("EmployeeID", new SqlValueType(SqlType.Int32));
table.CreateColumn("NationalIDNumber", Driver.TypeMappings[typeof(string)].MapType());
table.CreateColumn("ContactID", new SqlValueType(SqlType.Int32));
table.CreateColumn("LoginID", Driver.TypeMappings[typeof(string)].MapType());
table.CreateColumn("ManagerID", new SqlValueType(SqlType.Int32));
table.CreateColumn("Title", Driver.TypeMappings[typeof(string)].MapType());
table.CreateColumn("BirthDate", new SqlValueType(SqlType.DateTime));
table.CreateColumn("MaritalStatus", Driver.TypeMappings[typeof(string)].MapType());
table.CreateColumn("Gender", Driver.TypeMappings[typeof(string)].MapType());
table.CreateColumn("HireDate", new SqlValueType(SqlType.DateTime));
table.CreateColumn("VacationHours", new SqlValueType(SqlType.Int16));
table.CreateColumn("SickLeaveHours", new SqlValueType(SqlType.Int16));
table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
createBatch.Add(SqlDdl.Create(table));
table = schema.CreateTable("Department");
table.CreateColumn("DepartmentID", new SqlValueType(SqlType.Int32));
table.CreateColumn("Name", Driver.TypeMappings[typeof(string)].MapType());
table.CreateColumn("GroupName", Driver.TypeMappings[typeof(string)].MapType());
table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
createBatch.Add(SqlDdl.Create(table));
table = schema.CreateTable("Shift");
table.CreateColumn("ShiftID", new SqlValueType(SqlType.Int32));
table.CreateColumn("Name", Driver.TypeMappings[typeof(string)].MapType());
table.CreateColumn("StartTime", new SqlValueType(SqlType.DateTime));
table.CreateColumn("EndTime", new SqlValueType(SqlType.DateTime));
table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
createBatch.Add(SqlDdl.Create(table));
table = schema.CreateTable("JobCandidate");
table.CreateColumn("JobCandidateID", new SqlValueType(SqlType.Int32));
table.CreateColumn("EmployeeID", new SqlValueType(SqlType.Int32));
table.CreateColumn("ModifiedDate", new SqlValueType(SqlType.DateTime));
createBatch.Add(SqlDdl.Create(table));
ExecuteBatch(createBatch);
}
public void Test153()
{
SqlCreateView create = SqlDdl.Create(schema.Views["customer_list"]);
Console.Write(Compile(create));
}
public void Test157()
{
var renameColumn = SqlDdl.Rename(Catalog.Schemas["main"].Tables["customer"].TableColumns["LastName"], "LastName1");
Assert.Throws <NotSupportedException>(() => Console.Write(Compile(renameColumn)));
}
public void Test154()
{
SqlCreateSchema create = SqlDdl.Create(schema);
Console.Write(Compile(create));
}
public void Test162()
{
var alter = SqlDdl.Rename(Catalog.Schemas["main"].Tables["customer"], "SomeWierdTableName");
Console.Write(Compile(alter));
}
public void Test150()
{
SqlCreateTable create = SqlDdl.Create(Catalog.Schemas["main"].Tables["customer"]);
Console.Write(Compile(create));
}
private void ClearSchema(SqlDriver driver)
{
using (var connection = driver.CreateConnection()) {
connection.Open();
try {
var schema = driver.ExtractSchema(connection, SpecialSchemaAlias);
var foreignKeys = schema.Tables
.Select(t => new {
Table = t,
ForeignKeys = t.TableConstraints.OfType <ForeignKey>()
});
foreach (var dropConstraintText in from foreignKeyInfo in foreignKeys
from foreignKey in foreignKeyInfo.ForeignKeys
select driver.Compile(SqlDdl.Alter(foreignKeyInfo.Table, SqlDdl.DropConstraint(foreignKey))).GetCommandText())
{
using (var command = connection.CreateCommand(dropConstraintText)) {
_ = command.ExecuteNonQuery();
}
}
foreach (var table in schema.Tables)
{
var dropTableText = driver.Compile(SqlDdl.Drop(table, true)).GetCommandText();
using (var command = connection.CreateCommand(dropTableText)) {
_ = command.ExecuteNonQuery();
}
}
}
finally {
connection.Close();
}
}
}