Click here to Skip to main content
15,867,835 members
Articles / Mobile Apps / Android

Read SQLite Database from Android Asset Resource

Rate me:
Please Sign up or sign in to vote.
2.82/5 (3 votes)
22 Mar 2018CPOL2 min read 21K   5   6   2
Demonstrates a method to manipulate SQLite databases located in the asset folder

Download app-release.7z

Download SQLiteXTest.7z

Introduction

Improve the SQLite file handling performance from Android Asset Resource

Background

  1. Android SQLiteDatabase
  2. Android Asset Handling
  3. SQLite VFS
  4. SQLite URI

Using the code

Generally speaking, if we want to read a SQLite database from an Android asset file we have to copy the asset file to a local folder, then read the database from the local file.

This method has some disadvantages:

  1. wastes disk usage
    if the database file size is a little big
  2. wastes CPU
  3. security vulnerable
    user can replace the local database file after root the device

To overcome these disadvantages  we will implment a new SQLite VFS which supports Android asset resource.

SQLite use the unix-vfs on Unix OS and use win32-vfs on Window OS

Android wraps the SQLite code and export some java interface (android.database.sqlite.SQLiteDatabase), but miss some advanced features from the original SQLite implementation

  1. Custom Function
  2. Encryption
  3. URI file syntax
  4. VFS
Actually Android have already includes the above feature in libsqlite.so, but doesn't supply the Java interface/entrypoint, and according to Android O+ security behavior change, the developer MUST NOT access the libsqlite.so in the latter Android version

Fortunately, the SQLite developers already supply a similar Java  wrapper: SQLite Android Bindings, which can supply these features

The class name and members name are mostly identical , so you can import the aar and change your java source import from

Java
import android.database.sqlite.SQLiteDatabase;

to

Java
import org.sqlite.database.sqlite.SQLiteDatabase;

Steps to use

  1. disable the compress for the SQLiteDatabase file in android asset
     in build.gradle:
    JavaScript
    aaptOptions {
        noCompress 'db'
    }
  2. implement SQLite VFS and register it
    sqlite3_vfs_register(&AndroidAsset::vfs, false);
    sqlite3_vfs_register(&AssetFDMap::vfs, false);
    sqlite3_vfs_register(&AssetFD::vfs, false);
  3. open the database file in asset folder with custome URI 

I implement three VFSes for different scenerio 

First VFS :android_asset

Java (open SQLiteDatabase with custom SQLite URI):

Java
try (SQLiteDatabase db = SQLiteDatabase.openDatabase("file:asset_db.db?vfs=android_asset&immutable=1&mode=ro", null, SQLiteDatabase.OPEN_READONLY)) {
   ................................
}

Native:

C++
static int xRead(sqlite3_file *file, void *buf, int iAmt, sqlite3_int64 iOfst) {
    vfs_file *f = (vfs_file *) file;
    int expectReadLen = (iAmt + iOfst > f->length) ? (f->length - iOfst) : iAmt;
    int readLen = pread64(f->fd, buf, expectReadLen, iOfst + f->offset);
    if (readLen < 0) {
        return SQLITE_IOERR_READ;
    } else if (readLen == expectReadLen) {
        return SQLITE_OK;
    } else {
        memset((__uint8_t *) buf + readLen, 0, iAmt - readLen);
        return SQLITE_IOERR_SHORT_READ;
    }
}

static int vfsOpen(sqlite3_vfs *vfs, const char *path, sqlite3_file *file, int flags,
                   int *outflags) {

    ALOGD("%s:: path=%s flags=%x", __FUNCTION__, path, flags);
    if (g_AAssetManager == NULL) {
        return SQLITE_ERROR;
    }
    vfs_file *f = (vfs_file *) file;
    f->pMethods = &vfs_io_methods;
    AAsset *asset = AAssetManager_open(g_AAssetManager, path, AASSET_MODE_RANDOM);
    if (asset == NULL) {    //if the asset file don't exist
        return SQLITE_NOTFOUND;
    }
    f->fd = AAsset_openFileDescriptor64(asset, &f->offset, &f->length);
    AAsset_close(asset);
    if (f->fd < 0) {  //if the asset file is compressed
        return SQLITE_NOTFOUND;
    }
    *outflags = flags;
    return SQLITE_OK;
}

