Download SQLite database from Internet and load into Android application

Question

For my android application, I would like to use a large database (about 45 MB).

One solution would be to include the (splitted) database in the assets folder and copy it to the database directory on the first startup.

But this would consume disk space twice - one time in the assets folder where the file can't be deleted and one time in the database directory where it has been copied to.

So I would rather like to download the database from the Internet (webserver) on the first startup. How could I do this? Can I download a complete SQLite file and save it to the database directory? Or should I rather go with JSON data files that are used to populate the database?

Answer 1

One solution would be to include the (splitted) database in the assets folder and copy it to the database directory on the first startup.

It would not have to be split, just ZIPped. See SQLiteAssetHelper for an example.

How could I do this?

Use HttpUrlConnection. Or, use HttpClient.

Can I download a complete SQLite file and save it to the database directory?

Yes. Use getDatabasePath() to get the correct local path to use.

Or should I rather go with JSON data files that are used to populate the database?

You could, but for 45MB, that would be ghastly slow.

Answer 2

Having been asked what solution I ended up with, here's the code I used (roughly). It may not be complete anymore, and it's not elegant or clean, either. But maybe it can be of help to you.

MyActivity.java

public class MyActivity extends Activity {

    private static final String SD_CARD_FOLDER = "MyApp";
    private static final String DB_DOWNLOAD_PATH = "http://www.example.org/downloads/dictionary.sqlite";
    private Database mDB = null;
    private DatabaseDownloadTask mDatabaseDownloadTask = null;
    private DatabaseOpenTask mDatabaseOpenTask = null;

    private class DatabaseDownloadTask extends AsyncTask<Context, Integer, Boolean> {

        @Override
        protected void onPreExecute() {
            mProgressDialog = new ProgressDialog(MyActivity.this);
            mProgressDialog.setTitle(getString(R.string.please_wait));
            mProgressDialog.setMessage(getString(R.string.downloading_database));
            mProgressDialog.setIndeterminate(false);
            mProgressDialog.setMax(100);
            mProgressDialog.setProgressStyle(ProgressDialog.STYLE_HORIZONTAL);
            mProgressDialog.setCancelable(false);
            mProgressDialog.show();
            getWindow().addFlags(WindowManager.LayoutParams.FLAG_KEEP_SCREEN_ON);
        }

        @Override
        protected Boolean doInBackground(Context... params) {
            try {
                File dbDownloadPath = new File(Database.getDatabaseFolder());
                if (!dbDownloadPath.exists()) {
                    dbDownloadPath.mkdirs();
                }
                HttpParams httpParameters = new BasicHttpParams();
                HttpConnectionParams.setConnectionTimeout(httpParameters, 5000);
                HttpConnectionParams.setSoTimeout(httpParameters, 5000);
                DefaultHttpClient client = new DefaultHttpClient(httpParameters);
                HttpGet httpGet = new HttpGet(DB_DOWNLOAD_PATH);
                InputStream content = null;
                try {
                    HttpResponse execute = client.execute(httpGet);
                    if (execute.getStatusLine().getStatusCode() != 200) { return null; }
                    content = execute.getEntity().getContent();
                    long downloadSize = execute.getEntity().getContentLength();
                    FileOutputStream fos = new FileOutputStream(Database.getDatabaseFolder()+Database.DATABASE_NAME+".sqlite");
                    byte[] buffer = new byte[256];
                    int read;
                    long downloadedAlready = 0;
                    while ((read = content.read(buffer)) != -1) {
                        fos.write(buffer, 0, read);
                        downloadedAlready += read;
                        publishProgress((int) (downloadedAlready*100/downloadSize));
                    }
                    fos.flush();
                    fos.close();
                    content.close();
                    return true;
                }
                catch (Exception e) {
                    if (content != null) {
                        try {
                            content.close();
                        }
                        catch (IOException e1) {}
                    }
                    return false;
                }
            }
            catch (Exception e) {
                return false;
            }
        }

        protected void onProgressUpdate(Integer... values) {
            if (mProgressDialog != null) {
                if (mProgressDialog.isShowing()) {
                    mProgressDialog.setProgress(values[0]);
                }
            }
        }

        @Override
        protected void onPostExecute(Boolean result) {
            if (mProgressDialog != null) {
                mProgressDialog.dismiss();
                mProgressDialog = null;
            }
            if (result.equals(Boolean.TRUE)) {
                Toast.makeText(MyActivity.this, getString(R.string.database_download_success), Toast.LENGTH_LONG).show();
                mDatabaseOpenTask = new DatabaseOpenTask();
                mDatabaseOpenTask.execute(new Context[] { MyActivity.this });
            }
            else {
                Toast.makeText(getApplicationContext(), getString(R.string.database_download_fail), Toast.LENGTH_LONG).show();
                finish();
            }
        }

    }

