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.

In reply to Re: (z) Separation of SQL code by dws
in thread (z) Separation of SQL code by zigdon

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.