Migração de banco de dados (flywaydb) baseada em um arquivo sql compactado (.zip)

README.md

Migração de banco de dados (flywaydb) baseada em um arquivo sql compactado (.zip)

Permite compactar scripts extensos e a execução do mesmo pelo https://flywaydb.org.

Na configuração do flyway (https://flywaydb.org/documentation/maven/migrate), definir o resolver

resolvers = com.github.nidorx.migration.ZipSqlMigrationResolver

O resolver irá descompactar o script sql em um diretório temporário e executar esse arquivo na tarefa de migração.

Exemplo de uso

Conforme imagem em anexo, existem diversos arquivos para execução, e alguns deles estão compactados (Base de dados CEP que descompactado tem aproximadamente 80MB).

Arquivos de migração

Log da execução

2017-02-06 INFO  org.flywaydb.core.internal.util.VersionPrinter info - Flyway 4.0.3 by Boxfuse
2017-02-06 INFO  org.flywaydb.core.internal.dbsupport.DbSupportFactory info - Database: jdbc:postgresql://127.0.0.1:5432/******* (PostgreSQL 9.6)
2017-02-06 INFO  org.flywaydb.core.internal.command.DbValidate info - Successfully validated 20 migrations (execution time 00:04.278s)
2017-02-06 INFO  org.flywaydb.core.internal.command.DbSchemas info - Creating schema "*******" ...
2017-02-06 INFO  org.flywaydb.core.internal.metadatatable.MetaDataTableImpl info - Creating Metadata table: "*******"."historico_migracao"
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Current version of schema "*******": 0
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.00 - Versao Inicial
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.01 - Dados CBO Grande Grupo
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.02 - Dados CBO SubGrupo Principal
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.03 - Dados CBO SubGrupo
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.04 - Dados CBO Familias
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.05 - Dados CBO Ocupacao
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.06 - Dados CBO Sinonimos
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.07 - Tabelas Endereco CEP
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.08 - Dados CEP uf e bairro
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.09 - Dados CEP localidade
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.10 - Dados CEP logradouro PARTE 1
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.11 - Dados CEP logradouro PARTE 2
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.12 - Dados CEP logradouro PARTE 3
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.13 - Dados CEP logradouro PARTE 4
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.14 - Dados CEP logradouro PARTE 5
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.15 - Dados CEP logradouro PARTE 6
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.16 - Dados CEP logradouro PARTE 7
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.17 - Dados CEP logradouro PARTE 8
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.18 - Dados CEP logradouro PARTE 9
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Migrating schema "*******" to version 0.0.1.19 - Dados CEP logradouro PARTE 10
2017-02-06 INFO  org.flywaydb.core.internal.command.DbMigrate info - Successfully applied 20 migrations to schema "*******" (execution time 02:16.004s).

Registros na base de dados

migrations.png

result.png

ZipSqlMigrationExecutor.java

package com.github.nidorx.migration;

import java.sql.Connection;
import org.flywaydb.core.api.resolver.MigrationExecutor;
import org.flywaydb.core.internal.dbsupport.DbSupport;
import org.flywaydb.core.internal.dbsupport.DbSupportFactory;
import org.flywaydb.core.internal.dbsupport.JdbcTemplate;
import org.flywaydb.core.internal.dbsupport.SqlScript;
import org.flywaydb.core.internal.util.PlaceholderReplacer;
import org.flywaydb.core.internal.util.scanner.Resource;

/**
 * Migração de banco de dados baseada em um arquivo sql.
 *
 * @author Alex Rodin <[email protected]>
 */
public class ZipSqlMigrationExecutor implements MigrationExecutor {

    private DbSupport dbSupport;

    private final PlaceholderReplacer phReplacer;

    private final Resource sqlFileResource;

    private final String encoding;

    public DbSupport getDbSupport() {
        return dbSupport;
    }

    public ZipSqlMigrationExecutor(Resource sqlFileResource, PlaceholderReplacer phReplacer, String encoding) {
        this.encoding = encoding;
        this.phReplacer = phReplacer;
        this.sqlFileResource = sqlFileResource;
    }

    @Override
    public void execute(Connection connection) {
        if (dbSupport == null) {
            dbSupport = DbSupportFactory.createDbSupport(connection, true);
        }
        final SqlScript sqlScript = new SqlScript(dbSupport, sqlFileResource, phReplacer, encoding);
        sqlScript.execute(new JdbcTemplate(connection, 0));
    }

    @Override
    public boolean executeInTransaction() {
        return true;
    }
}

ZipSqlMigrationResolver.java

package com.github.nidorx.migration;

import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.zip.CRC32;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;
import org.flywaydb.core.api.FlywayException;
import org.flywaydb.core.api.MigrationType;
import org.flywaydb.core.api.MigrationVersion;
import org.flywaydb.core.api.configuration.FlywayConfiguration;
import org.flywaydb.core.api.resolver.BaseMigrationResolver;
import org.flywaydb.core.api.resolver.MigrationResolver;
import org.flywaydb.core.api.resolver.ResolvedMigration;
import org.flywaydb.core.internal.resolver.MigrationInfoHelper;
import org.flywaydb.core.internal.resolver.ResolvedMigrationComparator;
import org.flywaydb.core.internal.resolver.ResolvedMigrationImpl;
import org.flywaydb.core.internal.util.Location;
import org.flywaydb.core.internal.util.Pair;
import org.flywaydb.core.internal.util.PlaceholderReplacer;
import org.flywaydb.core.internal.util.scanner.Resource;
import org.flywaydb.core.internal.util.scanner.Scanner;
import org.flywaydb.core.internal.util.scanner.classpath.ClassPathResource;
import org.flywaydb.core.internal.util.scanner.filesystem.FileSystemResource;

/**
 * Resolver para migração de arquivos SQL compactados com .zip
 *
 * Os nomes dos arquivos .zip devem ter o sufixo <code>${sqlMigrationSuffix}.zip</code>, onde ${sqlMigrationSuffix} =
 * https://flywaydb.org/documentation/maven/migrate  <code>
 *      V1__Description.sql.zip
 *      V1_1__Description.sql.zip
 * </code>
 *
 * Dentro de cada arquivo .zip deve existir apenas 1 arquivo .sql, com o mesmo nome do arquivo zip
 *
 * @author Alex Rodin <[email protected]>
 */
public class ZipSqlMigrationResolver extends BaseMigrationResolver implements MigrationResolver {

    private Scanner scanner;

    private String prefix;

    private String suffix;

    private String zipSuffix;

    private String separator;

    private String encoding;

    private PlaceholderReplacer phReplacer;

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

        scanner = new Scanner(conf.getClassLoader());
        prefix = flywayConfiguration.getSqlMigrationPrefix();
        suffix = flywayConfiguration.getSqlMigrationSuffix();
        separator = flywayConfiguration.getSqlMigrationSeparator();
        zipSuffix = suffix + ".zip";
        encoding = flywayConfiguration.getEncoding();
        phReplacer = new PlaceholderReplacer(
                conf.getPlaceholders(), conf.getPlaceholderPrefix(), conf.getPlaceholderSuffix());
    }

    @Override
    public List<ResolvedMigration> resolveMigrations() {
        List<ResolvedMigration> migrations = new ArrayList<>();

        for (String descriptor : flywayConfiguration.getLocations()) {
            final Location location = new Location(descriptor);
            scanForMigrations(migrations, location);
            scanForMigrations(migrations, location);
        }

        Collections.sort(migrations, new ResolvedMigrationComparator());
        return migrations;
    }

    public void scanForMigrations(List<ResolvedMigration> migrations, Location location) {
        for (Resource zipResource : scanner.scanForResources(location, prefix, zipSuffix)) {
            parseZip(migrations, zipResource, location);
        }
    }

    private void parseZip(
            List<ResolvedMigration> migrations, Resource zipResource, Location location) throws FlywayException {

        final String zipFilename = extractZipFileName(zipResource, location);
        final String fileNameWtExt = zipFilename.substring(0, zipFilename.length() - zipSuffix.length());

        ZipInputStream zis = null;
        try {
            final Path tempDir = Files.createTempDirectory("flywaydb-sql-zip");

            zis = getZipInputStream(zipResource);

            ZipEntry entry;
            while (true) {
                entry = zis.getNextEntry();
                if (entry == null) {
                    break;
                }
                if (entry.isDirectory()) {
                    // Ignores directory
                    continue;
                }
                if (!entry.getName().equals(fileNameWtExt + suffix)) {
                    // Only file with the same name
                    continue;
                }

                final File sqlFile = tempDir.resolve("./" + entry.getName()).toFile();
                sqlFile.getParentFile().mkdirs();

                int size;
                byte[] buffer = new byte[2048];

                try (OutputStream fos = new FileOutputStream(sqlFile);
                        BufferedOutputStream bos = new BufferedOutputStream(fos, buffer.length)) {
                    while ((size = zis.read(buffer, 0, buffer.length)) != -1) {
                        bos.write(buffer, 0, size);
                    }
                    bos.flush();
                }

                migrations.add(createMigration(zipResource, zipFilename, sqlFile));
                break;
            }
        } catch (IOException ex) {
            throw new FlywayException(
                    "Unable to unzip file" + zipResource.getLocation() + " (" + zipResource.getLocationOnDisk() + ")",
                    ex
            );
        } finally {
            try {
                if (zis != null) {
                    zis.close();
                }
            } catch (final IOException ioe) {
                // ignore
            }
        }
    }

    private ZipInputStream getZipInputStream(final Resource zipResource) {
        InputStream inputStream = null;
        try {
            if (zipResource instanceof FileSystemResource) {
                inputStream = new FileInputStream(zipResource.getLocation());

            } else if (zipResource instanceof ClassPathResource) {
                inputStream = flywayConfiguration.getClassLoader().getResourceAsStream(zipResource.getLocation());
            }
        } catch (IOException e) {
            throw new FlywayException("Unable to obtain inputstream for resource: " + zipResource.getLocation(), e);
        }

        if (inputStream == null) {
            throw new FlywayException("Unable to obtain inputstream for resource: " + zipResource.getLocation());
        }

        return new ZipInputStream(inputStream);
    }

    private ResolvedMigrationImpl createMigration(
            final Resource zipResource, final String zipFilename, final File sqlFile) throws FlywayException {

        final String filename = zipResource.getFilename();
        final Resource sqlFileResource = new FileSystemResource(sqlFile.getAbsolutePath());

        final Pair<MigrationVersion, String> info
                = MigrationInfoHelper.extractVersionAndDescription(filename, prefix, separator, zipSuffix);

        final ResolvedMigrationImpl migration = new ResolvedMigrationImpl();
        migration.setVersion(info.getLeft());
        migration.setDescription(info.getRight());
        migration.setScript(zipFilename);
        migration.setChecksum(calculateChecksum(zipResource, sqlFile));
        migration.setType(MigrationType.CUSTOM);

        migration.setPhysicalLocation(sqlFile.getAbsolutePath());
        migration.setExecutor(new ZipSqlMigrationExecutor(sqlFileResource, phReplacer, encoding));
        return migration;
    }

    private String extractZipFileName(Resource resource, Location location) {
        if (location.getPath().isEmpty()) {
            return resource.getLocation();
        }

        return resource.getLocation().substring(location.getPath().length() + 1);
    }

    static int calculateChecksum(Resource resource, File file) {
        final CRC32 crc32 = new CRC32();

        try {
            InputStream fis = new FileInputStream(file);
            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(fis));
            String line;
            while ((line = bufferedReader.readLine()) != null) {
                crc32.update(line.getBytes("UTF-8"));
            }
        } catch (IOException e) {
            String message = "Unable to calculate checksum";
            if (resource != null) {
                message += " for " + resource.getLocation() + " (" + resource.getLocationOnDisk() + ")";
            }
            throw new FlywayException(message, e);
        }

        return (int) crc32.getValue();
    }

}