An Android helper class to manage database creation and version management using an application's raw asset files

Last update: May 15, 2022

THIS PROJECT IS NO LONGER MAINTAINED

Android SQLiteAssetHelper

An Android helper class to manage database creation and version management using an application's raw asset files.

This class provides developers with a simple way to ship their Android app with an existing SQLite database (which may be pre-populated with data) and to manage its initial creation and any upgrades required with subsequent version releases.

It is implemented as an extension to SQLiteOpenHelper, providing an efficient way for ContentProvider implementations to defer opening and upgrading the database until first use.

Rather than implementing the onCreate() and onUpgrade() methods to execute a bunch of SQL statements, developers simply include appropriately named file assets in their project's assets directory. These will include the initial SQLite database file for creation and optionally any SQL upgrade scripts.

Setup

Gradle

If you are using the Gradle build system, simply add the following dependency in your build.gradle file:

dependencies {
    compile 'com.readystatesoftware.sqliteasset:sqliteassethelper:+'
}

Ant/Eclipse

If you are using the old build system, download the latest library JAR and put it in your project's libs folder.

Usage

SQLiteAssetHelper is intended as a drop in alternative for the framework's SQLiteOpenHelper. Please familiarize yourself with the behaviour and lifecycle of that class.

Extend SQLiteAssetHelper as you would normally do SQLiteOpenHelper, providing the constructor with a database name and version number:

public class MyDatabase extends SQLiteAssetHelper {

    private static final String DATABASE_NAME = "northwind.db";
    private static final int DATABASE_VERSION = 1;

    public MyDatabase(Context context) {
	    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
}

SQLiteAssetHelper relies upon asset file and folder naming conventions. Your assets folder will either be under your project root, or under src/main if you are using the default gradle project structure. At minimum, you must provide the following:

  • A databases folder inside assets
  • A SQLite database inside the databases folder whose file name matches the database name you provide in code (including the file extension, if any)

For the example above, the project would contain the following:

assets/databases/northwind.db

Earlier versions of this library required the database asset to be compressed within a ZIP archive. This is no longer a requirement, but is still supported. Applications still targeting Gingerbread (API 10) or lower should continue to provide a compressed archive to ensure large database files are not corrupted during the packaging process. The more Linux friendly GZIP format is also supported. The naming conventions using the above example are as follows:

  • ZIP: assets/databases/northwind.db.zip (a single SQLite database file must be the only file within the archive)
  • GZIP: assets/databases/northwind.db.gz

The database will be extracted from the assets and copied into place within your application's private data directory. If you prefer to store the database file somewhere else (such as external storage) you can use the alternate constructor to specify a storage path. You must ensure that this path is available and writable whenever your application needs to access the database.

super(context, DATABASE_NAME, context.getExternalFilesDir(null).getAbsolutePath(), null, DATABASE_VERSION);

The database is made available for use the first time either getReadableDatabase() or getWritableDatabase() is called.

The class will throw a SQLiteAssetHelperException if you do not provide the appropriately named file.

The SQLiteOpenHelper methods onConfigure, onCreate and onDowngrade are not supported by this implementation and have been declared final.

The samples:database-v1 project demonstrates a simple database creation and usage example using the classic Northwind database.

Database Upgrades

At a certain point in your application's lifecycle you will need to alter it's database structure to support additional features. You must ensure users who have installed your app prior to this can safely upgrade their local databases without the loss of any locally held data.

To facilitate a database upgrade, increment the version number that you pass to your SQLiteAssetHelper constructor:

private static final int DATABASE_VERSION = 2;

Update the initial SQLite database in the project's assets/databases directory with the changes and create a text file containing all required SQL commands to upgrade the database from its previous version to it's current version and place it in the same folder. The required naming convention for this upgrade file is as follows:

assets/databases/<database_name>_upgrade_<from_version>-<to_version>.sql

For example, northwind.db_upgrade_1-2.sql upgrades the database named "northwind.db" from version 1 to 2. You can include multiple upgrade files to upgrade between any two given versions.

If there are no files to form an upgrade path from a previously installed version to the current one, the class will throw a SQLiteAssetHelperException.

The samples:database-v2-upgrade project demonstrates a simple upgrade to the Northwind database which adds a FullName column to the Employee table.

Generating upgrade scripts

You can use 3rd party tools to automatically generate the SQL required to modify a database from one schema version to another. One such application is SQLite Compare Utility for Windows.

Upgrades via overwrite

If you have a read-only database or do not care about user data loss, you can force users onto the latest version of the SQLite database each time the version number is incremented (overwriting the local database with the one in the assets) by calling the setForcedUpgrade() method in your SQLiteAsstHelper subclass constructor.

You can additionally pass an argument that is the version number below which the upgrade will be forced.

Note that this will overwrite an existing local database and all data within it.

Credits

####Author:

Contributors:

License

Copyright (C) 2011 readyState Software Ltd
Copyright (C) 2007 The Android Open Source Project

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

GitHub

https://github.com/jgilfelt/android-sqlite-asset-helper
Comments
  • 1. Added splitSqlScript function to parse SQL DB upgrade scripts

