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,

Moshe Kaplan