2

I'm trying to create some sort of backup & restore function in my app. Before that, I've been reading for a while to understand if it's possible to achieve, but I found out this question: Sqlite DB Android Backup/Restore

The only other way I could see to do it, would be to read the actual contents of the DB and generate a file containing the SQL which which it can be restored from, this is obviously a more complex and doesn't offer any advantages to justify this complexity.

This answer, I think, is the best way to accomplish that; not explorting the .db file, but exporting queries.

You know; when you export a SQL data from mysql, you get a file which contains all the queries that creates the structure and queries that fill the structure with data.

That's what I'm trying to mimic; generate a file which contains sql queries from a .db file.

Do you guys think it's possible, I mean, is there any builtin method to achieve that?

Otherwise, if its too hard to handle, how do you manage to avoid what this user (https://stackoverflow.com/a/10842043/1943607) is talking about?

So, I disabled WAL with "PRAGMA journal_mode = DELETE" and then I was able to view the database in the browser and able to restore it on my test device fine.

That previous part, I can't understand it. Is this a configuration you set to sqlite?

Thanks

Community
  • 1
  • 1
Reinherd
  • 5,476
  • 7
  • 51
  • 88
  • Did you read the link? – Reinherd Mar 19 '13 at 10:00
  • Don't wanna be rude, but I'm not supposed to justify the complexity. As the user says in the question I've linked before, just copying isnt a good way of backing up and restoring, because sqlite might not read the db even if its the same app, and sqlite version. So we've to look for a workaround, which exporting raw queries would be a nice try. – Reinherd Mar 19 '13 at 11:53

3 Answers3

1

I haven't actually tried this with sqlite, but with mysql you could do things like create "dumps" of your database. Those dumps contained exactly what you describe: a set of queries that, when executed together, recreate the database, including the contents.

Judging from the "sqlite3" documentation found at http://www.sqlite.org/sqlite.html (especially the "Converting An Entire Database To An ASCII Text File" section), you can do the same for sqlite. Since you can execute shell commands from a java application (using Runtime.getRuntime().exec() methods), and you are the "owner" (Linux user id) of the database, you should be able to run this "sqlite3 .dump" command even on a non-rooted device. I have never seen an Android device without the sqlite3 tool installed, so the command should always be available.

Moreover, since dump file is just a text file, you should be able to prepend any PRAGMA's to it that are required for compatibility (like the one you quoted).

I haven't tested any of this, but just wanted to think with you on this interesting topic.

baske
  • 1,316
  • 9
  • 16
  • looks cool. I will check it out today. Will update you if you're interested ;) – Reinherd Mar 19 '13 at 10:00
  • flagging this question as "favorite" ;-) – baske Mar 19 '13 at 12:16
  • I'm having some troubles while trying to execute sqlite3. `Process p = Runtime.getRuntime().exec(new String[] { "sqlite3"});` is throwing an Exception `ExceptionError running exec(). Command: [sqlite3] Working Directory: null Environment: null`. Any idea? – Reinherd Mar 19 '13 at 22:52
  • I think there is another .exec() method that allows you to not only specify the command, but also the directory and environment. Maybe another issue could be that the sqlite3 command cannot be found.. If that is the case, I am not sure how to proceed. But hopefully you can just get away with defining the dir/env: sqlite3 has always been on my PATH when opening 'adb shell' so I would expect (maybe wishful thinking here) it to be findable by .exec() calls as well. – baske Mar 20 '13 at 14:00
1

An sqlite database is just a file so you could copy the file but I think you may have problems with permissions in android preventing you from accessing the database.

A better solution IMO would be to sync your data to an external website. Using a combination of a custom sync adapter and the account manager with a website or web service that has a RESTfull api to receive and send the synced data would be the most reliable approach.

http://developer.android.com/training/id-auth/identify.html is a great introduction to setting up the account manager.

And for a custom sync adapter this is a great starting point. http://www.c99.org/2010/01/23/writing-an-android-sync-provider-part-1/ and http://www.c99.org/2010/01/23/writing-an-android-sync-provider-part-2/

And finally an explanation of how it all fits together https://sites.google.com/site/andsamples/concept-of-syncadapter-androidcontentabstractthreadedsyncadapter The above approach would enable a user to switch phones and retain data at the same time and the data would always be up to date (providing you sync at the appropriate times.

It seems like a lot of work as you will need to set up a web service but it is the BEST way to make sure data is kept safe and secure and can be restored and backed up at any point.

For a web service there are lots of options available to you including cloud services such as Google docs or writing your own website. Ruby on Rails is a great solution for developing your own site as you get a full RESTfull api out of the box and it;'s dead easy to secure/lock down a rails site to authorised users only with a couple of lines of code and with Heroku you can get free hosting.

As usual with Android development the simplest of requirements actually ends up being the most difficult to implement but where data safety is paramount then it's worth the effort to do it properly.

jamesc
  • 12,423
  • 15
  • 74
  • 113
  • I'm not sure if it's the best way at achieveing this. The other answer, looks "easier". Haven't tried yet, but I will check it out asap. I don't think we should build that huge system to control a simple database app. Maybe just extracting the queries, and generating a integrity code should do the trick. – Reinherd Mar 19 '13 at 11:22
  • @SergiCastellsaguéMillán I agree this is a complicated solution but it is secure, flexible and achievable. You have not stated why you need to back up and restore your data so I can not offer a judgement as to which is the best option for you just that this is the recommended android design pattern for doing something like this. Perhaps it is overkill. Only you can judge :) – jamesc Mar 19 '13 at 12:35
0

The question is too open to answer simply because the changes that may apply to the db file content are open and one can't guarantee a specific behavior .

On the positive side sqlite project is an open source and the format of the DB file is specified Here

After taking a look there, it seems very possible/not too complicated to parse any DB file looking for Data Only and write it/dump it to another functional db file.

I believe this is the fastest and cleanest solution to the issue in hand.

so to wrap up:

  • Copy DB file everytime you want to back it up.
  • When you want to restore create a new DB using Android APIs.
  • Parse the data from the backed up file and write them to the newly created DB.

P.S: regarding how to use

PRAGMA journal_mode = DELETE

Simply use db.exec("PRAGMA journal_mode = DELETE"); when creating the DB

Mr.Me
  • 9,192
  • 5
  • 39
  • 51