Re: Ease the integration with Liquibase

[Lukas Eder]

Hello

we use JOOQ/Liquibase for database access abstraction and they work quite fine. The only minor annoyance that both use own table metadata info and type system and we continuously have to convert/maintain consistency between the two representation (and a third one, which is our legacy stuff :) It would be really grate if the two system can be merged.

"Merging" sounds easy, but it isn't :-) Consider merging licenses, copyrights, ownership, responsibility, mission statements, etc. etc.

So in a more realistic scenario, the two tools could improve cooperation. Could you be specific about your use-cases? Where do you run into trouble, when meta data info is duplicated? How could this be improved (on the jOOQ side, on the Liquibase side?)

Cheers

Lukas

[Gaboo]

Hi Lukas,

"Merging" sounds easy, but it isn't :-) Consider merging licenses, copyrights, ownership, responsibility, mission statements, etc. etc.

Sorry, for the confusion: by merging I just mean the merging of the metadata/connection handling (or come up with some common interface). 

So in a more realistic scenario, the two tools could improve cooperation. Could you be specific about your use-cases? Where do you run into trouble, when meta data info is duplicated? How could this be improved (on the jOOQ side, on the Liquibase side?)

We use both tool to create an abstraction over the database specific syntax. We cannot generate classes with JOOQ (as schema is on the fly constructed) and we do not use change-set management feature of the liquibase.

One of the usage scenario: we import csv like files into a database:

-- process the file: metadata like column names, types (and length), table names are inferred

-- the table is created with the help of liquibase

--- we convert column names to liquibase.database.structure.Column and use the its TypeConverter class to get the right type mapping

--- then we create the table

-- the content is loaded by jooq

--- we convert column names to org.jooq.Field and lookup the type representation in org.jooq.impl.SQLDataType 

--- then we load the data

Cheers, Gaboo

[Lukas Eder]

"Merging" sounds easy, but it isn't :-) Consider merging licenses, copyrights, ownership, responsibility, mission statements, etc. etc.

Sorry, for the confusion: by merging I just mean the merging of the metadata/connection handling (or come up with some common interface). 

I think that a common interface will require a couple of "organisational merging" first as a common interface is a contract that multiple parties will have to adhere to. Besides, I have some doubts that such an interface will be easy to evolve. Sooner or later, a common interface between jOOQ, QueryDSL, JPA, Liquibase, Flyway, etc. would be "useful", too.

But don't get me wrong, I'm sure there is potential for writing adapters for a better integration! Specifically, once such an integration is started, lots of new feature requests and ideas might arise, for jOOQ as well.

 

So in a more realistic scenario, the two tools could improve cooperation. Could you be specific about your use-cases? Where do you run into trouble, when meta data info is duplicated? How could this be improved (on the jOOQ side, on the Liquibase side?)

We use both tool to create an abstraction over the database specific syntax. We cannot generate classes with JOOQ (as schema is on the fly constructed) and we do not use change-set management feature of the liquibase.

One of the usage scenario: we import csv like files into a database:

-- process the file: metadata like column names, types (and length), table names are inferred

-- the table is created with the help of liquibase

--- we convert column names to liquibase.database.structure.Column and use the its TypeConverter class to get the right type mapping

--- then we create the table

-- the content is loaded by jooq

--- we convert column names to org.jooq.Field and lookup the type representation in org.jooq.impl.SQLDataType 

--- then we load the data

So from what I understand, there is a bit of potential here, in writing adapters for these types:

LB Database <-> jOOQ Executor (previously, jOOQ Factory)

LB ForeignKey <-> jOOQ ForeignKey

LB PrimaryKey / UniqueConstraint <-> jOOQ UniqueKey

LB Schema <-> jOOQ Schema

LB Sequence <-> jOOQ Sequence

LB Table / View <-> jOOQ Table

LB Column <-> jOOQ Field

LB DataType <-> jOOQ DataType

LB TypeConverter <-> jOOQ Converter

In a first step, I'd suggest that a Liquibase adapter for jOOQ would be written. This would cover your use-case, as the master meta-information is managed by Liquibase. This could then be further evolved into a "both-ways" adapter solution, if possible. I have filed a feature request for this:

https://github.com/jOOQ/jOOQ/issues/2286

I'll evaluate feasibility and usefulness of such an addition for jOOQ 3.1. I'll keep you posted. Any further implementation hints, partial contributions are very welcome, of course

Cheers

Lukas

[Lukas Eder]

Some update on this integration...

 

So in a more realistic scenario, the two tools could improve cooperation. Could you be specific about your use-cases? Where do you run into trouble, when meta data info is duplicated? How could this be improved (on the jOOQ side, on the Liquibase side?)

We use both tool to create an abstraction over the database specific syntax. We cannot generate classes with JOOQ (as schema is on the fly constructed) and we do not use change-set management feature of the liquibase.

One of the usage scenario: we import csv like files into a database:

-- process the file: metadata like column names, types (and length), table names are inferred

-- the table is created with the help of liquibase

--- we convert column names to liquibase.database.structure.Column and use the its TypeConverter class to get the right type mapping

--- then we create the table

-- the content is loaded by jooq

