[VXD Guy]

Basic idea is that the local sqlite database is a cached copy of a remote mysql database. All db transactions are threaded and all the plugins used the dbmirror interface to read/write settings so there's no concerns about other plugins accessing the same database incorrectly.

What's not shown is a lot of the grunt work for handling the difference between sqlite and mysql, connecting to the db's, and otherwise managing the database.

Initial connect by a new player:
  When a player connects to the server, the plugin looks at the timestamp of the local cached records for the new player (by steamid).
  If the data doesn't exist, it's created with blank fields, and the record is dated to be stale.
  If the data is stale, a read request is sent to the remote db to update the local db.  The local record is marked as "pending read"
  If the data is recent (not stale), nothing more is done during initial player connection

Initial connect remote read fails:
  Start a 60 timer to trigger Event_RemoteRead
  Global flag indicating remote db failure is set

Initial connect remote read succeeds:
  Update local db with remote db in formation
  Update record timestamp so record doesn't appear stale
  Clear "pending read" status for the updated record
  Global flag indicating remote db failure is cleared

Event_RemoteRead
  Select local "pending read" record where the player is connected to the server
  If none selected (i.e. player disconnected), select any "pending read" record
  If none selected, exit (technically an error, the routine shouldn't have been started if no pending reads exist)
  Send read request to remote db for the selected records

Event_RemoteRead fails:
  Start a 60 second timer to trigger Event_RemoteRead
  Global flag indicating remote db failure is set
  
Event_RemoteRead succeeds:
  Update local db with remote db info
  Update timestamp so records won't appear stale
  Clear "pending read" status for the updated record
  Global flag indicating remote db failure is cleared
  All records marked "pending read" should be cleared at this point otherwise re-trigger Event_RemoteRead
  
API Read_Setting (called by other plugins)
  Sanity Check:  If the data doesn't exist, somehow data wasn't created on player connection
  Read setting from local db

API Write_Setting (called by other plugins)
  Update local db with setting, set timestamp, mark record "pending write"
  Start 0.1 second timer to trigger Event_RemoteWrite

Event_RemoteWrite:
  Select local "pending write" record where the player is connected to the server
  If none selected (i.e. player disconnected), select any "pending write" record
  If none selected, exit (technically an error, the routine shouldn't have been started if no pending writes exist)
  Send write request to remote db for the selected records

Event_RemoteWrite fails:
  Start a 60 second timer to trigger Event_RemoteWrite
  Global flag indicating remote db failure is set

Event_RemoteWrite succeeds:
  Clear "pending write" status for the updated record
  Global flag indicating remote db failure is cleared
  Sanity check:  All records marked "pending write" should be cleared at this point, otherwise re-trigger Event_RemoteWrite

The Event_RemoteRead and Event_RemoteWrite work on the entire local db. That is, they don't work on one player at a time, the look for marked records and update everyone that needs to be updated.

To invalidate the cache copy you'd just set everyone's timestamp to null or delete the local db. A corrupt local db is a non-issue since you can erase the db and it'll be recreated and filled in from data on the remote db.

A remote db that's down for a day won't cause major issues since stale records aren't deleted, just marked for update when the player connects. Online admins can still set flags as they see fit and the info will replicate to the remote db when it comes up. There's a resync issue if a stale record is updated on the remote db and the local db while the connection is down; in case of conflict the local db wins (this will be configurable).

There is no correlation between timestamp (game server time) and remote db server time. The local game server time is used only to identify when a record is more than a week (default) old in order to mark it as stale. If you make most of the flag changes via game server, this works fine. If you make most of your changes on the remote db (i.e. from web admin console), you'd want to change the stale record time to something much shorter (24hrs would work well). You could also run a rcon command to mark all records stale which would force updates to replicate from the remote db to the local db.


Anyway, any comments, constructive criticism, or feedback on the proposed logic shown in the pseudocode above would be appreciated. It's much easier to fix problem in the design stage than after it's been implemented in code...