Sideout

I'm having a bit of a hard time grasping how to make meaningful tests based on the randomly generated data vs making my test in a transaction where I feed it initial data and check for an expected outcome.

For example's sake if I had an SP called DivideTwoCols that would divide colA by colB in table0...

If I were making tests with known data, I'd probably make a test using two positives, one negative and one positive, two negatives, colA = 0, colB = 0, and no rows in table0.

If I were just testing randomly generated data how would I know I had my cases covered would the random test just pick a random row to try it with using select top 1 colA, colB from table0 order by newid()

I can appreciate throwing in some randomness in testing to check for conditions you may have forgotten to check for, but it seems fairly hit or miss and not a reliable indicator that I'm checking all things properly.

Am I missing some core idea or plan for using random testing Are there any tutorials, best practices, sites available that go over ideas on random testing with the generator



Re: Visual Studio Team System - Database Professionals Best Practices for Data Generator Based Tests?

Sideout

The example in the documentation was so contrived that it did not help me understand the usage any better.

http://msdn2.microsoft.com/en-us/library/aa833283(VS.80).aspx

Based on their example maybe their data generation plan would randomly have Seattle added and the test would pass, or maybe it wouldn't with a different seed or # of rows and the test would fail.





Re: Visual Studio Team System - Database Professionals Best Practices for Data Generator Based Tests?

Henry Zhang - MSFT

I think both predefined test data and randomly generated data are useful for doing the tests. We do generate random test data, and we don't prevent you from using predefined data. Here is my proposed steps to have predefined data in your unittests, using your example:

(1) In another database, you can have a table called TestDataTable1 with colA and colB and insert the following data inside the table: (3,2), (-3,2), (-2,-3), (0,0).

(2) In Data Generation Plan UI, select Data Bound Generators for DivideTwoCols.colA and DivideTwoCols.colB, and set up the Data Bound Generator to pull data from TestDataTable1.colA to DivideTwoCols.colA, and from TestDataTable1.colB to DivideTwoCols.colB.

(3) Populate data, now you should have your predefined data in your DivideTwoCols table for your test need.

(4) You can set up a unittest to use the Data Generation Plan created above to test all the scenarios you listed except the no row scenario.

(5) For testing no row scenario in DivideTwoCols table, you can set up another Data Generation Plan that just populate 0 row in DivideTwoCols table and set up another unittest to use this Data Generation Plan to test no row scenario.

Please let me know if this help.

Thanks,






Re: Visual Studio Team System - Database Professionals Best Practices for Data Generator Based Tests?

Sideout

Thanks. I see that as a potential way to use the data generator to setup expected data and it's a good idea to consider.

I'm still also curious what sort of testing scenarios using actual random data from the generator would help me out with.





Re: Visual Studio Team System - Database Professionals Best Practices for Data Generator Based Tests?

Henry Zhang - MSFT

Randomly generated data may not help too much for your specific scenario. It is true that purely random data may provide less help. However the data we generate are not purely random (Please see the items I listed below). And I also think it is still valuable to provide a set of default data generated for the database if you don't do any specific configuration such as the steps I provided. Here are some of the advantages of our generated data:

(1) It generates repeatable random data. As long as the seeds on the column and other setting are not changed, it always generate the same data everytime which helps you to repro the problems when you run the unittests.

(2) It is not uncontrollable random. You can specify properties such as Max, Min, Percentage Null, Step, Unique and so on to control the kind of data to generate.

(3) It generates data that conform to the foreign key relationships in the database.

(4) It generates unique values when there is unique constraint on a column and even generate unique values when multiple foreign keys on a table have one unique constraint. If you write your own code to generate data like these, it will take much longer time.

(5) Using Regular Expression generator can generate meaningful string type data like address, phone number, email address, url and so on to make your test data more meaningful and readable.

(6) There are more ...






Re: Visual Studio Team System - Database Professionals Best Practices for Data Generator Based Tests?

Sideout

Those are good points. Considering those I think the random generator would prove excellent for generating test data for load tests and testing capacities and performance limits of your app. A test condition of execution time would be helpful in that case. It might also be interesting to see if a custom condition could be generated somehow for # reads as a red flag that perhaps after data grows to a certain level a poor execution plan may be running due to perhaps parameter sniffing or some other guesses the DB is making or simply lack of proper indexing catching up with you.



Re: Visual Studio Team System - Database Professionals Best Practices for Data Generator Based Tests?

Ashu283

Can you guide me in, how to use data generated by my Data Generator plan in my Unit Testing



Re: Visual Studio Team System - Database Professionals Best Practices for Data Generator Based Tests?

Henry Zhang - MSFT

Please see the information provided in the link

http://msdn2.microsoft.com/en-us/library/aa833283(VS.80).aspx

Let us know if you have specific questions.

Thanks,






Re: Visual Studio Team System - Database Professionals Best Practices for Data Generator Based Tests?

Ashu283

Ya, its solved. thanks.

I was thinking to use Data generation plan to do Data driven DB testing of SPs,Fns. But Jamie has provided the way in other thread to do Data Driven testing in DB.





Re: Visual Studio Team System - Database Professionals Best Practices for Data Generator Based Tests?

barkingdog

Henry,

Regarding

>>>

(5) Using Regular Expression generator can generate meaningful string type data like address, phone number, email address, url and so on to make your test data more meaningful and readable.

>>>

I can see that for a Title field, such as "Mr or Miss" with a few choices but how can addresses, phones numbers, etc be handled via regular expressions

Also, is it possible for the data generation routines to simply import a text file of addresses DP Pro generated data is readable but "un memorable" and dev's like to be able to recognize the same data then next time they see it.

TIA,

barkingdog





Re: Visual Studio Team System - Database Professionals Best Practices for Data Generator Based Tests?

Henry Zhang - MSFT

Thanks for asking! Here are some Regular Expression samples and please try them out to see if they help.

[1-6]{1}[0-9]{1,3} (SE|NE|NW|SW) [1-2]{1}[0-9]{1,2}th (ST|CT|PL|AVE), (Redmond, WA 9805[0-9]|Bellevue, WA 9800[1-9]|Sammamish, WA 9807[0-9]|Seattle, WA 9806[0-9]|Issaquah, WA 9808[0-9])

Seattle|(New York)|Boston|Miami|Beijing|(Los Angles)|London|Paris

(206|425)-[1-9][0-9]{2,2}-[0-9]{4,4}

[a-z]{5,8}@(hotmail\.com|msn\.com|[a-z]{3,8}\.(com|net|org))

http(s) ://(www\.) [a-z]{3,8}\.(com|org|net|info)(/[a-z]{3,6}){1,2}\.(asp|html|aspx|cgi)

If you have installed DBPro PowerTools, you should be able to use the Regular Expression Editor that provides a very handy way for you to write/test Regular Expression, pull out or save a list of existing Regular Expressions. The tools also helps to translate the String Type Check Constraint from the corresponding column into a Regular Expression for you to use directly. If you have PowerTools installed, you should be able to launch Regular Expression Editor from a button on the right side of Regular Expression Generator in Column Details window.

You can also use Data Bound Generator to pull data from other data sources such as a database table row, Excel, etc.

Please let me know if these help.

Thanks,