jdbc driver interface ?

[terefang]

hello

will this library expose a jdbc interface in the future ?

[otoolep]

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.

[bwarminski]

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.

[bwarminski]

This was the attempt so far with the delayed ResultSet population https://github.com/bwarminski/rqlite-java/commit/f74f6fcf9afb059d7bc13aab8b20d4e1e44ffa5f

[otoolep]

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?

[bwarminski]

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.

[otoolep]

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.

[1638234804]

Hello, this is still a question about JDBC. All popular data layer frameworks require jdbc drivers. I have only found one rqlite-jdbc repository: rqlite-jdbc but this library has not been maintained for more than two years. I don’t know if it is a good choice. Is there a clever way for rqlite-java lib to be compatible with this active sqlite-jdbc repository: sqlite-jdbc

[bwarminski]

I was pretty close on it but needed to shift priorities. Interested in collaborating and helping me finish? Iirc it just needed some tests and clarity around how to handle timestamps and locales.

On Fri, Nov 8, 2024, 1:30 AM GFTXWD @.***> wrote:

Hello, this is still a question about JDBC. All popular data layer frameworks require jdbc drivers. I have only found one rqlite-jdbc repository: , https://github.com/WebCommTech/rqlite-jdbc but this library has not been maintained for more than two years. I don’t know if it is a good choice. Is there a clever way for rqlite-java lib to be compatible with this active sqlite-jdbc repository: https://github.com/xerial/sqlite-jdbc

— Reply to this email directly, view it on GitHub https://github.com/rqlite/rqlite-java/issues/15#issuecomment-2463880650, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA2NASV3DZTGQX6AZFGK6NLZ7RK7DAVCNFSM6AAAAABRMZLYGOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINRTHA4DANRVGA . You are receiving this because you commented.Message ID: @.***>

[otoolep]

It would be great to have a better Java client. I'm not a Java programmer, but am happy to answer questions as needed about rqlite -- and support this development.

[otoolep]

It is important to know that rqlite doesn't support traditional transactions, but if the driver can signal to clients that transactions are not supported, then it should be OK. The Go DB library allows a client library to advertise such constraints, for example.

[otoolep]

Finally, if folks can show they are committed to creating a Java library for rqlite, I would be prepared to grant those folks commit rights to this repo so they could maintain the driver going forward.

[1638234804]

I tried rqlite-jdbc lib yesterday, and it is indeed outdated and incompatible with rqlite-java api. I tried to read the source code of both libraries. Interestingly, rqlite-jdbc is also based on sqlite-jdbc, which shows that the idea discussed is feasible. I fixed the outdated code and tested some basic queries, which worked fine. However, when using the jdbc driver in conjunction with the framework, I found that this library still has many potential problems, such as the commonly used java.sql.PreparedStatement.execute() interface is not implemented.

[1638234804]

I was pretty close on it but needed to shift priorities. Interested in collaborating and helping me finish? Iirc it just needed some tests and clarity around how to handle timestamps and locales. … On Fri, Nov 8, 2024, 1:30 AM GFTXWD @.> wrote: Hello, this is still a question about JDBC. All popular data layer frameworks require jdbc drivers. I have only found one rqlite-jdbc repository: , https://github.com/WebCommTech/rqlite-jdbc but this library has not been maintained for more than two years. I don’t know if it is a good choice. Is there a clever way for rqlite-java lib to be compatible with this active sqlite-jdbc repository: https://github.com/xerial/sqlite-jdbc — Reply to this email directly, view it on GitHub <#15 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA2NASV3DZTGQX6AZFGK6NLZ7RK7DAVCNFSM6AAAAABRMZLYGOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINRTHA4DANRVGA . You are receiving this because you commented.Message ID: @.>

Are you rewriting it based on sqlite-jdbc? Normally, you should be able to reuse the value reading and conversion code blocks in sqlite-jdbc, because this has been verified by many people, which can avoid duplication of development artifacts

[bwarminski]

Yes, I was using their type conversions and was in the process of porting their test suite over before life got crazy. https://github.com/bwarminski/rqlite-java/tree/jdbc is the fork. It includes PreparedStatements but not transactions. Since someone other than me is engaging with the idea, I'm happy to pick it up again over the holidays, or you can feel free to fork and do what you'd like. Let me know.

[bwarminski]

One note if you do check it out, tests currently require a running rqlite on localhost. I figured I'd find a way to solve more elegantly than that if it ever came to fruition.