search

elixir-sqlite / sqlitex

An Elixir wrapper around esqlite. Allows access to sqlite3 databases.
https://hex.pm/packages/sqlitex
120 stars 34 forks source link

Would you consider implementing server side statements #105

Open lud opened 3 years ago

lud commented 3 years ago

Hi,

I do not know if you know the depo elixir package, but it has an interesting feature.

This feature may be implemented in the Sqlitex.Server module.

Basically, you create a statement with a name. So instead of calling Sqlitex.Server.prepare(:example, "SELECT * FROM t") you would call Sqlitex.Server.register_statement(:example, :all_from_t, "SELECT * FROM t").

And now, you could call the statement with Sqlitex.Server.query(:example, :all_from_t)

The point is to liberate the user from the statements bookeeping as users do not need to keep the statement variable around, neither the SQL query binary. Plus, as the statement is only visible in the db server, it cannot be messed with by another process.

What do you think ?

ConnorRigby commented 3 years ago

I like this idea and have never considered it before. I haven't had a ton of time to work on this project lately, but when i get some time i'd love to take a crack at it. I've added the help wanted tag to it, so anyone reading this in the future should feel free to try their hand at implementing it as well.

lud commented 3 years ago

Hi,

I'm glad that you like the idea!

I will give it a try in the near future too, but I was thinking of writing a complete alternative server module instead of modifying the current one. I am not sure I would not break the current one.

lud commented 3 years ago

I made a proof of concept here with a simple statement cache for well-known queries.

It works in transactions too. Though I changed a little bit the way transactions work in my version, but that should not make a a difference if you want to implement the same thing in the current server implementation. It was because at some point I had a throw in some transaction, and I discovered that throws make a genserver's handle_call return early. I was not aware of that and it took me some time to figure that out.