Building Browsergames: DRYing out our database connections (Perl)

One piece of our code that definitely needs a bit of DRYing out is our database connections – we keep copying and pasting the exact same code anytime we want to connect!

We’ve been repeatedly writing out the exact same three lines, any time we want to connect to a database – in some cases, even multiple times in the same file! This is the offending code:

1
2
3
use DBI;
use config;
my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});

While this has worked for us so far, it’s still a bit of a bad idea – what happens if our game suddenly changes and needs to work off of an Oracle database? You would need to go through and find every single file where this code occurs, and manually change it. Not fun.

With that in mind, we’re going to simplify things a little bit – by creating a file that exists purely to connect to the database for us. We’re going to merge our database code with some of our configuration file code, to create a file called database.pm:

1
2
3
4
5
6
7
8
9
10
11
12
13
package database;
 
use Exporter;
use DBI;
use config;
our @ISA = qw(Exporter);
 
our $dbh;
our @EXPORT = qw($dbh);
 
$dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
 
1;

And now that that’s done, we’ll never have to worry about copying and pasting those lines again! In any file that interacts with the database, all we need to do is add this line, right at the top:

use database;

Which means we can modify our DRY stats code from looking like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
package statsDRY;
 
use DBI;
 
sub getStatDRY {
	my ($tableName,$columnName,$statName,$userID) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$config{dbName}:$config{dbHost}",$config{dbUser},$config{dbPass},{RaiseError => 1});
	createIfNotExistsDRY($tableName,$columnName,$statName,$userID);
	my $sth = $dbh->prepare("SELECT value FROM $tableName WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND $columnName = ?");
	$sth->execute($statName,$statName,$userID);
	my $value;
	$sth->bind_columns(\$value);
	$sth->fetch;
	return $value;
}
 
sub setStatDRY {
	my ($tableName,$columnName,$statName,$userID,$statValue) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$config{dbName}:$config{dbHost}",$config{dbUser},$config{dbPass},{RaiseError => 1});
	createIfNotExistsDRY($tableName,$columnName,$statName,$userID);
	my $sth = $dbh->prepare("UPDATE $tableName SET value = ? WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND $columnName = ?");
	$sth->execute($statValue,$statName,$statName,$userID);
}
 
sub createIfNotExistsDRY {
	my ($tableName,$columnName,$statName, $userID) = @_;	
	use config;
	my $dbh = DBI->connect("DBI:mysql:$config{dbName}:$config{dbHost}",$config{dbUser},$config{dbPass},{RaiseError => 1});
	my $sth = $dbh->prepare("SELECT count(value) FROM $tableName WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND $columnName = ?");
	$sth->execute($statName,$statName,$userID);
	my $count;
	$sth->bind_columns(\$count);
	$sth->fetch;
	if($count == 0) {
		# no entry for that stat/user combination - insert one with a value of 0
		$sth = $dbh->prepare("INSERT INTO $tableName(stat_id,$columnName,value) VALUES ((SELECT id FROM stats WHERE display_name = ? OR short_name = ?),?,?)");
		$sth->execute($statName,$statName,$userID,0);
	}	
}
 
 
1;

Into something like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package statsDRY;
 
use database;
 
sub getStatDRY {
	my ($tableName,$columnName,$statName,$userID) = @_;
	createIfNotExistsDRY($tableName,$columnName,$statName,$userID);
	my $sth = $dbh->prepare("SELECT value FROM $tableName WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND $columnName = ?");
	$sth->execute($statName,$statName,$userID);
	my $value;
	$sth->bind_columns(\$value);
	$sth->fetch;
	return $value;
}
 
sub setStatDRY {
	my ($tableName,$columnName,$statName,$userID,$statValue) = @_;
	createIfNotExistsDRY($tableName,$columnName,$statName,$userID);
	my $sth = $dbh->prepare("UPDATE $tableName SET value = ? WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND $columnName = ?");
	$sth->execute($statValue,$statName,$statName,$userID);
}
 
sub createIfNotExistsDRY {
	my ($tableName,$columnName,$statName, $userID) = @_;	
	my $sth = $dbh->prepare("SELECT count(value) FROM $tableName WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND $columnName = ?");
	$sth->execute($statName,$statName,$userID);
	my $count;
	$sth->bind_columns(\$count);
	$sth->fetch;
	if($count == 0) {
		# no entry for that stat/user combination - insert one with a value of 0
		$sth = $dbh->prepare("INSERT INTO $tableName(stat_id,$columnName,value) VALUES ((SELECT id FROM stats WHERE display_name = ? OR short_name = ?),?,?)");
		$sth->execute($statName,$statName,$userID,0);
	}	
}
 
 
1;

Because we used the our keyword to put $dbh into the root scope, we don’t need to bother re-useing the database code that we’ve created – all we do is put a use statement at the start of our code file, and we’re in business.

There is one slight caveat, however – you need to be very careful about scope in a situation like this. Because $dbh is now in the root scope, it’s very easy to accidentally try to use it when another function is trying to as well – which causes weird bugs. A good way to prevent this is to just clone over $dbh in whatever function needs it:

my $dbh = $dbh;

At which point you don’t need to worry about scope conflicts at all.

What have we gained by making this change? Well, we’ve managed to make it so that we don’t have to write as much code – and, if the way we connect to our database ever needs to change, we’ll only need to modify one file, instead of every single file in our codebase. This is one of the big benefits of DRY, and if you ever get into a situation where you need to make a change like this, you’ll definitely appreciate it!

Wish there was more?

I'm considering writing an ebook - click here.

.

Luke is the primary editor of Building Browsergames, and has written a large portion of the articles that you read here. He generally has no idea what to say when asked to write about himself in the third person.

Thursday, July 10th, 2008 DRY, buildingbrowsergames, code, design, perl
blog comments powered by Disqus

About

Building Browsergames is a blog about browsergames(also known as PBBG's). It's geared towards the beginner to intermediate developer who has an interest in building their own browsergame.

Sponsors

Got Something to Say?

Send an e-mail to luke@buildingbrowsergames.com, or get in touch through Twitter at http://twitter.com/bbrowsergames