    DB upgrade scripts that contain semicolons in strings for inserts / updates are not parsed correctly. e.g.

    1: CREATE TABLE temp (a TEXT); 2: INSERT INTO temp VALUES ("this;that");

    was previously parsed as:

    2: INSERT INTO temp VALUES ("this; 3: that");

    I have added a splitSqlScript function which I have taken from http://www.michenux.net/android-database-sqlite-creation-upgrade-245.html (search for splitSqlScript).

    Note: I have modified the code on Line 345 to check for a double quote character rather than the single quote character used in the linked page. This is to align with the example-v2 code which uses double quotes in SQL scripts.

    I have tested this for my project and it is correctly handling semicolons. I have also generated a new .jar but it has doubled the size of the .jar (12kb -> 23kb).

    Reviewed by kevinchai at 2013-06-28 16:35
  • 2. Fixed documentation bug.

    The documentation makes an incorrect statement here and I added some clarification. After tracing on the ADB (and reading through your code) I discovered that the name of the zip archive must have .db in it.

    Reviewed by jsinglet at 2014-01-14 17:53
  • 3. Fix incorrect alphanumeric sorting of upgrade scripts that can cause them to be applied in the wrong order

    This patch fixes the case when applying many upgrade scripts at once that go up to the next digit, for example from 9 to 10, or 99 to 100, from being applied in the wrong order.

    The patch upgrades the Sort call on the file list to compare paths by their upgrade version numbers, instead of using alphanumeric comparison on plain file names.

    For example, when applying 2 different upgrade files from version 9 to 11, this fix keeps it from using the incorrect alphanumeric order of 10_11 before 9_10, and instead runs them in the correct order of 9_10 then 10_11.

    I thought about changing some of the logic order so that it can do this compare earlier without the regular expression parsing, but that would be a fairly decent logic adjustment. So I opted for keeping this code separated out into its own class (but still nested in the main class).

    Reviewed by jon-adams at 2013-10-25 23:07
  • 4. Allow/Disallow nested transactions

    As I kept forgetting to remove the "BEGIN TRANSACTION" and "COMMIT TRANSACTION" lines of the migration script produced by the SQLite Compare Utility, I made the library's default behaviour to skip them.

    This will essentially treat any nested transactions in the migration script as part of the parent one.

    If someone really wants to, nested transactions can be re-enabled by calling "setAllowNestedTransactions(true)".

    (I told you I am bad at repetitive tasks)

    -Alex

    Reviewed by alt236 at 2012-03-05 16:52
  • 5. don't skip .sql upgrade scipts after a forced upgrade if the bundled database file is not the latest version

    I don't always update the bundled .db file when I add a new schema version but instead I add an upgrade script .sql file (e.g. database.db_upgrade_2-3.sql) and accodingly increment (e.g. 2->3) the version parameter when calling the super constructor SQLiteAssetHelper(...). This way existing installs use the .sql script as usual and new installs copy the bundled database.db (version 2) and then execute the .sql upgrade on top of that.

    I found a problem when I use setForcedUpgrade(2), now updates from version 1 to 3 would replace the old version 1 database with the bundled version 2 (like expected), but not apply the version 3 upgrade .sql file and instead just set the version to 3.

    I found that the code in getWritableDatabase() blindly overrides the version of the newly copied database to be the latest version mNewVersion that had been defined by calling the super constructor. I based this PR on top of #87 because that is also related to using setForcedUpgrade(). The main change in this PR is to remove the line db.setVersion(mNewVersion); in order to still trigger the version check and upgrade using .sql files if the bundled sqlite.db file is "outdated".

    Reviewed by p-schneider at 2017-02-02 23:43
  • 6. fixed memory leaks; Update SQLiteAssetHelper.java

    fixes some memory leaks

    Thanks to CoolMind. contribution source: https://github.com/jgilfelt/android-sqlite-asset-helper/issues/59 https://github.com/jgilfelt/android-sqlite-asset-helper/issues/62

    Reviewed by mantas84 at 2015-12-17 17:46
  • 7. added a method to import sql scripts instead of copying predefined

    Hi Jeff, I think I messed with whitespaces, so ?w=1 should do it https://github.com/RookieGuy/android-sqlite-asset-helper/compare/jgilfelt:master...master?w=1

    How about merging this if you like what you see? Regards Klaus

