Building Browsergames: Reducing Repetition (Perl)

While working on your own browsergame project, one of the things that will slow you down the most is re-writing the exact same code. Unfortunately, we’ve been doing that with our stats code – as reader sepp helpfully pointed out.

In the programming world, there is a concept known as ‘DRY’ – which stands for “Don’t Repeat Yourself”. Applying DRY to your code means that you should never have to write code to do almost the same thing twice – you should be writing code flexible enough to do both. Using a DRY approach can drastically cut down our development time, in addition to improving our code’s maintainability(because there’s less of it to worry about). So today, we’re going to convert our stats code from being 3 different pieces of code to a single piece of DRY code.

So far, we have 3 helper modules that retrieve our stats for us – each from different tables and columns, but for the most part they’re the exact same code. Here’s a refresher on what our stats::getStat function looks like:

sub getStat {
	my ($statName,$userID) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	createIfNotExists($statName,$userID);
	my $sth = $dbh->prepare("SELECT value FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND user_id = ?");
	$sth->execute($statName,$statName,$userID);
	my $value;
	$sth->bind_columns(\$value);
	$sth->fetch;
	return $value;
}

While that would be fine if we were only going to use this code for retrieving user stats, it’s organically grown into 3 different places with the exact same code – which is bad. Let’s rewrite the code slightly, so that any of our stats code can call it:

sub getStatDRY {
	my ($tableName,$columnName,$statName,$userID) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$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;
}

And with that done, we can now use that function to retrieve any of our stats – not just user stats. All we have to do is pass in the table and column name for the stat table, in addition to the information for which stat value we want to retrieve.

Doing it this way works because our stats tables are laid out in virtually the same way – they all have a name, and they’re virtually exactly the same, except for the name of the one column that tracks the ID number of whatever we are storing stats for. If our stats tables were very different from each other, this might not work quite as easily.

One thing that you need to notice and be aware of is the fact that neither the table name or the column name are escaped when we build our SQL query – which means that there’s potential for a SQL injection vulnerability there. You can get around this and keep your system safer by never using user input for the table or column names in your calls to this code.

We’ve seen how the change works for the getStat function – now let’s see how it goes for the other two:

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);
	}	
}

As you can now see, there isn’t a big change being made for this – all we do is add two more arguments to our functions, and then modify the way we create our queries. But the gains we’re making in terms of development are huge.

You might be wondering why these functions now have the word ‘DRY’ at the end of their names – the reason for this is that we can now use these functions for one of the cooler aspects of DRY – being able to replace whole chunks of backend logic, without having to modify the primary codebase. If you save the current file as statsDRY.pm, you can now modify stats.pm to look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
package stats;
use DBI;
 
use statsDRY;
 
use constant TABLE => 'user_stats';
use constant COLUMN => 'user_id';
 
sub getStat {
	my ($statName,$userID) = @_;
	return statsDRY::getStatDRY(TABLE,COLUMN,$statName,$userID);
}
 
sub setStat {
	my ($statName,$userID,$statValue) = @_;
	statsDRY::setStatDRY(TABLE,COLUMN,$statName,$userID,$statValue);
}
 
1;

And this is where you see the real gains of DRY – now, all of our stats logic is inside of statsDRY.pm, and if we ever need to modify it we’ll only need to modify that one file. We can change the way that all of our stats code works, from a single file – and those changes will seamlessly work accross item stats, user stats, monster stats, and any other stats we decide to implement in the future.

While our current codebase is relatively small, this isn’t a big deal – but what if you were making this change and had 20 different files calling stats::getStat()? You wouldn’t want to go through each and every one of them and modify the call – by moving our stats code out into its own module, we’ve made it so that you only have to edit a file or two, instead of having to edit every file that ever uses your stats code. That’s a real time-saver, and one of the great aspects of using the DRY approach to writing code.

There isn’t anything ‘new’ to this code, with the except of the two constants we set up – TABLE, and COLUMN. These two constants are what are passed to our DRY stats retrieval code, and creating them allows us to make another DRY change. Now, if you ever decide to modify where your user stats are stored, you only need to modify those two constants – and not go through every file that uses user_stats, changing it to the name of whatever table you switched to. While this will probably never happen, it’s still a good idea to have this in place – just in case.

Here’s what the finished statsDRY.pm looks like:

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;

I’ve also rewritten stats.pm to use this new code – you could do this for all your stats code, or(if you preferred, and are fairly certain your database structure won’t change) you could even swap all the code to just use the DRY functions instead of working off of the stats modules. Here’s what stats.pm looks like now:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
package stats;
use DBI;
 
use statsDRY;
 
use constant TABLE => 'user_stats';
use constant COLUMN => 'user_id';
 
sub getStat {
	my ($statName,$userID) = @_;
	return statsDRY::getStatDRY(TABLE,COLUMN,$statName,$userID);
}
 
sub setStat {
	my ($statName,$userID,$statValue) = @_;
	statsDRY::setStatDRY(TABLE,COLUMN,$statName,$userID,$statValue);
}
 
1;

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.

Tuesday, July 8th, 2008 Uncategorized
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