Search code examples
javascriptsqlitecordovacordova-pluginsfileapi

PhoneGap - Read pre-packaged sqlite database

I've been struggling to get this working for too long. I just started to get into PhoneGap/Cordova. I got the demo app running, installed all official plugins, and added this SQLite plugin to begin work with: https://github.com/brodysoft/Cordova-SQLitePlugin

However, I want the plugin to read a pre-packaged database. Simply put, create an app that works offline and does not need to download an SQLite database when it is run for the first time.

I have been developing for Android for quite some time, and the solution was to deploy a read-only database file in the assets folder, and on first run copy the database file into a different folder, where I can read/write as I please.

So I did a little research and realized that it SHOULD be the same with PhoneGap. Place a binary SQLite database somewhere, so that when built, it will be packaged with the app. And then simply load it with the SQLite plugin I linked here.

Finally, the question! What are the exact steps I have to take to be able to read a file that was packaged with the app?

  1. Where should I put my binary file to ensure that it gets packaged into the app?
  2. How exactly should I initialize the File API? Should I ask for persistent storage using window.requestFileSystem? Or should I get some (not sure which) directory (cordova.file.applicationDirectory) as a DirectoryEntry using window.resolveLocalFileSystemURL? If so, what are the EXACT parameters I should use? I am mostly referring to this: https://github.com/apache/cordova-plugin-file/blob/master/doc/index.md
  3. How do I read the packaged file? Using window.resolveLocalFileSystemURL with the right folder? What should then the parameters be?
  4. File copying should work OK if I get to this point. Just copy from the read-only cordova.file.applicationDirectory into cordova.file.dataDirectory, which is read/write. Right?

I know the questions might be pretty basic, but my countless attempts were unsuccessful and I did not find a tutorial that explains this in-depth (believe me, I googled). Also, the documentation seems insufficient.

Just working with a WebSQL database and load data the DB using a s***load of INSERTs is not a viable option, the database has thousands of records.

Thanks for the help in advance, you will save me a headache or two.

Solution

  • Currently Cordova's standard SQLite Plugin supports Pre-populated Databases, which will copy your database file from www and put it into the right directory. It also supports Android as well as iOS, so you don't need to have different logic for different platforms.

    When you open the database, you can specify that it is pre-populated using createFromLocation: 1 like this:

    var db = window.sqlitePlugin.openDatabase({
    name: "my.db",
    createFromLocation: 1
});

    For iOS, it will first check www and if the file exists, it will copy it to Documents. Keep in mind that it will be backed up by iCloud. If you want to exclude it from iCloud, then add location: 2 on openDatabase as well, which will put your databases in Library/LocalDatabase.

    UPDATE (2016)

    The original Cordova-sqlite-storage project does not support pre-populated db anymore.

    However, this feature just moved to another project from the same creator. Now, use Cordova-sqlite-ext for this purpose.