Guest Blog Post: Shipping a populated SQLite DB with PhoneGap
This is my second "Guest Blog Post" for the month. Sorry I've been a bit slow on my own content lately! Today's post comes from Scott Buckel. He and I shared a conversation earlier this month about databases and PhoneGap. PhoneGap makes it pretty darn easy to create and work with a database in your application. What isn't so trivial is actually shipping a prepopulated database with the app itself. I wasn't able to help him as much as I'd like - but he worked at it until he came up with a solution. Here is what he discovered.
These instructions are very raw, and not optimized.
- I used this plugin: https://github.com/chbrody/Cordova-SQLitePlugin/
- Copy your sqlite db file to /assets folder of PhoneGap.
- Then, follow instructions here: http://gauravstomar.blogspot.com/2011/08/prepopulate-sqlite-in-phonegap.html to copy the file to native storage. Change this.copy("Databases.db","/data/data/"+pName+"/app_database/");
- Instead of Databases.db, use your database filename.
- Instead of app_database, use "databases"
- You'll probably want to delete the file from /assets, since it is duplicated and no longer needed. My app was double the size it needed to be.
- (Not sure if this step is necessary, I'm getting out of the office for the day). Edit SQLitePlugin.java
- Lines 176, I edited 1==1 so that if statement is always executed. Line 179 I changed this.setStorage(appPackage, false); to this.setStorage(appPackage, true);
- You can then use the following command to open the DB and use it as any other PhoneGap database
- var db = window.sqlitePlugin.openDatabase("[full_database_name_including_extension]", "1.0", "PhoneGap Demo", 200000);
10 hours of work later, I have a working database!
Feel free to use this information on a blog, I feel it would help a LOT of people out. Step #4 is weird, I'm sure there's a "prettier" way to do it. If used, just give Scott Buckel @ Corporate Zen credit please :).
Thanks!
When using the Cordova-SQLitePlugin, it looks in the Documents directory, and Gaurav Tomar's instructions refers to WebKit/Databases directory (which I believe is no longer used because of an iOS 5.1 change).
I found the following works instead:
NSString *databaseName = @"Database.db";
NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *databasePath = [documentPaths objectAtIndex:0];
NSString *databaseFile = [databasePath stringByAppendingPathComponent:databaseName];
NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
NSFileManager *fileManager = [NSFileManager defaultManager];
if ([fileManager fileExistsAtPath:databaseFile] == NO) {
[fileManager copyItemAtPath:databasePathFromApp toPath:databaseFile error:nil];
[fileManager release];
}
Thanks again for pointing me in the right direction!
http://iphonedevlog.wordpress.com/2012/10/05/insta...
or
http://iphonedevlog.wordpress.com/2012/08/21/prepo...
Regards,
Steve Husting
https://github.com/jarlehansen/PhoneGap-SQLitePlug...
package com.rkadukar.database;
import org.apache.cordova.DroidGap;
import android.os.Bundle;
public class MyPhoneGapActivity extends DroidGap {
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
super.loadUrl("file:///android_asset/www/index.html");
}
}
where do I add the code from Gaurav Tomar's site after I make the changes suggested by you
Is there a way for me to download a SQLite file and do SQL operations on that SQLite database file? Here's why I need that. My app will be downloading a huge document, and for searching purpose, I am thinking of implementing the index using the SQLite database file.
Got ideas on how I can do this?