Any plans to implement sqlite3_busy_handler ?

[SteveAlexander]

I recently found myself wanting to implement the sqlite3_busy_handler hook and a busy_timeout, to handle BUSY errors, rather than using a try-except.

https://www.sqlite.org/c3ref/busy_handler.html

Any thoughts on implementing this so that a timeout value and a Dart callback could be used?

[simolus3]

We could expose the callback, but I'm not sure how helpful it would actually be in implementing a timeout.

The callback needs to return synchronously, so it needs to block the calling thread if it wants to wait. We can't support Dart futures that resume sqlite3 asynchronously. What would you want the callback to do that can reasonably be written in Dart?

[SteveAlexander]

I'd like to register a busy_timeout of say 50ms, and make the callback something like the following.

enum BusyHandlerResult {
  noAdditionalAttempts,
  additionalAttempt
}

BusyHandlerResult busyHandler(int numberOfPreviousAttempts) {
  return numberOfPreviousAttempts < 3 ? additionalAttempt : noAdditionalAttempts;
}

I imagine most usage would be based on deciding whether to try again or not based on the number of previous attempts. So maybe there could be a simple API for this to set a Duration timeout and number of attempts, and not need a Dart callback?

[simolus3]

I'd like to register a busy_timeout of say 50ms, and make the callback something like the following.

The problem is that just setting a busy_timeout will override the busy handler, you can't have both. Calling sqlite3_busy_timeout will internally call sqlite3_busy_handler. So if we then set another handler afterwards it would just reset the timeout.

Now, we could put the thread to sleep in Dart (based on a Duration returned), but it would require additional platform-specific FFI that I don't think should be in this package.