protected Object readDefaultValue(
CachedRow columnMetadataResultSet, Column columnInfo, Database database)
throws SQLException, DatabaseException {
if (database instanceof MSSQLDatabase) {
Object defaultValue = columnMetadataResultSet.get("COLUMN_DEF");
if (defaultValue != null && defaultValue instanceof String) {
if (defaultValue.equals("(NULL)")) {
columnMetadataResultSet.set("COLUMN_DEF", null);
}
}
}
if (database instanceof OracleDatabase) {
if (columnMetadataResultSet.get("COLUMN_DEF") == null) {
columnMetadataResultSet.set("COLUMN_DEF", columnMetadataResultSet.get("DATA_DEFAULT"));
if (columnMetadataResultSet.get("COLUMN_DEF") != null
&& ((String) columnMetadataResultSet.get("COLUMN_DEF")).equalsIgnoreCase("NULL")) {
columnMetadataResultSet.set("COLUMN_DEF", null);
}
Object columnDef = columnMetadataResultSet.get("COLUMN_DEF");
if (columnInfo.getType().getTypeName().equalsIgnoreCase("CHAR")
&& columnDef instanceof String
&& !((String) columnDef).startsWith("'")
&& !((String) columnDef).endsWith("'")) {
return new DatabaseFunction((String) columnDef);
}
if (columnMetadataResultSet.get("VIRTUAL_COLUMN").equals("YES")) {
Object column_def = columnMetadataResultSet.get("COLUMN_DEF");
if (column_def != null && !column_def.equals("null")) {
columnMetadataResultSet.set("COLUMN_DEF", "GENERATED ALWAYS AS (" + column_def + ")");
}
}
}
}
return SqlUtil.parseValue(
database, columnMetadataResultSet.get("COLUMN_DEF"), columnInfo.getType());
}
@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);
}
}
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;
}
protected Column readColumn(CachedRow columnMetadataResultSet, Relation table, Database database)
throws SQLException, DatabaseException {
String rawTableName = (String) columnMetadataResultSet.get("TABLE_NAME");
String rawColumnName = (String) columnMetadataResultSet.get("COLUMN_NAME");
String rawSchemaName =
StringUtils.trimToNull((String) columnMetadataResultSet.get("TABLE_SCHEM"));
String rawCatalogName =
StringUtils.trimToNull((String) columnMetadataResultSet.get("TABLE_CAT"));
String remarks = StringUtils.trimToNull((String) columnMetadataResultSet.get("REMARKS"));
if (remarks != null) {
remarks = remarks.replace("''", "'"); // come back escaped sometimes
}
Column column = new Column();
column.setName(StringUtils.trimToNull(rawColumnName));
column.setRelation(table);
column.setRemarks(remarks);
if (database instanceof OracleDatabase) {
String nullable = columnMetadataResultSet.getString("NULLABLE");
if (nullable.equals("Y")) {
column.setNullable(true);
} else {
column.setNullable(false);
}
} else {
int nullable = columnMetadataResultSet.getInt("NULLABLE");
if (nullable == DatabaseMetaData.columnNoNulls) {
column.setNullable(false);
} else if (nullable == DatabaseMetaData.columnNullable) {
column.setNullable(true);
} else if (nullable == DatabaseMetaData.columnNullableUnknown) {
LogFactory.getLogger()
.info("Unknown nullable state for column " + column.toString() + ". Assuming nullable");
column.setNullable(true);
}
}
if (database.supportsAutoIncrement()) {
if (table instanceof Table) {
if (columnMetadataResultSet.containsColumn("IS_AUTOINCREMENT")) {
String isAutoincrement = (String) columnMetadataResultSet.get("IS_AUTOINCREMENT");
isAutoincrement = StringUtils.trimToNull(isAutoincrement);
if (isAutoincrement == null) {
column.setAutoIncrementInformation(null);
} else if (isAutoincrement.equals("YES")) {
column.setAutoIncrementInformation(new Column.AutoIncrementInformation());
} else if (isAutoincrement.equals("NO")) {
column.setAutoIncrementInformation(null);
} else if (isAutoincrement.equals("")) {
LogFactory.getLogger()
.info(
"Unknown auto increment state for column "
+ column.toString()
+ ". Assuming not auto increment");
column.setAutoIncrementInformation(null);
} else {
throw new UnexpectedLiquibaseException(
"Unknown is_autoincrement value: '" + isAutoincrement + "'");
}
} else {
// probably older version of java, need to select from the column to find out if it is
// auto-increment
String selectStatement;
if (database.getDatabaseProductName().startsWith("DB2 UDB for AS/400")) {
selectStatement =
"select "
+ database.escapeColumnName(
rawCatalogName, rawSchemaName, rawTableName, rawColumnName)
+ " from "
+ rawSchemaName
+ "."
+ rawTableName
+ " where 0=1";
LogFactory.getLogger().debug("rawCatalogName : <" + rawCatalogName + ">");
LogFactory.getLogger().debug("rawSchemaName : <" + rawSchemaName + ">");
LogFactory.getLogger().debug("rawTableName : <" + rawTableName + ">");
LogFactory.getLogger().debug("raw selectStatement : <" + selectStatement + ">");
} else {
selectStatement =
"select "
+ database.escapeColumnName(
rawCatalogName, rawSchemaName, rawTableName, rawColumnName)
+ " from "
+ database.escapeTableName(rawCatalogName, rawSchemaName, rawTableName)
+ " where 0=1";
}
LogFactory.getLogger()
.debug(
"Checking "
+ rawTableName
+ "."
+ rawCatalogName
+ " for auto-increment with SQL: '"
+ selectStatement
+ "'");
Connection underlyingConnection =
((JdbcConnection) database.getConnection()).getUnderlyingConnection();
Statement statement = null;
ResultSet columnSelectRS = null;
try {
statement = underlyingConnection.createStatement();
columnSelectRS = statement.executeQuery(selectStatement);
if (columnSelectRS.getMetaData().isAutoIncrement(1)) {
column.setAutoIncrementInformation(new Column.AutoIncrementInformation());
} else {
column.setAutoIncrementInformation(null);
}
} finally {
try {
if (statement != null) {
statement.close();
}
} catch (SQLException ignore) {
}
if (columnSelectRS != null) {
columnSelectRS.close();
}
}
}
}
}
DataType type = readDataType(columnMetadataResultSet, column, database);
column.setType(type);
column.setDefaultValue(readDefaultValue(columnMetadataResultSet, column, database));
return column;
}
