Clever Geek Handbook

Span: a common script to run after every migration

I have a general cleanup script that I would like to run after every migration. Is there a good way to run this script after every migration (other than the script itself as being changed on every migration?)

I see that there was pre-and post-migration scripts for Flyway before this question , and the answer at that point was not, not really.

Has the answer been changed at all in the last 1.5 years?

+3


source to share


5 answers


With flyway 3.0 this has changed and callback scripts are now possible. In this situation, the afterMigration.sql file can be used for cleanup.



See http://flywaydb.org/documentation/callbacks.html for details .

+2


source


This hasn't changed. Use any of the suggested workarounds for now.



+1


source


I've looked at the suggestions here and the Pre-and Post-migration scenarios for Flyway and would like to provide a use case that I don't see that might be most applicable (if any). Suppose dba creates a restore point before executing developer-generated migrations.

Right now, with our migration process (no hop), dba creates a restore point before running a set of migrations. Migrations will work fine without a restore point. But if they don't have the correct code (e.g. missing column creation), it is often preferable to roll back to the oracle restore point to avoid downtime and give the developer time to work around the fix.

I don't think a developer should enable a migration that makes this restore point understandable, because: 1. They can forget (this should happen automatically, without developer intervention) 2. There may be different start migrations depending on the state of the schema, so if the one that includes the restore point does not start, it may be old, and the data may change in the interim.

Having a separate migration that makes the restore point have similar disadvantages: 1. They will need to manually create a new migration, which is essentially a copy of the old migration with a different version number, in order to make the restore point.

For design diagrams with large existing data, it is impractical to clean up the diagram when designing a migration because it precedes the flyway and can take a significant amount of time to recreate from scratch.

For development, ideally the workflow looks like this: 1. create a restore point 2. Develop migration (s), work using flyway 3. Roll back to restore the point if the migration does not work as needed.

If there is a way to automate step # 1 across the board, that would allow us to use the span and eliminate the need for dba, unless something went wrong and a rollback would be necessary there. There might be a more "flyby" way to fix this problem, but the workarounds I've found don't seem to fit into our existing workflow.

+1


source


We had the same problem. Ie, call a bunch of scripts always before and after every migration. For example, deleting and creating a materialized view, granting table permissions. These scripts do not change from migration to migration, but they need to be executed.

So, I took the callback class org.flywaydb.core.internal.callback.SqlScriptFlywayCallback

and adapted it for multiple files.

I tried to stay in philosophy flyway

and use the following pattern. Files starting with am__

or am__

are after the migration script, for those who bi__

have before and information. I am sorting the scripts so they run in the correct order.

