Overview
This guide will attempt to help all of you that would like but cannot avoid adding multiple SQLite databases in your application. Having not found a complete guide online, I was forced to write my own implementation and thus, I also decided to create this guide to share my solution.
Laying the ground
So, let’s say your application will need to import an .sqlite file dynamically, after the production of your application.
This might happen when the user makes an in-app purchase, chooses to download an expansion pack, or simply activates some features. An important aspect of that .sqlite file is that we know its schema beforehand. From where we’re standing, we have two choices as programmers;
- import and integrate that into our own, already implemented internal SQLite database, or
- add it as an external source to our application, as a separate SQLite database.
Let’s analyze the two cases.
Case #1: Integrate the new database in our internal app database
In this case, we have already created the schema into our own database and it is ready to import new rows of data in it. We create an empty TABLE in our SQLiteHelper extension and create the methods in our global DatabaseHelper class.The user receives the .sqlite file, reads the contents and copies them to their own database.
Importing sqlite files to our database.
Pros (+) and cons (-);
Pros (+)
- Reduced complexity; we know it works as we ‘ve done many times before and it’s our safe way
Cons (-)
- Size matters; we have no idea how much data our user is going to store. With great data size comes great read overhead, as nicely explained in this informative stackoverflow post.
- Serious time overhead; it might take a considerable amount of time to copy all the data to our internal database. Also, to delete those if necessary.
- Double size required; by the time we have copied the data, unless we choose to explicitly delete the new .sqlite file, the user is going to be burdened with lots of extra space.
- Exposure of our main database; just imagine your insert operation is somehow interrupted (power, sudden crash, you-name-it). Then you are probable stuck with a half-completed database and possibly unusable data.
Case #2: Adding it as an external source to the application (separate SQL db)
The second case is far more interesting and what my java-oriented guts said I should do, although the hassle I had to go through. The concept in this case goes something like that; no copy operations take place upon receiving of the new database files. We plainly leave the database file there, until the user needs the contents. Then, we are free to access the data, since the data scheme is known beforehand. The only thing missing is the connection with our application. To achieve this, we map the data with the stored file, but we’ll get more into that as you read on.
Linking our database to the additional SQLite files.
Pros (+)
- Scalable; really, there is no limit on how much data the user can store, since we’ll be using the (virtual) external memory for the .sqlite file.
- Speed; no overhead comes with the increase of our stored data. Also, fast data removal
- Clean; no risk of polluting your internal database with unusable data
Cons (-)
- Complexity; you need to walk out of your safe zone for this one. But don’t worry, we got your back with this guide
Coding towards the solution
What we need at this point is the databases’ manipulation classes, as well as an adapter and some methods that will be invoked along with the application.
Database manipulation classes
Well, let’s begin by adding 4 essential files;
DatabaseFiles
InternalDatabaseControl and InternalDatabaseHelper will hold the same functionality that we have used so far in our applications; be there when we need a bigger and more organized place to store data than the SharedPreferences.
ExternalDatabaseControl and ExternalDatabaseHelper will hold the schemas, and all CRUD operations you want to perform on your external, .sqlite formatted, data.
InternalDatabaseHelper
First, we begin by creating a table of contents that will hold information about our external databases. Let’s say we’ll be storing books. In your Internal database you would store any information regarding the book, such as “title”, “description”, “icon”, “price”, et cetera.
However, and here comes the twist, you will also store a field “path” that will wold the external path (ie. in the /sdcard) that you will store the .sqlite file that contains the contents of the book.
/**
* Created by Orestis Gartaganis - ogartaganis@gmail.com.
*/
public class InternalDatabaseHelper extends SQLiteOpenHelper
{
protected static final String TAG = "DataAdapter";
private static final String DATABASE_NAME = "bibliapp.db";
private static final int DATABASE_VERSION = 3;
public static final String TABLE_BOOKS = "table_books";
public static final String COLUMN_BOOK_ID = "_bid";
public static final String COLUMN_BOOK_TITLE = "b_title";
public static final String COLUMN_BOOK_PATH = "b_path";
public static final String COLUMN_BOOK_VERSION_ID = "b_version_id";
public static final String COLUMN_BOOK_DESCRIPTION = "b_description";
public static final String COLUMN_BOOK_ICON = "b_icon";
public static final String COLUMN_BOOK_PRICE = "b_price";
// TODO: Check which fields will be null or not
private static final String DATABASE_CREATE_BOOKS = "create table "
+ TABLE_BOOKS + "("
+ COLUMN_BOOK_ID + " integer primary key autoincrement, "
+ COLUMN_BOOK_TITLE +" text not null, "
+ COLUMN_BOOK_PATH + " text, "
+ COLUMN_BOOK_VERSION_ID + " integer, "
+ COLUMN_BOOK_DESCRIPTION + " text not null, "
+ COLUMN_BOOK_ICON + " text not null, "
+ COLUMN_BOOK_PRICE + " text, "
+ "); ";
public InternalDatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.d(TAG, "onCreate InternalDbManager");
db.execSQL(DATABASE_CREATE_BOOKS);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.d(TAG, "onUpgrade InternalDbManager");
Log.w(InternalDatabaseHelper.class.getName(),
"Upgrading database from version " + oldVersion + " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOKS);
onCreate(db);
}
}
InternalDatabaseControl
We will create the logic for the control of our books’ database here. All CRUD operations go in this point, as you would know if you’ve ever worked with Android SQLite before.
/**
* Created by Orestis Gartaganis - ogartaganis@gmail.com.
*/
public class InternalDatabaseControl {
// Database fields
private SQLiteDatabase database;
private InternalDatabaseHelper internalDbHelper;
private String[] allBooksColumns = {InternalDatabaseHelper.COLUMN_BOOK_ID, InternalDatabaseHelper.COLUMN_BOOK_TITLE, InternalDatabaseHelper.COLUMN_BOOK_PATH,
InternalDatabaseHelper.COLUMN_BOOK_VERSION_ID, InternalDatabaseHelper.COLUMN_BOOK_DESCRIPTION,
InternalDatabaseHelper.COLUMN_BOOK_ICON, InternalDatabaseHelper.COLUMN_BOOK_PRICE};
public InternalDatabaseControl(Context context){
internalDbHelper = new InternalDatabaseHelper(context);
}
public void openGeneralDatabase(){
database = internalDbHelper.getWritableDatabase();
}
public void closeGeneralDatabase(){
internalDbHelper.close();
}
...
I will include methods to retrieve all my books and all my installed books. Why do I need to differentiate? Cause my user might have a list of all the items they can purchase/download and then we need to see which of those they actually have available on their device. All of these are up to your business logic.
public ArrayList<BookPojo> getAllBooks(){
ArrayList<BookPojo>books = new ArrayList<>();
Cursor cursor = database.query(InternalDatabaseHelper.TABLE_BOOKS, allBooksColumns, null, null, null, null, null);
cursor.moveToFirst();
while(!cursor.isAfterLast()){
BookPojo book = cursorToBook(cursor);
books.add(book); cursor.moveToNext();
}
cursor.close();
return books;
}
The difference between those two methods will be that the ones installed are also going to have a path.
public ArrayList<BookPojo> getAllInstalledBooks(){
ArrayList<BookPojo> installedBooks = new ArrayList<>();
// Cursor cursor = database.query(InternalDatabaseHelper.TABLE_BOOKS, allBooksColumns,
// null, null, null, null, null);
// I'd be better off finding those books that have a path...
Cursor cursor = database.query(InternalDatabaseHelper.TABLE_BOOKS, allBooksColumns,
null, null, null, null, null);
cursor.moveToFirst();
while(!cursor.isAfterLast()){
// Checking if book path (column nr 3) is null, that means it's not installed..
if(!cursor.isNull(2)){
BookPojo book = cursorToBook(cursor);
installedBooks.add(book);
}
cursor.moveToNext();
}
cursor.close();
return installedBooks;
}
ExternalDatabaseHelper
Here we will declare the schema of the external .sqlite files we want to import. Remember, as we declared in the beginning of this guide, we need to know the schema of our external database sources beforehand. An example of that would look like this:
/**
* Created by Orestis Gartaganis - ogartaganis@gmail.com
*/
public class ExternalDatabaseHelper extends SQLiteOpenHelper {
private static final String TAG = "ExternalDatabaseHelper";
public static final String TABLE_TOC = "toc";
public static final String COLUMN_TOC_ROW_ID = "rowid";
public static final String COLUMN_TOC_PARENT_ID = "parentID";
public static final String TABLE_CONTENT = "content_content";
public static final String COLUMN_CON_CON_DOC_ID = "docid";
public ExternalDatabaseHelper(Context context, String name, int version) {
super(context, name, null, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.d(TAG, "onCreate ExternalDbManager");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.d(TAG, "onUpgrade ExternalDbManager");
}
}
ExternalDatabaseControl
Similar to the InternalDatabaseControl Class, we are going to include all our CRUD logic in this place. We are going to create all our queries for our external sources here.
/**
* Created by Orestis Gartaganis - ogartaganis@gmail.com
*/
public class ExternalDatabaseControl {
private SQLiteDatabase database;
private ExternalDatabaseHelper externalDbHelper;
private static String TAG = "ExternalDatabaseControl";
private String[] allTocColumns = { ExternalDatabaseHelper.COLUMN_TOC_ROW_ID, ExternalDatabaseHelper.COLUMN_TOC_PARENT_ID, ExternalDatabaseHelper.COLUMN_TOC_TOP_PARENT_ID};
private String[] allContentContentColumns = { ExternalDatabaseHelper.COLUMN_CON_CON_DOC_ID};
public ExternalDatabaseControl(SQLiteDatabase database) {
this.database = database;
}
...
}
External Database Adapter
Now that we’re done with all our database manipulation classes, we still have to see how we connect all these information. What we need, is a helper Class holding all the control operations regarding connecting our internal-external connection. We’ll call that ExternalDatabaseAdapter.
In this, we will hold an array of SQLiteOpenHelper (or ExternalDatabaseHelper) and one of SQLiteDatabase (or ExternalDatabaseControl). The reason why we choose to hold these arrays is to initialize the same number of helpers and control files, as our external .sqlite files we wish to control.
private SQLiteOpenHelper[] mDatabaseManager;
private SQLiteDatabase[] mDatabases;
private static final String TAG = "External Db Adapter";
public ExternalDatabaseAdapter(int booksCount){
mDatabaseManager = new SQLiteOpenHelper[booksCount];
mDatabases = new SQLiteDatabase[booksCount];
}
Thus, our constructor will hold a number retrieved from our internal database, informing us how many books we have, in order to initialize the respective number of helper and control classes.
Then, in our application (or onCreate()) method, we call the following method:
public static void refreshExternalAdapter(){
Log.d(TAG, "Refreshing external adapter .. size of it: "+db.getAllInstalledBooks().size());
externalDatabaseAdapter = new ExternalDatabaseAdapter(db.getAllInstalledBooks().size());
}
Its use is pretty straightforward. We could make it with one copy of helper and control, but we need to keep the option of having multiple of those open, should our functionalities request so.
Apart from the constructor, we also define a series of helper methods to open/close our adapters.
IMPORTANT: In order to keep a consistency in our book references, we have to use a common id between the adapter and the database. So, we sync our internal database id with the array numbering at the point I’m retrieving my book from the internal db:
(exempt from InternalDatabaseControl.java)
private BookPojo cursorToBook(Cursor cursor){
BookPojo book = new BookPojo();
// ************************************** SOS *****************************************//
// I am deducting one from this point because the sqlite rows start from 1,2,.. //
// whereas my externalDatabase array from 0,1,... //
// **************** This is only used in my internal adapter system ****************** //
book.set_bid(cursor.getInt(0)-1);
.....
}
So, after this small but important detail, we continue with the aforementioned adapter methods:
/**
* Checks the database state and throws an {@link IllegalStateException} if database isn't open.
* Should always be used before starting to access the database.
*
* @param bookId of the book, which is the respective row in the internal DB.
*/
public void checkDbState(int bookId) {
if (mDatabases[bookId] == null || !mDatabases[bookId].isOpen()) {
throw new IllegalStateException("The database has not been opened");
}
}
public void closeAll(){
Log.w(TAG, "Received closeAll() command");
for(BookPojo book: BibliApp.db.getAllInstalledBooks()){
// Log.e(TAG, "attempting to close book id, bid: "+book.getBookProductId()+ ", "+book.get_bid());
close(Integer.valueOf(book.get_bid())); // parameter is internal id, ie Order in internal DB
}
}
/**
* Closes the database of the given type.
*
* @param bookId of the book, which is the respective row in the internal DB.
*/
public void close(int bookId) {
Log.d(TAG, "Closing book with book id: "+bookId);
// try {
if (mDatabases[bookId].isOpen()) {
mDatabases[bookId].close();
mDatabases[bookId] = null;
if (mDatabaseManager[bookId] != null) {
mDatabaseManager[bookId].close();
mDatabaseManager[bookId] = null;
}
}
// }catch (NullPointerException n){
// Log.e(TAG, "Caught Null in closing of a book");
// }
}
/**
* @param bookId of the book, which is the respective row in the internal DB.
* @return true if the database is open, false otherwise.
*/
public boolean isOpen(int bookId) {
return mDatabases[bookId] != null && mDatabases[bookId].isOpen();
}
/**
* Opens the default database.
*
* @param bookPojo the pojo describing the book db we want to open
*/
public void open(BookPojo bookPojo) {
mDatabaseManager[bookPojo.get_bid()] = new ExternalDatabaseHelper(BibliApp.getInstance(), bookPojo.getBookPath(), 1);
if (!isOpen(bookPojo.get_bid())) {
mDatabases[bookPojo.get_bid()] = mDatabaseManager[bookPojo.get_bid()].getWritableDatabase();
Log.d(TAG, "got Writable Databse for path: "+bookPojo.getBookPath());
Log.d(TAG, "got Writable Databse for book product id: "+bookPojo.getBookProductId());
Log.d(TAG, "got Writable Databse for book title: "+bookPojo.getBookTitle());
}
}
public ExternalDatabaseControl getExternalDatabaseControl(int bookId){
return new ExternalDatabaseControl(mDatabases[bookId]);
}
Et voila! A use case of this could be:
1) Retrieve a list of objects available to download/purchase.
i. Store the objects in the database, leaving the path as null.
2) User purchases an object.
ii. Store the object to the (virtual) external file system.
iii. Store the path by updating the respective entry in your internal database.
3) Access the object(s)
iv. Initialize the respective number of adapters as the number of your installed objects.
v. When needed, access the object and all its operations you have defined.
Happy Coding!
