Home > Uncategorized > Copy Prepopulated SQLite db with Phonegap

Copy Prepopulated SQLite db with Phonegap

If you have an app that needs to access a large amount of offline data, say >2Mb, then you pass what’s sensible to include a json file included in the www folder of a phonegap project, and you need to use a heftier tool, such as SQLite.

One of the first problems you hit, is the fact that Phonegap SQLite plugins such as PGSqlite or lite4cordova require you to have the database file in the /Documents folder of your app, but you can only put the file in the /www folder using xcode.

Therefore, you have to write some ios native code to copy the file from the /www folder to the /Documents folder.

So, in AppDelegate.m, scroll to the function didFinishLaunchingWithOptions

And add the following code  :

    NSLog(@”I’ve started”);
NSString *src = [NSBundle.mainBundle.bundlePath stringByAppendingPathComponent:@”www/dict3.db”];
NSLog(@”%@”,src);
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *dest = [documentsDirectory stringByAppendingPathComponent:@”dict3.db”];
NSLog(@”%@”,dest);
NSFileManager *manager = [NSFileManager defaultManager];
NSError *error;
[manager copyItemAtPath:src toPath:dest error:&error];
if(error)
{
NSLog(@”%@”,[error localizedDescription]);
}

What this does, is that it gets the path to the source file, in my case /www/dict3.db, and a reference to the destination location, which is /Documents/dict3.db then uses NSFileManager to copy the file from one location to another.

Now, that would be all fine, apart from a nasty, that comes in the form of iCloud. It breaks apple’s data storage terms if you put large amounts of data in the Documents folder, that isn’t user-generated. Since apple will try to back this up on the iCloud, and it shouldn’t need to.

So, with a function that I unashamedly took from Stack Overflow (Sorry stack overflow, I still appreciate the trip to San Francisco that you sent me on!)…

– (BOOL)addSkipBackupAttributeToItemAtURL:(NSURL *)URL
{
const char* filePath = [[URL path] fileSystemRepresentation];
const char* attrName = “com.apple.MobileBackup”;
if (&NSURLIsExcludedFromBackupKey == nil) {
// iOS 5.0.1 and lower
u_int8_t attrValue = 1;
int result = setxattr(filePath, attrName, &attrValue, sizeof(attrValue), 0, 0);
return result == 0;
} else {
// First try and remove the extended attribute if it is present
int result = getxattr(filePath, attrName, NULL, sizeof(u_int8_t), 0, 0);
if (result != -1) {
// The attribute exists, we need to remove it
int removeResult = removexattr(filePath, attrName, 0);
if (removeResult == 0) {
NSLog(@”Removed extended attribute on file %@”, URL);
}
}

// Set the new key
return [URL setResourceValue:[NSNumber numberWithBool:YES] forKey:NSURLIsExcludedFromBackupKey error:nil];
}
}

then add the call it like so:

 // Anti-iCloud.
NSURL *destUrl = [NSURL fileURLWithPath:dest];
NSLog(@”%@”,destUrl);
[self addSkipBackupAttributeToItemAtURL:destUrl];

NSLog(@”Good to go!”);

You can use terminal to navigate to the paths shown in the console output, to see that the file, has in fact been copied.

Then, we can open up index.html, and write some JavaScript to see that the database is working.

  document.addEventListener(“deviceready”, init, false);

function init()
{
console.log(“Device Ready.”);
var db = window.sqlitePlugin.openDatabase({name: “dict3.db”, bgType:1});
console.log(“Database open”);
db.transaction(function(tx){
console.log(“Transaction started”);
tx.executeSql(“select ru from dictionary where word=’HELLO’;”,[], function(tx,res){
console.log(res.rows.item(0).ru);
});
});
}

This opens the database using the sqlLitePlugin, creates a transaction, then runs a simple select against the database. Obviously, your SQL statement will be different from mine.

This took me hours to figure out, so I hope it comes in useful to someone!

Advertisements
Categories: Uncategorized
  1. January 14, 2014 at 9:52 am

    Great article !!! You saved me hours ! I’d been searching for this for some time and you saved my day. Thanks !

  2. Thomas Bildsøe
    March 2, 2014 at 10:22 pm

    I get the error: The operation couldn’t be completed. (Cocoa error 516.) when trying to copy the db to another location, have you experienced this?

    BR
    Thomas

  3. Dhahi
    December 2, 2014 at 2:29 am

    Dear Dananos
    I spent more than 3 weeks googling and looking for plugin to manage the SQLite preloaded database file. I managed to work it out with Android since i have excellent skills on Java, but when it came to iOS the solution i found of the plugin could not take to finish the work. Tour article is straight forward and very precise. I appreciate your time and thanks for the nice work. Keep it up.
    Regards
    Dhahi

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: