Diffing 2 different DBs with same data shows differences

General Discussion
1

Hi,
I’m sure some people must have run into this problem before…
I’m installing the same schema to DB2 and HSQL, then doing a diff between them and I’m getting differences such as:

        NOT_NULL_TBL.BLOB_1MB           from VARBINARY(1048576) to BLOB     NOT_NULL_TBL.CLOB_1MB           from VARCHAR(1048576) to CLOB

The reason is because in HSQL, Liquibase is calls BLOBs & CLOBs – VARBINARY & VARCHAR.
I tried modifying the Liquibase Diff code to say if the type is “VARCHAR” just call it a CLOB instead, and it fixed it for HSQL / DB2 diffing, but then when I diff other DBs like HSQL / MySQL I get the error again because they call it yet another name.  I found that depending on the DB, a CLOB could be called:  TEXT,  LONG VARCHAR,  LONGVARCHAR,  or  BLOB SUB_TYPE TEXT
Obviously the hack that I wrote isn’t a very good solution; so I’m wondering how other people are handling these type name mismatches?

2

One solution I think might work would be to modify Column.getDataTypeString() to have a case like this:

    if ( (database instanceof HsqlDatabase) && (this.getDataType() == "VARCHAR") && (this.getColumnSize() == 1048576) ) {   translatedTypeName = "CLOB"; }
What do you guys think?
3

Nathan will confirm but I think that diffing different databases perfectly is difficult and is out of the scope of the current 2.0 version.

4

I believe we fixed it by adding this to Column.isDataTypeDifferent()

                // Hack: HSQL doesn't seem to return LOBs which are inserted into it             // convert varchar/varbinary of default size into clob/blobs             // TODO: consider putting this sort of code in HsqlTypeConverter             if (this.getTable().getDatabase() instanceof HsqlDatabase) {             final int DEFAULT_HSQL_LOB_SIZE = 1048576;             if (this.getDataType() == Types.VARCHAR && this.getColumnSize() == DEFAULT_HSQL_LOB_SIZE) {             thisDataType = "CLOB";             }             if (this.getDataType() == Types.VARBINARY && this.getColumnSize() == DEFAULT_HSQL_LOB_SIZE) {             thisDataType = "BLOB";             }             }

                if (otherColumn.getTable().getDatabase() instanceof HsqlDatabase) {
                final int DEFAULT_HSQL_LOB_SIZE = 1048576;
                if (otherColumn.getDataType() == Types.VARCHAR && otherColumn.getColumnSize() == DEFAULT_HSQL_LOB_SIZE) {
                otherDataType = “CLOB”;
                }
                if (otherColumn.getDataType() == Types.VARBINARY && otherColumn.getColumnSize() == DEFAULT_HSQL_LOB_SIZE) {
                otherDataType = “BLOB”;
                }
                }

5

That does work around the problem in this case, but it is part of a general problem related to comparing different database types.  The same issue will occur with database that return “float” vs “real” etc.  Then there is more vague cases like comparing “decimal” with “money”.

I have some ideas, mainly around having each database convert from database-specific types to generic types, but like lujop said, I am pushing improvements to the diff tool to 2.1 so that I can get 2.0 out.

Nathan

6
Originally posted by: Nathan
That does work around the problem in this case, but it is part of a general problem related to comparing different database types.  The same issue will occur with database that return "float" vs "real" etc.  Then there is more vague cases like comparing "decimal" with "money".

I already “fixed” that problem in an ugly brute force hammer sort of way:

                  if ( otherDataType.equals( "CHARACTER" ) )      { otherDataType = "CHAR"; }               if ( thisDataType.equals(  "BOOLEAN" ) )        { thisDataType  = "BIT"; }               if ( otherDataType.equals( "BOOLEAN" ) )        { otherDataType = "BIT"; }               if ( thisDataType.equals(  "FLOAT" ) )          { thisDataType  = "DOUBLE"; }               if ( otherDataType.equals( "FLOAT" ) )          { otherDataType = "DOUBLE"; }               if ( thisDataType.equals(  "REAL" ) )          { thisDataType  = "DOUBLE"; }               if ( otherDataType.equals( "REAL" ) )          { otherDataType = "DOUBLE"; }
I'm sure it'll probably cause problems with certain database combinations, but it works for DB2, HSQL & MySQL.  Although I'll feel better once you put your proper fix in so I can remove this hack.  :)
7

Glad you have a work around for now.  Keep an eye out for changes in 2.1.  This is definitely a use case I have in mind to support.

Nathan