Cordova-sqlite-storage: iOS: How to change the database location and preserve existing data

[hanthomas]

I developed an iOS app that has been storing data in the Documents directory. But in order to comply with Apple's guidelines, I need to switch the location of the database to Library/LocalDatabase. The problem is that if I simply change the code, users will lose their existing data. Is there a simple and reliable way to 1) move the data from Documents to Library/LocalDatabase and 2) remove the database from Documents?

[brodybits]

You can use the File API to accomplish this. An automatic migration function is under future consideration.

[hanthomas]

Is there a reliable way to derive the path to the database file? And, if I move the file to its new location, will the plugin open it?

[brodybits]

Is there a reliable way to derive the path to the database file?

This is already documented, though maybe not as clear as it should be. I think you know that every app has its own directory tree. For the old location you would go to the Documents subdirectory. For the new location you would go to the Library/LocalDatabase subdirectory.

And, if I move the file to its new location, will the plugin open it?

Yes if you use the location: 'default' setting in the sqlitePlugin.openDatabase call as documented.

[brodybits]

I just found an excellent tutorial for the File API at: http://www.tutorialspoint.com/cordova/cordova_file_system.htm

I hope to make this easier someday.

[hanthomas]

@brodybits - Thanks for pointing me in the right direction.

[paredesivan]

@brodybits hello. i have a similar problem
we have a native android application published in the market. version 2.3.3, it uses a sql database.

the question is that now we want to continue this application, using IONIC, or tear of version 2.3.4, but the idea is that the database persist after update the app.

I sign the app with the same keystore (i actualize it, I tested and all good). but when I make a query to a table in the database it tells me "the table not exists."

This is the code of the app made IONIC
var db = window.sqlitePlugin.openDatabase ({name: 'PideTuTaxi.db' location: 'default'});

var query = "SELECT * FROM estados_aplicacion '";
$ CordovaSQLite.execute (db, query) .then (function (result) {
if (results.rows.length> 0) {
alert ( "found latitude:" + results.rows.item (0) [ 'LATITUD']);
}
else {
alert ( "the company was not found in the database");

}

}, Function (err) {
alert (JSON.stringify (err));
});

do you have idea if they change locations, or something to consider. a way to check that ?. thank you

[brodybits]

@paredesivan that depends on how the previous version of your app created the database. If the previous app used an older version of this plugin you may need to specify something like iosDatabaseLocation: Documents to read the data from the old location.

If you used a different library to create the database:

• determine where the library created the database
• if the database is in a location that is supported by this plugin then you can use iosDatabaseLocation to specify the right location
• otherwise you could use cordova-plugin-file to migrate the database, as described in my earlier comments

[paredesivan]

@brodybits it's doesn't matter that i use android instead ios?

[paredesivan]

@brodybits the previous app is a native android app, it doesn't use this plugin

[brodybits]

@paredesivan : For Android this plugin opens the database from the standard database location as documented at: https://developer.android.com/reference/android/content/Context.html#getDatabasePath(java.lang.String)

If the native app already put the database there then it should just work, assuming you use the correct database name. The plugin will ignore the actual location or iosDatabaseLocation value for Android and Windows.

If the native app put the database somewhere else you have 2 options:

• use the File API to move the database to the standard location
• https://github.com/litehelpers/Cordova-sqlite-evcore-extbuild-free which is available under different license terms provides an androidDatabaseLocation option to specify where the database is stored.

[paredesivan]

@brodybits ok, last question. Where is the standard location? or how i obtain it

[brodybits]

/data/data/<application package name>/databases ref: http://stackoverflow.com/questions/15326455/what-is-the-default-database-location-of-an-android-app-for-an-unrooted-device

will be documented when I get a chance

[paredesivan]

@brodybits thank you. you are a genius!

[mikpalmen]

I just stumbled into this issue when trying to create a new app update (and now with requiring location) on Android it just worked but on Ios everything ended up in Library folders instead of my old documents.. So I created a new function:

- (void) moveOldDB:(NSString *)libDir {
    NSFileManager *fileManager = [NSFileManager defaultManager];

    NSError *error = nil;

    NSString *docDir = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];

    NSArray *contents = [fileManager contentsOfDirectoryAtPath:docDir error:&error];

    for(NSString *docFileName in contents) {
        NSString *docFile = [docDir stringByAppendingPathComponent:docFileName];
        NSArray *parts = [docFile componentsSeparatedByString:@"/"];
        NSString *filename = [parts lastObject];

        if([filename isEqualToString:@"MY_OLD_DB_NAME"]){
            NSString *destFile = [libDir stringByAppendingPathComponent:docFileName];
            BOOL success = [fileManager moveItemAtPath:docFile toPath:destFile error:&error];
            if(success) {
                NSLog(@"fileMoved");
            } else {
                NSLog(@"Error: %@", error);
            }

        }
    }
    [appDBPaths setObject: libDir forKey:@"nosync"];

}

Then altered line 56 and 68:

[appDBPaths setObject: nosync forKey:@"nosync"];

To

[self moveOldDB: nosync];

Dont know if this is a bad example or idea. But it seems to work.

[brodybits]

@mrPalm that looks like a nice workaround, thanks for reporting. Please keep in mind that you would have to support this workaround for a very long time since we cannot be certain when your users will upgrade from the version with the old database location. It also depends on a certain app database name and does not work for an app with multiple database files. You would have to reapply this workaround every time you want to update your app with a newer version of this plugin.

I don't think this was clear enough: the iosDatabaseLocation option allows your app to open from either the old database location or from the new database location. You can use iosDatabaseLocation: 'Documents' instead of location: 'default' to open the database from the old location. I would generally recommend this for existing apps, along with modifying the iOS Xcode project to disable iCloud backups, to deal with upgrades.

If you do absolutely need to move the database to the new location, the recommended solution is to use cordova-plugin-file.

For future consideration is an option for the plugin to automatically migrate the database from the old location. Unfortunately I am busy with 1-2 months of existing backlog.

[mikpalmen]

Yea I noticed that myself and if i have time ill make it more proper by adding another option to the plugin (and send it in) itself and to migrate all from old to new. This was just a one way solution to migrate from the old app to the new. And hence I will for next version make a more sustainable solution to the problem.

[harleybl]

I haven't seen any movement on this thread in a while and it is still open.

Perhaps this is a dumb idea but... rather than using the file to move the database file to a new location and having to know where things are put, could one way be to basically open up 2 databases, one at the old location (e.g. default) and one at the new location (e.g. Documents). Then read from 1 connection and write to the other.

The outcome would be the same either way, but doing it using 2 database connections would allow the developer to work at a slightly higher level of abstraction and perhaps offer the flexibility of being able to back things up to iCloud/Google at the record level. Not sure what the use case would be, but just wondering if that approach would work or if there is some limitation to having SQLLite databases open that would be a technical blocker to this.