Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

(z) Separation of SQL code

by zigdon (Deacon)
on Sep 11, 2003 at 13:25 UTC ( #290680=perlquestion: print w/replies, xml ) Need Help??

zigdon has asked for the wisdom of the Perl Monks concerning the following question:

In starting to work on a new (largish) project, we're trying to make some basic design decisions. 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.

The problem is that now any code that needs to use the database object is loading a lot of code that it doesn't need.

So my question, O esteemed monks, is should I continue to use this method (perhaps with autoloading methods as needed, for increased speed), or is it considered "better" to have each object (User object, Item object) carry it's own SQL code?

Thanks for any advice!

-- zigdon

Replies are listed 'Best First'.
Re: (z) Separation of SQL code
by jeffa (Bishop) on Sep 11, 2003 at 14:04 UTC
    Check out this comparison. I too am a fan of Class::DBI, but who is to say that SPOPS or Tangram is not the correct choice for your needs? My only gripe with Class::DBI is its inability to transparently handle complex queries involving multiple table joins. I asked this question on the poop-group mailing list and i received one answer (directly to my email address - it's not on the list) that made me want to switch to Tangram. We'll see. ;)

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
    -R--R-RR-R--R-RR-R--R-RR
    B--B--B--B--B--B--B--B--
    H---H---H---H---H---H---
    (the triplet paradiddle with high-hat)
    
Re: (z) Separation of SQL code
by dragonchild (Archbishop) on Sep 11, 2003 at 13:45 UTC
    <INSERT SUPERHERO MUSIC HERE/>

    Class::DBI to the rescue! :-)

    Seriously, though, there are a HUGE number of solutions to this problem on CPAN, most of which will actually have better performance and more features than any home-grown solution devised by a person who doesn't eat, drink, and sleep DB optimizations in Perl. I'd highly suggest look at them, especially Class::DBI which may fit your problemspace to a T.

    ------
    We are the carpenters and bricklayers of the Information Age.

    The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

    Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

Re: (z) Separation of SQL code
by dws (Chancellor) on Sep 11, 2003 at 15:34 UTC
    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.

      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.

Re: (z) Separation of SQL code
by hardburn (Abbot) on Sep 11, 2003 at 13:45 UTC

    Neither. After several abortive attempts, I've finally started using Class::DBI, and I now curse myself for not trying it before.

    ----
    I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
    -- Schemer

    Note: All code is untested, unless otherwise stated

      Yah, I've been meaning to look at Class::DBI for a while now, maybe this will be the excuse I need :)

      -- zigdon

Re: Separation of SQL code
by adrianh (Chancellor) on Sep 11, 2003 at 14:48 UTC
    The problem is that now any code that needs to use the database object is loading a lot of code that it doesn't need.

    And this is a problem why?

    (serious question - this is smelling a little like premature optimisation to me :-)

    is it considered "better" to have each object (User object, Item object) carry it's own SQL code?

    In general no. I would always try an isolate the SQL code into a separate module or the database itself.

      It actually is a case of premature optimization. The problem is, we're trying to build a site that on it's launch date, might get something in the order of 2M visits a month. And with our (way-too-limited) budget, we have to squeeze as much out of our hardware as possible. So we do try to write the code, from the ground up, in a way that will scale very well.

      I would always try an isolate the SQL code into a separate module

      So you would create modules like Project::User::SQL, Project::Item::SQL, or just a Project::SQL?

      Thanks!

      -- zigdon

        It actually is a case of premature optimization. The problem is, we're trying to build a site that on it's launch date, might get something in the order of 2M visits a month. And with our (way-too-limited) budget, we have to squeeze as much out of our hardware as possible. So we do try to write the code, from the ground up, in a way that will scale very well.

        I still don't see how splitting it into separate modules will help. If you've got a server that's needs to deal with all of these objects they you'll be loading them all anyway - so you don't get a saving.

        So you would create modules like Project::User::SQL, Project::Item::SQL, or just a Project::SQL?

        Depends on the project.

        If there was mostly a good mapping from tables to objects then I would probably use Class::DBI, or roll something similar myself. So the SQL would be handled automagically by the base-class.

        If not, then I would likely use DBIx::AnyDBD to create a Project::Default module that would contain all the functions that needed to talk to the database directly. All database access would go through this module.

        I might split this into multiple classes if I felt that there were parts of the database that were likely to be changed independently of each other. However, this would be very unlikely to be at the level of individual objects since I very rarely find that level of granularity useful.

