Create suite of sanity tests across DBs and types for equivalency

[chadlwilson]

Context / Goal

Since the tool is designed to help you validate migration of data across different schema types and even (relational) database implementations, and primarily works based on hashed data representing a row in a dataset, we want to have a way to validate that it is actually working correctly for this purpose.

Since there are potentially differences in the way drivers handle things such as character encodings, number types, timestamp/data types we want to ensure that the hashed data representing one data type in one database is considered hash-identical to that of another. If there is not, there should be a way to ensure they are so using something simple in SQL, or we should probably change our implementation.

Expected Outcome

• Modify/refactor the MultiDataSourceConnectivityIntegrationTest from Configure integration test tooling to allow testing across DB types #28 to instead of just testing R2DBC connectivity via Micronaut, instead have a suite of simple scenarios that focus on real integration testing scenarios, focused on type differences
  • It is likely that the hashing impl in HashedRow is not going to work correctly. An int with value 10 in one DB will likely not be considered equal to a long with value 10 in another DB, and similar with other types. We will have to make some decisions about how this should work to canonicalize values, and how configurable it needs to be.
  • Should a string of "10" be considered equal to a bigint of 10?
• Run simple, but real reconciliations that focus on ensuring that hashed values from one DB of a given type are equivalent to that of a different DB

Out of Scope

• Anything we don't support in terms of types (without SQL coercion) from Support mapping/hashing of more common data types #27

Additional context / implementation notes

• At time of writing we are using the Exposed framework in test code to generate schemas for testing with. This may not give us the level of control over data types in the databases that we require, and may need to be re-evaluated.
• Possibly these tests could run in a matrix style with simple data set queries on each side like SELECT id as MigrationKey, test_type_column FROM testdata, creating a single table with a single test data column per test
  • Dimension 1: DB (mysql, postgres, mssql)
  • Dimension 2: DB Type under test (CHAR/VARCHAR, INT/INTEGER, BIGINT, NUMERIC/DECIMAL/REAL/FLOAT, DATETIME/DATE/TIME/TIMESTAMP etc)
• Types
  • PostGres Data types
  • MySQL Data types
  • MS SQL Server Data types

[chadlwilson]

This is done now.

As part of this

• it was immediately obvious that the previous hashing approach was not going to work well and was too strict and lead to differences across DBs, simply due to the drivers deserializing types slightly differently. As a result HashingStrategy was introduced and TypeLenient added and made the default approach. This leads to a lot better results.
• MariaDB testing was added
• MultiDataSourceConnectivityIntegrationTest was reborn as DatasetRecServiceCrossDatabaseIntegrationTest
• The Exposed framework was removed for setting up test data, to avoid having yet another tool to figure out, instead allowing use of Flyway programmatically as in https://github.com/ThoughtWorks-SEA/recce/blob/eaf61c7c59774d3451a1d6e9348e8014928c6786/src/test/kotlin/recce/server/dataset/datasource/FlywayUtil.kt#L11-L26
  • Exposed didn't really enough flexibility to define raw types and do things programmatically to generate tests
  • Flyway is far more manual, but at least is an existing tool in use. Since you have to write SQ
• The test runs this matrix currently, using the lowest common denominator of ANSI types that are understood by both DBs https://github.com/ThoughtWorks-SEA/recce/blob/eaf61c7c59774d3451a1d6e9348e8014928c6786/src/test/kotlin/recce/server/dataset/DatasetRecServiceCrossDatabaseIntegrationTest.kt#L31-L50

Things observed and still to consider

• BOOLEAN or BIT types can still hash unequally across DBs due to driver and DB type differences (comes back as ByteBuffer on some drivers when using BIT type, and Boolean on others). This seems a hard problem in general. Unless we hash everything as raw bytes and add to the hash "minimal bytes to represent the number of type x" instead of the current approach, we will have these issues.
• It's generally nonsense to try and hash and compare REAL/DECIMAL etc, just as it is in regular code to compare for equality. While the code can hash them, maybe it shouldn't. Or should warn you, because the hashes are unlikely to match due to either precision inside the DB being slightly different, or precision being slightly different when represented as Java types in memory.
• Might be good to add ability to compare DB1(value A of type X) with DB2(value A of type Y)