Building Browsergames: DRYing out our stats

Over the course of developing our game, our database has sort of grown organically. As we added a new feature, we’d add the tables we needed to accomodate this feature. While this works(and has been working for us just fine), it’s not neccessarily the best way to design your database – because as you can see, we now have 3 stats tables(user_stats,monster_stats, and item_stats) when we only actually need one – as sepp has once again helpfully pointed out.

With that in mind, we’re going to create another table, to replace those three. Because lots of different things can have stats, I’m going to call the table entity_stats – although if you want to, you can call it whatever you want(but make sure to keep your change in mind when you’re working through this code). Here’s what we’re going to do to create the table:

CREATE TABLE entity_stats (
	id int NOT NULL AUTO_INCREMENT,
	stat_id int,
	entity_id int,
	value text,
	entity_type ENUM('User','Monster','Item'),
	PRIMARY KEY(id)
);

Now that we’ve created this new table, we’re going to need to customize our SQL queries slightly. Previously, we were only using an object’s ID value to retrieve the stats for it – now that we don’t have that separation, we will need to run the query based on two things – the object’s ID, and the object’s type.

Because we’ve been doing re-writing to our stats code, we’re actually in a good position to make this change – we only need to change the query in one file, instead of 3(or more). Our old SQL looked like this:

"SELECT value FROM table WHERE stat_id = (SELECT id FROM stats WHERE display_name = 'foo' OR short_name = 'bar') AND column = 'baz'"

The new SQL is going to look like this:

"SELECT value FROM entity_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = 'foo' OR short_name = 'bar') AND entity_id = 'baz' AND type = 'bat'"

One of the benefits of doing this is that we’re actually going to be cleaning up our new DRY stats code a little bit more, too – we get to trim it down to only take a ‘type’ argument, instead of the table and column names it needs to retrieve with. Think back to our DRY stats code from earlier:

PHP

