rqlite
jdbc driver interface ?
It would be nice, but unless someone else writes it, it's unlikely to happen soon. I need to re-write the Go client first.
Hello two year old issue --
I've been taking a stab at this over the past few weeks by modeling this after the Vitess JDBC driver. Initially I thought I could do some clever stuff to support limited transactions as long as the caller committed them before inspecting ResultSets but the JDBC API wants you to return rows updated and intermediate results from selects while you're inside a transaction.
I think I can probably pare back what I was doing to simply have it barf when a user attempts a transaction or borrow from the Go implementation and ignore begin statements, but that seems like it could potentially limit its usability in cases where an ORM is in use.
I'll see if I can pull off connecting this to JDBI as a proof-of-concept without the transaction support and that could give us an answer on the feasibility of jdbc.
Were there any long term plans to support some sort of PL/SQL type of logic within transaction boundaries in the main project? Even something like what redis does who those LUA scripts could suffice for situations where someone needs to perform some sort of logic within a transaction.
This was the attempt so far with the delayed ResultSet population https://github.com/bwarminski/rqlite-java/commit/f74f6fcf9afb059d7bc13aab8b20d4e1e44ffa5f
Were there any long term plans to support some sort of PL/SQL type of logic within transaction boundaries in the main project?
What does this mean, exactly? Can you give me an example?
Definitely. This specific issue might be better raised in the main repo, but the friction trying to make this work in JDBC made me think of it. I'm also aware that transactions have limited support right now.
One example would be a system where you are maintaining something that needs to be transferred between two rows atomically, like a bank balance. If you have rules that say that a bank balance can never go below zero, it's hard to model right now because you might want to do something like:
UPDATE bank_account SET balance = balance + ? where account = x;
### If rows affected < 1 (no account exists)
ROLLBACK
### else
UPDATE bank_account SET balance = balance - ? where account = y;
SELECT balance from account x;
### If balance < 0
ROLLBACK;
### else
COMMIT;
Yes, we're probably not running a bank on sqlite alone, but the idea could apply to other situations. This is difficult to do right now, nonetheless.
In my specific case, I'd want to be able to able to introduce this into an existing application that relies on this ability to add business logic into SQL transactions and would have difficulty.
FWIW - I noticed in the server code that requests that require a leader get applied as a command in the raft log, and I'm imagining an extension of this functionality where instead of passing a single query, a user could pass something with logic like a LUA script that would be processed serially in the log. Redis accomplishes this serially with LUA and it seems like a similar approach could be provided as a flexible alternative. Redis has similar constraints where values can't rely on random or external inputs like time.
As you've worked on this, did you have any similar thoughts or alternative approaches? Happy to open this specific idea in the main repo as well if you'd like.
Yeah, obviously missing classic transaction support prevents doing what you want. I have thought about it before, but it's a major, major change to rqlite. I'm not ruling it out but, it would be months of work.
I don't know much about LUA I'm guessing I'd need to add some sort of parser, runtime (or something to execute LUA) etc.