A Gant Script to Call Hibernate’s SchemaExport

Being new to Grails, I’ve been closely following the User and Dev mailing lists for tips and there was a recent discussion on the User list about accessing or generating the Hibernate schema script. The schema that Hibernate builds is good but typically DBAs need to customize things and the Hibernate output is a good starting point. It’s also useful to view the DDL between builds to monitor database changes.

In a traditional Spring/Hibernate app I use the schemaexport Ant task that comes with Hibernate Tools (this is a wrapper for SchemaExport), or by calling it directly if I need more control than the Ant task provides.

Running SchemaExport programmatically is pretty straightforward – you just have to build a Configuration from your domain classes (using hbm.xml files or annotated classes). But domain classes in Grails are (typically) .groovy files; I looked at the Grails/GORM code but couldn’t figure out how to properly build the Application, so the domain classes weren’t found and the Configuration was empty.

I’ve been reading the Grails source code trying to figure out how things work and I was looking at the code for the command-line scripts (e.g. “grails war”, “grails test-app”, etc.) and realized that I could borrow some setup code from there and get SchemaExport to work. It took a few hours to get going and a few more to distill it down to proper idiomatic Groovy (I am still a Java programmer …).

The code is below but you can download the script here. Some usage notes:

  • Usage: grails [environment] schema-export [generate | export] [stdout] [filename]
  • The action (generate or export), stdout option, and the filename are optional; if ommitted the action is generate, the filename defaults to ddl.sql in the project base dir, and ddl is only sent to stdout if the stdout option is present
  • Example 1: ‘grails schema-export’ generates ddl.sql for the default configuration
  • Example 2: ‘grails prod schema-export stdout’ generates ddl.sql for the ‘prod’ configuration, sending ddl to stdout in addition to the default file
  • Example 3: ‘grails dev schema-export c:/foo/bar/grails-app.sql’ generates the file ‘c:/foo/bar/grails-app.sql’ for the ‘dev’ configuration
  • Example 4: ‘grails prod schema-export export’ exports the schema (drop and create) using the ‘prod’ configuration
  • Example 5: ‘grails prod schema-export export stdout’ exports the schema using the ‘prod’ configuration, echoing the ddl to stdout
import org.hibernate.dialect.Dialect
import org.hibernate.dialect.DialectFactory
import org.hibernate.tool.hbm2ddl.SchemaExport
import org.codehaus.groovy.grails.orm.hibernate.cfg.DefaultGrailsDomainConfiguration
import org.springframework.jdbc.datasource.DriverManagerDataSource
import org.springframework.jdbc.support.JdbcUtils

grailsHome = Ant.project.properties.'environment.GRAILS_HOME'
includeTargets << new File("${grailsHome}/scripts/Bootstrap.groovy")

Properties props = new Properties()
String filename = "${basedir}/ddl.sql"
boolean export = false
boolean stdout = false

def configClasspath = {

  getClass().classLoader.rootLoader?.addURL(new File(classesDirPath).toURL())

  Ant.copy(todir: classesDirPath, file: "${basedir}/application.properties")
  Ant.copy(todir: classesDirPath, failonerror: false) {
    fileset(dir: "${basedir}/grails-app/conf",
           excludes: '*.groovy, log4j*, hibernate, spring')
    fileset(dir: "${basedir}/grails-app/conf/hibernate")
    fileset(dir: "${basedir}/src/java", excludes: '**/*.java')
  }
}

def configureFromArgs = {

  args = args ?: ''
  args.split('\n').each { arg ->
    arg = arg.trim()
    if (arg.length() > 0) {
      if (arg == 'export') {
        export = true
      }
      else if (arg == 'generate') {
        export = false
      }
      else if (arg == 'stdout') {
        stdout = true
      }
      else {
        // assume filename override
        filename = arg
      }
    }
  }
}

def populateProperties = {

  File dsFile = new File("${basedir}/grails-app/conf/DataSource.groovy")
  def dsConfig = null
  if (dsFile.exists()) {
    dsConfig = new ConfigSlurper(grailsEnv).parse(dsFile.text)
  }

  props.'hibernate.connection.username' = dsConfig?.dataSource?.username ?: 'sa'
  props.'hibernate.connection.password' = dsConfig?.dataSource?.password ?: ''
  props.'hibernate.connection.url' =
       dsConfig?.dataSource?.url ?: 'jdbc:hsqldb:mem:testDB'
  props.'hibernate.connection.driver_class' =
       dsConfig?.dataSource?.driverClassName ?: 'org.hsqldb.jdbcDriver'
  if (dsConfig?.dataSource?.dialect) {
    def dialect = dsConfig.dataSource.dialect
    if (dialect instanceof Class) {
      dialect = dialect.name
    }
    props.'hibernate.dialect' = dialect
  }
  else {
    println("WARNING: Autodetecting the Hibernate Dialect; "
    + "consider specifying the class name in DataSource.groovy")
    try {
      def ds = new DriverManagerDataSource(
          props.'hibernate.connection.driver_class',
          props.'hibernate.connection.url',
          props.'hibernate.connection.username',
          props.'hibernate.connection.password')
      String dbName = JdbcUtils.extractDatabaseMetaData(ds,
          'getDatabaseProductName')
      int majorVersion = JdbcUtils.extractDatabaseMetaData(ds,
          'getDatabaseMajorVersion')
      props.'hibernate.dialect' =
        DialectFactory.determineDialect(dbName, majorVersion).class.name
    }
    catch (Exception e) {
      println "ERROR: Problem autodetecting the Hibernate Dialect: ${e.message}"
      throw e
    }
  }
}

target('default': 'Run Hibernate SchemaExport') {
  depends(classpath, checkVersion, configureProxy, packageApp)

  configureFromArgs()

  File file = new File(filename)
  Ant.mkdir(dir: file.parentFile)

  configClasspath()
  loadApp()

  populateProperties()

  def configuration = new DefaultGrailsDomainConfiguration(
      grailsApplication: grailsApp,
      properties: props)

  def schemaExport = new SchemaExport(configuration)
    .setHaltOnError(true)
    .setOutputFile(file.path)
    .setDelimiter(';')

  String action = export ? "Exporting" : "Generating script to ${file.path}"
  println "${action} in environment '${grailsEnv}' using properties ${props}"

  if (export) {
    // 1st drop, warning exceptions
    schemaExport.execute(stdout, true, true, false)
    schemaExport.exceptions.clear()
    // then create
    schemaExport.execute(stdout, true, false, true)
  }
  else {
    // generate
    schemaExport.execute(stdout, false, false, false)
  }

  if (!schemaExport.exceptions.empty) {
    ((Exception)schemaExport.exceptions[0]).printStackTrace()
  }
}

6 Responses to “A Gant Script to Call Hibernate’s SchemaExport”

  1. Jeff Brown says:

    hah! I was just writing this myself. If you don’t mind attaching your code to GRAILS-2760 I may incorporate some of your approach in what we end up using.

    Thanks for the work!

    jb

  2. Burt says:

    Sure, glad to help.

  3. Chad Roberts says:

    Burt,

    I am using 1.0 rc1. I added this line into your script so it could find the packageApp target. Thanks for the script.

    includeTargets << new File(“${grailsHome}/scripts/Package.groovy”)

  4. Manas says:

    This is great! Works like a charm…you are a life saver.

  5. Manas says:

    Another addition would be to get the DDL when JNDI is specified.

  6. Justin Coyne says:

    “sa” is not a good default value for username when you are using postgreSQL. I often don’t use a username for development (e.g. username is current environment $USER). “sa” is way off.

Leave a Reply

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 License.