schema export via Vert.x driver instead of JDBC

[aguibert]

Currently the RxPersistenceProvider does not have schema support, which means things like automatic table creation need to be done by JDBC.

It seems a bit awkward to require users to add/configure a JDBC driver if their app is only using RX. In theory we should be able to do all of the same schema generation using RxConnection instead of JDBC Connection.

[DavideD]

It is a bit weird. At the same time I would prefer not to have too many ways to configure the same connection string. In the future a user will probably be able to use ORM with Rx and it might want to use the Rx API only in some cases. It is also nice that we use properties that are already familiar to an ORM user.

[DavideD]

Ah sorry. I've just realized that you weren't talking about the property names.

[DavideD]

Mostly, I'm wondering if there is a use case where one would want to use different configuration parameters. I guess it could happen if the native reactive driver has some specific properties. Anyway, I guess we will have to have similar properties to the ORM ones but with the`jdbc` part removed from the name. And decide what happens if only one type of properties is set.

…

On Fri, May 1, 2020 at 5:53 PM Andrew Guibert ***@***.***> wrote: By the same token I don't think we should over-reach with making things convenient/familiar. For example, right now we support configuring RX with a jdbc protocol URL, which is convenient/familiar but not technically correct since we're not using JDBC. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#104 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEIQ5JHS5PQ7M7APGO2YVLRPL5APANCNFSM4MXDM4CA> .

[gavinking]

Anyway, I guess we will have to have similar properties to the ORM ones but with thejdbc part removed from the name.

I think we should stick with the standard JDBC URL format because:

• it's standard
• it's well-documented by the database vendors
• everyone already knows it
• it makes it easier to configure Hibernate RX and plain Hibernate or plain JDBC at the same time

[aguibert]

@DavideD I may have not properly described the issue in the OP, but how does config parameters come into play for schema gen?

To re-state the intent I had with this issue, I'm suggesting that the automatic drop/create table procedures can be done without needing to involve any JDBC drivers or jdbc-only config (if RX happens to re-use the same properties, that's fine though)

[DavideD]

Ah sorry. I've just realized that you weren't talking about the property names.

Yeah, got it. My bad. I read it too quickly the first time.

It seems a bit awkward to require users to add/configure a JDBC driver if their app is only using RX.

This sentence here made me think you were talking about the fact that we use the properties for the JDBC configuration that already existis in ORM. It's still nice that we talked about it and I'm not planning to change them for now.

[gavinking]

