design

Spreadsheets are your friend

I use Excel, but there are other spreadsheet programs out there. OpenOffice’s Calc is a good free app alternative. That being said, I will be using Excel’s functions and while Calc’s may be similar, I cannot guarantee they are the same. We will build a spreadsheet together and in the process go over some useful functions and features and talk about game balancing.

Spreadsheets are useful because it provides a way for you to enter or create equations for things such as Health Points and then actually see the data at different levels. Another great thing is that Excel functions can use data from another cell. This means you can create several different data sets, one building off the other. This allows what I like to call Relational Formulas. These formulas stay consistent and balanced because as one factor changes, they also change.

I recently decided to see what I could do to improve my game’s current HP formula and rate of increase of weapon’s damage. What was happening was maximum HP was not increasing with each level as much as player’s stats and weapons at low to mid levels, but HP started to get very high at higher levels. This meant very short battles for level 5-30, but long battles for those 50 and up.

Lets build a basic HP formula sheet and I can show you how I balanced this. Open a new spreadsheet and name the first three columns: Level, HP and HP Diff., respectively. I usually make my headers bold just to stand out as well. Put a 1 in the first level column cell (A2).

Column Headers

Now in A3 type the following: =A2+1
There are a couple different ways of expanding a formula across a column or row. One way is to copy the single cell and highlight all the cells you wish to paste into and paste. Excel also allows you to hover over the bottom right corner of a cell and click, hold and drag over the cells you wish to put that function into. Keep in mind that wherever you move this equation, the cell it references will always stay relational. In this case, no matter what cell you copy this into, it will always add 1 to the cell directly above it. For the Level column, this is an easy way of not having to type 1-100 manually! If you ever wish to copy the value of a cell using an equation instead of the equation itself, just copy paste and then click the small box that appears nearby and select “Value Only.”
Lets click and drag the bottom right corner of A3 down A21. That will give us 20 levels to see.

Now for the HP formula. Most HP formulas involve level, that is why I included that in the first column. You could use this template to experiment with different formulas. If you have another factor in your equation, just add the data to another column and reference it in the formula. In my game I like to have each level’s new HP build upon their previous max HP since they may have increased their HP through Stat points. If I limit my equation so that each level has to have X many health points, it will not work well with any other means of increasing max HP.

After much experimenting I will use the following equation: Square root of your level * 1000 + your previous HP.
So enter into B2: =(sqrt(A2*1000))+168

That is just level 1 though, so enter into B3:=(sqrt(A2*1000))+B2
Why add the 168 you may ask? Well you can change that to whatever you want to move the starting HP. With 168 it gives us 200 as our starting HP, but you could add +968 and start at 1000 HP if you wanted. (If you are wondering where that equation came from, keep an eye out for a future post about how to create equations to do what you want. In this case, the square root allows me to have large increases in HP even at low levels, but still larger at higher levels, just not exponentially so.)

But what about the annoying decimal numbers because of the square root? This is where you will want to use the ROUNDUP or ROUNDDOWN function. Simply add it to the beginning of your equation and then add a coma and zero telling it to round to zero decimals. =ROUNDUP(sqrt(A2*1000)+B2,0) Putting 1 or 2 will make it round to 1 or 2 decimal points, respectively. Putting -1 or -2 would go to the opposite side of the decimal point and round to the tens and hundreds.
Now drag that equation down the column to row 21 and you will see the base maximum HP for up to level 20.

I am a big fan of seeing every possible variable I can and having them in data cells since I never know when I might want to use them in a formula.
The third column will simply show us the difference in HP between the HP for the previous level and the current for each level. The first row (C2) will be left blank. Type this into C3: =B3-B2
Drag it down to row 21 and you can see how the HP increases per a level. As mentioned before, you can now see how the HP jumps up a good amount just from level 1 to 2, but it continues to increase gradually, just not on a drastic curve.

The basic tips on making equations, dragging equations and rounding up or down can be used for anything, not just HP. You could use it to make a sheet that shows you the Experience points required for each level.
You could use this to list your weapons and their damage and then make an equation to make the cost of the weapon dependent on how much damage it does; now it is consistent and you don’t have to come up with numbers out of thin air.
Get creative and use this valuable tool to both document data as well as balance things by making them relational, or just experiment with different stat or cost values.

