SQLServerCleanModePlugin.java

/*-
 * ========================LICENSE_START=================================
 * flyway-sqlserver
 * ========================================================================
 * Copyright (C) 2010 - 2026 Red Gate Software Ltd
 * ========================================================================
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * =========================LICENSE_END==================================
 */
package org.flywaydb.clean.database;

import org.flywaydb.core.extensibility.CleanModePlugin;
import org.flywaydb.core.internal.command.clean.CleanModel.Mode;
import org.flywaydb.core.internal.database.base.Database;
import org.flywaydb.core.internal.jdbc.JdbcTemplate;
import org.flywaydb.database.sqlserver.SQLServerDatabase;

import java.sql.SQLException;
import java.util.List;
import java.util.stream.Collectors;

public class SQLServerCleanModePlugin implements CleanModePlugin<SQLServerDatabase> {
    @Override
    public boolean handlesMode(final Mode cleanMode) {
        return cleanMode == Mode.ALL;
    }

    @Override
    public boolean handlesDatabase(final Database database) {
        return database instanceof SQLServerDatabase;
    }

    @Override
    public void cleanDatabasePostSchema(final SQLServerDatabase database, final JdbcTemplate jdbcTemplate)
        throws SQLException {
        if (supportsColumnEncryptionKeys(database)) {
            for (final String statement : cleanColumnEncryptionKeys(database, jdbcTemplate)) {
                jdbcTemplate.execute(statement);
            }
        }

        if (supportsColumnMasterKeys(database)) {
            for (final String statement : cleanColumnMasterKeys(database, jdbcTemplate)) {
                jdbcTemplate.execute(statement);
            }
        }

        for (final String statement : cleanSymmetricKey(database, jdbcTemplate)) {
            jdbcTemplate.execute(statement);
        }

        for (final String statement : cleanEventNotifications(database, jdbcTemplate)) {
            jdbcTemplate.execute(statement);
        }

        for (final String statement : cleanDatabaseExtendedProperties(database, jdbcTemplate)) {
            jdbcTemplate.execute(statement);
        }

        for (final String statement : cleanDatabaseRoles(database, jdbcTemplate)) {
            executeIgnoringErrors(jdbcTemplate, statement);
        }

        final boolean oldAutoCommit = jdbcTemplate.getConnection().getAutoCommit();
        jdbcTemplate.getConnection().setAutoCommit(true);
        for (final String statement : cleanFulltextStoplist(database, jdbcTemplate)) {
            jdbcTemplate.execute(statement);
        }
        for (final String statement : cleanSearchPropertyList(database, jdbcTemplate)) {
            jdbcTemplate.execute(statement);
        }
        for (final String statement : cleanFullTextCatalogs(database, jdbcTemplate)) {
            jdbcTemplate.execute(statement);
        }
        jdbcTemplate.getConnection().setAutoCommit(oldAutoCommit);
    }

    private void executeIgnoringErrors(final JdbcTemplate jdbcTemplate, final String statement) {
        try {
            jdbcTemplate.execute(statement);
        } catch (Exception ignored) {
        }
    }

    private boolean supportsColumnEncryptionKeys(final SQLServerDatabase database) {
        return database.getVersion().isAtLeast("13");
    }

    private boolean supportsColumnMasterKeys(final SQLServerDatabase database) {
        return database.getVersion().isAtLeast("13");
    }

    private List<String> cleanFullTextCatalogs(final SQLServerDatabase database, final JdbcTemplate jdbcTemplate)
        throws SQLException {
        return jdbcTemplate.queryForStringList("SELECT name FROM sys.fulltext_catalogs")
            .stream()
            .map(name -> "DROP FULLTEXT CATALOG " + database.quote(name))
            .collect(Collectors.toList());
    }

    private List<String> cleanColumnEncryptionKeys(final SQLServerDatabase database, final JdbcTemplate jdbcTemplate)
        throws SQLException {
        return jdbcTemplate.queryForStringList("SELECT name FROM sys.column_encryption_keys")
            .stream()
            .map(name -> "DROP COLUMN ENCRYPTION KEY " + database.quote(name))
            .collect(Collectors.toList());
    }

    private List<String> cleanColumnMasterKeys(final SQLServerDatabase database, final JdbcTemplate jdbcTemplate)
        throws SQLException {
        return jdbcTemplate.queryForStringList("SELECT name FROM sys.column_master_keys")
            .stream()
            .map(name -> "DROP COLUMN MASTER KEY " + database.quote(name))
            .collect(Collectors.toList());
    }

    private List<String> cleanSymmetricKey(final SQLServerDatabase database, final JdbcTemplate jdbcTemplate)
        throws SQLException {
        return jdbcTemplate.queryForStringList("SELECT name FROM sys.symmetric_keys")
            .stream()
            .map(name -> "DROP SYMMETRIC KEY " + database.quote(name))
            .collect(Collectors.toList());
    }

    private List<String> cleanEventNotifications(final SQLServerDatabase database, final JdbcTemplate jdbcTemplate)
        throws SQLException {
        return jdbcTemplate.queryForStringList("SELECT name FROM sys.event_notifications")
            .stream()
            .map(name -> "DROP EVENT NOTIFICATION " + database.quote(name) + " ON DATABASE")
            .collect(Collectors.toList());
    }

    private List<String> cleanDatabaseExtendedProperties(final SQLServerDatabase database,
        final JdbcTemplate jdbcTemplate) throws SQLException {
        return jdbcTemplate.queryForStringList("SELECT name FROM sys.extended_properties WHERE class = 0")
            .stream()
            .map(name -> "EXEC sp_dropextendedproperty  @name= " + database.quote(name))
            .collect(Collectors.toList());
    }

    private List<String> cleanDatabaseRoles(final SQLServerDatabase database, final JdbcTemplate jdbcTemplate)
        throws SQLException {
        return jdbcTemplate.queryForStringList(""
                + "SELECT name FROM sys.database_principals "
                + "WHERE (type = 'A' OR type = 'R') AND is_fixed_role = 0")
            .stream()
            .map(name -> "DROP ROLE " + database.quote(name))
            .collect(Collectors.toList());
    }

    private List<String> cleanFulltextStoplist(final SQLServerDatabase database, final JdbcTemplate jdbcTemplate)
        throws SQLException {
        return jdbcTemplate.queryForStringList("SELECT name FROM sys.fulltext_stoplists")
            .stream()
            .map(name -> "DROP FULLTEXT STOPLIST " + database.quote(name) + ";")
            .collect(Collectors.toList());
    }

    private List<String> cleanSearchPropertyList(final SQLServerDatabase database, final JdbcTemplate jdbcTemplate)
        throws SQLException {
        return jdbcTemplate.queryForStringList("SELECT name FROM sys.registered_search_property_lists")
            .stream()
            .map(name -> "DROP SEARCH PROPERTY LIST " + database.quote(name) + ";")
            .collect(Collectors.toList());
    }
}