How can I save and load an adventure game?

Question

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.
    if(cachedRect.contains(range)){
        return retrieveDataFromCached(cachedData, range);
    }

    var scaledRange = scale(range, 10);
    var data = retrieveFromSqlite(scaledRange);
    var inRange = [];
    for(var d : data){
        if(range.contains(d)){
            inRange.append(d);
        }else{
            cached.append(d);  
        }
    }
    cachedRange.append(range);
    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.

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.}