Tuesday, July 29th, 2008 balancing, design 1 Comment

Diary of a Browsergame: Planning out the templates

Now that I’ve set up my database, it’s time to settle on a templating system, and decide on how I want my templates to work.

Working Title only has a few pages that will need to be displayed - according to Design 1.0, there are a total of 7 - Home, Design Document, Register, Login, Logout, Codex, and Play. This will make developing the templates very easy, as there are only a few that I need to build.

I’m going to use HTML::Template for my templating system, just like we have been in our Perl-based browsergame tutorial. However, my templating setup will be slightly different; I am planning on nesting two templates within each other. I’ll have a simple outer template which defines some of the global styles for all of the pages, and then another inner template that is populated with the information unique to the page(this is an example of applying DRY to my templates).

To that end, I’ve whipped up a quick outer template for Working Title, that will(for the moment) do what I need it to. There isn’t much to it, but you can take a look at it here.

Is it a rough template? Yes. Will it change before Working title is done? Probably. But it’s enough to get me up and running, and free up my time to work on developing the rest of the game, instead of just working on the template.

Wednesday, July 23rd, 2008 design, diaryofabrowsergame, templates, workingtitle 2 Comments

Balancing your game: real-time or actions-based?

While you’re building your browsergame, there’s one important point to consider: balancing your game.

A lot of developers create browsergames without thinking about balancing them first - this usually tends to result in games that are released with some serious balance issues. Certain characters might be over-powered in comparison to others, and certain items might be complete gamebreakers - things that make the game virtually unplayable for players without the item by comparison.

How will you solve the balance problem in your browsergame?

One approach that a lot of browsergame developers take is limiting users to a certain number of turns in a day - that way, a user who has all the time in the world and a user who can only sign in for 5 minutes each day can both remain relatively close to each other in terms of in-game performance.

While action-point/turn-based systems are a good way to keep your players a little more evenly matched, they don’t (unfortunately) fix balance problems - at best, all they can do is prolong the amount of time that it takes for a balance issue to appear.

Here’s an example: you run a small browsergame, with 5000 users who are active daily. Players can play the game in real-time, and at any given moment there are at least 200 players online at once, interacting with the game. In a weekly update, you push out a new sword that can only be found after defeating a particular boss monster. Unbeknownst to you, this sword in combination with the armor you released three weeks ago combine to form the most powerful combination in the game - capable of instantly killing any player who is attacked by a player using both of them.

In a real-time game, you’ll notice this issue fairly quickly - all of a sudden, a handful of players will be able to instantly defeat any other player. You’ll soon start to see complaints in your forums (if you have them), and you’ll be able to watch as more players find the item and begin to exploit the balancing issues present.

Now think of the same scenario, in the context of a turn-based game. Users get 25 turns per day, and it takes them 40 turns to get to the boss (if they don’t use turns, they stack up). It will be at least 2 days post-update for a user to get to the boss, let alone retrieve the sword - and if it takes them a full turn to attack a player, a user who just acquired the sword (assuming they had 50 turns saved up) will be able to instantly kill 10 players. Now, is the 10 players being instantly defeated by a single player a balance issue, or just a player who is that much stronger than 10 other players?

As you can see, there are pros and cons to either system. If you build a real-time system, there are more balance issues related to keeping casual and hardcore players even - but you will quickly find balance issues that have to do with gameplay features. In a turn-based system, there are far less problems keeping individual players balanced - but gameplay features can easily cause balance headaches that you won’t find for a long period of time.

So how will you balance your game? The answer is entirely up to you - although I would recommend setting your game up in a way that suits your development style. If you heavily test your changes before deploying them to a production environment, you can probably safely get away with a turn-based system - you’ll just need to make sure you thoroughly test all of your changes before you deploy them. If you’d rather have your players do the testing for you, and just fix balance issues as they come up - go for real-time. Either way, it’s your game - and the only important thing is recovering from balance issues when they do appear.

