Hi,
I’m trying to implement the jdbcDatabaseSnapshotGenerator.readUniqueConstraints() function so I can get unique constraints to work properly. Right now I’m hard-coding it for DB2, but I’ll abstract the query out to be DB agnostic later. Here’s what I was thinking of doing, but I’m not sure where I should be saving the Schema name? Should I be saving the Table name as “SchemaName.TableName” or just “TableName”?
- protected void readUniqueConstraints(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData) throws DatabaseException, SQLException {
Database database = snapshot.getDatabase();
updateListeners("Reading unique constraints for " + database.toString() + " ...");
// For DB2, query the system tables like this:
// “SELECT tc.CONSTNAME, tc.TABSCHEMA, tc.TABNAME, skc.COLNAME FROM syscat.TABCONST tc, sysibm.SYSKEYCOLUSE skc WHERE tc.TYPE = ‘U’ AND skc.CONSTNAME = tc.CONSTNAME”
ResultSet rs = null;
try {
rs = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement().executeQuery(
“SELECT tc.CONSTNAME, tc.TABSCHEMA, tc.TABNAME, skc.COLNAME FROM syscat.TABCONST tc, sysibm.SYSKEYCOLUSE skc WHERE tc.TYPE = ‘U’ AND skc.CONSTNAME = tc.CONSTNAME” );
List foundUCs = new ArrayList();
while ( rs.next() )
{
String constName = rs.getString( “CONSTNAME” );
String schemaName = rs.getString( “TABSCHEMA” );
String tableName = rs.getString( “TABNAME” );
String columnName = rs.getString( “COLNAME” );
UniqueConstraint unique = new UniqueConstraint();
unique.setName( constName );
// unique.setSchemaName( schemaName ); // There is no setSchemaName() function, so where do I save this info?
unique.setTable( snapshot.getTable( tableName ) );
unique.getColumns().addAll( Arrays.asList( columnName.split( “,” ) ) );
foundUCs.add( unique );
}
snapshot.getUniqueConstraints().addAll( foundUCs );
} finally {
if ( rs != null ) {
rs.close();
}
}
}