Second VFS: asset_fd_map

Java (open SQLiteDatabase with custom SQLite URI):

Java
try (AssetFileDescriptor afd = getAssets().openFd("asset_db.db")) {
    try (SQLiteDatabase db = SQLiteDatabase.openDatabase(String.format("file:%X_%X_%X?vfs=asset_fd_map&immutable=1&mode=ro", afd.getParcelFileDescriptor().getFd(), afd.getStartOffset(), afd.getLength()), null, SQLiteDatabase.OPEN_READONLY)) {
     .................................
  }
}

Native:

C++
static int xRead(sqlite3_file *file, void *buf, int iAmt, sqlite3_int64 iOfst) {
    vfs_file *f = (vfs_file *) file;
    int expectReadLen = (iAmt + iOfst > f->length) ? (f->length - iOfst) : iAmt;
    memcpy(buf, (__uint8_t *) f->address + iOfst + f->offset, expectReadLen);
    int readLen = expectReadLen;
    return SQLITE_OK;
}

static int vfsOpen(sqlite3_vfs *vfs, const char *path, sqlite3_file *file, int flags,
                   int *outflags) {

    ALOGD("%s:: path=%s flags=%x", __FUNCTION__, path, flags);
    vfs_file *f = (vfs_file *) file;
    f->pMethods = &vfs_io_methods;
    if (3 > sscanf(path, "%x_%llx_%llx", &f->fd, &f->offsetFileStart, &f->length)) {
        return SQLITE_ERROR;
    }
    //because mmap() require the offset must be on the page boundary
    __int64_t offsetToPage = (f->offsetFileStart / 4096) * 4096;
    f->offsetMapStart = f->offsetFileStart - offsetToPage;
    f->mapLength = f->length + f->offsetMapStart;

    f->address = mmap64(NULL, f->mapLength, PROT_READ, MAP_PRIVATE, f->fd, offsetToPage);
    if (f->address == MAP_FAILED) {
        return SQLITE_ERROR;
    }
    *outflags = flags;
    return SQLITE_OK;
}

Third VFS: asset_fd

Java (open SQLiteDatabase with custom SQLite URI):

Java
try (AssetFileDescriptor afd = getAssets().openFd("asset_db.db")) {
    try (SQLiteDatabase db = SQLiteDatabase.openDatabase(String.format("file:%X_%X_%X?vfs=asset_fd&immutable=1&mode=ro", afd.getParcelFileDescriptor().getFd(), afd.getStartOffset(), afd.getLength()), null, SQLiteDatabase.OPEN_READONLY)) {
     .................................
  }
}

Native:

C++
static int xRead(sqlite3_file *file, void *buf, int iAmt, sqlite3_int64 iOfst) {
    vfs_file *f = (vfs_file *) file;
    int expectReadLen = (iAmt + iOfst > f->length) ? (f->length - iOfst) : iAmt;
    int readLen = pread64(f->fd, buf, expectReadLen, iOfst + f->offset);
    if (readLen < 0) {
        return SQLITE_IOERR_READ;
    } else if (readLen == expectReadLen) {
        return SQLITE_OK;
    } else {
        memset((__uint8_t *) buf + readLen, 0, iAmt - readLen);
        return SQLITE_IOERR_SHORT_READ;
    }
}

static int vfsOpen(sqlite3_vfs *vfs, const char *path, sqlite3_file *file, int flags,
                   int *outflags) {

    ALOGD("%s:: path=%s flags=%x", __FUNCTION__, path, flags);
    vfs_file *f = (vfs_file *) file;
    f->pMethods = &vfs_io_methods;
    if (3 > sscanf(path, "%x_%llx_%llx", &f->fd, &f->offset, &f->length)) {
        return SQLITE_ERROR;
    }
    *outflags = flags;
    return SQLITE_OK;
}

In the demo project, I compare the different methods.

The elapsed time difference seems not too great, probably because the performance bottleneck is the SQLite inner data processing, but not the file handling

Image 1

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
China China
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionSample project's link is broken Pin
Member 109431802-May-19 10:09
Member 109431802-May-19 10:09 
QuestionHelp me please! Pin
Luis Turcios24-Sep-18 14:26
professionalLuis Turcios24-Sep-18 14:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.