I had created some test data on a labmanager VM. It had taken some time — I had recorded a few calls, an email, and a chat, then tweaked the recording metadata to try out a few important aspects of the stored procedure I was testing.
It’s at these times that I get the nagging feeling — the test was so time-intensive to create and run, why not take the step of automating it so we can run it in the future and catch regressions ‘most instantly?
I wanted an SQL script that I could run that would INSERT the test data as it currently existed on my test machine. And I didn’t want to create those INSERT statements by hand. Boy, did I not want to write them all by hand.
Solution
Microsoft SQL Server 2005 comes with something called Database Publishing Wizard (look under Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2\ ). With the following command:
sqlpubwiz script -d MyDatabase -dataonly c:\temp\MyDatabase.sql
…I was able to generate INSERT statements for all the data in my little test database to an .sql script file. I then edited the file to remove things I didn’t need (and to avoid key collisions by substituting NEWID() calls for hardcoded uniqueidentifiers as necessary).
Finally, with all the data in place, I could add the following tests. (If anything goes wrong, an error message appears in the Messages window.)
DECLARE @tempTable TABLE(BlahId uniqueidentifier,
BlahType int,
BlahDateUTC DateTime,
BlahLength int,
PhoneNumber nvarchar(60),
RightsMask int);
DECLARE @return_value int;
INSERT INTO @tempTable
EXEC @return_value = [dbo].[blah_get_blah_chunk]
@i_sessionId = @sessionId,
@i_chunkSize = 50;
IF @return_value <> 0 RAISERROR('Return value should be zero but was %d', 15, 1, @return_value);
DECLARE @expectedRecords int;
DECLARE @actualRecords int;
SET @expectedRecords = 5;
SET @actualRecords = (SELECT COUNT(*) FROM @tempTable);
IF @actualRecords <> @expectedRecords RAISERROR('Expected %d records in result set but found %d', 15, 1, @expectedRecords, @actualRecords);
DECLARE @actualDisplayName nvarchar(60);
DECLARE @expectedDisplayName nvarchar(60);
SET @expectedDisplayName = N'blah_user -';
SELECT @actualDisplayName = (SELECT LocalPartyName FROM @tempTable WHERE RecordingId = N'a0ca1a4f-0933-4565-b5da-d8fc3222a42a');
IF @actualDisplayName <> @expectedDisplayName RAISERROR('Expected display name to be "%s" but it was "%s"', 15, 1, @expectedDisplayName, @actualDisplayName);
GO
In the future we could have a script test runner that keeps track of the output and flags errors as part of the build… but one step at a time, right? This already takes test scenarios that took more than a day to set up and makes them runnable in a minute or two.
Database Publishing Wizard can also generate script to create the entire database schema, but it’s the data generation that will support my unit test needs.
Thanks, Microsoft!
Acknowledgement
Thanks to Pinalkumar Dave for his helpful write-up titled Generate Script with Data from Database – Database Publishing Wizard
