Starting new SQLite plugin design for Apache Cordova

[brodybits]

Background: Apache Cordova enables people to develop mobile apps for Android, iOS, and other platforms in HTML and JavaScript. I have been maintaining a widely used plugin that provides SQLite storage capability with a JavaScript API. The plugin has a somewhat complex JavaScript API that closely emulates the draft and deprecated Web SQL API.

I am now starting a new SQLite plugin design based on a lightweight SQLite connection management component, with a preview already available in: brodybits/sqlite-batch-connection-support-preview-2020-01

Note that the preview is archived since I am not interested in handling any PRs at this time. Any questions, ideas, or suggestions should be raised here or in a new issue on this project.

The preview was designed to be thread-safe, with exception for a single initialization function that should be run from the main thread. It includes a proof-of-concept demo of a new Cordova plugin that provides many of the batch SQL capabilities like the draft Web SQL API.

Here are the major goals that I would like to achieve, in response to my past experience and some recent user demand:

• end result with a much simpler, easier-to-use API
• able to support multiple connections, working in parallel from multiple background threads
• easy SQLite database access from Java code in a custom Android plugin, as needed to avoid the "multiple SQLite problem" described in these places:
  • https://ericsink.com/entries/multiple_sqlite_problem.html
  • https://www.sqlite.org/howtocorrupt.html#multiple_copies_of_sqlite_linked_into_the_same_application
  • https://www.sqlite.org/faq.html#q5
• less code to be updated in case of some other new features such as named parameters that may be desired in the future
• easy to open a database file from a random location, with the database file path to be determed with help from another plugin such as cordova-plugin-file
• licensing will start with a choice of permissive license such as MIT or commercial license option; it should be possible for me to make it public domain someday in the future (no promise)

Major non-goals:

• Higher-level API ideas such as Promise-based API and fluent API - should be provided by some kind of higher-level wrapper component
• Special support for failure-safe transactions - application or higher-level wrapper component should support failure-safe transactions by using BEGIN, COMMIT, and ROLLBACK statements as needed

I think that the preview with the Cordova plugin demo already fulfills all goals except for running SQL operations in background threads. Access from custom Java code on Android is demonstrated in sample code in the documentation of the preview.

Note that the API of the preview should be considered a draft API and is subject to change when I publish the new plugin.

I am raising this issue as the new step in continuation from a discussion on the existing plugin: storesafe/cordova-sqlite-storage#862

I will probably need a few more weeks to get something ready to replace the existing plugin and am giving priority to paying customers. Please contact [email protected] if interested.

[ClearedFram3]

Do you have any plans to move to Capacitor?

[brodybits]

My apologies for the delay. Apache Cordova is my primary target; Capacitor should be able to run almost any plugin working on Cordova. A major advantage of Cordova is no need for platform artifacts to be committed in the git history.

[gatelli]

Hi, do you have an approximated release date for that ? May I start to use this SQLite plugin expecting the next one will be compatible ? Thanks!

[brodybits]

I am working on something for both Cordova & React Native, should be ready in the next 1-2 weeks or so.

[T3chTobi]

@brodybits are there any news about the new sql plugin for Cordova?

[brodybits]

My apologies for the delays. I may need another 1-2 weeks or so.

[steveetm]

Hey, any update on this?:)

[brodybits]

New, unstable plugin version is here: https://github.com/brodybits/cordova-plugin-sqlite-batch-connection-manager-core-unstable-0x

The native SQLite3 libraries are not committed in master. TODO item is to update the plugin to install native SQLite3 libraries using pod for iOS, Maven for Android. Until then, it is recommended to install from an unstable build release tag, for example:

cordova plugin add github: com/brodybits/cordova-plugin-sqlite-batch-connection-manager-core-unstable-0x#build-2020-06-17-01

The database file path generally needs to be resolved using cordova-sqlite-storage-file; database path is still in unstable path on iOS.

[steveetm]

Thanks for the response! I couldn't yet check it deeply but are there any plans to move from old websql limitations like persist an sqlite transaction more than a tick, essentially making async/await possible in transactions?

[brodybits]

The new plugin API is pretty low-level, and I would generally recommend some kind of a JavaScript wrapper layer. Transactions would be accomplished by using the standard BEGIN, COMMIT, and ROLLBACK statements. A promise-based wrapper API could be used to support async/await.

[brodybits]

