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;Coincidentally, I'm having to deal with someone else's code at the moment which is running our database out of connections.
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
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 the past couple of years that I've finally started to learn about the art/science of programming.
Other people may scratch their heads and say "why did Ovid do something so stupid?", but it's inexperience. I'm getting better and I still feel that I am better than the vast majority of Perl programmers out there, but that's due to the huge number of dotcom babies who thought that they could move from HTML to building Web-based applications with Perl in 24 hours (at least, that's what the books tell 'em). Naturally, I look like a god to them
I learn fast, though. Give me a couple of years and I'll have merlyn coming to me for advice (just kidding!).
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 = $lastname";
$sth->execute();
-Dom
Re:Security
Ovid on 2002-12-12T19:02:51
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 with Web apps and he wouldn't listen. He once asked us to test out some code that he was very proud of and in five minutes I had generated an entry in the error log saying that I couldn't drop a table due to foreign key constraints. The "DROP TABLE" command was embedded in the URL I had created. Nothing should ever get that close to the database.