Deploy and Upgrade Android Database From External Directory

After seeing the huge response for the Import and Use External Database in Android article, I have realized the importance of deploying Android database from external sources. The library used in the above article allows you to import database only from the assets directory. However, recently one of my readers, asked for a way to import and upgrade the database from SD card. As a solution for his use case, I have developed a new library named "externalsqliteimporter" which allows you to import database either from assets directory or from SD card. This article explains the application of this Android library using a sample application.


The ExternalSQLiteImporter library allows you to build your SQLite database on your desktop computer, and to import and use it in your Android application. This library has two separate ways to maintain your database.

This library is still under development. Deploying and upgrading  the database from an external directory is not secure as it is publicly available for third party applications as well. Use that feature with caution. 


Method 1: Deploying from assets/database directory.
In this way, you can pack an external database into your application and during the first launch, the database will be deployed as the default database. In case you want to upgrade the database, you need to create an SQL script to upgrade the database and place it in the same assets/database directory, increase the database version and distribute the new apk file. Otherwise, you can write your changes to upgrade the database in the overriding onUpgradeInternally method.

Method 2: Deploying from an external directory like SD card.
In this way, you have to have a directory in your SD card or in internal drive dedicated for your application. The directory must contain the database to deploy and a text file named version.info. The version.info file should keep the current version which will be used as the version of your application. To upgrade the database, you have to provide either an SQL script or a new database instance. If you want to upgrade the existing database using a new database instance, you can do that in two ways which will be discussed later in this article.

The second method allows you to upgrade your database without releasing a new application, which may be useful in some scenarios where your application is using the info provided by other applications. However method 2 is not secured as method 1 because any vulnerable applications can modify your external upgrade script and/or the database since they are publicly available. I am working on providing encryption support for external database and SQL script but it will take time.

Step 1:
Create a database quotes.db using your favorite SQLite database application (DB Browser for SQLite is a portable cross-platform freeware, which can be used to create and edit SQLite databases). Create a table 'quotes' with a single column 'quote'. Insert some random quotes into the table 'quotes'.


Step 2:
Create a new application “Import External Database” with a package name “com.javahelps.importexternaldatabase”.

Step 3:
Open the build.gradle (Module: app) file and add the following dependency.
dependencies {
    compile 'com.javahelps:externalsqliteimporter:+'
}


Once you have saved the build.gradle file, click on the 'Sync Now' link or the 'Sync Project with Gradle Files' menu icon to update the project.

Step 4:
Add a ListView in your activity_main.xml.
<?xml version="1.0" encoding="utf-8"?>
<FrameLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context="com.javahelps.importexternaldatabase.MainActivity">

    <ListView
        android:id="@+id/listView"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_gravity="center" />
</FrameLayout>

Step 5: Follow this step, only if you want to import from assets directory
5.1: Right click on the app folder and create new assets folder.


5.2: Create a new folder 'database' inside the assets folder.

5.3: Copy and paste the  quotes.db file inside the assets/databases folder.


Step 6: Follow this step, only if you want to import from external directory
6.1: Add the following permission in the AndroidManifest.xml
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />

6.2: Create a new file version.info on your desktop and save "1" without quotes in that file. The version.info must contain the version of the database. Anything other than an integer value will throw a runtime exception.

6.3: Create a new folder "databases" in you device's SD card or the emulator's /storage/emulated/0/ path and push both quotes.db and version.info into that folder.


Step 7:
Create a new class 'DatabaseOpenHelper'
package com.javahelps.importexternaldatabase;

import android.content.Context;

import com.javahelps.externalsqliteimporter.ExternalSQLiteOpenHelper;

public class DatabaseOpenHelper extends ExternalSQLiteOpenHelper {
    /**
     * Name of the database.
     */
    private static final String DATABASE_NAME = "quotes.db";

    /**
     * Version of the database. Only used to import from assets.
     */
    private static final int DATABASE_VERSION = 1;

    /**
     * Use this constructor if you want to import database from assets/database directory.
     */
    public DatabaseOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    /**
     * Use this constructor if you want to import database from external directory.
     */
    public DatabaseOpenHelper(Context context, String sourceDirectory) {
        super(context, DATABASE_NAME, sourceDirectory, null);
    }
}
Notice that rather than extending SQLiteOpenHelper, the DatabaseOpenHelper extends  ExternalSQLiteOpenHelper class.

Step 9:
Create a new class DatabaseAccess and enter the code as shown below. More details about this class is available at Advanced Android Database tutorial.
package com.javahelps.importexternaldatabase;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.javahelps.externalsqliteimporter.ExternalSQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

public class DatabaseAccess {
    private ExternalSQLiteOpenHelper openHelper;
    private SQLiteDatabase database;
    private static DatabaseAccess instance;

    /**
     * Private constructor to avoid object creation from outside classes.
     *
     * @param context
     * @param sourceDirectory
     */
    private DatabaseAccess(Context context, String sourceDirectory) {
        if (sourceDirectory == null) {
            this.openHelper = new DatabaseOpenHelper(context);
        } else {
            this.openHelper = new DatabaseOpenHelper(context, sourceDirectory);
        }
    }

    /**
     * Return a singleton instance of DatabaseAccess.
     *
     * @param context         the Context
     * @param sourceDirectory optional external directory
     * @return the instance of DabaseAccess
     */
    public static DatabaseAccess getInstance(Context context, String sourceDirectory) {
        if (instance == null) {
            instance = new DatabaseAccess(context, sourceDirectory);
        }
        return instance;
    }

