question: how to refactor?

[mikegolod]

The problem I'm trying to solve is the following:

• Every table in my database must have some technical fields: id, date and user (for update and delete)
• I don't whant to copy-paste this column definitions from one createTable to another

How can I factor this peace of "code" for reuse? The only thing I came up with is to define a closure and then call it like this:

createTable(...) {
  techColumns.delegate = delegate
  techColumns('primary_key_constraint_name')
}

But this isn't very ellegant :(

[stevesaliman]

I'm not entirely sure what you're asking, but I think what you are looking for is a way to make sure all tables you create have the same standard columns. Something like this:

changeSet(id: 'myId', author: 'me') {
  createStandardTable(tableName: 'my_table') {
    column(name: 'code', type: 'varchar(50)
  }
}

The magic would have to be in the createStandardTable method. You could define a method like this:

def createStandardTable(map, closure) {
  createTable(map) {
    column(name: 'id', type: int)
    colum(name: 'create_date', type: 'datetime')
   closure.call
  }
}

I'm not sure this would actually do the job, but I think this would be on the right track. I think the method would need to be defined inside the databaseChangeLog block of your main changeset for everything to be linked up properly. If I get some time, I'll try to come up with a working example, but I'm not sure I'll get much free time in the near future.

As for reuse, this solution would work great within a project, but not so well across projects. To do that, you'd probably need to define a custom change and include it in your classpath. In your case, you could probably extend the Liquibase CreateTable change. I haven't played with custom changes yet.

I hope this helps point you in a good direction.

[mikegolod]

I can't define method inside databaseChangeLog :(. Defining it as a clojure didn't work also.

[stevesaliman]

You should be able to put any legal Groovy code a closure. I guess defining another method is not legal in a closure. :-( It makes sense now that I think about it, but it means we'll need to come up with another way.

When I inherited the project, there was a comment in the code that we needed to find a way to support custom changes that take a closure. It looks like that is something I should take another look at.

I'm not sure when I'll get time to look at this, but I'll keep you posted.

[mikegolod]

Thanks! Btw I'm giving a small presentation about liquibase and it's groovy DSL today :)! Should I close this issue?

[ryan-gustafson]

I've been struggling with this issue too. The benefits of Groovy DSL over XML, for me anyway, is that I can better standardize the management of various database structures. For example, I can add a set of bookkeeping columns to a table (e.g. versioning and auditing), or use a naming strategy for FOREIGN KEYs (e.g. FK_TABLE1COL1...COLN_TABLE2_COL1...COLM). The list goes on. I accomplished a bit of this with XML entities, but there is quite a bit they cannot handle (e.g. attributes).

My current strategy is to make all utility closures requiring Map parameters, with a "delegate" passed in. If not, I raise a nice error saying to add it on the call. I then set the delegate, so the DSL usage inside the closure works properly. The general structure is something like this...

def autoSetDelegate(closure) {
   // Utility to setup a closure to chain a delegate found in a provided Map, and give good error messages
}

def addColumnWithForeignKey = autoSetDelegate { params ->
   // This is a custom closure with Groovy DSL statements, delegate is assumed set properly
}

databaseChangeLog {
   createTable {
      addColumnWithForeignKey(delegate: delegate, ...) // Call to custom closure, have to deal with delegate
   }
   ...
   createTable {
      addColumnWithForeignKey(delegate: delegate, ...) // Call to custom closure
   }
}

It would be nice to not have to specify the delegate everywhere. Perhaps, if I could dynamically bind additional closures onto the delegates used by the DSL, they would then be usable throughout the DSL.

Not to muddy the waters, but since we're talking about delegates, it would also be nice to access information from the enclosing DSL blocks. For example, inside the body of a createTable, it can be nice to know, for instance, the tableName it was provided. Currently I either have to cut-n-paste the values multiple times, or introduce a Groovy variables and reference them repeatedly. I'd be happy with being able to it get using via "${tableName}" or maybe namespaced like "${enclosing.tableName}", or if that would be ambiguous maybe "${enclosing.createTable.tableName}" (using GString examples here).

[jrsall92]

You can create templates in most modern IDEs. The solution would be to create a 'createTable' template that you can easily insert with a shortcut. Here's an example for Intellij's Live Templates that can do what you want (in Groovy DSL, you can make something similar in XML as well):

createTable(tableName: '$tableName$', schemaName: '$schemaName$'){        
        column(name: 'id', type: 'int')
        ...
}

I didn't add all of your fields, but you get the idea. You also mentioned that you don't want to be copy-pasting that piece of config everywhere, but I think it keeps things cleaner that way.

[mikegolod]

@jrsall92 sorry, but i disagree. Here is just some thoughts about such template abuse:

• different projects will have different requirements for technical columns (+1 template for each set?)
• how to change 'id' to 'pk' for all those tables (while we can, e.g. early dev stage)?
• what if I don't use IDE because I just hate them?

Well, I don't think templates gonna solve all that, especially problems that @ryan-gustafson mentioned above.

P.S. I mean no offence by this comment

[stevesaliman]

You can't define a method inside a changelog, but you can still define custom methods like this:

org.liquibase.groovy.delegate.DatabaseChangeLogDelegate.metaClass.createStandardTable = { properties ->
  // whatever code you need here
}

databaseChangeLog {
  // standard Liquibase stuff here, and you can use your new method, for example...
  createStandardTable(tableName: 'myTable', schemaName: "mySchema")
}

The key is that it needs to be declared outside the databaseChangeLog section. In my case, I put it in my master.groovy file before anything else. The best part is that once I've defined the method in master.groovy, I can use it in any other included groovy file. Try doing that in XML :smile:

In my case, I wanted to pass a map of properties to my new method, but you could pass anything you wanted, including closures, and your method can do just about anything you'd need. Using the Groovy DSL's source code as a guide, you can probably find a way to make a createStandardTable method that takes the same parameters and closure that createTable does, and create a Liquibase change with the columns from the closure plus the standard columns you need.