Feature: Execute a directory tree of "code migration" scripts for each hook.

[tohagan]

Database migrations can be split into two types ...

1. Schema Changes: Table, Index, Foreign Keys, Constraints
2. Code Changes: Stored Procedures, Functions, Triggers, Views

Example for Code migrations see Flyway's Repeatable Scripts.

• Schema migrations should be executed once and then never changed (they are idempotent). They are well supported by Batis. Thanks!
• Code migrations should be able to be ALL re-executed after ALL up migrations. This is not yet well supported by Batis.
• In development, Code migrations should ideally only be executed if they are updated .
• In pre-release testing and production, Code migrations should ALL be re-execute after ALL schema changes have been applied. This is an important step that ensures that no code is broken by the schema change. It's synonymous with compiler type checking in other languages. If we have many code objects then this can be slow so we ideally don't wish to force this on the developer every time. Developers won't want this happening when testing up or down but you want the option to perform this to check that no code is broken by the change.

To execute these code scripts, the simplest way is to just place them all in a directory and add a feature to execute a directory tree containing SQL scripts to drop/create each code object. To ensure consistent state, execution must be in sorted file name order. This is my feature request.

Code migrations must also be executed in dependency order (assuming we have one script per object). This is a partial ordering so we don't need to number every code script to do this. We simply create numbered sub-directories that correspond to their dependency level. So for example ...

• code/views/01/myview1.sql - Level 01 objects don't have any dependencies.
• code/views/01/myview2.sql
• code/views/02/myview3.sql - Level 02 objects depend on Level 01 objects.
• code/views/03/myview4.sql - Level 03 objects depend on Level 01 & 02 objects.
  etc.

I've implemented this for another migration tool FluentMigrator.

So what am I asking for?

v1 Solution

This the simplest "available now" solution.

• Provide a Javascript or Groovy code sample to execute a directory of SQL using the existing hooks feature
• Document the dependency method I've described above.

v2 Solution

Allow ALL hooks to look for a folder corresponding to the hook name under the scripts folder and execute all SQL in the folder's sub-directories in path name order .

• When performing up and down and when generating scripts using the script command, provide an option to execute all the hooked code scripts.
• Add a command line option to enable All/Updated code scripts to run. I think the default should be Updated. We'd need a means track which are updated.

[harawata]

Hi,

Thank you for sharing your method!

Here is a JavaScript (Nashorn) example.
It assumes simpler directory structure, but you get the idea.

var Arrays = Java.type("java.util.Arrays");
var File = Java.type("java.io.File");
var FileFilter = Java.type("java.io.FileFilter");
var FilenameFilter = Java.type("java.io.FilenameFilter");
var Files = Java.type("java.nio.file.Files");
var Paths = Java.type("java.nio.file.Paths");
var StandardCharsets = Java.type("java.nio.charset.StandardCharsets");
var MessageDigest = Java.type("java.security.MessageDigest");
var DatatypeConverter = Java.type("javax.xml.bind.DatatypeConverter");

// In production.properties, add ':ignoreChecksum=true' to the hook def
var ignoreChecksum = (typeof ignoreChecksum == undefined) ? false : java.lang.Boolean.valueOf('' + ignoreChecksum);

// Get an array of dirs in 'code' dir
var dirs = new File(migrationPaths.getBasePath(), "code").listFiles(new FileFilter() {
  accept: function(item) {
    return item.isDirectory();
  }
});
// Sort by path
Arrays.sort(dirs);
// Get an array of scripts in the dir
Arrays.asList(dirs).forEach(function(dir) {
  var scripts = dir.listFiles(new FilenameFilter() {
    accept: function(dir, name) {
      return name.endsWith(".sql");
    }
  });
  // Sort by path
  Arrays.sort(scripts);
  Arrays.asList(scripts).forEach(function(script) {
    // Read script file as a byte array
    var bytes = Files.readAllBytes(script.toPath());
    // Calculate checksum
    var newChecksum = DatatypeConverter.printHexBinary(MessageDigest.getInstance("MD5").digest(bytes));
    // Assuming checksum is saved in the same dir with an extension.
    var checksumPath = script.getAbsolutePath() + ".md5";
    // Read the saved checksum from the file.
    var oldChecksum = readChecksum(checksumPath);
    // Compare checksum
    if (ignoreChecksum || oldChecksum != newChecksum) {
      // New or updated.
      print('Execute : ' + script);
      var sql = new java.lang.String(bytes, StandardCharsets.UTF_8);
      hookContext.executeSql(sql);
      saveChecksum(checksumPath, newChecksum);
    } else {
      print('Not modified : ' + script);
    }
  });
});

function readChecksum(path) {
  var p = Paths.get(path);
  if (Files.exists(p)) {
    return Files.readAllLines(p, StandardCharsets.UTF_8).get(0);
  }
  return "";
}

function saveChecksum(path, checksum) {
  Files.write(Paths.get(path), checksum.getBytes(StandardCharsets.UTF_8), java.nio.file.StandardOpenOption.CREATE);
}

# development.properties
hook_after_up=js:update_code.js

# production.properties
hook_after_up=js:update_code.js:ignoreChecksum=true

As a side note, I had the impression that it might be better to create a standalone Java app so that you can perform the task at any time (independently of migration).
It should also be possible to invoke the app code from within a hook script (not tested, though).

Document the dependency method I've described above.

As it is an advanced topic, how about a wiki page?
I think you can create a new page here.
Using a few concrete objects (tables, views, etc.) might help us understand the strategy better.

Hope this helps,
Iwao

[tohagan]

Thanks so much for this code sample. A long overdue thanks!
I'm sorry I failed to do a write up for you to explain scripting dependencies.
Will try to get a moment on this in my "spare time" soon.