Building Browsergames: Implementing a flexible stats system (Perl)

You already know how to design a flexible stats system – so let’s go over how to implement it, using Perl.

To begin with, we will need some starting table structure and data:

CREATE TABLE users (
	id int NOT NULL AUTO_INCREMENT,
	username varchar(250),
	password varchar(50),
	PRIMARY KEY(id)
);
CREATE TABLE stats (
	id int NOT NULL AUTO_INCREMENT,
	display_name text,
	short_name varchar(10),
	PRIMARY KEY(id)
);
INSERT INTO stats(display_name,short_name) VALUES ('Magic','mag');
INSERT INTO stats(display_name,short_name) VALUES ('Attack','atk');
CREATE TABLE user_stats (
	id int NOT NULL AUTO_INCREMENT,
	user_id int,
	stat_id int,
	value text,
	PRIMARY KEY(id)
);

And now that this is done, we can begin actually writing code.

First off, we need a way to retrieve the stat value for a particular user ID. The SQL for this query looks like this:

SELECT value FROM user_stats WHERE stat_id = <foo> AND user_id = <bar>

That’s okay, but not exactly optimal – if we wanted to retrieve the value for a particular stat, we’d first need to figure out what its ID was(by using its name or short name), and then use that to figure out the value of the stat. Thankfully, we can shorten than down to just one query:

SELECT value FROM user_stats WHERE stat_id = (SELECT id FROM stats WHERE display_name = <foo> OR short_name = <foo>) AND user_id = <bar>

By using the above query, we can retrieve the stat value based on the stat’s name and the user’s ID – which is much easier for us as a developer to work with, because we can refer to our stats as “atk”, or “attack” – instead of needing to figure out that “atk” actually means “1″. Using this sub-query, retrieving the stat value for a player is easy:

1
2
3
4
5
6
7
8
9
10
11
sub getStat {
	my ($statName,$userID) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	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;
}

And that’s all we need to do to retrieve a stat. But what if we wanted to store a new value for a stat?

As it turns out, that’s pretty easy too – it’s practically the same code:

1
2
3
4
5
6
7
sub setStat {
	my ($statName,$userID,$statValue) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	my $sth = $dbh->prepare("UPDATE user_stats SET value = ? WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND user_id = ?");
	$sth->execute($statValue,$statName,$statName,$userID);
}

And that’s all there is to updating the stat! You can do whatever transformations you want to perform on the stat(like adding, subtracting, multipling) before you save it back into the database.

And that’s all the code that you need to use to implement stats in Perl! It’s just as simple as it sounds – and by doing it this way, you can add stats any time you need to without worrying about having to actually make changes to your database – just add the stat and write your new code to deal with it. Here’s all the code in one place(which is perfect for putting it into its own module – like stats.pm or something):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
sub getStat {
	my ($statName,$userID) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	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;
}
sub setStat {
	my ($statName,$userID,$statValue) = @_;
	use config;
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	my $sth = $dbh->prepare("UPDATE user_stats SET value = ? WHERE stat_id = (SELECT id FROM stats WHERE display_name = ? OR short_name = ?) AND user_id = ?");
	$sth->execute($statValue,$statName,$statName,$userID);
}

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.

Wednesday, May 7th, 2008 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