Unit Testing and Code Generation

Question

Post reply

Unit Testing and Code Generation

Grant Fritchey

SSC Guru

Points: 398882
More actions
June 9, 2006 at 12:32 pm

#171087

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/gFritchey/unittestingandcodegeneration.asp
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

cutespn SSCommitted Points: 1669 More actions · Answer 1

cutespn

SSCommitted

Points: 1669

June 21, 2006 at 12:10 am

#644952

Good work Grant..

Regards,
Sudheer

Scott Ambler SSC Rookie Points: 45 More actions · Answer 2

I'm glad to see people starting to write about modern development techniques for relational databases. I suspect that over then next two or three years we're going to see major changes with respect to how databases are developed and evolved. We're clearly moving away from the serial approach favored by many data professionals to the evolutionary approach favored by application developers.

If you're interested in reading more about database regression testing, I suspect that you'll find http://www.agiledata.org/essays/databaseTesting.html to be an interesting read.

- Scott

ESP Grasshopper Points: 14 More actions · Answer 3

This is an exciting development with the Visual Studio IDE. This is a big step forward in how to manage database additions and changes.

Jer-285830 SSC Enthusiast Points: 169 More actions · Answer 4

Jer-285830

SSC Enthusiast

Points: 169

June 23, 2006 at 12:28 pm

#645648

Great article!

Undebtedly SSCrazy Points: 2938 More actions · Answer 5

I have a lot of interest in this topic. I'm glad you're taking TSQLUnit beyond the discussion I had in my article a couple of years ago (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro04/html/sp04i1.asp).

I followed you up to the point where you query sysobjects for a list of stored procedures and their parameters. After that things get murky for me. Seems that your statements that the individual programmer needs to determine expected results runs contrary to your efforts to generate templates.

You also have a note about running a comparison set between two data sets. I'm not quite understanding here. (It might be just me that's a bit slower than normal. I've traveled about 15,000 miles in the last couple of weeks.)

Steve Miller

Undebtedly SSCrazy Points: 2938 More actions · Answer 6

Scott, I'm glad to see you here. Your previous book has helped me a lot. Hopefully I can get your new book soon.

Steve Miller

Scott Ambler SSC Rookie Points: 45 More actions · Answer 7

Thanks. The new book is called Refactoring Databases: Evolutionary Database Design and a description of it can be found at http://www.ambysoft.com/books/refactoringDatabases.html . Although I'm obviously biased, I believe that it is one of those few seminal books which comes along that every data professional will want to read. This is particularly true for the Microsoft community as the beta release of Visual Studio for Database Professionals ( http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/&nbsp shows rudimentary support for DB refactoring and testing. Microsoft has a gotten a great head start on modern database development techniques. I believe that the adoption of agile/evolutionary database development techniques will prove to be a fundamental shift in the data community, and database regression testing and database refactoring are primary techniques in your new intellectual toolkit.

The frustrating thing is that this new direction is coming from the agile community and not where it really should be coming from, the data management community. Agilists are starting to raise the bar on data professionals, they had better start paying attention.

- Scott

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

Hello,

Sorry I didn't get back to you sooner, but I didn't get any alerts that there were posts on this article. I thought no one was reading it.

Several of us here have read your article and we used it as a template to get started. Thanks for your work.

Sorry I was unclear in the article. Yes, we're generating the templates, but not completely eliminating the need for a developer to build out an expected result set. It's just not something we've been able to come up with any method of automating. The main thing the generation does for us is eliminate a ton of typing since we build out the variable declaration and query execution, etc..

We built a query that compares two sets of data by simply doing a count of the table and a count of the union. If they're different, it fails the test. Simplistic and limited it may be, but it gets the job done. We had tried exporting data to XML & doing all kinds of other comparisons, but this simple approach seemed to work much better than the fancier ones.

So, the developers have to generate an expected test data set, run the proc to get the actual data set and run these into the assert proc to determine if they're the same.

Hopefully that sheds a bit more light on the matter. Sorry again for the lack of clarity. Thanks again for your article, it was a big help to get us started.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

Woo Hoo! Scott Ambler!

Thanks for the response. I've read both your database books (Agile Database Techniques & Refactoring Databases) and have forced a few people in the organization to read the first one (I'm not finished with the second). It's great work and has really helped us align our database development with the Agile methods that the application developers have been working on.

I've read most of your web site (some of several times) and that's been a big help too. I'd also recommend people to subscribe to the Agile database email list.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

I have to agree. The database development team that I belong to has been trying to work out these kinds of methods, but the nature of databases and the dirth of tools has made it rather difficult. We're just starting to look at implementing the Visual Studio instead of TSQLUnit as an aid to those methods.

The one issue that we've run into repeatedly is the amount of time it takes us to make database refactors as opposed to the amount of time a code refactoring takes. We need to get the developers to allow us more time or we have to find methods of speeding that process up.

BTW, the Visual Studio Team Edition for Database Developers actually does most of what our CodeSmith templates did and it's integrated with the development environment, so... so much for all that work.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Scott Ambler SSC Rookie Points: 45 More actions · Answer 11

There really is a lack of tools, but the vendors are starting to move forward finally. I think with MS taking the lead we're going to see the other DB tool vendors wake up and realize that their toolsets don't really reflect modern development practices. There has been virtually no thought leadership coming out of the data management community for years now, many of them still haven't come up to speed on evolutionary if not agile development, and they're struggling to deal with the new ways which development teams are working. Worse yet, some of them are still pushing the outdated, and largely disproved, serial and/or command-and-control approach to development. It's pretty sad.

Anyway, if you want to help things change in the data community, please start pointing people to http://www.agiledata.org. I think it will give them something to think about.

- Scott

Undebtedly SSCrazy Points: 2938 More actions · Answer 12

> We built a query that compares two sets of data by simply doing a count of the table and a count of the union. If they're different, it fails the test. Simplistic and limited it may be, but it gets the job done. We had tried exporting data to XML & doing all kinds of other comparisons, but this simple approach seemed to work much better than the fancier ones.

I did that at first, but found that unit tests failed more frequently than taking a known bit of data that hasn't changed in a long while, and using that instead.

Steve Miller

Undebtedly SSCrazy Points: 2938 More actions · Answer 13

> Anyway, if you want to help things change in the data community, please start pointing people to http://www.agiledata.org. I think it will give them something to think about.

I've been pointing them there for a while now. 🙂 Thanks for creating the web site.

Steve Miller

Undebtedly SSCrazy Points: 2938 More actions · Answer 14

> BTW, the Visual Studio Team Edition for Database Developers actually does most of what our CodeSmith templates did and it's integrated with the development environment, so... so much for all that work.

I'll admit my ignorance publically now. (My excuse is that I've been overseas for the past year, and haven't been keeping up to date. That's my story and I'm sticking to it. 😉 What's this VS Edition for Database Developers?

Steve Miller