Schema Hashing

[jpoehls]

Hash will include:

• tables (catalog, schema, name)
• table columns (name, default values, all data type information and optionally their order)
• constraints (foreign key and primary key)
• views (catalog, schema, name and definition)

Notes:

• Add a [hash] column to the [schema_info] table to store a hash of the schema taken after the migration
• On rollback, confirm that the schema hash matches the last migrations [hash] → if it doesn’t, throw a warning (or, optionally) throw an error and rollback the transaction. A non-match would indicate that either the Tear Down of the migration didn’t correctly return the schema to the previous state, or indicates that changes to the schema were made manually (and not by migration scripts).
• On migrate up, confirm that the schema hash matches the last migrations [hash] before starting. A non-match would indicate the schema has changes since the last migration was applied. Throw a warning (or error).
• It should be a config option whether schema mismatches throw warnings or errors (errors would also mean the migration transaction would be rolled back).
• It should be a config option whether the order of columns in a table are included in the hash.
• Evaluate the performance of calculating and comparing the hashes. Determine whether the perf hit is substantial enough to warrant including a switch to skip the hashing.

Questions:

• What happens if you change the option whether to include column order in the hash after migrations have already been applied? This would mean the hashes already taken are all invalid.
• Option 1: Regenerate the hashes by running a command similar to the ‘test’ command that rolls back the database and migrates it up inside of a transaction. This way we could regenerate all the hashes and then return the database to the original state when done. (This would have the same performance impacts as noted in the ‘test’ command.)
• Option 2: Always store 2 hashes. One with column order and one without.
  How will we know when the column order hash option has been changed?

Resources:

• http://haacked.com/archive/2006/07/05/BulletproofSqlChangeScriptsUsingINFORMATION_SCHEMAViews.aspx