@Override
protected void addTo(DatabaseObject foundObject, DatabaseSnapshot snapshot)
throws DatabaseException, InvalidExampleException {
if (!snapshot.getSnapshotControl().shouldInclude(Column.class)) {
return;
}
if (foundObject instanceof Relation) {
Database database = snapshot.getDatabase();
Relation relation = (Relation) foundObject;
List<CachedRow> allColumnsMetadataRs = null;
try {
JdbcDatabaseSnapshot.CachingDatabaseMetaData databaseMetaData =
((JdbcDatabaseSnapshot) snapshot).getMetaData();
Schema schema;
schema = relation.getSchema();
allColumnsMetadataRs =
databaseMetaData.getColumns(
((AbstractJdbcDatabase) database).getJdbcCatalogName(schema),
((AbstractJdbcDatabase) database).getJdbcSchemaName(schema),
relation.getName(),
null);
for (CachedRow row : allColumnsMetadataRs) {
Column exampleColumn =
new Column()
.setRelation(relation)
.setName(StringUtils.trimToNull(row.getString("COLUMN_NAME")));
relation.getColumns().add(exampleColumn);
}
} catch (Exception e) {
throw new DatabaseException(e);
}
}
}
@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);
}
}