Hi Ronald,

Thanks for your message. I'm sure you'll like jOOQ even more after my
answers ;-)

Comments inline

2018-04-11 13:31 GMT+02:00 Ronald van Raaphorst <rvraapho...@gmail.com>:

> Hi,
> We're experimenting with JOOQ and we're really pleased with what it can do
> so far :)
> We have the following use case though, and I wonder if they are or might
> be supported:
> 1) Compare a schema in the database against a defined schema in a file
> Sometimes when we deploy to staging or production, we 'forget' that a
> table or field has changed.
> (We build our queries dynamically using JOOQ steps; queries are not hard
> coded in our code so some changes might not be caught using compile time
> checks)
> I'd like to include a check at application startup to verify that the
> connected database has the same tables, fields, and keys defined as defined
> in a certain file.
> I thought the xml representation of the information schema could be used.
> Scenario:
>   a) Load the XML and create a schema out of it.
>   b) Dynamically load all meta information about a certain database schema
>   c) Compare both and log differences

The comparison isn't implemented (yet) in jOOQ, but jOOQ has many ways for
you to do that manually, or automatically:

   - The most complete approach would be to run the code generator again
   and compare the version that you're getting on a specific schema with the
   version that is in source control. This is a good approach to do manual
   comparisons, as it also includes vendor-specific data types, stored
   procedures, packages, and other object types that are not necessarily
   available through other meta data API
   - You could use jOOQ's many APIs to work with XML representations of
   your database schema. These include the XMLGenerator [1] and the
   XMLDatabase [2] in jOOQ-meta (the code generator library), or the
   DSLContext.informationSchema() [3] and DSLContext.meta() [4] methods from
   jOOQ (the runtime library)

Let me know if you have any specific questions about the above.

[1] https://www.jooq.org/doc/latest/manual/code-generation/xmlgenerator/
[2] https://www.jooq.org/doc/latest/manual/code-generation/codegen-xml/

2) Dynamically load a schema from the database and serialise the meta data
> to an information_scheme xml file

Use DSLContext.meta() to get the schema (and its tables / columns), and
export that using DSLContext.informationSchema() [3], above. You'll get a
JAXB annotated object that you can serialise to an XML file using JAXB. The
format is here [5]

[5] https://www.jooq.org/xsd/jooq-meta-3.10.0.xsd

> 3) Sometimes, we'd like to create queries based on the meta information
> only, ie without code generation, and use
> JOOQ to generate queries dynamically (Of course, without compile time
> checks).
> The meta information could be loaded from a (xml) file, or extracted from
> the database at runtime.

Yes. Same as above. [3] and [4]

I hope this helps,

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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to