@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);
}
}