The new plugin design has been working together with SQLCipher and PouchDB for RTI (https://www.rti.org). I will still need some time to get it ready for more widespread usage. Here are the components available and delivered so far:

• https://github.com/brodybits/sqlite-batch-connection-core-2020-01 - SQLite batch processing on Android and iOS, including low-level C wrapper module, low-level Java JNI wrapper for Android NDK, native batch processing modules, and demo on Apache Cordova - permissive MIT license
• https://github.com/brodybits/sqlite-fake-crypto-batch-connection-core-unstable-2020-0x - addition of crypto key support, using FAKE CRYPTO sqlite3_key function for testing purposes - permissive MIT license
• https://github.com/brodybits/sqlite-batch-connection-core-pro-free-2020-01-unstable-0x - addition of partial support for u0000 character - GPL v3 or commercial license options
• https://github.com/brodybits/sqlite-fake-crypto-batch-connection-core-pro-free-2020-01-unstable-0x - includes both crypto key support, using FAKE CRYPTO sqlite3_key function for testing purposes, and partial support for u0000 character - GPL v3 or commercial license options
• https://github.com/brodybits/android-database-sqlcipher-crypto-connection-core-glue-jar-build-unstable-0x - build Android NDK SQLCipher JAR, based on https://github.com/sqlcipher/android-database-sqlcipher with net.sqlcipher C++ & Java classes removed, using https://github.com/brodybits/sqlite-fake-crypto-batch-connection-core-unstable-2020-0x for JNI interface
• https://github.com/brodybits/android-database-sqlcipher-crypto-connection-core-glue-jar-pro-free-build-unstable-0x - build Android NDK SQLCipher JAR using https://github.com/brodybits/sqlite-fake-crypto-batch-connection-core-pro-free-2020-01-unstable-0x (GPL v3 or commercial license terms)
• https://github.com/brodybits/cordova-plugin-sqlite-batch-connection-manager-core-unstable-0x - new Cordova plugin, using https://github.com/brodybits/sqlite-batch-connection-core-2020-01 for low-level libraries, requires local build step (unstable build release tags available) - permissive MIT license
• https://github.com/brodybits/cordova-plugin-sqlite-batch-connection-manager-core-pro-free-unstable-0x - new Cordova plugin with partial u0000 character support and workaround for large result memory issue on Android, using https://github.com/brodybits/sqlite-batch-connection-core-pro-free-2020-01-unstable-0x for low-level libraries, requires local build step - GPL v3 or commercial license options
• https://github.com/brodybits/cordova-plugin-sqlcipher-crypto-batch-connection-manager-core-pro-free-unstable-0x - new Cordova plugin with SQLCipher, including partial u0000 character support and workaround for large result memory issue on Android, requires local build step (unstable build release tags available) - GPL v3 or commercial license options
• https://github.com/brodybits/cordova-sqlite-storage-file - separate plugin to resolve SQLite database file path in a similar manner to cordova-sqlite-storage - permissive MIT license
• https://github.com/brodybits/cordova-sqlite-plugin-legacy-object-wrapper - wrapper to use the new Cordova plugin with cordova-sqlite-storage API, requires https://github.com/brodybits/cordova-sqlite-storage-file , delivered to support use with PouchDB - GPL v3 license

The following patch is required in https://github.com/brodybits/cordova-sqlite-plugin-legacy-object-wrapper to support SQLCipher with the password key option:

index 12af61b..c2ecbbe 100644
--- a/cordova-sqlite-plugin-legacy-object-wrapper.js
+++ b/cordova-sqlite-plugin-legacy-object-wrapper.js
@@ -224,6 +224,7 @@
           console.log('path: ' + path);
           opts = {
             fullName: path,
+            key: _this.openargs.key || '',
             flags: 6
           };
           return window.sqliteBatchConnectionManager.openDatabaseConnection(opts, function(id) {

Some loose ends:

• Some of the components were marked "unstable-0x" due to the potential of rebasing within the master branch during development, storing existing work in "save" branches before rebasing.
• cordova-sqlite-storage-file still needs to support LocalDatabase directory that is used by cordova-sqlite-storage on iOS & macOS ("osx") platforms
• Testing with Capacitor
• React Native support
• It would be ideal to make some of the low-level components available as Gradle references for Android & pod references for iOS.

[brodybits]

I would like to better understand this comment if it may affect using an open SQLite connection from a thread pool: https://sqlite.org/forum/forumpost/f9d10599b6

[brodybits]

I have renamed the project for 2021 and pushed some updates to support named parameters (on Android, iOS, and macOS).