Tech Tantra Thursday - Bill Eberle

Question 1
- Who is the all time top Cosmic Encounter® Online player? Which Alien is the Top Alien?

Our Cosmic Encounter® Online database does a lot more than help us keep track of our membership. The database is also integrated with the online Cosmic Encounter® game engine and custom Perl scripts which help keep track of what happens in every game with at least one member playing and which use that information about games to rank players and aliens.

The Cosmic Encounter® Online Leaderboard

The database behind the Cosmic website and game is described in more detail in the first tech tantra article. The tables which keep information for player and alien statistics and ranking are:
the 1. game_session and 2. game_session_detail tables (this year's tables and then archive tables with one of each table for each year, e.g. ar2003_game_session, ar2003_game_session_detail, etc. up to last year) and the 3. player_rank and 4. alien_rank tables which have monthly ranking information for all years. If it would help to see how the tables are connected, take a look at the schema diagram from that first article.

Player Rank is described in our online rules and on the Ranking Details page on this site.

Details about how we compute our player and alien statistics is also described in our online rules and on the Statistics Details page here.

For this article I'm focusing on the little application we call the Leaderboard that shows monthly, yearly (and daily) statistics. The Leaderboard is a Flash browser application which talks to Perl scripts to retrieve data for each kind of statistic we keep for 1) players who are members, 2) aliens when they are played by players, and 3) aliens when played by the game engine (alien bots). Describing how we calculate the player and alien statistics that show up when you click on a player or alien bot name is something we can do later in another article.

You can think of the Leaderboard as an interactive reporting tool which lets you see different months, years, and days. Behind the scenes in the ActionScript for the Flash application are date objects that keep track of how to move to the next month, year, or day, and “know” when to change to a different month or year.

Also behind the scenes are the Perl scripts which get information from the Flash application about “which” statistics to get (which numbers, e.g. 1-10, 11-20, etc.; and for which time period). And nested in the procedural logic of the Perl scripts are SQL statements which include variables sent by the Flash and/or manipulated by the Perl and these SQL statements do the actual work of getting the information. The Perl creates delimited strings with all of the information and returns it to the Flash which uses its (my) knowledge of how the delimiters are organized to unpack the information and populate display fields so the information shows up in the application.

But that's the easy part. All of the calculations needed to support ranking are done automatically, whenever a game starts, to set the stage, and then, when a game ends, to update the game session tables and adjust the ranking.