0

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?

Community
  • 1
  • 1
T. Shaffner
  • 359
  • 1
  • 5
  • 22
  • [restore](http://www.merriam-webster.com/dictionary/restore) Did you mean [merge](http://www.merriam-webster.com/dictionary/merge) ? ... then option is *or do I have to do all that manually* because it is always involve some kind of logic which should be apply (fx: add only non existing rows from backup, override newer, etc.) – Selvin Feb 05 '16 at 15:07
  • I suppose a merge yes, but a merge from a backup. Writing out to XML or some other file is a lot more complicated so it would be nice if the copying of the DB file did a merge on restore; I'm just not sure I believe it would. – T. Shaffner Feb 05 '16 at 15:15
  • A merge is not a restore. That the merge comes from a backup makes no difference. The details of the merging depend on your data, and how it is structured. – CL. Feb 05 '16 at 15:16
  • In which case, merge probably isn't what I'm looking for and restore was the right word choice in the first place. – T. Shaffner Feb 05 '16 at 15:20
  • @J. Natael, Some questions and comments to understand the problem better. 1- Since you are concerned about the new data being overwritten by the old backed up data, it seems that you want to 'add' the backed up data to the new data (without any modifications to the new data). 2- Based on the way the data (records) is created by your app, you may need to take care of any conflicts between the primary key values of the newer records and the older records when 'adding' old records to the current database. Just pointing out; this may not be an issue in your case. 3- How many tables are involved? – Damodar Periwal Feb 05 '16 at 19:40
  • @Damodar Periwal, 1: You're correct; I'd like to add the backed up data in. 2: Agreed; I expect that to be a somewhat finnicky process which is partly why I asked the question; love any insight anyone has. 3: The number of tables will be dynamic. I'm open to alternative solutions too if there's an approach that doesn't have the primary key issues. The broader point to me is that all the other DB backup discussions seem to have a hole in them, which is that if data already exists in the database on restore it will be overwritten or lost. Any approach that avoids that issue would work for me. – T. Shaffner Feb 10 '16 at 17:27

2 Answers2

0

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.

Damodar Periwal
  • 399
  • 1
  • 2
  • 13
  • Thanks for taking the time to follow up with such depth! I think this strays from the point though. If I back up to XML or something of the like then during a restore the two approaches you discuss come in to play; if I have to restore from XML it's certainly doable, just more complicated. My question though is, is there a simpler way to backup/restore my DB? If not then I need to generate a bunch of code to write my DB to XML and read it back in from it. – T. Shaffner Feb 11 '16 at 13:55
  • You are welcome. The approach I described can be used to restore data from a pre-existing database (a SQLite db file) to a new database (another SQLite db file) without resorting to any XML. JDXA ORM can simplify that process. – Damodar Periwal Feb 11 '16 at 19:11
  • Hmm, in that case I think I misunderstood your answer and this is a better approach than any other I've found. I'm marking this as the answer. If I research JDXA ORM will I find all the information I need on restoring from a pre-existing SQLITE db file or is there anything else I should research too? – T. Shaffner Feb 12 '16 at 03:26
  • Please check the sample apps JDXAndroidDBUpgradeV1Example and JDXAndroidDBUpgradeV2Example shipped with the SDK for an example of how data transfer can be achieved from an old database to a new database. We can take it up offline if you need more information or help. – Damodar Periwal Feb 12 '16 at 04:59
  • The JDXA ORM user manual, shipped with the SDK, has a detailed section on data migration strategy during database version upgrade. You may download the SDK by visiting this [link](http://softwaretree.com/v1/products/jdxa/download-jdxa.php). – Damodar Periwal Feb 15 '16 at 05:43
0

After continued research I'm increasingly concluding there isn't a better approach to this problem. For a simple backup you can simply copy the database file as many of the other threads discuss and leave the overwrite on restore issue unaddressed (and also risk compatibility problems evidently: https://stackoverflow.com/a/10842043/3108762). For a truly robust backup you just have to write your entire DB out to XML and read it back in with all the work that entails.

The only better alternative I've found is mentioned in https://stackoverflow.com/a/34477622/3108762 which refers to the new Android feature Configuring Auto Backup for Apps, but only works in Android 6.0 and up. I will be testing that, but if it doesn't work, and for anything below Marshmallow, it seems writing my entire DB out to XML and then reading it back in again is the only truly robust approach. Not entirely a surprise but good to know.

Community
  • 1
  • 1
T. Shaffner
  • 359
  • 1
  • 5
  • 22
  • As I mentioned in my earlier responses on this thread, you don't need to resort to export database to XML and import XML into database. You can take care of overwrite on restore issues with the approach I have outlined above. Even if you want to use XML for the database compatibility issue, you may be able to handle the primary key conflict issues with the ideas mentioned in my posting. – Damodar Periwal Feb 12 '16 at 00:05