    Reviewed by uc-mobileapps-zz at 2014-05-11 00:01
Kodein-DB is a Kotlin/Multiplatform embedded NoSQL database that works on JVM, Android, Kotlin/Native and iOS.
Kodein-DB is a Kotlin/Multiplatform embedded NoSQL database that works on JVM, Android, Kotlin/Native and iOS.

Kodein-DB is a Kotlin/Multiplatform embedded NoSQL database that works on JVM, Android, Kotlin/Native and iOS. It is suited for client or mobile applications.

May 15, 2022
A library for reading Shared Preferences and Database values within the application.
A library for reading Shared Preferences and Database values within the application.

AppDataReader A library for reading and writing Shared Preferences and Database values of the application within the device. Advantages of using this

May 18, 2022
A quick and easy database manager plugin library for your DBFlow databases.
A quick and easy database manager plugin library for your DBFlow databases.

DBFlowManager A quick and easy database manager and viewer plugin library for your DBFlow databases to view, insert, delete, update the tables directl

Jan 14, 2021
A key-value database for Android
A key-value database for Android

SnappyDB SnappyDB is a key-value database for Android it's an alternative for SQLite if you want to use a NoSQL approach. It allows you to store and g

May 11, 2022
Insanely easy way to work with Android Database.

Sugar ORM Insanely easy way to work with Android databases. Official documentation can be found here - Check some examples below. The example applicat

May 19, 2022
Android Database Performance Benchmarks

ℹ Check out our new performance test app that includes ObjectBox. Android Database Performance Benchmark This project evaluates Android databases and

Apr 6, 2022
Test any type of cloud database on Android apps. No need of a dedicated backend.

Test any type of cloud database on Android apps. No need of a dedicated backend.

May 9, 2022
Android with Real-time Database
 Android with Real-time Database

Android with Real-time Database It was too much effort to build my own real-time database, but the result really satisfying, so it was worth it. Note

Oct 11, 2021
Realm is a mobile database: a replacement for SQLite & ORMs

Realm is a mobile database that runs directly inside phones, tablets or wearables. This repository holds the source code for the Java version of Realm

May 15, 2022
ObjectBox is a superfast lightweight database for objects
ObjectBox is a superfast lightweight database for objects

ObjectBox Java (Kotlin, Android) ObjectBox is a superfast object-oriented database with strong relation support. ObjectBox is embedded into your Andro

May 19, 2022
A simple NoSQL client for Android. Meant as a document store using key/value pairs and some rudimentary querying. Useful for avoiding the hassle of SQL code.

SimpleNoSQL A simple NoSQL client for Android. If you ever wanted to just save some data but didn't really want to worry about where it was going to b

May 10, 2022
Android library for viewing and sharing in app databases.
Android library for viewing and sharing in app databases.

DbInspector DbInspector provides a simple way to view the contents of the in-app database for debugging purposes. There is no need to pull the databas

May 17, 2022
A small library to help with Realm.IO integration in Android apps

Android Realm Asset Helper A small library of methods to help with Realm.IO integration in Android apps Copies a realm database from a the assets fold

Dec 28, 2021
A wrapper around Android's SQLiteDatabase with restoring capability

Restorable SQLiteDatabase RestorableSQLiteDatabase is a wrapper to replicate android's SQLiteDatabase class with restoring capability. This wrapper ma

Jan 20, 2022
an android library for debugging what we care about directly in app.
an android library for debugging what we care about directly in app.

EN | 中文 Pandora is a tool box that allows you to inspect and modify what includes networks, databases, UIs, etc. directly in your application. It is s

May 19, 2022
Core Data for Android

NexusData Core Data for Android NexusData is an object graph and persistence framework for Android. It allows for organizing and managing relational d

Jul 21, 2021
🧬 Android DataBinding kit for notifying data changes from Model layers to UI layers on MVVM architecture.
🧬 Android DataBinding kit for notifying data changes from Model layers to UI layers on MVVM architecture.

?? Android DataBinding kit for notifying data changes from Model layers to UI layers on MVVM architecture.

May 10, 2022
An Android helper class to manage database creation and version management using an application's raw asset files

THIS PROJECT IS NO LONGER MAINTAINED Android SQLiteAssetHelper An Android helper class to manage database creation and version management using an app

May 15, 2022
A library which will save you a lot of time from writing the same intent creation code. it consist of many intent creation codes like Share, Contacts, Email and etc, which you can easily use.

Android-Intent-Library A library which will save you a lot of time from writing the same intent creation code. it consist of many intent creation code

Sep 10, 2021
Abysl Asset Manager is an easy to use library management tool to index and search game assets
Abysl Asset Manager is an easy to use library management tool to index and search game assets

Abysl Asset Manager is an easy to use library management tool to index and search game assets. Features Itch.IO Library Import Hum

May 15, 2022