Transaction types

Hi! 👋

Firstly, thanks for your work on this project! 🙂

Today I used patch-package to patch loopback-connector-sqlite3@3.0.0 for the project I'm working on.

The issue i'm facing is that loopback uses enum of isolation levels (below), which doesn't correspond to the sqlite3-supported list of transaction options.

The Loopback enum:

export declare enum IsolationLevel {
    READ_COMMITTED = "READ COMMITTED",
    READ_UNCOMMITTED = "READ UNCOMMITTED",
    SERIALIZABLE = "SERIALIZABLE",
    REPEATABLE_READ = "REPEATABLE READ"
}

Supported SQLITE3 BEGIN ... TRANSACTION is DEFERRED, IMMEDIATE and EXCLUSIVE (see https://www.sqlite.org/lang_transaction.html)

Here is the diff that solved my problem:

diff --git a/node_modules/loopback-connector-sqlite3/lib/transaction.js b/node_modules/loopback-connector-sqlite3/lib/transaction.js
index 580cc28..c348094 100644
--- a/node_modules/loopback-connector-sqlite3/lib/transaction.js
+++ b/node_modules/loopback-connector-sqlite3/lib/transaction.js
@@ -22,6 +24,9 @@ function mixinTransaction(SQLite3) {
       cb = isolationLevel;
       isolationLevel = 'DEFERRED';
     }
+    if (!['DEFERRED', 'IMMEDIATE', 'EXCLUSIVE'].includes(isolationLevel)) {
+      isolationLevel = 'DEFERRED';
+    }
 
     debug('Begin a transaction with isolation level: %s', isolationLevel);
     this._getConnection(function(err, connection) {

This issue body was partially generated by patch-package.

Start with lib/transaction.js and compare its transaction handling with SQLite's BEGIN ... TRANSACTION documentation. Check how the connector handles DEFERRED, IMMEDIATE, and EXCLUSIVE and what happens for LoopBack's isolation-level values. The work is done when SQLite-supported transaction options are handled correctly without using unsupported isolation-level names.