Transaction support?

[nikgraf]

I was checking out the API, but seems like transactions are not supported. Do you have plans to add them?

[ospfranco]

I got lazy, react-native-sqlite-storage implements some simple mutex on the JS side

https://github.com/andpor/react-native-sqlite-storage/blob/master/lib/sqlite.core.js#L134

on the SQLite side you only need to execute a BEGIN TRANSACTION and END TRANSACTION statement.

But I'm currently not using the package, so I'm not actively working on adding features to it, if you really need it you are going to have to add it yourself

[nikgraf]

makes sense! thanks for the quick reply

[z0al]

Is the new executeSqlBatch equivalent to transactions?

[ospfranco]

yes, internally it is using transactions

[ospfranco]

Time to stop being lazy and implement transactions for real. Anyone has any idea how to do it? 😁 the ones from sqlite-storage look mighty complex.

[ospfranco]

@EduFrazao the executeSqlBatch does everything on the native side, but a generic callback on the javascript makes sense right?

[EduFrazao]

executeSqlBatch

Today, executeSqlBatch dispatch a bunch of commands inside a implicit transaction, and returns the status and affected rows. Its async counterpart does the same thing in another thread and invokes a callback do dispatch the result.

What is your idea about a generic callback? I think I don't get the idea.

About a transaction API, I have some doubts too:

-- Maybe a transacion object with a mutex lock? This transaction object must be served inside a method invocation (callback), so we can know when the work is finshed and can commit (or rollback) and release the mutex.

-- How to implement this transaction API in a async way? Maybe invoking the callback (on the JS Thread) to collect all the SQL commands and put them inside a vector to dispatch everything? This will work, but only for update querys, and this lead us to another question:
-- How can we support select statements inside the transacion scope and isolation to allow users to change their transaction logic based on this results?

[ospfranco]

This is what react-native-sqlite-storage (and other packages) offer for transactions:

I know that executeSqlBatch does the internal transaction, but it's non-standard from what I see. You might also want to do javascript logic inside of a transaction(? or not? not really sure), so having javascript access inside of the block might be useful.

• Yes, a mutex-like mechanisms are implemented in other libraries here is the link to sqlite-storage again
• If all the work is inside of a callback then it's not really a problem of executing the function body in an async manner, right?
• Didn't think of this, would it really be an issue?

[EduFrazao]

This is what react-native-sqlite-storage (and other packages) offer for transactions:

I know that executeSqlBatch does the internal transaction, but it's non-standard from what I see. You might also want to do javascript logic inside of a transaction(? or not? not really sure), so having javascript access inside of the block might be useful.

• Yes, a mutex-like mechanisms are implemented in other libraries here is the link to sqlite-storage again
• If all the work is inside of a callback then it's not really a problem of executing the function body in an async manner, right?
• Didn't think of this, would it really be an issue?

I think that the only benefit of having transaction support in the JavaScript side (besides the more semantic code) is to able to collect data from the transaction context, that is somehow impossible today. If you run a statement, that fires a trigger that changes anything, you will not be able to fetch this data inside the transaction scope and even rollback based of this data for example. So yes, I think that its a very nice low level api feature :).

Execute the whole body in a async fashion may be challenge, because we need to translate the parans inside the JavaScript thread, execute SQL commands in another thread, and then convert the results to JavaScript objects in the JS thread again to deliver it to the JavaScript API.

I don't know if this can be done keeping a sane transaction block using everything async.
For example:

db.transaction((tx) => {
    tx.executeSQL('insert into x values (?)', [1], (_tx, res) => {
        // To be able to get "res" object on this callback this execution must be dispatched first to the invokeAsync of the jsCallInvoker to translate data, and at this point, as this will be deffered, we can already closed the transaction code, because we cannot (I think) wait fot the invokeAsync conclusion).
        
      // Selecting some data of table Y after a trigger of X alter it.
      tx.executeSQL('select * from y', [], (_tx, res) => {
         // Again, this code can only be executed in a invokeAsync c++ callback.
          if(res.rows.item(0).value > 10) { 
             tx.executeSQL('DELETE FROM Z where ID = ?', [res.rows.item(0).someColumn]);
           } else {
             tx.markForRollback(); // A API to ask for a silent rollback? 
           }
      });
    });
   // How can we know that is safe to commit the transaction here, if we can't ensure that nested executeSQL callbacks have concluded their work? Maybe track them somehow? Its a very cool problem :)
});

With the synchronized API this can be done of course, but we will have the performance problems that we are aware of, in large transactions.

[ospfranco]

I do understand that you have very large transactions, but I believe you are in the minority here, and you can always fallback to the existing async methods. Whereas this is generally applicable to most apps I believe. So I would go for it.

[EduFrazao]

I do understand that you have very large transactions, but I believe you are in the minority here, and you can always fallback to the existing async methods. Whereas this is generally applicable to most apps I believe. So I would go for it.

Sure.. Do you want to start the work for a synchronized transaction support, and later try to work on the async counterpart? I will like to contribute of course :)

[ospfranco]

yeah, I would like to give it a try. But I'm not sure where/how to start. The sqlite-storage approach looks simple(ish) on the surface... I'll try to replicate it and see how far I get