Deprecate standard transaction mechanism in next major release?

[brodycj]

The deprecated Web SQL transaction API described in https://www.w3.org/TR/webdatabase/ has led to some bugs and significant confusion over the past few years. For example:

• TypeError in success callback #409 - TypeError in success callback, fixed with a workaround solution
• plugin would fire more callbacks after an error with no error handler that returns false (fixed with no issue filed)
• Transaction problem after page change, WITH POSSIBLE DATA LOSS #666 - Transaction problem after page change, WITH POSSIBLE DATA LOSS (fixed with a workaround solution in this plugin version)
• Simultaneously Transactions with web workers and main app litehelpers/cordova-sqlite-evmax-ext-workers-legacy-build-free#3 - transaction within a transaction issue with simultaneous transactions in web worker and main app code in that version (workaround is to use database.executeSql and database.sqlBatch instead of database.transaction in both worker and main app code)

ADDITIONAL ISSUES DISCOVERED:

• not possible to support Web SQL transaction API in worker on the Browser platform ref: New premium version with web worker support upon customer demand (under consideration) cordova-sqlite-evcore-extbuild-free#11
• Problem with workaround solution to Transaction problem after page change, WITH POSSIBLE DATA LOSS #666 reported in Transaction problem after page change, WITH POSSIBLE DATA LOSS #666 (comment)
• Database might be busy when close is called ref: Database might be busy when close is called #702

Given these issues I would like to deprecate the db.transaction call in the next major release discussed in #687. Users would be advised to use db.executeSql for SELECT and single-statement changes and database.sqlBatch for multi-statement changes as already documented. These calls would be supported on the "browser" platform in addition to Android/iOS/macOS/Windows.

I am thinking of the following options:

• Require a special setting in sqlitePlugin.openDatabase if the user wants to use the standard db.transaction call
• The db object constructed by the sqlitePlugin.openDatabase call would not support the standard db.transaction call. Instead the user could issue a call such as db.getStandardDatabaseHandle() to get a new object that does support the db.transaction call.

I would like to leave this issue open for feedback from the user community.

ADDITIONAL NOTE: This may go along with be related to the possible redesign discussed in #548.

[abram456]

Require a special setting in sqlitePlugin.openDatabase if the user wants to use the standard db.transaction call!!!! What is that special setting @brodybits ??

[brodycj]

What is that special setting @brodybits ??

Not decided yet.

[kanoshin]

In some cases it is useful to do several actions in one transaction that depend on each other. E.g. read some rows, perform calculation and then update this rows in the same transaction. Looks like new API doesn't allow it.

[mirko77]

I am using db.transaction a lot to be fair, how are we handling backwards compatibility? By using that flag you mentioned? @brodybits

[RhinoLance]

As Chrome doesn't appear to support db.executeSql, this change will remove the ability to develop and test against a browser before moving to a device. That was always the strength of the plugin, in that I could transparently fall back to WebSQL if required.
The issues does state "These calls would be supported on the "browser" platform...", but it doesn't work for me. Am I missing something?

[brodycj]

Thanks guys for the feedback, will not be removed from this plugin version.

[iamjoyce]

@brodybits Does that mean that db.transaction may still be removed in future versions? In that case, is it recommended to just use executeSql where possible instead? Thanks

[brodycj]

@brodybits Does that mean that db.transaction may still be removed in future versions?

Not from this plugin version. May not be supported by a new sqlite plugin based on possible redesign discussed in #548.

[...] is it recommended to just use executeSql where possible instead?

I would recommend db.executeSql() call for read and simple (single-statement) write or db.sqlBatch() call in case of multiple-statement modification. Simpler, more future-proof in case of new sqlite plugin version with redesign discussed in #548.

FYI a promise-based API wrapper is available in https://github.com/brodybits/sql-promise-helper, which provides functionality similar to db.executeSql() and db.sqlBatch() calls.