I'm using cocos2dx and sqlite to store datum. The table looks like this: x INT, y INT, className TEXT, properties TEXT. I use json to serialize terrains and sprites. But I don't know how to synchronise it. Below is how I'm doing:

//1: create a interface takes an range and returns data in it. 
Data getData(Rect range);

//both are global objects.
cachedRange = [];
cachedData = [];

//2: while reading from sqlite, I scale the range and cached other data.
Data getData(Rect range){
    //if it has been cached.
        return retrieveDataFromCached(cachedData, range);

    var scaledRange = scale(range, 10);
    var data = retrieveFromSqlite(scaledRange);
    var inRange = [];
    for(var d : data){
    return inRange;

However, these data will change. For example, some mobs are instantiated and then killed, and some are moved. When should I delete original records of the table.

  • \$\begingroup\$ What's the primary key of that table? \$\endgroup\$ – Philipp Jan 31 '16 at 17:06
  • \$\begingroup\$ It has no primary key. Should I use an auto increased ID to index them? @Philipp \$\endgroup\$ – Nya Feb 1 '16 at 2:46
  • 2
    \$\begingroup\$ "it has no primary key." - it always amazes me that there are relational databases which allow this travesty. May the ghost of Ted Codd haunt them in their sleep. \$\endgroup\$ – Philipp Feb 1 '16 at 9:37
  • \$\begingroup\$ Yes you should have a primary key that is auto-increased in this scenario. \$\endgroup\$ – Johnathon Sullinger Mar 13 '16 at 20:50

Give each persistent object an unique id value (an unsigned int should suffice unless the number of objects can go into billions). Use that value as the primary key* of your database table. This ensures that the database will never have two entries for the same object. I would recommend you to not use auto increment for the id and instead assign that value by the game. That way the game doesn't need to ask the database which id value it assigned to the object.

When you update or remove a game-object, always do so by referring to it by its id (DELETE/UPDATE table ... WHERE id = ?). That way you make sure that no other game-object is modified.

* for those which do not often work with relational databases: the primary key uniquely identifies each row. There can only be one row per unique primary key value and finding database rows by primary key is usually very fast because it is automatically indexed.

  • \$\begingroup\$ Thanks. I find out sqlite provides rowid which is a bit faster than an auto increament sequence. \$\endgroup\$ – Nya Feb 1 '16 at 11:47
  • \$\begingroup\$ If this is a single player game then having the game auto-increment it is fine. Personally I always let the database be responsible for maintaining the integrity of its data and not the apps pushing things into it; just personal preference. If this is a networked game then having the game generate the id could cause problems. You don't know if it is really unique by the time it gets to the server. \$\endgroup\$ – Johnathon Sullinger Mar 13 '16 at 20:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.