Download app-release.7z
Download SQLiteXTest.7z
Introduction
Improve the SQLite file handling performance from Android Asset Resource
Background
- Android SQLiteDatabase
- Android Asset Handling
- SQLite VFS
- 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:
- wastes disk usage
if the database file size is a little big - wastes CPU
- 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
- Custom Function
- Encryption
- URI file syntax
- 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
- disable the compress for the SQLiteDatabase file in android asset
in build.gradle:
aaptOptions {
noCompress 'db'
}
- implement SQLite VFS and register it
sqlite3_vfs_register(&AndroidAsset::vfs, false);
sqlite3_vfs_register(&AssetFDMap::vfs, false);
sqlite3_vfs_register(&AssetFD::vfs, false);
- 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) { return SQLITE_NOTFOUND;
}
f->fd = AAsset_openFileDescriptor64(asset, &f->offset, &f->length);
AAsset_close(asset);
if (f->fd < 0) { 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;
}
__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
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.