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:
3.1: Clone the  externalsqliteimporter library from this GitHub repository.
3.2: Goto File → New→ Import Module...
3.3: Browse and select the  externalsqliteimporter witha the library name: externalsqliteimporter

Deploy and Upgrade Android Database From External Directory

3.4: Open the build.gradle (Module: app) file and add the following dependency.
dependencies {
    compile project(path: ':externalsqliteimporter')
}

Deploy and Upgrade Android Database From External Directory

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.

Deploy and Upgrade Android Database From External Directory

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.

Deploy and Upgrade Android Database From External Directory

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.
Latest
Previous
Next Post »

14 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
sharon perumala
AUTHOR
December 29, 2017 at 1:35 PM delete

Sir,
I tried to build apk but it shows two error

Error:(25, 31) error: constructor DatabaseOpenHelper in class DatabaseOpenHelper cannot be applied to given types;
required: Context,String
found: Context
reason: actual and formal argument lists differ in length

Error:(46, 73) error: DATABASE_NAME has private access in DatabaseOpenHelper

Reply
avatar
Gobinath
AUTHOR
December 30, 2017 at 12:26 AM delete

Hi,
I have updated the article (Check Step 3). Please test it again and let me know.


Regards

Reply
avatar
sharon perumala
AUTHOR
December 30, 2017 at 11:30 AM delete

I'm getting this error while adding the library

Error:(27, 0) Could not find method implementation() for arguments [directory 'libs'] on object of type org.gradle.api.internal.artifacts.dsl.dependencies.DefaultDependencyHandler.

Reply
avatar
Gobinath
AUTHOR
December 30, 2017 at 6:23 PM delete

Hi,
What is your Android Studio and Gradle version?
Please make sure that you are have and use the latest one. If it does not help, let me know.

Reply
avatar
sharon perumala
AUTHOR
December 30, 2017 at 8:30 PM delete

Sir, I'm using Gradle version 3.3 and Android Studio version 2.3.3

Reply
avatar
sharon perumala
AUTHOR
January 1, 2018 at 9:40 PM delete

Thanks a lot Sir, now the application works fine.
I upgraded to Android Studio 3.0.1 and Gradle version is 4.1

I have done some edits in the above code

In build.gradle(module: app)
under dependencies

implementation 'com.android.support:appcompat-v7:26.0.0-beta1'
was chaged to
implementation 'com.android.support:appcompat-v7:26.1.0'



In DataBaseOpenHelper.java class

private static final String DATABASE_NAME = "quotes.db";
was changed to
public static final String DATABASE_NAME = "quotes.db";



In MainActivity.java

private final boolean fromExternalSource = false;
was changed to
private final boolean fromExternalSource = true;

And the folder specified in MainActivity.java was just "/database" I changed it to "/databases"


And finally

apply from: 'https://raw.githubusercontent.com/blundell/release-android-library/master/android-release-aar.gradle'

in build.gradle(module: externalsqliteimporter)
was causing some errors because I disconnected Internet during building the apk. So I reconnected Internet and the problem was fixed.

Reply
avatar
ivan
AUTHOR
January 11, 2018 at 4:57 AM delete

Hi Gobinath,
I ran the app with no errors, but I see no data in the emuator, just the message at the beginning that says"Until you grant the permission, we cannot display the quotes". I did everything exactly as the example and I haven´t found a part where I could fix this error. Sqlite doesn´t manage grant permissions.
Thanks

Reply
avatar
Gobinath
AUTHOR
January 11, 2018 at 5:37 AM delete

Hi,
I think you are using an emulator with Android 6 or latest. Go to Settings --> Apps --> Quotes and grant all the listed permissions.

Reply
avatar

Contact Form

Name

Email *

Message *