/// <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!"); } }
private static void CreateSourceTable(IConnectionManager connection, string tableName) { DropTableTask.DropIfExists(connection, tableName); var TableDefinition = new TableDefinition(tableName , new List <TableColumn>() { new TableColumn("Col1", "VARCHAR(100)", allowNulls: true), new TableColumn("Col2", "VARCHAR(100)", allowNulls: true), new TableColumn("Col3", "VARCHAR(100)", allowNulls: true) }); TableDefinition.CreateTable(connection); ObjectNameDescriptor TN = new ObjectNameDescriptor(tableName, connection.QB, connection.QE); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES('1','Test1','1')"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES('1.35','TestX','X')"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES('2','Test2', NULL)"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES('X',NULL, NULL)"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES('3','Test3', '3')"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES('4','Test4', 'X')"); }
public override void BulkInsert(ITableData data, string tableName) { var TN = new ObjectNameDescriptor(tableName, QB, QE); var sourceColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.SourceColumn).ToList(); var destColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.DataSetColumn).ToList(); var quotedDestColumns = destColumnNames.Select(col => TN.QB + col + TN.QE); using (var writer = DbConnection.BeginBinaryImport($@" COPY {TN.QuotatedFullName} ({string.Join(", ", quotedDestColumns)}) FROM STDIN (FORMAT BINARY)")) { while (data.Read()) { writer.StartRow(); foreach (var destCol in destColumnNames) { TableColumn colDef = DestinationColumns[destCol]; int ordinal = data.GetOrdinal(destCol); object val = data.GetValue(ordinal); if (val != null) { object convertedVal = System.Convert.ChangeType(data.GetValue(ordinal), colDef.NETDataType); writer.Write(convertedVal, colDef.InternalDataType.ToLower()); } else { writer.WriteNull(); } } } writer.Complete(); } }
protected override void DoBulkInsert(ITableData data) { data.Definition.EnsureColumns(this, true); var TN = new ObjectNameDescriptor(data.Definition.Name, ConnectionManagerType.Postgres); var quotedDestColumns = data.Definition.ColumnsSynchronized.Names().Select(col => TN.QB + col + TN.QE); using (var writer = DbConnection.BeginBinaryImport($@" COPY {TN.QuotatedFullName} ({string.Join(", ", quotedDestColumns)}) FROM STDIN (FORMAT BINARY)")) { foreach (var row in data.EnumerateRowsAndConvertValuesToNETDataType()) { writer.StartRow(); foreach (var column in row) { if (column.value != null) { writer.Write(column.value, column.column.InternalDataType.ToLower()); } else { writer.WriteNull(); } } } writer.Complete(); } }
void InsertSourceData(IConnectionManager connection, ObjectNameDescriptor TN) { SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'I','Insert', 'Test1')"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'U','Update', 'Test2')"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'E','NoChange', 'Test3')"); }
void InsertDestinationData(IConnectionManager connection, ObjectNameDescriptor TN) { SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'U','Update', 'XXX')"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'E','NoChange', 'Test3')"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'D','Delete', 'Test4')"); }
public void MySqlQuotatedTableName() { ObjectNameDescriptor desc = new ObjectNameDescriptor("`Test`", ConnectionManagerType.MySql); Assert.Equal(@"", desc.QuotatedSchemaName); Assert.Equal(@"`Test`", desc.QuotatedObjectName); Assert.Equal(@"`Test`", desc.QuotatedFullName); Assert.Equal(@"Test", desc.UnquotatedObjectName); Assert.Equal(@"Test", desc.UnquotatedFullName); }
public void PostgresWithSchemaAndQuotation() { var desc = new ObjectNameDescriptor(@"""public"".""Test""", @"""", @""""); Assert.Equal(@"""public""", desc.QuotatedSchemaName); Assert.Equal(@"""Test""", desc.QuotatedObjectName); Assert.Equal(@"""public"".""Test""", desc.QuotatedFullName); Assert.Equal(@"Test", desc.UnquotatedObjectName); Assert.Equal(@"public.Test", desc.UnquotatedFullName); }
public void MySqlQuotatedTableNameAndDots() { var desc = new ObjectNameDescriptor("`Test.Test`", "`", "`"); Assert.Equal(@"", desc.QuotatedSchemaName); Assert.Equal(@"`Test.Test`", desc.QuotatedObjectName); Assert.Equal(@"`Test.Test`", desc.QuotatedFullName); Assert.Equal(@"Test.Test", desc.UnquotatedObjectName); Assert.Equal(@"Test.Test", desc.UnquotatedFullName); }
public void SqlServerNoSchemaWithQuotation() { ObjectNameDescriptor desc = new ObjectNameDescriptor("[Test]", ConnectionManagerType.SqlServer); Assert.Equal("", desc.QuotatedSchemaName); Assert.Equal("[Test]", desc.QuotatedObjectName); Assert.Equal("[Test]", desc.QuotatedFullName); Assert.Equal("Test", desc.UnquotatedObjectName); Assert.Equal("Test", desc.UnquotatedFullName); }
public void PostgresWithSchemaAndQuotation() { ObjectNameDescriptor desc = new ObjectNameDescriptor(@"""public"".""Test""", ConnectionManagerType.Postgres); Assert.Equal(@"""public""", desc.QuotatedSchemaName); Assert.Equal(@"""Test""", desc.QuotatedObjectName); Assert.Equal(@"""public"".""Test""", desc.QuotatedFullName); Assert.Equal(@"Test", desc.UnquotatedObjectName); Assert.Equal(@"public.Test", desc.UnquotatedFullName); }
public void SqlServerNoSchemaWithQuotation() { var desc = new ObjectNameDescriptor("[Test]", "[", "]"); Assert.Equal("", desc.QuotatedSchemaName); Assert.Equal("[Test]", desc.QuotatedObjectName); Assert.Equal("[Test]", desc.QuotatedFullName); Assert.Equal("Test", desc.UnquotatedObjectName); Assert.Equal("Test", desc.UnquotatedFullName); }
public void SqlServerWithSchema() { var desc = new ObjectNameDescriptor("dbo.Test", "[", "]"); Assert.Equal("[dbo]", desc.QuotatedSchemaName); Assert.Equal("[Test]", desc.QuotatedObjectName); Assert.Equal("[dbo].[Test]", desc.QuotatedFullName); Assert.Equal("dbo", desc.UnquotatedSchemaName); Assert.Equal("Test", desc.UnquotatedObjectName); Assert.Equal("dbo.Test", desc.UnquotatedFullName); }
public void SqlServerSchemaWithDot() { var desc = new ObjectNameDescriptor("[Foo.Bar].[Test]", "[", "]"); Assert.Equal("[Foo.Bar]", desc.QuotatedSchemaName); Assert.Equal("[Test]", desc.QuotatedObjectName); Assert.Equal("[Foo.Bar].[Test]", desc.QuotatedFullName); Assert.Equal("Foo.Bar", desc.UnquotatedSchemaName); Assert.Equal("Test", desc.UnquotatedObjectName); Assert.Equal("Foo.Bar.Test", desc.UnquotatedFullName); }
void InsertTestData(IConnectionManager connection, string tableName) { ObjectNameDescriptor TN = new ObjectNameDescriptor(tableName, connection); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(1, 10 ,'Test1')"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(2, 10 ,'Test2')"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(3, 10 ,'Test3')"); }
public void SqlServerSchemaWithDot() { ObjectNameDescriptor desc = new ObjectNameDescriptor("[Foo.Bar].[Test]", ConnectionManagerType.SqlServer); Assert.Equal("[Foo.Bar]", desc.QuotatedSchemaName); Assert.Equal("[Test]", desc.QuotatedObjectName); Assert.Equal("[Foo.Bar].[Test]", desc.QuotatedFullName); Assert.Equal("Foo.Bar", desc.UnquotatedSchemaName); Assert.Equal("Test", desc.UnquotatedObjectName); Assert.Equal("Foo.Bar.Test", desc.UnquotatedFullName); }
void AddFKConstraint(IConnectionManager connection, string sourceTableName, string referenceTableName) { ObjectNameDescriptor TN = new ObjectNameDescriptor(sourceTableName, connection); ObjectNameDescriptor TNR = new ObjectNameDescriptor(referenceTableName, connection); SqlTask.ExecuteNonQuery(connection, "Add FK constraint", $@"ALTER TABLE {TN.QuotatedFullName} ADD CONSTRAINT constraint_fk FOREIGN KEY ({TN.QB}Key2{TN.QE}) REFERENCES {TNR.QuotatedFullName}({TNR.QB}Id{TNR.QE}) ON DELETE CASCADE;"); }
private void InsertTestData(IConnectionManager connection, string tableName) { var TN = new ObjectNameDescriptor(tableName, connection.QB, connection.QE); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'\0 \"" \b \n \r \t \Z \\ \% \_ ')"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(2,' '' """" ')"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(3,' !""�$%&/())='' ')"); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(4,NULL)"); }
void ReCreateTable(IConnectionManager connection, ObjectNameDescriptor TN) { DropTableTask.DropIfExists(connection, TN.ObjectName); CreateTableTask.Create(connection, TN.ObjectName, new List <TableColumn>() { new TableColumn("ColKey1", "INT", allowNulls: false, isPrimaryKey: true), new TableColumn("ColKey2", "CHAR(1)", allowNulls: false, isPrimaryKey: true), new TableColumn("ColValue1", "NVARCHAR(100)", allowNulls: true, isPrimaryKey: false), new TableColumn("ColValue2", "NVARCHAR(100)", allowNulls: true, isPrimaryKey: false), }); }
void ReCreateOtherTable(IConnectionManager connection, string tablename) { DropTableTask.DropIfExists(connection, tablename); CreateTableTask.Create(connection, tablename, new List <TableColumn>() { new TableColumn("Id", "INT", allowNulls: false, isPrimaryKey: true), new TableColumn("Other", "VARCHAR(100)", allowNulls: true, isPrimaryKey: false), }); ObjectNameDescriptor TN = new ObjectNameDescriptor(tablename, connection); SqlTask.ExecuteNonQuery(connection, "Insert demo data" , $@"INSERT INTO {TN.QuotatedFullName} VALUES(10,'TestX')"); }
internal void ReadTableDefinition(ObjectNameDescriptor TN, List <TableColumn> columns) { DataTable schemaTable = GetSchemaDataTable(TN.UnquotatedFullName, "Columns"); foreach (var row in schemaTable.Rows) { DataRow dr = row as DataRow; TableColumn col = new TableColumn() { Name = dr[schemaTable.Columns["COLUMN_NAME"]].ToString(), DataType = dr[schemaTable.Columns["TYPE_NAME"]].ToString(), AllowNulls = dr[schemaTable.Columns["IS_NULLABLE"]].ToString() == "YES" ? true : false }; columns.Add(col); } }
private static void AssertFirstRow(IConnectionManager conn, string tableName) { // IntCol LongCol DecimalCol DoubleCol DateTimeCol DateCol StringCol CharCol DecimalStringCol NullCol //1 - 1 2.3 5.4 2010 - 01 - 01 10:10:10.100 2020 - 01 - 01 Test T 13.4566000000 NULL var TN = new ObjectNameDescriptor(tableName, conn.QB, conn.QE); SqlTask.ExecuteReaderSingleLine(conn, "Check data", $"SELECT * FROM {TN.QuotatedFullName} WHERE {conn.QB}intcol{conn.QE} = 1", col => Assert.True(Convert.ToInt32(col) == 1), col => Assert.True(Convert.ToInt64(col) == -1), col => Assert.True(Convert.ToDecimal(col) == 2.3M), col => Assert.True(Convert.ToDecimal(col) == 5.4M), col => Assert.True(Convert.ToDateTime(col) == new DateTime(2010, 1, 1, 10, 10, 10)), col => Assert.True(Convert.ToDateTime(col) == new DateTime(2020, 1, 1)), col => Assert.True(Convert.ToString(col) == "Test"), col => Assert.True(Convert.ToString(col) == "T" || Convert.ToString(col) == "84"), col => Assert.True(Convert.ToString(col).Replace("0", "") == "13.4566"), col => Assert.True(col == null), col => Assert.True(Convert.ToInt32(col) == 2) ); }
public void MergeWithCompositeKey(IConnectionManager connection) { //Arrange ObjectNameDescriptor TNS = new ObjectNameDescriptor("DBMergeSource", connection.QB, connection.QE); ObjectNameDescriptor TND = new ObjectNameDescriptor("DBMergeDestination", connection.QB, connection.QE); ReCreateTable(connection, TNS); ReCreateTable(connection, TND); InsertSourceData(connection, TNS); InsertDestinationData(connection, TND); //Act DbSource <MyMergeRow> source = new DbSource <MyMergeRow>(connection, "DBMergeSource"); DbMerge <MyMergeRow> dest = new DbMerge <MyMergeRow>(connection, "DBMergeDestination"); source.LinkTo(dest); source.Execute(); dest.Wait(); //Assert Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDestination")); Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDestination", $"{TND.QB}ColKey2{TND.QE} = 'E' and {TND.QB}ColValue2{TND.QE} = 'Test3'")); Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDestination", $"{TND.QB}ColKey2{TND.QE} = 'U' and {TND.QB}ColValue2{TND.QE} = 'Test2'")); Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDestination", $"{TND.QB}ColKey2{TND.QE} = 'I' and {TND.QB}ColValue2{TND.QE} = 'Test1'")); }
private static TableDefinition ReadTableDefinitionFromSQLite(IConnectionManager connection, ObjectNameDescriptor TN) { TableDefinition result = new TableDefinition(TN.ObjectName); TableColumn curCol = null; var readMetaSql = new SqlTask( $@"PRAGMA table_info(""{TN.UnquotatedFullName}"")" , () => { curCol = new TableColumn(); } , () => { result.Columns.Add(curCol); } , cid => {; } , name => curCol.Name = name.ToString() , type => curCol.DataType = type.ToString() , notnull => curCol.AllowNulls = (long)notnull == 1 ? true : false , dftl_value => curCol.DefaultValue = dftl_value?.ToString() , pk => curCol.IsPrimaryKey = (long)pk >= 1 ? true : false ) { DisableLogging = true, ConnectionManager = connection, TaskName = $"Read column meta data for table {TN.ObjectName}" }; readMetaSql.ExecuteReader(); return(result); }
private static TableDefinition ReadTableDefinitionFromMySqlServer(IConnectionManager connection, ObjectNameDescriptor TN) { TableDefinition result = new TableDefinition(TN.ObjectName); TableColumn curCol = null; var readMetaSql = new SqlTask( $@" SELECT DISTINCT cols.column_name , CASE WHEN cols.data_type IN ('varchar','char') THEN CONCAT (cols.data_type,'(',cols.character_maximum_length, ')') WHEN cols.data_type IN ('decimal') THEN CONCAT (cols.data_type,'(',cols.numeric_precision,',', cols.numeric_scale, ')') ELSE cols.data_type END AS 'data_type' , CASE WHEN cols.is_nullable = 'NO' THEN 0 ELSE 1 END AS 'is_nullable' , CASE WHEN cols.extra IS NOT NULL AND cols.extra = 'auto_increment' THEN 1 ELSE 0 END AS 'auto_increment' , CASE WHEN isnull(k.constraint_name) THEN 0 ELSE 1 END AS 'primary_key' , cols.column_default , cols.collation_name , cols.generation_expression , cols.column_comment , CASE WHEN tc_uq.CONSTRAINT_TYPE = 'UNIQUE' THEN 1 ELSE 0 END AS 'is_unique' , tc.CONSTRAINT_NAME AS 'pk_name' , tc_uq.CONSTRAINT_NAME AS 'uq_constr_name' , cols.ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS cols INNER JOIN INFORMATION_SCHEMA.TABLES tbl ON cols.table_name = tbl.table_name AND cols.table_schema = tbl.table_schema AND cols.table_catalog = tbl.table_catalog LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON cols.table_name = k.table_name AND cols.table_schema = k.table_schema AND cols.table_catalog = k.table_catalog AND cols.column_name = k.column_name AND k.constraint_name = 'PRIMARY' LEFT JOIN information_schema.TABLE_CONSTRAINTS tc ON k.TABLE_SCHEMA = tc.TABLE_SCHEMA AND k.TABLE_NAME = tc.TABLE_NAME AND k.CONSTRAINT_NAME = tc.CONSTRAINT_NAME LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE uq ON cols.table_name = uq.table_name AND cols.table_schema = uq.table_schema AND cols.table_catalog = uq.table_catalog AND cols.column_name = uq.column_name LEFT JOIN information_schema.TABLE_CONSTRAINTS tc_uq ON uq.TABLE_SCHEMA = tc_uq.TABLE_SCHEMA AND uq.TABLE_NAME = tc_uq.TABLE_NAME AND uq.CONSTRAINT_NAME = tc_uq.CONSTRAINT_NAME AND tc_uq.CONSTRAINT_TYPE = 'UNIQUE' WHERE ( cols.table_name = '{TN.UnquotatedFullName}' OR CONCAT(cols.table_catalog,'.',cols.table_name) = '{TN.UnquotatedFullName}') AND cols.table_schema = DATABASE() ORDER BY cols.ordinal_position " , () => { curCol = new TableColumn(); } , () => { result.Columns.Add(curCol); } , column_name => curCol.Name = column_name.ToString() , data_type => curCol.DataType = data_type.ToString() , is_nullable => curCol.AllowNulls = (int)is_nullable == 1 ? true : false , auto_increment => curCol.IsIdentity = (int)auto_increment == 1 ? true : false , primary_key => curCol.IsPrimaryKey = (int)primary_key == 1 ? true : false , column_default => curCol.DefaultValue = column_default?.ToString() , collation_name => curCol.Collation = collation_name?.ToString() , generation_expression => curCol.ComputedColumn = generation_expression?.ToString() , comment => curCol.Comment = comment?.ToString() , uq_key => curCol.IsUnique = (int)uq_key == 1 ? true : false , pk_name => result.PrimaryKeyConstraintName = String.IsNullOrWhiteSpace(pk_name?.ToString()) ? result.PrimaryKeyConstraintName : pk_name.ToString() , uq_name => result.UniqueKeyConstraintName = String.IsNullOrWhiteSpace(uq_name?.ToString()) ? result.UniqueKeyConstraintName : uq_name.ToString() , ignore => { } ) { DisableLogging = true, ConnectionManager = connection, TaskName = $"Read column meta data for table {TN.ObjectName}" }; readMetaSql.ExecuteReader(); return(result); }
private static TableDefinition ReadTableDefinitionFromPostgres(IConnectionManager connection, ObjectNameDescriptor TN) { TableDefinition result = new TableDefinition(TN.ObjectName); TableColumn curCol = null; var readMetaSql = new SqlTask( $@" SELECT cols.column_name ,CASE WHEN LEFT(cols.data_type,4) = 'time' THEN REPLACE(REPLACE(REPLACE(cols.data_type,'without time zone',''), 'with time zone', 'tz'),' ','') ELSE cols.data_type END AS ""internaldatatype"" ,CASE WHEN cols.domain_name IS NOT NULL THEN domain_name WHEN cols.data_type='character varying' THEN CASE WHEN character_maximum_length IS NULL THEN 'varchar' ELSE 'varchar('||character_maximum_length||')' END WHEN cols.data_type='character' THEN 'char('||character_maximum_length||')' WHEN cols.data_type='numeric' THEN CASE WHEN numeric_precision IS NULL THEN 'numeric' ELSE 'numeric('||numeric_precision||','||numeric_scale||')' END WHEN LEFT(cols.data_type,4) = 'time' THEN REPLACE(REPLACE(REPLACE(cols.data_type,'without time zone',''), 'with time zone', 'tz'),' ','') ELSE cols.data_type END AS ""datatype"" , CASE WHEN cols.is_nullable = 'NO' THEN 0 ELSE 1 END AS ""is_nullable"" , CASE WHEN cols.column_default IS NOT NULL AND substring(cols.column_default,0,8) = 'nextval' THEN 1 ELSE 0 END AS ""serial"" , CASE WHEN tccu.column_name IS NULL THEN 0 ELSE 1 END AS ""primary_key"" , cols.column_default , cols.collation_name , cols.generation_expression , CASE WHEN tccu_uq.column_name IS NULL THEN 0 ELSE 1 END AS ""unique_key"" , tccu.constraint_name , tccu_uq.constraint_name FROM INFORMATION_SCHEMA.COLUMNS cols INNER JOIN INFORMATION_SCHEMA.TABLES tbl ON cols.table_name = tbl.table_name AND cols.table_schema = tbl.table_schema AND cols.table_catalog = tbl.table_catalog LEFT JOIN INFORMATION_SCHEMA.table_constraints tc ON cols.table_name = tc.table_name AND cols.table_schema = tc.table_schema AND cols.table_catalog = tc.table_catalog AND tc.constraint_type = 'PRIMARY KEY' LEFT JOIN information_schema.constraint_column_usage tccu ON cols.table_name = tccu.table_name AND cols.table_schema = tccu.table_schema AND cols.table_catalog = tccu.table_catalog AND tccu.constraint_name = tc.constraint_name AND tccu.constraint_schema = tc.constraint_schema AND tccu.constraint_catalog = tc.constraint_catalog AND cols.column_name = tccu.column_name LEFT JOIN INFORMATION_SCHEMA.table_constraints tc_uq ON cols.table_name = tc_uq.table_name AND cols.table_schema = tc_uq.table_schema AND cols.table_catalog = tc_uq.table_catalog AND tc_uq.constraint_type = 'UNIQUE' LEFT JOIN information_schema.constraint_column_usage tccu_uq ON cols.table_name = tccu_uq.table_name AND cols.table_schema = tccu_uq.table_schema AND cols.table_catalog = tccu_uq.table_catalog AND tccu_uq.constraint_name = tc_uq.constraint_name AND tccu_uq.constraint_schema = tc_uq.constraint_schema AND tccu_uq.constraint_catalog = tc_uq.constraint_catalog AND cols.column_name = tccu_uq.column_name WHERE(cols.table_name = '{TN.UnquotatedFullName}' OR CONCAT(cols.table_schema, '.', cols.table_name) = '{TN.UnquotatedFullName}') AND cols.table_catalog = CURRENT_DATABASE() ORDER BY cols.ordinal_position " , () => { curCol = new TableColumn(); } , () => { result.Columns.Add(curCol); } , column_name => curCol.Name = column_name.ToString() , internal_type_name => curCol.InternalDataType = internal_type_name.ToString() , data_type => curCol.DataType = data_type.ToString() , is_nullable => curCol.AllowNulls = (int)is_nullable == 1 ? true : false , serial => curCol.IsIdentity = (int)serial == 1 ? true : false , primary_key => curCol.IsPrimaryKey = (int)primary_key == 1 ? true : false , column_default => curCol.DefaultValue = column_default?.ToString().ReplaceIgnoreCase("::character varying", "") , collation_name => curCol.Collation = collation_name?.ToString() , generation_expression => curCol.ComputedColumn = generation_expression?.ToString() , uq_key => curCol.IsUnique = (int)uq_key == 1 ? true : false , pk_name => result.PrimaryKeyConstraintName = String.IsNullOrWhiteSpace(pk_name?.ToString()) ? result.PrimaryKeyConstraintName : pk_name.ToString() , uq_name => result.UniqueKeyConstraintName = String.IsNullOrWhiteSpace(uq_name?.ToString()) ? result.UniqueKeyConstraintName : uq_name.ToString() ) { DisableLogging = true, ConnectionManager = connection, TaskName = $"Read column meta data for table {TN.ObjectName}" }; readMetaSql.ExecuteReader(); return(result); }
private static TableDefinition ReadTableDefinitionFromSqlServer(IConnectionManager connection, ObjectNameDescriptor TN) { TableDefinition result = new TableDefinition(TN.ObjectName); TableColumn curCol = null; var readMetaSql = new SqlTask( $@" SELECT cols.name , CASE WHEN tpes.name IN ('varchar','char','binary','varbinary') THEN CONCAT ( UPPER(tpes.name) , '(' , IIF (cols.max_length = -1, 'MAX', CAST(cols.max_length as varchar(20))) , ')' ) WHEN tpes.name IN ('nvarchar','nchar') THEN CONCAT ( UPPER(tpes.name) , '(' , IIF (cols.max_length = -1, 'MAX', CAST( (cols.max_length/2) as varchar(20))) , ')' ) WHEN tpes.name IN ('decimal','numeric') THEN CONCAT ( UPPER(tpes.name) , '(' , cols.precision ,',' ,cols.scale, ')' ) ELSE UPPER(tpes.name) END AS type_name , cols.is_nullable , cols.is_identity , ident.seed_value , ident.increment_value , CONVERT (BIT, CASE WHEN pkidxcols.index_column_id IS NOT NULL THEN 1 ELSE 0 END ) AS primary_key , defconstr.definition AS default_value , cols.collation_name , compCol.definition AS computed_column_definition , CONVERT (BIT, CASE WHEN uqidxcols.index_column_id IS NOT NULL THEN 1 ELSE 0 END ) AS is_unique , CASE WHEN pkidxcols.index_column_id IS NOT NULL THEN pkidx.name ELSE NULL END AS pkkey_name , CASE WHEN uqidxcols.index_column_id IS NOT NULL THEN uqidx.name ELSE NULL END AS uqkey_name FROM sys.columns cols INNER JOIN ( SELECT name, type, object_id, schema_id FROM sys.tables UNION SELECT name, type, object_id, schema_id FROM sys.views ) tbl ON cols.object_id = tbl.object_id INNER JOIN sys.schemas sc ON tbl.schema_id = sc.schema_id INNER JOIN sys.systypes tpes ON tpes.xtype = cols.system_type_id LEFT JOIN sys.identity_columns ident ON ident.object_id = cols.object_id LEFT JOIN sys.indexes pkidx ON pkidx.object_id = cols.object_id AND pkidx.is_primary_key = 1 LEFT JOIN sys.index_columns pkidxcols on pkidxcols.object_id = cols.object_id AND pkidxcols.column_id = cols.column_id AND pkidxcols.index_id = pkidx.index_id LEFT JOIN sys.indexes uqidx ON uqidx.object_id = cols.object_id AND uqidx.is_unique_constraint = 1 LEFT JOIN sys.index_columns uqidxcols on uqidxcols.object_id = cols.object_id AND uqidxcols.column_id = cols.column_id AND uqidxcols.index_id = uqidx.index_id LEFT JOIN sys.default_constraints defconstr ON defconstr.parent_object_id = cols.object_id AND defconstr.parent_column_id = cols.column_id LEFT JOIN sys.computed_columns compCol ON compCol.object_id = cols.object_id WHERE ( CONCAT (sc.name,'.',tbl.name) ='{TN.UnquotatedFullName}' OR tbl.name = '{TN.UnquotatedFullName}' ) AND tbl.type IN ('U','V') AND tpes.name <> 'sysname' ORDER BY cols.column_id " , () => { curCol = new TableColumn(); } , () => { result.Columns.Add(curCol); } , name => curCol.Name = name.ToString() , type_name => curCol.DataType = type_name.ToString() , is_nullable => curCol.AllowNulls = (bool)is_nullable , is_identity => curCol.IsIdentity = (bool)is_identity , seed_value => curCol.IdentitySeed = (int?)(Convert.ToInt32(seed_value)) , increment_value => curCol.IdentityIncrement = (int?)(Convert.ToInt32(increment_value)) , primary_key => curCol.IsPrimaryKey = (bool)primary_key , default_value => curCol.DefaultValue = default_value?.ToString().Substring(2, (default_value.ToString().Length) - 4) , collation_name => curCol.Collation = collation_name?.ToString() , computed_column_definition => curCol.ComputedColumn = computed_column_definition?.ToString().Substring(1, (computed_column_definition.ToString().Length) - 2) , uq_key => curCol.IsUnique = (bool)uq_key , pk_name => result.PrimaryKeyConstraintName = String.IsNullOrWhiteSpace(pk_name?.ToString()) ? result.PrimaryKeyConstraintName : pk_name.ToString() , uq_name => result.UniqueKeyConstraintName = String.IsNullOrWhiteSpace(uq_name?.ToString()) ? result.UniqueKeyConstraintName : uq_name.ToString() ) { DisableLogging = true, ConnectionManager = connection, TaskName = $"Read column meta data for table {TN.ObjectName}" }; readMetaSql.ExecuteReader(); return(result); }
private static TableDefinition ReadTableDefinitionFromOracle(IConnectionManager connection, ObjectNameDescriptor TN) { TableDefinition result = new TableDefinition(TN.ObjectName); TableColumn curCol = null; //Regarding default values: The issue is described partly here //https://stackoverflow.com/questions/46991132/how-to-cast-long-to-varchar2-inline/47041776 string sql = $@" SELECT cols.COLUMN_NAME , CASE WHEN cols.DATA_TYPE IN ('VARCHAR','CHAR', 'NCHAR', 'NVARCHAR', 'NVARCHAR2', 'NCHAR2', 'VARCHAR2', 'CHAR2' ) THEN cols.DATA_TYPE || '(' || cols.CHAR_LENGTH || ')' WHEN cols.DATA_TYPE IN ('NUMBER') THEN cols.DATA_TYPE || '(' ||cols.DATA_LENGTH ||',' || CASE WHEN cols.DATA_SCALE IS NULL THEN 127 ELSE cols.DATA_SCALE END || ')' ELSE cols.DATA_TYPE END AS data_type , cols.NULLABLE , cols.IDENTITY_COLUMN , CASE WHEN cons.CONSTRAINT_TYPE = 'P' THEN 'ENABLED' ELSE NULL END as primary_key , cols.DATA_DEFAULT --not working, see restriction above , cols.COLLATION , cols.DATA_DEFAULT AS generation_expression , CASE WHEN cons.CONSTRAINT_TYPE = 'U' THEN 'ENABLED' ELSE NULL END as unique_key FROM ALL_TAB_COLUMNS cols LEFT JOIN ( SELECT acols.table_name, acols.column_name, acols.position, acons.status, acons.owner, acons.constraint_type FROM ALL_CONSTRAINTS acons, ALL_CONS_COLUMNS acols WHERE acons.CONSTRAINT_TYPE IN ('U','P') AND acons.CONSTRAINT_NAME = acols.CONSTRAINT_NAME AND acons.OWNER = acols.OWNER ) cons ON cons.TABLE_NAME = cols.TABLE_NAME AND cons.OWNER = cols.OWNER --AND cons.position = cols.COLUMN_ID AND cons.column_name = cols.COLUMN_NAME WHERE cols.TABLE_NAME NOT LIKE 'BIN$%' AND cols.OWNER NOT IN ('SYS', 'SYSMAN', 'CTXSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'OUTLN', 'WKSYS', 'WMSYS', 'XDB', 'ORDPLUGINS', 'SYSTEM') AND ( cols.TABLE_NAME = '{TN.UnquotatedFullName}' OR (cols.OWNER || '.' || cols.TABLE_NAME ) = '{TN.UnquotatedFullName}' ) ORDER BY cols.COLUMN_ID "; var readMetaSql = new SqlTask( sql , () => { curCol = new TableColumn(); } , () => { result.Columns.Add(curCol); } , column_name => curCol.Name = column_name.ToString() , data_type => curCol.DataType = data_type.ToString() , nullable => curCol.AllowNulls = nullable.ToString() == "Y" ? true : false , identity_column => curCol.IsIdentity = identity_column?.ToString() == "YES" ? true : false , primary_key => curCol.IsPrimaryKey = primary_key?.ToString() == "ENABLED" ? true : false , data_default => curCol.DefaultValue = data_default?.ToString() , collation => curCol.Collation = collation?.ToString() , generation_expression => curCol.ComputedColumn = generation_expression?.ToString() , uq_key => curCol.IsUnique = uq_key?.ToString() == "ENABLED" ? true : false ) { DisableLogging = true, ConnectionManager = connection, TaskName = $"Read column meta data for table {TN.ObjectName}" }; readMetaSql.ExecuteReader(); return(result); }
private static TableDefinition ReadTableDefinitionFromAccess(IConnectionManager connection, ObjectNameDescriptor TN) { return(connection?.ReadTableDefinition(TN)); }
private static TableDefinition ReadTableDefinitionFromMySqlServer(IConnectionManager connection, ObjectNameDescriptor TN) { TableDefinition result = new TableDefinition(TN.ObjectName); TableColumn curCol = null; var readMetaSql = new SqlTask($"Read column meta data for table {TN.ObjectName}", $@" SELECT cols.column_name , CASE WHEN cols.data_type IN ('varchar','char') THEN CONCAT (cols.data_type,'(',cols.character_maximum_length, ')') WHEN cols.data_type IN ('decimal') THEN CONCAT (cols.data_type,'(',cols.numeric_precision,',', cols.numeric_scale, ')') ELSE cols.data_type END AS 'data_type' , CASE WHEN cols.is_nullable = 'NO' THEN 0 ELSE 1 END AS 'is_nullable' , CASE WHEN cols.extra IS NOT NULL AND cols.extra = 'auto_increment' THEN 1 ELSE 0 END AS 'auto_increment' , CASE WHEN isnull(k.constraint_name) THEN 0 ELSE 1 END AS 'primary_key' , cols.column_default , cols.collation_name , cols.generation_expression , cols.column_comment FROM INFORMATION_SCHEMA.COLUMNS cols INNER JOIN INFORMATION_SCHEMA.TABLES tbl ON cols.table_name = tbl.table_name AND cols.table_schema = tbl.table_schema AND cols.table_catalog = tbl.table_catalog LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON cols.table_name = k.table_name AND cols.table_schema = k.table_schema AND cols.table_catalog = k.table_catalog AND cols.column_name = k.column_name AND k.constraint_name = 'PRIMARY' WHERE ( cols.table_name = '{TN.UnquotatedFullName}' OR CONCAT(cols.table_catalog,'.',cols.table_name) = '{TN.UnquotatedFullName}') AND cols.table_schema = DATABASE() ORDER BY cols.ordinal_position " , () => { curCol = new TableColumn(); } , () => { result.Columns.Add(curCol); } , column_name => curCol.Name = column_name.ToString() , data_type => curCol.DataType = data_type.ToString() , is_nullable => curCol.AllowNulls = (int)is_nullable == 1 ? true : false , auto_increment => curCol.IsIdentity = (int)auto_increment == 1 ? true : false , primary_key => curCol.IsPrimaryKey = (int)primary_key == 1 ? true : false , column_default => curCol.DefaultValue = column_default?.ToString() , collation_name => curCol.Collation = collation_name?.ToString() , generation_expression => curCol.ComputedColumn = generation_expression?.ToString() , comment => curCol.Comment = comment?.ToString() ) { DisableLogging = true, ConnectionManager = connection }; readMetaSql.ExecuteReader(); return(result); }