Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Automating tests for T-SQL code Expand / Collapse
Author
Message
Posted Sunday, June 21, 2009 3:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 22, 2010 6:53 PM
Points: 7, Visits: 49
Comments posted to this topic are about the item Automating tests for T-SQL code
Post #739004
Posted Tuesday, June 30, 2009 12:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 3, 2014 2:45 AM
Points: 2,531, Visits: 536
I have not tried this tool yet, but if it works as mentioned, then this is what I am looking for. :)
I will try this as soon as possible and get back to this forum, to share the experience.

/Håkan Winther


/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
Post #744194
Posted Tuesday, June 30, 2009 5:52 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:44 PM
Points: 389, Visits: 1,041
I like this idea a lot, Ladislau! I will dig in as soon as I can.

I'm writing a series on database development. One of the things I'm covering is the evolution of database testing. This looks like highly evolved database testing indeed.

Great work, and great thinking!

:{> Andy


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #744327
Posted Tuesday, June 30, 2009 6:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
looks very cool ... definitely considering trying this out
Post #744355
Posted Tuesday, June 30, 2009 8:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
We are doing the same thing with Visual Studio Team Server. The Test Driven Development thing is being integrated into our methodology.

Your tool looks to be a more affordable alternative. Great job.


ATB

Charles Kincaid

Post #744472
Posted Tuesday, June 30, 2009 8:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
Very interesting. Yet this raises a lot of questions:

1. IMO a very big problem with database unit testing is its sheer slowness:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/23/controlling-the-number-and-size-of-unit-tests.aspx

How does an SP call
EXEC dbo.TestEquals 1.5, @MyVar
perform compared to an inline expression such as
SELECT CASE WHEN 1.5 = @MyVar THEN ... ELSE ... END

2. I see that 11 tests ran for 1 second. How much of that was setup/teardown? What time would it typically take to run 1000 tests?

3. When you cannot rollback, have you considered restoring from a snapshot:

http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes---testing-database-modifications/



4. To separate production code from test, have you considered moving test code to a separate schema and or separate database?
Post #744492
Posted Tuesday, June 30, 2009 10:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 13, 2010 10:37 AM
Points: 5, Visits: 50
Hello,

It sounds very goog ! but is it works with sql server 2000 ?

Thanks for your answer.

Post #744634
Posted Tuesday, June 30, 2009 7:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 13, 2009 8:05 PM
Points: 3, Visits: 6
I am glad to share one database unit testing tool. It is named as AnyDbTest (www.anydbtest.com). After expanding its functionality in previous releases, AnyDbTest 2.2 brings a wide-ranging set of improvements.

Writing unit and component tests for objects with external dependencies, such as databases or other objects, can prove arduous, as those dependencies may hinder isolation. Ultimately, effective white-box tests isolate an object by controlling outside dependencies, so as to manipulate its state or associated behavior.

If you are an app developer, you can utilize mock objects or stubs to for controlling outside database dependency. But if you are a DB developer, these mock and stub frameworks cannot give you any helps.

AnyDbTest (Official website, http://www.anydbtest.com) provides an elegant solution for controlling a database dependency within applications by allowing developers to manage the state of a database throughout a test. With AnyDbTest, a database can be seeded with a desired data set before a test; moreover, at the completion of the test, the database can be placed back into its pre-test state.

Automated tests are a critical facet of most successful software projects. AnyDbTest allows developers to create test cases that control the state of a database during their life cycles; consequently, those test cases are easily automatic, as they do not require manual intervention between tests; nor do they entail manual interpretation of results.

The best way to see how AnyDbTest works is to read Quick Start.
Post #745017
Posted Wednesday, July 1, 2009 3:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 22, 2010 6:53 PM
Points: 7, Visits: 49
No, it will not work with 2000. It uses features that are new in 2005 like TRY/CATCH.
Post #745198
Posted Wednesday, July 1, 2009 4:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 22, 2010 6:53 PM
Points: 7, Visits: 49
Alexander, here are the answers to the 4 points that you raise:

1. Regarding database slowness.
You touch a good point; yes database slowness is an issue that has to be considered. I would go even further and say that SQL has a lot more limitations when it comes to unit testing. The entire unit test game must be viewed differently when applied to SQL compared with languages like Java / C#. There are a few core principles that remain intact but there are big differences.

Regarding the SP calls to something like Assert.Equals I am going to give more details at point 2.

2. Performance numbers.
I would like to know more about what were those 11 tests you are mentioning.
In my tests 1000 simple test procedures took just a few seconds.
On my machine, after a server restart, the first three runs of 1000 tests (see below) took 5233, 3740 and 3830 miliseconds.
My system is a dual core (AMD 64 5200+ 2.7 GHz) with 2GB RAM memory.

The test I did was meant to give an idea about the overhead that the TST API has. Here is the test:
a. I created a database with a simple function (TFN_DoubleInt) that simply multiplied the input parameter with 2 and returned the resulted value.
b. I added 1000 different test stored procedures. Each looked something like this:


CREATE PROCEDURE SQLTest_PN
AS
BEGIN

DECLARE @ActualValue int
DECLARE @ExpectedValue int

SET @ActualValue=dbo.TFN_DoubleInt(N)
SET @ExpectedValue=2 * N
EXEC TST.Assert.Equals 'SQLTest_PN', @ExpectedValue, @ActualValue

END
GO

where N is a number between 1 and 1000.


3. Regarding rollback & restoring from a snapshot

At this point I think that the cases where the auto rollback will not be able to perform will be fairly rare in practice. Also, when that happens it is a consistent thing not an error like event. TST does a good job of detecting those cases and there is no risk that it will simply let them slide. In that case the user can with a little work do the clean-up himself in a TEARDOWN. But it all depends on the feedback I will get. I am open to that if it becomes a concern and is requested by enough people.

4. Separating production code from test. Separate schema and or separate database.
Yes, definitely you can do any of those. You can isolate all the test procedures in a separate database if you wish. Also you can place them in the production DB but isolate them in their own schema.
Post #745217
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse