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.
