storesafe/cordova-sqlite-storage-help

Prepopulated database documentation fix [options]

brodybits opened this issue · 3 comments

brodybits commented

From discussion in danielsogl/awesome-cordova-plugins#881 there seems to be a bit of confusion about prepopulated databases. In general I can think of the following options:

  • 1: Use cordova-sqlite-ext with createFromLocation option in openDatabase
  • 2: Use cordova-sqlite-storage (or any other version) with a plugin such as cordova-plugin-dbcopy (not supported here)
  • 3: Use File API to copy database from another location (such as www) from JavaScript into the proper location then use cordova-sqlite-storage (or any other version) to open the database
  • 4: Download the sqlite database file using something like FileTransfer, store it in the proper location, and the use cordova-sqlite-storage (or any other version)
  • 5: Fetch the data by REST using AJAX, possibly using a library call such as $.ajax and store it (using cordova-sqlite-storage or any other version)
  • 6: INSERT the data records from another source such as a sqlite dump or a hard coded object in JavaScript
  • 7 (additional option): use https://github.com/dpa99c/cordova-sqlite-porter to import from dump, JSON, etc.

The createFromLocation option discussed in option 1 will only work in cordova-sqlite-ext (and certain other evplus and FUTURE TBD cordova-sqlite-evcore-extbuild-free). The other options will work in all sqlite plugin versions.

In case of options 3 and 4 the proper database location is platform dependent. I will raise an issue on cordova-sqlite-storage to add a function to get the platform dependent database directory path.

I would personally favor options 5, 6 and 7 since they would work the cleanest. The downside is that there are issues with large batches that are solved in cordova-sqlite-evcore and cordova-sqlite-evplus plugin versions, with GPL or commercial license options.

ADDITIONAL NOTES:

I can think of 2 major pre-populated database scenarios:

  • i: Populate with default data upon first app startup and store changes from the user. In this case it would be extremely difficult if not impossible to update the pre-populated database upon app upgrade (options 1-3, maybe 6 or 7) or from the server (options 4-5)
  • ii: Populate with "read-only" data upon first app startup and then it would be possible to update the pre-populated database upon app upgrade (options 1-3, maybe 6 or 7) or from the server (options 4-5)

In general it would be much better if storesafe/cordova-sqlite-storage#212 (CREATE database if it does not exist should be optional) would be resolved in the near future.

brodybits commented

I would also favor the following added option (just discovered and added above):

cocowalla commented

I will raise an issue on cordova-sqlite-storage to add a function to get the platform dependent database directory path

+1 for this

I would personally favor options 5 and 6 since they would work the cleanest

In general, I'd agree, and this feels like the most 'natural' and obvious solution for the majority of use cases. But there are times (as the discussion in driftyco/ionic-native#881 ) when using a pre-populated database makes sense. cordova-sqlite-storage seems to already support this scenario (your options 3 and 4), bit I gather this is somewhat 'unofficial'?

brodybits commented

I would personally favor options 5 and 6

Now options 5/6/7 (I just discovered option 7).

But there are times (as the discussion in danielsogl/awesome-cordova-plugins#881 ) when using a pre-populated database makes sense.

Agreed.

cordova-sqlite-storage seems to already support this scenario (your options 3 and 4), bit I gather this is somewhat 'unofficial'?

Not anymore, thanks to the discussion in danielsogl/awesome-cordova-plugins#881. This will be documented when I make the next cordova-sqlite-storage release.

@cocowalla your point does make sense and I will update the documentation of cordova-sqlite-storage to reflect this (along with some other items in #26).

From danielsogl/awesome-cordova-plugins#881 (comment):

Woah as the primary author and maintainer I find that statement confusing! The cordova-sqlite-storage version does NOT implicitly support pre-populated databases (using the createFromLocation option).

@brodybits I find that statement confusing! :) I'm using this method successfully [...]

Got it. So far I had only supported option 1 which does need cordova-sqlite-ext. The discussion in danielsogl/awesome-cordova-plugins#881 basically expanded the definition which I am now happy to accept and support in the documentation.

To address a couple more points in danielsogl/awesome-cordova-plugins#881 (comment):

  • The reason for downloading populated databases (rather than downloading the data and inserting it into a new database created by the client), is that the databases I'm working with are rather large. It's far more efficient for my use case to download a ready-made database

Agreed, and this is the original reason why I had supported option 1 in cordova-sqlite-ext.

  • Is there some reason you would expect this not to work?

The samples in danielsogl/awesome-cordova-plugins#881 do not address the Windows platform.

Due to this discussion I am now thinking to put cordova-sqlite-ext into a "legacy maintenance" (deprecated) status and make a pure JavaScript replacement for option 1 (using cordova-plugin-file).

  • 👍1