Thank you very much for sharing your thoughts and for following up after our Twitter conversation - this is indeed very interesting, and I can definitely see how jOOQ can (will eventually) be able to help, here.
Indeed, jOOQ has various representations of your meta model: - jOOQ-meta (this is used for the code generator only, and isn't so powerful. Eventually, this will be replaced by the below) - jOOQ's Catalog, Schema, Table, Field types that model your database o either from the code generator o or hand written o or from org.jooq.Meta (backed by JDBC's DatabaseMetaData) o or from the InformationSchema (JAXB annotated model) o or from the <information_schema> ( https://www.jooq.org/xsd/jooq-meta-3.5.4.xsd) It would definitely make sense to parse DDL statements (and transform the org.jooq.DDLQuery API) into the above representations, whereas the above representations can be transformed back into DDL statements through DSLContext.ddl(). We're not there yet, but once we're able to parse a sufficent amount of commonly used DDL, sure, why not use jOOQ in the way you've mentioned! Thanks again for sharing these very interesting thoughts! Lukas 2017-03-24 19:50 GMT+01:00 <[email protected]>: > Hi, > > Lukas asked me to post my thoughts here on some of the benefits of the > ability for jOOQ to parse DDL, or more to the point, why someone might want > to transform it. Here I outline a few use cases that might work well for > you. > > Preamble > > For a long time now I've worked on applications that've talked SQL > databases, and managing changes for those has been a pain, until we > discovered Liquibase. For the most part it's been great, and what's > especially nice is that most changes can be written in an autocomplete > friendly declarative XML dialect. The benefit of this is that one can use > XSLT or just some plain old XML parsing to transform it. This all sounds > great, but the crappy part is that when you introduce Liquibase to an > existing database, you end up with a lot of tables, columns, indices, > triggers, etc... that must be painstakingly described, and tuned to ensure > they produce a nice fresh copy if you point it at a new database -- at > least this is the "proper way", you don't have to. No one really likes > doing that, so mostly people just get dumps of create table statements, and > shove them inside the escape hatch Liquibase has to just run raw SQL and > call it a day. On one occasion at a previous job we did decide to go > through the effort and made a declarative version instead, it took forever, > but after we were done, then we started noticing a few things. This effort > was inspired by treating the database as an independent network service > (I've blogged about that here: http://saem.blogspot.ca/ > 2015/01/treat-databases-as-yet-another-network.html > > Read Replica Tuning > > First, we could separate out all our indices that were created for OLTP, > and put them in a separate set of migrations only to run on instances that > needed them, we could also do this for some tables that stored temporary > data we didn't care to always replicate. On our read replicas, we could > then have indices tuned for the loads they experience, we could also create > more tables, updated via triggers to maintain summary views that would > never be on our OLTP database. This alone pushed away the need for a data > warehouse or anything fancy for a long time. > > Change Data Capture > > Second, we wanted to setup change data capture to log all changes over > time to our database to a another database with a very similar schema, > except the primary key is now a transaction ID + whatever key it had > before. Doing that transform on our declarative schema was really simple, > and we could derive a new schema easily. > > Unexplored Areas > > I never got a chance to dig deeper but there were a number of areas that I > had on my list to explore: > - logical diffs, rather than text, output code that would migrate towards > one or the other > - detecting breaking changes > - linting (enforce rules like tables must have created and modified > timestamps, etc...) > > Hope that gives an idea of the world of possibility when you can parse and > transform DDL. > > Thanks > > -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
