SqliteClient > transaction

[michaelpeterlee]

Can someone please provide an example of how to invoke sqlite.transaction(); Only init() and executeSql() are exposed.

I'm attempting to extend sqlite-client.mjs but having trouble getting it to work.

[tomayac]

Paging @etiennenoel who has contributed the SqliteClient.

[etiennenoel]

Can you tell me what you are looking to do exactly? Are you looking for a specific transaction method or just looking to execute a SQL transaction? For the latter, you can use the executeSql method and use the BEGIN TRANSACTION syntax.

For the former, I would need a bit more info of what the intended behaviour is.

[michaelpeterlee]

Thanks for the timely response folks.

Looking for a drop-in replacement for WebSQL for a number of large mature cross-platform Cordova applications.

The beauty of this approach is that you write once and run everywhere; The goal is to develop to a consistent database client interface for Web/iOS/Android/Electron.

Using direct statements is a good approach, however I'll need to refactor a fair amount but that helps for now.

I'll need to revisit how transaction() commits, as I simply pass tx around and tx.executeSql(); do not need to assert commit statement anywhere.

[michaelpeterlee]

The SQL statement approach requires additional logic;
I expect the subsequent BEGIN request to wait and issue a timeout error if transaction does not resolve.

Perhaps that is why they exposed the transaction() member, not sure..

const txConcurrency = async () => {

    const sqlite = new SqliteClient(FILENAME, WORKER_PATH);
    await sqlite.init();

    [1,2].forEach( async () => {
        await sqlite.executeSql('BEGIN IMMEDIATE TRANSACTION');
        await sqlite.executeSql('SELECT COUNT(id) FROM queue');
        await sqlite.executeSql('COMMIT');
    });

}

[etiennenoel]

Can you do this instead?

    [1,2].forEach( async () => {
        await sqlite.executeSql('BEGIN IMMEDIATE TRANSACTION; SELECT COUNT(id) FROM queue; COMMIT;');
    });

[michaelpeterlee]

Nice idea, unfortunately no as application logic exists after starting.
I have a function that recurses attempts to BEGIN, will share very soon.

[michaelpeterlee]

sqlite.executeSql() does not handle error; worker does not return error to callee process.

This example trips the same error, is not caught in transactionStart().

export const txConcurrency = async () => {

    sqlite = new SqliteClient(FILENAME, WORKER_PATH);
    await sqlite.init();

    sqlite.transactionStart = async () => {
        try {
            await sqlite.executeSql('BEGIN IMMEDIATE TRANSACTION');
        } catch (error) {
            if (error.message.indexOf('transaction within a transaction') > -1) {
                return setTimeout(sqlite.transactionStart,100); // NOTE: Recurse
            }
            throw(error);
        }
    };

    [1,2].forEach( async () => {
        await sqlite.transactionStart();
        await sqlite.executeSql('SELECT COUNT(id) FROM queue');
        await sqlite.executeSql('COMMIT');
    });

}

[etiennenoel]

Good point. I'll add explicit support for error throwing. I believe that sqlite behind the scenes is supposed to return the error in the callback, but it's more JS like to throw the error so I'll add support for that, at least for errors thrown.

[michaelpeterlee]

Worker partial as extracted from this project. Will extend for error handling:

executeSql(sqlStatement, bindParameters, callback) {
    return callback(
      this.db.exec({
        sql: sqlStatement,
        bind: bindParameters,
        returnValue: 'resultRows',
        rowMode: this.rowMode,
      }),
    );
  }

[michaelpeterlee]

Pushed as far as I can here, extending SqliteClient and Worker, below.

Errors are not being handled: sqlite3-bundler-friendly.mjs:10492:17

I'm forced to look at another SQLite WASM wrapper for the interim, but let me know if you require further feedback.

App:

sqlite = new SqliteClient(FILENAME, WORKER_PATH);
await sqlite.init();
const _executeSql = sqlite.executeSql.bind(sqlite);
sqlite.executeSql = (sql:string, params?:[]) => new Promise( async(resolve, reject) => {
        try {
            const result = await _executeSql(sql, params);
            resolve(result);
        } catch (error:any) {
            reject(error);
        }
    });

Worker:

executeSql(sqlStatement, bindParameters, success, fail) {
    try {
      const result = this.db.exec({
        sql: sqlStatement,
        bind: bindParameters,
        returnValue: 'resultRows',
        rowMode: this.rowMode,
      });
      return success(result); 
    } catch (error) {
      return fail(error); 
    }
  }

[sgbeal]

FWIW...

i can't speak to anything in that stack trace higher than OpfsDb.exec() (which i implemented, along with everything below it in that stack trace) but the whole core sqlite3 API is intended to be used 100% synchronously because it's a thin wrapper around the 100% synchronous C API. Attempts to make it behave asynchronously will be akin to ice-skating uphill.

It's my understanding that there are sqlite wrappers which are designed to be used asynchronously. This is not one of them and it won't be until/unless the underlying C library is remodeled to work asynchronously. The only vague concession to that in the core project's code is the ability to run SQL via a parallel Worker using postMessage(), but that introduces its own hiccups with transactions because getting their timing right with async calls is awkward.

[michaelpeterlee]

I appreciate the detail, thanks sincerely.

The specified App/Worker code [prior] actually does return the error even though it shows as unhandled in the Javascript console.

My recursive transactionStart() attempt resolves, however the callee never progresses further, which is most unusual.
As a result, I've dropped developing this further.

App code (full):

const sqlite = new SqliteClient(FILENAME, WORKER_PATH);
await sqlite.init();
 
const _executeSql = sqlite.executeSql.bind(sqlite);

sqlite.executeSql = (sql, params) => new Promise( async(resolve, reject) => {
        try {
            const result = await _executeSql(sql, params);
            resolve(result);
        } catch (error:any) {
            reject(error);
        }
});

sqlite.transactionStart = async () => {

    const attempt = async (resolve, reject) => {

        try {

            await sqlite.executeSql('BEGIN TRANSACTION');
            resolve();

        } catch (error:any) {

            if (error.message.indexOf('transaction within a transaction') > -1) {
                setTimeout(() => {
                    attempt(resolve, reject);
                }, 100); // NOTE: Recurse
            }

            reject(error);

        }
        
    };

    return new Promise((resolve, reject) => attempt(resolve, reject));

};

const txConcurrency = async () => {
        
    [1,2].forEach( async () => {
        await sqlite.transactionStart();
        await sqlite.executeSql('SELECT COUNT(id) FROM queue');
        await sqlite.transactionEnd();
    });

};

txConcurrency();

[michaelpeterlee]

For completeness, I also changed sqlite-client.mjs.executeSql() so it would return error, below:

return new Promise(async (resolve, reject) => {
      this.sqliteWorker.executeSql(
        sqlStatement,
        bindParameters,
        Comlink.proxy((rows) => {
          return resolve(rows);
        }),
        Comlink.proxy((error) => {
          return reject(error);
        }),
      );
});

[tomayac]

Obsoleted via #45.