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 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")); }
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 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 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 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 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 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 CreateTableWithDefaultConnection() { //Arrange //Act CreateTableTask.Create("TestTable", new List <TableColumn>() { new TableColumn("value", "INT") }); //Assert Assert.True(IfTableOrViewExistsTask.IsExisting("TestTable")); }
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 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 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 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 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 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 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 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 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 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 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 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 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 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 CreateTableWithComment() { //Arrange var columns = new List <TableColumn> { new TableColumn("col1", "INT") { Comment = "test" } }; //Act CreateTableTask.Create(MySqlConnection, "TableWithComment", columns); //Assert Assert.True(IfTableOrViewExistsTask.IsExisting(MySqlConnection, "TableWithComment")); var td = TableDefinition.FromTableName(MySqlConnection, "TableWithComment"); var description = td.Columns.Single(x => x.Name == "col1").Comment; Assert.Equal("test", description); }
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); }
public void CreateTableWithPrimaryKeyAndIndex(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, "CreateTablePKWithIDX", columns); CreateIndexTask.CreateOrRecreate(connection, "testidx", "CreateTablePKWithIDX", new List <string>() { "value2" }); //Assert Assert.True(IfTableOrViewExistsTask.IsExisting(connection, "CreateTablePKWithIDX")); Assert.True(IfIndexExistsTask.IsExisting(connection, "testidx", "CreateTablePKWithIDX")); var td = TableDefinition.FromTableName(connection, "CreateTablePKWithIDX"); Assert.True(td.Columns.Where(col => col.IsPrimaryKey).Count() == 1); }