Oh hey, detecting and updating user presence is ridiculous. ( = pre-submission goals: update #2)

An annoying behind-the-scenes bug turned out, as I've learned after many hours of struggle today, to be far more frustrating and complicated than I could have imagined.

In my Users table in the database, I simply store a boolean, 'logged_in', by which I intend to record which players are currently using the app.  This does not mean whether they are logged into Facebook, since that status is likely unrelated to my app.  So, I define my logged_in boolean to mean whether the user is on either the index (home) page or the playgame.php page.

It's simple enough to ensure this boolean is marked true when the user loads either of these pages.  Detecting their leaving a page, however -- with the added requirements of updating all contingent information -- is unbelievably far more difficult.

Concerning the latter note, if a user leaves a game while it's in progress...

• They should be marked 'logged_in' = false, since if they're navigating to one of the app pages, that page will restore that boolean to true.
• Their 'current_game_id' and 'current_pid' variables must be set to null, to indicate they are no longer in that game.
• If they were the last remaining player in that game, all turns pertaining to that game as well as the game's entry itself must be cleared from the Turns and Games tables, respectively.
• If there are any other players left in that game, the 'num_players' value for that game must be set to the new correctly decremented value (and I don't want to just subtract 1, in case of race conditions, so I always want to check with how many players are still marked as involved in the game based on 'current_game_id' in the Users table).
• The other players in the game should be checking for whether their fellow players have left, and must be informed of such dropouts.

 My original solution to detecting a player leaving the game page was to register the window.onunload event to make an AJAX POST to a php script that would update all appropriate things (see the list above).  This worked well enough in Firefox; I checked the results in the database, and Firebug showed me that the POST was indeed going through, despite the fact that the browser was navigating to another page.  However, I ended up discovering that the window.onunload event, or perhaps the POST, wasn't going through fully in Safari.  This led me to realize I should try an entirely different approach that would not rely on a browser event that may not have solid implementation in all browsers...

Many thanks are due here to John for helping me form new ideas for how to make this work.  Without going into the painful details and resulting unfortunate spaghetti-fication of my code (err..), the basic solution architecture is in three components:

• The Users table now has a field called 'activity_time'.  Every 30 seconds, whether a user is on the home page or the gameplay page, an AJAX POST calls a php script to set this field to the MySQL CURRENT_TIMESTAMP.  So this is saying "user x is still here".
• While on the gameplay page, the same script additionally checks for (a) any users marked with that 'current_game_id' but whose 'activity_time' is more than 30 seconds old, and (b) any users with one of the 'fb_id's registered (in a Javascript object stored by the page) as involved in that game, but with 'current_game_id' set to null.  [Note: (a) condition = those who have left the app by going to another page outside of it or closing the browser tab/window, and the cron job has not yet cleared up their data; see next bullet...  (b) condition = those who moved back to the app home page, which clears up their game data immediately, or those who have been cleared up already by the cron job.]  The id's of these "dropout" users are sent back to the page; the display of players in the game is updated to remove the dropouts, and a message is given to the user to advise them to return to the home page and start another game...(see below *).
• There is now a cron job set up on the server to run every 5 minutes.  It calls another php script which retrieves all users whose activity time is more than 50 seconds old, removes any current game data they have, marks them as logged out, and either decreases the number of players in the game they had been in, or deletes that game entirely if it's now empty of players.

(*) Right now, there is no handling for updating the game state as it's stored in the ActionScript within the Flash app instance, nor does the page stop polling for the dropout players' turns.  As I see it, there are two options for where to go from here, regarding this issue:

• Don't bother fixing the AS game state.  Just redirect the user automatically back to the home page.  This would make sense anyway for games that only had 2 players to start with, since playing alone is not as much fun.
• Use the ExternalInterface class in AS to receive a notification from the page (via JS); update the game state accordingly; allow play to continue without the lost player(s).

Given how much trouble logout status has given me for many hours today, I'm inclined to go with the first option (or just let the user figure out that it doesn't work, and that they need to manually go back to the home page or leave the app).  It might turn out to be pretty quick to implement the second option, as long as there is still at least one player in the game, but I would rather turn my attention and remaining time towards a couple of the other goals on my pre-submission list, since I do think they are still important.

Finally, finally:

(I wonder how many Pandora hours I've used, out of my 40 hours given for the month, just from working today...)

Multi: -player, -game, -track support

Comprehensibility of the following bullet-point lists is questionable, unless you're me and therefore intimately familiar with this project's code...

Multiplayer support:

• Games support 1, 2, 3, or 4 players.  Having just one player right now makes no sense because there is no AI opponent.
• Gameplay page polls (Javascript AJAX calls) database to discover additional players joining the game.
• Player who initiated the game can decide to stop the polling and start the game; alternatively, the game will automatically be started if 4 players have joined (since that's the maximum it supports).
• Once the game is to be started, it's set to in_play = true in the database, and the flashvars string is sent to the AS (including: game_id, track_num, pid, numPlayers...yeah for variable-name inconsistency!)

Multi-game support:

• Can start a new game from the index page - game_id is sent through the querystring as -1 to indicate a new game should be started; PHP figures out the current highest game_id number and inserts a new game to the db with the next-highest id number.
• Index page displays a list of games that have been initiated but are not yet in play (i.e. are open for more players to join) - these have not yet been closed by their organizer and still have fewer than 4 players.  One need simply click to join; game_id is passed in the querystring and retrieved in the PHP via the global $_GET variable.
• The game_id is sent to AS through flashvars as mentioned above.

Multi-track support:

• I made a MovieClip object called 'tracks'.  Each of 4 keyframes will eventually display a different track I've drawn, but right now they all show the same one.
• When you are starting a new game from the index page, thumbnails of the 4 options for default tracks are shown, and you simply select one before you click the 'go' button to head to the playgame.php page.   The track number you've selected is submitted via a form POST.
• For new games, the playgame.php page pulls the selected track number from the form POST and inserts it as one of the fields when adding the new game to the database.
• For existing games (that you're joining), the page queries the database based on the game_id provided in the querystring and thereby pulls the track_num.
• The track number is then passed to the AS as one of the flashvars.  Since this number refers to the keyframe of the instance of the 'tracks' MovieClip, I simply pass it as a parameter to display the correct track: realtrack.gotoAndStop(track_num).
• NOTE: I've currently not implemented support for players drawing their own tracks... If I get to that, I'm not sure if I will be able to associate the track number to each game by just storing it as one of the attributes in the Games table.  My original database design includes a separate table for tracks, and I imagine storing custom tracks somehow would require this table, BUT, right now, with only my 4 possible default tracks it works just fine to have an id number (1 - 4) as an attribute for each game.

Well this post feels a bit disorganized and haphazard... I will try to post a screen-capture video of all this stuff soon.

Quick note on the database engine

I met up with Jeff Nimeroff briefly because I thought I was about ready to set up the actual database tables and start using those in place of the test tables with which I'd been working.  I'd forgotten which engine he had suggested to use, and why, from a previous discussion.

Though there are a number of engines that MySQL supports, Jeff explained that the two main engines used are MyISAM and InnoDB.  The key difference between them is that MyISAM locks entire tables during queries, whereas InnoDB only locks at the level of a single row.  The functions of my game require running various polling functions that query the database so that players check for and receive updates on each others' actions.  Therefore it probably does not make sense to lock entire tables when, for example, up to 4 client instances of the game are making calls to the same table at once.  Thus he suggested I go with the InnoDB engine.

However, because this is a turn-based game, for the most part only one client can be making an update or insertion query at a time.  Therefore it's not totally critical to avoid table-locking.

Finally, he informed me that I can switch the database engine at any time--I'd thought it was set in stone, which is why I'd wanted to check in with him about which to use prior to creating the "real" database tables.

Multiplayer WORKS! (separate instances updating through the database)

Joe gave me permission to make the blog posts short and sweet.  I'm going to try to keep this one more at an overview level.

The goal I set out to accomplish was to separately run multiple instances (i.e. one per player) of the swf with each one keeping itself updated on the actions made (turns taken) by the others.

How it works:

• In the HTML for the embed/object elements, the FlashVars parameter provides (a) the total number of players in the game, and (b) with which player number that particular swf instance is to be associated.
• As the "whole document" class (TraceRace) is a DisplayObject, its Actionscript constructor uses its LoaderInfo instance to grab the values of the FlashVars.
• When it's a player's turn, that swf will both show and allow clicking on the options for the next turn.  Upon the click of an option, the URLVariables/URLLoader/etc. classes send off this data to a PHP handler script, which sends a MySQL query to input a row in the database table.
• When it's not a player's turn, that swf will show the other player's turn options, but they are not clickable (so that you can't go taking someone else's turn).  The swf uses the same URL[whatever] classes to poll for other players' turns showing up in the database: every two seconds, a POST is made which is handled by a PHP script.  If the script returns the other player's turn data (since it has been entered in the database), it stops polling, and this swf updates the other player's move visually and in its own state (variables).  If the other player's turn has not been entered into the database yet, the script just returns a boolean indicating such, and it initiates the next polling request.

Problems:

• After I thought I'd figured them out last time, turned out I still was having issues with booleans.   I'm now sending booleans through PHP echoes as 1 (true) or 0 (false), because the AS can easily convert them to actual booleans, whereas the word strings "true" and "false" are not so easily converted.
• During debugging I ended up trying to be pretty precise with types, even though I'm not always sure if that was causing a bug or not.  For example, I cast variables parsed from PHP-echoed name/value strings to, say, a Number or a uint.  I also cast a piece to a piece when I pull it out of an array, since I don't think arrays are designated to hold a certain single type.
• It looks like AS isn't happy with my passing an argument as 'null' (since I was going to retrieve the necessary object a different way, once inside the function).  Then I tried using two parameters with default values, but since you can only leave out arguments at the end of the list, I can't input the 2nd and drop the 3rd in one call while doing the reverse in another call.  Eventually I just wrote a getter function so I could access the object before the problematic call.
• One of the weirdest problems I've had yet, I actually managed to create myself.  By initializing an Array with a specific length, and subsequently pushing more elements into it, I ended up with empty spaces prior to the pushed elements.  This actually makes pretty decent sense.  But since previously I hadn't initialized the array with a length, the pushed elements just added/filled spots from the beginning.  I finally realized what was going on and now add the elements by accessing the array slots via indices.

Now for the cool part.  I took a screencast video (thanks Screencast-O-Matic!!!) to show how this actually all operates in my testing setup.

Successful push of variables in AS =[PHP]=> database, plus AS callback

So the other night I set out to implement the plan I outlined before to figure out how to push data (i.e. each turn's information) from Actionscript variables to the database.  Here are the components I wrote/set up:

• Database:  set up a test table called TestTurns with 3 fields - p_id (piece's ID number), xpos, and ypos.
• Actionscript:  added a storeTurn() method to the 'piece' class, which makes use of the URLVariables, URLRequest, and URLLoader classes to structure and send an HTTP POST request, containing a piece's ID number, x-position, and y-position, to the PHP file.  Also wrote a callback function - which gets attached to the Loader's COMPLETE event - that spits the echoed data from the PHP script (provided in name/value query-string format) into a URLVariables object and "logs" some information into a dynamic text box currently sitting in the middle of the race track.  (This is a temporary debugging mechanism, obviously.)
• PHP:  wrote a small script in a file which grabs the 3 POSTed variables, runs an insert query on the database, and echoes back the information it received and whether the query was successful.

These components seemed straightforward enough, but I did run into some issues along the way.

• First, I was trying to test the POST to the PHP script by running the Actionscript locally.  It was able to pull the file, but in the AS callback function I was getting these strange strings as supposed value counterparts of the names that the URLVariables constructor was able to parse out.  I rearranged and tested things for some time until the "well duh" moment: I was expecting PHP to run while sitting on my machine.  Oh, wait.  :)  (To test locally, I wrote a simple text file containing a name/value string, directed the HTTP request to that file, and the URLVariables constructor parsed it just fine.)  After this, I uploaded the files so I could test it for real (i.e. with the PHP running).
• I knew that for an insert query, the mysql_query function would return a true or false based on whether the query was successful or an error occurred.  However, I didn't know the details on the representation of that boolean, and how it would get converted when echoed by the PHP and then parsed by the URLVariables constructor in the Actionscript.  From the output I was getting dumped into the "log" it was appearing as a 1 or 0 instead of the string "true" or "false", so in order that the URLVariables variable for 'success' would actually have a value of true or false (that could then be used in a conditional), I just used the ternary conditional operator (?:) to convert the 1/0 value ahead of time in the PHP echo string.
• Also from the output log I realized that since I was calling (in the handler PHP file) require_once for the db_connect file, whatever was echoed in that file was showing up amidst what the handler file was echoing.  Naturally this was screwing up the name/value query string I was trying to create so that the URLVariables constructor (in AS) could read it.  I was looking through the PHP documentation for the include/require functions to see if there was a way to suppress the output of the included file, but didn't find anything along those lines.  As an alternative for echoing messages of success or error in db_connect.php, I created a variable to store a boolean for connection success.  Then, when I require db_connect.php in the handler file, I can store that boolean (i.e. $connect_success = require_once "db_connect.php";) to discover and deal with connection errors.

Once I had figured out these problems, though, it actually worked...!  I took screenshots both of the turn data in the test table and the output as it showed up in Flash:

Meeting with Dr. Nimeroff: Database structure and linking multiple players

I met with my adviser, Dr. Nimeroff, for a bit the other day and we mainly discussed two related points.  Since it's been awhile since I've tried to design a database, we first talked over my ER diagram.  He didn't see any major potential issues with it right off the bat, so that was a good start.  We focused on the 'player_group' relation between the 'games' and 'users' entities.  I had not been sure whether it would be necessary to store player state information, like the x- and y-position, for a running game--in my newness to Flash/Actionscript, I'd been thinking something like that could be stored in Actionscript variables.  Dr. Nimeroff explained, though, that the Flash swf and associated Actionscript are run locally on each user's browser, so when values and visual elements change for one player, they aren't automatically updated for other players.  The key here is that whatever information is needed to update players' states on each others' "copies" of the swf and of variable values in the code must be saved into the database (since that, of course, is stored on the server) and then retrieved by calls from the other players' browsers to receive the new state.  Thus, it was a good thing I included such state information in my ER design.

Along these lines, I then asked about an issue that has been a "hole" in my mental plan of implementation since the beginning: how do other players' local machines find out that an update has been made to the database and take appropriate action to update their own copy of the game state in the code and swf?  I do want the game to be real-time, though it is turn-based, which makes it far easier than having to support multiple players doing things at the same time.  The only thing I could think of was polling; the Actionscript would just ping the database every fixed interval of time to look for a change and pull the new info if necessary.  I had also wondered whether the reverse idea would be feasible: could the database notify the game instances when an update was ready?  Dr. Nimeroff said that the former (games-poll-database) method was definitely the way to go about it.  Since the game is turn-based, it's not creating a constant stream of updates that need to be passed around; thus, a polling request every couple of seconds or so would not be a strain on the server.  Plus, the amount of data to be transferred per request is very little.  Finally, this is much simpler than the database trying to keep track of which instance to notify, where to send the notification, and the like.  This conclusion was very exciting for me, as I am no longer in the dark about a doable and sensible approach the implementation of multiplayer support.  And I think I might even know at a high level how to go about it.  Yay!

Database planning - ER diagram and query thoughts

ER Diagram
So, with some refreshing to help out my memory of CIS 330 material, I have constructed a first-draft entity-relationship (ER) diagram for the database.  I'm the slightest bit iffy on whether I've indicated the participation and many-to-one constraints via the correct notation; see the legend (added mainly for my own reference) for my understanding of it.  I also can't remember if foreign keys should be indicated--I seem to recall that they should be given a dashed underline, but I left that out because I'm not sure.  At any rate, I've unconventionally included a bunch of extra notes (including mentions of the foreign keys) to provide further explanation and clarification.

Additional iterations and modifications of this setup are of course likely to occur.  I will try to post updated diagrams as they do.  (Maybe I'll make this into an Illustrator file, but for now, it was easiest for me to work it out in my head and put it directly onto paper.)

Please please click on the image to see it in full size... it's quite difficult to read at the embedded size.

Queries
While I'm thinking about it, here's a very preliminary and unfinished list of SQL queries I'll need to figure out...  (Capitalized terms are entity names; terms in single quotes are attribute names.  I don't know if that's correct syntax, but it's simple notation for myself and readers.)

• Get all Users currently logged in but not playing a game
• Get all default Tracks
• Get all Tracks created by a User
• Get all Tracks... [should I allow access to tracks created by other users?]
• Create a new Track - store the 'filepath', associate with a 'creator' = User.fb_id
• Create a new Game - associate a Track, a User.fb_id as organizer, and 'in_play' = false
• Add Users (unconfirmed) to a game - 'joined' = false
• Confirm a User's participation in a game - set 'joined' = true
• Set (update) a player's position on the game board grid - change 'xpos' and 'ypos' for given User.fb_id
• Get the player whose turn it is now/next
• Delete a Game when it's finished - this will include removal from Game table and all entries with that Game.id in player_group