Cordova-sqlite-storage: Whether to keep separate cordova-sqlite-ext version or not?

[brodybits]

Initial question on Twitter

@brodybits query - why do u separate the SQLite Ext plugin? it seems to have just 2 more features than the main plugin. why not just 1?

— Raymond Camden (@raymondcamden) August 3, 2016

My response on Twitter

@raymondcamden pre-populated db was added due to high demand. I personally favor loading it from the server. Also to try other new features.

— Chris Brody (@brodybits) August 4, 2016

Further discussion on Twitter

@brodybits i get why it was added - but why 2 plugins? why not just one main one?

— Raymond Camden (@raymondcamden) August 4, 2016

Background: what is added in cordova-sqlite-ext?

• pre-populated databases
• REGEXP (_not_ supported for Windows)
• support for _reading_ BLOB fields (_not_ for Windows)
  

  Further response

I would like to keep the separate cordova-sqlite-ext version for the following reasons:

• All features in this main version work for all supported platforms Android, iOS, and Windows, as well as WP8 (supported on Cordova-sqlite-legacy) and a browser platform in case I add it.
• All features in this main version are intended to work with the SQLCipher version. For example, I cannot see how a user could benefit with a pre-populated database with encryption. Also, the SQLCipher for Android build does not contain REGEXP support.
• In general I personally do not favor the use of pre-populated databases and think there are better ways to solve the same problem. However, there has been significant demand for this feature and I can understand that there are some cases where this pre-populated databases can prove to be handy. I hope to find a better general solution such as using an external addon plugin to achieve the same thing.
• I would like to try some other new features in cordova-sqlite-ext and gain some more insight before integrating them with the main branch supported here.

Why I do not favor pre-populated databases

Pre-populated databases add complexity and need more storage space. It may be possible to free some storage space by deleting the original pre-populated database but it would not work if someone wants to delete all changes and start over.

I would favor a solution where the app fetches the data upon startup. The data may be in a format such a database dump, JSON, compressed database, or whatever else. If a user wants to erase all changes and start again, this should really be straightforward.

An exception may be an organization-specific application where they want to install an app on the employees' devices without the extra step of downloading the data. But this could also be solved by using something like a database dump or JSON file.

Finally, there are alternatives such as using cordova-plugin-dbcopy or cordova-plugin-file (though they may be a little tricky to use correctly). For example the cordova-sqlite-plugin-2 documentation describes how to achieve this using cordova-plugin-file.

Could this be done better?

I have seen some others voice confusion about the proliferation of different plugin versions. To be honest, it does cost me quite a bit of time to maintain the different plugin versions. I generally merge the changes from this version into the others then resolve conflicts. I spend the most time testing and retesting different plugin versions and maintaining the documentation.

I can think of the following alternatives to support pre-populated databases:

• Use an external plugin to automatically copy the database from the resource if it does not exist. Maybe in native code or maybe in JavaScript that uses cordova-plugin-file as a dependency.
• A version like cordova-sqlite-ext that includes the implementation of this version as a dependency instead of git merging.

It would also be possible to change the SQLCipher version to include the implementation of this version as a dependency. Unfortunately it would be very difficult for the evcore and other "enterprise" versions to benefit this way due to the differences in implementation.

_P.S. This discussion is open for comments. I cannot promise when I would have a chance to implement any of the possible improvements discussed here due to my current backlog._

[iursevla]

Hi,
The ext plugin saved me. I couldn't load a pre-populated database from cordova-sqlite-storage and the ext version it works flawlessly. I lost almos 2 weeks trying to do this.
I really need this feature because my app will have something like 25K rows uppon app starting and through all the lifecycle of said app.
It's not possible to put everything in one plugin?
Thanks for your hard work.

[brodybits]

I just found https://www.thepolyglotdeveloper.com/2016/08/using-sqlstorage-instead-sqlite-ionic-2-app/#comment-2830426951:

Already talking with the creator but it's supposed to work like this. The storage plugin does not support pre populated databases atm. But the other plugin may dissapear in favor of another approaches like using a database dump or JSON File to create the database.
As seen here: [reference to this issue]

I did NOT mean that the cordova-sqlite-ext plugin would disappear in favor of using a database dump or JSON file. The other plugin was actually created to support the case where it is really needed and will not go away as long as there is a need for it.

At the moment I would favor an idea where this plugin would be used along with an external utility plugin to provide the same functionality for those who need it, kind of like cordova-plugin-dbcopy but better tested and more intuitive.

[iursevla]

Ah ok sorry for the misunderstanding.
Hope more people join the discussion here to give their point of view about this.

[brodybits]

Another idea under consideration would be to include the SQLite3 dependencies in cordova-sqlite-ext as a solution to #477.

_ADDITIONAL NOTE: This is a side comment. It is NOT related to the issue with pre-populated databases under discussion here._

[brodybits]

Some updates:

From discussions in driftyco/ionic-native#881 & litehelpers/Cordova-sqlite-help#26 I have identified a desire for a pure-JavaScript solution for pre-populated databases, with further discussion in litehelpers/Cordova-sqlite-storage#683.

In case I find a solution to litehelpers/Cordova-sqlite-storage#683 the createFromLocation option in cordova-sqlite-ext would no longer be needed. But I am not certain when or if the REGEXP & Base64 features in cordova-sqlite-ext would be integrated with this version.

[brodybits]

Changed my mind: I am now planning to issue a new release with this plugin version combined with cordova-sqlite-ext. One less plugin version to test and support; less risk of developers using invalid combination of cordova-sqlite-storage and cordova-sqlite-ext plugin versions (#719).

[philonor]

@brodybits Any update on when the merged release of cordova-sqlite-storage and cordova-sqlite-ext will be available? README still states September.

[brodybits]

Now planned for December 2018, about to update README.md

[fernandoralha]

Hello, Is this really inserted in the latest versions of cordova-sqlite-storage? Where can I find an example for bd pre-populate with this plugin and not sqlit-ext?

[brodybits]

Changed my mind (yet) again, my apologies. I will likely keep the separate plugin versions for now. I am hoping to make a more flexible redesign as discussed in #862.