Tuesday, July 22nd, 2008 balancing, design 3 Comments

Using the “on-view” method instead of cron

bardic commented on the cron post from yesterday, and he said:

While crons are a good way to do such a task, they aren’t the best solution. For a game with a large DB a cron can take too long to cycle through the DB.

bardic is right, here - in any situation where you need to deal with large amounts of data, using cron is a little less than optimal. In that case, you’d want to implement something that bardic refers to as the “on-view” event - essentially, you store information about the time a command was last run, and you use that time to figure out when the command should run next.

Implementing this in your own database is a trivial change; all you need to do is add a timestamp to the table you need to track times for, and then compare it to the current time. Here’s what an example table might look like:

CREATE TABLE actions (
	id int NOT NULL AUTO_INCREMENT,
	user_id int,
	energy int,
	last_updated timestamp,
);

And then all you would need to do in your code is something like this, on every page an authenticated user visits(pseudocode):

last_time = get_last_update_time();
current_time = get_current_time();
if(current_time - last_time >= tick_duration) {
	number_of_updates = int((current_time-last_time)/tick_duration);
	for(i = 0; i < number_of_updates;i++) {
		update_energy_value();
	}
}

And that would be essentially all you’d need to do - just check the last time a user’s information was updated, and then update it if it’s been longer than the amount of time you want to update a user’s stats in. Also, if you write your code in such a way that you can just pass in what to update the value with, you can reduce the overhead of using a for() loop as well:

	update_energy_value(update_amount * number_of_updates);

Now, this will allow you to keep large amounts of data updated, without encountering any sorts of timeout issues - and is therefore perfect for making sure that things like player stats update themselves at the proper times. However, there are still some areas where it’s better to use cron.

For example, let’s say that you are building a browsergame where players health regenerates automatically, at a rate of 1 health every 30 minutes. In this game, time passes just like in the real world - every day counts as a ‘year’ in the game. In this situation, you would want to use the “on-view” method to update the player’s health - but a cronjob would be a much better way to update the “year” value every day. The in-gameyear isn’t really linked to a particular player, and it’s a micro-value - something that is only available in a single place. While you could certainly use the “on-view” method to update the year as well, it’s not quite as practical - a simple cronjob can easily keep your year updated for you, with the added benefit of only running as it’s needed - as opposed to every single time a player visits certain pages.

In summary, “on-view” is another way to achieve virtually the same functionality as cron - with a far more reliable way to keep large amounts of data up-to-date when there are many users playing your game. However, there are still places for cron - and both approaches can be used together to provide the best of both worlds.

Update

In a quick e-mail, bardic has mentioned that this idea came out of a discussion from his blog - most notably, these two posts:

Friday, July 18th, 2008 design No Comments

Building Browsergames: Securing our hashes (Perl)

Yesterday, we worked on securing our hashes in PHP - and today, we’re going to take a look at our Perl systems.

According to tilly from perlmonks, crypt() is horribly insecure - which is definitely a big problem. We’re going to need to update our login and registration system, so that our user’s passwords are a little bit more secure if a malicious user ever gained access to our database.

First off, we’re going to convert our code to use the Digest::MD5 module so that we can MD5 our passwords - in addition to salting them. If you don’t have it already, you’ll need to install Digest::MD5. Enter this at your shell(or get your web host to install it for you if they haven’t already):

cpan install Digest::MD5

After installing Digest::MD5, we need to change each line that calls crypt() inside register.cgi and login.cgi. Here’s what the relevant line in register.cgi looks like now:

33
34

			use Digest::MD5 qw(md5);
			$sth->execute($arguments{username},md5('saltgoeshere' . $arguments{password}));

And that’s the only change that you need to make to your register or login code - just adjusting your execute() statements to md5 the password, and making sure that you

use Digest::MD5

in each code file.

However, if you upload your new files and take a look, you’ll notice something - you can no longer log in!

Unfortunately, making this change has broken logins for all of our users who signed up before we had to make this change. Now, if your game hasn’t been released to the public yet or everyone is used to you ‘resetting’ the game, this is fine - but if users are already playing your game, this is a bit of an issue. So we’re going to modify login.cgi, so that it first checks to see whether a user is still using a crypt()’d password or not - and if they are, it will redirect them to a page where they can change their password. Here’s what login.cgi looks like now:

#!/usr/bin/perl -w
 
use strict;
use CGI qw(:cgi);
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);
use DBI;
use config;		# this is our database settings
use HTML::Template;
use Digest::MD5 qw(md5);
 
my $query = new CGI;
my %arguments = $query->Vars;
 
my $template = HTML::Template->new(
		filename	=>	'login.tmpl',
		associate	=>	$query,		# for argument memory
	);
my %parameters;
 
if(%arguments) {
	my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
	my $sth = $dbh->prepare("SELECT COUNT(id) FROM users WHERE UPPER(username) = UPPER(?) AND password = ?");
	my $count;
	$sth->execute($arguments{username},crypt($arguments{password},$arguments{username}));
	$sth->bind_columns(\$count);
	$sth->fetch;
	if($count == 1) {
		my $cookie = $query->cookie(
				-name	=>	'username+password',
				-value	=>	$arguments{username} . '+' . crypt($arguments{password},$arguments{username}),
				-expires	=>	'+3M',
			);
		my $uri = 'changepass.cgi';
		print $query->header(-cookie=>$cookie,-location=>$uri);
	} else {
		$sth = $dbh->prepare("SELECT COUNT(id) FROM users WHERE UPPER(username) = UPPER(?) AND password = ?");
		$sth->execute($arguments{username},md5('saltgoeshere' . $arguments{password}));
		$sth->bind_columns(\$count);
		$sth->fetch;
		if($count == 1) {
			$sth = $dbh->prepare("UPDATE users SET last_login = NOW() WHERE UPPER(username) = UPPER(?) AND password = ?");
			$sth->execute($arguments{username},md5('saltgoeshere' . $arguments{password}));
			$sth = $dbh->prepare("SELECT is_admin FROM users WHERE UPPER(username) = UPPER(?) AND password = ?");
			my $is_admin;
			$sth->execute($arguments{username},md5('saltgoeshere' . $arguments{password}));
			$sth->bind_columns(\$is_admin);
			$sth->fetch;
			my $cookie = $query->cookie(
					-name	=>	'username+password',
					-value	=>	$arguments{username} . '+' . md5('saltgoeshere' . $arguments{password}),
					-expires	=>	'+3M',
				);
			my $uri = 'index.cgi';
			if($is_admin == 1) {
				# redirect to admin page
				$uri = 'admin.cgi';
			}
			print $query->header(-cookie=>$cookie,-location=>$uri);
		} else {
		$parameters{error} = 'That username and password combination does not match any currently in our database.';
		}
	}
}
 
$template->param(%parameters);
print $query->header(),$template->output();

It might be hard to tell what’s different, there - but take a look at our code to check and see if the user’s information matched anything in our database. We start off by checking the username and password using crypt() - and if it matches anything, we redirect them to changepass.cgi - which we’ll be building shortly. If it doesn’t match with crypt(), we check to see if it matches with Digest::MD5’s md5() function - and if it doesn’t match either of those, we tell the user that it didn’t match anything.

If you haven’t guessed it yet, changepass.cgi is going to be the page users use to change their password. Here’s what the template(changepass.tmpl) looks like:

<html>
<head>
	<title>Change Password</title>
</head>
<body>
	<tmpl_if name='error'>
		<span style='color:red'>Error: <!--tmpl_var name='error'--></span>
	</tmpl_if>
	<tmpl_if name='message'>
		<span style='color:green'><!--tmpl_var name='message'--></span>
	</tmpl_if>
	<form method='post' action='changepass.cgi'>
		Password: <input type='password' name='password' id='password' /><br />
		Confirm Password: <input type='password' name='confirm' /><br />
		<input type='submit' value='Change Password' />
	</form>
	<script type='text/javascript'>
	document.getElementById('password').focus();
	</script>
</body>
</html>

Here’s the code for changepass.cgi:

#!/usr/bin/perl -w
 
use strict;
use CGI qw(:cgi);
use DBI;
use config;
use Digest::MD5 qw(md5);
use HTML::Template;
 
