Click here to Skip to main content
14,173,149 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

10.1K views
5 downloads
5 bookmarked
Posted 21 Mar 2018
Licenced CPOL

Read SQLite Database from Android Asset Resource

, 22 Mar 2018
Rate this:
Please Sign up or sign in to vote.
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

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

License

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

Share

About the Author

Yesy
Software Developer (Senior)
China China
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionSample project's link is broken Pin
Member 109431802-May-19 10:09
memberMember 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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web02 | 2.8.190524.3 | Last Updated 23 Mar 2018
Article Copyright 2018 by Yesy
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid