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 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 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() ) { 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 , cons.status as primary_key , cols.DATA_DEFAULT --not working, see restriction above , cols.COLLATION , cols.DATA_DEFAULT AS generation_expression FROM ALL_TAB_COLUMNS cols LEFT JOIN ( SELECT acols.table_name, acols.column_name, acols.position, acons.status, acons.owner FROM ALL_CONSTRAINTS acons, ALL_CONS_COLUMNS acols WHERE acons.CONSTRAINT_TYPE = '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 cols.COLUMN_NAME = cons.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() ) { 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 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.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) ) { DisableLogging = true, ConnectionManager = connection, TaskName = $"Read column meta data for table {TN.ObjectName}" }; readMetaSql.ExecuteReader(); return(result); }