public static TableDefinition GetDefinitionFromTableName(string tableName, IConnectionManager connection)
{
IfTableOrViewExistsTask.ThrowExceptionIfNotExists(connection, tableName);
ConnectionManagerType connectionType = ConnectionManagerSpecifics.GetType(connection);
if (connectionType == ConnectionManagerType.SqlServer)
{
return(ReadTableDefinitionFromSqlServer(tableName, connection));
}
else if (connectionType == ConnectionManagerType.SQLite)
{
return(ReadTableDefinitionFromSQLite(tableName, connection));
}
else if (connectionType == ConnectionManagerType.MySql)
{
return(ReadTableDefinitionFromMySqlServer(tableName, connection));
}
else if (connectionType == ConnectionManagerType.Postgres)
{
return(ReadTableDefinitionFromPostgres(tableName, connection));
}
else
{
throw new ETLBoxException("Unknown connection type - please pass a valid TableDefinition!");
}
}
private static void GetColumns(IConnectionManager connectionManager, string name, List <TableColumn> columns)
{
ValidateName(name);
IfTableOrViewExistsTask.ThrowExceptionIfNotExists(connectionManager, name);
ConnectionManagerType connectionType = ConnectionManagerSpecifics.GetType(connectionManager);
ObjectNameDescriptor TN = new ObjectNameDescriptor(name, connectionType);
if (connectionType == ConnectionManagerType.SqlServer)
{
GetColumnsFromSqlServer(connectionManager, TN, columns);
}
else if (connectionType == ConnectionManagerType.SQLite)
{
GetColumnsFromSQLite(connectionManager, TN, columns);
}
else if (connectionType == ConnectionManagerType.MySql)
{
GetColumnsFromMySqlServer(connectionManager, TN, columns);
}
else if (connectionType == ConnectionManagerType.Postgres)
{
GetColumnsFromPostgres(connectionManager, TN, columns);
}
else if (connectionType == ConnectionManagerType.Access)
{
GetColumnsFromAccess(connectionManager, TN, columns);
}
else
{
throw new ETLBoxException("Unknown connection type - please pass a valid TableDefinition!");
}
}
public void WithOwnImplementation()
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("somedate", "ABC"),
new TableColumn("sometext", "TEXT")
};
//Act
var ctt = new CreateTableTask("CreateTableIDataTypeConverter", columns)
{
ConnectionManager = SqlConnection,
DataTypeConverter = new MyDataTypeConverter()
};
ctt.Create();
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(SqlConnection, "CreateTableIDataTypeConverter"));
var td = TableDefinition.FromTableName(SqlConnection, "CreateTableIDataTypeConverter");
Assert.Collection <TableColumn>(td.Columns,
col => Assert.True(col.DataType == "DATETIME"),
col => Assert.True(col.DataType == "VARCHAR(MAX)")
);
}
public void CreateTableWithDefault(IConnectionManager connection)
{
//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(10,2)", allowNulls: false)
{
DefaultValue = "3.12"
}
};
//Act
CreateTableTask.Create(connection, "CreateTable8", columns);
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "CreateTable8"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable8", connection);
Assert.Contains(td.Columns, col => col.DefaultValue == "0");
Assert.Contains(td.Columns, col => col.DefaultValue == "Test" || col.DefaultValue == "'Test'");
Assert.Contains(td.Columns, col => col.DefaultValue == "3.12");
}
public void IfViewExists(IConnectionManager connection)
{
//Arrange
if (connection.GetType() != typeof(AccessOdbcConnectionManager) &&
connection.GetType() != typeof(OracleConnectionManager))
{
SqlTask.ExecuteNonQuery(connection, "Drop view if exists"
, $@"DROP VIEW IF EXISTS existview_test");
}
//Act
var existsBefore = IfTableOrViewExistsTask.IsExisting(connection, "existview_test");
string viewtext = $@"CREATE VIEW existview_test AS SELECT 1 AS test";
if (connection.GetType() == typeof(OracleConnectionManager))
{
viewtext = $@"CREATE VIEW {connection.QB}existview_test{connection.QE} AS SELECT 1 AS test FROM DUAL";
}
SqlTask.ExecuteNonQuery(connection, "Create test data table"
, viewtext);
var existsAfter = IfTableOrViewExistsTask.IsExisting(connection, "existview_test");
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
}
public void CreateTableWithComputedColumn(IConnectionManager connection)
{
if (connection.GetType() != typeof(SQLiteConnectionManager) &&
connection.GetType() != typeof(PostgresConnectionManager))
{
//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(connection, "CreateTable9", columns);
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "CreateTable9"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable9", connection);
if (connection.GetType() == typeof(SqlConnectionManager))
{
Assert.Contains(td.Columns, col => col.ComputedColumn == "[value1]*[value2]");
}
else if (connection.GetType() == typeof(MySqlConnectionManager))
{
Assert.Contains(td.Columns, col => col.ComputedColumn == "(`value1` * `value2`)");
}
}
}
public void CopyTableFromOracleToSqlServer()
{
//Arrange
List<TableColumn> columns = new List<TableColumn>() {
new TableColumn("Id", "INT",allowNulls:false, isPrimaryKey:true, isIdentity:true),
new TableColumn("value1", "NVARCHAR(10)",allowNulls:true),
new TableColumn("value2", "DECIMAL(10,2)",allowNulls:false),
new TableColumn("value3", "DATE",allowNulls:false)
};
CreateTableTask.Create(OracleConnection, "CopyTable_Oracle2Sql", columns);
//Act
var definition =
TableDefinition.FromTableName(OracleConnection, "CopyTable_Oracle2Sql");
definition.Name = "CopyTable_Oracle2Sql_copy";
var ct = new CreateTableTask(definition)
{
IgnoreCollation = true,
ConnectionManager = SqlConnection
};
ct.Create();
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(SqlConnection, "CopyTable_Oracle2Sql_copy"));
}
public void SpecialCharsInTableName(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("Id1", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("Id2", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("value", "DATE", allowNulls: true)
};
string tableName = "";
if (connection.GetType() == typeof(SqlConnectionManager))
{
tableName = @"[dbo].[ T""D"" 1 ]";
}
else if (connection.GetType() == typeof(PostgresConnectionManager))
{
tableName = @"""public""."" T [D] 1 """;
}
else
{
tableName = @""" T [D] 1 """;
}
CreateTableTask.Create(connection, tableName, columns);
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, tableName));
var td = TableDefinition.GetDefinitionFromTableName(tableName, connection);
Assert.True(td.Columns.Where(col => col.IsPrimaryKey && col.Name.StartsWith("Id")).Count() == 2);
}
public void CopyTableUsingTableDefinition(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("Id", "INT", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("value1", "NVARCHAR(10)", allowNulls: true),
new TableColumn("value2", "DECIMAL(10,2)", allowNulls: false)
{
DefaultValue = "3.12"
}
};
CreateTableTask.Create(connection, "CreateTable10", columns);
//Act
var definition =
TableDefinition.GetDefinitionFromTableName(connection, "CreateTable10");
definition.Name = "CreateTable10_copy";
CreateTableTask.Create(connection, definition);
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "CreateTable10_copy"));
}
/// <summary>
/// Gather a table definition from an existing table in the database.
/// </summary>
/// <param name="connectionManager">The connection manager of the database you want to connect</param>
/// <param name="tableName">A name of an existing table in the database</param>
/// <returns></returns>
public static TableDefinition FromTableName(IConnectionManager connection, string tableName)
{
IfTableOrViewExistsTask.ThrowExceptionIfNotExists(connection, tableName);
ConnectionManagerType connectionType = connection.ConnectionManagerType;
ObjectNameDescriptor TN = new ObjectNameDescriptor(tableName, connection.QB, connection.QE);
if (connectionType == ConnectionManagerType.SqlServer)
{
return(ReadTableDefinitionFromSqlServer(connection, TN));
}
else if (connectionType == ConnectionManagerType.SQLite)
{
return(ReadTableDefinitionFromSQLite(connection, TN));
}
else if (connectionType == ConnectionManagerType.MySql)
{
return(ReadTableDefinitionFromMySqlServer(connection, TN));
}
else if (connectionType == ConnectionManagerType.Postgres)
{
return(ReadTableDefinitionFromPostgres(connection, TN));
}
else if (connectionType == ConnectionManagerType.Access)
{
return(ReadTableDefinitionFromAccess(connection, TN));
}
else if (connectionType == ConnectionManagerType.Oracle)
{
return(ReadTableDefinitionFromOracle(connection, TN));
}
else
{
throw new ETLBoxException("Unknown connection type - please pass a valid TableDefinition!");
}
}
public void CreateView(IConnectionManager connection)
{
//Arrange
//Act
CreateViewTask.CreateOrAlter(connection, "View1", "SELECT 1 AS Test");
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "View1"));
}
public void IfExistsLogging()
{
//Arrange
CreateSimpleTable("IfExistsTable");
//Act
IfTableOrViewExistsTask.IsExisting(SqlConnection, "IfExistsTable");
//Assert
Assert.Equal(2, CountLogEntries("IfTableOrViewExistsTask"));
}
public void CreateView(IConnectionManager connection)
{
//Arrange
//Act
CreateViewTask.CreateOrAlter(connection, "View1", "SELECT 1 AS test");
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "View1"));
var td = TableDefinition.FromTableName(connection, "View1");
Assert.Contains(td.Columns, col => col.Name == "test");
}
public void ThrowException(IConnectionManager connection)
{
//Arrange
//Act
//Assert
Assert.Throws <ETLBoxException>(
() =>
{
IfTableOrViewExistsTask.ThrowExceptionIfNotExists(connection, "xyz.Somestrangenamehere");
});
}
public void Drop(IConnectionManager connection)
{
//Arrange
CreateViewTask.CreateOrAlter(connection, "DropViewTest", "SELECT 1 AS Test");
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "DropViewTest"));
//Act
DropViewTask.Drop(connection, "DropViewTest");
//Assert
Assert.False(IfTableOrViewExistsTask.IsExisting(connection, "DropTableTest"));
}
public void CreateTableWithDefaultConnection()
{
//Arrange
//Act
CreateTableTask.Create("TestTable",
new List <TableColumn>()
{
new TableColumn("value", "INT")
});
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting("TestTable"));
}
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(IfTableOrViewExistsTask.IsExisting(connection, "CreateTable1"));
}
public void CreateLogTable(IConnectionManager connection)
{
//Arrange
//Act
CreateLogTableTask.Create(connection, "etlbox_testlog");
//Assert
IfTableOrViewExistsTask.IsExisting(connection, "etlbox_testlog");
var td = TableDefinition.GetDefinitionFromTableName("etlbox_testlog", connection);
Assert.True(td.Columns.Count == 10);
//Cleanup
DropTableTask.Drop(connection, "etlbox_testlog");
}
public void CreateErrorTable(IConnectionManager connection)
{
//Arrange
//Act
CreateErrorTableTask.Create(connection, "etlbox_error");
//Assert
IfTableOrViewExistsTask.IsExisting(connection, "etlbox_error");
var td = TableDefinition.FromName(connection, "etlbox_error");
Assert.True(td.Columns.Count == 3);
//Cleanup
DropTableTask.Drop(connection, "etlbox_error");
}
public void CreateView(IConnectionManager connection)
{
//Arrange
string viewtext = "SELECT 1 AS test";
if (connection.GetType() == typeof(OracleConnectionManager))
{
viewtext += " FROM DUAL";
}
//Act
CreateViewTask.CreateOrAlter(connection, "View1", viewtext);
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "View1"));
var td = TableDefinition.FromTableName(connection, "View1");
Assert.Contains(td.Columns, col => col.Name.ToLower() == "test");
}
public void CreateTableWithNullable(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value", "INT"),
new TableColumn("value2", "DATE", true)
};
//Act
CreateTableTask.Create(connection, "CreateTable3", columns);
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "CreateTable3"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable3", connection);
Assert.Contains(td.Columns, col => col.AllowNulls);
}
public void IfViewExists(IConnectionManager connection)
{
//Arrange
SqlTask.ExecuteNonQuery(connection, "Drop view if exists"
, $@"DROP VIEW IF EXISTS existview_test");
//Act
var existsBefore = IfTableOrViewExistsTask.IsExisting(connection, "existview_test");
SqlTask.ExecuteNonQuery(connection, "Create test data table"
, $@"CREATE VIEW existview_test AS SELECT 1 AS test");
var existsAfter = IfTableOrViewExistsTask.IsExisting(connection, "existview_test");
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
}
public void Drop(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("value", "int")
};
CreateTableTask.Create(connection, "DropTableTest", columns);
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "DropTableTest"));
//Act
DropTableTask.Drop(connection, "DropTableTest");
//Assert
Assert.False(IfTableOrViewExistsTask.IsExisting(connection, "DropTableTest"));
}
public void IfTableExists(IConnectionManager connection)
{
//Arrange
SqlTask.ExecuteNonQuery(connection, "Drop table if exists"
, $@"DROP TABLE IF EXISTS existtable_test");
//Act
var existsBefore = IfTableOrViewExistsTask.IsExisting(connection, "existtable_test");
SqlTask.ExecuteNonQuery(connection, "Create test data table"
, $@"CREATE TABLE existtable_test ( Col1 INT NULL )");
var existsAfter = IfTableOrViewExistsTask.IsExisting(connection, "existtable_test");
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
}
public void CreateTableWithPrimaryKey(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("Id", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("value2", "DATE", allowNulls: true)
};
//Act
CreateTableTask.Create(connection, "CreateTable4", columns);
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "CreateTable4"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable4", connection);
Assert.True(td.Columns.Where(col => col.IsPrimaryKey).Count() == 1);
}
public void Drop(IConnectionManager connection)
{
//Arrange
string viewtext = "SELECT 1 AS test";
if (connection.GetType() == typeof(OracleConnectionManager))
{
viewtext += " FROM DUAL";
}
CreateViewTask.CreateOrAlter(connection, "DropViewTest", viewtext);
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "DropViewTest"));
//Act
DropViewTask.Drop(connection, "DropViewTest");
//Assert
Assert.False(IfTableOrViewExistsTask.IsExisting(connection, "DropTableTest"));
}
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(IfTableOrViewExistsTask.IsExisting(connection, "CreateTable6"));
if (connection.GetType() != typeof(SQLiteConnectionManager))
{
var td = TableDefinition.GetDefinitionFromTableName("CreateTable6", connection);
Assert.Contains(td.Columns, col => col.IsIdentity);
}
}
public void CreateTableWithCompositePrimaryKey(IConnectionManager connection)
{
//Arrange
List <TableColumn> columns = new List <TableColumn>()
{
new TableColumn("Id1", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("Id2", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("value", "DATE", allowNulls: true)
};
//Act
CreateTableTask.Create(connection, "CreateTable41", columns);
//Assert
Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "CreateTable41"));
var td = TableDefinition.FromTableName(connection, "CreateTable41");
Assert.True(td.Columns.Where(col => col.IsPrimaryKey && col.Name.StartsWith("Id")).Count() == 2);
}
public void IfTableExists(IConnectionManager connection)
{
//Arrange
if (connection.GetType() != typeof(AccessOdbcConnectionManager) &&
connection.GetType() != typeof(OracleConnectionManager))
{
SqlTask.ExecuteNonQuery(connection, "Drop table if exists"
, $@"DROP TABLE IF EXISTS existtable_test");
}
//Act
var existsBefore = IfTableOrViewExistsTask.IsExisting(connection, "existtable_test");
SqlTask.ExecuteNonQuery(connection, "Create test data table"
, $@"CREATE TABLE {connection.QB}existtable_test{connection.QE} ( Col1 INT NULL )");
var existsAfter = IfTableOrViewExistsTask.IsExisting(connection, "existtable_test");
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
}
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(IfTableOrViewExistsTask.IsExisting(SqlConnection, "CreateTable7"));
var td = TableDefinition.GetDefinitionFromTableName("CreateTable7", SqlConnection);
Assert.Contains(td.Columns, col => col.IsIdentity && col.IdentityIncrement == 1000 && col.IdentitySeed == 50);
}
