Read SQLite Database from Android Asset Resource

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.

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

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

import android.database.sqlite.SQLiteDatabase;

to

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):

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

Native:

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):

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:

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):

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:

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