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)

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.

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.

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

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.

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

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"

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