require login;
 
my $query = new CGI;
my %arguments = $query->Vars;
my $cookie = $query->cookie('username+password');
my ($username) = split(/\+/,$cookie);
my %params;
 
if(%arguments) {
	if($arguments{password} ne $arguments{confirm}) {
		$params{error} = 'Passwords do not match!';
	} else {
		my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass,{RaiseError => 1});
		my $sth = $dbh->prepare("UPDATE users SET password = ? WHERE UPPER(username) = UPPER(?)");
		$sth->execute(md5('saltgoeshere' . $arguments{password}),$username);
		$params{message} = "Password updated successfully.";
	}
}
 
my $template = HTML::Template->new(
		filename	=>	'changepass.tmpl',
	);
$template->param(%params);
print $query->header(), $template->output;

And that’s all there is to it! By making this quick change, we’ve secured our user passwords a little better against any malicious users - and as an added bonus, we’ve created a change password page!

Note: don’t forget to change ’saltgoeshere’ to an actually random value, like ’s79dj@#*(hd’ or something - you won’t make any security gains if malicious users can easily guess your password salt. If you’re feeling really adventurous, you could(and probably should) turn the salt into a configuration parameter - but I’ll leave how to do that up to you.

Wednesday, July 16th, 2008 buildingbrowsergames, code, design, perl, security No Comments

Building Browsergames: Securing our hashes (PHP)

John Munsch recently pointed out that there’s a bit of a glaring security hole in our login and registration systems: at the moment, we’re extremely vulnerable to Rainbow Table attacks. In John’s words:

The MD5 hash doesn’t actually protect you if someone were able to dump your table of users or gain access to the database in some fashion.

And you know what? He’s absolutely right. If a malicious user managed to get access to our database at the moment, our user’s logins wouldn’t be protected at all. This is a big problem, and something we need to fix.

Unfortunately, because password hashing is one-way, we can’t just get users to reset their password. They’ll either need to re-register entirely, or we can setup a special page(and stat) in order to make sure that users have reset their passwords. John recommends adding what’s known as a ’salt’ value to user’s passwords - that way, you might have something like this:

password = 'foo'
password + salt = 'foobrownfox'
hashed password = hash('foobrownfox')

And if a user were to attack our login information using a rainbow table, they might manage to figure out that the passwords being stored in the database were values like ‘foobrownfox’ - but they’d have a bit of a harder time figuring out what was the salt value and what was the actual password.

Luckily, this is a pretty easy fix to implement - we just modify our two calls to md5() in our login and register code to add a salt to the user’s passwords. Here’s the changes we make to register.php:

24	mysql_real_escape_string(md5('saltgoeshere' . $password)));

Unfortunately, at this moment making these changes breaks things for users who signed up before we had to make this fix. In order to try and keep things as seamless as possible for the user, we’ll be modifying our login code slightly:

	$query = sprintf("SELECT COUNT(id) FROM users WHERE UPPER(username) = UPPER('%s') AND password='%s'",
		mysql_real_escape_string($username),
		mysql_real_escape_string(md5($password)));
	$result = mysql_query($query);
	list($count) = mysql_fetch_row($result);
	if($count == 1) {
		$_SESSION['authenticated'] = true;
		$_SESSION['username'] = $username;
		header('Location:changepass.php');
	} else {
		$query = sprintf("SELECT COUNT(id) FROM users WHERE UPPER(username) = UPPER('%s') AND password='%s'",
			mysql_real_escape_string($username),
			mysql_real_escape_string(md5('saltgoeshere' . $password)));
		$result = mysql_query($query);
		list($count) = mysql_fetch_row($result);
		if($count == 1) {
			$_SESSION['authenticated'] = true;
			$_SESSION['username'] = $username;
			$query = sprintf("UPDATE users SET last_login = NOW() WHERE UPPER(username) = UPPER('%s') AND password = '%s'",
				mysql_real_escape_string($username),
				mysql_real_escape_string(md5('saltgoeshere' . $password)));
			mysql_query($query);
			$query = sprintf("SELECT is_admin FROM users WHERE UPPER(username) = UPPER('%s') AND password='%s'",
				mysql_real_escape_string($username),
				mysql_real_escape_string(md5('saltgoeshere' . $password)));
			$result = mysql_query($query);
			list($is_admin) = mysql_fetch_row($result);
			if($is_admin == 1) {
				header('Location:admin.php');			
			} else {
				header('Location:index.php');				
			}
		} else {	
			$error = 'There is no username/password combination like that in the database.';
		}
	}

