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, 2016 at 17:06
  • \$\begingroup\$ It has no primary key. Should I use an auto increased ID to index them? @Philipp \$\endgroup\$
    – Nya
    Feb 1, 2016 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, 2016 at 9:37
  • \$\begingroup\$ Yes you should have a primary key that is auto-increased in this scenario. \$\endgroup\$ Mar 13, 2016 at 20:50

1 Answer 1


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, 2016 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\$ Mar 13, 2016 at 20:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .