Screaming Penguin

UpdateThis article describes one way to backup a complete Android database to an external file. Doing this is not an ideal way to backup data, but can be useful in some situations. A better general approach is to import or export data to another format (CSV, etc) and then parse it into your database. This allows you to control the DB schema, and not have to worry about older DB restores with a newer app version, and other related problems. Also, as of 2.2, the Android Data Backup service is available and highly recommended.End Update

Another useful Android programming tip is to either allow users to selectively backup any database your application uses, or just do it for them in the background (at some interval).

Why is this important? Well, if the user gets a new phone (for whatever reason, lost it, upgraded, chucked it out the window before going kayaking) one of the first things they will do is hit the Android Market again and try to re-download the applications they had (the Market saves this info and makes it easy, well, generally, there are a few quirks, but that is beyond the scope here). Also, they might just want to uninstall your app at some point and later come back to it on an existing phone. Without a database backup they might re-acquire your application but they will be pissed (rightly so) if all the data is missing.

Some applications handle this very well (notably Evan Charlton's Mileage, which is a great app BTW, has always upgraded for me flawlessly -- I don't know if it uses a similar method to what I advocate here or not, but it works very well however it does it), and others don't (Google Listen, I am looking at you ;)).

To that end in this article we are going to modify the AndroidExamples application we have worked on before here at TotSP to include a new Activity that allows users to backup the database. This is really just to demonstrate a few ways to do this rather than an exhaustive example, but it should get you started if you are interested in this area.

Our new version of AndroidExamples (complete code via the link) will have a Menu on the Main activity that lets users go to a new ManageData activity -- as seen in the screen shot below:

Our new ManageData activity, which allows us to backup our database file, AND hands off to another new class in case we want to backup our database as XML, is shown below:

public class ManageData extends Activity {

   private MyApplication application;

   private Button exportDbToSdButton;
   private Button exportDbXmlToSdButton;

   @Override
   public void onCreate(final Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);

      this.application = (MyApplication) this.getApplication();

      this.setContentView(R.layout.managedata);

      this.exportDbToSdButton = (Button) this.findViewById(R.id.exportdbtosdbutton);
      this.exportDbToSdButton.setOnClickListener(new OnClickListener() {
         public void onClick(final View v) {
            if (ManageData.this.isExternalStorageAvail()) {
               new ExportDatabaseFileTask().execute();
            } else {
               Toast.makeText(ManageData.this, "External storage is not available, unable to export data.",
                        Toast.LENGTH_SHORT).show();
            }
         }
      });

      this.exportDbXmlToSdButton = (Button) this.findViewById(R.id.exportdbxmltosdbutton);
      this.exportDbXmlToSdButton.setOnClickListener(new OnClickListener() {
         public void onClick(final View v) {
            if (ManageData.this.isExternalStorageAvail()) {
               new ExportDataAsXmlTask().execute("exampledb", "exampledata");
            } else {
               Toast.makeText(ManageData.this, "External storage is not available, unable to export data.",
                        Toast.LENGTH_SHORT).show();
            }
         }
      });
   }

   private boolean isExternalStorageAvail() {
      return Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED);
   }

   private class ExportDatabaseFileTask extends AsyncTask<String, Void, Boolean> {
      private final ProgressDialog dialog = new ProgressDialog(ManageData.this);

      // can use UI thread here
      protected void onPreExecute() {
         this.dialog.setMessage("Exporting database...");
         this.dialog.show();
      }

      // automatically done on worker thread (separate from UI thread)
      protected Boolean doInBackground(final String... args) {

         File dbFile =
                  new File(Environment.getDataDirectory() + "/data/com.totsp.androidexamples/databases/example.db");

         File exportDir = new File(Environment.getExternalStorageDirectory(), "exampledata");
         if (!exportDir.exists()) {
            exportDir.mkdirs();
         }
         File file = new File(exportDir, dbFile.getName());

         try {
            file.createNewFile();
            this.copyFile(dbFile, file);
            return true;
         } catch (IOException e) {
            Log.e(MyApplication.APP_NAME, e.getMessage(), e);
            return false;
         }
      }

      // can use UI thread here
      protected void onPostExecute(final Boolean success) {
         if (this.dialog.isShowing()) {
            this.dialog.dismiss();
         }
         if (success) {
            Toast.makeText(ManageData.this, "Export successful!", Toast.LENGTH_SHORT).show();
         } else {
            Toast.makeText(ManageData.this, "Export failed", Toast.LENGTH_SHORT).show();
         }
      }

      void copyFile(File src, File dst) throws IOException {
         FileChannel inChannel = new FileInputStream(src).getChannel();
         FileChannel outChannel = new FileOutputStream(dst).getChannel();
         try {
            inChannel.transferTo(0, inChannel.size(), outChannel);
         } finally {
            if (inChannel != null)
               inChannel.close();
            if (outChannel != null)
               outChannel.close();
         }
      }

   }

   private class ExportDataAsXmlTask extends AsyncTask<String, Void, String> {
      private final ProgressDialog dialog = new ProgressDialog(ManageData.this);

      // can use UI thread here
      protected void onPreExecute() {
         this.dialog.setMessage("Exporting database as XML...");
         this.dialog.show();
      }

      // automatically done on worker thread (separate from UI thread)
      protected String doInBackground(final String... args) {
         DataXmlExporter dm = new DataXmlExporter(ManageData.this.application.getDataHelper().getDb());
         try {
            String dbName = args[0];
            String exportFileName = args[1];
            dm.export(dbName, exportFileName);
         } catch (IOException e) {
            Log.e(MyApplication.APP_NAME, e.getMessage(), e);
            return e.getMessage();
         }
         return null;
      }

      // can use UI thread here
      protected void onPostExecute(final String errMsg) {
         if (this.dialog.isShowing()) {
            this.dialog.dismiss();
         }
         if (errMsg == null) {
            Toast.makeText(ManageData.this, "Export successful!", Toast.LENGTH_SHORT).show();
         } else {
            Toast.makeText(ManageData.this, "Export failed - " + errMsg, Toast.LENGTH_SHORT).show();
         }
      }
   }
}

Basically this activity is really boring in terms of UI, just two Buttons.

If the user selects the first button the SQLite database is just copied to the SD card. This is done via the ExportDatabaseFileTask inner class. This is another use of AsyncTask, the handy background task helper class that we saw introduced in the last tutorial of this series.

Within this task class we see that we obtain a reference to the /data/data/PACKAGE_NAME/databases/DB_NAME file starting from the Environment.getDataDirectory() method. Once we have a handle to the file we use a simple internal method to copy it to a new file located on the SD card. This is important, an SQLite database *is* JUST a file on the filesystem, so you don't need anything fancy to back it up -- just copy the file to the SD card.

The second button on our activity is a little more complicated. This allows the user to backup the data as an XML file using another AsyncTask, ExportDataAsXmlTask. As opposed to a regular simple file backup, an XML representation might be useful in some other contexts (such as later using XSLT to transform the data into another format for import to other systems). The XML version CAN also be used as a backup/restore method, but again, that's really more complicated than necessary. The DataXmlExporter class our task uses to covert our database into an XML representation is shown below:

public class DataXmlExporter {

   private static final String DATASUBDIRECTORY = "exampledata";

   private SQLiteDatabase db;
   private XmlBuilder xmlBuilder;

   public DataXmlExporter(SQLiteDatabase db) {
      this.db = db;
   }

   public void export(String dbName, String exportFileNamePrefix) throws IOException {
      Log.i(MyApplication.APP_NAME, "exporting database - " + dbName + " exportFileNamePrefix=" + exportFileNamePrefix);

      this.xmlBuilder = new XmlBuilder();
      this.xmlBuilder.start(dbName);

      // get the tables
      String sql = "select * from sqlite_master";
      Cursor c = this.db.rawQuery(sql, new String[0]);
      Log.d(MyApplication.APP_NAME, "select * from sqlite_master, cur size " + c.getCount());
      if (c.moveToFirst()) {
         do {
            String tableName = c.getString(c.getColumnIndex("name"));
            Log.d(MyApplication.APP_NAME, "table name " + tableName);

            // skip metadata, sequence, and uidx (unique indexes)
            if (!tableName.equals("android_metadata") && !tableName.equals("sqlite_sequence")
                     && !tableName.startsWith("uidx")) {
               this.exportTable(tableName);
            }
         } while (c.moveToNext());
      }
      String xmlString = this.xmlBuilder.end();
      this.writeToFile(xmlString, exportFileNamePrefix + ".xml");
      Log.i(MyApplication.APP_NAME, "exporting database complete");
   }

   private void exportTable(final String tableName) throws IOException {
      Log.d(MyApplication.APP_NAME, "exporting table - " + tableName);
      this.xmlBuilder.openTable(tableName);
      String sql = "select * from " + tableName;
      Cursor c = this.db.rawQuery(sql, new String[0]);
      if (c.moveToFirst()) {
         int cols = c.getColumnCount();
         do {
            this.xmlBuilder.openRow();
            for (int i = 0; i < cols; i++) {
               this.xmlBuilder.addColumn(c.getColumnName(i), c.getString(i));
            }
            this.xmlBuilder.closeRow();
         } while (c.moveToNext());
      }
      c.close();
      this.xmlBuilder.closeTable();
   }

   private void writeToFile(String xmlString, String exportFileName) throws IOException {
      File dir = new File(Environment.getExternalStorageDirectory(), DATASUBDIRECTORY);
      if (!dir.exists()) {
         dir.mkdirs();
      }
      File file = new File(dir, exportFileName);
      file.createNewFile();

      ByteBuffer buff = ByteBuffer.wrap(xmlString.getBytes());
      FileChannel channel = new FileOutputStream(file).getChannel();
      try {
         channel.write(buff);
      } finally {
         if (channel != null)
            channel.close();
      }
   }

   class XmlBuilder {
      private static final String OPEN_XML_STANZA = "<?xml version=\"1.0\" encoding=\"utf-8\"?>";
      private static final String CLOSE_WITH_TICK = "'>";
      private static final String DB_OPEN = "<database name='";
      private static final String DB_CLOSE = "</database>";
      private static final String TABLE_OPEN = "<table name='";
      private static final String TABLE_CLOSE = "</table>";
      private static final String ROW_OPEN = "<row>";
      private static final String ROW_CLOSE = "</row>";
      private static final String COL_OPEN = "<col name='";
      private static final String COL_CLOSE = "</col>";

      private final StringBuilder sb;

      public XmlBuilder() throws IOException {
         this.sb = new StringBuilder();
      }

      void start(String dbName) {
         this.sb.append(OPEN_XML_STANZA);
         this.sb.append(DB_OPEN + dbName + CLOSE_WITH_TICK);
      }

      String end() throws IOException {
         this.sb.append(DB_CLOSE);
         return this.sb.toString();
      }

      void openTable(String tableName) {
         this.sb.append(TABLE_OPEN + tableName + CLOSE_WITH_TICK);
      }

      void closeTable() {
         this.sb.append(TABLE_CLOSE);
      }

      void openRow() {
         this.sb.append(ROW_OPEN);
      }

      void closeRow() {
         this.sb.append(ROW_CLOSE);
      }

      void addColumn(final String name, final String val) throws IOException {
         this.sb.append(COL_OPEN + name + CLOSE_WITH_TICK + val + COL_CLOSE);
      }
   }
}

The inspiration for this approach came from this blog article: http://mgmblog.com/2009/02/06/export-an-android-sqlite-db-to-an-xml-file.... Our code is significantly different from the original, but you can see the core of the idea there -- thanks go to mmaitlen for that one.

For both of these approaches you may have also noticed that we first check whether external storage is available with the isExternalStorageAvailable method on the ManageData activity. This is a useful tip, you should always check that there IS external storage, and that it is available, before attempting to use it.

If you try this, you can then use the DDMS explorer (or the adb shell) to poke around and see the backup files in the /sdcard/exampledata location. Once the backup files are there, you can also do this same "task" in reverse and copy the database from the SD card back to the /data/data/APPNAME/databases location to restore it. The only trick there is to make sure there are no open database operations when you backup/restore, and then after you restore you need to close and re-open the database.

Overall this is another quick and simplified example, it isn't intended to be complete or comprehensive, but hopefully it may provide some direction for you to explore these topics on your own and make apps that work a little better for your users.

(And, when I get a bit more time the next article in this series will involve checking for the existence of a database backup at the first app startup and if found importing/using it.)