We’ve made a small change to our login code, so that it first tests to see if the user’s attributes match up to any users who haven’t had their passwords salted - if they do, we redirect them to the page where they can change their password. Here’s what the template for our ‘change password’ page(change_pass.tpl) looks like:

<html>
<head>
	<title>Change Password</title>
</head>
<body>
	{if $error ne ""}
		<span style='color:red'>Error: {$error}</span>
	{/if}
	{if $message ne ""}
		<span style='color:green'>{$message}</span>
	{/if}
	<form method='post' action='changepass.php'>
		Password: <input type='password' name='password' id='password' /><br />
		Confirm Password: <input type='password' name='confirm' /><br />
		<input type='submit' value='Change Password' />
	</form>
	<script type='text/javascript'>
	document.getElementById('password').focus();
	</script>
</body>
</html>

With the template created, all we need to do is build the page that handles changing the user’s password - like so:

<?php
 
include 'smarty.php';
require_once 'login-check.php';
 
if($_POST) {
	$password = $_POST['password'];
	$confirm = $_POST['confirm'];
	if($password != $confirm) {
		$error = 'Passwords do not match!';	
	} else {
		require_once 'config.php';		// our database settings
		$conn = mysql_connect($dbhost,$dbuser,$dbpass)
			or die('Error connecting to mysql');
		mysql_select_db($dbname);
		$query = sprintf("UPDATE users SET password = '%s' WHERE username = '%s'",
					mysql_real_escape_string(md5('saltgoeshere' . $_POST['password'])),
					mysql_real_escape_string($_SESSION['username']));
		mysql_query($query);
		$message = 'Password updated successfully.';
	}	
}
$smarty->assign('error',$error);
$smarty->assign('message',$message);
$smarty->display('change_pass.tpl');
 
?>

And that’s all there is to it! With a fairly simple change, we’ve managed to secure our user’s information a bit better - a malicious user with direct access to our database won’t be able to easily figure out what a user’s password is just by using a Rainbow table. As an added bonus, we’ve also created an extra piece of functionality - a change password page!

Tuesday, July 15th, 2008 buildingbrowsergames, code, design, php, security No Comments

Diary of a Browsergame: Setting up the database

One of the first things I do for any new project of mine is design the database - so that’s what’s going to happen to Working Title today. This design will probably change slowly over the course of development - it’s just a good starting point for now.

To begin with, we’ll need a users table.

CREATE TABLE users (
	id int NOT NULL AUTO_INCREMENT,
	username text,
	password text,
	PRIMARY KEY(id)
);

And once we have that, a stats table:

CREATE TABLE stats (
	id int NOT NULL AUTO_INCREMENT,
	display_name text,
	short_name varchar(25),
	PRIMARY KEY(id)
);

If this all looks familiar so far, that’s because it should - this is the exact same table structure(currently, anyway) as the game we’ve been working on. I’m taking all of the design decisions and changes that we made in our other project, and doing my best to apply them here - thereby continually improving my code, and my design.

Because Working Title will have a few distinct ‘entities’, my entity_stats is going to look a little bit different than the one we’re using right now. According to the design document, there are 4 main entities - players, rooms, monsters, and items. Those will make the enum in the entity_stats 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','Room'),
	PRIMARY KEY(id)
);

Thinking about it a little more, I’m pretty sure that I don’t need separate tables for each of the entities that can exist within Working Title - all I need is a single table to keep track of their name, ID, description, and type. So that’s what I’m going to do:

CREATE TABLE entities (
	id int NOT NULL AUTO_INCREMENT,
	name text,
	entity_type ENUM('Monster','Item','Room'),
	description text,
	PRIMARY KEY(id)
);

