Question in short form: It seems from the followups that I should perhaps emphasize and simplify the core of my question. The core is this: other backup options for Android DBs seems to leave the risk that a restore could overwrite data currently in the database. Is this so, and is there a way to backup/restore without this risk?
.
Question in long form: Having looked through many of the (rather numerous) questions about backing up up an SQLite db on Android I have one question that I couldn't find an answer to.
All the other backup/restore discussions dealt with saving the db file to the SD (or, in How to backup/restore SQLite database on Android to Dropbox, to the cloud), and then restoring it when required. My concern is, wouldn't that restore overwrite the current DB?
I'm concerned about when the a user has a new install of an app they've been using for a short time (generating new data) and then want to import data from a previous backup of the app. With all the other backup/restore approaches it seems like restoring the old DB file would overwrite any new data in the current DB file. What I want instead is a backup option that, on restore, would add the data from the backup into the current DB to make it complete without overwriting anything else in it.
Do the other approaches do this? Or do they, as I suspect, overwrite in such a case?
If they do overwrite then my best backup option is probably to write out to a csv or xml file or something and I expected these backup discussions to be about easy ways to do that. Are there any processes build to speed that process and make it easy or do I have to do all that manually? If so, recommendations on the format to write to and why?
Similarly, does anyone know if the built in Google backup using BackupAgentHelper would have this same overwrite issue?
And finally, if I end up going through a data migration (similar to How to Restore SQLite Database from Backup after Core Data model has changed (lightweight migration)) at any point what should I do now (I'm still in DB design stage) to make such a potential future change easier vis-à-vis this backup process?
I think the problem of restoring old data without changing (or conflicting with) the ‘newer’ data is not very hard to solve in the scenario you describe. It seems that essentially you just want to ‘add’ the old data (records) to the new database with the assumption that the old data has no logical conflict with the newer data (that is, it is semantically OK to create new records for the old data). I believe that taking care of the primary key conflicts during restoration would be the most important issue to consider.
Let’s take two cases:
1: You are using the auto generation of primary key values feature of the database (e.g., using an AUTOINCREMENT column of a SQLite table).
Let’s assume that the ‘newer’ database records might have used primary key (ROWID) values of 1 to 10 before the restoration process is started. If you have ‘older’ database records with any of those primary key values (1 to 10), you have a problem only if you want to preserve those old primary key values. To avoid that problem, don’t retain the ‘old’ primary key value of an‘old’ record – after reading the record from the ‘old’ database, just save the values of other attributes (columns) of the ‘old’ record and let the database generate a new primary key value for this ‘restored’ record. Essentially, the ‘old’ record is saved with a new primary key value. If you are concerned about maintaining a chronological order of the records after this ‘restoration’ process, I suggest you also keep a timestamp column whose value does not get changed in the process.
2: You are using an alternate mechanism (UUID, Sequence Generators, etc.) for generating primary key values:
In this case, read the ‘old’ record and before saving it in the ‘newer’ database, replace the ‘old’ primary key value with a primary key value generated with the alternate mechanism – that would, by design, guarantee the uniqueness of the primary key value of the ‘restored’ record with respect to the pre-existing ‘newer’ records.
To simplify the programming effort for this ‘restoration’ process, especially if you are dealing with multiple tables, you may use an ORM like JDXA. One of the sample apps shipped with the SDK illustrates a similar technique for transferring ‘old’ data while upgrading a database to a newer version. JDXA also offers a convenient sequence generator mechanism to easily and efficiently create unique ids that you can assign to your objects before persisting them.