SQLServerCentral / Discuss Content Posted by Andy Leonard / Article Discussions / Article Discussions by Author / An Example of Test-Driven Development / Latest Posts

RE: An Example of Test-Driven Development

Carla Wilson-484785 — Wed, 26 Aug 2009 09:12:45 GMT

Thanks, Andy,I've been following along with these articles over the past few months, but didn't have the time to sit down and DO your examples. (Your explanations are GREAT, btw!)We've been kicking around the idea of adding unit-testing for a few months now, but I have been so busy, I didn't want to slow down to write some. (No, we don't have the luxury of a Test Team right now.) Although I test the heck out of my code, I have really been running "functional" tests, not unit tests.Building the tests from the very beginning looks like the way to discipline oneself to write them. I am now motivated to use this technique on new projects, going forward.Thanks also for walking through building a solution from SSMS. I had never tried that and will try to use that in the immediate future.

RE: An Example of Test-Driven Development

Andy Leonard — Wed, 06 May 2009 10:45:17 GMT

Hi Max, Thanks for sharing your experiences and insight. We disagree, but I respect where you're coming from.:{> Andy

RE: An Example of Test-Driven Development

Max-146500 — Wed, 06 May 2009 10:36:32 GMT

I see you've had to try and justify this all before, and yes, I've heard it all before too.Suggesting that I'm just not with it isn't good enough Andy. My company has been struggling for over two years now to get VSTS and dbpro integrated as part of our development lifecycle and that includes all aspects of it right down to customised SSRS reporting based on the Team System database and cube and I'm strongly suggesting that it's an immature product that isn't ready for market yet. Why did we go down this route despite the incredible cost in money, time and sanity? well because of "legal / auditing requirements" paranoia. I know another company who had similar issues and eventually had the common sense to pull out before their evangelists started suggesting it was the team who were wrong and not them. But hey, if you want to be responsible for costing your company millions of squids in times like these and getting nowhere for greater overheads, be my guest.Here's my suggestion:- If you want to back up your databases (presuming you don't have a maintenance plan) and you prefer having your database comprised of a collection of text files, why not script them out and hey presto there's, thrown into the bargain, your auditing trail (see SQLCompare). - If you want a customisable quantifiable work register see Fogbugz.- If you want a refactoring tool, write one, I have.- If you really like checking things in see TortoiseSVN.I just don't see how defining duties between dba's and developers (BI included) based on the current product suite will herald a grand new future. Frankly, I think it's irresponsible of MS to release such an immature product.There goes my invite to the next developer forum :doze:

RE: An Example of Test-Driven Development

Lynn Pettis — Wed, 06 May 2009 10:16:30 GMT

[quote][b]mike brockington (5/6/2009)[/b][hr]@LynnMaybe I should have made it explicit that my comments were based on experience, not on conjecture.If the OP thinks it works well, I would be interested to hear what was different about his setup.@SRDEVSorry, I don't follow what you are getting at.[/quote]A previous employer implemented TDD for their projects. A co-worker from there said it didn't work but explained why. The developers wrote their tests to pass their code which isn't how it is supposed to work.Maybe it didn't work for you, but that doesn't invalidate the methodology, just the implementation you were working under.

RE: An Example of Test-Driven Development

Andy Leonard — Wed, 06 May 2009 09:41:56 GMT

Hi Max, There are different mindsets for DBAs and database developers. There's stuff they share, and stuff that one can learn from the other. It's folly to assume DBAs are going to think like database developers or database developers are going to think like DBAs. All this means, in this context, is: People in our field are going to react differently to the idea of test-driven database development. Regarding Team System: If you've been working with SQL Server for a while, you can see a pattern in the tools emerging. If you worked with DTS in SQL Server 2000, you did that in Enterprise Manager - the same place you did a lot of enterprise DBA tasks. With SQL Server 2005 came SSIS, and along with it a version of Visual Studio for developing SSIS. Can you do SSIS development inside SQL Server Management Studio? Yes - and you do, in fact, whenever you create a Maintenance Plan. So if it's possible inside there, why not leave it all there? My thinking is Microsoft is attempting to sever purely development activities from administrative activities. If I was a betting man, I'd bet the trend will continue. If that bugs you, you may be bugged. Database Edition is free with Developer Edition as of October 2008. A full Team System license isn't required unless you're wanting to do other Team System stuff (and then it's justified for those reasons). As for source control, you may not like it - many do not - but it's a reality for most database and application developers. Beyond the legal / auditing requirements, source control is just a good idea. I doubt seriously you'd question the assertion you should backup your database. One (merely one) benefit of source control is it backs up your business logic.:{> Andy

RE: An Example of Test-Driven Development

Max-146500 — Wed, 06 May 2009 08:57:57 GMT

[quote][b]Andy Leonard (5/6/2009)[/b][hr]Hang in here with me. All of this probably won't work for you, but I bet pieces of it will.[/quote]Would that include purchasing Team System licenses and going the dbpro development route forcing your database developers to develop in Visual Studio, deploying database changes using team system, no access to real data or databases? What's your next sales pitch: "If you don't use source or version control, I predict you will one day." {whether you want to or not} you should have added.It never fails to amuse me how many different angles this comes from.

RE: An Example of Test-Driven Development

Andy Leonard — Wed, 06 May 2009 08:16:37 GMT

Test-Driven Development doesn't work in every situation. In my experience, [i]nothing[/i] works in every situation. That's one of the reasons I titled the article "An Example..." in the first place.My goal in this article is to introduce the concepts. Granted, the example lacks practical application in most scenarios. But there are benefits to this approach that I explore in the next part of the article. Hang in here with me. All of this probably won't work for you, but I bet pieces of it will.:{> Andy

RE: An Example of Test-Driven Development

mike brockington — Wed, 06 May 2009 04:15:53 GMT

@LynnMaybe I should have made it explicit that my comments were based on experience, not on conjecture.If the OP thinks it works well, I would be interested to hear what was different about his setup.@SRDEVSorry, I don't follow what you are getting at.

RE: An Example of Test-Driven Development

SRDEV — Tue, 05 May 2009 10:05:11 GMT

I thought it was pretty good. Too bad you didn't see the point.I would be more concerned about your thoughts on the testing team. I would be pretty upset if the testing team was allowed to create tests as they go and keep changing the requirement. Is that what you want from a test team, to keep sending your programs back to development?I think this is really informal "testing" as a development process. Maybe there could be a better word for it. But, hopefully there are some requirements before you start. One key objective (at least in project management) is NOT to build more than is required. Someone should be responsible for specifying the requirements before development. Yes, it can specify some standard useability in general. But, work shouldn't come back for more and better ideas.

RE: An Example of Test-Driven Development

Lynn Pettis — Tue, 05 May 2009 09:59:07 GMT

I'd suggest doing more reading on Test Driven Development before passing judgement. If done correctly, TTD is a good development methodology. The problem I see with it is the abuse that is possible, which one problem is writing of tests such that the code will pass.The tests are supposed to be written first based on the users requirements. The code is then written and tested using those tests. Of cousre, you may miss some things this way also.

RE: An Example of Test-Driven Development

mike brockington — Tue, 05 May 2009 09:50:52 GMT

Great explanation of 'how' but could have done with a bit more info one exactly 'why' to do things like this.As far as I can see, writing the test first is a waste of time if it is being done by the same person as is developing the code: if you know what to test for, then you know how to write code that will pass, and will stop improving the code when it passes all of the tests.By testing after the code is written, you get a chance to write additional tests when you have seen odd behaviour.If you have the luxury of a seperate testing team, then there is no point bringing them in before any code has been written - that is what a design is supposed to be for!

RE: An Example of Test-Driven Development

steve smith-401573 — Thu, 30 Apr 2009 12:17:19 GMT

Wonderful article. I followed you completely up until almost the last sentence - where you used the ambiguous term 'opposite' in the Q&A section:'Scripting the database will work wherever restoring a backup will work, but the opposite doesn't always hold.'Which logical opposite? Are you saying that scripting will not work wherever a backup will not work, or that scripting will not work wherever a backup will work, or that a backup will not work where scripting does (not) work, or ....And, how would you build a (syntactic) test script for that sentence anyway?Great job!

RE: An Example of Test-Driven Development

JJ B — Thu, 30 Apr 2009 11:14:18 GMT

What a well-written article. I like how you explain things. Also, your writing style is easy to read and the touch of humor keeps me engaged. Alas, like others, I'm not convinced of the usefulness of this kind of approach. I'm not a lazy developer. I don't mind extra work for good value. For example, I document extensively even though it is no fun and does not directly affect the product. I just don't see the benefit of doing test driven development -- especially for database development.But, at least now I feel I really understand the approach and how to implement it. If one is sold on the concept of test driven development to begin with, your approach and set of scripts makes a lot of sense. I will definitely read any other articles you publish in the future on the subject. Who knows, maybe I'll be converted. Thanks.

RE: An Example of Test-Driven Development

Andy Lennon — Thu, 30 Apr 2009 11:00:27 GMT

This strikes me as a great approach to use for high-quality, (almost) self-documenting code.I think the overhead of time and effort, at least when one is first implementing this technique, seriously compromises it's usefulness. That is to say, i probably don't have the patience to use this approach when i can just write stuff off-the-cuff, and i know for a fact that my boss does not have the patience.Thanks for the article, Andy. I just don't think i'll ever be able to really use it. :crying:

RE: An Example of Test-Driven Development

SRDEV — Thu, 30 Apr 2009 08:46:45 GMT

I like the idea of Test Driven Development very much, but I'm not sure this is exactly how I would want it to go. Even in this simple example, the script that is created is actually an application to create the database. Instead I think I would be trying to keep it as lean as possible. Maybe do a check and print a result - and maybe fail. I can see where the script (application) created in the example could get to be bigger than the database project itself. If that permanently solved all the problems, that might work. But for continuous development, it means that new developers have to master not only the database, but the application. They would have to figure out how to modify the application to change the database.Still, there is something here in "TD3", and the presentation of SS2005 features employed is interesting.

RE: An Example of Test-Driven Development

Bill Nicolich — Thu, 30 Apr 2009 07:23:18 GMT

Andy: Great article: Simple and effective. If people want to delve deeper, that's what the discussion forum is for!Comparing notes:Why consider the "test first" technique? 1. I find it helps me focus on one feature at a time, which in turn helps me break down the complexity of a project into many simple parts. So it helps me manage complexity. 2. It keeps testing from becoming a neglected afterthought. I can manage the risks in quality issues up front.Why write reproducible tests? I feel more confident about making changes and adjustments if I can rerun the tests and get empirical evidence that things aren't broken. If I can efficiently make changes, I'm more agile.I develop mostly in SQL Server 2000 right now as I'm sure others do, which doesn't have the try/catch feature. However, there are tools in 2000 for raising errors: RAISERROR ('User-defined Error in usp_ThisStoredProcedure.', 16, 1). custom code or a framework can offer reporting of test success and failure: EXEC tsu_failure @FailureMessage.I've been using the TSQLUnit testing framework: http://tsqlunit.sourceforge.net/. TSQLUnit gives me the test suite logical grouping of tests with a setup and teardown for the group. Test code is written in stored procedures. The framework organizes the stored procedures through simple naming conventions. The framework gives some simple stored procedures to execute to run and manage the tests. Changes are rolled back at the conclusion of the tests.I find that I use the unit tests mainly to set up and use test data (insert, update and delete test records) and test the functionality of stored procedures (did the stored procedure delete the test recordset as expected?).I tend to not test all DDL (data definition language) statements such as whether or not certain objects exist except when necessary. For most of my DDL test needs, I've got the RedGate tool SQL Compare which compares every aspect of every object between my development and production. So, if the goal is to make sure nothing unexpected has changed, this tool does it for me in my environment where I don't require remote deployment.As a developer, I feel more agile when I use test-driven development techniques and I think I produce better quality code. It can be done even in SQL Server 2000.

RE: An Example of Test-Driven Development

Jack Corbett — Thu, 30 Apr 2009 06:25:12 GMT

Loved the article. I really need to get more into Test-Driven Development.

RE: An Example of Test-Driven Development

Rui Carvalho — Thu, 30 Apr 2009 03:03:59 GMT

I completely agree with the fact of writing sql scripts for deployment including tests and using sqlcmd is a very good practice, I use to do it in a similar way. But what is the relation with test driven development? TDD means that you model your application in order to pass the tests you define and refactor it iteratively, that's not the same than making unit tests on your scripts.

RE: An Example of Test-Driven Development

hodgy — Thu, 30 Apr 2009 02:25:32 GMT

I enjoyed this article; a good introduction to test-driven database development, something I have been looking to implement for quite some time.I would be interested to see this expanded with some more practical examples, how you test complex stored procs, or triggers. Also, how do you deal with changing data from one environment to another?Looking forward to more in this series.Tom

RE: An Example of Test-Driven Development

Atif Sheikh — Thu, 30 Apr 2009 02:17:01 GMT

Nice well demonstrated article. Still alot of confusions in my mind that WHY to through this long procedure of creating project, adding query files (if talk about the example in the article) . On can manually create a simple script file to accomplish the task. But, I would say that I might be too far away from the necessities of the TD3. Once again, Nice Article.

RE: An Example of Test-Driven Development

Calvin Lawson — Thu, 30 Apr 2009 01:37:44 GMT

I like this a lot, it's a good demonstration of what it means to do test driven development. Everyone uses their own techniques, I tend to favor test harnesses that write to log tables/files. But this great; a good many projects fail because nobody can thoroughly test them.

An Example of Test-Driven Development

Andy Leonard — Thu, 30 Apr 2009 00:25:28 GMT

Comments posted to this topic are about the item [B]An Example of Test-Driven Development[/B]