public void TestCreateSchema()
{
string schemaName = "s" + TestHelper.RandomString(9);
CreateSchemaTask.Create(schemaName);
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if schema exists", $"select count(*) from sys.schemas where schema_name(schema_id) = '{schemaName}'"));
}
public void HashCalculationForOneTable()
{
//Arrange
CreateSchemaTask.Create(Connection, "hash");
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value", "int")
};
CreateTableTask.Create(Connection, "DatabaseHash", columns);
//Act
string hash = CalculateDatabaseHashTask.Calculate(Connection, new List <string>()
{
"hash"
});
string hashAgain = CalculateDatabaseHashTask.Calculate(Connection, new List <string>()
{
"hash"
});
//Assert
Assert.Equal(hash, hashAgain);
Assert.Equal("DA39A3EE5E6B4B0D3255BFEF95601890AFD80709", hash);
}
private void CreateETLSchema()
{
EtlSchema = new CreateSchemaTask("etl")
{
DisableLogging = true
};
}
public AzureSqlTests(DataFlowDatabaseFixture dbFixture)
{
CleanUpSchemaTask.CleanUp(AzureSqlConnection, "[source]");
CleanUpSchemaTask.CleanUp(AzureSqlConnection, "[dest]");
CreateSchemaTask.Create(AzureSqlConnection, "[source]");
CreateSchemaTask.Create(AzureSqlConnection, "[dest]");
}
public void CreateSchemaLogging()
{
//Arrange
//Act
CreateSchemaTask.Create(SqlConnection, "createdSchema");
//Assert
Assert.Equal(2, CountLogEntries("CreateSchemaTask"));
}
public static void TestInit(TestContext testContext)
{
TestHelper.RecreateDatabase(testContext);
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString(testContext.Properties["connectionString"].ToString()));
CreateSchemaTask.Create("test");
SqlTask.ExecuteNonQuery("Create test table", $@"create table test.Table1 ( key1 int null, key2 int not null)");
SqlTask.ExecuteNonQuery("Create test table", $@"create table test.Table2 ( key1 int null, key2 int not null, value1 datetime null, value2 varchar(100) not null )");
}
public void CreateSchemaWithDefaultConnection()
{
//Arrange
//Act
CreateSchemaTask.Create("testschema");
//Assert
Assert.True(IfSchemaExistsTask.IsExisting("testschema"));
}
public static void TestInit(TestContext testContext)
{
TestHelper.RecreateDatabase(testContext);
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString(testContext.Properties["connectionString"].ToString()));
CreateSchemaTask.Create("test");
SqlTask.ExecuteNonQuery("Create test data table", $@"
create table test.RC ( value int null )
insert into test.RC select * from (values (1), (2), (3)) AS MyTable(v)");
}
public void CheckCreateSchemaTask()
{
//Arrange
//Act & Assert
Assert.Throws <ETLBoxException>(() =>
{
CreateSchemaTask.Create("test");
});
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
CreateSchemaTask.Create("s" + TestHelper.RandomString(9));
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='CREATESCHEMA' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void CreateSchema()
{
//Arrange
string schemaName = "s" + HashHelper.RandomString(9);
//Act
CreateSchemaTask.Create(Connection, schemaName);
//Assert
Assert.Equal(1, RowCountTask.Count(Connection, "sys.schemas",
$"schema_name(schema_id) = '{schemaName}'"));
}
public void CreateSchema(IConnectionManager connection)
{
if (connection.GetType() != typeof(MySqlConnectionManager))
{
//Arrange
string schemaName = "s" + TestHashHelper.RandomString(9);
//Act
CreateSchemaTask.Create(connection, schemaName);
//Assert
Assert.True(IfSchemaExistsTask.IsExisting(connection, schemaName));
}
}
public void CreateSchemaWithSpecialChar(IConnectionManager connection)
{
if (connection.GetType() != typeof(MySqlConnectionManager))
{
string QB = connection.QB;
string QE = connection.QE;
//Arrange
string schemaName = $"{QB} s#!/ {QE}";
//Act
CreateSchemaTask.Create(connection, schemaName);
//Assert
Assert.True(IfSchemaExistsTask.IsExisting(connection, schemaName));
}
}
public void Drop(IConnectionManager connection)
{
if (connection.GetType() != typeof(MySqlConnectionManager))
{
//Arrange
CreateSchemaTask.Create(connection, "testcreateschema");
Assert.True(IfSchemaExistsTask.IsExisting(connection, "testcreateschema"));
//Act
DropSchemaTask.Drop(connection, "testcreateschema");
//Assert
Assert.False(IfSchemaExistsTask.IsExisting(connection, "testcreateschema"));
}
}
public void Preparation()
{
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;"));
DropDatabaseTask.Drop("DemoDB");
CreateDatabaseTask.Create("DemoDB");
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;Initial Catalog=DemoDB"));
CreateSchemaTask.Create("demo");
OrderDataTableDef.CreateTable();
CustomerTableDef.CreateTable();
CustomerRatingTableDef.CreateTable();
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO demo.Customer values('Sandra Kettler')");
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO demo.Customer values('Nick Thiemann')");
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO demo.Customer values('Zoe Rehbein')");
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO demo.Customer values('Margit Gries')");
}
public void IfSchemaeExists(IConnectionManager connection)
{
if (connection.GetType() != typeof(MySqlConnectionManager))
{
//Arrange
var existsBefore = IfSchemaExistsTask.IsExisting(connection, "testschema");
CreateSchemaTask.Create(connection, "testschema");
//Act
var existsAfter = IfSchemaExistsTask.IsExisting(connection, "testschema");
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
}
}
public void CSV_DB_WithOdbcConnection()
{
ControlFlow.CurrentDbConnection = new OdbcConnectionManager(new OdbcConnectionString(OdbcConnectionStringParameter));
CreateSchemaTask.Create("test");
TableDefinition stagingTable = new TableDefinition("test.Staging", new List <TableColumn>()
{
new TableColumn("ID", "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Col1", "bit", allowNulls: true),
new TableColumn("Col2", "decimal(10,5)", allowNulls: true),
new TableColumn("Col3", "tinyint", allowNulls: true),
new TableColumn("Col4", "int", allowNulls: true),
new TableColumn("Col5", "uniqueidentifier", allowNulls: true),
new TableColumn("Col6", "nvarchar(100)", allowNulls: true)
});
stagingTable.CreateTable();
CSVSource source = new CSVSource("src/ConnectionManager/DatatypeCSV.csv")
{
Configuration = new CsvHelper.Configuration.Configuration()
{
Quote = '"'
}
};
RowTransformation <string[], string[]> trans = new RowTransformation <string[], string[]>("Set empty values to null",
row => {
for (int i = 0; i < row.Length; i++)
{
if (row[i] == String.Empty)
{
row[i] = null;
}
}
return(row);
});
DBDestination <string[]> dest = new DBDestination <string[]>(stagingTable, 2);
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count(stagingTable.Name));
}
public void DropIfExists(IConnectionManager connection)
{
if (connection.GetType() == typeof(MySqlConnectionManager) ||
connection.GetType() == typeof(MariaDbConnectionManager) ||
connection.GetType() == typeof(OracleConnectionManager)
)
{
return;
}
//Arrange
DropSchemaTask.DropIfExists(connection, "testcreateschema2");
CreateSchemaTask.Create(connection, "testcreateschema2");
Assert.True(IfSchemaExistsTask.IsExisting(connection, "testcreateschema2"));
//Act
DropSchemaTask.DropIfExists(connection, "testcreateschema2");
//Assert
Assert.False(IfSchemaExistsTask.IsExisting(connection, "testcreateschema2"));
}
public void Start()
{
//Basics
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;"));
DropDatabaseTask.Drop("DemoDB");
CreateDatabaseTask.Create("DemoDB");
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;Initial Catalog=DemoDB;"));
CreateSchemaTask.Create("demo");
CreateTableTask.Create("demo.table1", new List <TableColumn>()
{
new TableColumn(name: "key", dataType: "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn(name: "value", dataType: "nvarchar(100)", allowNulls: true)
});
SqlTask.ExecuteNonQuery("Insert data",
$@"insert into demo.table1 (value) select * from (values ('Ein Text'), ('Noch mehr Text')) as data(v)");
int count = RowCountTask.Count("demo.table1").Value;
Debug.WriteLine($"Found {count} entries in demo table!");
//Truncate:
//TruncateTableTask.Truncate("demo.table1");
//Batch processing / Go keyword:
//SqlTask.ExecuteNonQuery("sql with go keyword", @"insert into demo.table1 (value) select '####';
//go 2");
ControlFlow.CurrentDbConnection = new SMOConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;Initial Catalog=DemoDB;"));
SqlTask.ExecuteNonQuery("sql with go keyword", @"insert into demo.table1 (value) select '####';
go 2");
AddFileGroupTask.AddFileGroup("FGName", "DemoDB", "200MB", "10MB", isDefaultFileGroup: true);
CRUDProcedureTask.CreateOrAlter("demo.proc1", "select 1 as test");
}
public static void ClassInit(TestContext testContext)
{
TestHelper.RecreateDatabase(testContext);
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString(testContext.Properties["connectionString"].ToString()));
CreateSchemaTask.Create("test");
}
public DatabaseTasksLoggingTests(LoggingDatabaseFixture dbFixture)
{
CreateSchemaTask.Create(SqlConnection, "etl");
CreateLogTableTask.Create(SqlConnection);
ControlFlow.AddLoggingDatabaseToConfig(SqlConnection);
}
public void TestCreateSchemaTask()
{
CreateSchemaTask.Create("test");
}
