Math Buster 3 has passed review for the App Store and is available for iPhone 3GS+, iPod touch 3rd generation+ and iPad (Requires iOS 3.1.3 or later). If you don't have an iOS device, it released on Android devices (2.2+, Froyo) about 4 weeks ago.
The leaderboards are cross platform so, whether you're on an Android or iOS device, you can still compete against each other.
This release will likely mark the end of the Math Buster series (at least for the forseeable future) since I don't have any ideas to further extend on the current formula and I have a handful of ideas for new games that I want to try out!
In general, unit testing consists of writing code to make sure other code runs correctly. From wikipedia:
unit testing is a method by which individual units of source code, sets of one or more computer program modules together with associated control data, usage procedures, and operating procedures, are tested to determine if they are fit for use.
I'm working on a Runner-type game and one of the main features of the game is randomly generated geometry. Platforms are randomly generated as you play so no two games will ever play out the same way. One of the scary things about procedurally-generated gameplay is that unexpected things can happen which may completely break the game. In my case, platforms could be generated on top of each other or could even cross paths, potentially blocking the player from continuing to play. There's a lot of code in place to help prevent this but the only way to know for sure is to test it in a live scenario. I could sit there and play the game over and over to make sure nothing bad happens but that's a colossal waste of time when there's much better ways to go about it (in my case at least). That's where unit testing comes into place. I can speed up the platform generation much faster than I could ever play through it myself and if something happens that shouldn't, I can throw an error with all kinds of information to help figure out why it happened and fix it.
Usually, unit testing goes on solely in code and if everything works as intended, nothing will happen (or the unit test will relay that it had no problems). The thing about unit testing is that it will only check what you tell it to check. This is great for testing to make sure a number stays between 1-100, for example, but I don't have the time to code/think out every single scenario of platforms that could be spawned one after another. This is where the whole "visual unit testing" idea comes into place. Watching the platform generation happen has the added benefit of being able to catch anything that shouldn't happen but wasn't accounted for. If I catch something happen I can always include it in the unit test for future tests to make sure the fix actually works.
In this specific test, I simply spawn a random platform and delete the oldest platform every 0.1 seconds. The result? It looks a lot like snake...
You heard it right, Math Buster 3 is now available on Android 2.2+ devices (Froyo and above)! You can install/view the game from this link and if you get a chance to play, let me know what you like/dislike about it.
The iOS version will be a week or two behind (if there aren't any major issues) since my Macbook is in the mail. Once it's here I can start running through everything to see what works and what doesn't. I'll throw another post together when the iPhone release happens.
Part 4. We've setup a database to store all of our data and we've created 3 tables to store our Leaderboard score and our Usernames. Now we'll learn some of the basics of SQL which allows us to add data to our tables (INSERT) and how to read data from them (SELECT). For this part we'll focus solely on the basics of SQL and go through a handful of different queries that let's us read/write data to our database. In the next part we'll deal with HOW to communicate with our database from the outside and we'll use PHP to do so. Our PHP code will contain SQL queries so I wanted to split the SQL/PHP parts up instead of just throwing everything out at once and hoping it sticks in your brains. 🙂
Lets open up phpMyAdmin again and click on our Usernames table on the left-hand side and then navigate into the SQL tab at the top of the screen. Once again, we're presented with a blank box that let's us run SQL queries inside of it. We did this in part 3 when we created our Challenge table using SQL code instead of using the interface. If you look underneath the input box you'll notice a bunch of buttons; SELECT *, SELECT, INSERT, etc. These are different statements that we can run on our SQL tables. SELECT * means "select all", INSERT means "add" and UPDATE lets us change existing data in our tables. If you click on INSERT a default example will appear in the input box that looks like the following:
Let's pick this example apart before we modify it ourselves just to keep everyone on the same page.
- You'll notice INSERT INTO is the first thing we see followed by 'Usernames' in backticks. This is pretty self explanatory, "insert this data into our Usernames table."
- Next, we have 'username' and 'uniqueid' inside the first set of parenthesis. These are the names of the columns where we're going to insert the data. If you remember when we created our Usernames table we have 1 column named username and another named uniqueid. These names MUST match the exact name of the column. If we put username1 instead of username we would get a "Unknown column 'username1'..." error.
- Finally, we have the VALUES statement. The number of arguments in the parenthesis must match the number of arguments in our INSERT INTO statement parenthesis. Since we want to add a username and uniqueid into Usernames, we need to supply 2 values that correspond to those arguments.
Let's fill out the full query and run it to see what happens. Replace the [value-1] and [value-2] values with data of your liking. The first argument, [value-1], corresponds to our username argument so let's change [value-1] to 'jeff' (make sure to add the single quotes around it). Our second argument relates to uniqueid so let's add a random unique id just for the purpose of this article: 'abc123'. The final query ends up looking like this:
INSERT INTO Usernames(username, uniqueid) VALUES ('jeff','abc123')
Press Go in the bottom-right corner and the result of the query will be posted above the input box. If it succeeds it should say something similar to "1 row inserted." Let's navigate to the Browse tab at the top so we can look at our newly created data in the Usernames table. NOTE: notice that 'Usernames' and the INSERT INTO variables (username and uniqueid) in the example code that is generated have backticks around them, while our VALUES (jeff and abc123) have single quotes around them. Backticks are used in table names and column identifiers and that is to avoid issues with reserved keywords (like if you wanted to name your table 'null' for some reason). I don't use backticks so they won't be included past this point, they're just included so you know to look out for them in the future. Single quotes are used around string variables and those will be used anytime we're entering values. You should see the new entry near the middle of the screen:
You'll also that notice near the top of the screen it shows the current SQL query that was run to display the current results:
LIMIT 0 , 30
It's selecting all the data from the Usernames table and returning results 0-30.
Now that we've added a user to our Usernames table we can add data to either of our leaderboard tables; Arcade and Challenge. Remember when we setup the Foreign Keys to attach the username column in our leaderboard tables to the username column in our Usernames table? We had to insert a user into our Usernames table before we could add data to our leaderboard tables. If we tried to add data to our leaderboard table and we specified a username that doesn't exist in our Usernames table we would get an error similar to "Cannot add or update a child row: a foreign key constraint fails" and this is because we must reference an existing username in our Usernames table. This might seem like added complexity (if you didn't want to support Users changing their names then it would be) but for our needs it's necessary.
Let's open up our Arcade table and insert some scores into it. Navigate to the SQL tab like before and click on INSERT underneath the input box. We'll get a familiar looking INSERT INTO statement but this one has 4 arguments instead of 2; id, username, score and timestamp. Lets fill in the values with some actual data:
INSERT INTO Arcade(id, username, score, timestamp) VALUES (null, 'jeff', '465', DEFAULT)
For the id argument we pass in null. This is because it's setup to auto-increment automatically without us expressing a unique index ourselves. For the username argument we must pass in an existing username (from the Usernames table) so I'll pass in 'jeff' since that's what I used beforehand. The score argument can be any integer value that has a length of 10 or less so I just used '465'. Finally, the timestamp argument is setup to use the CURRENT_TIMESTAMP as the default and we'll use that which is signified by writing DEFAULT. In the future we won't specify the id and timestamp arguments since they will default to acceptable values, so let's add another score to our Arcade leaderboard without using those arguments:
INSERT INTO Arcade(username, score) VALUES ('jeff', '247')
Add 3 more random scores into our Arcade table so we have a total of 5. After you add those scores click on the Browse tab to verify that we have 5 scores saved in our table.
Now that we've learned how to insert some data into our tables, how can we read the data? We can use the SELECT statement to choose which data we want returned in our query. Let's go back into the SQL tab for our Arcade table. If we want to view all of the data in the table we can use the following query:
SELECT * FROM Arcade
Which reads as "SELECT everything FROM our Arcade table". If you haven't caught on yet, after the FROM statement we always specify the name of a table in our database. In our case we'll always have either Arcade, Challenge or Usernames after any FROM statement. What if we just want to see how many rows (i.e. scores) are in our Arcade table? We could use the following query:
SELECT COUNT(*) FROM Arcade
This returns an integer with the number of entries we have, so in our case it should return 5. What if we wanted to return scores that are greater than a certain value?
SELECT * FROM Arcade WHERE score > 300
This will return all scores that are greater than 300. The WHERE statement is an extra condition that must be met to the returned data. We'll use this one in almost all of our queries so we can get only the data we want. There's no reason to return all the data in our table then sort through it later using PHP (or whatever language you're using) because that's just a waste of resources and bandwidth. What if we want to order our scores from lowest to highest?
SELECT * FROM Arcade ORDER BY score ASC
This will order scores ASCENDING in value, so from the lowest values to the highest. Finally, how do we return a certain number of scores or return a certain page of scores? Pagination is something we'll do when we want to display our score because we don't want to display all the scores at once, we just want to show scores 10 at a time, for example, like 1-10, 11-20, etc.
SELECT * FROM Arcade LIMIT 0, 3
This will start at row 0 and display 3 scores. If we wanted to show our bottom 2 scores we could use LIMIT 3, 2 (start at row 3 and display 2 scores).
That's it for our quick SQL lesson! There's quite a bit more complicated SQL queries that we can run and we'll tackle those as we come across them in the next part where we'll start using PHP so we can communicate with our database from the outside.
In part 1 we talked about the requirements to get everything set up and in part 2 we created a database and added a User to that database so we can pass data to/from it. For part 3 we'll use phpMyAdmin to create tables in our database which will store all of our leaderboard/username data. After opening phpMyAdmin the dashboard page should look similar to the following:
On the left hand side we can see the database we created, in my case it's _MyGameDatabase. After clicking on our database it tells us that there aren't any tables found, so lets create our first one through the interface (we'll create a table using solely SQL next).
Creating a table through the phpMyAdmin interface.
Click on Create table and we're presented with a new screen that has all kinds of options:
So let's start filling out all of the required information. Where it says Table name let's name our table Arcade. Before we start filling out data in our columns, let's start by adding 2 more columns to our table. At the bottom of the screen we can see "Or Add [x] column(s)". Let's enter "2" in the text box and press "Go" next to it. This should update our table to display 4 columns instead of 2.
Now we need to fill our each of the 4 columns. Here's what the table should look like when finished (I'll explain the key points below):
|Attributes:||on update CURRENT_TIMESTAMP|
|Null:||NO (unchecked)||NO (unchecked)||NO (unchecked)||NO (unchecked)|
|AUTO_INCREMENT:||YES (checked)||NO (unchecked)||NO (unchecked)||NO (unchecked)|
Here's the general idea behind each column.
- id - ID is a column that we'll never add data to ourselves. This column is our "Primary index" that is auto-incremented so that every piece of data in our table (each row) will have a unique index. When we add data to this "id" column a number will automatically be incremented for each row, so no 2 rows will have the same id.
- username - Even though we'll have a Usernames table later on, we want to have a username attached to each score so we can easily display who got this score when we display it in our game. Usernames are case-insensitive (capitalization doesn't matter) as abbreviated by the "ci" in utf8_general_ci in our Collation row and we also impose a 15 character limit here just for the sake of keeping things short. If you want to change the length of usernames allowed for you game you can change that here. When the User updates their username in our game, we update the username in our Usernames table and when the username in the Usernames table is updated, it also updates the username attached to each score in our leaderboard tables. More on this later when we create the Usernames table.
- score - Our scores are saved as integer values with a max length of 10. This allows our scores go up to the single billions if necessary so if you have scores that will be larger than that, increase the limit here.
- timestamp - We also time stamp each of our scores so if we add a "find my scores from the last 24 hours" feature then we'll be able to check our 'timestamp' value for comparision.
When you have all of the data filled in, choose InnoDB as the Storage Engine and hit Save to save our Arcade table. InnoDB supports Foreign Keys which we'll make use of to update the username attached to each score when the User changes their username. Now on the left-hand side of our screen we should see our Arcade table listed.
Creating a table using SQL.
Creating the table using the interface is nice but sometimes you don't have access to all these nice features. Using solely SQL, we'll create a leaderboard named Challenge which will contain the exact same structure as the Arcade table we created above. At the phpMyAdmin dashboard (the first image displayed in this post) click on our database _MyGameDatabase and you should see the Arcade table listed. At the top of the page you can see different tabs; Structure, SQL, Search, etc. Click on SQL and you should be presented with the following screen:
Make sure you're about to run a query on your _MyGameDatabase database (as shown by the "Run SQL query..." line at the top of the screen, displayed in the image above) and not inside one of our tables. Using the CREATE TABLE function we can add a table to our database like we did through the interface before. Here's the code that will create a table with the same structure as our Arcade table but done solely using SQL:
CREATE TABLE `myUsername_MyGameDatabase`.`Challenge` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`username` VARCHAR( 15 ) NOT NULL ,
`score` INT( 10 ) NOT NULL ,
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY ( `id` ) ,
KEY `username` ( `username` )
) ENGINE = INNODB DEFAULT CHARSET = utf8;
NOTE: ALL of my databases are prefixed with my username that I sign in with and an underscore, so in the table above my actual database name is myUsername_MyGameDatabase (I black out my username in the images if it's displayed). When you paste this into the blank box be sure to change myUsername_MyGameDatabase to the name of your database, which should be displayed directly above the blank box (refer to the picture above for reference). After pasting this into the blank space and pressing Go you should notice that our Challenge table pops up on the left-hand side next to our Arcade table and it is successfully created.
We now have 2 tables, Arcade and Challenge, which represent 2 of the leaderboard modes in our game. I mentioned in a previous post that we would also have a table for storing our Usernames so when we need to change the username we can do that in one place and have it update all references to that username. Let's create a new table called Usernames.
Creating a Usernames table.
We'll create our table with the interface and I'll post the matching SQL code afterwards so both ways are presented again. Click on Create table and we should be presented with the 2 blank columns like before. We only need 2 columns for our Usernames table so we don't need to add any extra columns this time. Here's what these 2 columns should look like when filled out:
|Null:||NO (unchecked)||NO (unchecked)|
|AUTO_INCREMENT:||NO (unchecked)||NO (unchecked)|
You'll also notice we chose PRIMARY for the Index for our username column. A primary key is always unique in nature and since we want our usernames to also be unique that fits in nicely. Our uniqueid is marked UNIQUE to ensure that no duplicates of that exist because when the User wants to change their name we're going to be grabbing the unique identifier of the device they're playing from since that won't change. A downside to this is that if a user wanted to play on multiple devices they would need separate usernames because the device id would be different on each device. In this case you would probably want to set up a username/password login of some sort to allow users to play with the same username on multiple devices.
Here's the SQL code that matches what we just did through the interface above:
CREATE TABLE `myUsername_MyGameDatabase`.`Usernames` (
`username` VARCHAR( 15 ) NOT NULL ,
`uniqueid` VARCHAR( 45 ) NOT NULL ,
PRIMARY KEY ( `username` ) ,
UNIQUE KEY `uniqueid` ( `uniqueid` )
) ENGINE = INNODB DEFAULT CHARSET = utf8;
Now that we have a Usernames table, why do we also have a usernames column in our Arcade/Challenge tables? We can link the usernames column in our leaderboard tables to the usernames column in our Usernames table. Let's start by clicking on our Arcade table and navigating to the Structure tab. Underneath where our columns are listed we see a Relation view link, let's click it:
Here we can add a Foreign key constraint which allows us to link data from a different table. In the drop down next to the username column choose '_MyGameDataBase'.'Usernames'.'username' and set both ON DELETE and ON UPDATE to CASCADE.
What this does is link our username column to the username column in the Usernames table. Since we set ON UPDATE and ON DELETE to CASCADE, whenever we update our Usernames>username value it will cascade to all the other values that are linked to it. When we update our username it will also update the username attached to every score that user has in our tables. Open up our Challenge table and go through this same process of adding a Foreign key to link the username columns.
If you want to add a Foreign key using SQL you can use the following:
ALTER TABLE `Arcade` ADD FOREIGN KEY ( `username` ) REFERENCES `myUsername_MyGameDatabase`.`Usernames` (
) ON DELETE CASCADE ON UPDATE CASCADE ;
I think part 4 is going to be a simple run-through of how to INSERT/SELECT stuff using SQL just to familiarize everyone with it a little bit before we write a bunch of PHP scripts that use SQL in them. I'll also show how updating our username will also update all references to it.