    private class DatabaseOpenTask extends AsyncTask<Context, Void, Database> {

        @Override
        protected Database doInBackground(Context ... ctx) {
            try {
                String externalBaseDir = Environment.getExternalStorageDirectory().getAbsolutePath();
                // DELETE OLD DATABASE ANFANG
                File oldFolder = new File(Environment.getExternalStorageDirectory().getAbsolutePath()+"/"+SD_CARD_FOLDER);
                File oldFile = new File(oldFolder, "dictionary.sqlite");
                if (oldFile.exists()) {
                    oldFile.delete();
                }
                if (oldFolder.exists()) {
                    oldFolder.delete();
                }
                // DELETE OLD DATABASE ENDE
                File newDB = new File(Database.getDatabaseFolder()+"dictionary.sqlite");
                if (newDB.exists()) {
                    return new Database(ctx[0]);
                }
                else {
                    return null;
                }
            }
            catch (Exception e) {
                return null;
            }
        }

        @Override
        protected void onPreExecute() {
            mProgressDialog = ProgressDialog.show(MainActivity.this, getString(R.string.please_wait), "Loading the database! This may take some time ...", true);
        }

        @Override
        protected void onPostExecute(Database newDB) {
            if (mProgressDialog != null) {
                mProgressDialog.dismiss();
                mProgressDialog = null;
            }
            if (newDB == null) {
                mDB = null;
                AlertDialog.Builder downloadDatabase = new AlertDialog.Builder(MyActivity.this);
                downloadDatabase.setTitle(getString(R.string.downloadDatabase));
                downloadDatabase.setCancelable(false);
                downloadDatabase.setMessage(getString(R.string.wantToDownloadDatabaseNow));
                downloadDatabase.setPositiveButton(getString(R.string.download), new DialogInterface.OnClickListener() {
                    public void onClick(DialogInterface dialog, int which) {
                        dialog.dismiss();
                        mDatabaseDownloadTask = new DatabaseDownloadTask();
                        mDatabaseDownloadTask.execute();
                    }
                });
                downloadDatabase.setNegativeButton(getString(R.string.cancel), new DialogInterface.OnClickListener() {
                    public void onClick(DialogInterface dialog, int which) {
                        dialog.dismiss();
                        finish();
                    }
                });
                downloadDatabase.show();
            }
            else {
                mDB = newDB;
            }
        }
    }

    @Override
    public void onDestroy() {
        super.onDestroy();
        if (mDatabaseDownloadTask != null) {
            if (mDatabaseDownloadTask.getStatus() != AsyncTask.Status.FINISHED) {
                mDatabaseDownloadTask.cancel(true);
            }
        }
        if (mDatabaseOpenTask != null) {
            if (mDatabaseOpenTask.getStatus() != AsyncTask.Status.FINISHED) {
                mDatabaseOpenTask.cancel(true);
            }
        }
        if (mProgressDialog != null) {
            mProgressDialog.dismiss();
            mProgressDialog = null;
        }
        if (mDB != null) {
            mDB.close();
            mDB = null;
        }
    }

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        if (!Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)) {
            Toast.makeText(getApplicationContext(), getString(R.string.sd_card_not_found), Toast.LENGTH_LONG).show();
            finish();
        }
        mDatabaseOpenTask = new DatabaseOpenTask();
        mDatabaseOpenTask.execute(new Context[] { this });
    }

}

Database.java

public class Database extends SQLiteOpenHelper {

private static final String DATABASE_NAME = "dictionary";
private String DATABASE_PATH = null;
private static final int DATABASE_VERSION = 1;
private static final String PACKAGE_NAME = "com.my.package";
private SQLiteDatabase db;

public Database(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    DATABASE_PATH = getDatabaseFolder()+DATABASE_NAME+".sqlite";
    db = getWritableDatabase();
}

public static String getDatabaseFolder() {
    return Environment.getExternalStorageDirectory().getAbsolutePath()+"/Android/data/"+PACKAGE_NAME+"/databases/";
}

@Override
public synchronized SQLiteDatabase getWritableDatabase() {
    try {
        if (db != null) {
            if (db.isOpen()) {
                return db;
            }
        }
        return SQLiteDatabase.openDatabase(DATABASE_PATH, null, SQLiteDatabase.OPEN_READWRITE | SQLiteDatabase.NO_LOCALIZED_COLLATORS);
    }
    catch (Exception e) {
        return null;
    }
}

@Override
public synchronized void close() {
     if (db != null) {
         db.close();
         db = null;
     }
     super.close();
}

@Override
public void onCreate(SQLiteDatabase db) { }

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { }

}