public class MultipleScriptPerCallback extends BaseFlywayCallback {
private static final Log LOG = LogFactory.getLog(SqlScriptFlywayCallback.class);
private static final String DELIMITER = "__";

private static final String BEFORE_CLEAN = "bc";
private static final String AFTER_CLEAN = "ac";
private static final String BEFORE_MIGRATE = "bm";
private static final String AFTER_MIGRATE = "am";
private static final String BEFORE_EACH_MIGRATE = "bem";
private static final String AFTER_EACH_MIGRATE = "aem";
private static final String BEFORE_VALIDATE = "bv";
private static final String AFTER_VALIDATE = "av";
private static final String BEFORE_BASELINE = "bb";
private static final String AFTER_BASELINE = "ab";
private static final String BEFORE_REPAIR = "br";
private static final String AFTER_REPAIR = "ar";
private static final String BEFORE_INFO = "bi";
private static final String AFTER_INFO = "ai";

private static final List<String> ALL_CALLBACKS = Arrays.asList(BEFORE_CLEAN, AFTER_CLEAN, BEFORE_MIGRATE, BEFORE_EACH_MIGRATE,
        AFTER_EACH_MIGRATE, AFTER_MIGRATE, BEFORE_VALIDATE, AFTER_VALIDATE, BEFORE_BASELINE, AFTER_BASELINE, BEFORE_REPAIR,
        AFTER_REPAIR, BEFORE_INFO, AFTER_INFO);

private Map<String, List<SqlScript>> scripts;

@Override
public void setFlywayConfiguration(FlywayConfiguration flywayConfiguration) {
    super.setFlywayConfiguration(flywayConfiguration);

    if (scripts == null) {
        scripts = registerScripts(flywayConfiguration);
    }
}

private Map<String, List<SqlScript>> registerScripts(FlywayConfiguration flywayConfiguration) {
    Map<String, List<SqlScript>> scripts = new HashMap<>();
    for (String callback : ALL_CALLBACKS) {
        scripts.put(callback, new ArrayList<SqlScript>());
    }

    LOG.debug(String.format("%s - Scanning for Multiple SQL callbacks ...", getClass().getSimpleName()));
    Locations locations = new Locations(flywayConfiguration.getLocations());
    Scanner scanner = new Scanner(flywayConfiguration.getClassLoader());
    String sqlMigrationSuffix = flywayConfiguration.getSqlMigrationSuffix();
    DbSupport dbSupport = dbSupport(flywayConfiguration);
    PlaceholderReplacer placeholderReplacer = createPlaceholderReplacer();
    String encoding = flywayConfiguration.getEncoding();

    for (Location location : locations.getLocations()) {
        Resource[] resources;
        try {
            resources = scanner.scanForResources(location, "", sqlMigrationSuffix);
        } catch (FlywayException e) {
            // Ignore missing locations
            continue;
        }
        for (Resource resource : resources) {
            String key = extractKeyFromFileName(resource);
            if (scripts.keySet().contains(key)) {
                LOG.debug(getClass().getSimpleName() + " - found script " + resource.getFilename() + " from location: " + location);
                List<SqlScript> sqlScripts = scripts.get(key);
                sqlScripts.add(new SqlScript(dbSupport, resource, placeholderReplacer, encoding));
            }
        }
    }

    LOG.info(getClass().getSimpleName() + " - scripts registered: " + prettyPrint(scripts));
    return scripts;
}

private String prettyPrint(Map<String, List<SqlScript>> scripts) {
    StringBuilder prettyPrint = new StringBuilder();
    boolean isFirst = true;
    for (String key : scripts.keySet()) {
        if (!isFirst) {
            prettyPrint.append("; ");
        }
        prettyPrint.append(key).append("=").append("[").append(prettyPrint(scripts.get(key))).append("]");
        isFirst = false;
    }
    return prettyPrint.toString();
}

private String prettyPrint(List<SqlScript> scripts) {
    StringBuilder prettyPrint = new StringBuilder();
    boolean isFirst = true;
    for (SqlScript script : scripts) {
        if (!isFirst) {
            prettyPrint.append(", ");
        }
        prettyPrint.append(script.getResource().getFilename());
        isFirst = false;
    }
    return prettyPrint.toString();
}

private String extractKeyFromFileName(Resource resource) {
    String filename = resource.getFilename();
    eturn filename.substring(0, (!filename.contains(DELIMITER)) ? 0 : filename.indexOf(DELIMITER)).toLowerCase();
}

private DbSupport dbSupport(FlywayConfiguration flywayConfiguration) {
    Connection connectionMetaDataTable = JdbcUtils.openConnection(flywayConfiguration.getDataSource());
    return DbSupportFactory.createDbSupport(connectionMetaDataTable, true);
}

/**
 * @return  A new, fully configured, PlaceholderReplacer.
 */
private PlaceholderReplacer createPlaceholderReplacer() {
    if (flywayConfiguration.isPlaceholderReplacement()) {
        return
            new PlaceholderReplacer(flywayConfiguration.getPlaceholders(), flywayConfiguration.getPlaceholderPrefix(),
                flywayConfiguration.getPlaceholderSuffix());
    }

    return PlaceholderReplacer.NO_PLACEHOLDERS;
}

@Override
public void beforeClean(Connection connection) {
    execute(BEFORE_CLEAN, connection);
}

@Override
public void afterClean(Connection connection) {
    execute(AFTER_CLEAN, connection);
}

@Override
public void beforeMigrate(Connection connection) {
    execute(BEFORE_MIGRATE, connection);
}

@Override
public void afterMigrate(Connection connection) {
    execute(AFTER_MIGRATE, connection);
}

@Override
public void beforeEachMigrate(Connection connection, MigrationInfo info) {
    execute(BEFORE_EACH_MIGRATE, connection);
}

@Override
public void afterEachMigrate(Connection connection, MigrationInfo info) {
    execute(AFTER_EACH_MIGRATE, connection);
}

@Override
public void beforeValidate(Connection connection) {
    execute(BEFORE_VALIDATE, connection);
}

@Override
public void afterValidate(Connection connection) {
    execute(AFTER_VALIDATE, connection);
}

@Override
public void beforeBaseline(Connection connection) {
    execute(BEFORE_BASELINE, connection);
}

@Override
public void afterBaseline(Connection connection) {
    execute(AFTER_BASELINE, connection);
}

@Override
public void beforeRepair(Connection connection) {
    execute(BEFORE_REPAIR, connection);
}

@Override
public void afterRepair(Connection connection) {
    execute(AFTER_REPAIR, connection);
}

@Override
public void beforeInfo(Connection connection) {
    execute(BEFORE_INFO, connection);
}

@Override
public void afterInfo(Connection connection) {
    execute(AFTER_INFO, connection);
}

private void execute(String key, Connection connection) {
    List<SqlScript> sqlScripts = scripts.get(key);
    LOG.debug(String.format("%s - sqlscript: %s for key: %s", getClass().getSimpleName(), sqlScripts, key));
    Collections.sort(sqlScripts, new SqlScriptLexicalComparator());
    for (SqlScript script : sqlScripts) {
        executeScript(key, connection, script);
    }
}

//Not private for testing
void executeScript(String key, Connection connection, SqlScript script) {
    LOG.info(String.format("%s - Executing SQL callback: %s : %s", getClass().getSimpleName(), key,
            script.getResource().getFilename()));
    script.execute(new JdbcTemplate(connection, 0));
}

//Not private for testing
static final class SqlScriptLexicalComparator implements Comparator<SqlScript> {
    @Override
    public int compare(SqlScript o1, SqlScript o2) {
        return Collator.getInstance().compare(o1.getResource().getFilename(), o2.getResource().getFilename());
    }
}

}

0


source


There is a way: use a script with a name like "R__thisRunsEveryTimeAfterOtherScripts.sql". Note the "R" for "Repeatable" and that no version number is specified.

https://flywaydb.org/documentation/migrations#repeatable-migrations - see the Repeatable Migrations and SQL-Oriented Migrations sections.

0


source






More articles:


All Articles