Game State with sqlite + MySql

Question

I am building out my first android game, after some utility app development.

I want to be sure I am not moving into a direction that is not a best practice, so please (constructively) criticize my plan.

My game will have many NPCs that will be added often (>150) in increments of 10-15. Each will have their own stats associated with them and the user will have a unique interaction with each.

Is it possible to put all of the NPC character information into a MySql database and have my app download the new information when it understands that the servers contain new information (via C2DM). Including a link to a file I will host containing the png files for each NPC. This way when there are new NPCs to be added to the game, the user doesn't have to grab an update from the market/Google Play.

Furthermore is sqlite/MySql a preferable method to save the user's game state. I want to be able to save it locally so that it can be loaded without a network connection. And periodically updated on the server to allow the user to play the same game both on a phone and tablet for example.

If I sound like I'm on the right track with my plan, what opensource projects exists from which I can learn/analyse the methods they use to accomplish a similar task? I have been unable to find anything like this as of yet.

EDIT: by >250NPCs I mean that the game will start out with perhaps ten enemies. Each has their own stats (health, strength, magic etc). And since I plan on adding more than 150 types of enemies, I'd like the game to be able to update at run time rather than require an update with Google Play - since many users simply do not update their software on a timely basis, they will be missing out on the added value.

The reason I favor SQL is so that I can easily view my game characters and modify their stats/qualities on the fly should I see the game become unbalanced.

Answer 1

C2DM should be fine for updating the data.

As for saving data locally using sqlite, sure, that will work. I disagree with stephelton's answer because in practice relational databases are very fast and Android devices are likely to be writing to fast flash memory anyway, so concerns about disk seek speeds are likely to be unfounded.

Generally speaking though, most game developers do not save local game data into relational databases, usually preferring bespoke formats - but that's not a reason for you to avoid the database approach.

Answer 2

Not exactly sure what you're getting at... are you saving detailed game state in sql, or just initial data? And you say >150 -- 150 what?

Anyway, if you're talking about downloading game data (such as level layouts, enemy definitions, etc.) you're probably ok (but I personally still wouldn't go with sql for this, as it suffer longer loading times than a more packed format without all the sql overhead). If you are doing this, I'd batch your reads as much as possible and keep them in ram.

If you're talking about saving game state, such as current score, object hp, etc., you're probably asking for trouble.

Reading (which is primarily what you'd be doing if it's just game data you're talking about) comes with the same caveats, but the penalties are a bit lower. Waiting for a read from disk can definitely still stall your game.

In general, I'd say that if you are wanting to update a local sql database any more often than once every few seconds, you're likely to start running into problems. And the same if you're wanting to update a remote sql database any more often than once a minute or so.

There is a hefty bit of overhead that goes in to writing to a file, and it can very quite a bit. Writing local sql data includes this same overhead, but also includes its own overhead. Often a single sql save or read can require reading data from multiple locations on the hard drive (multiple seeks and multiple reads/writes) which compounds the problem.

When you're wanting to run a game at 30-60 fps, you have between 16 and 33 milliseconds to handle logic for a single frame and update the display. Writing to a file, even on a fast SSD, can easily stall you for many millis. Worse still, it's very unpredictable and you're very likely to see spikes that jump well past your allotment of 16-33 ms since you're contending for reads/writes to the hard drive with other processes running on the device.

I personally would stay away from sql for any game related tasks except maybe something like high scores / leaderboard type things.