SQL Comparator: A Open Source Tool for Minimal Change Database Validation
Michael Riben MD; UT MD Anderson Cancer Center; Mark J. Routbort MD; UT MD Anderson Cancer Center;
Content:
Best practices and laboratory credentialing organizations require formal validation of software changes in systems which manage clinical laboratory or pathology information. The scope of such validation can be quite broad, ranging from full-scale bench-to-EMR validation of a new laboratory information system, to validation of small changes in existing database tables or procedures. Unfortunately, even small changes to complex procedures have the potential to break existing functionality. In cases where small changes to existing queries or application logic is required for functioning systems, we posit that the most effective mode of validation is to directly compare production system data modeled with the old versus new logic.
Technology:
SQL Comparator is a VB.NET Windows application designed to facilitate rapid minimal change database validation, which is capable of connecting to any Open Database Connectivity (ODBC) compliant data source such as Microsoft SQL Server or Oracle.
Design:
The SQL Comparator application runs two user defined queries and compares the results in a graphical mode designed to facilitate the identification of minimal differences in complex tables. As long as the queries or stored procedures return data in equally dimensioned tables, the tool will calculate and highlight any row, column, and cell-based differences between the two datasets. The tabular data is displayed in two datagrids which are positionally synchronized and where differing cells are visually highlighted. Print functionality is provided to support permanent documentation.
Results:
We used the SQL Comparator tool to validate a series of changes to a complex billing stored procedure which consisted of over 1100 lines of Transact-SQL code and which created a highly denormalized output table containing over 130 columns. These changes included modifying the stored procedure to handle duplicate charges correctly and to include Unique Physician Identification Numbers (UPINs) to our existing charge system. Using live production system data, we were able to rapidly and effectively determine that the proposed changes affected only the appropriate data rows and columns. Subsequent deployment of these changes occurred seamlessly.
Conclusion:
Minimal change database validation is one of a complementary set of software testing approaches which may be of particular value in live production systems requiring ongoing modifications. The compiled version as well as source code of the SQL Comparator application is provided under the GNU General Public License in the hope that this tool will prove useful to others.
