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/ [3] https://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#informationSchema-org.jooq.Schema- [4] https://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#meta-org.jooq.util.xml.jaxb.InformationSchema- 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, 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
