Re: (z) Separation of SQL code

[dws]

In the past, I've always seperated all the SQL code to methods in the Database object, in hopes that it would make it easier to update in case the database design changes.

We (== current project team) work the problem from Uses Cases (or User Stories) on down. Uses Cases will tell us what kind of protocols are needed at the "service" layer. The service layer is very thin, and delegates to a "manager" layer for the real work. This delegation lets us swap in mock objects so that we can unit test the service layer, and clients of the service layer, without having to go against real data. This latter part makes it practical to run unit tests frequently (e.g., several times an hour).

The manager layer delegates to a database strategy, where all details of SQL are kept safely isolated from the rest of the code. This lets us write separate unit test that exercise the manager layer against live data. Since these unit tests require more setup and are typically slower, we run them less often (e.g., a few times a day, or more often if we're building out new manager functionality).

This is one or two more layers than most people are used to, but it lets us test pieces in isolation, which has proven to be a very big win.

Should we ever need to switch databases, we should only have to write a new set of database-specific strategy classes. Having unit tests to work backwards from should make the process go quickly.

Works for us. YMMV.

By the way, this approach doesn't preclude using something like Class::DBI in the database strategy classes.

[htoug]

You might even separate the layers into separate processes, perhaps even on separate machines.

We've done that with very good results. There are benefits to be had from protecting the application from the "messy" database design: if you normalize your database design - as you should - the database tables often bear very little resemblance to wwhat the user sees. Keeping that mapping in a tightly controlled module is Good(TM). Further benefits are to be got from using stored procedures (as Abigail-II notes)

Having the front-end (user and possibly badguy accessible) macine not having direct access to your valuable database is also a security bonus.