Mocks for SQL
One of the best books I have read in the last decade is Agile Database Warehousing. It has a lot of great content beyond Agile techniques. One of my favorites was how to test ETL (Enterprise Transform and Load) and Queries.
What is common about ETL, TSQL Queries and MDX Queries is that they all have input and output. This was one of the places I found people using MOCK data. You can create sample input and expected results for just about anything. If you generally use a data table as the results, it is possible quite often to automate testing by comparing data tables as final results.
Source data will vary depending on the process being tested. ETL may have file data, a spreadsheet, or other form of input. An MDX query may have a relational database, data marts, or more likely a data warehouse schema in the flow.
Marc has found the use of Mocks to be pivotal in their application development. He writes:
For a new project, in which we started from scratch, I was able to implement pseudo-mocks for tables, functions and stored procedures using MS Visual Studio Database Edition unit tests. It wasn’t really VS, but a set of stored procedures I made in a schema called “UnitTest”. For example, instead of having a unit test database with all the data setup in advance, I had one that was empty. So each unit test had to setup its own data in tables.
Trouble was, with a zillion foreign keys, it would require a lot of setup for each unit test. So I had a stored procedure that would dynamically find and remove all table constraints, except those that were included in an “exception list” which would be needed for the test. It would also add default constraints to all columns based on their data type. That let me insert only the data that would be used in the test. It took time to write up the utility stored procedures, but it allowed me to run tests without having to setup a multitude of extraneous data. Stored procedures and functions could also be “mocked” simply by replacing the syntax with “expected” output. Only the object being tested was left untouched.
All of this was done in a transaction that would be rolled back before the next unit test ran. We would not deploy the “UnitTest” schema to our customers.
So it is possible to mock SQL objects. It takes a little bit of forethought and time to setup the “utility” objects, but it worked very nicely. Of course, because it’s all on disk and rolled back, those unit tests take much longer to run than the C# developer’s unit tests.
Are you using Mocks in your SQL application development or implementations? Drop us a note and share your experience by writing to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Troubleshooting problems with joins in SQL Server 2008 R2
This article describes the problems you can have with SQL Server 2008 joins.Nob Hill Software Database Compare: A Data Developer’s Dream Tool For Comparison And Migration Of Databases
There are significant challenges presented by many packages on the market today when it comes to managing, comparing and working with database information and schemas. Many of the challenges and issues aren’t apparent until you are deep in a migration or testing project and realize that the information you have isn’t the information you really need. It doesn’t have to be this way. Software maker Nob Hill Software has built an application with a completely different approach to managing change between systems, data and schema comparisons — even tools for working with your dependencies, stored procedures and more.
Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ
SQL supports a concept called all-at-onc… (read more)