View Revisions: Issue #6605

All RevisionsBack to Issue
Summary 0006605: Support for cross platform BIT data type size > 1 (bit varying, varbit, bit string, bit array)
Revision 2024-10-01 23:14 by elong
Description Add cross platform support for BIT array style data types with a size greater than 1:

   PostgreSQL - BIT(n) and BIT VARYING(n), unlimited length, unspecified is unlimited, string literal b'101'
   https://www.postgresql.org/docs/current/datatype-bit.html

   MySQL / MariaDB - BIT(n), range of 1 to 64, unspecified is 1, string literal b'101'
   https://dev.mysql.com/doc/refman/8.4/en/bit-type.html

   SQL Anywhere - VARBIT(n), range of 1 to 32767, unspecified is 1, cast( '101' as VARBIT )
   https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.0/dbreference/datatypes-s-3941580.html

Each platform behaves a little differently. MySQL will accept byte[] or an Integer or BigDecimal, but String causes each character's hex code to be encoded -- String will work as expected if the prepared statement uses vendor-specific "b?" syntax in the SQL for the parameter. Postgres will accept byte[] or String. SQL Anywhere accepts at least byte[], other types weren't tested.

Other platforms have BIT, but the size is 1, so it gets mapped as Boolean in JDBC. It might be useful to be backwards compatible so that a BIT(1) acts like a Boolean, but BIT(n) where n > 1 is treated differently.

If hex encoding is chosen as the capture and transport format, then the following code changes would make sense. In the DdlReader for each platform, the mapUnknownJdbcTypeForColumn() can be used to override the type mapping to affect triggers and loading:

} else if ("BIT".equals(typeName)) {
    String columnSize = (String) values.get(getName("COLUMN_SIZE"));
    int size = StringUtils.isNotBlank(columnSize) ? Integer.parseInt(columnSize) : 1; // specified or unspecified size
    if (size > 1) {
        return Types.BINARY;
    } else {
        return super.mapUnknownJdbcTypeForColumn(values);
    }
}

In AbstractDatabasePlatform.getObjectValue(), make sure Hex string has even number of characters, otherwise it should be preceded with a zero (the MySQL hex() function is an example of sourcing odd number of characters):

} else if (encoding == BinaryEncoding.HEX) {
    if (value.length() % 2 == 1) {
        value = "0" + value;
    }
    objectValue = Hex.decodeHex(value.toCharArray());
}

For testing on MySQL, the export_set(bits, on, off, separator, num_bits) function is handy:

select id, export_set(mybits,'1', '0', '', 16) from sites;
Revision 2024-10-01 23:11 by elong
Description Add cross platform support for BIT array style data types with a size greater than 1:

   PostgreSQL - BIT(n) and BIT VARYING(n), unlimited length, unspecified is unlimited, string literal b'101'
   https://www.postgresql.org/docs/current/datatype-bit.html

   MySQL / MariaDB - BIT(n), range of 1 to 64, unspecified is 1, string literal b'101'
   https://dev.mysql.com/doc/refman/8.4/en/bit-type.html

   SQL Anywhere - VARBIT(n), range of 1 to 32767, unspecified is 1, cast( '101' as VARBIT )
   https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.0/dbreference/datatypes-s-3941580.html

Each platform behaves a little differently. MySQL will accept byte[] or an Integer or BigDecimal, but String causes each character's hex code to be encoded -- String will work as expected if the prepared statement uses vendor-specific "b?" syntax in the SQL for the parameter. Postgres will accept byte[] or String. SQL Anywhere accepts at least byte[], other types weren't tested.

Other platforms have BIT, but the size is 1, so it gets mapped as Boolean in JDBC. It might be useful to be backwards compatible so that a BIT(1) acts like a Boolean, but BIT(n) where n > 1 is treated differently.

If hex encoding is chosen as the capture and transport format, then the following code changes would make sense. In the DdlReader for each platform, the mapUnknownJdbcTypeForColumn() can be used to override the type mapping to affect triggers and loading:

} else if ("BIT".equals(typeName)) {
    String columnSize = (String) values.get(getName("COLUMN_SIZE"));
    int size = StringUtils.isNotBlank(columnSize) ? Integer.parseInt(columnSize) : 1; // specified or unspecified size
    if (size > 1) {
        return Types.BINARY;
    } else {
        return super.mapUnknownJdbcTypeForColumn(values);
    }
}

In AbstractDatabasePlatform.getObjectValue(), make sure Hex string has even number of characters, otherwise it should be preceded with a zero (the MySQL hex() function is an example of sourcing odd number of characters):

} else if (encoding == BinaryEncoding.HEX) {
    if (value.length() % 2 == 1) {
        value = "0" + value;
    }
    objectValue = Hex.decodeHex(value.toCharArray());
}