Replace DirtyNIF execution model with a different mechanism

[warmwaffles]

The dirtynif execution model is okay for most cases, but we have an issue where long running writes need to timeout / be interrupted mid execution and aborted. The only way to do that is to utilize https://sqlite.org/c3ref/progress_handler.html

[warmwaffles]

That may work. Handing back a handle to cancel a running query and doing that transparently.

I would probably still say then that the DBConnection abstraction could be moved to the ecto_sqlite3 repo, it is not really necessary for SQLite3 itself, and then it should be debated if ecto_sqlite3 itself needs DBConnection.

I agree with this. I'll start looking at moving it out of this repository soon. Probably over the holiday break I'll knock it out.

[josevalim]

Concurrent writes block at the sqlite level though and are either successfuly serialized with sqlites busy mechanic or some writes run into busy timeouts and crash.

Apparently sqlite does not automatically serializes it for you, you are respnsible to do it, which pretty much means a DBConnection with a pool of 1 is the way to go. So perhaps this can be closed? More info here: https://www.sqlite.org/rescode.html#busy

[warmwaffles]

Yea sadly at the current state of things, I don't know if I can interrupt the current executing query to cancel it. I haven't had a whole lot of time to dedicate to exploring a way to timeout/cancel executions.

[LostKobrakai]

Yea sadly at the current state of things, I don't know if I can interrupt the current executing query to cancel it.

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

This seems to be an explicit feature of sqlite. Couldn't this be used?

[warmwaffles]

Yes, that's the documentation I was looking at that spurred this ticket.