@Override protected void configureColumnType(Column column, ResultSet rs) throws SQLException { if (integerList.contains(column.getTable().getName() + "." + column.getName())) { column.setDataType(Types.INTEGER); } else { column.setDataType(rs.getInt("DATA_TYPE")); } column.setColumnSize(rs.getInt("COLUMN_SIZE")); column.setDecimalDigits(rs.getInt("DECIMAL_DIGITS")); // Set true, if precision should be initialize column.setInitPrecision( !((column.getDataType() == Types.DECIMAL || column.getDataType() == Types.NUMERIC || column.getDataType() == Types.REAL) && rs.getString("DECIMAL_DIGITS") == null)); }
protected DataType readDataType( CachedRow columnMetadataResultSet, Column column, Database database) throws SQLException { if (database instanceof OracleDatabase) { String dataType = columnMetadataResultSet.getString("DATA_TYPE"); dataType = dataType.replace("VARCHAR2", "VARCHAR"); dataType = dataType.replace("NVARCHAR2", "NVARCHAR"); DataType type = new DataType(dataType); // type.setDataTypeId(dataType); if (dataType.equalsIgnoreCase("NUMBER")) { type.setColumnSize(columnMetadataResultSet.getInt("DATA_PRECISION")); // if (type.getColumnSize() == null) { // type.setColumnSize(38); // } type.setDecimalDigits(columnMetadataResultSet.getInt("DATA_SCALE")); // if (type.getDecimalDigits() == null) { // type.setDecimalDigits(0); // } // type.setRadix(10); } else { type.setColumnSize(columnMetadataResultSet.getInt("DATA_LENGTH")); if (dataType.equalsIgnoreCase("NCLOB") || dataType.equalsIgnoreCase("BLOB") || dataType.equalsIgnoreCase("CLOB")) { type.setColumnSize(null); } else if (dataType.equalsIgnoreCase("NVARCHAR") || dataType.equalsIgnoreCase("NCHAR")) { type.setColumnSize(columnMetadataResultSet.getInt("CHAR_LENGTH")); type.setColumnSizeUnit(DataType.ColumnSizeUnit.CHAR); } else { String charUsed = columnMetadataResultSet.getString("CHAR_USED"); DataType.ColumnSizeUnit unit = null; if ("C".equals(charUsed)) { unit = DataType.ColumnSizeUnit.CHAR; type.setColumnSize(columnMetadataResultSet.getInt("CHAR_LENGTH")); } type.setColumnSizeUnit(unit); } } return type; } String columnTypeName = (String) columnMetadataResultSet.get("TYPE_NAME"); if (database instanceof FirebirdDatabase) { if (columnTypeName.equals("BLOB SUB_TYPE 0")) { columnTypeName = "BLOB"; } if (columnTypeName.equals("BLOB SUB_TYPE 1")) { columnTypeName = "CLOB"; } } if (database instanceof MySQLDatabase && (columnTypeName.equalsIgnoreCase("ENUM") || columnTypeName.equalsIgnoreCase("SET"))) { try { String boilerLength; if (columnTypeName.equalsIgnoreCase("ENUM")) boilerLength = "7"; else // SET boilerLength = "6"; List<String> enumValues = ExecutorService.getInstance() .getExecutor(database) .queryForList( new RawSqlStatement( "SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE, " + boilerLength + ", LENGTH(COLUMN_TYPE) - " + boilerLength + " - 1 ), \"','\", 1 + units.i + tens.i * 10) , \"','\", -1)\n" + "FROM INFORMATION_SCHEMA.COLUMNS\n" + "CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units\n" + "CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens\n" + "WHERE TABLE_NAME = '" + column.getRelation().getName() + "' \n" + "AND COLUMN_NAME = '" + column.getName() + "'"), String.class); String enumClause = ""; for (String enumValue : enumValues) { enumClause += "'" + enumValue + "', "; } enumClause = enumClause.replaceFirst(", $", ""); return new DataType(columnTypeName + "(" + enumClause + ")"); } catch (DatabaseException e) { LogFactory.getLogger().warning("Error fetching enum values", e); } } DataType.ColumnSizeUnit columnSizeUnit = DataType.ColumnSizeUnit.BYTE; int dataType = columnMetadataResultSet.getInt("DATA_TYPE"); Integer columnSize = null; Integer decimalDigits = null; if (!database.dataTypeIsNotModifiable( columnTypeName)) { // don't set size for types like int4, int8 etc columnSize = columnMetadataResultSet.getInt("COLUMN_SIZE"); decimalDigits = columnMetadataResultSet.getInt("DECIMAL_DIGITS"); if (decimalDigits != null && decimalDigits.equals(0)) { decimalDigits = null; } } Integer radix = columnMetadataResultSet.getInt("NUM_PREC_RADIX"); Integer characterOctetLength = columnMetadataResultSet.getInt("CHAR_OCTET_LENGTH"); if (database instanceof DB2Database) { String typeName = columnMetadataResultSet.getString("TYPE_NAME"); if (typeName.equalsIgnoreCase("DBCLOB") || typeName.equalsIgnoreCase("GRAPHIC") || typeName.equalsIgnoreCase("VARGRAPHIC")) { if (columnSize != null) { columnSize = columnSize / 2; // Stored as double length chars } } } DataType type = new DataType(columnTypeName); type.setDataTypeId(dataType); type.setColumnSize(columnSize); type.setDecimalDigits(decimalDigits); type.setRadix(radix); type.setCharacterOctetLength(characterOctetLength); type.setColumnSizeUnit(columnSizeUnit); return type; }
@Override protected DatabaseObject snapshotObject(DatabaseObject example, DatabaseSnapshot snapshot) throws DatabaseException, InvalidExampleException { Database database = snapshot.getDatabase(); Relation relation = ((Column) example).getRelation(); if (((Column) example).getComputed() != null && ((Column) example).getComputed()) { return example; } Schema schema = relation.getSchema(); List<CachedRow> columnMetadataRs = null; try { JdbcDatabaseSnapshot.CachingDatabaseMetaData databaseMetaData = ((JdbcDatabaseSnapshot) snapshot).getMetaData(); columnMetadataRs = databaseMetaData.getColumns( ((AbstractJdbcDatabase) database).getJdbcCatalogName(schema), ((AbstractJdbcDatabase) database).getJdbcSchemaName(schema), relation.getName(), example.getName()); if (columnMetadataRs.size() > 0) { CachedRow data = columnMetadataRs.get(0); Column column = readColumn(data, relation, database); if (column != null && database instanceof MSSQLDatabase && database.getDatabaseMajorVersion() >= 8) { String sql; if (database.getDatabaseMajorVersion() >= 9) { // SQL Server 2005 or later // https://technet.microsoft.com/en-us/library/ms177541.aspx sql = "SELECT CAST([ep].[value] AS [nvarchar](MAX)) AS [REMARKS] " + "FROM [sys].[extended_properties] AS [ep] " + "WHERE [ep].[class] = 1 " + "AND [ep].[major_id] = OBJECT_ID(N'" + database.escapeStringForDatabase( database.escapeTableName( schema.getCatalogName(), schema.getName(), relation.getName())) + "') " + "AND [ep].[minor_id] = COLUMNPROPERTY([ep].[major_id], N'" + database.escapeStringForDatabase(column.getName()) + "', 'ColumnId') " + "AND [ep].[name] = 'MS_Description'"; } else { // SQL Server 2000 // https://technet.microsoft.com/en-us/library/aa224810%28v=sql.80%29.aspx sql = "SELECT CAST([p].[value] AS [ntext]) AS [REMARKS] " + "FROM [dbo].[sysproperties] AS [p] " + "WHERE [p].[id] = OBJECT_ID(N'" + database.escapeStringForDatabase( database.escapeTableName( schema.getCatalogName(), schema.getName(), relation.getName())) + "') " + "AND [p].[smallid] = COLUMNPROPERTY([p].[id], N'" + database.escapeStringForDatabase(column.getName()) + "', 'ColumnId') " + "AND [p].[type] = 4 " + "AND [p].[name] = 'MS_Description'"; } List<String> remarks = ExecutorService.getInstance() .getExecutor(snapshot.getDatabase()) .queryForList(new RawSqlStatement(sql), String.class); if (remarks != null && remarks.size() > 0) { column.setRemarks(StringUtils.trimToNull(remarks.iterator().next())); } } return column; } else { return null; } } catch (Exception e) { throw new DatabaseException(e); } }