| Author |
Topic |
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
 Posted - 2011-05-31 : 06:53:57
|
| Hello everyone. Not been posting in a while because I've been busy with new work.I'd like to ask the community about what kind of test frameworks you all use for database development.... I'm in the middle of developing a star schema and associated aggregation sp's for pretty large (200 million) and extremely volatile (rolling 10 days) dataset of airline quotes. This would let us replace a bunch of horrible legacy code and a ton of tables that all model the same data at different levels of aggregation.My company is moving to a Test driven development approach which I really like. TTD makes complete sense to me for c# or python work. I'd love to have something similar for the database without having to have an abstraction layer on top (a test framework using on TSQL). This would have to be done to support continuous deployment which we also want to move to. At the moment the database development work slows everything down into static release cycles.My first thoughts were to have a tests schema and to put stored procs analogous to c# unit tests inside there that start a transaction and a try catch block then run whatever object you want to test. Then make some assertions and do *something*. Return a error value. Whatever that could be monitored by a test runner.I've had a look round the interweb' and have found a bunch of frameworks. They seem to be expansions of that idea.The main problems I can see are:1) Mocking - how do you mock out tables without breaking things.2) Transactional control -- is there a problem when a proc does a rollback?3) INSERT EXEC problems. If a sp performs an INSERT EXEC then a) it's probably doing something wrong b) It's almost impossible to test.......More I haven't thought of.What do you guys do?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-06-01 : 08:47:02
|
| TTD sounds interesting - I don't have any answers - but do have questions?1. Can't the same thing be accomplished with test robots/automated test scripts?2. If an application was developed without TTD in mind, is it possible to add it later? Difficult to do that?3. If the requirements change, won't the TTD code have to be rewritten anyways? |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-06-01 : 09:21:03
|
| Did you read this article? ->http://msdn.microsoft.com/en-us/magazine/cc164243.aspxEven though I have no experience whatsoever regarding your questions I've always wanted to know how test driven development works and how it applies/can apply to database development. If you are able to find answers to your questions I'd really appreciate you posting them here so we all could learn a thing or two :)- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-06-01 : 19:56:59
|
Hi Lumbago, denisfirst typo -- TTD should be TDD (test driven development).Lumbago - nice article, reading nowIn short -- before you write *any* production code you write a (failing) test that describes the desired behaviour. Then write the simplest code that will satisfy the test. Rince and repeat and soon you have a test set that should provide regression when you start to change something else.For C# or python for example:In general to unit test a specific object you would 'Mock' out other object that the test target depends on. For instance you could mock out a database layer so that the object you are testing gets a static dataset back from what it thinks is a database call. Inversion of control is really useful there using some sort of IOC container like castle.When I'm working with another developer in python or c# on data layers we'll use test driven development and pair programming quite heavily. Generally we "ping pong" it -- one of us writes the unit test and then gives the keyboard to the other developer to write the functionality. Then reverse rolls. Pair programming is really tough - you have to be constantly talking about what you are doing when you are coding and thinking about the other developers code when they are coding. it's tiring but it results in really nice code. Our company has been doing this for a while and it was hell to start with but we are starting to see the benefits in our front end code.The end goal is to move to a continual deployment setup where we can push code with confidence (the tests)Dennis:quote:
1. Can't the same thing be accomplished with test robots/automated test scripts?
possibly -- don't think there is much of conceptual difference between a test script and a unit test (a unit test could be considered a script). One of the end goals is to automate the testing to support continual deployment.quote:
If an application was developed without TTD in mind, is it possible to add it later? Difficult to do that?
Yup -- it's like pulling teeth. Looking through the code base I've found a ton of stored procs that have "test" calls in comments. When you try and run them the comment no longer matches the sp and the sp doesn't work. A proper unit test framework should be run whenever a checkin / change is made. We aren't even going to try and rewrite tests for all the legacy code but as we develop new stuff we should write tests for them if possible.quote:
3. If the requirements change, won't the TTD code have to be rewritten anyways?
This is actually what I see as the biggest *advantage* of them. They let you know that some assumption or requirement is no longer being fulfilled when you change something. If tests start failing then you know that something has changed in a way that no longer works for your business rules.It's a lot easier to mock out objects in python or c# than it is in the database because the logic in there is so intrinsically connected to *data*I'd love to hear from any of you that do continual deployment how you manage changes to the database schema / objects...I'll try and keep this thread updated with details of what we try and implement. this is one of the areas of database development that I think doesn't get considered very often.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-06-02 : 15:28:23
|
quote:
Originally posted by Transact Charlie
Hi Lumbago, denis...don't think there is much of conceptual difference between a test script and a unit test (a unit test could be considered a script). One of the end goals is to automate the testing to support continual deployment....Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
That is what I am scratching my head over. Some promote TDD like it is a new thing and maybe it is but, I used to use a test robot application way back, you record your interaction with the appliation. Then the Robot will re-play it for regression test and compare outcomes.I read some of the article. I found it interesting how it did a begin tran/ roll back so as to not alter the test environment. Another way do do that, and not neccessarily better, is to save your Database when starting. Then you need to restore before you run your test scripts. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
mbaylon
Starting Member
2 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-06-14 : 07:19:45
|
| Just a quick update - we're still talking about how we are going to do this but many people are on holiday so there's no consensus:Points that came up1) There seems to be no way we could do 'in database' testing of objects that return more than one result set. We have a lot of sp's that return multiple result sets and there's no way yo get the information out of them in the database layer using INSERT EXEC or OPENROWSET -- or does anyone know a funky way?So we're leaning towards an NUNIT wrapping framework to write our unit tests in that will be committed along with the database code (somehow).Can I ask again -- if any of you have experience with continual deployment then I'd love to hear about your processes and experiences.Or is this just not a *thing* in the database community?Charlie.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
mbaylon
Starting Member
2 Posts |
Posted - 2011-06-14 : 18:42:48
|
| Charlie, if I have understood what you are asking then - my take on including database development in an overall continuous integration process:1. All devs have own database sandbox environment2. Always start with a baseline database schema - based on current production db schema ie the target deployment. All changes are placed in 'atomic' individual SQL scripts3. Source control used eg subversion. With tags/labels easy to get scripts/changes deployed in a specific database version4. All change scripts (for whole project team) placed in dir 'WorkingDirectory' - which is under source control5. Prefix added to name of script files to ensure running order eg 001_CreateTable, 002_CreateSproc etc6. Test first approach taken. Write tests - then write SQL script that passes test.7. Tests written in C#. I've use a framework I've created - see http://dbtestunit.wordpress.com - there are a number of others that could be used.8. All code/scripts/tests under source control9. Use automated build tools - such as cruise control/nant.10. Auto build when a developer checks in code (at a minimum this should be done once a day - a 'nightly build'). All done in a separate 'build' environment.11. All code - including database scripts are automatically built from source code and all unit tests run.12. Build tools autogens version number for whole system eg 2.0.1.27812. Database schema can be versioned eg includes a 'dbo.BuildVersion' table13. Build process (eg using nant token replacement) can version the overall db schema using 'dbo.BuildVersion' table and individual script files eg using extended properties14. If all unit tests pass - changes then can be automatically passed to other environments eg deployment, integration and performance testing. |
|
|
|
|
TTD and Unit Testing in the database