TestingSQL

This keeps raising its ugly head. Its too easy to say "don't put business logic there" but there's many reasons why it makes sense to do so.

So how can we test, refactor and generally practice test-first design for stored procedures?

Just like many java IDEs, we need to first understand callers and called for each function. We need the confidence to make enhancements or refactorings ( "extract function?" :-) ). We need to write tests that exercise an sp, though we might have to accept that the database itself is a necessary resource for the tests.

I wonder if tools like this could help? http://www.castsoftware.com/products/DevelopmentTools/sqltools.html

Welcoming your ideas -- PaulS

TimBacon just ran across this article http://www.agilemodeling.com/essays/dataRefactorings.htm which might be useful. (I also had a conversation with a colleague the other day about how SQL generally is treated as 2nd class code - less well supported in editors and IDEs, often handed off to junior programmers eg when there's a lot of boiler-plate CRUD code to write - resulting in code that is indented poorly, contains very very long methods, etc etc. This makes SQL hard to start refactoring just because it's hard to read!)

Nice link, Tim, I wonder if we can formulate a few rules which might help the "should I write this in a stored proc or in the code?" decision. -- PaulS

Could you wrap them in further PL/SQL procedures that present a more accessible interface? --SteveF

Consider utPLSQL -- http://oracle.oreilly.com/utplsql/index.html -- for testing Oracle PL/SQL procedures from within the database. -- JeffGrigg?

Databases are hard to test because: they are generally shared, and nobody wants to be the guy who trashed the system.

Databases are hard to test because: developers often have patchy knowledge of how to tackle testing in the first place - such things as having a read-only mirror of your schema + test data so you can always get back to a working baseline. Having everything fully scripted so you can always get back to a baseline... just being able to quickly and easily get back to a working baseline is a great means of reducing fear. That and having a 'sandbox' system that the developers can muck about with, without being screamed at by 50 people if they break it for half a day. If your development workstations are powerful (and stable) enough, you might be able to get away with having the project's test DB on one of those.

On the subject of tools - those using Oracle might want to take a look at http://www.allroundautomations.nl/plsqldev.html - exceedingly good value for money when compared to Oracle's tools or Toad.

-DarrenH

I think that a lot of the problems come from what PaulS? said earlier, in that there is often a skills mismatch between the people writing the SQL stuff and the people doing the rest. We all know that we should try and avoid putting business logic in the SQL layer and so we use inexperienced developers here because they "are not going to have much to do". This tends to make the database code less maintainable. How much shared ownership is there of the SQL code? Often, not a lot. We also often avoid getting a decent DBA on board because they are not worth the money for the limited amount they will have to do.

Its a pity because a decent DBA will get around lots of the things that are causing the problems above. Developers having their own version of the DB is a good example of this. It is straightforward to do, but virtually never done.

-GeorgeB