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