iOS - access a single sqlite DB from both JavaScript and FMDB

Question

We've got a problem that only seems to show up on an iOS device, but seems to work fine on the simulator. Here's the problem...

• Our iOS app is Hybrid (Cordova) with some views that are entirely native and others that are entirely web.
• we'd like use the same sqlite db from both codebases.
• In the web we are using the WebSQL api (not a cordova plugin), from the Native iOS side we're using FMDB.
• The database is initially created from javascript and is placed in the App's Library Directory
  • 4.x Dir <AppDir>/Library/WebKit/Databases/file__0/0000000000000001.db
  • 5.x Dir <AppDir>/Library/Caches/file__0/0000000000000001.db
• Whenever the sqlite database is accessed by FMDB, the JS code can no longer run transactions against the database it created.

While there are other similar SO questions out there, I have yet to see one where the DB was to be accessed by both the web and native. Based upon the research I've done so far, it seems this is a sandboxing issue that only shows up on the device. Here is the code we are using to open the database.

NSArray  *libraryPaths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory,
                                                             NSUserDomainMask,
                                                             YES);
NSString *libraryDir   = [libraryPaths objectAtIndex:0];
NSString *databasePath = [libraryDir
                          stringByAppendingPathComponent:@"WebKit/Databases/file__0/"];

NSFileManager *fileManager = [NSFileManager defaultManager];

if (![fileManager fileExistsAtPath:databasePath]) {
    databasePath = [libraryDir
                    stringByAppendingPathComponent:@"Caches/file__0/"];
}

NSString *databaseFile = [databasePath
                          stringByAppendingPathComponent:@"0000000000000001.db"];

if (!static_fmdb) {
    static_fmdb = [FMDatabase databaseWithPath:databaseFile];
    NSAssert(static_fmdb, @"Unable to open create FMDatabase");
}
if (![static_fmdb open]) {
    NSLog(@"Error in %@: Failed to connect to database!\n",
          NSStringFromSelector(_cmd));
}

Note that after this code runs, subsequent calls to the database from the JS side result in the following error: "unable to open a transaction to the database"

Cantillate

Have you tried using the documents directory instead of the library directory? #6907881 – Jerlenejermain

are you closing the connection with FMDB prior to attempting connection from the JS side? – Objection

Answer 1

We were able to get to the desired result here by rolling back migrations on our sqlite database all the way back to before the first migration, effectively giving us a clean database. We also did not delete any of the cache files created by the web view. Its not the worlds best solution, but it seems to work for our use case. If there are any other answers out there, I'd love to hear more about them.

Cantillate