Sep 29, 2011

MySQL Unit Testing and Re-factoring Verification

If your software is highly dependent on a database, data intensive processes re-factoring verification can be a pain in the a$$. Yet, you probably would like to avoid a production change that will result in a total mess in your data.

Verification and Automation is Easier w/MySQL
MySQL helps us verifying the change by using a MD5 hashing. Please notice that the following works only on the Linux platform.

MD5 Hash Based Verification Step By Step

  1. Backup the baseline table before running any process: CREATE TABLE class_baseline SELECT * FROM class;
  2. Run the original process.
  3. Copy the results of the old process to a new table : CREATE TABLE class_original SELECT * FROM class;
  4. If needed, TRUNCATE the table or restore the baseline table results TRUNCATE class; INSERT INTO class SELECT * FROM class_baseline;
  5. Run the modified process.
  6. Change the pager to MD5. The result is a single short MD5 hash instead of the regular screen output: pager md5sum -.
  7. Perform a select statement on the original table. Don't forget to drop from the SELECT statement any auto enumerator or time stamp fields that are being changed in any table modification/insert. If your process re-factoring changes the insert order, don't forget to perform an ORDER BY to avoid the case of same content/different order: SELECT class_name, class_location from class_original ORDER BY class_name, class_location;
  8. Perform the same on the modified process result: SELECT class_name, class_location FROM class ORDER BY class_name, class_location;
  9. Compare the two results. If your re-factoring did change the data itself, you should receive identical HASH results.
  10. Recover to regular screen output mode using the nopager command.
Bottom Line
Re-factoring is much simple with MD5 hashing

Keep Performing,


Intense Debate Comments

Ratings and Recommendations