How to exclude system-generated Indexes from Missing/Unexpected Indexes list

Liquibase Development
1

Hi,
I’m not sure if anyone out there actually wants to have the missing/unexpected Indexes that the databases automatically create whenever you add a Unique Constraint, but I know I had to go to great lengths to remove them from the Liquibase Diff results.  I found a good way to remove them from the results returned by DB2; but for HSQL & MySQL I’ve only been able to come up with flimsy hacks, so if anyone knows a good way of querying HSQL & MySQL only for user created Indexes I’d love to know.

Here’s how to do it for DB2:
Add the following else if condition to JdbcDatabaseSnapshotGenerator.readIndexes() just after the if (database instanceof OracleDatabase) { line:

                } else if ( database instanceof DB2Database ) {                 statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();                 String sql = "SELECT idx.TABNAME AS TABLE_NAME, CASE WHEN idx.UNIQUERULE = 'D' THEN 1 ELSE 0 END AS NON_UNIQUE, "         + "idx.INDNAME AS INDEX_NAME, 3 AS TYPE, idxcol.COLSEQ AS ORDINAL_POSITION, idxcol.COLNAME AS COLUMN_NAME "         + "FROM SYSCAT.INDEXES idx, SYSCAT.INDEXCOLUSE idxcol WHERE idx.INDNAME = idxcol.INDNAME AND idx.USER_DEFINED=1 AND idx.TABSCHEMA = '"         + database.convertRequestedSchemaToSchema(schema) + "' AND idx.TABNAME = '" + table.getName() + "' ORDER BY idx.INDNAME, idxcol.COLSEQ";

                    rs = statement.executeQuery(sql);

Then in the while (rs.next()) { block below it, change this:

                    String filterCondition = rs.getString("FILTER_CONDITION");
to this:
                    String filterCondition = "";                 if ( !(database instanceof DB2Database) ) {                 filterCondition = rs.getString("FILTER_CONDITION");                 }
2

(sorry for the slow reply.  I’ve been on vacation and then very far behind)

Handling user indexes vs. generated indexes is a persistent problem.  I haven’t found a good solution so far for most databases, but plan on looking into it more as part of liquibase 2.1, which will focus on the diff tool.  Any help and suggestions anyone has would be greatly appreciated.

Nathan