1

db-maintain has the notion of repeatable scripts, such as stored procedures/triggers. When there is a change to such a script it needs to be rolled out again. Flyway seemingly always checks for the filename convention. So to have a repeatable script we might need to rename the file. Is there a more efficient way of doing this?

gazal
  • 222
  • 2
  • 10
  • Just out of curiosity: could you describe your use-case in a bit more detail? – Axel Fontaine Dec 20 '11 at 00:48
  • the idea is to maintain stored procedures and triggers, the same way we maintain code. If there needs to be a change, you just change the file, hence the checksum is different and the script is rolled out into the db again. Ensuring that the script is actually repeatable should of course be upto the developer. eg: in MySQL; the devloper should ensure there is a DROP IF EXISTS statement before a CREATE trigger or procedure block. – gazal Dec 27 '11 at 06:08
  • Thanks. I understand where you are coming from now. Triggers and stored procedures are database objects like all others, and I therefore believe they should follow normal migration patterns. – Axel Fontaine Dec 28 '11 at 08:39
  • IMHO it would probably be a nice idea to consider repeatable scripts separately at least from SCM perspective. Since flyway already uses a checksum of the script file, changes can be detected and repeatable once rolled out again post modification. This way, there's only one script file maintained over time for repeatable units like triggers and stored procedures. – gazal Jan 04 '12 at 07:21
  • I see what you mean. Could you file a request in the Flyway issue tracker outlining your needs? Thx. – Axel Fontaine Jan 04 '12 at 08:04
  • http://code.google.com/p/flyway/issues/detail?id=207 – gazal Jan 10 '12 at 09:06

2 Answers2

3

Flyway started to support repeatable migrations in version 4.0. Repeatable migrations are reapplied every time their checksum changes and can be maintained as single files in version control.

It is your responsibility to ensure the same repeatable migration can be applied multiple times. This usually involves making use of CREATE OR REPLACE clauses in your DDL statements.

More details here.

Moses Xu
  • 2,140
  • 4
  • 24
  • 35
2

This issue has come up in the Issue Tracker and in this other question.

There is currently no out of the box support for this.

Personally I would

  • package the repeatable actions in a stored procedure or trigger and add it to the DB as part of a regular migration
  • make sure this procedure/trigger gets invoked once per migration after that (could be as little a one-line statement)
  • ensure changes to the procedure/trigger necessary after that also happen as part of regular migrations

If that doesn't do it, feel free to star the Issue and comment with details about your use case.

Update: Repeatable scripts are now fully supported as of Flyway 4.0. See https://flywaydb.org/documentation/migration/repeatable

Community
  • 1
  • 1
Axel Fontaine
  • 34,542
  • 16
  • 106
  • 137