So previously I was arguing that doing the schema export over JDBC was perfectly fine. (What's a reactive schema export?)

However, in phone conversation with @emmanuelbernard and @Sanne, it's come to my attention that this is indeed actually a potential pain point for Quarkus users.

I should have paid more attention to @aguibert when he tried to warn me about this.

So, anyway, this is indeed an issue we should prioritize, though I suspect it can't be done in time for the initial release.

[Sanne]

weird idea - forgive me if it's silly as I don't really know - but wondering if we could wrap the reactive driver into a JDBC compatible adapter, then feed that into the schema gen code?

I guess such work would be overkill if it would just facilitate the schemagen tools, but perhaps there's more uses?

[gavinking]

@Sanne

Well there is an abstraction here, called GenerationTarget. We might be able to implement that, though I'm not 100% sure exactly how one goes about plugging one in.

Failing that, one thing we might be able to do is ask the schema export tool to export a SCRIPT, and then just send the commands one-by-one to the DB.

[gavinking]

We might be able to implement that, though I'm not 100% sure exactly how one goes about plugging one in.

The problem is that the list of these targets seems to be hardcoded, without any obvious way to add a new one. But a small fix to core might be enough to solve that.

[Sanne]

Ok. While this is still not the top priority, I'll defer releasing the next ORM as much as feasible, so in case someone finds the time we can include some more last minute patches.

For reference, remember that performing the release really doesn' take us long: about 20 minutes. But then we need to wait for Maven central synchronization and all that before it can actually be used :(
Which implies we need to release about 24h before a Reactive release is possible.

[gavinking]

I'll set aside some time to attempt this. Perhaps it's super-simple. We'll find out.

[gavinking]

Perhaps it's super-simple. We'll find out.

So, indeed, it was relatively straightforward, but it raised an issue I hadn't considered: Hibernate Reactive no longer has access to JDBC metadata, which does have some consequences, but I don't think they're too terrible. Fortunately Hibernate was written when JDBC metadata was very unreliable, and doesn't really depend on it.

I guess that's OK.

Also it exposed a bug in ForeignKeys where Hibernate Reactive would actually use the JDBC connection to obtain snapshots from the database! I will open a separate bug report for that problem.

[Sanne]

Awesome, thanks!

[Sanne]

Gavin's fix was merged in ORM; schema creation should be doable now.

I suspect automated schema update is a fair bit more complicated though - can we agree that's less important for the first cut?

[gavinking]

Gavin's fix was merged in ORM; schema creation should be doable now.

Thanks.

I suspect automated schema update is a fair bit more complicated though

Oh, much more difficult, since the current implementation is entirely based on JDBC metadata, IIRC.

can we agree that's less important for the first cut?

Yeah, I have no intention at all of working on that at this stage. Nor do I think it's needed.

[gavinking]

@Sanne how long before we get a release of ORM core with that change in it?

[Sanne]

@gavinking we can plan a release on Monday. I wonder though - if there's more changes possibly needed maybe it's best to defer?
We're also working on some other fixes that Quarkus needs - so while we can releas one whenever we like, the later the more fixes.

[gavinking]

@Sanne No hold tight, that comment was made before I discovered the seriousness of #113.

[aguibert]

@gavinking at some point could you write up an issue on Vertx-sql-client outlining what sorts of DB metadata is needed by Hibernate? The Vertx client should have a better metadata API than it currently does someday.

[BillyYccc]

+1 for Andrew! We have an initiative to build a metadata fetching API for Postgres in eclipse-vertx/vertx-sql-client#513. Feedbacks will certainly help us build a better generic API around this.

…

On Thu, May 14, 2020 at 9:58 PM Andrew Guibert ***@***.***> wrote: @gavinking <https://github.com/gavinking> at some point could you write up an issue on Vertx-sql-client outlining what sorts of DB metadata is needed by Hibernate? The Vertx client should have a better metadata API than it currently does someday. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#104 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABJV4JODSAYQT7BG2TWHZI3RRP2JFANCNFSM4MXDM4CA> .

[gavinking]

What the schema management tools needs is access to the information in information_schema.tables and I guess also information_schema.key_column_usage.

[gavinking]

What the schema management tools needs is access to the information in information_schema.tables and I guess also information_schema.key_column_usage.

Note that one option is for it to get that information directly by querying the information_schema. Historically it just the JDBC metadata API for that, in order to abstract away from platform differences.

[gavinking]

This is done!

And it's done in a way that's pretty transparent to the user: if they have a JDBC driver or connection pool set up, then Hibernate ORM will do the schema export over JDBC but iff they don't have a JDBC driver, then schema export over the Vert.x driver will kick in.

[DavideD]

That's awesome!
Thanks a lot

[Sanne]

And it's done in a way that's pretty transparent to the user: if they have a JDBC driver or connection pool set up, then Hibernate ORM will do the schema export over JDBC but iff they don't have a JDBC driver, then schema export over the Vert.x driver will kick in.

just curious, why should it try to use the JDBC approach first? If we have the capability to run it over vertx, why not do it always?

It might get confusing to users if there's different behaviours being triggered just because some unrelated connection is being setup. Also - how do you know if that other connection really points to the same DB?

[gavinking]

just curious, why should it try to use the JDBC approach first? If we have the capability to run it over vertx, why not do it always?

Well if you have regular Hibernate right there set up with a regular JDBC driver why not use it? Reactive schema export isn't a meaningful thing. To make this even work we have to do a totally nasty join() at the end, which Vert.x find quite upsetting.

[gavinking]

how do you know if that other connection really points to the same DB?

Cos it's the same JDBC URL?