Tech Tantra Thursday - Bill Eberle

We have a Perl script that the Java game engine calls to start a game. That script has a lot of work to do (it's a bit over 3000 lines of code). In addition to making sure that players are assigned aliens which they haven't played recently and which they are eligible to play (the details about which I could spend a whole other article describing) , the script gets information about each real player's current M-ric total and sends that as part of the information packet for each player for the game engine so the game engine can create the M-rics pool. That part (the SQL to get the M-rics and Perl code to add it to the XML data packet for the game engine) is fairly simple, but it's important to remember that the fact that everything is available in the player and alien rank tables is what makes it simple.

The interesting stuff happens when a game ends and the game engine calls the Perl script with information about what happened in the game so that the Perl script can update the game_session, game_session_detail, player_rank, and alien_rank tables.

What has to happen when a game ends? What information is needed and what problems have to be solved to do what has to be done?

Tasks (goals): 1. correctly update the player and alien rank tables (which is a bit trickier than you might think), 2. correctly update the game_session table with the time the game ended (simple table update), 3. update the game_session_detail table with the information sent by the game engine about how things looked at the end of the game (also simple table updates).

The end of game Perl script (4,800+ lines of code) runs whenever a member game ends and receives information from the game engine about a) player type (member, free, guest, or bot), b) each member player's identity, c) assigned alien, d) win code, and e) number of foreign and f) home colonies and also, the g) game session id so it knows which game records to update, and the h) total M-rics at stake in the game.

The problems that have to be solved involve the possible multi-record update scenarios in the ranking tables.

What is interesting is that, for each player (or alien) record that has to be correctly ranked, sometimes other player's (or other alien's) records also have to be changed.

Without giving away our custom solution, I'll define some of the possible cases.

CASE 1: rank changed and neither the original or the new M-rics (M-rics won in the game) number is tied with another rank record.

CASE 2: rank changed and only the new M-rics number is tied with another rank record.

CASE 3: rank changed and only the original M-rics number is tied with another rank record.

CASE 4: rank changed and both M-rics numbers (original and new) are tied with other rank records.

And one final note; we depend on the beauty (and power) of SQL which can update one record or many records at the same time with one SQL statement.