1
2
3
4
5
6
7
8
9
10
11
12
function getStatDRY($tableName,$columnName,$statName,$trackingID) {
	createIfNotExistsDRY($tableName,$columnName,$statName,$trackingID);
	$query = sprintf("SELECT value FROM %s WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND %s = '%s'",
		mysql_real_escape_string($tableName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($columnName),
		mysql_real_escape_string($trackingID));
	$result = mysql_query($query);
	list($value) = mysql_fetch_row($result);
	return $value;		
}

Perl

1
2
3
4
5
6
7
8
9
10
11
sub getStatDRY {
	my ($tableName,$columnName,$statName,$userID) = @_;
	my $dbh = $dbh;
	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 here’s what it looks like with the change made:

PHP

1
2
3
4
5
6
7
8
9
10
11
function getStatDRY($type,$statName,$trackingID) {
	createIfNotExistsDRY($type,$statName,$trackingID);
	$query = sprintf("SELECT value FROM entity_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND entity_id = '%s' AND entity_type = '%s'",
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($trackingID),
		mysql_real_escape_string($type));
	$result = mysql_query($query);
	list($value) = mysql_fetch_row($result);
	return $value;		
}

Perl

1
2
3
4
5
6
7
8
9
10
11
sub getStatDRY {
	my ($type,$statName,$trackingID) = @_;
	my $dbh = $dbh;
	createIfNotExistsDRY($type,$statName,$userID);
	my $sth = $dbh->prepare("SELECT value FROM entity_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND entity_id = ? AND entity_type = ?");
	$sth->execute($statName,$statName,$trackingID,$type);
	my $value;
	$sth->bind_columns(\$value);
	$sth->fetch;
	return $value;
}

Once we’ve made those changes, it’s easy to modify all of our code for the new database structure:

PHP

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
45
46
47
48
<?php
 
include 'database.php';
 
function getStatDRY($type,$statName,$trackingID) {
	createIfNotExistsDRY($type,$statName,$trackingID);
	$query = sprintf("SELECT value FROM entity_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND entity_id = '%s' AND entity_type = '%s'",
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($trackingID),
		mysql_real_escape_string($type));
	$result = mysql_query($query);
	list($value) = mysql_fetch_row($result);
	return $value;		
}
 
function setStatDRY($type,$statName,$trackingID,$value) {
	createIfNotExistsDRY($type,$statName,$trackingID);
	$query = sprintf("UPDATE entity_stats SET value = '%s' WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND entity_id = '%s' AND entity_type = '%s'",
		mysql_real_escape_string($value),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($trackingID),
		mysql_real_escape_string($type));
	$result = mysql_query($query);
}
 
function createIfNotExistsDRY($type,$statName,$trackingID) {
	$query = sprintf("SELECT count(value) FROM entity_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s') AND entity_id = '%s' AND entity_type ='%s'",
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($trackingID),
		mysql_real_escape_string($type));
	$result = mysql_query($query);
	list($count) = mysql_fetch_row($result);
	if($count == 0) {
		// the stat doesn't exist; insert it into the database
		$query = sprintf("INSERT INTO entity_stats(stat_id,entity_id,value,entity_type) VALUES ((SELECT id FROM stats WHERE display_name = '%s' OR short_name = '%s'),'%s','%s','%s')",
		mysql_real_escape_string($statName),
		mysql_real_escape_string($statName),
		mysql_real_escape_string($trackingID),
		'0',
		mysql_real_escape_string($type));
		mysql_query($query);
	}	
}
 
?>

Perl

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
package statsDRY;
 
use database;
 
sub getStatDRY {
	my ($type,$statName,$trackingID) = @_;
	my $dbh = $dbh;
	createIfNotExistsDRY($type,$statName,$userID);
	my $sth = $dbh->prepare("SELECT value FROM entity_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND entity_id = ? AND entity_type = ?");
	$sth->execute($statName,$statName,$trackingID,$type);
	my $value;
	$sth->bind_columns(\$value);
	$sth->fetch;
	return $value;
}
 
sub setStatDRY {
	my ($type,$statName,$trackingID,$statValue) = @_;
	my $dbh = $dbh;
	createIfNotExistsDRY($type,$statName,$userID);
	my $sth = $dbh->prepare("UPDATE entity_stats SET value = ? WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND entity_id = ? AND entity_type = ?");
	$sth->execute($statValue,$statName,$statName,$trackingID,$type);
}
 
sub createIfNotExistsDRY {
	my ($type,$statName, $trackingID) = @_;	
	my $dbh = $dbh;
	my $sth = $dbh->prepare("SELECT count(value) FROM entity_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND entity_id = ? AND entity_type = ?");
	$sth->execute($statName,$statName,$trackingID,$type);
	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 entity_stats(stat_id,entity_id,value,entity_type) VALUES ((SELECT id FROM stats WHERE display_name = ? OR short_name = ?),?,?,?)");
		$sth->execute($statName,$statName,$userID,0,$type);
	}	
}
 
 
1;

And with that done, our change is made! Now it’s just a matter of modifying the code we created for each stat specifically(only one shown):

PHP

1
2
3
4
5
6
7
8
9
10
11
12
<?php
 
require_once 'stats-dry.php';
 
function getStat($statName,$userID) {
	return getStatDRY('user',$statName,$userID);
}
function setStat($statName,$userID,$value) {
	setStatDRY('user',$statName,$userID,$value);
}
 
?>

Perl

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

And once all of the changes are done, you’re good! Now all of the stat values will be stored in a single table.

But what about users who have already registered for your game, or monsters and items that you’ve already added?

We definitely don’t want to have to tell every single person playing our game to re-register – so we’re going to write some quick SQL to copy the values over for us, before removing the tables that we no longer need:

INSERT INTO entity_stats(stat_id,entity_id,value,entity_type) (SELECT stat_id,user_id,value,'user' FROM user_stats);
DROP TABLE user_stats;
INSERT INTO entity_stats(stat_id,entity_id,value,entity_type) (SELECT stat_id,monster_id,value,'monster' FROM monster_stats);
DROP TABLE monster_stats;
INSERT INTO entity_stats(stat_id,entity_id,value,entity_type) (SELECT stat_id,item_id,value,'item' FROM item_stats);
DROP TABLE item_stats;

Once we’ve run that SQL, we’ll have deleted our 3 new tables – but not before we moved the data over from them to our new entity_stats table. Once you make the necessary modifications to your stats code and upload it all, take a look at your game – it will still work just like it did before!

This might seem like a weird thing to get excited about, but it’s a pretty big deal – we just changed a significant portion of our database, and didn’t break any of our existing code at all. This is why the DRY approach is so useful to have, and why you should always strive to use DRY in your designs to begin with – because making this change with the database logic spread accross dozens of files would drive even the calmest developer batty.

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.

Friday, July 11th, 2008 DRY, buildingbrowsergames, code, database, design, perl, php
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