This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It’s a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we’ll be talking about database testing and refactoring. In 3 posts we’ll cover:
SQLU part 1 - What and why of database testing
SQLU part 2 - What and why of database refactoring
SQLU part 3 - Database testing and refactoring tools and examples
This is the third and last part of the series and in it we’ll take a look at tools we can test and refactor with plus some an example of the both.
Tools of the trade
First a few thoughts about how to go about testing a database. I'm firmily against any testing tools that go into the database itself or need an extra database. Unit tests for the database and applications using the database should all be in one place using the same technology. By using database specific frameworks we fragment our tests into many places and increase test system complexity. Let’s take a look at some testing tools.
1. NUnit, xUnit, MbUnit
All three are .Net testing frameworks meant to unit test .Net application. But we can test databases with them just fine. I use NUnit because I’ve always used it for work and personal projects. One day this might change. So the thing to remember is to be flexible if something better comes along. All three are quite similar and you should be able to switch between them without much problem.
2. TSQLUnit
As much as this framework is helpful for the non-C# savvy folks I don’t like it for the reason I stated above. It lives in the database and thus fragments the testing infrastructure. Also it appears that it’s not being actively developed anymore.
3. DbFit
I haven’t had the pleasure of trying this tool just yet but it’s on my to-do list. From what I’ve read and heard Gojko Adzic (@gojkoadzic on Twitter) has done a remarkable job with it.
4. Redgate SQL Refactor and Apex SQL Refactor
Neither of these refactoring tools are free, however if you have hardcore refactoring planned they are worth while looking into. I’ve only used the Red Gate’s Refactor and was quite impressed with it.
5. Reverting the database state
I’ve talked before about ways to revert a database to pre-test state after unit testing. This still holds and I haven’t changed my mind. Also make sure to read the comments as they are quite informative. I especially like the idea of setting up and tearing down the schema for each test group with NHibernate.
Testing and refactoring example
We’ll take a look at the simple schema and data test for a view and refactoring the SELECT * in that view. We’ll use a single table PhoneNumbers with ID and Phone columns. Then we’ll refactor the Phone column into 3 columns Prefix, Number and Suffix. Lastly we’ll remove the original Phone column. Then we’ll check how the view behaves with tests in NUnit. The comments in code explain the problem so be sure to read them. I’m assuming you know NUnit and C#.
| T-SQL Code | C# test code |
USE tempdb
GO
CREATE TABLE PhoneNumbers
(
ID INT IDENTITY(1,1),
Phone VARCHAR(20)
)
GO
INSERT INTO PhoneNumbers(Phone)
SELECT '111 222333 444' UNION ALL
SELECT '555 666777 888'
GO
-- notice we don't have WITH SCHEMABINDING
CREATE VIEW vPhoneNumbers
AS
SELECT *
FROM PhoneNumbers
GO
-- Let's take a look at what the view returns
<pre class="csharpcode"><span class="rem">-- If we add a new columns and rows both tests will fail</span><br/><span class="kwrd">SELECT</span> *<br/><span class="kwrd">FROM</span> vPhoneNumbers <br/><span class="kwrd">GO</span></pre>
<pre class="csharpcode"><span class="kwrd"><a href="/img/subtext/WindowsLiveWriter/SQLUniversityDatabasetestingandrefactori_11259/image_2.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; margin-left: 0px; border-left-width: 0px; margin-right: 0px" title="image" border="0" alt="image" src="/img/subtext/WindowsLiveWriter/SQLUniversityDatabasetestingandrefactori_11259/image_thumb.png" width="124" height="58"/></a></span></pre>
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<pre id="codeSnippet" class="csharpcode"><font color="#ff0000"><strong>-- </strong>DoesViewReturnCorrectColumns test will <strong>SUCCEED</strong></font></pre>
<pre class="csharpcode"><font color="#ff0000"><strong>-- </strong>DoesViewReturnCorrectData test will </font><font color="#ff0000"><strong>SUCCEED</strong></font></pre>
</div>
</div>
</div>
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<pre class="csharpcode"><br/><span class="rem">-- refactor to split Phone column into 3 parts</span><br/><span class="kwrd">ALTER</span> <span class="kwrd">TABLE</span> PhoneNumbers <span class="kwrd">ADD</span> <span class="kwrd"><font color="#000000">Prefix</font></span> <span class="kwrd">VARCHAR</span>(3)<br/><span class="kwrd">ALTER</span> <span class="kwrd">TABLE</span> PhoneNumbers <span class="kwrd">ADD</span> Number <span class="kwrd">VARCHAR</span>(6)<br/><span class="kwrd">ALTER</span> <span class="kwrd">TABLE</span> PhoneNumbers <span class="kwrd">ADD</span> Suffix <span class="kwrd">VARCHAR</span>(3)<br/><span class="kwrd">GO</span><br/><span class="rem">-- update the new columns</span><br/><span class="kwrd">UPDATE</span> PhoneNumbers <br/><span class="kwrd">SET</span> <span class="kwrd"><font color="#000000">Prefix</font></span> = <span class="kwrd">LEFT</span>(Phone, 3),<br/> Number = <span class="kwrd">SUBSTRING</span>(Phone, 5, 6),<br/> Suffix = <span class="kwrd">RIGHT</span>(Phone, 3)<br/><span class="kwrd">GO</span><br/><span class="rem">-- remove the old column</span><br/><span class="kwrd">ALTER</span> <span class="kwrd">TABLE</span> PhoneNumbers <span class="kwrd">DROP</span> <span class="kwrd">COLUMN</span> Phone<br/><span class="kwrd">GO</span><br/><span class="rem">-- This returns unexpected results!</span><br/><span class="rem">-- it returns 2 columns ID and Phone even though </span><br/><span class="rem">-- we don't have a Phone column anymore.</span><br/><span class="rem">-- Notice that the data is from the Prefix column</span><br/><span class="rem">-- This is a danger of SELECT *</span><br/><span class="kwrd">SELECT</span> *<br/><span class="kwrd">FROM</span> vPhoneNumbers</pre>
</div>
</div>
</div>
</div>
<pre class="csharpcode"><a href="/img/subtext/WindowsLiveWriter/SQLUniversityDatabasetestingandrefactori_11259/image_4.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="/img/subtext/WindowsLiveWriter/SQLUniversityDatabasetestingandrefactori_11259/image_thumb_1.png" width="75" height="58"/></a> </pre>
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<pre id="codeSnippet" class="csharpcode"><font color="#ff0000"><strong>-- </strong>DoesViewReturnCorrectColumns test will <strong>SUCCEED</strong></font></pre>
<pre class="csharpcode"><font color="#ff0000"><strong>-- </strong>DoesViewReturnCorrectData test will </font><font color="#ff0000"><strong>FAIL</strong></font></pre>
</div>
</div>
</div>
</div>
</div>
<pre class="csharpcode"><font color="#ff0000"><strong></strong></font></pre>
</div>
</div>
<pre class="csharpcode"><br/><span class="rem">-- for a fix we have to call sp_refreshview </span><br/><span class="rem">-- to refresh the view definition</span><br/><span class="kwrd">EXEC</span> sp_refreshview <span class="str">'vPhoneNumbers'</span><br/><span class="rem">-- after the refresh the view returns 4 columns</span><br/><span class="rem">-- this breaks the input/output behavior of the database</span><br/><span class="rem">-- which refactoring MUST NOT do</span><br/><span class="kwrd">SELECT</span> *<br/><span class="kwrd">FROM</span> vPhoneNumbers</pre>
<pre class="csharpcode"><a href="/img/subtext/WindowsLiveWriter/SQLUniversityDatabasetestingandrefactori_11259/image_6.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="/img/subtext/WindowsLiveWriter/SQLUniversityDatabasetestingandrefactori_11259/image_thumb_2.png" width="162" height="59"/></a> </pre>
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<pre id="codeSnippet" class="csharpcode"><font color="#ff0000"><strong>-- </strong>DoesViewReturnCorrectColumns test will <strong>FAIL</strong></font></pre>
<pre class="csharpcode"><font color="#ff0000"><strong>-- </strong>DoesViewReturnCorrectData test will </font><font color="#ff0000"><strong>FAIL</strong></font></pre>
</div>
</div>
</div>
</div>
</div>
<pre class="csharpcode"><font color="#ff0000"><strong></strong></font></pre>
</div>
</div>
</div>
<pre class="csharpcode"><font color="#ff0000"><strong></strong></font></pre>
</div>
</div>
</div>
<pre class="csharpcode"><br/><span class="rem">-- to fix the input/output behavior change problem </span><br/><span class="rem">-- we have to concat the 3 columns into one named Phone</span><br/><span class="kwrd">ALTER</span> <span class="kwrd">VIEW</span> vPhoneNumbers<br/><span class="kwrd">AS</span><br/><span class="kwrd">SELECT</span> ID, <span class="kwrd"><font color="#000000">Prefix</font></span> + <span class="str">' '</span> + Number + <span class="str">' '</span> + Suffix <span class="kwrd">AS</span> Phone<br/><span class="kwrd">FROM</span> PhoneNumbers<br/><span class="kwrd">GO</span><br/><span class="rem">-- now it works as expected</span><br/><span class="kwrd">SELECT</span> *<br/><span class="kwrd">FROM</span> vPhoneNumbers</pre>
<pre class="csharpcode"><span class="kwrd"><a href="/img/subtext/WindowsLiveWriter/SQLUniversityDatabasetestingandrefactori_11259/image_2.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; margin-left: 0px; border-left-width: 0px; margin-right: 0px" title="image" border="0" alt="image" src="/img/subtext/WindowsLiveWriter/SQLUniversityDatabasetestingandrefactori_11259/image_thumb.png" width="124" height="58"/></a></span></pre>
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<div id="codeSnippetWrapper">
<pre id="codeSnippet" class="csharpcode"><font color="#ff0000"><strong>-- </strong>DoesViewReturnCorrectColumns test will <strong>SUCCEED</strong></font></pre>
<pre class="csharpcode"><font color="#ff0000"><strong>-- </strong>DoesViewReturnCorrectData test will </font><font color="#ff0000"><strong>SUCCEED</strong></font></pre>
</div>
</div>
</div>
</div>
</div>
<pre class="csharpcode"><font color="#ff0000"><strong></strong></font></pre>
</div>
</div>
</div>
</div>
</div>
<pre class="csharpcode"><br/><span class="rem">-- clean up</span><br/><span class="kwrd">DROP</span> <span class="kwrd">VIEW</span> vPhoneNumbers<br/><span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> PhoneNumbers<br/></pre>
</div>
</div>
</div>
</div>
</td>
<td valign="top" width="601">
<div id="codeSnippetWrapper">
<pre class="csharpcode">[Test]<br/><span class="kwrd">public</span> <span class="kwrd">void</span> DoesViewReturnCorrectColumns()<br/>{ <br/> <span class="rem">// conn is a valid SqlConnection to the server's tempdb</span><br/> <span class="rem">// note the SET FMTONLY ON with which we return only schema and no data</span><br/> <span class="kwrd">using</span> (SqlCommand cmd = <span class="kwrd">new</span> SqlCommand(<span class="str">"SET FMTONLY ON; </span></pre>
<pre class="csharpcode"><span class="str"> SELECT * FROM vPhoneNumbers"</span>, conn))<br/> { <br/> DataTable dt = <span class="kwrd">new</span> DataTable();<br/> dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));<br/> <span class="rem">// test returned schema: number of columns, column names and data types</span><br/> Assert.AreEqual(dt.Columns.Count, 2);</pre>
<pre class="csharpcode"><br/><br/> Assert.AreEqual(dt.Columns[0].Caption, <span class="str">"ID"</span>);<br/> Assert.AreEqual(dt.Columns[0].DataType, <span class="kwrd">typeof</span>(<span class="kwrd">int</span>));</pre>
<pre class="csharpcode"><br/><br/> Assert.AreEqual(dt.Columns[1].Caption, <span class="str">"Phone"</span>); <br/> Assert.AreEqual(dt.Columns[1].DataType, <span class="kwrd">typeof</span>(<span class="kwrd">string</span>));<br/> }<br/>}</pre>
<pre class="csharpcode"><br/>[Test]<br/><span class="kwrd">public</span> <span class="kwrd">void</span> DoesViewReturnCorrectData()<br/>{<br/> <span class="rem">// conn is a valid SqlConnection to the server's tempdb</span><br/> <span class="kwrd">using</span> (SqlCommand cmd = <span class="kwrd">new</span> SqlCommand(<span class="str">"SELECT * FROM vPhoneNumbers"</span>, conn))<br/> {<br/> DataTable dt = <span class="kwrd">new</span> DataTable();<br/> dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));<br/> <span class="rem">// test returned data: number of rows and their values</span><br/> Assert.AreEqual(dt.Rows.Count, 2);<br/> <br/> Assert.AreEqual(dt.Rows[0][<span class="str">"ID"</span>], 1);<br/> Assert.AreEqual(dt.Rows[0][<span class="str">"Phone"</span>], <span class="str">"111 222333 444"</span>);</pre>
<pre class="csharpcode"><br/><br/> Assert.AreEqual(dt.Rows[1][<span class="str">"ID"</span>], 2);<br/> Assert.AreEqual(dt.Rows[1][<span class="str">"Phone"</span>], <span class="str">"555 666777 888"</span>);<br/> }<br/>}<br/></pre>
<br/></div>
</td>
</tr>
|
With this simple example we’ve seen how a very simple schema can cause a lot of problems in the whole application/database system if it doesn’t have tests. Imagine what would happen if some outside process would depend on that view. It would get wrong data and propagate it silently throughout the system. And that is not good. So have tests at least for the crucial parts of your systems.
And with that we conclude the Database Testing and Refactoring week at SQL University. Hope you learned something new and enjoy the learning weeks to come. Have fun!
Legacy Comments
|
Michael Baylon
2010-06-04 |
re: SQL University: Database testing and refactoring tools and examples
Mladen, a set of really interesting blogs. The post on why we need to test databases especially resonated.
I agree, there is little emphasis around testing in general and even less for databases. Even where dev teams have fully embraced test driven development - database development is very rarely included.
I have created a database testing framework that might be of interest. It provides an API, templates and helper scripts that tries to remove some of the complexity around database testing. MBunit is used by default - but NUnit could be used.
More details can be found at http://michaelbaylon.wordpress.com/database-testing-framework/ |
|
Jorge Segarra
2010-06-05 |
re: SQL University: Database testing and refactoring tools and examples
Great job Mladen! Everyone, if you read this series of blogs and found it useful please leave Mladen and SQL University feedback at: http://bit.ly/SQLUfb |
|
kamal
2010-08-30 |
re: SQL University: Database testing and refactoring tools and examples
I am fresher to software world and i am testing team my team lead asked me about data base testing?
My questions about this are what is the need database testing & how we test database?
Can u provide any material or test book or sites to learn more about this topic |