Saturday, October 19, 2013

Grails database-migration-plugin: DB independent diff files

If you are using Grails database-migration-plugin and your application has to support MySQL as well as Oracle, you have 2 choices currently. As the underlying Liquibase library is currently unable to create real database-agnostic migration files when performing a diff, you can:

  • create 2 different sets of migration files, one for MySQL one for Oracle. Drawback of this is, that this is error prone and anything else than DRY.
  • Convert the created migration files automatically so they are real database agnostic.
Thanks to the Grails database-migration-plugin hooks (when using database-migration plugin version >= 1.3), we can do that automatically on initial start after creating a new migration file. Migration files are only migrated once, and migrated files will be marked with a special comment to indicate conversion.

In changelog.groovy, define all types you want to use for Oracle and MySQL (you can extend that to support other db types, easily):

databaseChangeLog = {
  
  /*
    DATABASE SPECIFIC TYPE PROPERTIES
   */
  property name: "text.type", value: "varchar(50)", dbms: "mysql"
  property name: "text.type", value: "varchar2(500)", dbms: "oracle"

  property name: "string.type", value: "varchar", dbms: "mysql"
  property name: "string.type", value: "varchar2", dbms: "oracle"

  property name: "boolean.type", value: "bit", dbms: "mysql"
  property name: "boolean.type", value: "number(1,0)", dbms: "oracle"

  property name: "int.type", value: "bigint", dbms: "mysql"
  property name: "int.type", value: "number(19,0)", dbms: "oracle"

  property name: "clob.type", value: "longtext", dbms: "mysql"
  property name: "clob.type", value: "clob", dbms: "oracle"

  property name: "blob.type", value: "longblob", dbms: "mysql"
  property name: "blob.type", value: "blob", dbms: "oracle"

  /* DATABASE SPECIFIC FEATURES */
  property name: "autoIncrement", value: "true", dbms: "mysql"
  property name: "autoIncrement", value: "false", dbms: "oracle"


  /* Database specific prerequisite patches */
  changeSet(author: "changelog", id: "ORACLE-PRE-1", dbms: "oracle") {
    createSequence(sequenceName: "hibernate_sequence")
  }

  /* Patch files */  
  include file: 'initial.groovy'

}

Then create a Callback Bean class for database-migration-plugin and register it in resources.groovy:

migrationCallbacks(DbmCallbacks)

Bean:

import liquibase.Liquibase
import liquibase.database.Database
import org.codehaus.groovy.grails.plugins.support.aware.GrailsApplicationAware;
import org.codehaus.groovy.grails.commons.GrailsApplication

class DbmCallbacks implements GrailsApplicationAware {
  private static final String MIGRATION_KEY = "AUTO_REWORKED_MIGRATION_KEY"
  private static final String MIGRATION_HEADER = "*/ ${MIGRATION_KEY} */"
  // DB-Specific types to liquibase properties mapping
  // see changelog.groovy for defined liquibase properties
  Map<String,String> liquibaseTypesMapping = [
          // start with specific ones, then unspecific ones.
          'type: "varchar(50)"': "type: '\\\${text.type}'",
          'type: "varchar2(500)"': "type: '\\\${text.type}'",
          'type: "varchar"': "type: '\\\${string.type}'",
          'type: "varchar2"': "type: '\\\${string.type}'",
          'type: "bit"': "type: '\\\${boolean.type}\'",
          'type: "number(1,0)"': "type: '\\\${boolean.type}'",
          'type: "bigint"': "type: '\\\${int.type}'",
          'type: "number(19,0)"': "type: '\\\${int.type}'",
          'type: "longtext"': "type: '\\\${clob.type}\'",
          'type: "clob"': "type: '\\\${clob.type}\'",
          'type: "longblob"': "type: '\\\${blob.type}\'",
          'type: "blob"': "type: '\\\${blob.type}\'",
          // regEx (e.g. "varchar(2)" to ${string.type}(2)'. Do not add trailing "'", here!
          '/.*(type: "varchar\\((.*)\\)").*/': "type: '\\\${string.type}",
          '/.*(type: "varchar2\\((.*)\\)").*/': "type: '\\\${string.type}",
          // db features
          'autoIncrement: "true"': "autoIncrement: '\\\${autoIncrement}'"
  ]