One of the things that the design document mentions is that “users will move between rooms” - which means that I will need way to track which room a user is currently in. I’m going to use a stat to do this:

INSERT INTO stats(display_name,short_name) VALUES ('Current Room','cur_room');

There are also supposed to be players, monsters, and items inside rooms - so I’ll need a table to keep track of all the entities within a room:

CREATE TABLE room_entities (
	id int NOT NULL AUTO_INCREMENT,
	room int,
	entity_id int,
	entity_type ENUM('Monster','User','Item'),
	PRIMARY KEY(id)	
);

Monsters will be able to drop items, so I’m going to need to add a table to do that. Because all they’re going to be able to drop is items(not other monsters or rooms or anything), I can make it so that the monster_drops table only contains information on what items, and what the percentage chance is for them to drop:

CREATE TABLE monster_drops (
	id int NOT NULL AUTO_INCREMENT,
	monster int,
	item int,
	drop_rate int,
	PRIMARY KEY(id)
);

The next table I need to add is one to track a user’s inventory - so I’ll add the user_items table:

CREATE TABLE user_items (
	id int NOT NULL AUTO_INCREMENT,
	user_id int,
	item int,
	quantity int,
	PRIMARY KEY(id)
);

The design document mentions “____ of holding”'s, which is something I haven’t quite figured out yet. I could just use a stat to keep track of the player’s current inventory limit, or I could come up with a better idea. For the moment, I’m going to leave this piece of functionality out of my database design - I’m not sure how to build it yet.(If you know a good way to do this, send me an e-mail at buildingbrowsergames@gmail.com, or comment on this blog post)

Based on the list of commands players can use, there are a few stats that I can add into the database right now:

INSERT INTO stats(display_name,short_name) VALUES ('Maximum Health','max_hp');
INSERT INTO stats(display_name,short_name) VALUES ('Current Health','cur_hp');
INSERT INTO stats(display_name,short_name) VALUES ('Current Weapon','cur_weapon');
INSERT INTO stats(display_name,short_name) VALUES ('Current Armor','cur_armor');
INSERT INTO stats(display_name,short_name) VALUES ('Current Ring (Left Hand)','cur_ring_left');
INSERT INTO stats(display_name,short_name) VALUES ('Current Ring (Right Hand)','cur_ring_right');
INSERT INTO stats(display_name,short_name) VALUES ('Gold In Bank','bank_gc');
INSERT INTO stats(display_name,short_name) VALUES ('Gold In Hand','hand_gc');
INSERT INTO stats(display_name,short_name) VALUES ('Current Level','cur_lvl');
INSERT INTO stats(display_name,short_name) VALUES ('Current Experience','cur_exp');
INSERT INTO stats(display_name,short_name) VALUES ('Experience to Next Level','next_exp');

That sets me up with some of my starter stats, although it’s definitely not all of them - I’m sure I’ll end up adding more stats as Working Title grows into more of a finished product.

The last table that I’m going to add for now is simply for keeping track of exits off of rooms: room_exits:

CREATE TABLE room_exits (
	id int NOT NULL AUTO_INCREMENT,
	name text,
	from_room int,
	to_room int,
	PRIMARY KEY(id)
);

I’ll be tracking the entity_id of both of the rooms, and using the name value to select which exit. For example, an exit to the North from room 1 to 2 might look like this:

INSERT INTO room_exits(name,from_room,to_room) VALUES ('North',1,2);

With all this finished, there’s only one more system to add - spells. I’m not quite sure how I want to implement spells yet, either - do I want to have them do a set amount of damage, or base it on level, or something else? I’m going to hold off on designing any database tables for my spells system for now - once I have Working Title in a playable state, I’ll work on adding spells. For now though, I’m going to stick with what I have - I’ve got the basics of my database in place, and I can safely start developing now.

Monday, July 14th, 2008 SQL, code, database, design, diaryofabrowsergame, workingtitle 2 Comments

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

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

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

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

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

<?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

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

Ever wanted to build a browsergame?

design

Update

PHP

Perl

PHP

Perl

PHP

Perl