- 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.