 void beforeStartMigration(Database database) {
   reworkMigrationFiles()
 }
 private void reworkMigrationFiles() {
    def config = grailsApplication.config.grails.plugin.databasemigration
    def changelogLocation = config.changelogLocation ?: 'grails-app/migrations'
    new File(changelogLocation)?.listFiles().each { File it ->
      List updateOnStartFileNames = config.updateOnStartFileNames
      if (updateOnStartFileNames?.contains(it.name)) {
        // do not convert updateOnStart files.
        return
      }
      convertMigrationFile(it)
    }
  }
 private void convertMigrationFile(File migrationFile) {
    def content = migrationFile.text
    if (content.contains(MIGRATION_KEY)) return
    liquibaseTypesMapping.each {
      String pattern = it.key
      String replace = it.value
      if (pattern.startsWith('/')) {
        // Handle regex pattern.
        def newContent = new StringBuffer()
        content.eachLine { String line ->
          def regEx = pattern[1..-2] // remove leading and trailing "/"
          def matcher = (line =~ regEx)
          if (matcher.matches() && matcher.groupCount() == 2) {
              String replaceFind = matcher[0][1] // this is the found string, e.g. 'type: "varchar(22)"'
              String replacement = "${replace}(${matcher[0][2]})\'"  // new string, e.g. "type: '${string.type}(22)' "
              line = line.replace(replaceFind, replacement)
          }
          newContent += "${line}\n"
        }
        content = newContent
      } else {
        // non-regEx, so replace all in one go.
        content = content.replaceAll(pattern, replace)
      }
    }
    // mark file as already migrated
    content = "${MIGRATION_HEADER} +"\n"+ content
    migrationFile.write(content, 'UTF-8')
    log.warn "*** Converted database migration file ${migrationFile.name} to be database independent"
  }


This for sure can be optimized (e.g. use only regEx definitions in the map and handle if no matcher groups are found, but it does it's job. 

Tested with MySQL and Oracle 11.0.2 XE.


Building 64bit TrueCrypt for OSX

Currently, TrueCrypt binaries are only available for PPC and i386 without any hardware accelleration.
Also, the available binaries are currently under suspect, as nobody knows if they were compiled from the official source code or if they were tampered by someone. (hick..).

A project tries to get funded to audit the TrueCrypt sources and binaries for any hidden backdoors: http://istruecryptauditedyet.com. The german C't magazine tried to rebuild the Windows binaries from the source code and found some suspect differences while comparing the binaries. See here [english translation] [original article in german].

To ensure at least you do not use tampered binaries, you can use this script to generate a 64bit OSX version from the TrueCrypt sources with hardware accellerated AES functions yourself. (Idea and patches see this Blog post).


#!/bin/sh
# Build TrueCrypt on OSX with 64bit and HW acc. AES
# 2013 http://roosbertl.blogspot.com
####
version=7.1a
md5="102d9652681db11c813610882332ae48"
sourcename="TrueCrypt ${version} Source.tar.gz"
####
download_filename="TrueCrypt%20${version}%20Source.tar.gz"
which /opt/local/bin/port &>/dev/null
if [ $? != 0 ]; then
echo "Port seems not to be installed."
echo "Please install www.macports.org, first" 
exit 1
fi
currDir=`pwd`
workDir="$0.$$"
echo "Creating TrueCrypt $version"
mkdir $workDir
trap "echo cleaning up; cd $currDir; rm -rf $workDir ; exit" SIGHUP SIGINT SIGTERM
echo "Getting required Ports.."
sudo port install wxWidgets-3.0 fuse4x nasm wget pkgconfig
sudo port select wxWidgets wxWidgets-3.0
echo " "
echo "Downloading $sourcename"
wget --quiet http://cyberside.planet.ee/truecrypt/$download_filename
echo "Checking md5.."
thisMd5=`openssl md5 < $sourcename | cut -d " " -f 2`
if [ ! "$md5" = "$thisMd5" ]; then
echo "MD5 checksum $thisMd5 does not match expected MD5 checksum $md5"
echo "Either the source file was modified or you tried to download a different version"
echo "FATAL ERROR. Aborting."
exit 1
else
echo "Checksum is ok."
fi
echo "Extracting '$sourcename'"
tar zxf "$sourcename"
cd truecrypt-${version}-source
echo "Getting Patch file.."
wget --quiet http://www.nerdenmeister.org/truecrypt-osx.patch
mkdir Pkcs11
cd Pkcs11
echo "Getting pkcs11 headers.."
wget --quiet ftp://ftp.rsasecurity.com/pub/pkcs/pkcs-11/v2-20/pkcs11.h
wget --quiet ftp://ftp.rsasecurity.com/pub/pkcs/pkcs-11/v2-20/pkcs11f.h
wget --quiet ftp://ftp.rsasecurity.com/pub/pkcs/pkcs-11/v2-20/pkcs11t.h
cd ..
echo "Patching TrueCrypt for 64bit and HW accellerated AES.."
patch -p0 < truecrypt-osx.patch
echo "Compiling..."
make -j4
echo "Compile done."
mv Main/TrueCrypt.app ..
echo "Cleanup.."
cd $currDir
rm -rf $0.$$
echo "Done creating TrueCrypt.app Version: $version"
# end