    /**
     * Open the database connection.
     */
    public void open() {
        this.database = openHelper.getWritableDatabase();
    }

    /**
     * Close the database connection.
     */
    public void close() {
        if (database != null) {
            this.database.close();
        }
    }

    /**
     * Read all quotes from the database.
     *
     * @return a List of quotes
     */
    public List<String> getQuotes() {
        List<String> list = new ArrayList<>();
        Cursor cursor = database.rawQuery("SELECT * FROM quotes", null);
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            list.add(cursor.getString(0));
            cursor.moveToNext();
        }
        cursor.close();
        return list;
    }
}
In this class, only the getQuotes method is implemented to read the data from the database. You have the full freedom to insert, update and delete any rows in the database as usual. For more details, follow this link Advanced Android Database.

All the database related setups are completed and now we need to create a ListView to display the quotes.

Step 11:
Find the object of ListView in the onCreate method of MainActivity and feed the quotes which are read from the database. The instance variable fromExternalSource controls whether to deploy from  the external database or from assets folder.  Depending on your choice, set either true or false to the fromExternalSource variable.
package com.javahelps.importexternaldatabase;

import android.Manifest;
import android.content.pm.PackageManager;
import android.os.Build;
import android.os.Bundle;
import android.os.Environment;
import android.support.annotation.NonNull;
import android.support.v7.app.AppCompatActivity;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.Toast;

import java.io.File;
import java.util.List;

public class MainActivity extends AppCompatActivity {

    private ListView listView;
    private static final int REQUEST_EXTERNAL_STORAGE = 1;
    private static String[] PERMISSIONS_STORAGE = {
            Manifest.permission.READ_EXTERNAL_STORAGE
    };

    private final boolean fromExternalSource = false;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        this.listView = (ListView) findViewById(R.id.listView);

        // Request for permission to read external storage
        if (fromExternalSource && Build.VERSION.SDK_INT >= Build.VERSION_CODES.M && checkSelfPermission(Manifest.permission.READ_EXTERNAL_STORAGE) != PackageManager.PERMISSION_GRANTED) {
            requestPermissions(PERMISSIONS_STORAGE, REQUEST_EXTERNAL_STORAGE);
        } else {
            showQuotes();
        }
    }

    private void showQuotes() {
        DatabaseAccess databaseAccess;
        if (fromExternalSource) {
            // Check the external database file. External database must be available for the first time deployment.
            String externalDirectory = Environment.getExternalStorageDirectory().getAbsolutePath() + "/database";
            File dbFile = new File(externalDirectory, DatabaseOpenHelper.DATABASE_NAME);
            if (!dbFile.exists()) {
                return;
            }
            // If external database is avaliable, deploy it
            databaseAccess = DatabaseAccess.getInstance(this, externalDirectory);
        } else {
            // From assets
            databaseAccess = DatabaseAccess.getInstance(this, null);
        }

        databaseAccess.open();
        List<String> quotes = databaseAccess.getQuotes();
        databaseAccess.close();

        ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, quotes);
        this.listView.setAdapter(adapter);
    }

    @Override
    public void onRequestPermissionsResult(int requestCode, @NonNull String[] permissions, @NonNull int[] grantResults) {
        if (requestCode == REQUEST_EXTERNAL_STORAGE) {
            if (grantResults[0] == PackageManager.PERMISSION_GRANTED) {
                // Permission is granted
                showQuotes();
            } else {
                Toast.makeText(this, "Until you grant the permission, we cannot display the quotes", Toast.LENGTH_SHORT).show();
            }
        }

    }
}

Step 12:
Save all the changes and run the application.


To contribute to this library and/or to report bugs, please visit: externalsqliteimporter

Find the project @ Git Hub.
Previous
Next Post »

6 comments

Write comments
vvp
AUTHOR
July 25, 2016 at 10:41 PM delete

Hello, Thank you. This method works. But this method requires the database name to be predefined as part of the application. This makes my database have a standard database name always. Is there a way around that such that I do not have to predefine the database's name?

Reply
avatar
Gobinath
AUTHOR
July 26, 2016 at 7:59 AM delete

Hi,
That's a good idea to add as a new feature. However we need a strategy to provide the database file name to the library. What about providing the database file name in version.info as a property? If you have any other better ideas, please comment below :-)

Reply
avatar
vvp
AUTHOR
July 26, 2016 at 8:14 PM delete

I have been trying to figure out if there is a way to import database by including an option to browse from the computer and adding the database file after the application is running. I presume this could also work.

Reply
avatar
Gobinath
AUTHOR
July 27, 2016 at 2:40 PM delete

Hi,
I have added opened an issue [1] for this feature. I will add this feature soon.

[1] https://github.com/javahelps/externalsqliteimporter/issues/1

Reply
avatar
vvp
AUTHOR
August 2, 2016 at 2:29 AM delete

Okay. Thank you. I shall follow up with you if I find any leads.

Reply
avatar
Nikolay
AUTHOR
December 5, 2016 at 12:54 PM delete

Hi,
I've tried to build some project using library, but it point me, that in file ExternalSQLiteOpenHelper.java cannot resolve sumbols Utility and

ExternalSQLiteOpenHelperConstants

Reply
avatar

Contact Form

Name

Email *

Message *