All the Perl that's Practical to Extract and Report
use Perl Log In
Ovid (2709)
(email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)
Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.
Sometimes I feel stupid
Working on a site where performance is becoming a problem. Much of that is in the site design, but a lot of that is in my code. As the work is ongoing, I am getting plenty of experience refactoring, but this morning, it finally dawned on my that I had programs instantiating multiple database handles without good reason. Now, by adding only three lines of code, I've converted the database handle to a singleton and it works perfectly.
If you're not familiar with singletons, they are a "Design Pattern" whereby you only allow one instantiation of a particular object. In this case, with one exception, I don't need separate database handles for every object. As everything goes through one database API, I check to see if I already have a handle. If so, I return that handle rather than create a new one, unless different permissions are needed.
use Test::More tests => 3;
use constant MODULE => 'Foo::Database';
can_ok( MODULE, 'new' );
my $db1 = Foo::Database->new;
isa_ok( $db, MODULE );
my $db2 = Foo::Database->new;
is( $db1->{_dbh}, $db2->{_dbh},
'... and it should return a singleton database handle' );
I pour myself a cup of strong coffee. :-)
-- Jarkko Hietaniemi
Old trick (Score:2)
Coincidentally, I'm having to deal with someone else's code at the moment which is running our database out of connections.
alternative (Score:1)
Apache::DBI (Score:2)
If you're doing this in a web environment, you can just use Apache::DBI, and it will make sure that only a single connection exists for each Apache child for you (assuming you use the same connection arguments every time.
--David
Better to say I was ignorant (Score:2)
djberg96 wrote: I learned to avoid that long ago, but then maybe I've been doing DBI longer.
It's quite possible that you've been using DBI longer, but I've been using it for three or four years now, so I can't use that as an excuse. The real problem here is simple: I'm rather mediocre in terms of experience. I understand logic well and, as a result, I found that I had an ability to write spaghetti code that ran relatively bug-free compared to code of many of my coworkers. However, it's only been in
DBI optimizations (Score:1)
Depending on how ofter a statement is used you might find the prepare_cached() call useful. It really helped one of my programs.
$sth = $dbh->prepare_cached($statement);Be careful though, you'll want to create your statement with parameters rather than using variable interpolation otherwise you are caching statements that you might not want to cache.
my $sql = "SELECT lname FROM cust WHERE lname = ?";$sth->execute($lastname);
rather than
my $sql = "SELECT lname FROM cust WHERE lname = $lastnSecurity (Score:1)
-Dom
Re:Security (Score:2)
I've improved the security of the site by ensuring that I don't forget to untaint or use DBI->quote. Those I have used religiously to ensure that nothing nasty can occur. I never directly interpolate user-supplied data in an SQL statement. Whatever other problems occur with my code, security isn't the largest (though I realize that there are always security holes).
We had another programmer here who didn't bother about security. I tried to explain to him how database security works in conjunction wi