I am currently using a sqlite database file that I load up, access and modify during the process of the game running, which I realize is just not going to work for me overall. I've currently got to the point where I'm doing modifications to the database that's just not going to allow me to refresh this for a new-game, or really support the idea of being able to change the in-game database for updates etc, whilst still allowing the saved games to not be destoryed.
The idea I've thought of to get around this issue is to still load the database up from a file, but instead to copy the contents of this (or attach? I'm not sure) into an "in-memory" database which the game will use during execution. Then, when I need to save the game, I was thinking of doing a comparion between the in-memory database and the stored one on-disk, fashioning a shiny new "saved game" database which just stores the changes between the two. In this way, upon loading up a saved game, it would load up the "template" database from the disk as normal (into the "in-memory" database) and then basically "patch-in" the changes from the saved game into this too.
My question though, is what would be the easiest/best way to actually detect the differences between two databases like this so that I am able to create a save-file with only the changes? (and be able to load it again in the same way).
Just to note: I'm not the world's greatest expert with SQL, so please be patient in explaining anything etc. Also, I'm completely willing to any suggestions if you think I've gone crazy and this just isn't possible or a good idea to begin with!
Thanks in advance for any support on this!