public void ReCreateIndex(IConnectionManager connection)
{
//Arrange
string indexName = "ix_IndexTest2";
CreateTableTask.Create(connection, "IndexCreationTable2", new List <TableColumn>()
{
new TableColumn("Key1", "INT", allowNulls: false),
new TableColumn("Key2", "INT", allowNulls: true),
});
CreateIndexTask.CreateOrRecreate(connection, indexName, "IndexCreationTable2",
new List <string>()
{
"Key1", "Key2"
});
//Act
CreateIndexTask.CreateOrRecreate(connection, indexName, "IndexCreationTable2",
new List <string>()
{
"Key1", "Key2"
});
//Assert
Assert.True(IfExistsTask.IsExisting(connection, "ix_IndexTest2"));
}
public void CreateTableWithDefault()
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value1", "int", allowNulls: false)
{
DefaultValue = "0"
},
new TableColumn("value2", "nvarchar(10)", allowNulls: false)
{
DefaultValue = "Test"
},
new TableColumn("value3", "decimal", allowNulls: false)
{
DefaultConstraintName = "TestConstraint", DefaultValue = "3.12"
}
};
//Act
CreateTableTask.Create(SqlConnection, "dbo.CreateTable8", columns);
//Assert
Assert.True(IfExistsTask.IsExisting(SqlConnection, "CreateTable8"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable8", SqlConnection);
Assert.Contains(td.Columns, col => col.DefaultValue == "0");
Assert.Contains(td.Columns, col => col.DefaultValue == "Test");
Assert.Contains(td.Columns, col => col.DefaultValue == "3.12" && col.DefaultConstraintName == "TestConstraint");
}
public void CreateIndexWithInclude()
{
//Arrange
string indexName = "ix_IndexTest3";
CreateTableTask.Create(SqlConnection, "dbo.IndexCreation3", new List <TableColumn>()
{
new TableColumn("Key1", "INT", allowNulls: false),
new TableColumn("Key2", "CHAR(2)", allowNulls: true),
new TableColumn("Value1", "NVARCHAR(10)", allowNulls: true),
new TableColumn("Value2", "DECIMAL(10,2)", allowNulls: false),
});
//Act
CreateIndexTask.CreateOrRecreate(SqlConnection, indexName, "dbo.IndexCreation3",
new List <string>()
{
"Key1", "Key2"
},
new List <string>()
{
"Value1", "Value2"
});
//Assert
Assert.True(IfExistsTask.IsExisting(SqlConnection, "ix_IndexTest3"));
}
public void CreateView(IConnectionManager connection)
{
//Arrange
//Act
CreateViewTask.CreateOrAlter(connection, "View1", "SELECT 1 AS Test");
//Assert
Assert.True(IfExistsTask.IsExisting(connection, "View1"));
}
public void IfExistsLogging()
{
//Arrange
CreateSimpleTable("IfExistsTable");
//Act
IfExistsTask.IsExisting(Connection, "IfExistsTable");
//Assert
Assert.Equal(2, CountLogEntries("IFEXISTS"));
}
public void ThrowException(IConnectionManager connection)
{
//Arrange
//Act
//Assert
Assert.Throws <ETLBoxException>(
() =>
{
IfExistsTask.ThrowExceptionIfNotExists(connection, "xyz.Somestrangenamehere");
});
}
public void DropView(IConnectionManager connection)
{
//Arrange
CreateViewTask.CreateOrAlter(connection, "DropViewTest", "SELECT 1 AS Test");
Assert.True(IfExistsTask.IsExisting(connection, "DropViewTest"));
//Act
DropViewTask.Drop(connection, "DropViewTest");
//Assert
Assert.False(IfExistsTask.IsExisting(connection, "DropTableTest"));
}
public void DropProcedure()
{
//Arrange
CreateProcedureTask.CreateOrAlter(SqlConnection, "DropProc1", "SELECT 1");
Assert.True(IfExistsTask.IsExisting(SqlConnection, "DropProc1"));
//Act
DropProcedureTask.Drop(SqlConnection, "DropProc1");
//Assert
Assert.False(IfExistsTask.IsExisting(SqlConnection, "DropProc1"));
}
public void CreateTable(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value", "INT")
};
//Act
CreateTableTask.Create(connection, "CreateTable1", columns);
//Assert
Assert.True(IfExistsTask.IsExisting(connection, "CreateTable1"));
}
public void DropTable(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value", "int")
};
CreateTableTask.Create(connection, "DropTableTest", columns);
Assert.True(IfExistsTask.IsExisting(connection, "DropTableTest"));
//Act
DropTableTask.Drop(connection, "DropTableTest");
//Assert
Assert.False(IfExistsTask.IsExisting(connection, "DropTableTest"));
}
public void IfTableExists(IConnectionManager connection)
{
//Arrange
SqlTask.ExecuteNonQuery(connection, "Drop table if exists"
, $@"DROP TABLE IF EXISTS ExistTableTest");
//Act
var existsBefore = IfExistsTask.IsExisting(connection, "ExistTableTest");
SqlTask.ExecuteNonQuery(connection, "Create test data table"
, $@"CREATE TABLE ExistTableTest ( Col1 INT NULL )");
var existsAfter = IfExistsTask.IsExisting(connection, "ExistTableTest");
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
}
public void AlterView(IConnectionManager connection)
{
//Arrange
CreateViewTask.CreateOrAlter(connection, "View2", "SELECT 1 AS Test");
Assert.True(IfExistsTask.IsExisting(connection, "View2"));
//Act
CreateViewTask.CreateOrAlter(connection, "View2", "SELECT 5 AS Test");
//Assert
if (connection.GetType() == typeof(SqlConnectionManager))
{
Assert.Equal(1, RowCountTask.Count(connection, "sys.objects",
"type = 'V' AND object_id = object_id('dbo.View2') AND create_date <> modify_date"));
}
Assert.True(IfExistsTask.IsExisting(connection, "View2"));
}
public void CreateTableWithNullable(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value", "INT"),
new TableColumn("value2", "DATETIME", true)
};
//Act
CreateTableTask.Create(connection, "CreateTable3", columns);
//Assert
Assert.True(IfExistsTask.IsExisting(connection, "CreateTable3"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable3", connection);
Assert.Contains(td.Columns, col => col.AllowNulls);
}
public void CreateTableWithPrimaryKey(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("Key", "int", allowNulls: false, isPrimaryKey: true),
new TableColumn("value2", "datetime", allowNulls: true)
};
//Act
CreateTableTask.Create(connection, "CreateTable4", columns);
//Assert
Assert.True(IfExistsTask.IsExisting(connection, "CreateTable4"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable4", connection);
Assert.Contains(td.Columns, col => col.IsPrimaryKey);
}
public static TableDefinition GetDefinitionFromTableName(string tableName, IConnectionManager connection)
{
IfExistsTask.ThrowExceptionIfNotExists(connection, tableName);
ConnectionManagerType connectionType = ConnectionManagerTypeFinder.GetType(connection);
//return ReadTableDefinitionFromDataTable(tableName, connection);
if (connectionType == ConnectionManagerType.SqlServer)
{
return(ReadTableDefinitionFromSqlServer(tableName, connection));
}
else if (connectionType == ConnectionManagerType.SQLLite)
{
return(ReadTableDefinitionFromSQLite(tableName, connection));
}
else
{
throw new ETLBoxException("Unknown connection type - please pass a valid TableDefinition!");
}
}
public void CreateTableWithIdentity(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value1", "int", allowNulls: false, isPrimaryKey: true, isIdentity: true)
};
//Act
CreateTableTask.Create(connection, "CreateTable6", columns);
//Assert
Assert.True(IfExistsTask.IsExisting(connection, "CreateTable6"));
if (connection.GetType() != typeof(SQLiteConnectionManager))
{
var td = TableDefinition.GetDefinitionFromTableName("CreateTable6", connection);
Assert.Contains(td.Columns, col => col.IsIdentity);
}
}
public void DropView(IConnectionManager connection)
{
//Arrange
CreateTableTask.Create(connection, "DropIndexTable", new List <TableColumn>()
{
new TableColumn("Test1", "INT")
});
CreateIndexTask.CreateOrRecreate(connection, "IndexToDrop", "DropIndexTable",
new List <string>()
{
"Test1"
});
Assert.True(IfExistsTask.IsExisting(connection, "IndexToDrop"));
//Act
DropIndexTask.Drop(connection, "DropIndexTable", "IndexToDrop");
//Assert
Assert.False(IfExistsTask.IsExisting(connection, "IndexToDrop"));
}
public void CreateTableWithComputedColumn()
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value1", "int", allowNulls: false),
new TableColumn("value2", "int", allowNulls: false),
new TableColumn("compValue", "bigint", allowNulls: true)
{
ComputedColumn = "value1 * value2"
}
};
//Act
CreateTableTask.Create(SqlConnection, "dbo.CreateTable9", columns);
//Assert
Assert.True(IfExistsTask.IsExisting(SqlConnection, "CreateTable9"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable9", SqlConnection);
Assert.Contains(td.Columns, col => col.ComputedColumn == "[value1]*[value2]");
}
public void CreateTableWithIdentityIncrement()
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value1", "int", allowNulls: false)
{
IsIdentity = true,
IdentityIncrement = 1000,
IdentitySeed = 50
}
};
//Act
CreateTableTask.Create(SqlConnection, "CreateTable7", columns);
//Assert
Assert.True(IfExistsTask.IsExisting(SqlConnection, "CreateTable7"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable7", SqlConnection);
Assert.Contains(td.Columns, col => col.IsIdentity && col.IdentityIncrement == 1000 && col.IdentitySeed == 50);
}
