I had been unit testing an SQL generator and had a bunch of tests that gave various input to the generator and tested its output against expected SQL. The SQL in my tests looked like this:
Now I was ready to feed the SQL to a database engine to verify that it was validly formed. I would generally use grep for this type of task; but here my SQL statements were formatted multiline for easier reading, and grep operates in a line-by-line mode. There were over 100 test cases, so it was worth figuring out an automated solution. I also wanted to avoid writing a single-purpose text-processing utility if possible.
I ended up writing down the following steps for myself:
- Turn the tests into a single line of text for ease of working with tools
On command line:cat Test*.cpp | tr -d "\n\r" > all-one-line.out
- Discard everything but the queries, inserting a newline after each
In editor (SlickEdit for me), open all-one-line.out and Replace All (using Perl-style regexes):
.*?("(?:\( )*SELECT[^;]+?;)
with
\1\n
- Clean up what the regex didn’t
Delete the last line - Get rid of quotes
Replace\"
with nothing - Get rid of semicolons
Replace;$
with nothing - Get rid of extra spaces
Replace <space>+ with <space> - Save the file in the editor
- Get rid of Oracle-specific tests
grep --invert-match TO_DATE < all-one-line.out > all-one-line.sql
- Let cool for 5 minutes before serving
Pasteall-one-line.sql
into MS SQL Server Management Studio and execute (with Results to Text)
This may look like a large number of steps, but I got to where I could run through them in about 30 seconds and test all 130 queries on the server. Nice!
Future improvements
Once I had the ability to test my test output against the database server, I wanted to do that each time the tests’ expected results changed. So where I had originally envisioned a single smoke test run, I ended up going through these automation-assisted manual steps ten or twenty times. In retrospect, the single-purpose utility script would clearly have been the better approach after all. I need to get more comfortable whipping up such scripts to lower the barrier to writing them when these occasions arise.
Twelve years in, I would think I would be at the top of my craft by now, but there are still things that seem pretty basic that I’m learning. Hmmm…I wonder if life really does begin at 40?