TestingSQL

- Last edited March 6, 2002

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? www.castsoftware.com/products/DevelopmentTools/sqltools.html

Welcoming your ideas -- PaulS

TimBacon just ran across this article 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

KentBeck hinted at Xp2001? that there was a long way to go where databases are concerned, and that there would be great rewards for people how made it easier. At BondClick? we used Oracle8i? and TopLink?, toplink generated all the insert update and delete calls, but there were against views (which can be read write in 8i). Then under the views some business logic was added, but the logic was very much just taking a highly denormalised view (which made Toplinks job easier), and putting things into the right tables.--OliBye

As an associated point, does anyone have any hints on how to test (or even sensibly call!) PL/SQL stored procedures which make use of custom data types not supported by the JDBC driver. A client of mine has a 'large' enterprise application, written mostly in PL/SQL in Oracle 7.3.4 which makes much use of the likes of PL/SQL arrays of PL/SQL structures. I have found to my chagrin that even Oracle's own JDBC drivers can't handle such data types - so I am unable to call them from Java. Has anyone else come up against this problem ? --FrankCarver

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

Databases are hard to test because: the developers are not the DBAs - getting test rigs set up usually involves weeks of grief.

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 www.allroundautomations.nl/plsqldev.html - exceedingly good value for money when compared to Oracle's tools or Toad.

-DarrenH

- Last edited March 6, 2002

https://casino-brain.com/