Re: (z) Separation of SQL code
by Abigail-II (Bishop) on Sep 11, 2003 at 14:27 UTC
    I would push the SQL as far away from the Perl code as possible: stored procedures in the database.

    Abigail

      There will always be SQL in the Perl code, if only to call the stored procedures. So, the design question remains as to where that SQL to call the stored procedure should live. Granted, you now have business-level SQL calls with well-designed names, so it's a simpler problem.

      ------
      We are the carpenters and bricklayers of the Information Age.

      The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

      Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

        There will always be SQL in the Perl code

        Unless you decide to use Class::Phrasebook, which I've also discussed here. It has meant all the fiddling about is done in one file not in several modules. Plus porting between CVS, MySQL and MS SQL7 has simply been a matter of changing the phrasebook.

        --
        Barbie | Birmingham Perl Mongers | http://birmingham.pm.org/

        Well, as Abigail mentioned, if you use stored procs you can limit the amount of database-specific code to something very small.

        At eCircles (unfortunately dead now) we had a web site with around 80k lines of perl code, of which 400 were database-specific (using Sybase::CTlib), and with table-driven definitions for each stored procedure (i.e. logical database request). And because we were using Sybase's RPC functionality to call the procs there was 0 risk of SQL injection as there was no SQL parsing involved anywhere in the execution path.

        DBD::Sybase is capable of using the RPC functionality as well

        Michael

        Well, you also have far less SQL code in your program. If all you call is stored procedures, you only need one function that contains creates SQL to call a stored procedure.

        Too bad DBI doesn't support calling of stored procedures yet, otherwise, you wouldn't have to create SQL in your program at all.

        Abigail

      I believe that's not an option in MySQL? And this project probably can't use anything else, due to technical and budget constraints.

      -- zigdon

        Have you looked at PostgreSQL? It's also free (as in beer) and supports stored procedures in a number of languages, including Perl, and apparently works nicely with PHP as well.

        There are a number of visual DBA tools available for it (some also free) which make it pretty easy to use.

        I've been using the Eclipse IDE lately, and like the Eclipse plugin, Quantum, which extends the basic IDE to allow working with most databases that provide JDBC support.

        - mikeB

        Oh, your question suggested you were working on a serious project. MySQL is a feature-poor database that I can't take seriously.

        Abigail

Re: (z) Separation of SQL code
by bean (Monk) on Sep 11, 2003 at 17:23 UTC
    Unless you've benchmarked your application and found this to be a real problem, I wouldn't worry about loading a little extra code. Other issues will be more important. Does your site use authentication? Don't reauthenticate every page (seems obvious, I know, but I'm supporting an application that does this - and I can't change it because my group does not control the authentication module) - use Apache's native authentication or an encrypted cookie instead. Cache database queries when appropriate. Instead of keeping session information in the database or on the server, keep it in the user's (encrypted) cookie. Javascript, while annoying to write (and a possible accessibility issue), can help you avoid unnecessary hits on the server. For instance, instead of redirecting server-side, you can redirect client-side - instead of reloading the page to get a secondary drop-down list, create the list dynamically. I once wrote a file manager application that loaded the user's entire directory structure into a javascript hash - then the user could navigate through his/her directories without hitting the server again - javascript would rewrite the page as appropriate (this was necessary because the users were in South America with slow connections and the server was in New Jersey). If you duplicate (at least some) form validation client-side, you'll have fewer resubmits, which will lighten the load on your server (I said "duplicate" because you'll still have to do all the validation server-side, since you can never trust information from the client). If you're writing a web based application (file manager, mail client, quality record system, chat room, etc) rather than something people will want to bookmark (a news-site, blog, etc), frames (although unfashionable) might be appropriate - they divide the application into discrete units (for a three-frame application, usually main navigation, secondary navigation, and the active screen) and make it so you don't have to reload parts of that application that aren't changing. If you do use frames, be sure to include code to maintain frame state (if a frame is loaded independently, redirect to the frameset). Even if your project doesn't lend itself to frames, you can still cache sections of the page, like navigation, polls, etc.
Re: (z) Separation of SQL code
by bsb (Priest) on Sep 12, 2003 at 08:58 UTC
    It sounds like your current version is similar to Ima::DBI, which is the basis for Class::DBI so your transition could be quite easy. I'm a fan of Class::DBI and have tried Alzabo (which I suspect Class::DBI is evolving into, they just added column objects). I think Alzabo tries to do too much, YMMV.

    I use MySQL with Innodb tables when I can, I just need transactions. That's when I can't ditch the RDBMS entirely. There seems to be such a mismatch between schema oriented and strongly-type databases and Perl.

    Regarding stored procedures vs perl logic, read Domain Logic and SQL for a language neutral consideration of the problem. The article points out a few cases where you'd want to go one way or the other but boils down to "Choose based on your company resources and culture"

Re: (z) Separation of SQL code
by princepawn (Parson) on Sep 11, 2003 at 21:33 UTC
    Dont forget SQL::Catalog !!!

    Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality... download and use The Emacs Code Browser

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://290680]
Approved by smitz
Front-paged by htoug
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (3)
As of 2022-05-21 09:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (76 votes). Check out past polls.

    Notices?