--- we convert column names to org.jooq.Field and lookup the type representation in org.jooq.impl.SQLDataType 

--- then we load the data

So from what I understand, there is a bit of potential here, in writing adapters for these types:

LB Database <-> jOOQ Executor (previously, jOOQ Factory)

LB ForeignKey <-> jOOQ ForeignKey

LB PrimaryKey / UniqueConstraint <-> jOOQ UniqueKey

LB Schema <-> jOOQ Schema

LB Sequence <-> jOOQ Sequence

LB Table / View <-> jOOQ Table

LB Column <-> jOOQ Field

LB DataType <-> jOOQ DataType

LB TypeConverter <-> jOOQ Converter

In a first step, I'd suggest that a Liquibase adapter for jOOQ would be written. This would cover your use-case, as the master meta-information is managed by Liquibase. This could then be further evolved into a "both-ways" adapter solution, if possible. I have filed a feature request for this:

https://github.com/jOOQ/jOOQ/issues/2286

I'll evaluate feasibility and usefulness of such an addition for jOOQ 3.1. I'll keep you posted. Any further implementation hints, partial contributions are very welcome, of course

I have added an initial implementation here:

https://github.com/jOOQ/jOOQ/tree/master/jOOQ-liquibase

This module will not be released with jOOQ 3.0.0, but it is already available on Github master.

There is not much that these jOOQ Adapters can discover from Liquibase metamodel artefacts, as the only sensible artefact is the "DatabaseSnapshot", an object holding a view of all relevant database objects in the Liquibase model. Unfortunately, this snapshot doesn't evenly distribute data for navigation. For instance, the PrimaryKey type references its Table, but the Table doesn't reference the PrimaryKey type. I will create a couple of feature requests at Liquibase, to improve their model.

In the mean time, I'd be glad if you could have a look at the jOOQ-liquibase implementation draft, and give me some feedback

Cheers

Lukas

[kailas...@gmail.com]

Hey can i use liquibase now or still the support is not available, i need that .. so should i integrate liquibase separately then ?

On Wednesday, February 27, 2013 5:58:55 AM UTC-6, Gaboo wrote:

Hi, 

[Lukas Eder]

Hello,

We have attempted some initial implementation of an integrated API with Liquibase, although we found that the integration would only add little value to our users. This project has effectively been cancelled. This doesn't mean that you cannot use jOOQ and Liquibase in a single project. We have a couple of customers doing that, in fact.

So to answer your question:

2014-06-25 23:39 GMT+02:00 <kailas...@gmail.com>:

so should i integrate liquibase separately then ?

Yes, a "separate" integration would probably be the way to go

Best Regards,
Lukas

[Kailash]

thanks i will give that a try today

Sent from my iPhone

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/CQv7i4jDyCc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Stanislas Nanchen]

Hello everyone,

We are successfully using Liquibase and Jooq together. Ping me if you need any help.

Cheers. stan.

[a...@ajbrown.org]

Stan, 

Can you provide some insight on how you're using Liquibase and Jooq together?  I'm about to step into this for a project, and would love to get a head start from someone who's already tackled it.

[kailash Joshi]

We have a liquibase configuration for creating the database, we are executing the liquibase using spring after the context initialization. 

Only issue is you have to execute the liquibase beforehand so that you can generate the corresponding model/interface/dao/record using jooq.

So usually I execute the liquibase using unit test and then generate the jooq code.

Let me know if i can help in something else  

[Lukas Eder]

Hi guys,

This is about jOOQ+Flyway instead of jOOQ+Liquibase, but I'm sure you may find some similar principles. I've actually recently blogged about using the two frameworks together in a Maven setup:

http://blog.jooq.org/2014/06/25/flyway-and-jooq-for-unbeatable-sql-development-productivity

There's also a sample project, which we're maintaining, showing how this integration can be done:

https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-flyway-example

Hope this helps,

Cheers

Lukas

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Stanislas Nanchen]

Hi!

Our Webapp has the constraint that it must work again HSQLDB (for offline use) and Oracle (for online use). This constraint has the benefit that we can use an HSQLDB to generate the Jooq files during our build process. Our build process does the following (we use gradle).

1. Create a file based HSQLDB database.

2. Run the liquibase file on it

3. Run jooq on the HSQLDB.

Some Remarks:

1. We are currently using 3 schemas. The schemas have standard names, lets say A, B and C. In HSQLDB, they are named like that. For Oracle, we use the Schema Mapping functionality.

2. We use liquibase variables for schema names: you can "inject" the real schema names from the environment or the java properties resp.

3. We use liquibase variables for types, so that differences between Oracle and HSQLDB are abstracted away.

Does this help?

I shall write some blog article to give more details, I'll try to do it this week.

Cheers, stan.

[Lukas Eder]

Does this help?

I shall write some blog article to give more details, I'll try to do it this week.

That would be awesome!

[Stanislas Nanchen]

The post is online; i hope it helps :)

http://stanislas.github.io/2014/08/23/liquibase-and-jooq.html

[Lukas Eder]

Thank you very much, Stan! We'll refer to your post also from the newsletter and from the www.jooq.org/community website

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.