SQL-based callbacks

[teicher]

Hello Axel,
you explained why you didn't implement a post-migrate script (that runs after any and every migration) here:
http://stackoverflow.com/a/7268878/459816
However I do have a great example why we need this: (postgres)
grant select ON all tables in schema public to portalread;
This will grant the permission to all current (!) tables.
when cleanig and rebuilding, or adding new tables, those will not be accessible.
thus, I need to run this script "always".
Good enough ?
Cheers, Tom.

[axelfontaine]

Hi Tom,

This should be easily achievable with #651 and it will be out next week as part of the 3.0 release. :-)

You will then have the possibility to hook into the lifecycle of all operations with before and after method receiving a connection object. In your case, you should then use either afterMigrate() or afterEachMigrate(). The former runs once after all migrations, while the latter runs after each migration.

Cheers
Axel

Closing as duplicate of #651.

[teicher]

Well, I hope I can also hook in from my ant script... :-)

[axelfontaine]

Supported on all clients. You simply need to have the class containing the callback implementation on your classpath.

[teicher]

aargh, so no easy way to just fire the SQL from the build.xml ...

[axelfontaine]

Not yet, in 3.0. But could be considered for 3.1 -> build tool-based callback implementation.

Hmmm... How would this feel?

<flyway:migrate ...>
  <callbacks>
    <!-- Current solution -->
    <callback name="com.mypkg.MyFlywayCallback"/>
  </callbacks>

  <!-- Proposed additional solution for Flyway 3.1 -->
  <callback>
    <!-- All elements optional -->
    <afterMigrate>
        grant select ON all tables in schema public to portalread;
        other statement;
    </afterMigrate>
    <beforeClean>drop ...</beforeClean>
  </callback>
</flyway:migrate>

[teicher]

or maybe even ;-)

<!-- Proposed additional solution for Flyway 3.1 -->
  <callback>
    <afterMigrate sqlFile="postMigrate.sql"/>
    <beforeClean sqlFile="beforClean.sql"/>
  </callback>

[pauxus]

or even better:

<!-- Proposed additional solution for Flyway 3.1 -->
<callback>
  <afterMigrate folder="postMigrate"/>
  <beforeClean folder="beforClean"/>
</callback>

[axelfontaine]

Here is my proposed solution for 3.1:

look in all locations for files named callbackMethod sqlMigrationSuffix (no space in between) and automatically run them at the correct point in the lifecycle. Only one file per callbackMethod is allowed.

Ex. (using the default suffix): beforeClean.sql, afterEachMigrate.sql

Thoughts?

[PeeZu]

According your last comment, what about if we have afterMigrate operation for each major version, for instance:
1.0 folder contains all 1.x migration scripts
2.0 folder contains all 2.x migration scripts
3.0 folder contains all 3.x migration scripts
and so on...

Each major version, representing as folder in our case, need to have after migration operation.

If we recreate from scratch the database, from 1.0 to current (3.0), which after migration callback will be executed ?

[axelfontaine]

@PeeZu Only one sql file per callback method will be allowed at first. This restriction may be loosened in the future if it proves to be a widespread need and we have a good solution to the ordering problem.

Remember, this is just a convenience implementation. You can still implement your own to meet more advanced requirements.

[jdoose]

An annotation in case somebody runs into the same situation as we did:

If the sqlMigrationPrefix is explicitly set to none ("flyway.sqlMigrationPrefix=") the Sql file callbacks do not work:
ERROR: Wrong migration name format: afterEachMigrate.sql(It should look like this: 1_2__Description.sql)

A workaround is to use an sql migration prefix (or the default).

If you, @axelfontaine, think this is a bug I leave it to you to open a new issue.
If it is not a bug, the documentation should be adjusted. If you want we can provide a fix.

[asv]

@axelfontaine, what about the problem @jdoose ? I have exactly same problem with sql callbacks, if sql prefix is not specified (Flyway 3.2.1).

[axelfontaine]

@jdoose @asv This does indeed sound like a bug. Please file a separate issue.

[jdoose]

Done in #1165